最近写的一个存储过程

发布一个k8s部署视频:https://edu.youkuaiyun.com/course/detail/26967

课程内容:各种k8s部署方式。包括minikube部署,kubeadm部署,kubeasz部署,rancher部署,k3s部署。包括开发测试环境部署k8s,和生产环境部署k8s。

腾讯课堂连接地址https://ke.qq.com/course/478827?taid=4373109931462251&tuin=ba64518

第二个视频发布  https://edu.youkuaiyun.com/course/detail/27109

腾讯课堂连接地址https://ke.qq.com/course/484107?tuin=ba64518

介绍主要的k8s资源的使用配置和命令。包括configmap,pod,service,replicaset,namespace,deployment,daemonset,ingress,pv,pvc,sc,role,rolebinding,clusterrole,clusterrolebinding,secret,serviceaccount,statefulset,job,cronjob,podDisruptionbudget,podSecurityPolicy,networkPolicy,resourceQuota,limitrange,endpoint,event,conponentstatus,node,apiservice,controllerRevision等。

第三个视频发布:https://edu.youkuaiyun.com/course/detail/27574

详细介绍helm命令,学习helm chart语法,编写helm chart。深入分析各项目源码,学习编写helm插件
————————————————------------------------------------------------------------------------------------------------------------------

 

create or replace procedure generateClientFixRateTask is
   role varchar2(32);
   CHECKER_USERID varchar2(40);
   PUTOUTDATE date;
   v_serialno varchar2(40);
   v_customerid varchar2(40);
   v_need_task_customerid varchar2(40);
   v_customertype varchar2(20);
   cursor clients is select distinct info.customerid,info.customertype from t01_cmis_customer_info info , t03_cmis_business_duebill du
   where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='') and to_date(MATURITY,'yyyymmdd')+90>sysdate
   and to_date(du.PUTOUTDATE,'yyyymmdd') +15 < sysdate;
   v_perv_start_date date;
   v_current_start_date date;
   v_times int;
   v_current_end_date date;
