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 ;