--存储过程
create or replace procedure prc_cl_szyl_shbx_02(p_date in varchar2,
p_nreturn out number)
AUTHID CURRENT_USER IS

v_task_pos varchar2(256);
v_sqlerrm varchar2(256);
v_sqlerrcode varchar2(256);
v_sp_name varchar2(50);
v_sql varchar2(32767);
v_tablename varchar2(50);
v_flag number;
v_no number;
v_gzm varchar2(10);

begin
v_task_pos := '程序运行开始';
v_sp_name := 'prc_cl_01_check_date';
--sp_record_log(v_serial_no,v_sp_name,sysdate,v_task_pos,'');
-- 设置初始成功标志
p_nreturn := 200;
v_no := 1;

v_tablename := 'SHFFJL_P1';
v_gzm :='P1';
v_sql := 'create table ' || v_tablename ||
'as select s.jsq,s.dwm,s.bmdm,s.tbr,s.xm,s.shbzh,min(jsq)as jsq,t.dwm,t.tyymd,t.txymd
from shffjl s left join txda t
on s.tbr = t.tbr
group by s.jsq,s.dwm,s.bmdm,s.tbr,s.xm,s.shbzh,jsq,t.dwm,t.tyymd,t.txymd
having s.dwm =t.dwm and s.tbr =s.tbr and s.jsq < nvl(t.tyymd,t.txymd)';
v_no := v_no + 1;
delete from qx_log_ywlj_wt;
select count()
into v_flag
from user_tables
where table_name = v_tablename;
/if v_flag = 0 then
execute immediate v_sql;
else
execute immediate 'drop table ' || v_tablename || '';
execute immediate v_sql;
end if;*/
execute immediate v_sql;
insert into qx_log_ywlj_wt
(yno, ytablename, gzm)
values
(v_no, v_tablename, v_gzm);

commit;
v_task_pos := '程序运行成功';
exception
when others then
rollback;
v_task_pos := '程序运行失败';
v_sqlerrm := substr(sqlerrm, 0, 800);
dbms_output.put_line(v_sqlerrm);
v_sqlerrcode := sqlcode;
-- sp_record_log(v_serial_no,v_sp_name,sysdate,v_task_pos,v_sqlerrm||v_sqlerrcode);
p_nreturn := v_no;

END prc_cl_szyl_shbx_02;

--测试页面
-- Created on 2020/1/2 by ADMINISTRATOR
declare
-- Local variables here
i integer;
pdate integer;
pnreturn integer;
begin
-- Test statements here
pdate :=0;
prc_cl_szyl_shbx_02(p_date => pdate,p_nreturn => pnreturn);
end;

--报警信息
ORA-00922: 选项缺失或无效