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
      

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18