DB2使用笔记生成存储过程ID
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
CREATE PROCEDURE DB2INST1.CREATEID(OUT OV_RETVAL INTEGER,
2
OUT OV_RETMSG VARCHAR(400),
3
OUT NEWID INTEGER,
4
IN SNTYPE VARCHAR(50))
5
SPECIFIC SQL090829140326600
6
MODIFIES SQL DATA
7
NOT DETERMINISTIC
8
NULL CALL
9
LANGUAGE SQL
10
BEGIN
11
DECLARE v_strState char(5) default ''; --自定义SQL出错状态码\
12
DECLARE SQLCODE integer; --系统SQL出错编码
13
DECLARE SQLSTATE char(5); --系统SQL出错状态码
14
DECLARE v_step integer; ---默认自增量
15
DECLARE NUM integer; ---当前最大值
16
17
DECLARE NOWDATE VARCHAR(50); ---获取时间
18
DECLARE V_TYPE VARCHAR(50); ---输入的类型
19
--异常处理
20
DECLARE EXIT HANDLER FOR SQLEXCEPTION
21
BEGIN
22
VALUES(SQLCODE ,SQLSTATE) INTO ov_retval , v_strState;
23
SET ov_retmsg = ov_retmsg || '处理失败,其中SQLSTATE:'||v_strState;
24
END;
25
--固定变量赋初值
26
SET ov_retval=0; --返回代码
27
SET ov_retmsg=''; --返回信息
28
29
SET v_step =1; --默认为自增1
30
SET V_TYPE = SNTYPE; ---设置输入的类型
31
----对不存在的类型
32
if not exists(select * from serviceid where sntype=V_TYPE) then
33
---加入这条记录
34
-- set NOWDATE = (select current timestamp from sysibm.sysdummy1); ---得到当前时间
35
insert into SERVICEID(SNTYPE,MAXNUM,STEP) VALUES(V_TYPE,1,v_step);
36
SET newID=1; ---设置新ID为1
37
----对已存在的类型,返回当前值加步长
38
else
39
-- set NOWDATE = (select current timestamp from sysibm.sysdummy1); ---得到当前时间
40
set NUM = (select MAXNUM from SERVICEID WHERE SNTYPE=V_TYPE)+1; ---查询出当前最大值
41
42
update SERVICEID SET MAXNUM=NUM WHERE SNTYPE=V_TYPE ;
43
SET newID = NUM;
44
end if;
45
46
SET ov_retmsg = '执行成功' ;
47
END
原文地址:https://www.cnblogs.com/bayker/p/1556356.html