| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071 |
- 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 dsj.t_customer_class b
- where b.CUSTOMER_ID = a.scustid
- ) WHERE 1=1 ;
- dbms_output.put_line('up_t_customers_class_1函数跑批完成！');
- EXCEPTION
- WHEN HIVE_EXCEPTION THEN
- INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
- WHEN Others THEN
- INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
- END;
|
