我对oracle数据库存储过程很陌生,所以如果我问的是非常简单或基本的问题,请原谅我。
我想遍历使用游标在过程中生成的内部连接结果集。
我创建了一个过程,其中包含一个简单的SQL选择查询拖曳表和两个表中的选择字段。
CREATE OR REPLACE PROCEDURE demoprocedure(crsr out SYS_REFCURSOR)
AS
BEGIN
OPEN crsr FOR
SELECT
TABLE1.field1, TABLE2.field2, TABLE1.fields11, TABLE2.field22
FROM
TABLE1 INNER JOIN
TABLE2
ON
TABLE1.field12 = TABLE2.field12
END demoprocedure;
我想使用游标遍历这个结果集,这是我尝试过的
DECLARE
crsr SYS_REFCURSOR;
temp SYS_REFCURSOR;
BEGIN
demoprocedure(crsr);
LOOP
FETCH crsr INTO temp;
EXIT WHEN crsr%NOTFOUND;
Dbms_Output.Put_Line('Fields1 is : ' || temp.field1);
END LOOP;
CLOSE crsr;
END;
但这不起作用,好吧,我已经尝试创建自定义对象,但也给出了错误。
预期成果:
Fields1 is : 1
Fields1 is : 2
Fields1 is : 3
问题在于临时变量的声明。您可以在局部变量中获取SYS_REFCURSOR
的内容,然后将其显示如下:
程序:
SQL> CREATE OR REPLACE PROCEDURE DEMOPROCEDURE ( 2 CRSR OUT SYS_REFCURSOR 3 ) AS 4 BEGIN 5 OPEN CRSR FOR SELECT 6 'ABC' AS COL1, 7 'BCD' AS COL2, 8 'CDE' AS COL3, 9 'DEF' AS COL4 10 FROM 11 DUAL; 12 13 END DEMOPROCEDURE; 14 / Procedure created.
测试它(循环访问它(
SQL> SET SERVEROUT ON; SQL> DECLARE 2 CRSR SYS_REFCURSOR; 3 V_COL1 VARCHAR2(100); 4 V_COL2 VARCHAR2(100); 5 V_COL3 VARCHAR2(100); 6 V_COL4 VARCHAR2(100); 7 BEGIN 8 DEMOPROCEDURE(CRSR); 9 LOOP 10 FETCH CRSR INTO 11 V_COL1, 12 V_COL2, 13 V_COL3, 14 V_COL4; 15 EXIT WHEN CRSR%NOTFOUND; 16 DBMS_OUTPUT.PUT_LINE('Fields1 is : ' || V_COL1); 17 DBMS_OUTPUT.PUT_LINE('Fields2 is : ' || V_COL2); 18 DBMS_OUTPUT.PUT_LINE('Fields3 is : ' || V_COL3); 19 DBMS_OUTPUT.PUT_LINE('Fields4 is : ' || V_COL4); 20 END LOOP; 21 22 CLOSE CRSR; 23 END; 24 / Fields1 is : ABC Fields2 is : BCD Fields3 is : CDE Fields4 is : DEF PL/SQL procedure successfully completed. SQL>
干杯!!