如何将游标的SELECT查询转换为在PL/SQL存储过程中完全运行？

Question

我在Oracle 12中有一个缓慢的存储过程，我想看看是否可以进行优化。它以输出游标的形式返回多个查询结果。我想我应该使用Allround /SQL Developer中的profile工具来分析这个过程，但是我意识到执行这个过程并不会计算(迭代)输出游标，所以我的概要文件不会给我带来任何有趣的东西。

如何修改我的存储过程以简单地完全评估游标？在Server中，过程中可能只包含裸露的SELECT语句，但看起来在PL/SQL过程中这是不可能的。如何修改游标的SELECT查询的PL/SQL以全面评估PL/SQL存储过程中的SELECT，以便全面分析该过程？

我认为我正在与Oracle范式(而不是Server )进行斗争，所以如果我认为这是错误的，我很乐意听到它。

    CREATE OR REPLACE PROCEDURE sp_my_proc(
        /* ... */
        out_my_cursor OUT SYS_REFCURSOR) IS 
    BEGIN 
        /* ... */
        OPEN out_my_cursor FOR
        SELECT ... FROM ... ; -- I want to profile this whole proc including this SELECT
    END sp_my_proc;

Answer 1

这里是一个最小的示例，您如何在PL/SQL中从返回的游标中获取所有行。

添加您的输入参数和游标中返回的附加列

CREATE OR REPLACE PROCEDURE sp_my_proc(
    out_my_cursor OUT SYS_REFCURSOR) IS 
BEGIN 
    OPEN out_my_cursor FOR
    SELECT rownum ID FROM dual connect by level <= 5; 
END sp_my_proc;
/    


declare
cv_out sys_refcursor; 
--  
v_id   NUMBER;
begin
         sp_my_proc(cv_out);
         /* to read the cursor use fetch in a loop */
         LOOP
            FETCH cv_out INTO v_id;
            EXIT WHEN cv_out%NOTFOUND;
            dbms_output.put_line( 'ID= '|| v_ID   ); 
        END LOOP;       
end;
/  

在从游标中获取行时，主要的困难是在FETCH INTO 子句中定义变量.。

Oracle提供了两个选项来简化此任务。

如果光标列与表或视图对应1:1，则可以使用名称%ROWTYPE

示例

v_cur   tab%ROWTYPE;
BEGIN 
    OPEN out_my_cursor FOR
    SELECT col1, col2 FROM tab;  /* tab has two columns */

更多的话，你需要一般情况下的光标%ROWTYPE

但不幸的是，这不能与动态游标一起使用，所以您必须明确声明游标(从过程中复制它)。

declare
 CURSOR cur is
    SELECT col1, col2 FROM tab;  /* tab has more than two columns */
 v_cur cur%ROWTYPE;
begin
         OPEN cur;
         LOOP
            FETCH cur INTO v_cur;
            EXIT WHEN cur%NOTFOUND;
             dbms_output.put_line( 'col1= '|| v_cur.col1   );
         END LOOP;       
end;
/