데이터베이스 (집공부9)

1. 시퀀스

 -> 오라클에서는 행을 구분하기 위해서 기본 키를 두고 있습니다. 기본키는 중복된 값을 가질 수 있으므로 항상 유일한 값을 가져야 합니다.

 -> 기본 키가 유일한 값을 갖도록 사용자가 직접 값을 생성해내려면 부담이 클 것입니다.

 -> 시퀀스는 테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기이므로 시퀀스를 기본키로 사용하게 되면 사용자의 부담을 줄일 수 있다.

--------------------------------------------------------------------------------------------------------------------------------------

1.1 시퀀스 생성하기 위한 기본 형식

CREATE SEQUENCE sequence_name

[START WITH n]                                -①

[INCREMENT BY n]                            -②

[{MAXVALUE n | NOMAXVALUE}]          -③

[{MINVALUE n | NOMINVALUE}]           -④

[{CYCLE | NOCYCLE}]                          -⑤

[{CACHE n | NOCACHE}]                     -⑥

①. START WITH

 -> 시퀀스 번호의 시작값을 지정할 때 사용됩니다. 만일 1부터 시작되는 시퀀스를 생성하려면 START WITH 1 이라고 기술하면 됩니다.

②. INCREMENT BY

 -> 연속적인 시퀀스 번호의 증가치를 지정할 때 사용됩니다. 만일 1씩 증가하는 시퀀스를 생성하려면 INCREMENT BY 1 이라고 기술하면 됩니다.

③. MAXVALUE n | NOMAXVALUE

 -> MAXVALUE 은 시퀀스가 가질 수 있는 최대값을 지정합니다. 만일 NOMAXVALUE를 지정하게 되면 ASCENDING 순서일 경우에는 10의 27승이고

DESCENDING 순서일 경우에는 -1로 설정됩니다.

④. MINVALUE n | NOMINVALUE

 -> MINVALUE은 시퀀스가 가질 수 있는 최소값을 지정합니다. 만일 NOMINVALUE을 지정하게 되면 ASCENDING 순서일 경우에는 1이고

DESCENDING 순서일 경우에는 10의26승으로 설정됩니다.

⑤. CYCLE | NOCYCLE

 -> CYCLE은 지정된 시퀀스 값이 최대값까지 증가가 완료되게 되면 다시 START WITH 옵션에 지정한 시작 값에서 다시 시퀀스를 시작하도록 합니다.

NOCYCLE은 증가가 완료되게 되면 에러를 유발시킵니다.

⑥. CACHE n | NOCACHE

 -> CACHE은 메모리상의 시퀀스 값을 관리하도록 하는 것인데 기본 값은 20입니다. NOCACHE는 원칙적으로 메모리 상에서 시퀀스를 관리하지 않습니다.

--------------------------------------------------------------------------------------------------------------------------------------

실습

/*데이터가 입력되는 순서를 기본키로 지정한다고 하였을 때 사용자가 직접 입력을 해야 한다면 

어려움이 있다. 이렇듯 숫자를 자동으로 생성하여 기본 키의 조건을 만족시켜 주며, 편의를

제공해줄 수 있는 방법이 시퀀스(sequence)라는 것이다.

*/

--시퀀스 생성

CREATE SEQUENCE JONG_KYU        -- JONG_KYU라는 시퀀스 객체의 이름을 저장

 START WITH 1                            --

 INCREMENT BY 1                        -- 증가치에 대한 정보

 MAXVALUE 10000                       -- 최대값

 MINVALUE 1                              -- 최소값

 NOCYCLE;

--시퀀스 검색(현재 계정의 모든 시퀀스를 보여줌)

SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG

FROM USER_SEQUENCES;

select * from USER_SEQUENCES;

--------------------------------------------------------------------------------------------------------------------------------------

--MEMBERINFO 테이블 생성

CREATE TABLE MEMBERINFO(

NO NUMBER PRIMARY KEY, --기본키(시퀀스 적용할 컬럼)

NAME VARCHAR2(10) NOT NULL,

PHONENUMBER VARCHAR2(20) NOT NULL

);

select * from memberinfo;

--------------------------------------------------------------------------------------------------------------------------------------

--시퀀스 테이블에 적용하기 위해 INSERT문을 이용해서 데이터를 입력하자

--시퀀스는 대부분 INSERT 문과 사용된다.

INSERT INTO MEMBERINFO

VALUES(JONG_KYU.NEXTVAL, '종규','01012345678');

INSERT INTO MEMBERINFO

VALUES (JONG_KYU.NEXTVAL, '김종','01015425678');

INSERT INTO MEMBERINFO

VALUES (JONG_KYU.NEXTVAL, '규김','01015559888');

--MEMBERINFO 테이블 검색

select * from memberinfo;

--현재 시퀀스의 값 검색

select JONG_KYU.currval from dual;

--------------------------------------------------------------------------------------------------------------------------------------

1.2 시퀀스 수정

 

--수정 기본 형식

ALTER SEQUENCE 시퀀스명

[INCREMENT BY 수정할 증가값]

[MAXVALUE 수정할 최대값]

[MINVALUE 수정할 최소값]

[CYCLE 또는 NOCYCLE];

--시퀀스 수정

SELECT * from USER_SEQUENCES;

ALTER SEQUENCE JONG_KYU

MAXVALUE 2000;

SELECT * from USER_SEQUENCES;

--------------------------------------------------------------------------------------------------------------------------------------

1.2 시퀀스 삭제

--시퀀스 삭제

SELECT *from USER_SEQUENCES;

drop sequence JONG_KYU;

SELECT *from USER_SEQUENCES;

--MEMBERINFO 테이블 검색

SELECt * from MEMBERINFO;

테이블을 삭제하여도 이미 입력한 데이터는 영향을 받지 않는 것을 알 수 있다.!!!!

--------------------------------------------------------------------------------------------------------------------------------------

1.3 CURRVAL, NEXTVAL

 -> 시퀀스의 현재 값을 알아내기 위해서는 CURRVAL을 사용하고, 다음 값을 알아내기 위해서는 NEXTVAL를 사용합니다.

CURRVAL : 현재 값을 반환한다.

NEXTVAL : 현재 시퀀스값의 다음 값을 반환한다.

- CURRVAL에 새로운 값이 할당되기 위해서는 NEXTVAL로 새로운 값을 생성해야 합니다.

- 즉, NEXTVAL로 새로운 값을 생성한 다음에 이 값을 CURRVAL에 대체하게 됩니다.

☞ NEXTVAL, CURRVAL을 사용할 수 있는 경우

 -> 서브 쿼리가 아닌 SELECT 문

 -> INSERT문의 SELECT 절

 -> INSERT문의 VALUE 절

 -> UPDATE문의 SET 절

☞ NEXTVAL, CURRVAL을 사용할 수 없는 경우

 -> VIEW의 SELECT 절

 -> DISTINCT 키워드가 있는 SELECT 문

 -> GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문

 -> SELECT, DELETE, UPDATE의 서브 쿼리

 -> CREATE TABLE, ALTER TABLE 명령의 DEFAULT 값

ex)

1. SELECT JONG_KYU.NEXTVAL FROM DUAL;

2. SELECT JONG_KYU.CURRVAL FROM DUAL: