发布一个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 ;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
