刚写的,做个记录:
-- -------------------------------------------------------------------------------- -- 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
相关推荐
Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三...
本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考,具体如下: mysql存储函数只返回一个值。要开发返回多个值的存储过程,需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的...
mysql经典教程+mysql存储过程讲解 重点讲解Mysql的存储过程,触发器,游标的使用 对mysql不太熟的朋友可以好好学习。。。
c++实现调mysql存储过程,实现存储过程的出参入参,可以支持查询多数据返回,还有存储过程的复杂数据的增删改等
mysql中文手册.chm+mysql命令大全.chm+mysql存储过程.pdf
mysql存储过程实现分页 mysql存储过程实现分页 mysql存储过程实现分页 mysql存储过程实现分页
MySQL存储过程学习 MySQL存储过程 MySQL存储过程
本文实例讲述了MySQL存储过程的异常处理方法。分享给大家供大家参考。具体如下: mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc -> (p_first_name VARCHAR(30), -> p_last_name VARCHAR(30)...
MySQL存储过程编程 可以看看 数据库 存储过程
mysql存储过程ppt
mysql 存储过程 实战
MySQL存储过程
mysql获取两个时间相差年月日:使用mysql存储过程,实现计算两个时间的差值,并用年月日时分秒的格式输出,且从大到小为0时则不展示对应单位(如:相差1小时20秒 则展示'1时0分20秒')
mysql存储过程,存储函数练习,里面有详细代码,供大家参考
php中调用MySQL存储过程示例
mysql存储过程实例详细介绍了mysql存储过程的开发步骤
MySQL存储过程,入门简单,循序渐进,很适合自学,很详尽,很好学。
MySQL存储过程 MySQL存储过程 MySQL存储过程 MySQL存储过程 MySQL存储过程
MySQL存储过程实例教程,MySQL存储过程实例教程