Sqlplus中调试带cursor的存储过程

1. 存储过程
   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;
2. 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;
3. 总结
   1. pl/sql developer 里的Command Window里面输入(Sqlplus调试代码)调试存储过程会出现“refcursor don't supported!“的错误，换到sqlplus里面调试一切正常。
4. 参考文档
   1. SQL*Plus User's Guide and Reference Release 9.2