PL/SQL打印存储过程返回的ref游标

Question

如何从存储过程(OUT变量)返回的ref游标中获取并将结果行打印到SQL*PLUS中的STDOUT ?

ORACLE存储过程:

PROCEDURE GetGrantListByPI(p_firstname IN VARCHAR2, p_lastname IN VARCHAR2,
p_orderby IN VARCHAR2, p_cursor OUT grantcur);

PL/SQL:

SET SERVEROUTPUT ON;
DECLARE
  TYPE r_cursor IS REF CURSOR;
  refCursor r_cursor;
  CURSOR grantCursor IS
    SELECT last_name, first_name
    FROM ten_year_pis
    WHERE year_added = 2010;
  last_name VARCHAR2(100);
  first_name VARCHAR2(100);
BEGIN
  OPEN grantCursor;
  FETCH grantCursor INTO last_name, first_name;
  WHILE grantCursor%FOUND LOOP
    PMAWEB_PKG.GetGrantListByPI(last_name, first_name, 'last_name', refCursor);
    --HOW DO I LOOP THROUGH THE RETURNED REF CURSOR (refCursor)
    --AND PRINT THE RESULTING ROWS TO STDOUT?
    FETCH grantCursor into last_name, first_name;
  END LOOP;
  CLOSE grantCursor;
END;
/

Answer 1

注意:此代码未经测试

为你的refCursor返回类型定义一条记录，命名为rec。

TYPE MyRec IS RECORD (col1 VARCHAR2(10), col2 VARCHAR2(20), ...);  --define the record
rec MyRec;        -- instantiate the record

一旦从过程中返回了refcursor，就可以在注释所在的位置添加以下代码:

LOOP
  FETCH refCursor INTO rec;
  EXIT WHEN refCursor%NOTFOUND;
  dbms_output.put_line(rec.col1||','||rec.col2||','||...);
END LOOP;

Answer 2

可以在SQLPlus级别使用绑定变量来完成此操作。当然，您几乎无法控制输出的格式。

VAR x REFCURSOR;
EXEC GetGrantListByPI(args, :x);
PRINT x;

Answer 3

如果您想打印select子句中的所有列，您可以使用autopprint命令。

CREATE OR REPLACE PROCEDURE sps_detail_dtest(v_refcur OUT sys_refcursor)
AS
BEGIN
  OPEN v_refcur FOR 'select * from dummy_table';
END;
SET autoprint on;
--calling the procedure
VAR vcur refcursor;
DECLARE 
BEGIN
  sps_detail_dtest(vrefcur=>:vcur);
END;

希望这能给你一个替代的解决方案

Answer 4

更简单的选择是使用DBMS_SQL.return_result();

假设你的包/过程/游标规范如下:

    create or replace PACKAGE my_package IS
    
    TYPE my_ref_cursor_type IS REF CURSOR;
    
    PROCEDURE my_procedure (
        p_in_param1     IN     VARCHAR2, 
        p_in_param2     IN     VARCHAR2, 
        p_in_param3     IN     VARCHAR2, 
        p_my_ref_cursor OUT    my_ref_cursor_type,
        p_err_code      OUT    NUMBER,
        p_err_msg       OUT    VARCHAR2   
        );
    
    END my_package;

尝试从sql开发人员工作表

调用过程

SET SERVEROUTPUT ON;
DECLARE
  P_MY_REF_CURSOR my_schema.my_package.my_ref_cursor_type;
  P_ERR_CODE NUMBER;
  P_ERR_MSG VARCHAR2(200);
  
BEGIN
  my_package.my_procedure(
    'VALUE1',
    'VALUE2',
    'VALUE3',
    P_MY_REF_CURSOR => P_MY_REF_CURSOR,
    P_ERR_CODE => P_ERR_CODE,
    P_ERR_MSG => P_ERR_MSG
  );
    DBMS_OUTPUT.PUT_LINE(P_ERR_MSG); 
    DBMS_OUTPUT.PUT_LINE(P_ERR_CODE); 
    DBMS_SQL.return_result(P_MY_REF_CURSOR);
END;

希望对你有帮助!

Answer 5

显示sys_refcursor结果集的方法有很多，其中一个非常简单的方法是使用SQL Developer获取sys_refcursor并打印输出，它们是:

1. 创建一个测试函数来打印其结果
2. 执行函数
3. 查看输出
4. 验证结果集