mysql 存储过程

刚写的，做个记录：

 

-- ---------------------------------------------------------------------------------- Routine DDL-- Note: comments before and after the routine body will not be stored by the server-- --------------------------------------------------------------------------------DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `getPercentOfPass`(IN p_batchCode varchar(255), IN p_customerCode varchar(255), OUT p_ispass INT)BEGINDECLARE Done INT DEFAULT 0;DECLARE Done2 INT DEFAULT 0;DECLARE Done3 INT DEFAULT 0;DECLARE singleMacId INT;DECLARE detailCount INT DEFAULT 0;DECLARE detailPassCount INT DEFAULT 0;DECLARE singleRssiPass INT;DECLARE singlePwPass INT;DECLARE totalRssiPass INT DEFAULT 0;DECLARE totalPwPass INT DEFAULT 0;/*  * 逻辑： *1、得到该批次号所有的mac地址， *2、循环，取出每个mac地址对应的测试明细， *  3、分析信号通过比率以及功率通过比率，各自大于等于80%，则此macid为通过     *  4、整批通过为1，有一个不通过，则返回0 */DECLARE cursor_macId CURSOR FOR SELECT DISTINCT(macId) AS macIds FROM batchdetail bd LEFT JOIN batch b ON bd.bId = b.bId WHERE b.bName = p_batchCode;DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;OPEN cursor_macId;WHILE Done = 0 DOFETCH cursor_macId INTO singleMacId;/*必须加if,否则会多执行一次*/IF Done = 0 THEN/* 得到每个mac地址对应的总的明细条数 */SELECT COUNT(1) INTO detailCount FROM batchDetail bd WHERE bd.macId = singleMacId;SET totalRssiPass = 0;/* 每个mac地址的明细条数中，能通过测试的总数 */SET totalPwPass = 0;/* 每个mac地址的明细条数中，能通过测试的总数 */SET Done2 = 0;/* 嵌套游标需要加begin end */BEGINDECLARE cursor_detail CURSOR FOR SELECT bd.rssipass, bd.pwpass FROM batchDetail bd WHERE bd.macId = singleMacId;DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done2 = 1;OPEN cursor_detail;WHILE Done2 = 0 DOFETCH cursor_detail INTO singleRssiPass, singlePwPass;IF Done2 =  0 THEN/*SELECT singleRssiPass,singlePwPass;*/IF singleRssiPass = 1 THENSET totalRssiPass = totalRssiPass + 1;END IF;IF singlePwPass = 1 THENSET totalPwPass = totalPwPass + 1;END IF;END IF;END WHILE;CLOSE cursor_detail;select totalRssiPass, totalPwPass, detailCount, totalRssiPass/detailCount, totalPwPass/detailCount;SET @ccode = p_customerCode;SET @bcode = p_batchCode;SET @macid = singleMacId;SET @createdate = now();IF totalRssiPass / detailCount >= 0.8 THEN/*SELECT ("pass") AS ispass;*/SET @rssiPass = 1;ELSE/*SELECT ("not pass") AS ispass;*/SET @rssiPass = 0;END IF;IF totalPwPass / detailCount >= 0.8 THEN/*SELECT ("pass") AS ispass;*/SET @pwPass = 1;ELSE/*SELECT ("not pass") AS ispass;*/SET @pwPass = 0;END IF;SET SQL_SAFE_UPDATES = 0;DELETE FROM detectiondata WHERE customercode= p_customerCode AND batchcode = p_batchCode;SET @insertDetectiondataSql = CONCAT('INSERT INTO detectiondata(customercode,batchcode,macid,rssipass,pwpass,createdate) VALUES(?,?,?,?,?,?)');PREPARE stmt_insertDetectiondataSql FROM @insertDetectiondataSql;EXECUTE stmt_insertDetectiondataSql USING @ccode,@bcode,@macid,@rssiPass,@pwPass,@createdate;END;END IF;END WHILE;CLOSE cursor_macId;BEGINDECLARE passFlag INT DEFAULT 1;/* 得到该批次所有macId的通过情况，有一个macid不过，则整批设为不过*/DECLARE cursor_dd CURSOR FOR SELECT macid, rssipass, pwpass FROM detectiondata WHERE batchcode = p_batchCode AND customercode = p_customerCode;DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done3 = 1;OPEN cursor_dd;WHILE Done3 = 0 DOFETCH cursor_dd INTO singleMacId,singleRssiPass,singlePwPass;IF Done3 = 0 THENIF singleRssiPass = 0 OR singlePwPass = 0 THENSET passFlag = 0;SET Done3 = 0;/*SELECT '跳出';*/END IF;END IF;END WHILE;CLOSE cursor_dd;IF passFlag = 1 THENSET p_ispass = 1;ELSESET p_ispass = 0;END IF;SELECT p_ispass;END;END