MySQL怎么test存储过程_mysql 存储过程

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(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 DEFAULTFALSE;DECLARE cur CURSOR FOR SELECT * FROMtmp_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 EXISTStmp_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 byfail_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语句

EXECUTEstmt;--释放掉预处理段

DEALLOCATE PREPAREstmt;--select * from tmp_table_result; -- 查询下临时表

--打开游标

OPENcur;--开始循环

read_loop: LOOP--提取游标里的数据；

--FETCH cur INTO var_execution_amount,var_fail_type;

FETCH cur INTOvar_class_name ,var_method_name,var_module,var_case_name,var_execution_amount,var_fail_type,var_comment;--声明结束的时候

IF done THENLEAVE 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_flagAND 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 INTOreport(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;ENDLOOP;--关闭游标

CLOSEcur;END$$

DELIMITER ;