DB2存储过程--创建临时表，返回临时表集合
Code1 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 CURSORWITH 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 SETov_retmsg = '执行成功' ;
47 END
页:
[1]