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

CREATE OR REPLACE PROCEDURE shanglifeecif.init_Individual_salecom() IS
DECLARE
BEGIN
 
-- 客户信息添加渠道
--
	UPDATE shanglifeecif.Individual si1 SET si1.SALECOM = (
	SELECT t.salecoms FROM (
			SELECT customerno   , concat_ws(',',collect_set(salecom))  AS salecoms  FROM (
				SELECT b.customerno AS customerno  ,b.salecom   AS salecom FROM 
				  shanglifeecif.individual  a ,   shanglifeecif.customerno_salecom_relation b   
			WHERE  a.scustid = b.customerno   
			
			) GROUP BY    customerno
		) t 
	
	WHERE t.customerno =  si1.scustid); 
	
 
dbms_output.put_line('init_Individual_salecom 函数跑批完成！');

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;