mysql 存储过程 循环
程序员文章站
2022-05-08 18:37:26
...
-- 数据修复存储过程执行 -- CALL REPAIR_SRT_DATAS(); -- 数据修复存储过程 /*DELIMITER $$ -- 把默认的输入的结束符;替换成$$*/ /*DEFINER:创建者*/ DROP PROCEDURE IF EXISTS REPAIR_SRT_DATAS; DELIMITER $$ /*把默认的输入的结束符;替换成$$*/ CREATE DEFINER=`root`@`localhost` PROCEDURE REPAIR_SRT_DATAS() BEGIN DECLARE done INT DEFAULT 0; DECLARE requestCode VARCHAR(20); DECLARE dftHospitalCode VARCHAR(20); DECLARE hospitalCode VARCHAR(20); DECLARE hospitalName VARCHAR(100); /*申请记录中多个医院,设置记录中未生成结算关系*/ DECLARE requDatas CURSOR FOR (SELECT srtr.code,srtr.dm_hospital_code,srtr.dm_hospital_name FROM srt_sett_rela_tran_requ srtr LEFT JOIN srt_sett_rela_tran_set srts ON srtr.code = srts.request_code AND srtr.dm_hospital_code = srts.dm_hospital_code WHERE (SELECT COUNT(t.dm_hospital_code) FROM srt_sett_rela_tran_set t WHERE t.request_code = srtr.code AND t.dm_hospital_code = srtr.dm_hospital_code) = 0); /*游标异常后捕捉并设置变量,done 为 1 跳出循环*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*开始事务*/ START TRANSACTION; /*打开游标*/ OPEN requDatas; out_loop: LOOP /*游标向下走一步,将查询出来的值付给定义的变量*/ FETCH requDatas INTO requestCode,hospitalCode,hospitalName; IF done =1 THEN LEAVE out_loop; END IF; /*查询最先插入的医院编码*/ SELECT dm_hospital_code INTO dftHospitalCode FROM srt_sett_rela_tran_set WHERE request_code = requestCode GROUP BY dm_hospital_code ORDER BY id ASC LIMIT 1; /*修复多家医院时,未生成的结算设置记录*/ INSERT INTO srt_sett_rela_tran_set(CODE,MODEL,MATERIAL,UNIT,DM_HOSPITAL_CODE,DM_HOSPITAL_NAME,REQUEST_CODE,CREATOR,CREATE_TIME,MODIFIER,MODIFY_TIME,RESERVED1,RESERVED2,RESERVED3) SELECT CODE,MODEL,MATERIAL,UNIT,hospitalCode DM_HOSPITAL_CODE, hospitalName DM_HOSPITAL_NAME,REQUEST_CODE,REQUEST_DATE,AUDITOR,AUDIT_STATUS,AUDIT_OPINION,AUDIT_TIME,PUBLISH_TIME,CREATOR,CREATE_TIME,MODIFIER,MODIFY_TIME,RESERVED1,RESERVED2,RESERVED3 FROM srt_sett_rela_tran_set WHERE request_code = requestCode AND dm_hospital_code = dftHospitalCode /*and audit_status = 2*/; SET done = 0; END LOOP out_loop; /*关闭游标*/ CLOSE requDatas; -- SELECT dftHospitalCode; /*事务提交*/ COMMIT; END $$ DELIMITER ;
上一篇: 倾情教授仰泳技巧,让你游的顺畅瘦得开心
下一篇: 掌握游泳三要素 让水快速冲走脂肪团