mysql 存储过程

USE `test_cases`$$ DROP PROCEDURE IF EXISTS `proc_write_report`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_write_report`(IN p_table_name VARCHAR(80),IN p_class_name VARCHAR(80),IN p_method_name VARCHAR(80),IN p_execution_flag VARCHAR(80)) BEGIN DECLARE var_class_name VARCHAR(280) DEFAULT NULL; -- 测试类名 DECLARE var_method_name VARCHAR(180) DEFAULT NULL; -- 测试方法名 DECLARE var_module VARCHAR(180) DEFAULT NULL; -- 测试模块 中文 DECLARE var_case_name VARCHAR(180) DEFAULT NULL; -- 测试用例名称 中文 DECLARE var_pass_amount INT DEFAULT 0; -- 通过用例数量 DECLARE var_execution_amount INT DEFAULT 0; -- 通过用例数量 DECLARE var_fail_amount INT DEFAULT 0; -- 失败用例数量 DECLARE var_fail_reason VARCHAR(2000) DEFAULT NULL; -- 失败原因 DECLARE var_fail_type VARCHAR(2000) DEFAULT NULL; -- 失败类型 DECLARE var_comment VARCHAR(2000) DEFAULT NULL; -- 备注 DECLARE var_sql_string VARCHAR(2000) DEFAULT NULL; -- sql 语句 DECLARE var_sql_temp_table VARCHAR(2000) DEFAULT NULL; -- sql 语句 -- 遍历数据结束标志 DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT * FROM tmp_table_result; -- 将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 游标 -- DECLARE cur CURSOR FOR SELECT fail_type FROM execution_order_price WHERE execution_flag = ‘201606091918‘ AND test_class = ‘com.fc.htgl.testcases.TestOrderPrice‘ AND test_method = ‘testYDOrderPrice‘ GROUP BY fail_type; SET @var_cass_name = p_class_name; SET @var_method_name = p_method_name; -- 删除临时表 DROP TEMPORARY TABLE IF EXISTS tmp_table_result; -- 预处理临时表用的sql语句 SET @var_sql_temp_table = CONCAT(‘CREATE TEMPORARY TABLE tmp_table_result ‘,"select TEST_CLASS,TEST_METHOD,TEST_module_NAME,TEST_case_name,COUNT(*),fail_type,COMMENT from ",p_table_name," where execution_flag = ‘",p_execution_flag,"‘ and test_class = ‘",p_class_name,"‘ and test_method = ‘",p_method_name,"‘ group by fail_type"); -- SET @var_sql_temp_table = CONCAT(‘CREATE TEMPORARY TABLE tmp_table_result ‘,"select count(*),fail_type from ",p_table_name," where execution_flag = ‘",p_execution_flag,"‘ and test_class = ‘",p_class_name,"‘ and test_method = ‘",p_method_name,"‘ group by fail_type"); -- select @var_sql_temp_table; -- 预处理要执行的动态SQL PREPARE stmt FROM @var_sql_temp_table; -- 执行SQL语句 EXECUTE stmt; -- 释放掉预处理段 DEALLOCATE PREPARE stmt; -- select * from tmp_table_result; -- 查询下临时表 -- 打开游标 OPEN cur; -- 开始循环 read_loop: LOOP -- 提取游标里的数据； -- FETCH cur INTO var_execution_amount,var_fail_type; FETCH cur INTO var_class_name ,var_method_name,var_module,var_case_name,var_execution_amount,var_fail_type,var_comment; -- 声明结束的时候 IF done THEN LEAVE read_loop; END IF; -- 循环更新插入 -- select var_class_name ,var_method_name,var_module,var_case_name,var_execution_amount,var_fail_type,var_comment; -- 获取失败原因 SET @var_sql_string = CONCAT("select distinct actual_result from ",p_table_name," where test_method = ‘",p_method_name,"‘ and execution_flag = ‘",p_execution_flag,"‘ and test_class = ‘",p_class_name,"‘ and is_select = 1 and is_execution = 1 and fail_type = ‘",var_fail_type,"‘"); -- SELECT @var_sql_string; CALL proc_get_table_column_content(@var_sql_string,@var_fail_reason); -- 往report表中写结果 IF (SELECT COUNT(*) FROM report WHERE execution_flag = p_execution_flag AND class_name = p_class_name AND method_name = p_method_name AND fail_type = var_fail_type ) THEN -- select ‘条件存在，update‘; UPDATE report SET class_name = p_class_name, method_name = p_method_name, module = var_module, case_name = var_case_name, execution_amount = var_execution_amount, fail_reason = @var_fail_reason, `COMMENT` = var_comment, `TIME` = NOW() WHERE execution_flag = p_execution_flag AND class_name = p_class_name AND method_name = p_method_name AND fail_type = var_fail_type; ELSE -- SELECT ‘条件不存在，insert‘; INSERT INTO report(execution_flag,class_name,method_name,module,case_name,execution_amount,fail_reason,fail_type,`time`,`comment`) VALUES (p_execution_flag,p_class_name,p_method_name,var_module,var_case_name,var_execution_amount,@var_fail_reason,var_fail_type,NOW(),var_comment); END IF; END LOOP; -- 关闭游标 CLOSE cur; END$$ DELIMITER ;

 

mysql 存储过程

标签：