begin
  open clients;
  loop
    fetch clients into v_customerid,v_customertype;
       exit when clients%notfound ;
      --获取检查周期
      select c.times into v_times from check_task_config c  where (
             select  sum(du.BUSINESSSUM) from t01_cmis_customer_info info , t03_cmis_business_duebill du
           where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='')
           and to_date(du.PUTOUTDATE,'yyyymmdd') < sysdate and info.customerid=v_customerid
      ) between
        c.min_loan and c.max_loan and c.loan_type=(select ci.customertype from t01_cmis_customer_info ci where ci.customerid
           =v_customerid
      );

    --查询当前周期开始时间
    select max(t.current_start_date) into v_perv_start_date from CLIENT_FIX_CHECK_INTERVAL t where t.cumsumerid=v_customerid;

    if v_perv_start_date is null then
      --如果没有当前周期开始时间,则根据超过15天但是还没有结束的借据的最小放出时间作为当前周期开始时间
      select  min(to_date(du.putoutdate,'yyyymmdd')+15) into v_current_start_date from t01_cmis_customer_info info , t03_cmis_business_duebill du
         where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='') and to_date(MATURITY,'yyyymmdd')+90>sysdate
         and to_date(du.PUTOUTDATE,'yyyymmdd') +15 < sysdate and info.customerid=v_customerid ;
    else
      v_current_start_date := v_perv_start_date+v_times;
    end if;
    --当期周期结束时间
    v_current_end_date := v_current_start_date+v_times;
    --判断此客户是否需要产生任务
      select max(info.customerid) into v_need_task_customerid from t01_cmis_customer_info info where info.customerid=v_customerid
        and not exists (select * from client_check_c_task ct where ct.customerid=info.customerid
        and ct.tasktype=3 and ct.create_time>v_current_start_date-v_times)
        and not exists (select * from client_check_p_task pt where pt.customerid=info.customerid
        and pt.tasktype=3 and pt.create_time>v_current_start_date-v_times);
      --需要产生任务
      if v_need_task_customerid is not null then
          select c.roleid into role from check_task_config c  where (
             select  sum(du.BUSINESSSUM) from t01_cmis_customer_info info , t03_cmis_business_duebill du
                 where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='')
                 and to_date(du.PUTOUTDATE,'yyyymmdd') < sysdate and info.customerid=v_customerid
            ) between
              c.min_loan and c.max_loan and c.loan_type=(select ci.customertype from t01_cmis_customer_info ci where ci.customerid
                 =v_customerid
            );
          if role='客户经理' then
             select max(info.MANAGERUSERID) into CHECKER_USERID from t01_cmis_customer_info info where info.customerid=v_customerid;
          else
              select max(mng.checkerid) into CHECKER_USERID from t01_cmis_customer_info info,checker_custmng mng where info.MANAGERUSERID=mng.custmng and  info.customerid=v_customerid;
          end if;
          if v_customertype='0310' then

            insert into client_check_c_task(
                    TASK_ID,IS_CORRECT_USE,IS_FINANCIAL_EXCEPTION,IS_REPORT_FINANCIAL,IS_REGULAR_PAY_INTEREST,
                    IS_PRODUCT_AFFECT_PAYBACK,IS_MORTGAGE_BROKEN,IS_FIXASSETS_EXCEPTION,IS_RELATED_TRADE_ILLIGAL,
                    IS_EXTERNAL_POLOCY_CHANGE,IS_CLIENT_BIG_CHANGE,IS_GUARANTEE_EXCEED,IS_FINANCIAL_ABILITY_CHANGE,
                    IS_INVOLVE_LITIGATION,IS_OTHER_BANK_BREACH,IS_EXIST_RISK_FACTOR,CHECKER_OPPION,CUSTOMERID,
                    CHECKER_USERID,CREATE_TIME,CREATORID,LAST_UPDATE_TIME,LAST_UPDATER_ID,STATUS,TASKTYPE,DUEBILL_SERIALNO
             )values(
                    sys_guid(),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,v_customerid,CHECKER_USERID,sysdate,'00000000',null,null,1,2,null
             );
             --更新或新增
             if v_perv_start_date is null then
                 insert into CLIENT_FIX_CHECK_INTERVAL(cumsumerid,CURRENT_START_DATE)values(v_customerid,v_current_start_date);
             else
                 update CLIENT_FIX_CHECK_INTERVAL set CURRENT_START_DATE=v_current_start_date where cumsumerid=v_customerid;
             end if;
             commit;
          else
             insert into client_check_p_task(
                    IS_PRODUCT_CONTRACT,IS_PRODUCT_CONTRACT_REMARK,IS_MONTHLY_INTEREST_BILL,IS_MONTHLY_INTEREST_BILL_RK,IS_BANK_WATER_ACCOUNT,
                    IS_BANK_WATER_ACCOUNT_REMARK,IS_PRODUCE_IMAGE,IS_PRODUCE_IMAGE_REMARK,IS_STORAGE_IMAGE,IS_STORAGE_IMAGE_REMARK,
                    IS_BANK_RELATED_BILL,IS_BANK_RELATED_BILL_REMARK,IS_GUARANTEE_CHECK_MATERIAL,IS_GUARANTEE_CHECK_MATERIAL_RK,
                    IS_OTHER_NEEDED_MATERIAL,IS_OTHER_NEEDED_MATERIAL_RK,IS_EXIST_NEGTIVE_REPAY_CON,IS_EXIST_NEGTIVE_REPAY_CON_RK,
                    IS_FUND_CORRECT_USE,IS_FUND_CORRECT_USE_REMARK,IS_USE_OTHERNAME_LOAN,IS_USE_OTHERNAME_LOAN_REMARK,IS_CHANGE_PROJECT,
                    IS_CHANGE_PROJECT_REMARK,IS_PROJECT_INCOME_CHANGE,IS_PROJECT_INCOME_CHANGE_RK,IS_INVEST_LOSS,IS_INVEST_LOSS_REMARK,
                    IS_DEBIT_INCREASE,IS_DEBIT_INCREASE_REMARK,IS_FOLK_FINANCE,IS_FOLK_FINANCE_REMARK,IS_FAMILY_CHANGE,
                    IS_FAMILY_CHANGE_REMARK,IS_FREQUENTLY_LOST_CONTACT,IS_FREQUENTLY_LOST_CONTACT_MK,IS_MARRIAGE_CHANGE,
                    IS_MARRIAGE_CHANGE_REMARK,IS_HEALTH_CHANGE,IS_HEALTH_CHANGE_REMARK,IS_SOCIAL_BAD_REACT,IS_SOCIAL_BAD_REACT_REMARK,
                    IS_BAD_CREDIT,IS_BAD_CREDIT_REMARK,IS_CONNECT_WITH_BADPEOPLE,IS_CONNECT_WITH_BADPEOPLE_RK,IS_BREAK_LAW,
                    IS_BREAK_LAW_REMARK,IS_GUARANTOR_ABLITY_DOWN,IS_GUARANTOR_ABLITY_DOWN_RK,IS_GUARANTEE_WELL,IS_GUARANTEE_WELL_REMAK,
                    IS_EXIST_OTHER_NEGTIVE,IS_EXIST_OTHER_NEGTIVE_REMARK,IS_CERT_CHANGE_EMPTY,IS_CERT_CHANGE_EMPTY_REMARK,IS_PAY_MATERIAL,
                    IS_PAY_MATERIAL_REMARK,IS_FANANCIAL_REPORT,IS_FANANCIAL_REPORT_REMARK,IS_UTILITY_INVOICE,IS_UTILITY_INVOICE_REMARK,
                    IS_TAX_INVOICE,IS_TAX_INVOICE_REMARK,IS_ALL_KIND_LEDGER,IS_ALL_KIND_LEDGER_REMARK,CHECKER_OPION,DERECTOR_OPTION,
                    TASK_ID,CUSTOMERID,CHECKER_USERID,CREATE_TIME,CREATORID,LAST_UPDATE_TIME,LAST_UPDATER_ID,STATUS,TASKTYPE,DUEBILL_SERIALNO
             )values(
                    0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
                    0,0,0,0,0,0,0,0,0,null,null,sys_guid(),v_customerid,CHECKER_USERID,sysdate,'00000000',null,null,1,3,null
             );
             --更新或新增
             if v_perv_start_date is null then
                 insert into CLIENT_FIX_CHECK_INTERVAL(cumsumerid,CURRENT_START_DATE)values(v_customerid,v_current_start_date);
             else
                 update CLIENT_FIX_CHECK_INTERVAL set CURRENT_START_DATE=v_current_start_date where cumsumerid=v_customerid;
             end if;
             commit;
          end if;
      end if;

   end loop;
