匿名sql

coverl

　　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;
　　/