Oracle初级存储过程的游标参数常规及动态调用。

使用Execute Immediate动态游标

create or replace procedure get_cur(v_cur in sys_refcursor)
    as
    v_rec user_objects%rowtype;
    begin
     fetch v_cur into v_rec;
     dbms_output.put_line(v_rec.object_name);
    end;
create or replace procedure
  call_proc_ref_cursor as
  v_cur sys_refcursor;
begin
  open v_cur for
    select * from user_objects;
  execute immediate 'begin get_cur(:1);end;'
    USING v_cur;
  close v_cur;
end;

平常动态游标的调用

CREATE OR REPLACE PACKAGE pkg_test
AS
PROCEDURE p1;
PROCEDURE p2(p_cur SYS_REFCURSOR);
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE p1
AS
  lv_cur SYS_REFCURSOR;
BEGIN
  OPEN lv_cur FOR SELECT * FROM T;
  p2(lv_cur);
  CLOSE lv_cur;
END;
PROCEDURE p2(p_cur SYS_REFCURSOR)
AS
  lv_row t%ROWTYPE;
BEGIN
  LOOP
     FETCH p_cur INTO lv_row;
     EXIT WHEN p_cur%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(lv_row.id);
  END LOOP;
END;
END pkg_test;
/