sql _ 시퀀스

** 참고사이트 https://m.blog.naver.com/heartflow89/220988415124

[Oracle/오라클] 시퀀스(sequence) 생성 / 검색 / 적용 / 수정 / 삭제

시퀀스를 사용하는 이유?

라클에서 데이터베이스의 무결성을 위해 제약 조건을 설정하고, 그중 행을 구분하기 위해

기본 키(PRIMARY KEY / not null + unique)를 설정한다고 설명하였다.

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

숫자를 자동으로 생성하여 기본 키의 조건을 만족시켜 주며, 편의를 제공해 줄수 있는 방법이 시퀀스이다.

 

ex) 지금 하고 있는 프로젝트 중 시나리오실행에서 시퀀스(SEQUENCE) 생성에서 시나리오no을 시퀀스에 적용하는 것이 아니라 실행번호를 시퀀스로 적용해야하는 이유는,

예를 들어 시나리오 번호는 사용자가 입력하게 되는 부분이다, 그래서 사용자가 5번을 입력 후 나중에 또 5번을 입력하면 겹치게 되므로 lock걸려있는 실행번호를 시퀀스로 적용을 해놓으면 겹치지 않게 된다.

 

시퀀스를 생성하는 형식은 아래와 같음. 테이블이나 뷰 등과 생성하는 방법이 비슷하다.

ex) 진행중인 TTS

 

CREATE SEQUENCE  "PVOICE_TEMP"."SEQ_TTSEX"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 241 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;

 

-- 시퀀스 생성
CREATE SEQUENCE NO_SEQ
  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;

시퀀스 적용

실제 테이블에 시퀀스를 적용하는 방법에 대해서 알아보도록 하자.

우선 시퀀스를 적용하기 위한 테이블을 하나 생성하도록 하자.

 

--MEMBERINFO 테이블생성
CREATE TABLE MEMBERINFO(
	NO NUMBER PRIMARY KEY, --기본키(시퀀스 적용할 컬럼)
    NAME VARCHAR2(10) NOT NULL,
    PHONENUMBER VARCHAR2(20) NOT NULL
);

시퀀스를 테이블에 적용하기 위해서는 INSERT문을 이용해서 데이터를 입력하는 곳에 '시퀀스명.NEXTVAL'를 입력하면 된다. (시퀀스는 대부분 INSERT문과 사용된다.) 위의 생성한 시퀀스를 이용해서 MEMBERRINFO 테이블에 입력하고 확인해보자.

 

EX) 특수용어사전 

INSERT INTO T_SCENARIO_VAR (

ID,

VARSEQ,

VARNAME,

VALUE,

SCOPE,

JOBDAY

) VALUES (

#{id},

SCENARIO_VAR_SEQ.NEXTVAL,

#{varname},

#{value},

#{scope},

SYSDATE

)

--데이터 추가
INSERT INTO MEMBERINFO
VALUES(NO_SEQ.NEXTVAL, 'JOKER', '01012345678');

INSERT INTO MEMBERINFO
VALUES(NO_SEQ.NEXTVAL, 'BATMAN', '01012340987');

INSERT INTO MEMBERINFO
VALUES(NO_SEQ.NEXTVAL, 'Quinn', '01010041004');

--MEMBERINFO 테이블 검색
SELECT * FROM MEMBERINFO;

--현재 시퀀스의 값 검색
SELECT NO_SEQ.CURRVAL FROM DUAL;

시퀀스를 적용하여 데이터를 입력하면 자동적으로 증가되면서 저장되는것을 알수있따.

이때 NEXTVAL는 현재 시퀀스값의 다음 값을 반환하고, CURRVAL는 현재 시퀀스 값을 반환한다.

 

시퀀스 수정

 

ALTER문을 이용하여 시퀀스 수정이 가능하다. CREATE문과 구조가 거의 동일

단, START값은 수정이 불가능 하기 때문에 시작 값을 다시 지저하려면 삭제 후 재 생성을 해야한다.

위에서 생성했던 시퀀스의 초대값을 수정하는 코드는 아래와 같다.

 

-- 시퀀스 수정
ALTER SEQUENCE NO_SEQ
  MAXVALUE 1000;

-- 시퀀스 수정 결과 검색
SELECT SEQUENCE_NAME, MAX_VALUE 
FROM USER_SEQUENCES;

시퀀스 삭제

시퀀스를 삭제하는 방법은 DROP 명령어를 사용하면 된다. 삭제하는 형식은 

"DROP SEQEUNCE 시퀀스명" 이다.

 

-- 시퀀스 삭제
DROP SEQUENCE NO_SEQ;

-- 시퀀스 삭제 결과 확인
SELECT SEQUENCE_NAME FROM USER_SEQUENCES ;

-- MEMBERINFO 테이블 검색
SELECT * FROM MEMBERINFO;

테이블을 검색해보면 시퀀스를 삭제하여도 이미 입력한 데이터는 영향을 받지 않는 것을 알 수 있다.

 

//

데이터를 삭제할때 사고를 대비해서 SQL에서 백업하는 방법과 테이블을 따로 만들어서 연습하는 방법을 배워햐할거같다.