declare

  TYPE VARCHAR2_ARRAY IS TABLE OF VARCHAR2(100);

  p_product_codes VARCHAR2_ARRAY;

  

  p_date varchar2(50):='2016-07-13';

  v_product_code       varchar2(50);

  v_product_codes      varchar2(1000);

  v_techinalproduct_codes    varchar2(1000);

  v_date       varchar2(50);

  v_column     varchar2(3000);

  v_sql        varchar2(5000);

  v_insert     varchar2(6000);

  v_plans      varchar2(3000);

  v_delete     varchar2(5000);

  v_sql_delete varchar2(5000);

 

  cursor curdeleteproduct(v_product_code varchar2) is  

      select ' FROM CHANNEL_AUTHORIZE A' as from_name,

             ' WHERE A.ID_MARKETPRODUCT_INFO ='''||v_product_code||''''  as where_name

      from dual

      union all

      select 

             ' FROM PACKAGE_DUTY_ATTR A' as from_name,

             ' WHERE A.ID_PACKAGE_INFO IN (SELECT B.ID_PACKAGE_INFO FROM PACKAGE_INFO B WHERE  B.ID_MARKETPRODUCT_INFO='''||v_product_code||''')'  as where_name

      from dual

      union all

      select 

             ' FROM PACKAGE_PLAN_DUTY_REL A' as from_name,

             ' WHERE A.ID_PACKAGE_INFO IN (SELECT B.ID_PACKAGE_INFO FROM PACKAGE_INFO B WHERE  B.ID_MARKETPRODUCT_INFO='''||v_product_code||''')'  as where_name

      from dual

      union all

      select  ' FROM PACKAGE_INFO A' as from_name,

             ' WHERE A.ID_MARKETPRODUCT_INFO ='''||v_product_code||''''  as where_name

      from dual

      union all

      select  ' FROM MARKETPRODUCT_DEPARTMENT A' as from_name,

             ' WHERE A.ID_MARKETPRODUCT_INFO ='''||v_product_code||''''  as where_name

      from dual

      union all

      select  ' FROM MP_ASSISTANCE_SERVICE A' as from_name,

             ' WHERE A.ID_MARKETPRODUCT_INFO ='''||v_product_code||''''  as where_name

      from dual

      union all

      select  ' FROM MARKETPRODUCT_RULE_PARAM A' as from_name,

             ' WHERE A.ID_MARKETPRODUCT_INFO ='''||v_product_code||''''  as where_name

      from dual

      union all

      select  ' FROM MARKETPRODUCT_PLAN_DUTY_REL A' as from_name,

             ' WHERE A.ID_MARKETPRODUCT_INFO ='''||v_product_code||''''  as where_name

      from dual

      union all

      select  ' FROM MARKETPRODUCT_DUTY_ATTR A' as from_name,

             ' WHERE A.ID_MARKETPRODUCT_INFO ='''||v_product_code||''''  as where_name

      from dual

      union all

      select  ' FROM MARKETPRODUCT_INFO A' as from_name,

             ' WHERE A.ID_MARKETPRODUCT_INFO ='''||v_product_code||''''  as where_name

      from dual

      ;

          

  cursor cur(v_product_code varchar2,v_plan varchar2) is  

      select 'PRODDATA' as owner,

             'MARKETPRODUCT_INFO'  as table_name,

             ' FROM MARKETPRODUCT_INFO A' as from_name,

             ' WHERE  A.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'CHANNEL_AUTHORIZE'  as table_name,

             ' FROM CHANNEL_AUTHORIZE A' as from_name,

             ' WHERE  A.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'MARKETPRODUCT_DUTY_ATTR'  as table_name,

             ' FROM MARKETPRODUCT_DUTY_ATTR A' as from_name,

             ' WHERE  A.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'MARKETPRODUCT_PLAN_DUTY_REL'  as table_name,

             ' FROM MARKETPRODUCT_PLAN_DUTY_REL A' as from_name,

             ' WHERE  A.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'MARKETPRODUCT_RULE_PARAM'  as table_name,

             ' FROM MARKETPRODUCT_RULE_PARAM A' as from_name,

             ' WHERE  A.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'MARKETPRODUCT_DEPARTMENT'  as table_name,

             ' FROM MARKETPRODUCT_DEPARTMENT A' as from_name,

             ' WHERE  A.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'MP_ASSISTANCE_SERVICE'  as table_name,

             ' FROM MP_ASSISTANCE_SERVICE A' as from_name,

             ' WHERE  A.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'PACKAGE_INFO'  as table_name,

             ' FROM PACKAGE_INFO A' as from_name,

             ' WHERE  A.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'PACKAGE_PLAN_DUTY_REL'  as table_name,

             ' FROM PACKAGE_PLAN_DUTY_REL A,PACKAGE_INFO B' as from_name,

             ' WHERE A.ID_PACKAGE_INFO=B.ID_PACKAGE_INFO AND B.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'PACKAGE_DUTY_ATTR'  as table_name,

             ' FROM PACKAGE_DUTY_ATTR A,PACKAGE_INFO B' as from_name,

             ' WHERE A.ID_PACKAGE_INFO=B.ID_PACKAGE_INFO AND B.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual

      union all

        select 'PRODDATA' as owner,

             'PROD_FORMULA_INFO'  as table_name,

             ' FROM PROD_FORMULA_INFO A,PACKAGE_PLAN_DUTY_REL B,PACKAGE_INFO C' as from_name,

             ' WHERE A.ID_PROD_FORMULA_INFO=B.FORMULA_ID AND B.ID_PACKAGE_INFO=C.ID_PACKAGE_INFO AND C.ID_MARKETPRODUCT_INFO='''||v_product_code||''''  as where_name

      from dual;

      

            

begin

  p_product_codes := VARCHAR2_ARRAY('2F4104D4B6293BD0E054002128F3CB8E');

  v_date :=p_date;

  delete from sqltext;--这个需要创建的表

  commit;

   --生成要删除的数据

   for j in 1..p_product_codes.count    loop    

        dbms_output.put_line(p_product_codes(j));

        begin 

          execute immediate 'alter session set NLS_DATE_FORMAT =''YYYY-MM-DD HH24:MI:SS''';

          v_product_code := upper(p_product_codes(j));

         

         for kf in curdeleteproduct(v_product_code) loop

            v_delete:='delete  '|| kf.from_name ||kf.where_name||';';

            v_delete:=REPLACE(v_delete,'''','''''');

    

            v_sql_delete := 'insert into sqltext values (''' || v_delete || ''',SYSTIMESTAMP)';

  

            execute immediate v_sql_delete; ---执行动态sql的函数

         end loop ;

         end;

   end loop;


  for j in 1..p_product_codes.count    loop  

     v_product_codes:=v_product_codes||','''||p_product_codes(j)||'''' ;

  end loop;

  v_product_codes:=substr(v_product_codes,2);


  commit;


  for j in 1..p_product_codes.count    loop    

    dbms_output.put_line(p_product_codes(j));         

    begin 

     execute immediate 'alter session set NLS_DATE_FORMAT =''YYYY-MM-DD HH24:MI:SS''';

     v_product_code := upper(p_product_codes(j));

   

     for k in cur(v_product_code,v_plans) loop

        --组装列

        v_column :='';

        for i in (select case

                       when t.DATA_TYPE = 'VARCHAR2' then

                        '''''''||A.' || t.COLUMN_NAME || '||'''''''

                         when t.DATA_TYPE = 'CHAR' then

                        '''''''||A.' || t.COLUMN_NAME || '||'''''''

                       when t.DATA_TYPE = 'NUMBER' then

                        '''||A.'||t.COLUMN_NAME||'||'''

                       when t.DATA_TYPE = 'DATE' then

                       'to_date(''''''||to_char(A.'||t.COLUMN_NAME||',''yyyy-mm-dd hh24:mi:ss'')||'''''',''''yyyy-mm-dd hh24:mi:ss'''')'

                       when t.DATA_TYPE = 'CLOB' then

                       '''''''||to_char(A.'||t.COLUMN_NAME||')||'''''''

                       else

                        '' || t.TABLE_NAME || ''

                     end as column_name

                from dba_tab_columns t

               where owner = k.owner

                 and table_name = k.table_name

                 order by column_id) loop


        v_column := v_column || ',' || i.column_name;


        end loop;   

        v_insert := '''insert into ' || k.owner || '.' || k.table_name ||

                ' values(' || substr(v_column, 2) || ');'''||',SYSTIMESTAMP';

         --根据表和表对应的where条件查询到此产品对应的数据，然后批量插入数据到表sqltext中       

        v_sql := 'insert into sqltext select  ' || v_insert || k.from_name||k.where_name;


        execute immediate v_sql;

       update sqltext set text=replace(text,',,',',null,') ;

       update sqltext set text=replace(text,',,',',null,') ;

       update sqltext set text=replace(text,',)',',null)') ;

        commit;

     end loop;

   end;

  end loop;

end;

/