如何从IBMDB2中的存储过程返回临时表

Question

我需要在IBM DB2中创建存储过程，在其中我们必须创建临时表，并向该临时表插入和删除一些值，然后返回临时表。

IBM DB2中的存储过程出现以下错误

DB2ADMIN。GETRORDS-部署已启动。Create存储过程返回SQLCODE:-104，SQLSTATE:42601。DB2ADMIN。GETRORDS:39:一个意外的令牌"在"&"；。预期的令牌可以包括："。。SQLCODE=-104，SQLSTATE=42601，DRIVER=4.19.56一个意想不到的令牌"在"&"；。预期的令牌可以包括："。。SQLCODE=-104，SQLSTATE=42601，DRIVER=4.19.56DB2ADMIN。GETRORDS-部署失败。DB2ADMIN。GETRORDS-回滚成功完成。

下面是IBM DB2中的存储过程

CREATE OR REPLACE PROCEDURE GETRECORDS ()
DYNAMIC RESULT SETS 1

P1：开始

-- temporary table declared 
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPRECORDDETAILS
(Column1 NVARCHAR(50) NOT NULL,
Column2 NVARCHAR(50) NOT NULL,
Column3 NVARCHAR(50) NOT NULL,
Column4 NVARCHAR(255) NOT NULL,
Column5 FLOAT ,
Column6 FLOAT ,
Column7 NVARCHAR(50) NOT NULL,
Column8 FLOAT ,
Column9 FLOAT ,
Column10 NVARCHAR(255) NOT NULL
);
-- inserted data in temporary table
INSERT INTO SESSION.TEMPRECORDDETAILS 
SELECT  VM.Column1,
VM.Column2,
VM.Column3,
VM.Column4,
VM.Column5,
VM.Column6,
VM.Column7,
VM.Column8,
VM.Column9,TRIM(VM.Column1) + TRIM(VM.Column2) + TRIM(VM.Column3)
FROM    (SELECT * FROM  RECORDUNITS )
AS VM       
-- deleted data from temporary table
DELETE FROM  SESSION.TEMPRECORDDETAILS 
WHERE TRIM(Column10) IN(SELECT TRIM(Column1) + TRIM(Column2) + TRIM(Column3) 
FROM OLDRECORDS);
-- trying to return temporary table
DECLARE entCursor1 CURSOR WITH RETURN FOR
SELECT Column1, 
Column2,
Column3,
Column4,
Column5,
Column6,
Column7,
Column8,
Column9
FROM SESSION.TEMPRECORDDETAILS ORDER BY Column1;
OPEN entCursor1;

结束P1

Answer 1

您需要遵守复合SQL编译块中语句顺序的规则。游标声明必须位于其他变量声明之后和其他可执行语句之前。

DGTT可以稍后定义，游标(在DGTT之前声明但未定义(可以稍后定义并在DGTT填充后准备。

在本例中，您可以通过使用从RECORDUNITS返回的查询来完全避免DGTT。。。除了。。。旧记录。但是，这个答案显示了一种处理DGTT游标的方法。

CREATE OR REPLACE PROCEDURE GETRECORDS ()
DYNAMIC RESULT SETS 1
P1: BEGIN
declare v_cursor_text varchar(1024);
DECLARE entCursor1 CURSOR WITH RETURN FOR Statement1;
-- temporary table declared 
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPRECORDDETAILS
(Column1 NVARCHAR(50) NOT NULL,
Column2 NVARCHAR(50) NOT NULL,
Column3 NVARCHAR(50) NOT NULL,
Column4 NVARCHAR(255) NOT NULL,
Column5 FLOAT ,
Column6 FLOAT ,
Column7 NVARCHAR(50) NOT NULL,
Column8 FLOAT ,
Column9 FLOAT ,
Column10 NVARCHAR(255) NOT NULL
) with replace on commit preserve rows not logged ;
-- inserted data in temporary table
INSERT INTO SESSION.TEMPRECORDDETAILS
SELECT  VM.Column1,
VM.Column2,
VM.Column3,
VM.Column4,
VM.Column5,
VM.Column6,
VM.Column7,
VM.Column8,
VM.Column9,TRIM(VM.Column1) + TRIM(VM.Column2) + TRIM(VM.Column3)
FROM    (SELECT * FROM  RECORDUNITS ) AS VM;
-- deleted data from temporary table
DELETE FROM  SESSION.TEMPRECORDDETAILS
WHERE TRIM(Column10) IN(SELECT TRIM(Column1) + TRIM(Column2) + TRIM(Column3)
FROM OLDRECORDS);
set v_cursor_text = 'SELECT Column1, 
Column2,
Column3,
Column4,
Column5,
Column6,
Column7,
Column8,
Column9
FROM SESSION.TEMPRECORDDETAILS ORDER BY Column1' ;
prepare Statement1 from v_cursor_text;

OPEN entCursor1;
END P1
@