orcle 根据时间 动态删除原表数据，调用动态创建表函数。插入新表数据

create or replace procedure exportnew(ddate string) authid current_user is

  newname   varchar2(30);

  strsql varchar2(2000);

  cnt       int;

Begin

 

  select count(*)

    into cnt

    from hy_sczb_data

   where v_lrsj like '%' || ddate || '%';

 

  if cnt = 0 then

    --没有结果时不处理

   Dbms_output.Put_line('不处理');

 

  Else

    creat_sczb_data(ddate); -- 创建新表 表名为hy_sczb_data_16_08

     newname := 'sczb_data_' || replace(ddate, '-', '_'); --新表名

 

     strsql := 'Insert into ' ||newname|| ' select * fromhy_sczb_data where v_lrsj like :key';

     execute immediate strsql

     using '%' || ddate || '%';

 

     strsql := 'DELETE FROM hy_sczb_data WHERE  v_lrsj like :key';

     execute immediate strsql

     using '%' || ddate || '%';

    

     strsql := 'DELETE FROM hy_srzb_data WHERE  v_lrsj like :key';

     execute immediate strsql

     using '%' || ddate || '%';

 

  End if;

End exportnew;