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

CREATE OR REPLACE PROCEDURE shanglifeecif.age_sex_distribution() IS
DECLARE
BEGIN
 	UPDATE shanglifeecif.Individual si1 SET si1.label4 = (SELECT CASE WHEN temp.age>=0 AND temp.age<13 THEN '儿童' WHEN temp.age>=13 AND temp.age <19 THEN '少年' WHEN temp.age >=19 AND temp.age <41 THEN '青年' WHEN temp.age >=41 AND temp.age <66 THEN '中年' ELSE '老年' END FROM (SELECT TRUNC(months_between(sysdate, si2.birthday)/12) age,si2.indid FROM shanglifeecif.Individual si2) temp WHERE temp.indid = si1.indid);
	 DELETE FROM shanglifeecif.age_sex_distribution;
	insert into shanglifeecif.age_sex_distribution (
		id,
		labelName,
		gender,
		cusNum
	) SELECT row_number()over(),t.labelName,t.gender,t.cusNum FROM (
	SELECT si.label4 labelName,si.gender,count(1) cusNum FROM shanglifeecif.individual si WHERE si.label4 IS NOT null GROUP BY si.label4,si.gender
	) t;
	dbms_output.put_line('age_sex_distribution函数跑批完成！');
		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	;