mysql 存储过程 计算报表
把用例执行情况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 ;
