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

CREATE OR REPLACE PROCEDURE shanglifeecif.riskcode_statistics_channel() IS
DECLARE
BEGIN
      DELETE FROM shanglifeecif.riskcode_statistics_channel; 
	 insert into shanglifeecif.riskcode_statistics_channel (
		id,
		kindName,
		khnum,
		tatolprem,
		salecom,
		salecomname
	) --险种大类统计
		SELECT 
			row_number()over(),
			temp.kindName,
			temp.khnum,
			temp.tatolprem,
			temp.salecom,
			temp.salecomname
		FROM (
			SELECT 
				crt.riskcategoriesname AS kindName,
				count(DISTINCT scustid) AS khnum,
				sum(risk) AS tatolprem,
				crt.salecom AS salecom,
				crt.salecomname AS salecomname
			FROM shanglifeecif.customer_risk_temp crt 
			WHERE crt.riskcategoriesname IS NOT null
			and crt.salecom is not null 
			GROUP BY crt.riskcategoriesname,crt.salecom,crt.salecomname
		) temp;
	dbms_output.put_line('riskcode_statistics_channel函数跑批完成！');
	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	;