EXCEPTION
   when no_data_found then dbms_output.put_line('没有需要生成的数据');

end generateClientFixRateTask;

 

再来一个

 

create or replace procedure generateDuebillReminderTask is

Cursor duebillsEnt is select b.serialno,b.customerid  from t03_cmis_business_duebill b,t01_cmis_ent_info info 
   where b.customerid=info.customerid and to_date(b.PUTOUTDATE,'yyyymmdd') <=sysdate and
to_date(b.PUTOUTDATE,'yyyymmdd') +15 >= sysdate and b.serialno not in (
   select ct.duebill_serialno from client_check_c_task ct where ct.customerid=b.customerid
   and ct.tasktype=1 
);

Cursor duebillsInd is select b.serialno,b.customerid  from t03_cmis_business_duebill b,t01_cmis_ind_info info 
   where b.customerid=info.customerid  and to_date(b.PUTOUTDATE,'yyyymmdd') <=sysdate and
to_date(b.PUTOUTDATE,'yyyymmdd') +15 >= sysdate and b.serialno not in (
   select pt.duebill_serialno from client_check_p_task pt where pt.customerid=b.customerid
   and pt.tasktype=1 
);

   CHECKER_USERID varchar2(40);
   v_serialno varchar2(40);
   v_customerid varchar2(40);
   v_sqlcode varchar2(100);
   v_sqlerrm varchar2(4000);
