欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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 ;

 

相关标签: mysql procedure