Mysql 函数与存储过程

-- 创建函数
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();