本文介绍了PL / SQL打印出由存储过程返回的引用游标的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何从存储过程(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打印出由存储过程返回的引用游标的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!