mysql 預存程序

標籤：

mysql預存程序功能

1、使用暫存資料表，動態資料指標只能用暫存資料表

2、暫存資料表有多個欄位，遊標同時有多個參數來接受

3、迴圈讀寫資料

4、 如果存在記錄就更新記錄，如果不存在記錄，就插入記錄

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 DEFAULT FALSE;     DECLARE cur CURSOR FOR SELECT * FROM tmp_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 EXISTS tmp_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 by fail_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語句    EXECUTE stmt;     -- 釋放掉預先處理段         DEALLOCATE PREPARE stmt;     -- select * from tmp_table_result; -- 查詢下暫存資料表        -- 開啟遊標    OPEN cur;      -- 開始迴圈    read_loop: LOOP                -- 提取遊標裡的資料；        -- FETCH cur INTO var_execution_amount,var_fail_type;        FETCH cur INTO var_class_name ,var_method_name,var_module,var_case_name,var_execution_amount,var_fail_type,var_comment;        -- 聲明結束的時候        IF done THEN            LEAVE 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_flag        AND 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  INTO report(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;         END LOOP;    -- 關閉遊標    CLOSE cur;END$$DELIMITER ;

 

mysql 預存程序