Discussion :

[AXON69]

Bonjour à tous !

Je découvre actuellement la gestion des curseurs sous Oracle, j'ai passé plusieurs années sous TRANSACT-SQL (Microsoft) auparavant.

Ma plasticité neuronale n'étant pas exceptionnelle, j'ai un peu de mal à faire le switch intellectuel .

Contexte de ma question :

J'ai un carnet de commande plusieurs lignes de commande envoyé par un client tous les débuts de semaines (Colonnes article, date, une quantité à livrer, Reste à livrer). Rien d'exceptionnel jusque la.



Notre société livre plusieurs fois dans la semaine, on nous aimerions connaitre le reste à livrer sur les commandes en question avant la prochaine réception du carnet de commande la semaine d'après.

Contrainte : La somme des BL expédiés doit décrémenter les quantité des commandes ligne par ligne dans une colonne reste à livrer en fonction des dates.

Le tout est ensuite injecté dans une autre table (similaire) qui vient accueillir le reste à livrer. Je précise que ne veux pas modifier la table d'origine (il s'agit de la table d'un ERP).

J'éprouve de grandes difficultés à garder la variable du "reste" en mémoire pour qu'elle soit traitée dans la ligne d'après (quelque chose m'échappe).

Quelqu'un peut-il m'orienter sur le moment ou je dois initialiser/calculer mes variables ?

N.B : Désolé si mon code manque de classe, j'en suis à mes balbutiements

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
49
50
51
52
53
54
55
56
57
58
59
60
 
DECLARE
 
REFERENCE T_CMDE.REFERENCE%TYPE;
NUM_CMDE_CLI T_CMDE.NUM%TYPE;
DATE_LIV T_CMDE.DATE_JOUR%TYPE;
QTE T_CMDE.QTE%TYPE;
QTE_RAL NUMBER :=0;
QTE_LAST_BL NUMBER :=16;
RESTE NUMBER :=0;
RESTE2 NUMBER :=0;
 
CURSOR CURS_ IS
 
SELECT   
         a.REFERENCE
       , a.NUM_CMDE_CLI
       , a.DATE_LIV
       , a.QTE
       , QTE_RAL
       , QTE_LAST_BL
 
FROM T_CMDE a
WHERE a.REFERENCE='ARTICLE_1' 
ORDER BY 
, TO_DATE(TO_CHAR(TO_DATE(SUBSTR(a.DATE_LIV,0,8),'yyyymmdd'),'dd-mm-yyyy')) ASC;
 
BEGIN
 
  OPEN CURSE_GS;
 
     LOOP
 
     FETCH CURSE_GS INTO  
           ID_EDI_FILE
         , REFERENCE
         , NUM_CMDE_CLI
         , DATE_LIV
         , QTE
         , QTE_RAL
         , QTE_LAST_BL
         ;
 
INSERT INTO T_CMDE_RAL 
VALUES
(
ID_EDI_FILE
, REFERENCE
, NUM_CMDE_CLI
, DATE_LIV
, QTE
, QTE_RAL
, QTE_LAST_BL
, RESTE2
);
 
 
    EXIT WHEN NOT CURSE_GS%FOUND;
 
END LOOP;

Merci par avance pour votre aide !

[vanagreg]

Bonjour,

Quelle est ta version d'Oracle? Sous Oracle en général on privilégie le SQL au PL/SQL. Le PL/SQL ne sert finalement souvent qu'à orchestrer les requêtes SQL entre elles.
Par exemple pour ton besoin ici tu peux utiliser une requête récursive si tu es sous Oracle 11g2 minimum (une clause MODEL si 10g).

Voici le résultat attendu avec une requête récursive:

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
 
with donnees_triees
as (select reference, 
           num_cmde_cli, 
           date_liv, 
           qte, 
           qte_ral,
           row_number() over (partition by reference order by to_date(date_liv, 'yyyymm')) rn
     from t_cmde
    ),
donnees_recursives(reference, num_cmde_cli, date_liv, qte, qte_ral, qte_last_bl, rn) 
as (select reference, 
           num_cmde_cli, 
           date_liv, 
           qte, 
           qte_ral,
           qte-qte_ral,
           rn
    from donnees_triees
    where rn = 1
    union all
    select dt.reference, 
           dt.num_cmde_cli, 
           dt.date_liv, 
           dt.qte, 
           dt.qte_ral,
           dr.qte_last_bl-dt.qte,
           dt.rn
     from donnees_triees dt
     join donnees_recursives dr
     on dt.reference = dr.reference and dt.rn = dr.rn + 1  
     )
select reference, 
       num_cmde_cli, 
       date_liv, 
       qte, 
       qte_ral, 
       qte_last_bl
from donnees_recursives;

