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
      

Copyright ＠ 2007 MinTao学以致用网（www.mintao.net） Inc All Rights Reserved.
QQ：543098146 QQ：261561092　QQ：179647303 MSN:min906@126.com
站长：MinTao 信息产业部ICP备案号:鄂ICP备07500065号