mysql获取存储过程名字_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