PL / SQL打印出由存储过程返回的引用游标 [英] PL/SQL print out ref cursor returned by a stored procedure

问题描述

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

How can I fetch from a ref cursor that is returned from a stored procedure (OUT variable) and print the resulting rows to STDOUT in SQL*PLUS?

ORACLE存储过程：

ORACLE stored procedure:

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;
/

推荐答案

注意：

为您的refCursor返回类型定义一个记录，调用它。例如：

Define a record for your refCursor return type, call it rec. For example:

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

从过程返回refcursor后，您可以添加以下代码现在您的意见是：

Once you have the refcursor returned from your procedure, you can add the following code where your comments are now:

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

这篇关于PL / SQL打印出由存储过程返回的引用游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！