dgtis-modules-data/一期存储过程（最终版）/init_customerno_salecom_relation.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.init_customerno_salecom_relation() IS
DECLARE
BEGIN
 
-- 用户 渠道关系表
	insert into shanglifeecif.customerno_salecom_relation (
		customerno,
		salecomname,
		salecom,
		gender,
		label1,
		label2,
		label3,
		label4,
		label9,
		label10,
		label21,
		Label67,
		Label69,
		Label70,
		Label71,
		Label74,
		label77,
		Label80,
		Label96,
		Label97,
		Label100,
		Label101,
		Label103,
		Label104,
		Label105,
		Label106
		) 
	SELECT   t.customerno , t.salecomname ,t.salecom   ,i.gender    ,		i.label1,
		i.label2,
		i.label3,
		i.label4,
		i.label9,
		i.label10,
		i.label21,
		i.Label67,
		i.Label69,
		i.Label70,
		i.Label71,
		i.Label74,
		i.label77,
		i.Label80,
		i.Label96,
		i.Label97,
		i.Label100,
		i.Label101,
		i.Label103,
		i.Label104,
		i.Label105,
		i.Label106 FROM (
 SELECT     customerno , salecomname ,salecom  FROM dsj.POLICY_INFORMATION GROUP BY     customerno , salecomname,salecom
 ) t  , shanglifeecif.individual  i  WHERE t.customerno = i.scustid  ;
 
 
dbms_output.put_line('insuredno_salecom_relation 函数跑批完成！');

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;