如何在过程中返回数据集

Question

我有这样的过程：

CREATE OR REPLACE PROCEDURE RIC.TEST 
(P_VAR1 IN VARCHAR2, P_VAR2 IN VARCHAR2, P_VAR3 IN VARCHAR2, P_VAR4 IN VARCHAR2)
IS 
L_RC SYS_REFCURSOR;
BEGIN
INSERT INTO RIC.TEMP_TABLE
    SELECT * FROM RIC.TABLE WHERE COL1=P_VAR1 AND COL2=P_VAR2 AND COL3=P_VAR3 AND COL4=P_VAR4 AND ...;
OPEN L_RC
FOR 'SELECT .... FROM RIC.TEMP_TABLE WHERE ...';
...

temp_table是一个临时表。当我运行此过程时，我需要返回并显示第二个选择输出(从ric.temp_table中选择.... tem.temp_table ...(。我怎样才能做到这一点？光标是错误的吗？

谢谢。

Answer 1

将过程修改为

CREATE OR REPLACE PROCEDURE ric.test (p_var1     IN     VARCHAR2,
                                      p_var2     IN     VARCHAR2,
                                      p_var3     IN     VARCHAR2,
                                      p_var4     IN     VARCHAR2,
                                      p_cursor      OUT SYS_REFCURSOR)
IS
BEGIN
   INSERT INTO ric.temp_table
      SELECT *
        FROM ric.table
       WHERE col1 = p_var1 AND col2 = p_var2 AND col3 = p_var3;
   OPEN p_cursor FOR SELECT * FROM ric.temp_table;
END;
/

并执行该过程为

VARIABLE cur REFCURSOR;
EXEC test (:cur);
PRINT cur;

如果您想使用SQL执行该过程，请将过程包装在函数中，该函数应在SQL语句中调用该函数。示例