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)
BEGIN
	DECLARE 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 DO
		FETCH 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 */
			BEGIN
				DECLARE 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 DO
						FETCH cursor_detail INTO singleRssiPass, singlePwPass;
						IF Done2 =  0 THEN
							/*SELECT singleRssiPass,singlePwPass;*/
							IF singleRssiPass = 1 THEN
								SET totalRssiPass = totalRssiPass + 1;
							END IF;

							IF singlePwPass = 1 THEN
								SET 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;
	
	BEGIN
		DECLARE 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 DO
			FETCH cursor_dd INTO singleMacId,singleRssiPass,singlePwPass;
			IF Done3 = 0 THEN
				IF singleRssiPass = 0 OR singlePwPass = 0 THEN
					SET passFlag = 0;
					SET Done3 = 0;
					/*SELECT '跳出';*/
				END IF;
			END IF;
		END WHILE;
		CLOSE cursor_dd;

		IF passFlag = 1 THEN
			SET p_ispass = 1;
		ELSE
			SET p_ispass = 0;
		END IF;
		SELECT p_ispass;
	END;
	
END