触发器小案例

古城微笑少年丶 2023-06-01 12:49 20阅读 0赞

### 触发器小案例 ###

CREATE DEFINER=`root`@`%` TRIGGER `tri_add` AFTER INSERT ON `tb_trigger` FOR EACH ROW   # 行级触发器
    BEGIN
        # 遍历数据结束标志  0: 未结束   1：结束
        declare done int default 0;
        # 需要定义接收游标数据的变量
        declare tcname varchar(50);
        # 获取所有指定版本,失败的uiFunction测试例名，并定义游标
        declare cur cursor for (select distinct tc_name from tb_report_uifunction where test_event_id in (select id from tb_test_event
                where build_id=new.build_id and testtype_id=3) and test_result=0);
        # 定义异常处理
        declare continue handler for not found set done=1;
            
        # 参数获取
        set @testEventId = new.test_event_id;   # 定义test_event_id
        set @projectId = new.project_id;    # 定义project_id
        set @buildId = new.build_id;    # 定义build_id
            
        IF new.test_type = 1 THEN   # 性能测试数据
            
            # 获取冷启动测试的apk个数，apk_number
            set @apkNumber = (select count(DISTINCT apk_package_name) from tb_report_perf_coldstart where test_event_id=@testEventId);
            
            # 获取冷启动测试 APK平均启动时长和 （所有启动时间取平均即可）
            set @csAvg = (select avg(apk_start_time) from tb_report_perf_coldstart where test_event_id=@testEventId);
            # 获取冷启动测试 APK最大启动时长和    （获取单个APK各轮最大启动时长 -> 求和）
            set @csMax = (select SUM(max_apk_start_time) from (select apk_package_name, MAX(apk_start_time) as max_apk_start_time from tb_report_perf_coldstart where test_event_id=@testEventId GROUP by apk_package_name) temp);
            
            # 获取衰退率
            # 获取tb_test_event中，执行项目，当前test_event的前一个test_event_id （用于获取上轮测试数据）
            set @preTestEventId = (select max(id) from tb_test_event where prj_id=@projectId and testtype_id=1 and id<@testEventId);
            IF @preTestEventId>0 THEN   # 如果获取到，则获取前一轮的数据
                # 定义pre_cs_total_avg
                set @preCsAvg = (select cs_total_avg from tb_report_perf_brief where test_event_id = @preTestEventId);
                # 定义pre_cs_total_max
                set @preCsMax = (select cs_total_max from tb_report_perf_brief where test_event_id = @preTestEventId);
            ELSE    # 获取没有前一轮，则将当前数据赋予前轮（前后两轮数据没变化）
                set @preCsAvg = @csAvg;
                set @preCsMax = @csMax;
            END IF;
            # 获取平均启动和与最大启动和的衰退率
            set @pectAvg = ((@csAvg-@preCsAvg)/@preCsAvg)*100;
            set @pectMax = ((@csMax-@preCsMax)/@preCsMax)*100;
    
            insert into tb_report_perf_brief(test_event_id,project_id,apk_number, cs_total_avg,cs_total_avg_pct, cs_total_max,cs_total_max_pct)
            values (
                @testEventId,   # tb_trigger中test_event_id
                @projectId, # tb_trigger中project_id
                @apkNumber, # 从coldstart详表中计算apk的数量 where test_event_id=new.test_event_id
                @csAvg, # 从coldstart详表中计算apk启动时间的平均值 where test_event_id=new.test_event_id
                @pectAvg,   # 计算apk平均值衰退
                @csMax, # 当前test_event_id的apk启动时间最大值
                @pectMax    # 计算apk最大值衰退
            );
        ELSEIF new.test_type = 3 then   # 若tb_trigger表中新添加记录中test_type=3,就向ui简表及相关失败率表中添加记录
            
            # UiFunction测试参数
            # 获取UiFunction测试，同一版本的总测试轮数（查询同一build下，e_round最大值即可）
            set @totalRound = (select max(e_round) from tb_test_event where build_id=@buildId and testtype_id = 3);
            
            # 获取同一版本下，UiFunction测试 测试例执行总个数 （不去重，同一测试例执行多次都算）
            set @totalCases = (select count(id) from tb_report_uifunction where test_event_id in (select id from tb_test_event where build_id = @buildId and testtype_id=3));
            # 获取同一版本下，UiFunction测试 测试例执行失败个数（不去重，同一测试例执行多次都算）
            set @failCases = (select count(id) from tb_report_uifunction where test_event_id in (select id from tb_test_event where build_id = @buildId and testtype_id=3) and test_result=0);
            # 获取同一版本下，UiFunction测试 测试例执行成功个数（不去重，同一测试例执行多次都算）
            set @passCases = (select count(id) from tb_report_uifunction where test_event_id in (select id from tb_test_event where build_id = @buildId and testtype_id=3) and test_result=1);
            
            # 计算同一版本，总的测试持续时长
            # 获取同一版本下，UiFunction测试 最早的开始时间
            set @minStartTime = (select MIN(start_time) from tb_test_event where build_id = @buildId and testtype_id=3);
            # 获取同一版本下，UiFunction测试 最晚的结束时间
            set @maxEndTime = (select MAX(end_time) from tb_test_event where build_id = @buildId and testtype_id=3);
            set @totalHour = (SELECT TIMESTAMPDIFF(HOUR, @minStartTime, @maxEndTime));
    
            # 检查该版本的简表记录是否以存在，如存在则更新，否者插入
            set @passRate = @passCases/@totalCases*100;
            set @uifunction_brief_id = (select id from tb_report_uifunction_brief where build_id=@buildId);
            
            IF @uifunction_brief_id>0 THEN  # 更新记录
                update tb_report_uifunction_brief set total_round=@totalRound, total_cases=@totalCases, fail_cases=@failCases, pass_rate=@passRate, total_time=@totalHour where id=@uifunction_brief_id;
            ELSE    # 插入新纪录
                insert into tb_report_uifunction_brief (build_id, total_round, total_cases, fail_cases, pass_rate, total_time) values (@buildId, @totalRound, @totalCases, @failCases, @passRate, @totalHour);
            END IF;
    
            # 往失败率表中添加记录
            # 打开游标
            OPEN cur;
                flag_loop:loop
                    # 如果 done==1 结束循环
                    IF done=1 THEN 
                        leave flag_loop; 
                    END IF;
                    
                    # 提取游标的值 多个值的时候：fetch xxx into xxx，xxx 按照定义游标result里值的顺序赋值
                    fetch cur into tcname;
                    IF done!=1 THEN
                        # 获取总的测试次数
                        set @totalCount = (select count(id) from tb_report_uifunction where tc_name=tcname and test_event_id in (select id from tb_test_event where build_id = @buildId and testtype_id=3));
                        # 获取失败测试
                        set @failCount = (select count(id) from tb_report_uifunction where tc_name=tcname and test_event_id in (select id from tb_test_event where build_id = @buildId and testtype_id=3) and test_result=0);
                        set @failRate = @failCount/@totalCount*100;
                        # 检查指定测试例与版本的失败率表记录是否以存在，如存在则更新，否者插入
                        set @uifunction_failrate_id = (select id from tb_report_uifunction_failrate where build_id=@buildId and tc_name=tcname);
                        IF @uifunction_failrate_id>0 THEN
                            update tb_report_uifunction_failrate set total_count=@totalCount, fail_count=@failCount, fail_rate=@failRate where id=@uifunction_failrate_id;
                        ElSE
                            insert into tb_report_uifunction_failrate (build_id, tc_name, total_count, fail_count, fail_rate) values (@buildId, tcname, @totalCount, @failCount, @failRate);
                        END IF;
                    END IF;
                END loop;
            CLOSE cur;
        END IF;
    END

转载于:https://www.cnblogs.com/itzlg/p/11307446.html