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 SQL0908291403266006
MODIFIES SQL DATA7
NOT DETERMINISTIC8
NULL CALL9
LANGUAGE SQL10
BEGIN11
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 SQLEXCEPTION21
BEGIN22
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; --默认为自增130
SET V_TYPE = SNTYPE; ---设置输入的类型31
----对不存在的类型32
if not exists(select * from serviceid where sntype=V_TYPE) then33
---加入这条记录 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为137
----对已存在的类型,返回当前值加步长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

浙公网安备 33010602011771号