convertir une procédure interbase en oracle

Version imprimable

Salut j'ai besoin d'un coup de pouce pour arriver a convertir cette procedure interbase en procédure oracle, avez vous des conseils et existe t'il des llogiciels pour faire ça?

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
 
CREATE OR REPLACE PROCEDURE P_210672 AS
(
VNB NUMBER(10);
VNBDEMI NUMBER(10);
VCODE VARCHAR2(6);
VMOIS VARCHAR2(10);
VNP NUMBER(10);
VNP2 NUMBER(10);
VPATIENT VARCHAR2(10);
)
BEGIN
 
FOR SELECT DISTINCT INTERVENANT,EXTRACT(MONTH FROM DATE_HEURE) || '/01/' || EXTRACT(YEAR FROM DATE_HEURE),PATIENT
 
FROM ACTIVITE A1
 
WHERE (PRESENCE = 'P') AND (P_OU_U = 'U') AND (DATE_HEURE >= '01/05/2006') AND (DATE_HEURE < '01/06/2006')
 
 AND (CODE_2 IN ('HL'))
 
 
 
 AND A1.ACONFIRMER IS NULL 
 
AND (CODE_1 IN ('SJO','PEJO','AJ','AJO','JA','JC','JO','JS'))
 
 
 
INTO :VCODE, :VMOIS, :VPATIENT
 
DO BEGIN
 
SELECT COUNT(1) AS X,COUNT(DISTINCT PATIENT) AS Y FROM ACTIVITE A2
 
WHERE (PRESENCE = 'P') AND (P_OU_U = 'U') AND (DATE_HEURE >= '01/05/2006') AND (DATE_HEURE < '01/06/2006')
 
 AND (CODE_2 IN ('HL'))
 
 
 
AND (CODE_1 IN ('SJO','PEJO','AJ','AJO','JA','JC','JO','JS'))
 
 
 
 AND (A2.INTERVENANT = :VCODE) AND (EXTRACT(MONTH FROM A2.DATE_HEURE) + '/01/' + EXTRACT(YEAR FROM A2.DATE_HEURE) = :VMOIS)
 
 AND A2.ACONFIRMER IS NULL 
 
AND (A2.CODE_9 IS NULL OR LTRIM(RTRIM(A2.CODE_9)) = '')
 
AND (A2.PATIENT = :VPATIENT)
 
INTO :VNB ,:VNP;
 
SELECT COUNT(1) AS X, COUNT(DISTINCT PATIENT) AS Y FROM ACTIVITE A2
 
WHERE (PRESENCE = 'P') AND (P_OU_U = 'U') AND (DATE_HEURE >= '01/05/2006') AND (DATE_HEURE < '01/06/2006')
 
 AND (CODE_2 IN ('HL'))
 
 
 
AND (CODE_1 IN ('SJO','PEJO','AJ','AJO','JA','JC','JO','JS'))
 
 
 
 AND (A2.INTERVENANT = :VCODE) AND (EXTRACT(MONTH FROM A2.DATE_HEURE) + '/01/' + EXTRACT(YEAR FROM A2.DATE_HEURE) = :VMOIS)
 
 AND (A2.CODE_9 IS NOT NULL AND A2.CODE_9 = 'DJ')
 
 AND A2.ACONFIRMER IS NULL 
 
AND (A2.PATIENT = :VPATIENT)
 
INTO :VNBDEMI, :VNP2;
 
VNP = :VNP2 + :VNP;
 
INSERT INTO T_210672 (UNITE, MOIS, NOMBRE, DEMI, NBPAT,LIGNE,PATIENT)
 
VALUES (:VCODE,:VMOIS,:VNB,:VNBDEMI,:VNP,'X',:VPATIENT);
 
END
 
INSERT INTO T_210672
 
SELECT INTERVENANT, EXTRACT(MONTH FROM A2.DATE_HEURE) + '/01/' + EXTRACT(YEAR FROM A2.DATE_HEURE),PATIENT,RECAP,DATE_HEURE,NULL,NULL,NULL
 
FROM ACTIVITE A, CODEACT C
 
WHERE (PRESENCE = 'P') AND (P_OU_U = 'U') AND (DATE_HEURE >= '01/05/2006') AND (DATE_HEURE < '01/06/2006')
 
 AND (CODE_2 IN ('HL'))
 
 
 
 AND A.ACONFIRMER IS NULL 
 
AND (A.CODE_1 = C.CODE) AND (C.RECAP IS NOT NULL);
 
END

14/06/2006, 12h14
Magnus