begin
  open duebillsEnt;
  loop
    fetch duebillsEnt into v_serialno,v_customerid;
    exit when duebillsEnt%notfound ;
    begin
         select checker.checkerid into CHECKER_USERID from t01_cmis_ent_info info,checker_custmng checker where info.manageuserid=checker.custmng and info.customerid=v_customerid;
    exception
         when others then
              v_sqlcode:=SQLCODE;
              v_sqlerrm:=SQLERRM;
              insert into task_generator_error(id,customerid,serialno,task_type,error_code,error_msg,is_solve,customer_type,occor_date,solve_date,exception_sql)
              values(sys_guid(),v_customerid,v_serialno,1,v_sqlcode,v_sqlerrm,0,'c',sysdate,null,
                     'select checker.checkerid into CHECKER_USERID from t01_cmis_ent_info info,checker_custmng checker where info.manageuserid=checker.custmng and info.customerid=v_customerid;'
              );
              commit;
              continue;
    end;
    insert into client_check_c_task(
              TASK_ID,IS_CORRECT_USE,IS_FINANCIAL_EXCEPTION,IS_REPORT_FINANCIAL,IS_REGULAR_PAY_INTEREST,
              IS_PRODUCT_AFFECT_PAYBACK,IS_MORTGAGE_BROKEN,IS_FIXASSETS_EXCEPTION,IS_RELATED_TRADE_ILLIGAL,
              IS_EXTERNAL_POLOCY_CHANGE,IS_CLIENT_BIG_CHANGE,IS_GUARANTEE_EXCEED,IS_FINANCIAL_ABILITY_CHANGE,
              IS_INVOLVE_LITIGATION,IS_OTHER_BANK_BREACH,IS_EXIST_RISK_FACTOR,CHECKER_OPPION,CUSTOMERID,
              CHECKER_USERID,CREATE_TIME,CREATORID,LAST_UPDATE_TIME,LAST_UPDATER_ID,STATUS,TASKTYPE,DUEBILL_SERIALNO,CHECK_TIME,
              IS_SYNC,IS_BIG_PROBLEM,IS_SOLVE_PROBLEM
       )values(
              sys_guid(),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,v_customerid,CHECKER_USERID,sysdate,'00000000',null,null,1,1,v_serialno,null,
              0,0,0
       );

    commit;
  end loop;
  close duebillsEnt;
  
  open duebillsInd;
  loop
    fetch duebillsInd into v_serialno,v_customerid;
    exit when duebillsInd%notfound ;
    begin
        select checker.checkerid into CHECKER_USERID from t01_cmis_ind_info info,checker_custmng checker where info.manageuserid=checker.custmng and info.customerid=v_customerid;
    exception
         when others then
              v_sqlcode:=SQLCODE;
              v_sqlerrm:=SQLERRM;
              insert into task_generator_error(id,customerid,serialno,task_type,error_code,error_msg,is_solve,customer_type,occor_date,solve_date,exception_sql)
              values(sys_guid(),v_customerid,v_serialno,1,v_sqlcode,v_sqlerrm,0,'p',sysdate,null,
                     'select checker.checkerid into CHECKER_USERID from t01_cmis_ent_info info,checker_custmng checker where info.manageuserid=checker.custmng and info.customerid=v_customerid;'
              );
              commit;
              continue;
    end;
    insert into client_check_p_task(
                IS_PRODUCT_CONTRACT,IS_MONTHLY_INTEREST_BILL,IS_BANK_WATER_ACCOUNT,
                IS_PRODUCE_IMAGE,IS_STORAGE_IMAGE,
                IS_BANK_RELATED_BILL,IS_GUARANTEE_CHECK_MATERIAL,
                IS_OTHER_NEEDED_MATERIAL,IS_EXIST_NEGTIVE_REPAY_CON,
                IS_FUND_CORRECT_USE,IS_USE_OTHERNAME_LOAN,IS_CHANGE_PROJECT,
                IS_PROJECT_INCOME_CHANGE,IS_INVEST_LOSS,
                IS_DEBIT_INCREASE,IS_FOLK_FINANCE,IS_FAMILY_CHANGE,
                IS_FREQUENTLY_LOST_CONTACT,IS_MARRIAGE_CHANGE,
                IS_HEALTH_CHANGE,IS_SOCIAL_BAD_REACT,
                IS_BAD_CREDIT,IS_CONNECT_WITH_BADPEOPLE,IS_BREAK_LAW,
                IS_GUARANTOR_ABLITY_DOWN,IS_GUARANTEE_WELL,
                IS_EXIST_OTHER_NEGTIVE,IS_CERT_CHANGE_EMPTY,IS_PAY_MATERIAL,
                IS_FANANCIAL_REPORT,IS_UTILITY_INVOICE,
                IS_TAX_INVOICE,IS_ALL_KIND_LEDGER,CHECKER_OPION,DERECTOR_OPTION,
                TASK_ID,CUSTOMERID,CHECKER_USERID,CREATE_TIME,CREATORID,LAST_UPDATE_TIME,LAST_UPDATER_ID,STATUS,TASKTYPE,DUEBILL_SERIALNO,
                CHECK_TIME,IS_SYNC,IS_BIG_PROBLEM,IS_SOLVE_PROBLEM
         )values(
                0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
                null,null,sys_guid(),v_customerid,CHECKER_USERID,sysdate,'00000000',null,null,1,3,v_serialno,
                null,0,0,0
         );

         commit;
     end loop;
     close duebillsInd;
