-- 创建函数
DROP FUNCTION IF EXISTS getHighRiskLevel;
CREATE FUNCTION getHighRiskLevel(v_str VARCHAR(350)) RETURNS INT(10)
BEGIN
DECLARE r int(1) DEFAULT 0;
DECLARE c int(20) DEFAULT 0;
SELECT sum(LOCATE(dh.name,v_str)) into c from t_dic_highrisk dh WHERE dh.statue=0 and dh.level=1;
if c >0 then
set r = 1;
else
SELECT sum(LOCATE(dh.name,v_str)) into c from t_dic_highrisk dh DELETE FROM t_sys_module WHERE module_name ='HIS取诊断数据' and scn='sys_hisupload_any';
INSERT INTO `t_sys_module` VALUES ((SELECT (MAX(s.id)+1) FROM t_sys_module s), '', 'HIS取诊断数据', '1', 'sys_hisupload_any', '0');
SELECT * FROM t_sys_module WHERE scn='sys_manualupload_dh'
WHERE dh.statue=0 and dh.level=2;
if c >0 then
set r = 2;
else
SELECT sum(LOCATE(dh.name,v_str)) into c from t_dic_highrisk dh WHERE dh.statue=0 and dh.level=3;
if c >0 then
set r = 3;
else
set r = 0;
end if;
end if;
end if;
RETURN r;
END;
-- 创建存储过程
DROP PROCEDURE IF EXISTS updateChiClass;
CREATE PROCEDURE updateChiClass()
BEGIN
DECLARE done_id BIGINT(20) DEFAULT 0;
DECLARE classid varchar(100);
DECLARE numm varchar(100);
DECLARE c CURSOR FOR SELECT id FROM t_chi_school s;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done_id = NULL;
OPEN c;
FETCH c INTO classid;
WHILE(done_id is not null) DO
select COUNT(*) INTO numm
from t_chi_person p left join t_arc_person a on p.mother_id=a.id
left join t_chi_school s on p.nursery_org=s.id left join t_chi_class c on p.chi_class_org=c.id
where (p.leave_org_flag is null or p.leave_org_flag=0) and p.statue=0 and p.is_end=0
and s.id in (classid);
UPDATE t_chi_school ss SET ss.students_num=numm,ss.students_arch_num=numm WHERE ss.id=classid;
FETCH c into classid;
END WHILE;
CLOSE c;
END;
call updateChiClass();