Sqlplus中调试带cursor的存储过程|中文方案文档站(提供各类方案下载,WORD文档下载,范文,案例等)

存储过程
1. 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;
2. 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调试代码
1. 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;
总结
1. pl/sql developer 里的command window里面输入(sqlplus调试代码)调试存储过程会出现“refcursor don't supported!“的错误，换到sqlplus里面调试一切正常。
参考文档
1. sql*plus user's guide and reference release 9.2

Sqlplus中调试带cursor的存储过程