DB2 产生顺序号存储过程

1.首先创建一个临时表

TEMPVOUCHERNO

字段 MY_ID VARCHAR(7)

2.用Toad来创建存储过程 具体按照如下图所示

值得注意地方：

1.要选择MODIFIES SQL DATA

2.在转化时候 “right( digits(cast(RIGHT(NEW_ID,7) as INTEGER)+1),7);”

具体代码：

BEGIN DECLARE NUM VARCHAR(7); SET NEW_ID=NULL; SELECT MY_ID into NEW_ID FROM TEMPVOUCHERNO order by my_id desc fetch first 1 rows only; IF NEW_ID IS NULL THEN SET NEW_ID='0000001'; ELSE SET NEW_ID=right( digits(cast(RIGHT(NEW_ID,7) as INTEGER)+1),7); END IF; insert into TEMPVOUCHERNO(MY_ID) values (NEW_ID); END