CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_main() -- ��ʼ�� ����ȫ������ IS DECLARE individual_count int strsql string BEGIN shanglifeecif.customertotaltemp(); DELETE FROM shanglifeecif.individual; --��ʼ���ͻ���Ϣ shanglifeecif.init_all_individual(); --�����û��ȼ���Ϣ shanglifeecif.up_t_customers_class_1(); --����������Ϣ shanglifeecif.up_other_customerinfo() END; / CREATE OR REPLACE PROCEDURE shanglifeecif.customertotaltemp() IS DECLARE BEGIN DELETE FROM shanglifeecif.customertotaltemp; insert into shanglifeecif.customertotaltemp( scustid, name, gender, birthday, idcard, idtype ) SELECT scustid , max(name) as name, max(gender) as gender , max(birthday) as birthday, max(idcard) as idcard, max(idtype) AS idtype FROM ( SELECT trim(customerno) AS scustid,--Ͷ���� trim(name) AS name, trim(sex) AS gender, trim(birthday) AS birthday, trim(idtype) AS idtype , trim(idno) AS idcard FROM policy_information WHERE customerno IS NOT NULL UNION SELECT trim(insuredno) AS scustid,--������ trim(insuredname) AS name, trim(insuredsex) AS gender, trim(insuredbirthday) AS birthday, trim(insuredidtype) AS idtype , trim(insuredidno) AS idcard FROM policy_information WHERE insuredno IS NOT NULL ) tmpTable GROUP BY scustid; UPDATE shanglifeecif.customertotaltemp sct SET sct.mobile = (SELECT trim(t.MOBILE) FROM t_customer_class t WHERE sct.scustid=t.customer_id); EXCEPTION WHEN HIVE_EXCEPTION THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); WHEN Others THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); END ; / CREATE OR REPLACE PROCEDURE shanglifeecif.init_all_individual() -- ��ʼ�����пͻ���Ϣ IS BEGIN --��ѯ���˴δ��������ݲ������� insert into shanglifeecif.individual ( indid, custid, scustid, name, gender, birthday, idcard, Passport, Dlicense, OtherIdNumber, created_time, created_by ) SELECT row_number()OVER(), 'CP'||lpad(row_number()over(),10,'0'), sc.scustid, sc.name, sc.gender, sc.birthday, CASE sc.idtype WHEN '0' THEN sc.idcard END AS idcard, CASE sc.idtype WHEN '1' THEN sc.idcard END AS passport, CASE sc.idtype WHEN '3' THEN sc.idcard END AS dlicense, CASE WHEN sc.idtype <> '0' AND sc.idtype <> '1' AND sc1.idtype <> '3' THEN sc.idcard END AS otherIdnumber, sysdate, 'admin' FROM shanglifeecif.customertotaltemp sc ORDER BY sc.scustid; EXCEPTION WHEN HIVE_EXCEPTION THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); WHEN Others THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); END; / CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_0( individual_count OUT int) -- ��ʼ�� ����֤ IS BEGIN --��ѯ���˴δ��������ݲ������� insert into shanglifeecif.individual ( indid, custid, scustid, name, gender, birthday, idcard, created_time, created_by ) SELECT row_number()over(), 'CP'||lpad(row_number()over(),10,'0'), scustid , max(name) as name, max(gender) as gender , max(birthday) as birthday, max(idcard) as idcard, sysdate, 'admin' FROM ( SELECT trim(customerno) AS scustid,--Ͷ���� trim(name) AS name, trim(sex) AS gender, trim(birthday) AS birthday, trim(idtype) AS idtype , trim(idno) AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype=0 UNION SELECT trim(insuredno) AS scustid,--������ trim(insuredname) AS name, trim(insuredsex) AS gender, trim(insuredbirthday) AS birthday, trim(insuredidtype) AS idtype , trim(insuredidno) AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype=0 ) tmpTable GROUP BY scustid --�Ѿ����ڵ��������� select count(0) into individual_count from shanglifeecif.individual; EXCEPTION WHEN HIVE_EXCEPTION THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); WHEN Others THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); END; / CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_1(individual_count INOUT int) -- ��ʼ�� ���� IS BEGIN --��ѯ���˴δ��������ݲ������� insert into shanglifeecif.individual ( indid, custid, scustid, name, gender, birthday, passport, created_time, created_by ) SELECT row_number()over(), 'CP'||lpad(row_number()over(),10,'0'), scustid , max(name) as name, max(gender) as gender , max(birthday) as birthday, max(idcard) as idcard, sysdate, 'admin' FROM ( SELECT trim(customerno) AS scustid,--Ͷ���� trim(name) AS name, trim(sex) AS gender, trim(birthday) AS birthday, trim(idtype) AS idtype , trim(idno) AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype=1 UNION SELECT trim(insuredno) AS scustid,--������ trim(insuredname) AS name, trim(insuredsex) AS gender, trim(insuredbirthday) AS birthday, trim(insuredidtype) AS idtype , trim(insuredidno) AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype=1 ) tmpTable GROUP BY scustid --�Ѿ����ڵ��������� select count(0) into individual_count from shanglifeecif.individual; EXCEPTION WHEN HIVE_EXCEPTION THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); WHEN Others THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); END; / CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_3(individual_count INOUT int) -- ��ʼ�� ���� IS BEGIN --��ѯ���˴δ��������ݲ������� insert into shanglifeecif.individual( indid, custid, scustid, name, gender, birthday, dlicense, created_time, created_by ) SELECT row_number()over(), 'CP'||lpad(row_number()over(),10,'0'), scustid , max(name) as name, max(gender) as gender , max(birthday) as birthday, max(idcard) as idcard, sysdate, 'admin' FROM ( SELECT trim(customerno) AS scustid,--Ͷ���� trim(name) AS name, trim(sex) AS gender, trim(birthday) AS birthday, trim(idtype) AS idtype , trim(idno) AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype=3 UNION SELECT trim(insuredno) AS scustid,--������ trim(insuredname) AS name, trim(insuredsex) AS gender, trim(insuredbirthday) AS birthday, trim(insuredidtype) AS idtype , trim(insuredidno) AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype=3 ) tmpTable GROUP BY scustid --�Ѿ����ڵ��������� select count(0) into individual_count from shanglifeecif.individual; EXCEPTION WHEN HIVE_EXCEPTION THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); WHEN Others THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); END; / CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_other(individual_count INOUT int) -- ��ʼ��������������֤����Ϣ IS BEGIN --��ѯ���˴δ��������ݲ������� insert into shanglifeecif.individual( indid, custid, scustid, name, gender, birthday, otheridnumber, created_time, created_by ) SELECT row_number()over(), 'CP'||lpad(row_number()over(),10,'0'), scustid , max(name) as name, max(gender) as gender , max(birthday) as birthday, max(idcard) as idcard, sysdate, 'admin' FROM ( SELECT trim(customerno) AS scustid,--Ͷ���� trim(name) AS name, trim(sex) AS gender, trim(birthday) AS birthday, trim(idtype) AS idtype , trim(idno) AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype not in (0,1,3) UNION SELECT trim(insuredno) AS scustid,--������ trim(insuredname) AS name, trim(insuredsex) AS gender, trim(insuredbirthday) AS birthday, trim(insuredidtype) AS idtype , trim(insuredidno) AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype not in (0,1,3) ) tmpTable GROUP BY scustid --�Ѿ����ڵ��������� select count(0) into individual_count from shanglifeecif.individual; EXCEPTION WHEN HIVE_EXCEPTION THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); WHEN Others THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); END; / CREATE OR REPLACE PROCEDURE shanglifeecif.up_t_customers_class_1() -- �����洢���� IS BEGIN UPDATE shanglifeecif.individual a SET ( CustClass ,-- �ͻ��ȼ� ConValue ,-- ���ȷ� Awarded3 ,-- ��ͥ�ӷ�2 Awarded2 ,-- ���ڼӷ� Awarded1 ,-- �����ӷ� SOValue ,-- �ܷ�ֵ EndDate, -- �ͻ��ȼ�ʧЧ���� Height, --���� Weight, --���� BMI, PIncome, --���������� FIncome, --��ͥ������ IncomeSource, --������Դ SIStatus, --�籣��� --Ethnic, --������� Nation, --���� MaritalStat, --���� Employer,--������λ Education,--ѧ�� Dday, --�������� regtype, --�������� ZIPCODE,--�ʱ� HPhone,--�绰 PMPhone,--�ֻ� email, -- ���� RAL, --��ϵ��ַ sobirth --ʡ�� ) = ( select b.CLASS_VALUE,--�ͻ��ȼ� b.CONTRIBUTION_VALUE,--���ȷ� b.AWARDED3, --��ͥ�ӷ� b.AWARDED2, --���ڼӷ� b.AWARDED1, --�����ӷ� b.TOTAL_VALUE, --�ܷ�ֵ trim(b.END_DATE) as END_DATE, --ʧЧ���� b.STATURE, --���� b.AVOIRDUPOIS,--���� b.BMI, --�����������ؼ��� b.YEARINCOME, --���������� b.FAMILYYEARSALARY, --��ͥ������ trim(b.INCOMESOURCE) as INCOMESOURCE, --������Դ trim(b.SOCIALINSUFLAG) as SOCIALINSUFLAG, --�籣��� --trim(b.NATIONALITY) as NATIONALITY, --������� trim(b.NATIVEPLACE) as NATIVEPLACE, --���� trim(b.MARRIAGE) as MARRIAGE,--���� trim(b.GRPNAME) as GRPNAME,--������λ���� trim(b.DEGREE) as DEGREE,--ѧ�� trim(b.DEATHDATE) as DEATHDATE,--�������� trim(b.DENTYPE) as DENTYPE,--�������� trim(b.ZIPCODE) as ZIPCODE,--�ʱ� trim(b.PHONE) as PHONE,--�绰 trim(b.MOBILE) as MOBILE,--�ֻ� trim(b.EMAIL) as EMAIL,--���� regexp_replace(trim(b.POSTALADDRESS),'[0-9]','*') as POSTALADDRESS, --��ϵ��ַ regexp_extract(b.POSTALADDRESS,'(.*?)ʡ|(.*?)��',0) -- ʡ�� from t_customer_class b where b.CUSTOMER_ID = a.scustid ) WHERE 1=1 ; EXCEPTION WHEN HIVE_EXCEPTION THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); WHEN Others THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); END; / CREATE OR REPLACE PROCEDURE shanglifeecif.up_other_customerinfo() -- �����洢���� IS BEGIN --���¿ͻ�����Ͷ���� UPDATE shanglifeecif.individual i SET i.custtype = 'Ͷ����' WHERE i.scustid IN (SELECT p.customerno FROM policy_information p); --���¿ͻ�����Ͷ���ˡ������� UPDATE shanglifeecif.individual i SET i.custtype = '������' WHERE i.scustid IN (SELECT pi.insuredno FROM policy_information pi); --���¿ͻ�����Ͷ���ˡ������� UPDATE shanglifeecif.individual i SET i.custtype = 'Ͷ���ˡ�������' WHERE i.scustid IN (SELECT p.customerno FROM policy_information p) AND i.scustid IN (SELECT pi.insuredno FROM policy_information pi); --����ҵ������ UPDATE shanglifeecif.Individual si1 SET si1.policybelong = ( SELECT trim(t.SALECOM) FROM ( SELECT row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate asc) rd,p.customerno,p.SALECOM FROM policy_information p ) t WHERE t.rd = 1 AND si1.scustid = t.customerno ); UPDATE shanglifeecif.Individual si1 SET si1.policybelong = ( SELECT trim(t.SALECOM) FROM ( SELECT row_number()OVER(PARTITION BY p.insuredno ORDER BY p.polapplydate asc) rd,p.insuredno,p.SALECOM FROM policy_information p ) t WHERE t.rd = 1 AND si1.scustid = t.insuredno ); --����ְҵ���������Ϣ UPDATE shanglifeecif.Individual si1 SET (si1.Occupation,si1.Ethnic) = ( SELECT trim(t.occupationname),trim(t.NATIONALITYNAME) FROM ( SELECT row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate desc) rd,p.customerno,p.occupationname,p.NATIONALITYNAME FROM policy_information p ) t WHERE t.rd = 1 AND si1.scustid = t.customerno ); --���¹����� UPDATE shanglifeecif.individual si SET si.officialCalculus = ( SELECT temp.available_points FROM (SELECT t.available_points,t.certificate_no,row_number()OVER(PARTITION BY t.certificate_no ORDER BY t.gmt_created DESC) rd FROM t_account t) temp WHERE temp.rd = 1 AND temp.certificate_no = si.idcard ); EXCEPTION WHEN HIVE_EXCEPTION THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); WHEN Others THEN INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate()); END; / /* CREATE OR REPLACE PROCEDURE shanglifeecif.up_health_insurance_listing_1() -- �����洢���� IS BEGIN --���¸�����Ϣ�� UPDATE shanglifeecif.individual a SET ( OccupationId ,-- ְҵ���� Occupation, -- ְҵ���� HomeAdress ) = ( SELECT t.appntoccupationcode, t.appntoccupationname, t.APPNTHOMEADDRESS FROM ( select appntno, max(appntoccupationcode) appntoccupationcode,--ְҵ���� max(appntoccupationname) appntoccupationname,--ְҵ���� max(APPNTHOMEADDRESS) APPNTHOMEADDRESS --סַ from HEALTH_INSURANCE_LISTING WHERE appntoccupationcode is not null and appntoccupationname is not NULL group by appntno ) t WHERE t.appntno = a.scustid ); END; */ BEGIN shanglifeecif.init_individual_main(); end ;