??????????洢?????д????????????????洢???????

CREATE PROCEDURE testStat(IN start_date DATE?? IN end_date DATE?? IN error_allow DOUBLE)
//?????????????????????????????????????
BEGIN
DECLARE uc_id INT DEFAULT 0;//???????ID
DECLARE done_num INT DEFAULT 0;//????????
DECLARE do_num INT DEFAULT 0;//???????
DECLARE ratio DOUBLE DEFAULT 0;//?????
DECLARE r_max DOUBLE DEFAULT 0;//????????е???????
DECLARE r_min DOUBLE DEFAULT 100;//????????е???????
DECLARE point_test DOUBLE DEFAULT 0;//?????????????
DECLARE point_tested DOUBLE DEFAULT 0;//????????????????
DECLARE point_error DOUBLE DEFAULT 0.1;//???
DECLARE uc_info CURSOR for SELECT ucid FROM tb_os_point;
DECLARE CONTINUE HANDLER FOR SQLSTATE ’02000′ SET uc_info=NULL;
OPEN uc_info;
FETCH uc_info INTO uc_id;
WHILE uc_id IS NOT NULL DO//?????????????????м????
SELECT count(*) INTO do_num //??????????
FROM tb_os_log
WHERE visit_date>start_date AND vistit_date SELECT count(*) INTO done_num //????????????
FROM tb_os_log ;
WHERE visit_date>start_date AND vistit_date SET ratio = done_num / do_num;
IF ratio > r_max THEN
SET r_max=ratio;
END IF
IF ratio < r_min THEN
SET r_min=ratio;
END IF
INSERT INTO tb_os_test(ucid??os_do??os_done??os_ratio)
VALUES(uc_id??do_num??done_num??ratio);
FETCH uc_info INTO uc_id;
END WHILE
CLOSE uc_info;
UPDATE tb_os_test SET ratio_max=r_max;//???????У???????????
UPDATE tb_os_test SET ratio_min=r_min;//???????У?????С?????
OPEN uc_info;
WHILE uc_id IS NOT NULL DO//????????????????????????????
SELECT os_ratio INTO ratio FROM tb_os_test WHERE ucid=uc_id;
SELECT total INTO point_tested
FROM tb_os_point
WHERE ucid=uc_id;//??????????????
SET point_test = (ratio-r_min)/(r_max-r_min)*100;//?????????
UPDATE tb_os_test SET os_point=point_test WHERE ucid=uc_id;
SET point_error = point_test-point_tested;//????????????
IF ABS(point_error) <= error_allow THEN
// ????????????С???????????????????????????????
//??????????
UPDATE tb_os_test SET is_pass=0 WHERE ucid=uc_id;
ELSE
//???????????
UPDATE tb_os_test SET is_pass=0 WHERE ucid=uc_id;
END IF
FETCH uc_info INTO uc_id;
END WHILE
CLOSE uc_info;
END

???????????????????????????????????????????????????е?С????????????????д?????????????????RD??PM????????????????????????????????????????????????????????????????μ?????????????????????????????????????????????????????????????????????д???????????????????д????????????洢?????

????????????????????ü??????????????????????call testStat( ?? ??0.1)???洢??????к?????????е?is_pass??μ?????????Щ??????????????????Щ??????????????????????????????????????????????????????????????????λ??Bug??

????4?????

??????????????????????????????У?????????Щ??????д???????????????????????????????洢???????????????????????????????????????????????????????????MySQL???????????????MySQL???????????????????dbForge Studio for MySQL??????????????????????????????Σ??????????????????????????????????????????????????????????????????????????????????????н??в???????????????????????У??????з????????????????У?????????????????????С?????????е???????????????????????и????????????????