把用例执行情况mysql表汇总起来
proc_write_report 汇总执行用例表中的测试数据 写入report 表，report表包括字段
report_id(自增)execution_flag,class_name,method_name, module,case_name,execution_amount, pass_amount,fail_amount,fail_reason,`time`,`comment`
DELIMITER $$
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(180) 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_fail_amount INT DEFAULT 0; -- 失败用例数量
DECLARE var_fail_reason VARCHAR(2000) DEFAULT NULL; -- 失败原因
DECLARE var_comment VARCHAR(2000) DEFAULT NULL; -- 备注
DECLARE var_sql_string VARCHAR(2000) DEFAULT NULL; -- sql 语句
SET @var_cass_name = p_class_name;
SET @var_method_name = p_method_name;
-- 获取成功用例数
SET @var_sql_string = CONCAT("select * 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_pass = 1 ");
-- SELECT @var_sql_string;
CALL p_get_select_row_number(@var_sql_string,@var_pass_amount);
-- SELECT @var_pass_amount;
-- 获取失败用例数
SET @var_sql_string = CONCAT("select * 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_pass = 0 ");
-- SELECT @var_sql_string;
CALL p_get_select_row_number(@var_sql_string,@var_fail_amount);
-- SELECT @var_fail_reason;
-- 获取用例模块中文名称
SET @var_sql_string = CONCAT("SELECT DISTINCT test_module_name FROM ",p_table_name," WHERE execution_flag = '",p_execution_flag,"' AND test_method = '",p_method_name,"'");
-- select @var_sql_string;
CALL proc_get_table_column_content(@var_sql_string,@var_module);
-- SELECT @var_module;
-- 获取用例方法中文名称
SET @var_sql_string = CONCAT("SELECT DISTINCT test_case_name FROM ",p_table_name," WHERE execution_flag = '",p_execution_flag,"' AND test_method = '",p_method_name,"'");
-- select @var_sql_string;
CALL proc_get_table_column_content(@var_sql_string,@var_case_name);
-- SELECT @var_module;
-- select * from `execution_send_express_sf`;
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_pass = 0 and is_execution = 1");
-- SELECT @var_sql_string;
CALL proc_get_table_column_content(@var_sql_string,@var_fail_reason);
-- SELECT @var_fail_reason ;
IF (SELECT COUNT(*) FROM report WHERE execution_flag = p_execution_flag
AND class_name = p_class_name AND method_name = p_method_name ) THEN
UPDATE report SET class_name = p_class_name, method_name = p_method_name,
module = @var_module, case_name = @var_case_name,
execution_amount = (@var_pass_amount + @var_fail_amount), pass_amount = @var_pass_amount , fail_amount = @var_fail_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 ;
ELSE
INSERT INTO report(execution_flag,class_name,method_name,
module,case_name,execution_amount,
pass_amount,fail_amount,fail_reason,`time`,`comment`)
VALUES (p_execution_flag,p_class_name,p_method_name,
@var_module,@var_case_name,(@var_fail_amount + @var_pass_amount),
@var_pass_amount,@var_fail_amount,@var_fail_reason,NOW(),@var_comment );
END IF;
END$$
DELIMITER ;
获取查询语句影响的行数，输入查询，输出行数
DELIMITER $$
USE `test_cases`$$
DROP PROCEDURE IF EXISTS `proc_get_select_row_number`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_get_select_row_number`(IN p_select_sql VARCHAR(500), OUT p_count INT(8) )
BEGIN
-- 声明
DECLARE cnt INT DEFAULT 0;
DECLARE stmt VARCHAR(200);
-- 赋值
SET @stmt = CONCAT(p_select_sql);
-- prepare
PREPARE s1 FROM @stmt;
-- 执行
EXECUTE s1;
-- 获取查询影响的行数
SET @cnt = FOUND_ROWS();
-- 输出影响函数
SELECT @cnt INTO p_count ;
END$$
DELIMITER ;
proc_get_table_column_content存储过程，
把某个字段里面查找出来的多个值，循环取出，赋值追加拼接到另外一个变量里面去,输入的查询语句最多只有一个字段
DELIMITER $$
USE `test_cases`$$
DROP PROCEDURE IF EXISTS `proc_get_table_column_content`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_get_table_column_content`(IN select_sql_string VARCHAR(1000), OUT return_string VARCHAR(5000))
BEGIN
DECLARE no_more_contents INT DEFAULT 0;
DECLARE var_id INT;
-- 保存单个内容
DECLARE var_content VARCHAR(200) DEFAULT NULL;
-- 保存所有类容
DECLARE var_all_content VARCHAR(5000) DEFAULT NULL;
-- 保存查询语句
DECLARE var_sql VARCHAR(1000) DEFAULT NULL;
-- 定义游标
DECLARE dept_csr CURSOR FOR SELECT * FROM tmp_table;
-- 错误处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_contents=1;
-- 使用临时表
-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS tmp_table;
-- 组装创建临时表语句
SET var_sql = CONCAT('CREATE TEMPORARY TABLE tmp_table ',select_sql_string);
SET @var_sql=var_sql;
-- select var_sql;
-- 预处理要执行的动态SQL
PREPARE stmt FROM @var_sql;
-- 执行SQL语句
EXECUTE stmt;
-- 释放掉预处理段
DEALLOCATE PREPARE stmt;
SET no_more_contents=0;
-- 打开游标
OPEN dept_csr;
contentloop: LOOP
FETCH dept_csr INTO var_content;
-- 如果没有值，退出循环
IF no_more_contents = 1 THEN
LEAVE contentloop;
END IF;
-- select var_content;
-- 循环取值，并且把不同的值追加赋给同一个变量
-- SELECT var_content;
-- SELECT var_all_content AS A;
IF var_all_content IS NULL THEN
SET var_all_content = var_content;
ELSEIF (var_content IS NOT NULL) && (LENGTH(var_content)!=0) THEN
SET var_all_content = CONCAT(var_all_content,'、',var_content);
END IF;
-- SELECT var_all_content AS B;
-- select var_all_content;
-- 循环结束 退出循环
IF no_more_contents = 1 THEN
LEAVE contentloop;
END IF;
-- SELECT var_all_content AS A;
END LOOP contentloop;
-- 关闭游标
CLOSE dept_csr;
SET no_more_contents=0;
SELECT var_all_content INTO return_string;
DROP TEMPORARY TABLE tmp_table;
END$$
DELIMITER ;
