[ORACLE]오라클에서 시퀀스(SEQUENCE) 처리를 트리거(TRIGGER)로 생성 by VINS
오라클 이외 다른 몇몇 DBMS에서는
자동증분하는 메카니즘은 각 DB마다 방법이 다양하게 존재를 한다.
MS-SQL은 IDENTITY(1,1)를 - 1부터 1개씩 증가 // (1000, 1) 1000부터 1개씩
MySQL은 auto_increment 처럼 테이블 생성시 한번 설정해 놓으면
오라클 처럼 INSERT문에서 다시는 그 시퀀스의 해당하는 컬럼을 고려하지 않아도
자동 증분 값이 이루어 지게 된다.
오라클에서는 CREATE SEQUENCE 통해 증분되는 값을 개발자가 컨트롤 할 수 있게 되어 있다.
MS-SQL, MySQL등 디비에서도 사실 내부적으로 이와 유사한 처리를 하고 있어
가능하지만 오라클은 그 보다 더 많은 제어권을 개발자한테 넘기고 있기 때문에 자동증분을 처리해야하는 경우
개발자의 몫이 좀 더 크다고 할 수 있다
이는 곧 장점도 단점도 될 수 있다는 뜻이 된다.
오라클 시퀀스의 장점으로는 MS-SQL 단점(?)을 역설하자면의 경우 identity(1,1) 로 설정되었다고 가정할 경우
Insert된 시퀀스값을 알기 위해 @@identity, Scope_identity() 또 하나는 기억나지 않는지만. ...
이런것들로 현재 증분되는 값을 알기위한 로직이 따로 존재를 한다.
이 테이블의 대한 예를 한번 보자
create table sometable(
idx int identity(1,1) primary key
, col1 varchar(30)
, col2 varchar(30)
)
하지만 대부분 개발자들의 DB에 대한 무지함으로
Insert sometable (col1, col2) values('col1','col2);
이후 select MAX(idx) from sometable ㅡ.ㅡ 라는 쿼리를 쓰는 것을 종종 보았다
잠시 실제 있었던 에피소드를 하나 들자면
위와 같은 쿼리를 내가 모기업에서 근무를 할 때 일이었다
하루(Oneday의 의미 : 필자의 명이 아님)는 개발자에게 물어봤다
동시에 2개 이상의 Insert 쿼리가 날라오면 어떤 값이 선택되어 지는 건가요? 라고
그랬더니 확율(?)상 해당 인덱스 값이 나올 경우가 대부분이라는 대답을 들었다.
그래서 그 사람이 계속 회사에 남아서 일하는 확율이 얼마나 될지 생각해 보았다. ㅋㅋ
기막힌 추론이었고 그는 그 이상으로 무언가를 탐구하는 것을 좋아하지 않았다.
오라클은 시퀀스를 제공함으로서 이런 무지함을 용서하지 않을려고 했던 것 같다 (개인적인 내생각임)
다시 본문으로 돌아와서 얘기를 계속 하겠다.
오라클은 이런 자동증분 설정이 없기 때문에 시퀀스라고 하는 것을 만들어야 한다.
시퀀스를 만드는 부분은 여기서 소개할 내용과 다르기 때문에
"CREATE SEQUENCE" 라는 키워드로 검색해 보길 바란다.
난 아래와 같이 시퀀스를 하나 만들었다.
CREATE SEQUENCE Seq_ST
MINVALUE 1
NOMAXVALUE
INCREMENT BY 1 START WITH 1
NOCACHE
NOCYCLE;
필자는 명명 규칙을 항상 디렉토리 형식의 계층화를 하는 것을 좋아한다.
그리고 약어로는 의미있는 앞자를 따서 사용하는 방식 즐겨 사용한다.
명명은 여러분 마음대로 해도 상관 없다.
어째든 여기서는 시퀀스명을 "Seq_ST"로 지정했다 ST = SomeTable
지금 부터 트리거를 통한 시퀀스를 자동증분 키 처럼 보이도록 하는 부분이다.
----------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER ST_trigger
BEFORE INSERT
ON SomeTable FOR EACH ROW
BEGIN
//시퀀스의 다음 값을 new.idx 로 저장
SELECT Seq_ST.NEXTVAL INTO :new.idx FROM dual;
END;
트라거가 생성되었습니다.
-----------------------------------------------------------------------------
위에서 보면 :new 키워드, NEXTVAL이 나온다.
NEXTVAL은 시퀀스 값을 증가 시킨후 반환하는 함수이고
현재값 조회는 CURRVAL 라는 함수로 하면 된다.
Select Seq_ST.CurrVal from dual; 라고 하면 현재 값을 반환해 줄 것이다.
하지만 여긴 사용 규칙이 있다.
----------------NextVal, CurrVal의 사용 규칙-----------------------
◈ NEXTVAL, CURRVAL을 사용할 수 있는 경우
- subquery가 아닌 select문
- insert문의 select절
- insert문의 value절
- update문의 set절
◈ NEXTVAL, CURRVAL을 사용할 수 없는 경우
- view의 select절
- distinct 키워드가 있는 select문
- group by, having, order by절이 있는 select문
- select, delete, update의 subquery
- create table, alter table 명령의 default값
다음은 new 키워드인데 이는 시퀀스에서 사용하는 메카니즘이 아니라.
트리거에서 사용한다.
트리거에서 형의 내용을 제어 하기위해서 :old, :new 라는 두개의 키워드를
사용한다.
:OLD 연산자 - 이전행의 값, 현재 존재하는 행의 값등을 지정할때 사용
:NEW 연산자 - 새로 지정될 행의 값등을 지정할 때 사용
EX) 위 샘플 테이블의 내용을 기준으로 설명 하겠음. (SomeTable)
INSERT 문의 경우 : NEW.idx (new 연산자만 존재 가능)
UPDATE 문의 경우: SET 절에서 OLD.Col1 = NEW.Col1 (양측 사용가능)
DELETE 문의 경우 : OLD.idx (old 연산자만 존재 가능)
만일 :new, :old 연산자가 마음에 안든다면 바꿀수도 있다.
하지만 대부분 그런 일이 없어 여기 적지 않겠다.
------------------- SQL의 실행----------------------------
자 지금까지 작업했던 내용을 테스트 해 볼 차례이다.
SQL> insert into sometable values(NULL, 'antineutron','하루의 블로그');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO T(Col1, Col2) VALUES('ANTINEUTRON','반중성자');
1 개의 행이 만들어졌습니다.
SQL> select * from t;
IDX COL1 COL2
---------- ------------------- ----------
1 antineutron 하루의 블로그
2 ANTINEUTRON 반중성자
이상 끝