J'ai fait les suppositions suivantes:
- on partionne par article (reference) car on ne se préoccupe pas par quelle commande il est livré
La de de livraison est en fait du varchar2 au format YYYYMM. Si c'est une vraie date alors il suffit simplement de trier directement dessus

[AXON69]

Bonjour,

Merci beaucoup pour ta réponse .

Du coup j'ai réussi à initialiser les valeurs comme je le souhaitai dans le curseur et réussi à obtenir le résultat voulu. J'ai encore un peu de boulot mais ça marche.

Le problème ce code doit être encapsulé dans une procédure, qui doit être lancée via Excel (lien ODBC).

La version de Oracle est la 11g. Donc il est normalement possible d'utiliser les requête récursives ?

Je vais explorer cette piste vivement puisqu'elle ne semble générer aucun insert dans une table (si je ne me trompe pas).

Question : Cette requête se lance forcément par le biais d'une procédure stockée ? Ou peut-on imaginer plus simple ?

Voici le code saisi concernant le curseur (ci-dessous).

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
 
BEGIN
 
  OPEN CURSE_GS;
 
RESTE_MEM:=QTE_LAST_BL;
 
WHILE QTE_RAL>0 LOOP
 
 
     FETCH CURSE_GS INTO  
 
           REFERENCE
         , NUM_CMDE_CLI
         , DATE_LIV
         , QTE
         , QTE_RAL
         , QTE_LAST_BL
         ;
 
IF RESTE_MEM>QTE THEN QTE_RAL:=RESTE_MEM-QTE;
ELSE QTE_RAL:=QTE-RESTE_MEM;
END IF;
 
 
 
INSERT INTO TOPADDIN.T_FOLLOW_EDI_SIMPLE 
VALUES
(
 
 REFERENCE
, NUM_CMDE_CLI
, DATE_LIV
, QTE
, QTE_RAL
, QTE_LAST_BL
, RESTE
);
 
RESTE_MEM:=QTE_RAL;
 
    EXIT WHEN NOT CURSE_GS%FOUND;
 
END LOOP;
 
CLOSE CURSE_GS;
 
END;

[vanagreg]

Tu peux utiliser une requête récursive si tu est en 11g2 (11.2.0.1, 11.2.0.2, 11.2.0.3 ou 11.2.0.4)
Si c'est pour alimenter un fichier Excel avec VBA alors tu peux directement exécuter la requête à partir d'une macro. Par contre pourquoi utiliser un lien ODBC? Il vaut mieux utiliser le driver Oracle OLE DB. J'ai déjà fait dans le passé des macros Excel qui se connectent à une base Oracle donc si tu as besoin d'aide là dessus je peux éventuellement retrouver.

[AXON69]

Il s'agit bien de la 11G2

Concernant le driver OLE DB, très honnêtement cela m'intéresse car j'envisage de créer d'autres "extractions" mais avec passage de paramètre(s) pour une exécuter d'autres procédures stockées.

Mais ne l'ayant jamais fait si tu as des exemples je suis preneur.

N.B : Je cherche aussi des exemples pour lancer des procédures avec Crystal Report (+ passages de paramètres).

Encore merci à toi pour tes lumières !

[vanagreg]

Bonjour,

Il faudra que tu télécharges le driver pour OraOLEDB (inclus dans les composants Oracle ODAC):

https://www.oracle.com/database/tech...downloads.html

Ensuite, dans ta macro Excel, il faudra que tu coches les composants ADO dans les références. Concernant le code dans la macro, voici la chaine de connexion à la base de données:

Code :

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

1
2
3
4
5
6
7
 
Set Conn = New ADODB.Connection
 
With Conn
    .ConnectionString = "PROVIDER=OraOLEDB.Oracle;USER ID=" & pUser & ";PASSWORD=" & pPwd & ";DATA SOURCE=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST = " & vServeur & ")(PORT = " & vPort & ")))(CONNECT_DATA =(SERVICE_NAME = " & vService & ")))"
    .Open
End With

Ensuite tu vas utiliser ADO pour faire tes différentes instructions SQL. Par exemple si tu veux faire un SELECT:

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
Set CommandeSQL = New ADODB.Command
With CommandeSQL
    .ActiveConnection = Conn
    .CommandText = "SELECT col1, col2 FROM tab1"
    .CommandType = adCmdText
End With
 
Set JeuEnreg = New ADODB.Recordset
Set JeuEnreg = CommandeSQL.Execute
 
Do While Not JeuEnreg.EOF
......
 
    JeuEnreg.MoveNext
Loop

Pour exécuter une procédure stockée:

'Appelle de la procédure PL/SQL soumettant la génération

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Set CommandeSQL = New ADODB.Command
With CommandeSQL
    .ActiveConnection = Conn
    .CommandText = "Nom_de_la_proc"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("p1", adInteger, adParamInput)
End With
 
p1.Value = 123
 
On Error Resume Next
CommandeSQL.Execute