EXCEPTION
   when no_data_found then dbms_output.put_line('没有需要生成的数据');
end generateDuebillReminderTask;



改进版:

 

create or replace procedure generateClientFixRateTask is
   CHECKER_USERID varchar2(40);
   v_customerid varchar2(40);
   v_need_task_customerid varchar2(40);
   cursor clients_Ent is select distinct info.customerid from t01_cmis_ent_info info , t03_cmis_business_duebill du
   where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='') and to_date(MATURITY,'yyyymmdd')+90>sysdate
   and to_date(du.PUTOUTDATE,'yyyymmdd') +15 < sysdate;
   cursor clients_Ind is select distinct info.customerid from t01_cmis_ind_info info , t03_cmis_business_duebill du
   where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='') and to_date(MATURITY,'yyyymmdd')+90>sysdate
   and to_date(du.PUTOUTDATE,'yyyymmdd') +15 < sysdate;
   v_perv_start_date date;
   v_current_start_date date;
   v_times int;
   v_current_end_date date;
   v_sqlcode varchar2(100);
   v_sqlerrm varchar2(4000);
   v_skip int;
begin
  --处理企业新信息
  open clients_Ent;
  loop
    fetch clients_Ent into v_customerid;
       exit when clients_Ent%notfound ;
      begin 
          --获取检查周期
          select c.times into v_times from check_task_config c  where (
                 select  sum(du.BUSINESSSUM) from t01_cmis_ent_info info , t03_cmis_business_duebill du
               where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='')
               and to_date(du.PUTOUTDATE,'yyyymmdd') < sysdate and info.customerid=v_customerid
          ) between
            c.min_loan and c.max_loan and c.customer_type='c';
      exception
            when others then
                    v_sqlcode:=SQLCODE;
                    v_sqlerrm:=SQLERRM;
                    insert into task_generator_error(id,customerid,serialno,task_type,error_code,error_msg,is_solve,customer_type,occor_date,solve_date,exception_sql)
                    values(sys_guid(),v_customerid,null,3,v_sqlcode,v_sqlerrm,0,'c',sysdate,null,
                           'select c.times into v_times from check_task_config c  where (
                                   select  sum(du.BUSINESSSUM) from t01_cmis_ent_info info , t03_cmis_business_duebill du
                                 where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='''')
                                 and to_date(du.PUTOUTDATE,''yyyymmdd'') < sysdate and info.customerid=v_customerid
                            ) between
                              c.min_loan and c.max_loan and c.customer_type=''c'';'
                    );
                    commit;
                    continue;
    end;
    begin
      --查询当前周期开始时间
      select t.current_start_date into v_perv_start_date from CLIENT_FIX_CHECK_INTERVAL t where t.cumsumerid=v_customerid;
    exception 
             when no_data_found then
                 goto LB1;
             when others then
                    v_sqlcode:=SQLCODE;
                    v_sqlerrm:=SQLERRM;
                    insert into task_generator_error(id,customerid,serialno,task_type,error_code,error_msg,is_solve,customer_type,occor_date,solve_date,exception_sql)
                    values(sys_guid(),v_customerid,null,3,v_sqlcode,v_sqlerrm,0,'c',sysdate,null,
                           ' select t.current_start_date into v_perv_start_date from CLIENT_FIX_CHECK_INTERVAL t where t.cumsumerid=v_customerid;'
                    );
                    commit;
                    continue;
             
    end;
    <<LB1>>
    if v_perv_start_date is null then
      --如果没有当前周期开始时间,则根据超过15天但是还没有结束的借据的最小放出时间作为当前周期开始时间
      select  min(to_date(du.putoutdate,'yyyymmdd'))+15 into v_current_start_date from t01_cmis_ent_info info , t03_cmis_business_duebill du
         where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='') and to_date(MATURITY,'yyyymmdd')+90>sysdate
         and to_date(du.PUTOUTDATE,'yyyymmdd') +15 < sysdate and info.customerid=v_customerid ;
    else
      v_current_start_date := v_perv_start_date+v_times;
    end if;
    --如果当前时间与本周期开始时间大于一个以上的周期间隔时间
    v_skip:=ceil(sysdate - v_current_start_date)*1.0/v_times;
    if v_skip>1 then
      --调整当前周期开始时间为最近周期开始时间
      v_current_start_date:=v_current_start_date+(v_skip-1)*v_times;
    end if;
    --当期周期结束时间
    v_current_end_date := v_current_start_date+v_times;
    --判断此客户是否需要产生任务
      select info.customerid into v_need_task_customerid from t01_cmis_ent_info info where info.customerid=v_customerid
        and not exists (select * from client_check_c_task ct where ct.customerid=info.customerid
        and ct.tasktype=3 and ct.create_time>=v_current_start_date);
      --需要产生任务
      if v_need_task_customerid is not null then
          begin
               --查询检查人
               select checker.checkerid into CHECKER_USERID from (
                      SELECT *
                        FROM (SELECT ROW_NUMBER() OVER(PARTITION BY xd_cust_id ORDER BY statis_dt DESC) rn,
                        J03_C_LOAN_INFO.*
                        FROM J03_C_LOAN_INFO) t
                        WHERE rn = 1
               ) cl,checker_custmng checker where cl.manageuserid=checker.custmng and cl.xd_cust_id=v_customerid;
          exception
               when others then
                    v_sqlcode:=SQLCODE;
                    v_sqlerrm:=SQLERRM;
                    insert into task_generator_error(id,customerid,serialno,task_type,error_code,error_msg,is_solve,customer_type,occor_date,solve_date,exception_sql)
                    values(sys_guid(),v_customerid,null,1,v_sqlcode,v_sqlerrm,0,'c',sysdate,null,
                           '
                                select checker.checkerid into CHECKER_USERID from (
                                        SELECT *
                                          FROM (SELECT ROW_NUMBER() OVER(PARTITION BY xd_cust_id ORDER BY statis_dt DESC) rn,
                                          J03_C_LOAN_INFO.*
                                          FROM J03_C_LOAN_INFO) t
                                          WHERE rn = 1
                                 ) cl,checker_custmng checker where cl.manageuserid=checker.custmng and cl.xd_cust_id=v_customerid;
                           '
                    );
                    commit;
                    continue;
          end;

            insert into client_check_c_task(
                    TASK_ID,IS_CORRECT_USE,IS_FINANCIAL_EXCEPTION,IS_REPORT_FINANCIAL,IS_REGULAR_PAY_INTEREST,
                    IS_PRODUCT_AFFECT_PAYBACK,IS_MORTGAGE_BROKEN,IS_FIXASSETS_EXCEPTION,IS_RELATED_TRADE_ILLIGAL,
                    IS_EXTERNAL_POLOCY_CHANGE,IS_CLIENT_BIG_CHANGE,IS_GUARANTEE_EXCEED,IS_FINANCIAL_ABILITY_CHANGE,
                    IS_INVOLVE_LITIGATION,IS_OTHER_BANK_BREACH,IS_EXIST_RISK_FACTOR,CHECKER_OPPION,CUSTOMERID,
                    CHECKER_USERID,CREATE_TIME,CREATORID,LAST_UPDATE_TIME,LAST_UPDATER_ID,STATUS,TASKTYPE,DUEBILL_SERIALNO
             )values(
                    sys_guid(),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,v_customerid,CHECKER_USERID,sysdate,'00000000',null,null,1,3,null
             );
             --更新或新增
             if v_perv_start_date is null then
                 insert into CLIENT_FIX_CHECK_INTERVAL(cumsumerid,CURRENT_START_DATE)values(v_customerid,v_current_start_date);
             else
                 update CLIENT_FIX_CHECK_INTERVAL set CURRENT_START_DATE=v_current_start_date where cumsumerid=v_customerid;
             end if;
             commit;
          
      end if;

   end loop;
   close clients_Ent;
   --处理个人信息
   open clients_Ind;
  loop
    fetch clients_Ind into v_customerid;
       exit when clients_Ind%notfound ;
      begin 
          --获取检查周期
          select c.times into v_times from check_task_config c  where (
                 select  sum(du.BUSINESSSUM) from t01_cmis_ind_info info , t03_cmis_business_duebill du
               where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='')
               and to_date(du.PUTOUTDATE,'yyyymmdd') < sysdate and info.customerid=v_customerid
          ) between
            c.min_loan and c.max_loan and c.customer_type='p';
      exception
            when others then
                    v_sqlcode:=SQLCODE;
                    v_sqlerrm:=SQLERRM;
                    --如果报错记录错误日志
                    insert into task_generator_error(id,customerid,serialno,task_type,error_code,error_msg,is_solve,customer_type,occor_date,solve_date,exception_sql)
                    values(sys_guid(),v_customerid,null,3,v_sqlcode,v_sqlerrm,0,'p',sysdate,null,
                           'select c.times into v_times from check_task_config c  where (
                                   select  sum(du.BUSINESSSUM) from t01_cmis_ent_info info , t03_cmis_business_duebill du
                                 where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='''')
                                 and to_date(du.PUTOUTDATE,''yyyymmdd'') < sysdate and info.customerid=v_customerid
                            ) between
                              c.min_loan and c.max_loan and c.customer_type=''p'';'
                    );
                    commit;
                    continue;
    end;
    begin
      --查询当前周期开始时间
      select t.current_start_date into v_perv_start_date from CLIENT_FIX_CHECK_INTERVAL t where t.cumsumerid=v_customerid;
    exception 
             when no_data_found then
                 goto LB1;
             when others then
                    v_sqlcode:=SQLCODE;
                    v_sqlerrm:=SQLERRM;
                    insert into task_generator_error(id,customerid,serialno,task_type,error_code,error_msg,is_solve,customer_type,occor_date,solve_date,exception_sql)
                    values(sys_guid(),v_customerid,null,3,v_sqlcode,v_sqlerrm,0,'p',sysdate,null,
                           ' select t.current_start_date into v_perv_start_date from CLIENT_FIX_CHECK_INTERVAL t where t.cumsumerid=v_customerid;'
                    );
                    commit;
                    continue;
             
    end;
    <<LB1>>
    if v_perv_start_date is null then
      --如果没有当前周期开始时间,则根据超过15天但是还没有结束的借据的最小放出时间作为当前周期开始时间
      select  min(to_date(du.putoutdate,'yyyymmdd'))+15 into v_current_start_date from t01_cmis_ind_info info , t03_cmis_business_duebill du
         where info.customerid=du.customerid and (du.finishdate is  null or du.finishdate='') and to_date(MATURITY,'yyyymmdd')+90>sysdate
         and to_date(du.PUTOUTDATE,'yyyymmdd') +15 < sysdate and info.customerid=v_customerid ;
    else
      v_current_start_date := v_perv_start_date+v_times;
    end if;
    --如果当前时间与本周期开始时间大于一个以上的周期间隔时间
    v_skip:=ceil(sysdate - v_current_start_date)*1.0/v_times;
    if v_skip>1 then
      --调整当前周期开始时间为最近周期开始时间
      v_current_start_date:=v_current_start_date+(v_skip-1)*v_times;
    end if;
    --当期周期结束时间
    v_current_end_date := v_current_start_date+v_times;
    --判断此客户是否需要产生任务
      select info.customerid into v_need_task_customerid from t01_cmis_ind_info info where info.customerid=v_customerid
        and not exists (select * from client_check_p_task ct where ct.customerid=info.customerid
        and ct.tasktype=3 and ct.create_time>=v_current_start_date);
      --需要产生任务
      if v_need_task_customerid is not null then
          begin
               --查询检查人
               select checker.checkerid into CHECKER_USERID from (
                      SELECT *
                        FROM (SELECT ROW_NUMBER() OVER(PARTITION BY xd_cust_id ORDER BY statis_dt DESC) rn,
                        J03_P_LOAN_INFO.*
                        FROM J03_P_LOAN_INFO) t
                        WHERE rn = 1
               ) cl,checker_custmng checker where cl.manageuserid=checker.custmng and cl.xd_cust_id=v_customerid;
          exception
               when others then
                    v_sqlcode:=SQLCODE;
                    v_sqlerrm:=SQLERRM;
                    insert into task_generator_error(id,customerid,serialno,task_type,error_code,error_msg,is_solve,customer_type,occor_date,solve_date,exception_sql)
                    values(sys_guid(),v_customerid,null,1,v_sqlcode,v_sqlerrm,0,'p',sysdate,null,
                           '
                                select checker.checkerid into CHECKER_USERID from (
                                        SELECT *
                                          FROM (SELECT ROW_NUMBER() OVER(PARTITION BY xd_cust_id ORDER BY statis_dt DESC) rn,
                                          J03_P_LOAN_INFO.*
                                          FROM J03_P_LOAN_INFO) t
                                          WHERE rn = 1
                                 ) cl,checker_custmng checker where cl.manageuserid=checker.custmng and cl.xd_cust_id=v_customerid;
                           '
                    );
                    commit;
                    continue;
          end;

            insert into client_check_p_task(
                    IS_PRODUCT_CONTRACT,IS_MONTHLY_INTEREST_BILL,IS_BANK_WATER_ACCOUNT,
                    IS_PRODUCE_IMAGE,IS_STORAGE_IMAGE,
                    IS_BANK_RELATED_BILL,IS_GUARANTEE_CHECK_MATERIAL,
                    IS_OTHER_NEEDED_MATERIAL,IS_EXIST_NEGTIVE_REPAY_CON,
                    IS_FUND_CORRECT_USE,IS_USE_OTHERNAME_LOAN,IS_CHANGE_PROJECT,
                    IS_PROJECT_INCOME_CHANGE,IS_INVEST_LOSS,
                    IS_DEBIT_INCREASE,IS_FOLK_FINANCE,IS_FAMILY_CHANGE,
                    IS_FREQUENTLY_LOST_CONTACT,IS_MARRIAGE_CHANGE,
                    IS_HEALTH_CHANGE,IS_SOCIAL_BAD_REACT,
                    IS_BAD_CREDIT,IS_CONNECT_WITH_BADPEOPLE,IS_BREAK_LAW,
                    IS_GUARANTOR_ABLITY_DOWN,IS_GUARANTEE_WELL,
                    IS_EXIST_OTHER_NEGTIVE,IS_CERT_CHANGE_EMPTY,IS_PAY_MATERIAL,
                    IS_FANANCIAL_REPORT,IS_UTILITY_INVOICE,
                    IS_TAX_INVOICE,IS_ALL_KIND_LEDGER,CHECKER_OPION,DERECTOR_OPTION,
                    TASK_ID,CUSTOMERID,CHECKER_USERID,CREATE_TIME,CREATORID,LAST_UPDATE_TIME,LAST_UPDATER_ID,STATUS,TASKTYPE,DUEBILL_SERIALNO,
                    CHECK_TIME,IS_SYNC,IS_BIG_PROBLEM,IS_SOLVE_PROBLEM
             )values(
                    0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
                    null,null,sys_guid(),v_customerid,CHECKER_USERID,sysdate,'00000000',null,null,1,3,null,
                    null,0,0,0
             );

             --更新或新增
             if v_perv_start_date is null then
                 insert into CLIENT_FIX_CHECK_INTERVAL(cumsumerid,CURRENT_START_DATE)values(v_customerid,v_current_start_date);
             else
                 update CLIENT_FIX_CHECK_INTERVAL set CURRENT_START_DATE=v_current_start_date where cumsumerid=v_customerid;
             end if;
             commit;
          
      end if;

   end loop;
   close clients_Ind;
EXCEPTION
   when no_data_found then dbms_output.put_line('没有需要生成的数据');

end generateClientFixRateTask ;

 

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

hxpjava1

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值