CREATE PROCEDURE DB2INST1.tem_pro(OUT OV_RETVAL INTEGER,
2 OUT OV_RETMSG VARCHAR(400)) 3 4 RESULT SETS 1 5 MODIFIES SQL DATA 6 NOT DETERMINISTIC 7 NULL CALL 8 LANGUAGE SQL 9 BEGIN 10 DECLARE v_strState char(5) default ''; --自定义SQL出错状态码\ 11 DECLARE SQLCODE integer; --系统SQL出错编码 12 DECLARE SQLSTATE char(5); --系统SQL出错状态码 13 DECLARE V_time integer; --统计 14 15 -----定义游标--- 16 --异常处理 17 DECLARE EXIT HANDLER FOR SQLEXCEPTION 18 BEGIN 19 VALUES(SQLCODE ,SQLSTATE) INTO ov_retval , v_strState; 20 SET ov_retmsg = ov_retmsg || '处理失败,其中SQLSTATE:'||v_strState; 21 END; 22 ----定义临时表 23 DECLARE GLOBAL TEMPORARY TABLE session.gbl_temp 24 ( 25 OID INTEGER, 26 NAME VARCHAR(50) 27 )NOT LOGGED WITH REPLACE; 28 29 --固定变量赋初值 30 SET ov_retval=0; --返回代码 31 SET ov_retmsg=''; --返回信息 32 SET V_time=1; 33 p2: BEGIN 34 ----定义返回游标 35 DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FOR 36 SELECT * FROM session.gbl_temp 37 FOR READ ONLY; 38 39 WHILE (V_time < 5) DO 40 SET V_time=V_time+1; 41 INSERT INTO session.gbl_temp values(1,'dd'); 42 END WHILE; 43 --打开游标- 44 open R_CRSR; 45 END P2; 46 SET ov_retmsg = '执行成功' ; 47 END |
|