mysql 存储过程 计算报表
时间：2021-07-01 10:21:17
帮助过：2人阅读
USE `test_cases`$$
DROP PROCEDURE IF EXISTS `p_write_report_3`$$
CREATE DEFINER
=`root`@`localhost`
PROCEDURE `p_write_report_3`(
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_cass_name
VARCHAR(
80)
DEFAULT NULL;
-- 测试类名
DECLARE var_method_name
VARCHAR(
80)
DEFAULT NULL;
-- 测试方法名
DECLARE var_module
VARCHAR(
80)
DEFAULT NULL;
-- 测试模块 中文
DECLARE var_case_name
INT DEFAULT 0;
-- 测试用例名称 中文
DECLARE var_pass_amount
INT DEFAULT 0;
-- 通过用例数量
DECLARE var_fail_reason
VARCHAR(
200)
DEFAULT NULL;
-- 失败原因
DECLARE var_comment
VARCHAR(
200)
DEFAULT NULL;
-- 备注
DECLARE var_sql_string
VARCHAR(
200)
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_reason);
SELECT @var_fail_reason;
SELECT GROUP_CONCAT(fail_reason)
INTO @fail_reason FROM report LIMIT
10;
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 execution_amount
= 22, pass_amount
= 22 , fail_amount
= 22,
fail_reason = ‘111‘, COMMENT
= ‘1111‘, TIME
= NOW()
WHERE execution_flag
= 201603301554 AND class_name
= p_class_name
AND method_name
= p_method_name ;
SELECT TRUE,var_fail_reason ,NOW();
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,
‘insertResult‘ ,
‘暗室逢灯‘,
‘是否‘,
22,
23,
24,
‘oooooo‘ ,NOW(),
‘ssss‘);
SELECT FALSE,var_fail_reason ,NOW();
END IF;
END$$
DELIMITER ;
CALL p_write_report_3(‘execution_remote_open‘,
‘com.fc.htgl.testcases.TestRemoteOpen‘,
‘testCustomerGetExpressError‘,
‘201604061017‘);
SELECT * FROM report
ORDER BY report_id
DESC;
SELECT * FROM execution_remote_open
ORDER BY execution_id
DESC;
mysql 存储过程 计算报表
标签：
