CREATE OR REPLACE PROCEDURE SP_GET_DAY_SEQ
(
P_SEQ_NM IN VARCHAR2 -- 이용할 SEQUENCE NAME
,P_YYYYMMDD IN VARCHAR2 -- YYYYMMDD
,P_OUT_VAL OUT VARCHAR2
,P_SQLCODE OUT INTEGER -- SQLCODE : 출력_에러코드
,P_SQLMSG OUT VARCHAR2 --ERROR MESSAGE : 출력_에러코드
)
/***********************************************************************
PROGRAM NAME : SP_GET_DAY_SEQ
CREATOR : jeeta
CREATION DATE : 2009.09.05
PROGRAM DESC. : 특정 일자 SEQ 를 조회하여 NEXTVAL RETURN
CONDITION :
* SEQUENCE NAME FORMAT : TABLE_NAME || DD || 'S'
( LIKE 'T_JOB_BASE_01S' )
------------------------------------------------------------------------
e.g)
DECLARE
P_OUT_VAL VARCHAR2(100);
P_SQLCODE INTEGER;
P_SQLMSG VARCHAR2(200);
BEGIN
SP_GET_DAY_SEQ( 'T_JOB_BASE', '20090905', P_OUT_VAL, P_SQLCODE, P_SQLMSG);
DBMS_OUTPUT.PUT_LINE('P_OUT_VAL:' || P_OUT_VAL );
END;
==> return value is like '2009090500000014'
--***********************************************************************/
IS
V_SEQ_NM VARCHAR2(40);
V_SEQ_LEN NUMBER(2);
V_SQL VARCHAR2(200);
V_NEXT_SEQ NUMBER(13);
V_CURSOR NUMBER;
V_EXEC INTEGER;
V_OUT_VAL VARCHAR2(30);
BEGIN
V_OUT_VAL := '';
P_OUT_VAL := '';
-- GET SEQUENCE NAME
BEGIN
/*
SELECT
FROM ALL_SEQUENCES
WHERE 0 = 0
AND SEQUENCE_NAME LIKE 'T_JOB_BASE' || '%'
AND SUBSTR(SEQUENCE_NAME, LENGTH(SEQUENCE_NAME)-2,2) = SUBSTR('20090905', LENGTH('20090905')-1,2)
*/
SELECT SEQUENCE_NAME, LENGTH(MAX_VALUE)
INTO V_SEQ_NM, V_SEQ_LEN
FROM ALL_SEQUENCES
WHERE 0 = 0
AND SEQUENCE_NAME LIKE P_SEQ_NM || '%'
AND SUBSTR(SEQUENCE_NAME, LENGTH(SEQUENCE_NAME)-2,2) = SUBSTR(P_YYYYMMDD, LENGTH(P_YYYYMMDD)-1,2);
EXCEPTION WHEN OTHERS THEN
P_SQLCODE := SQLCODE;
P_SQLMSG := SUBSTR(SQLERRM,1,200);
RETURN;
END;
-- GET NEXT VAL OF SEQUENCE
BEGIN
V_SQL := 'SELECT ' || V_SEQ_NM || '.NEXTVAL FROM DUAL';
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(V_CURSOR,V_SQL,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(V_CURSOR,1,V_NEXT_SEQ);
V_EXEC := DBMS_SQL.EXECUTE(V_CURSOR);
IF DBMS_SQL.FETCH_ROWS(V_CURSOR) > 0 THEN
DBMS_SQL.COLUMN_VALUE(V_CURSOR,1,V_NEXT_SEQ);
END IF;
EXCEPTION WHEN OTHERS THEN
P_SQLCODE := SQLCODE;
P_SQLMSG := SUBSTR(SQLERRM,1,200);
RETURN;
END;
-- REMAKE VALUE USING SEQ.NEXTVAL
BEGIN
IF LENGTH(V_NEXT_SEQ) > 0 THEN
V_OUT_VAL := P_YYYYMMDD || LPAD(V_NEXT_SEQ, V_SEQ_LEN, '0');
END IF;
END;
P_OUT_VAL := V_OUT_VAL;
END;
'IT - S/W, Biz > S/W Development' 카테고리의 다른 글
node.js - express > 가상 디렉토리 설정 (0) | 2014.04.29 |
---|---|
html5 canvas 녹화 관련 경험기 (0) | 2014.04.28 |
Oracle JDBC Driver maven 디펜던시에 추가하기 [펌] (0) | 2014.04.24 |
표준프레임워크 2.7 64bit 세팅 - HelloWorld 프로젝트까지 (0) | 2014.04.15 |
Spring 4.0 기반 개발환경 세팅 (Spring 4.0.2, Sts 3.4.0, Tomcat 8.0) (0) | 2014.03.19 |