CREATE OR REPLACE PROCEDURE shanglifeecif.customer_risk_temp() 
IS
DECLARE
BEGIN
	  DELETE FROM shanglifeecif.customer_risk_temp;
      INSERT INTO shanglifeecif.customer_risk_temp(
	id,
	  scustid,
	  name,
	  gender,
	  idcard,
	  birthday,
	  policyno,
	  productname,
	  riskcategoriesname,
	  risk,
	  salecom,
	  salecomname
	) 
	SELECT
		row_number()over(),
		si.scustid,
		si.name,
		CASE si.gender 
			WHEN '0' THEN '男' 
			WHEN '1' THEN '女'
			END AS gender,
			si.idcard,
			to_char(si.birthday,"yyyy-MM-dd") birthday,
			sit.policyno,
			sit.productname,
			sit.risk_categories_name,
			sit.Risk,
			sit.policybelong,
			sit.salecomname
	FROM shanglifeecif.individual si 
	LEFT JOIN shanglifeecif.insurancearrangement sit ON si.scustid = sit.insuredscustid;
	dbms_output.put_line('customer_risk_temp函数跑批完成！');
		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	;