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

CREATE OR REPLACE PROCEDURE shanglifeecif.update_insuranceclaimthread_add_salecom() 
IS
BEGIN
	UPDATE shanglifeecif.insuranceclaimthread  insd
SET (
	insd.SALECOM,
	insd.salecomname
	)
    =(SELECT t.salecom ,  t.salecomname FROM 
		 (
		 	SELECT 
			   contno,  salecom ,  salecomname
			  FROM  dsj.policy_information t
			GROUP BY   contno  ,  salecom  ,  salecomname
		 ) t WHERE t.contno = insd.policyno 
		-- AND t.contno in('2019012400157198','2019071600119318')
     )
	dbms_output.put_line('update_insuranceclaimthread add saleCome 函数跑批完成！');
	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;