oracle – PL/SQL打印出由存储过程返回的参考游标
发布时间:2020-12-12 13:57:38 所属栏目:百科 来源:网络整理
导读:如何从存储过程返回的引用游标(OUT变量)中获取并将结果行打印到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 SERVE
如何从存储过程返回的引用游标(OUT变量)中获取并将结果行打印到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; /注意:此代码未经测试 定义您的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; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |