打印Oracle PLSQL OUT变量 [英] Print Oracle PLSQL OUT variable

问题描述

create or replace package demo_pckg 
as
 type cursor_type is ref cursor;
 procedure demo_proc(i_deptno number,o_result out cursor_type);
end;
/


create or  replace package body demo_pckg
as
procedure demo_proc(i_deptno number,o_result out cursor_type)
as
 begin
    open o_result for
    select * from employees 
    where department_id=i_deptno;
    end;
end;
/

如果要打印OUT游标变量，该怎么办?

What should I do in case I want to print that OUT cursor variable ?

推荐答案

从SQL * Plus或SQL Developer获得的最简单方法是使用

The simplest way from SQL*Plus or SQL Developer is with variable and print:

var deptno number
var result refcursor
exec :deptno := 10;
exec demo_pckg.demo_proc(:deptno, :result);
print result

或者:

var result refcursor
declare
  deptno emplyees.department_id%type;;
begin
  deptno := 10;
  demo_pckg.demo_proc(deptno, :result);
end;
/
print result

result在过程调用中被视为绑定变量，因此在其中以:为前缀，但是它是SQL * Plus的本机变量，因此对于print调用没有一个.您可以在SQL * Plus中运行，也可以在SQL Developer中作为脚本运行，这将在脚本输出"窗口中显示结果.

The result is treated as a bind variable in the procedure call, so it's prefixed with : there, but it's a native variable to SQL*Plus so it doesn't have one for the print call. You can run either in SQL*Plus, or as a script in SQL Developer, which will show the results in the Script Output window.

当然，在任何一种情况下，您都可以在过程调用中对deptno值进行硬编码，而不必研究变量来保存它.

You can hard-code the deptno value in the procedure call in either case, of course, rather than delcaring a variable to hold that.

如果您是通过Java或其他客户端程序调用此函数，则可以将OUT游标与其他任何结果集一样对待.

If you're calling this from Java or some other client program you can treat your OUT cursor like any other result set.

您还可以在过程中将result声明为sys_refcursor，而不是声明自己的类型.

You can also declare result as a sys_refcursor in your procedure, rather than declaring your own type.

这篇关于打印Oracle PLSQL OUT变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！