Discussion :

[midouutn]

Bonjour tout le monde j ai une procedure qui été en oracle et la je veux l'executer sur MYSQL
Est ce que quel qu un peut m aider s il y a des truc a changer pour que ca marche
mon premiere au niveau ROWTYPE ,
voila mon code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
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 @@

merci de votre aide .

[midouutn]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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