[DB] MySql Sequence 생성 및 사용 방법 정리하기

Oracle 에서는 seq를 생성 하고 seq명.NEXTVAL 로 설정해주면 자동으로 seq가 채번이 된다

 

하지만 MySql에서는 seq 개념 자체가 존재하지 않기때문에 Oracle에서 제공해주는 기능처럼 사용자가 프로시저를 만들어 사용해야 되는 차이점이 존재한다.

 

1. SEQ 테이블 생성하기

SEQ 의 값이 저장되고 변경될 때 사용하는 테이블을 생성해준다.

보통 SEQ 스키마를 따로 생성하여 SEQ.테이블명으로 구분해놓는다.

CREATE TABLE "seqTable" (
  "seq_nm" varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  "bgin_val" bigint unsigned DEFAULT NULL,
  "max_val" bigint unsigned DEFAULT NULL,
  "cur_val" bigint unsigned DEFAULT '0',
  "step_val" bigint DEFAULT '1',
  "cycle_yn" tinyint DEFAULT '1',
  "tbl_schema" varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin DEFAULT NULL,
  "tbl_nm" varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin DEFAULT NULL,
  "seqce_cret_dtm" timestamp NULL DEFAULT NULL,
  "seqce_updt_dtm" timestamp NULL DEFAULT NULL,
  PRIMARY KEY ("seq_nm")
);

 

여러 seq가 생성될 수 있기때문에 seq 명, 시작값,최대값,현재값,증가값,반복여부, 사용될 테이블 스키마/테이블명 등등으로 구성해주면 된다.

 

2. SEQ 프로시저 생성하기

Oracle에서 제공해주는 NEXTVAL 의 기능을 프로시저로 구현한다.

 

CREATE PROCEDURE "nextval"(in sq_name varchar(64), out v_ret bigint unsigned)
    MODIFIES SQL DATA
begin
   declare v_cur bigint unsigned ;
   declare v_min bigint unsigned;
   declare v_max bigint unsigned;
   declare v_step bigint ;
   declare v_cycle tinyint ;

   select cur_val, bgin_val, max_val, step_val, cycle_yn into v_cur, v_min, v_max, v_step, v_cycle from seq.seqce_info_lst   where seq_nm = lower(sq_name)  ;

   if v_cycle = 0 and v_cur = v_max then
      signal SQLSTATE '45000'
         set MESSAGE_TEXT='sequence current value has reached the maximum (max_val).' ;
         set v_ret = -1 ;
   end if ;

      start transaction ;
        update seq.seqTable 
        set cur_val = cur_val + step_val , 
            seqce_updt_dtm = current_timestamp
        where seq_nm = lower(sq_name) ;
        select cur_val into v_ret from seq.seqTable where seq_nm = lower(sq_name) ;
       commit ;
    end if ;
end

 

위의 코드를 간략하게 설명하자면,

 

1. 현재 채번되어있는 값이 최대값과 같으면 SIGNAL 명령어를 사용하여 사용자 에러를 나타낸다. 

SQLSTATE 4500의 값은 예외를 구분하는 코드로, 표준 SQL에 정의되어있는 값이다.

2자리는 예외 클래스를 3자리는 예외코드를 나타내고 있으므로 앞의 2자리 45는 데이터 예외를 뜻하는 코드이다.

 

2. Max가 아니면 현재값에 증가값을 더해서 seq를 update 후 out 변수에 할당해 넘겨주고 있다.

 

3. SEQ 프로시저 사용하기

SEQ.nextval('seq명') 을 사용하면 out 값으로 seq nextValue값이 채번이 된다.

이런식으로 필요한 기능들을 프로시저로 구현하여 호출해서 사용하고 있다.