oracle PL/SQL 打印出存储过程返回的引用游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5822241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL print out ref cursor returned by a stored procedure
提问by elpisu
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?
如何从存储过程（OUT 变量）返回的引用游标中获取数据并将结果行打印到 SQL*PLUS 中的 STDOUT？
ORACLE stored procedure:
ORACLE 存储过程：
PROCEDURE GetGrantListByPI(p_firstname IN VARCHAR2, p_lastname IN VARCHAR2,
p_orderby IN VARCHAR2, p_cursor OUT grantcur);
PL/SQL:
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;
/
回答by DCookie
Note: This code is untested
注意：此代码未经测试
Define a record for your refCursor return type, call it rec. For example:
为您的 refCursor 返回类型定义一条记录，将其命名为 rec。例如：
TYPE MyRec IS RECORD (col1 VARCHAR2(10), col2 VARCHAR2(20), ...); --define the record
rec MyRec; -- instantiate the record
Once you have the refcursor returned from your procedure, you can add the following code where your comments are now:
从过程中返回 refcursor 后，您可以在现在的注释位置添加以下代码：
LOOP
FETCH refCursor INTO rec;
EXIT WHEN refCursor%NOTFOUND;
dbms_output.put_line(rec.col1||','||rec.col2||','||...);
END LOOP;
回答by Dave Costa
You can use a bind variable at the SQLPlus level to do this. Of course you have little control over the formatting of the output.
您可以在 SQLPlus 级别使用绑定变量来执行此操作。当然，您几乎无法控制输出的格式。
VAR x REFCURSOR;
EXEC GetGrantListByPI(args, :x);
PRINT x;
回答by akzhere
If you want to print all the columns in your select clause you can go with the autoprint command.
如果要打印 select 子句中的所有列，可以使用 autoprint 命令。
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;
Hope this gives you an alternate solution
希望这可以为您提供替代解决方案
