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

CREATE OR REPLACE PROCEDURE shanglifeecif.effective_customer() -- 创建主存储过程
IS 
DECLARE
	scount int
BEGIN
	 DELETE FROM shanglifeecif.effectivecustomer WHERE fadateY = to_char(SYSDATE,"yyyy");
	 INSERT INTO shanglifeecif.effectivecustomer(
	  		esid,
	  		fadateY,
	  		custclass,
	  		cnum
	) 
	SELECT 
		to_char(SYSDATE,"yyyy")||si.custclass,
		to_char(SYSDATE,"yyyy"),	
	   si.custclass,
	   count(*) AS cnum 
   FROM  
		shanglifeecif.individual si  WHERE si.Label91 IS NULL AND si.custclass IS NOT NULL AND si.custclass <> '0' GROUP BY si.custclass;
	dbms_output.put_line('effective_customer函数跑批完成！');
	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;