oracle--存取过程001

CREATE OR REPLACE
procedure PROC_IS_NULL as
begin
declare
  --变量
  --拼接查询字段
  l_value_sql varchar2(2000);
  --错误结果拼接
  l_error_message clob;
  --拼接where语句
  l_where_sql varchar2(2000);
  --插入sql
  l_insert_sql clob;
  --获取主键
  p_key varchar2(100);
  --去重主键
  p_key_distinct varchar2(100);

  
  --处理临时表信息中默认数据字典--------------------
  
  --------------------------
  --判断是否为空，将为空数据插入到err表
  cursor tra_table is
    select distinct table_name from all_tab_columns where table_name like '%_TRA' and table_name not like 'S_%_TRA' and table_name not like 'E_%_TRA';
  begin
    for tra in tra_table loop
      --拼接查询字段
      select wm_concat(column_name) into l_value_sql from all_tab_columns where table_name=tra.table_name;
      --拼接错误结果
      select rtrim(replace(wm_concat('nvl2('||column_name||','||''''''||','||''''||column_name||'为空'''||'),'),',,','||'),',') into l_error_message from all_tab_columns where table_name='S_'||tra.table_name AND NULLABLE='N';
      --拼接where语句
      select substr(replace(wm_concat('or '||column_name||' IS NULL '),',',' '),4) into l_where_sql from all_tab_columns where table_name='S_'||tra.table_name AND NULLABLE='N';
      --插入错误表为空数据
      if length(l_error_message) > 0 then 
        l_insert_sql := 'insert into E_'||tra.table_name||' ('||l_value_sql||',ERRORRESULT)'||' select '||l_value_sql||','||l_error_message||' from '||tra.table_name||' where '||l_where_sql;  
        Execute immediate l_insert_sql;
      end if;

      --判断数据是否合法，将不合法数据插入到err表
      --拼接where条件
      delete from long_to_char;
      insert into long_to_char(sc,column_name) select to_lob(UC.search_condition),ucc.column_name sc from user_cons_columns ucc left join user_constraints uc on ucc.constraint_name = uc.constraint_name where ucc.table_name= 'S_'||tra.table_name and generated='USER NAME' and status='ENABLED' and constraint_type='C';
      select replace(replace(replace(substr(wm_concat('or '||to_char(sc)),3),'),or',') or'),'IN (','NOT IN ('),',or','') into l_where_sql from long_to_char;
      if length(l_where_sql)>0 then 
      --拼接错误语句
        select replace(wm_concat('case when '||to_char(sc)||' then '||''''''||' else '||''''||column_name||'无效'||''''||' end'),',case when','|| case when') into l_error_message from long_to_char;
        l_insert_sql := 'insert into E_'||tra.table_name||' ('||l_value_sql||',ERRORRESULT)'||' select '||l_value_sql||','||l_error_message||' from '||tra.table_name||' where '||l_where_sql;  
        Execute immediate l_insert_sql;
      end if;
      --判断字符串是否超长
    end loop;
   
    
    --将不在错误表中的数据插入到正确表
   declare
    cursor tra_suc_tables is
      select distinct table_name from all_tab_columns where table_name like '%_TRA' and table_name not like 'S_%_TRA' and table_name not like 'E_%_TRA';
    begin
       for tra_suc_table in tra_suc_tables loop 
         --将非重复数据插入到错误表中
         --查询value
         dbms_output.put_line(tra_suc_table.table_name);
         select wm_concat(column_name) into l_value_sql from all_tab_columns where table_name=tra_suc_table.table_name;
         --查询关键字
         select replace(wm_concat(column_name),',','||') into p_key from user_cons_columns ucc left join user_constraints uc on ucc.constraint_name = uc.constraint_name where ucc.table_name='S_'||tra_suc_table.table_name and constraint_type = 'P' group by ucc.table_name;
         select wm_concat(column_name) into p_key_distinct from user_cons_columns ucc left join user_constraints uc on ucc.constraint_name = uc.constraint_name where ucc.table_name='S_'||tra_suc_table.table_name and constraint_type = 'P' group by ucc.table_name;
         
         --将重复数据插入到错误表中
         l_insert_sql := 'insert into E_'||tra_suc_table.table_name||' ('||l_value_sql||',ERRORRESULT)'||' select '||l_value_sql||','||'''数据重复'''||' from '||tra_suc_table.table_name||' where '||p_key||' in (select '||p_key||' from '|| tra_suc_table.table_name ||' group by '|| p_key  ||' having count(*) > 1)';  
         Execute immediate l_insert_sql;

         l_insert_sql := 'insert into S_'||tra_suc_table.table_name||'('||l_value_sql||') SELECT '||l_value_sql||' FROM '||tra_suc_table.table_name||' WHERE '||p_key||' not in (select '||p_key||' from E_'||tra_suc_table.table_name||')';
         Execute immediate l_insert_sql;
       end loop;                                                                    
    end; 

    ------------------------------------
    --判断表之间的关联关系
    --判断该合同是否有客户信息,将没有客户信息的合同插入到错误表
   insert into e_lb_contract_info_tra(
          customertype,
          unionnumber,
          isrenter,
          distributor_bank_name,
          distributor_acc_number,
          distributor_account,
          distributor_name,
          consign_repay_phone,
          consign_repay_cert,
          is_consign_repay,
          acc_number,
          account,
          bank_code,
          debit_bank,
          retention_rate,
          consultiong_rate,
          HANDLING_TAX_RATE,
          capital_rate,
          tax_rate,
          project_dept,
          data_sources,
          customer_id,
          distributor_id,
          contract_invoice_status,
          contract_status,
          project_manage,
          car_type,
          leas_form,
          area,
          telephone,
          customername,
          product_name,
          contract_number,
          id,
          errorresult
      )
   select lcit.customertype,
          lcit.unionnumber,
          lcit.isrenter,
          lcit.distributor_bank_name,
          lcit.distributor_acc_number,
          lcit.distributor_account,
          lcit.distributor_name,
          lcit.consign_repay_phone,
          lcit.consign_repay_cert,
          lcit.is_consign_repay,
          lcit.acc_number,
          lcit.account,
          lcit.bank_code,
          lcit.debit_bank,
          lcit.retention_rate,
          lcit.consultiong_rate,
          lcit.HANDLING_TAX_RATE,
          lcit.capital_rate,
          lcit.tax_rate,
          lcit.project_dept,
          lcit.data_sources,
          lcit.customer_id,
          lcit.distributor_id,
          lcit.contract_invoice_status,
          lcit.contract_status,
          lcit.project_manage,
          lcit.car_type,
          lcit.leas_form,
          lcit.area,
          lcit.telephone,
          lcit.customername,
          lcit.product_name,
          lcit.contract_number,
          lcit.id,
          nvl2(customer_account.customer_id,'','该合同没有相关客户')||nvl2(slccct.CONTRACT_NUMBER,'','该合同没有相关方案数据')
      from  lb_contract_info_tra lcit
            left join (select customer_id
                      from s_customer_person_tra
                      union all
                      select customer_id from s_customer_company_tra) customer_account
                       on lcit.customer_id = customer_account.customer_id 
             left join s_lc_calc_condition_tra slccct 
                       on slccct.CONTRACT_NUMBER = lcit.CONTRACT_NUMBER 
      where customer_account.customer_id is null or slccct.CONTRACT_NUMBER is null;
      --判断该合同是否有租金计划
      
   insert into e_lb_contract_info_tra(
          customertype,
          unionnumber,
          isrenter,
          distributor_bank_name,
          distributor_acc_number,
          distributor_account,
          distributor_name,
          consign_repay_phone,
          consign_repay_cert,
          is_consign_repay,
          acc_number,
          account,
          bank_code,
          debit_bank,
          retention_rate,
          consultiong_rate,
          HANDLING_TAX_RATE,
          capital_rate,
          tax_rate,
          project_dept,
          data_sources,
          customer_id,
          distributor_id,
          contract_invoice_status,
          contract_status,
          project_manage,
          car_type,
          leas_form,
          area,
          telephone,
          customername,
          product_name,
          contract_number,
          id,
          errorresult
      )
   select lcit.customertype,
          lcit.unionnumber,
          lcit.isrenter,
          lcit.distributor_bank_name,
          lcit.distributor_acc_number,
          lcit.distributor_account,
          lcit.distributor_name,
          lcit.consign_repay_phone,
          lcit.consign_repay_cert,
          lcit.is_consign_repay,
          lcit.acc_number,
          lcit.account,
          lcit.bank_code,
          lcit.debit_bank,
          lcit.retention_rate,
          lcit.consultiong_rate,
          lcit.HANDLING_TAX_RATE,
          lcit.capital_rate,
          lcit.tax_rate,
          lcit.project_dept,
          lcit.data_sources,
          lcit.customer_id,
          lcit.distributor_id,
          lcit.contract_invoice_status,
          lcit.contract_status,
          lcit.project_manage,
          lcit.car_type,
          lcit.leas_form,
          lcit.area,
          lcit.telephone,
          lcit.customername,
          lcit.product_name,
          lcit.contract_number,
          lcit.id,
          '该合同没有租金计划'
      from  s_lb_contract_info_tra lcit
            where contract_number not in (select distinct contract_number from lc_rent_plan_tra);
      --判断该合同是否有资金计划
   insert into e_lb_contract_info_tra(
          customertype,
          unionnumber,
          isrenter,
          distributor_bank_name,
          distributor_acc_number,
          distributor_account,
          distributor_name,
          consign_repay_phone,
          consign_repay_cert,
          is_consign_repay,
          acc_number,
          account,
          bank_code,
          debit_bank,
          retention_rate,
          consultiong_rate,
          HANDLING_TAX_RATE,
          capital_rate,
          tax_rate,
          project_dept,
          data_sources,
          customer_id,
          distributor_id,
          contract_invoice_status,
          contract_status,
          project_manage,
          car_type,
          leas_form,
          area,
          telephone,
          customername,
          product_name,
          contract_number,
          id,
          errorresult
      )
   select lcit.customertype,
          lcit.unionnumber,
          lcit.isrenter,
          lcit.distributor_bank_name,
          lcit.distributor_acc_number,
          lcit.distributor_account,
          lcit.distributor_name,
          lcit.consign_repay_phone,
          lcit.consign_repay_cert,
          lcit.is_consign_repay,
          lcit.acc_number,
          lcit.account,
          lcit.bank_code,
          lcit.debit_bank,
          lcit.retention_rate,
          lcit.consultiong_rate,
          lcit.HANDLING_TAX_RATE,
          lcit.capital_rate,
          lcit.tax_rate,
          lcit.project_dept,
          lcit.data_sources,
          lcit.customer_id,
          lcit.distributor_id,
          lcit.contract_invoice_status,
          lcit.contract_status,
          lcit.project_manage,
          lcit.car_type,
          lcit.leas_form,
          lcit.area,
          lcit.telephone,
          lcit.customername,
          lcit.product_name,
          lcit.contract_number,
          lcit.id,
          '该合同没有资金计划'
      from  s_lb_contract_info_tra lcit
            where contract_number not in (select distinct contract_number from lc_fund_plan_tra);
      --将错误数据删除
      delete from  s_lb_contract_info_tra where contract_number in (select customer_id from E_LB_CONTRACT_INFO_TRA);
      
    
    commit;
  end;
end;