Si tu veux automatiser la conversion de tes procédures interbase vers oracle alors je ne peux pas t'aider :wink: mais s'il s'agit d'une conversion occasionnelle alors tu peux :
- remplacer les appels à tes UDF par des TO_CHAR, TO_DATE, ...
- remplacer le bloc FOR... DO... par un curseur déclaré dans les variables
- ...
14/06/2006, 12h58
korntex5

un curseur c koi ça?
14/06/2006, 17h18
Magnus

Citation:

Envoyé par korntex5

un curseur c koi ça?

Une structure de données qui te permet de parcourir des enregistrements : cf. http://sheikyerbouti.developpez.com/...=Chap1#L1.2.22

c bon j'ai pu compiler sous toad ma procé dure merci de vos conseils :mouarf:

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
 
CREATE OR REPLACE PROCEDURE p_099595
AS
   vnb        NUMBER (10);
   vnbdemi    NUMBER (10);
   vcode      VARCHAR2 (6);
   vmois      VARCHAR2 (10);
   vnp        NUMBER (10);
   vnp2       NUMBER (10);
   vpatient   VARCHAR2 (10);
BEGIN
   FOR i IN (SELECT DISTINCT intervenant,
                             ('01/' || TO_CHAR (date_heure, 'MM/YYYY')
                             ) AS adate,
                             patient
                        FROM activite a1
                       WHERE (presence = 'P')
                         AND (p_ou_u = 'U')
                         AND (date_heure >= '01/04/2006')
                         AND (date_heure < '01/05/2006')
                         AND (code_2 IN ('HL'))
                         AND a1.aconfirmer IS NULL
                         AND (code_1 IN
                                 ('SJO',
                                  'PEJO',
                                  'AJ',
                                  'AJO',
                                  'JA',
                                  'JC',
                                  'JO',
                                  'JS'
                                 )
                             ))
   LOOP
      vcode := i.intervenant;
      vmois := i.adate;
      vpatient := i.patient;
 
      SELECT COUNT (1) AS x, COUNT (DISTINCT patient) AS y
        INTO vnb, vnp
        FROM activite a2
       WHERE (presence = 'P')
         AND (p_ou_u = 'U')
         AND (date_heure >= '01/04/2006')
         AND (date_heure < '01/05/2006')
         AND (code_2 IN ('HL'))
         AND (code_1 IN ('SJO', 'PEJO', 'AJ', 'AJO', 'JA', 'JC', 'JO', 'JS')
             )
         AND (a2.intervenant = vcode)
         AND ('01/' || TO_CHAR (a2.date_heure, 'MM/YYYY') = vmois)
         AND a2.aconfirmer IS NULL
         AND (a2.code_9 IS NULL OR LTRIM (RTRIM (a2.code_9)) = '')
         AND (a2.patient = vpatient);
 
      SELECT COUNT (1) AS x, COUNT (DISTINCT patient) AS y
        INTO vnbdemi, vnp2
        FROM activite a2
       WHERE (presence = 'P')
         AND (p_ou_u = 'U')
         AND (date_heure >= '01/04/2006')
         AND (date_heure < '01/05/2006')
         AND (code_2 IN ('HL'))
         AND (code_1 IN ('SJO', 'PEJO', 'AJ', 'AJO', 'JA', 'JC', 'JO', 'JS')
             )
         AND (a2.intervenant = vcode)
         AND ('01/' || TO_CHAR (a2.date_heure, 'MM/YYYY') = vmois)
         AND (a2.code_9 IS NOT NULL AND a2.code_9 = 'DJ')
         AND a2.aconfirmer IS NULL
         AND (a2.patient = vpatient);
 
      vnp := vnp2 + vnp;
 
      INSERT INTO t_099595
                  (unite, mois, nombre, demi, nbpat, ligne, patient
                  )
           VALUES (vcode, vmois, vnb, vnbdemi, vnp, 'X', vpatient
                  );
   END LOOP;
 
   INSERT INTO t_099595
      SELECT intervenant, '01/' || TO_CHAR (a.date_heure, 'MM/YYYY'), patient,
             recap, date_heure, NULL, NULL, NULL
        FROM activite a, codeact c
       WHERE (presence = 'P')
         AND (a.p_ou_u = 'U')
         AND (date_heure >= '01/04/2006')
         AND (date_heure < '01/05/2006')
         AND a.aconfirmer IS NULL
         AND (a.code_1 = c.code)
         AND (c.recap IS NOT NULL)
         AND (a.p_ou_u = c.p_ou_u)
         AND (c.axe = '1');
END;