Function with DML and Commit

之前寫到一個需求
需要動態產生一組Sequence..
且Sequence不只是流水號
而是有前置字元的流水號
例如CF0703001(XXYYMMSSS)這樣的型態

當用Function來產生這個流水號時
同時要在Table中記錄已用到那一個號碼
這樣的話
Function中就會有Update的DML語法...
在Compile時會發生ORA-01655的Error
表示function不應包括DML的SQL

然而...可以透過PRAGMA AUTONOMOUS_TRANSACTION的宣告
避開此Error
如下

CREATE OR REPLACE
FUNCTION tw_get_cks_seq
( i_seq_type IN varchar2)
RETURN varchar2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
--associated obj: tw_cks_sequence
o_seq_no varchar2(20);
v_check varchar2(20);
BEGIN
select seq_last
into v_check
from tw_cks_sequence
where seq_type=i_seq_type;

if substr(v_check,3,4)=to_char(sysdate,'YYMM') then
--use next
select i_seq_type||to_char(sysdate,'YYMM')||trim(to_char(seq_cur+1,seq_length))
into o_seq_no
from tw_cks_sequence
where seq_type=i_seq_type;


update tw_cks_sequence
set seq_cur=seq_cur+1,
seq_last=o_seq_no,
last_updated_date=sysdate
where seq_type=i_seq_type;

COMMIT;

else
--use 1
select i_seq_type||to_char(sysdate,'YYMM')||trim(to_char(1,seq_length))
into o_seq_no
from tw_cks_sequence
where seq_type=i_seq_type;

update tw_cks_sequence
set seq_cur=1,
seq_last=o_seq_no,
last_updated_date=sysdate
where seq_type=i_seq_type;

COMMIT;
end if;

RETURN o_seq_no ;
EXCEPTION
WHEN others THEN
return SQLCODE||':'||SQLERRM ;
END;
/