Sqlplus中调试带cursor的存储过程--打印文章

Sqlplus中调试带cursor的存储过程

作者：武汉SEO闵涛文章来源：敏韬网点击数1126 更新时间：2007/11/14 12:53:48 文章录入：mintao 责任编辑：mintao

存储过程
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