存储过程调试_1

存储过程调试过程用的表

create table wxmtestl(vsql1 clob ,vsql2 clob ); 

---

v_sql :='XX';
execute immediate 'truncate table wxmtestl';
insert into wxmtestl(vsql1) values(v_sql);
commit;

open p_cur for v_sql ;或execute immediate v_sql using v_starttime或open i_cursor for v_sql using v_starttime---执行v_starttime过程中用到“dtime >=:starttime”或“dtime >=:1”

--

select * from wxmtestl；

--

create or replace procedure p_bundle_c(
    p_starttime              in varchar2,      --query start time

    p_endtime                in varchar2,      --query end time
    p_projectid              in varchar2,      --query project ID
   i_cursor  out sys_refcursor--游标定义

）

is

v_date      date;

v_starttime      date;

v_endtime        varchar2(100);

v_dtime          varchar2(1000);
v_sql1           clob;
v_sql            varchar2(10000);

v_err            exception;

begin

    case
        when p_starttime is null then
            v_date:= sysdate - 30;
        else
            v_date:= to_date(p_starttime,'yyyy-MM-dd');
    end case;

v_endtime := 'to_date('''||p_endtime||''',''yyyymmdd'')';
v_starttime := 'to_date('''||p_starttime||''',''yyyymmdd'')'; 
v_dtime:= 'to_char(to_date('|| p_starttime||',''yyyy-mm-dd''),''yyyy/mm/dd'') ||''~''|| to_char(to_date('|| p_endtime||',''yyyy-mm-dd''),''yyyy/mm/dd'')';

  v_sql:= ' with basetab as ('||v_sql1||')
              select XXXXX   from basetab
                       ' ;

 open p_cur for v_sql ;

exception
    when others then
      open p_cur for
 select           1 DTIME,
                   cast('err' as varchar2(128)) TEST,
                   cast('err' as varchar2(128)) TESTONE
              from dual;

end p_bundle_c;