|
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出錯(cuò)狀態(tài)碼\ 11 DECLARE SQLCODE integer; --系統(tǒng)SQL出錯(cuò)編碼 12 DECLARE SQLSTATE char(5); --系統(tǒng)SQL出錯(cuò)狀態(tài)碼 13 DECLARE V_time integer; --統(tǒng)計(jì) 14 15 -----定義游標(biāo)--- 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 ----定義臨時(shí)表 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 ----定義返回游標(biāo) 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 --打開游標(biāo)- 44 open R_CRSR; 45 END P2; 46 SET ov_retmsg = '執(zhí)行成功' ; 47 END |
|
|
