Powerbuilder2010. 6. 21. 20:57









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
Posted by Julyus