Sqlplus中调试带cursor的存储过程

存储过程 create or replace package pkg_test as
  type t_cursor is ref cursor;
  procedure sp_get_catalogs(p_userid in number, cur_catalogs out t_cursor);
end; create or replace package body pkg_test as
  procedure sp_get_catalogs(p_userid in number, cur_catalogs out t_cursor) is
    v_sql varchar2(500);
  begin
    if p_userid = 0 then
      open cur_catalogs for
        select userid, loginid, name from tuser;
    else
      v_sql := 'select userid,loginid,name from tuser where userid=:w_userid';
      open cur_catalogs for v_sql
        using p_userid;
    end if;
  exception
    when others then
      dbms_output.put_line('other exception');
  end sp_get_catalogs;
end pkg_test; Sqlplus调试代码 variable v_cur refcursor;
variable v_userid number;
set autoprint off;
begin
    :v_userid := -9222229;
end;
/
execute pkg_test.sp_get_catalogs(:v_userid, :v_cur);
print v_cur; 总结 pl/sql developer 里的Command Window里面输入(Sqlplus调试代码)调试存储过程会出现“refcursor don't supported!“的错误，换到sqlplus里面调试一切正常。 参考文档 SQL*Plus User's Guide and Reference Release 9.2

 

 

本文地址：http://com.8s8s.com/it/it20365.htm