DB2存储过程无效

fumotvh3 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(269)

我在这里错过了什么，为什么它在上下文中是无效的？

CREATE OR REPLACE PROCEDURE "TEST" ()
LANGUAGE SQL
SPECIFIC SQL220908110735859
BEGIN
 declare c1 cursor for select * from xyzschema.table;
  DECLARE GLOBAL TEMPORARY TABLE abc LIKE xyzschema.table;
  open c1;
  fetch c1 into abc; 
END

错误：{0：0}“abc”在使用它的上下文中无效。SQLCODE= -206，SQLSTATE=42703，DRIVER=4.25.1301

主要任务是通过存储过程复制内容。Current Table

红色数据列已经存在。绿色数据列应该在复制之后建立。这表示应该复制识别码为 1 之数据列的内容。输入参数是 Table_A 的org_id（此处为 1）与tocopy_id（此处为 2），条件是型态应该是 A。
我的解决方案如下所示：

CREATE OR REPLACE PROCEDURE TEST
(IN org_id INT, IN tocopy_id INT)
LANGUAGE SQL
SPECIFIC SQL220909154402272
BEGIN
--copy contents of Table_B
   FOR v AS cur1 CURSOR FOR 
      select * from TABLE_B where Table_A_Ref = org_id JOIN TABLE_A table_a On Table_A_Ref = table_a.id Where table_a.type = 'A'
   DO 
   --Pk is auto generated
   INSERT INTO TABLE_B VALUES (tocopy_id, v.name_1, v.name_2);
   END FOR; 

   --copy contents of Table_C
END

db2

来源：https://stackoverflow.com/questions/73649174/db2-storedprocedure-not-valid

1条答案

按热度按时间

hzbexzde1#

您可以按原样试用。
使用DGTT存储SELECT的结果。

--#SET TERMINATOR @

CREATE OR REPLACE PROCEDURE TEST
BEGIN
  DECLARE GLOBAL TEMPORARY TABLE SESSION.ABC AS 
  (
    SELECT * FROM SYSCAT.SCHEMATA
  ) WITH DATA WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
  -- Do something useful with SESSION.ABC here if needed
END@

CALL TEST@

SELECT * FROM SESSION.ABC@

提取到行变量中。

--#SET TERMINATOR @

SET SERVEROUTPUT ON@

BEGIN
  DECLARE TYPE T_SCHEMATA AS ROW ANCHOR ROW SYSCAT.SCHEMATA;
  DECLARE L_VAR T_SCHEMATA;
  DECLARE SQLSTATE CHAR (5);
  DECLARE C1 CURSOR FOR
    SELECT * FROM SYSCAT.SCHEMATA;
  OPEN C1;

  L1:
  LOOP
     FETCH C1 INTO L_VAR;
     IF SQLSTATE = '02000' THEN LEAVE L1; END IF;
     -- Do something useful with L_VAR here
     CALL DBMS_OUTPUT.PUT_LINE (L_VAR.SCHEMANAME);
  END LOOP L1;
  CLOSE C1;
END@

SET SERVEROUTPUT OFF@

赞(0）回复(0）举报 2022-11-07

我来回答

DB2存储过程无效

1条答案

相关问题

热门标签

最新问答