![](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
|