当前位置：文江博客话题详情

Oracle SQL Developer：如何查看引用游标的结果？

发布于 2024-07-26 22:12:36 字数 92 浏览 6 评论 0原文

如果我有一个返回查询引用游标的函数，我如何在 SQL Developer 中查看其结果集？ Toad 有一个特殊的选项卡，用于查看参考光标的结果，这是我想要找到的功能。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜是你 2024-08-02 22:12:36

SET SERVEROUTPUT ON;
VARIABLE X REFCURSOR;
EXEC PROCEDURE_WITH_OUTPUT_SYS_REFCURSOR(:X);
PRINT X;

SET SERVEROUTPUT ON;
VARIABLE X REFCURSOR;
EXEC PROCEDURE_WITH_OUTPUT_SYS_REFCURSOR(:X);
PRINT X;

回复收藏 0 原文

亂 2024-08-02 22:12:36

双击结果记录中的光标字段。右侧有一个“...”图标。点击这个就可以看到内容了

回复收藏 0 原文

岁月静好 2024-08-02 22:12:36

嗨，我知道这是不久前被问到的，但我刚刚弄清楚了这一点，它可能对其他人有帮助。不确定这是否正是您正在寻找的内容，但这就是我调用存储过程并在 SQL Developer 中查看输出的方式。
在 SQL Developer 中查看过程时，右键单击并选择“运行”或选择 Ctrl+F11 打开“运行 PL/SQL”窗口。这将创建一个包含您需要修改的输入和输出参数的模板。要返回 sys_refcursor 的结果，您需要声明一个与 proc 返回的 select stmt / sys_refcursor 完全等效的行类型。下面我声明与我的输出字段匹配的“type t_row”，然后循环返回的 sys_refcursor。如果 t_row 与我的 sys_refcursor 匹配，则它会填充 sys_refcursor 的每一行：

DECLARE
  P_CAE_SEC_ID_N NUMBER;
  P_FM_SEC_CODE_C VARCHAR2(200);
  P_PAGE_INDEX NUMBER;
  P_PAGE_SIZE NUMBER;
  v_Return sys_refcursor;
  type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),rownum number, v_total_count number);
  v_rec t_row;

BEGIN
  P_CAE_SEC_ID_N := NULL;
  P_FM_SEC_CODE_C := NULL;
  P_PAGE_INDEX := 0;
  P_PAGE_SIZE := 25;

  CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY(
    P_CAE_SEC_ID_N => P_CAE_SEC_ID_N,
    P_FM_SEC_CODE_C => P_FM_SEC_CODE_C,
    P_PAGE_INDEX => P_PAGE_INDEX,
    P_PAGE_SIZE => P_PAGE_SIZE,
    P_FOF_SEC_REFCUR => v_Return
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('P_FOF_SEC_REFCUR = ');
  loop
    fetch v_Return into v_rec;
    exit when v_Return%notfound;
    DBMS_OUTPUT.PUT_LINE('sec_id = ' || v_rec.CAE_SEC_ID || 'sec code = ' ||v_rec.FM_SEC_CODE);
  end loop;

END;

Hi I know this was asked a while ago but I've just figured this out and it might help someone else. Not sure if this is exactly what you're looking for but this is how I call a stored proc and view the output in SQL Developer.
In SQL Developer when viewing the proc, right click and choose 'Run' or select Ctrl+F11 to bring up the Run PL/SQL window. This creates a template with the input and output params which you need to modify. To return the results of a sys_refcursor you then need to declare a row type that is exactly equivalent to the select stmt / sys_refcursor being returned by the proc. Below I declare "type t_row" which matches my output fields, then loop through the returned sys_refcursor. If t_row matches my sys_refcursor then it gets populated with each row of the sys_refcursor:

DECLARE
  P_CAE_SEC_ID_N NUMBER;
  P_FM_SEC_CODE_C VARCHAR2(200);
  P_PAGE_INDEX NUMBER;
  P_PAGE_SIZE NUMBER;
  v_Return sys_refcursor;
  type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),rownum number, v_total_count number);
  v_rec t_row;

BEGIN
  P_CAE_SEC_ID_N := NULL;
  P_FM_SEC_CODE_C := NULL;
  P_PAGE_INDEX := 0;
  P_PAGE_SIZE := 25;

  CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY(
    P_CAE_SEC_ID_N => P_CAE_SEC_ID_N,
    P_FM_SEC_CODE_C => P_FM_SEC_CODE_C,
    P_PAGE_INDEX => P_PAGE_INDEX,
    P_PAGE_SIZE => P_PAGE_SIZE,
    P_FOF_SEC_REFCUR => v_Return
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('P_FOF_SEC_REFCUR = ');
  loop
    fetch v_Return into v_rec;
    exit when v_Return%notfound;
    DBMS_OUTPUT.PUT_LINE('sec_id = ' || v_rec.CAE_SEC_ID || 'sec code = ' ||v_rec.FM_SEC_CODE);
  end loop;

END;

回复收藏 0 原文