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
