老男孩旗下品牌:网校平台

老男孩IT教育,只培养技术精英

全国免费咨询电话:400-609-2893
10分钟轻松学会mysql 存储过程 循环

    /    2017-07-13

Sql代码  收藏代码

-- 数据修复存储过程执行 

-- 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 ; 


(0)

分享至