存储过程调试过程用的表
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;