[Oracle] 날마다 시퀀스가 초기화되는 오라클 날짜 시퀀스(Sequence) 만들기

날짜를 포함한 시퀀스가 필요해서 만들어 봤습니다.

날마다 시퀀스가 1로 초기화 돼야 해서 생각보다는 복잡합니다.

1. 시퀀스 생성
2. 시퀀스를 1로 초기화하는 프로시저 생성
3. 프로시저를 오라클 job에 등록
4. 날짜를 포함한 시퀀스 값을 가져오는 함수를 생성

1. 시퀀스 생성
- 저는 3자리 시퀀스가 필요해서 MAX 999까지만 설정했습니다.

/* 배치키 시퀀스 */
CREATE SEQUENCE SEQ_BATCHKEY
START WITH 1
MAXVALUE 999
MINVALUE 1
CYCLE
CACHE 10
NOORDER
;

2. 시퀀스를 1로 초기화하는 프로시저 생성한다.

/* 시퀀스 초기화 Procedure */
CREATE OR REPLACE PROCEDURE SZP_BATCHKEY_RESET(SEQ_NAME IN VARCHAR2)
IS
    L_VAL NUMBER;
BEGIN
 
    EXECUTE IMMEDIATE 'SELECT '|| SEQ_NAME ||'.NEXTVAL FROM DUAL' INTO L_VAL;
    
    EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| SEQ_NAME ||' INCREMENT BY -'|| L_VAL ||' MINVALUE 0';
    
    EXECUTE IMMEDIATE 'SELECT '|| SEQ_NAME ||'.NEXTVAL FROM DUAL' INTO L_VAL;
    
    EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| SEQ_NAME ||' INCREMENT BY 1 MINVALUE 0';
 
END;

3. 프로시저를 오라클 job에 등록한다.

DECLARE
    X NUMBER;
BEGIN
        SYS.DBMS_JOB.SUBMIT
        (
              job => X
            , what =>
                'BEGIN
                        SZP_BATCHKEY_RESET(''SEQ_BATCHKEY'');
                 END;'
            , next_date => to_date('05-31-2017 00:00:00', 'mm/dd/yyyy hh24:mi:ss')
            , interval => 'TRUNC(SYSDATE+1)'
            , no_parse => FALSE
        );
        SYS.DBMS_OUTPUT.PUT_LINE('Job Number is : '|| to_char(X));
END;

4. 날짜를 포함한 시퀀스 값을 가져오는 함수를 생성한다.

CREATE OR REPLACE FUNCTION ZBF_GET_BATCHKEY(
    p_type in varchar2
) RETURN VARCHAR2
AS
     v_returnValue VARCHAR2(180);
BEGIN
 
    BEGIN
 
        select TO_CHAR(SYSDATE, 'YYMMDD') || LPAD(SEQ_BATCHKEY.nextval, 3, 0)
          into v_returnValue
          from dual;
 
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_returnValue := ' ';
      WHEN OTHERS THEN
        v_returnValue := ' ';
    END;
 
    RETURN v_returnValue;
END;

이렇게 모두 생성이 되었다면 이런식으로 호출하면 되겠죠.

select ZBF_GET_BATCHKEY('now') from DUAL;

제가 만들고 실행해보니 아래처럼 결과가 잘 나옵니다.