DB2 Sequence 생성하는 PB 스크립트

String ls_sequence

String ls_prefix

String ls_len, ls_mod

String ls_value1, ls_value2

String ls_select, ls_get, ls_create, ls_grant

String ls_pk

SELECT ZCCVAL02,     ZCCVAL03,   ZCCVAL04

INTO   :ls_sequence, :ls_prefix, :ls_len

FROM   TLVDTALB.GZCDA121

WHERE  ZCCSYSCD = 'MIS'

AND    ZCCGRPCD = 'ZWK'

AND    ZCCCOMCD = :as_wktp

;

If Trim(ls_sequence) = '' OR Trim(ls_prefix)  = '' OR Trim(ls_len)  = '' Then

Return '오류'

End if

ls_mod = '1' + Fill('0', long(ls_len)) 

// 1. 값 가져올 SQL 문장 생성

ls_get    = " NEXTVAL for " + ls_sequence

ls_select = "SELECT CHAR(Mod( " + ls_get + "," + ls_mod + ") ), CHAR(( " + ls_get + " ) / " + ls_mod + " )  FROM SYSIBM.SYSDUMMY1"

// 2. 시도

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;

PREPARE SQLSA FROM :ls_select ;

OPEN DYNAMIC my_cursor ;

IF sqlca.sqlcode <> 0 Then

   // 3. 존재하지 않을 경우 새로 생성후 값 가져오기

ls_create = "CREATE SEQUENCE " + ls_sequence + " AS NUMERIC (10,0) START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE"

EXECUTE IMMEDIATE :ls_create;

ls_grant = "GRANT ALL ON " + ls_sequence + " TO PUBLIC "

EXECUTE IMMEDIATE :ls_grant;

DECLARE my_cursor1 DYNAMIC CURSOR FOR SQLSA ;

PREPARE SQLSA FROM :ls_select ;

OPEN DYNAMIC my_cursor1 ;

FETCH my_cursor1 INTO :ls_value1, :ls_value2 ;

CLOSE my_cursor1 ;

Else

// 4. 존재할 경우 값 가져오기

FETCH my_cursor INTO :ls_value1, :ls_value2 ;

CLOSE my_cursor ;

End if

ls_value1 = Trim(ls_value1)

ls_value2 = Trim(ls_value2)

If LONG(ls_value2) <> 0 Then

ls_prefix = mid(ls_prefix,1, lenA(ls_prefix) - 1) + char(Asc(Right(ls_prefix,1)) + 1)

End if

ls_pk =  ls_prefix +   Fill('0', long(ls_len) - lenA(ls_value1) ) + ls_value1

Return ls_pk