mysql 存储过程 动态sql 游标 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;    
/