l'équivalant procedure d'oracle en MYSQL

DELIMITER @@ CREATE PROCEDURE `Set_voms_daily_details_Stat` (v_recharge_day DATE,v_recharge_amount VARCHAR(13),v_recharge_nbr VARCHAR(10),total_amount VARCHAR(14),pctg_total_transac float,pctg_total_amount float,tamount VARCHAR(10),nbr1 VARCHAR(10)) BEGIN DECLARE STAT_CONFIG CURSOR FOR SELECT id, min_1 AS MIN_TH,max_1 AS MAX_TH FROM voms_config_stat WHERE status = 0; SELECT COUNT(*),SUM(credit/100) INTO v_recharge_nbr,total_amount FROM voms_report_file WHERE oper_state = '61' AND TRUNC(time_stamp)=TRUNC(v_recharge_day); STAT_CONF_1 STAT_CONFIG%ROWTYPE; OPEN STAT_CONFIG; LOOP FETCH STAT_CONFIG INTO STAT_CONF_1; EXIT WHEN STAT_CONFIG%NOTFOUND; SELECT SUM(credit/100), COUNT(*) INTO tamount, nbr1 FROM voms_report_file WHERE (TRUNC(time_stamp)=TRUNC(v_recharge_day)) AND oper_state = '61' AND credit/100 >= STAT_CONF_1.MIN_TH AND credit/100 <= STAT_CONF_1.MAX_TH ; pctg_total_transac := nbr1*100 / v_recharge_nbr; pctg_total_amount := nvl((tamount*100 / total_amount),0) ; INSERT INTO voms_daily_details(v_recharge_amount,v_recharge_nbr,total_amount, pctg_total_transac,pctg_total_amount,v_recharge_day) VALUES(TO_CHAR(STAT_CONF_1.MIN_TH)||'_'||TO_CHAR(STAT_CONF_1.MAX_TH),nbr1,tamount, pctg_total_transac,pctg_total_amount,TRUNC(v_recharge_day)); COMMIT; END LOOP; CLOSE STAT_CONFIG; END @@

bon j ai pu l'exécuté sans erreur mais ca marche pas

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
CREATE PROCEDURE Set_voms_daily_details_Stat() 
BEGIN
DECLARE v_recharge_day DATE;
DECLARE v_recharge_amount VARCHAR(13);
DECLARE v_recharge_nbr VARCHAR(10);
DECLARE total_amount VARCHAR(14);
DECLARE pctg_total_transac float;
DECLARE pctg_total_amount float;
DECLARE tamount VARCHAR(10);
DECLARE nbr1 VARCHAR(10);
DECLARE MIN_TH int;
DECLARE MAX_TH int;
 
DECLARE STAT_CONFIG CURSOR FOR
SELECT id, min_1 AS MIN_TH,max_1 AS MAX_TH
FROM  voms_config_stat WHERE status = 0; 
 
 
SELECT COUNT(*),SUM(credit/100) INTO v_recharge_nbr,total_amount  FROM voms_report_file 
WHERE oper_state = '61' AND
TRUNC(time_stamp)=TRUNC(v_recharge_day);
 
#STAT_CONF_1 STAT_CONFIG%ROWTYPE;
 
OPEN STAT_CONFIG;
LOOP    
        FETCH STAT_CONFIG INTO MIN_TH,MAX_TH;
		SELECT MIN_TH,MAX_TH;
     #   EXIT WHEN STAT_CONFIG%NOTFOUND;
 
        SELECT SUM(credit/100), COUNT(*) INTO tamount, nbr1 FROM voms_report_file 
        WHERE (TRUNC(time_stamp)=TRUNC(v_recharge_day))
        AND oper_state = '61' AND credit/100 >= MIN_TH AND credit/100 <= MAX_TH ;
 
        select pctg_total_transac from voms_daily_details where pctg_total_transac =  (nbr1*100 / v_recharge_nbr);
        select pctg_total_amount from voms_daily_details where pctg_total_amount = (tamount*100 / total_amount);
 
 
        INSERT INTO voms_daily_details(v_recharge_amount,v_recharge_nbr,total_amount,
        pctg_total_transac,pctg_total_amount,v_recharge_day) 
        VALUES(TO_CHAR(MIN_TH)||'_'||TO_CHAR(MAX_TH),nbr1,tamount,
        pctg_total_transac,pctg_total_amount,TRUNC(v_recharge_day));    
        COMMIT;
 
END LOOP;
CLOSE STAT_CONFIG;
END