如何使用SQL Developer显示ref游标的结果 [英] How to display the results of a ref cursor using SQL Developer

问题描述

我有一个带有多个游标的存储过程.它们被定义为IN OUT参数.我想使用SQL Developer显示游标的结果. 这是存储过程的一个示例:

I have a stored procedure with several cursors. They are defined as IN OUT parameters. I would like to display the result of the cursors using SQL Developer. This is an example of the stored procedure:

SET serveroutput on;
DECLARE
         p_input_stream       VARCHAR2(200);
        p_msg_code            NUMBER;
         p_msg_parms                         VARCHAR2(200);
         p_return_code                       NUMBER;
         p_trailer_cur                    sl_globals.curtype_weak;

 BEGIN
    /* Assign values to IN parameters */
    p_input_stream := '24954286Mnull|5155035|2|436|SCAN|47720|XTRA|0105||5155035||||N|~|\r';
    p_trailer_cur := null;

EXEC TRAILER_INFO(p_input_stream, 
        p_msg_code, p_msg_parms, p_return_code, 
        p_trailer_cur)

 /* Display OUT parameters */
    dbms_output.put_line('p_msg_code: ' || p_msg_code);
    dbms_output.put_line('p_msg_parms: ' || p_msg_parms);
    dbms_output.put_line('p_return_code: ' || p_return_code);

我试图创建一个refcursor变量，并用它代替p_trailer_cur这样的

I have tried creating a refcursor variable and using it in place of p_trailer_cur like this

VARIABLE trailer_cur refcursor;
 EXEC TRAILER_INFO(p_input_stream, 
        p_msg_code, p_msg_parms, p_return_code, 
        :trailer_cur)
 print trailer_cur;

我得到了错误:

SP2-0552:绑定Varialbe未声明trailer_cur.

SP2-0552: Bind Varialbe "trailer_cur is not declared.

声明了变量，所以我不理解错误.

The variable is declared so I don't understand the error.

推荐答案

SQL Developer支持的两种方式-GUI和代码.

Two ways SQL Developer supports this - the GUI and the Code.

GUI

如果您从代码编辑器中执行存储过程，请在树中找到该存储过程，单击它，然后使用执行"按钮-我们将获取您的所有输出，并将其显示在输出面板下方:

If you execute your stored procedure from the Code Editor, find the stored procedure in the tree, click on it, use the Execute button - we'll grab ALL of your output, and show it below in the output panels:

还有您的尝试，代码:

如果您在SQL工作表中并且具有匿名块，则可以使用F5(包括打印命令)运行它.

If you're in the SQL Worksheet and you have your anonymous block, you can run it with F5, including your print command.

像这样-

create or replace function get_emps(dno in number) return sys_refcursor
    is
      return_value sys_refcursor;
    begin
      open return_value for
        select * from employees where department_id = dno;
      return return_value;
    end;
    /



 var rc refcursor
 exec :rc := get_emps(90)

 print rc

这篇关于如何使用SQL Developer显示ref游标的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！