-- 删除存储过程 DROPPROCEDURE IF EXISTS proc_employee_getCount -- 创建存储过程 CREATEPROCEDURE proc_employee_getCount(out n int) BEGIN SELECTCOUNT(\*) FROM employee ; END --MYSQL调用存储过程 CALL proc_employee_getCount(@n);
-- 带输入参数的存储过程:
-- 删除存储过程 DROPPROCEDURE IF EXISTS proc_employee_findById; -- 创建存储过程 CREATEPROCEDURE proc_employee_findById(in n int) BEGIN SELECT \*FROM employee where id=n; END -- 定义变量 SET@n=1; -- 调用存储过程 CALL proc_employee_findById(@n);
-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除 DROPPROCEDURE IF EXISTS pro_upd_z_vac_sta_num; delimiter // -- 创建存储过程 CREATEPROCEDURE pro_upd_z_vac_sta_num() BEGIN -- 定义变量 DECLARE p INTDEFAULT0; DECLARE cur_id_num VARCHAR(20); DECLARE cur_batch VARCHAR(20); DECLARE cur_com_id INT; DECLARE cur_com_nam VARCHAR(30); DECLARE sel_sta_num INT; -- 定义游标,并将sql结果集赋值到游标中 DECLARE cur_vac_inf CURSORFORSELECT id_number,batch FROM u_vaccination_info WHERE has_statis=0; -- 声明当游标遍历完后将标志变量置成某个值 DECLARE CONTINUE HANDLER FORNOT FOUND SET p=1; -- 打开游标 OPEN cur_vac_inf; -- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致 FETCH cur_vac_inf INTO cur_id_num,cur_batch; -- 当s不等于1,也就是未遍历完时,会一直循环 WHILE p<>1 DO loop_lable: LOOP -- 获取社区名称 SELECT `community` INTO cur_com_nam FROM e_resident_info WHERE id_num=cur_id_num; -- 获取社区编号 SELECT `id` INTO cur_com_id FROM e_community_info WHERE `name`=cur_com_nam; IF NOT ISNULL(cur_com_id) THEN LEAVE loop_lable; END IF; FETCH cur_vac_inf INTO cur_id_num,cur_batch; END LOOP loop_lable; -- 获取查询社区统计信息是否存在 SELECTCOUNT(*) INTO sel_sta_num FROM z_vaccination_statistics WHERE `id`=cur_com_id; IF sel_sta_num<1THEN INSERTINTO z_vaccination_statistics(`id`,`first_dose_num`,`first_dose_ratio`,`second_dose_num`,`second_dose_ratio`,`third_dose_num`,`third_dose_ratio`,`statis_date`) VALUES(cur_com_id,0,0,0,0,0,0,NOW()); END IF; IF cur_batch LIKE'%第一剂次%'THEN UPDATE z_vaccination_statistics SET `first_dose_num`=`first_dose_num`+1where `id`=cur_com_id; UPDATE u_vaccination_info SET `has_statis`=1where `id_number`=cur_id_num; ELSEIF cur_batch LIKE'%第二剂次%'THEN UPDATE z_vaccination_statistics SET `second_dose_num`=`second_dose_num`+1where `id`=cur_com_id; UPDATE u_vaccination_info SET `has_statis`=1where `id_number`=cur_id_num; ELSE UPDATE z_vaccination_statistics SET `third_dose_num`=`third_dose_num`+1where `id`=cur_com_id; UPDATE u_vaccination_info SET `has_statis`=1where `id_number`=cur_id_num; END IF; -- 将游标中的值再赋值给变量,供下次循环使用 FETCH cur_vac_inf INTO cur_id_num,cur_batch; -- 当s等于1时表明遍历以完成,退出循环 END WHILE; -- 关闭游标 CLOSE cur_vac_inf; END // delimiter ;