Gestion des variables dans un curseur explicite

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. :)

Pièce jointe 548695

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 :oops:

Pièce jointe 548714

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

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 !

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:

---

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

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:

---

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;

---

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.

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 !

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:

---

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:

---

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:

---

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

---

Bonjour !

Désolé pour ma réponse très tardive, gérer le confinement + les enfants, les courses etc... est parfois un exercice compliqué.

Merci énormément pour ta macro, je vais tester cela dans les prochaines heures.

Une questions me taraude...

J'imagine avoir plusieurs utilisateurs qui utilisent le même fichier Excel. Je souhaites donc tracer le résultat de leur requête à chaque fois qu'il effectuent une consultation par le biais de la macro.

J'imagine donc générer un Insert dans une table, qui va stocker le résultat de la requête mais aussi :

L'ID SQL de l'utilisateur qui execute la requête.
La date à laquelle la requête a été générée (trop facile ?).
Éventuellement à partir de quel poste l'utilisateur utilise son fichier.

Je pensais éventuellement à faire plusieurs fichiers Excel, chaque fichier Excel envoie son ID utilisateur bidon que je stocke en dur dans le code vba (en tant que paramètre).

Qu'en penses-tu ? Existe t'il une méthode plus élégante pour réaliser cette "traçabilité ?"

Encore merci pour tes précieux conseils ! :zoubi:

Bonjour,

Si tu souhaites tracer alors oui tu peux mettre en place un mécanisme simple. Si tu souhaites stocker le résultat à chaque fois alors il te faudra 2 tables : une table entête qui stockera un id d'exécution, l'identifiant du fichier Excel utilisant la macro, la date. Les utilisateurs vont-ils utiliser le même utilsateur Oracle pour se connecter à la base? S'ils ont chacun leur propre utilisateur alors le stocker aussi.
La deuxième table aura en fk l'id d'exécution et le résultat de la requête car il y a plusieurs lignes.
Mais s'il n'y a pas de mise à jour de données cette trace est-elle utile?

Hello,

Merci, je pensais partir sur ce raisonnement l'ID du fichier excel, c'est le meilleur moyen de retrouver l'origine de l'execution de la requête.

Il y aura mise à jour de données environ une fois par semaine (voir plus dans certains cas), car nous recevons le carnet de commande complet à chaque fois. D'où l'intérêt d'envoyer les données dans une table.

Cela permettra de réaliser "une photo" à un instant T du carnet de commande client.

Car dans le cas de certains client (je travaille en industrie), certains approvisionneurs un peu filou annulent des lignes de commandes sans rien dire...le but est de détecter ces fameuses lignes et d'anticiper certaines chutes de besoins (ou augmentation).

Cas de figure est très dangereux du fait qu'en fin d'affaire, on risque de se retrouver avec un stock matière très important (que le client ne prendra pas en charge financièrement).

Dans un autre sens cela me permet aussi de montrer si une requête à généré ou erreur (ou non) un problème.

.....Car parfois un utilisateur ayant fait un problème d'analyse déchargera très rapidement sa responsabilité sur l'informatique... :mouarf:

Encore merci pour tes conseils et tes idées.

Je cherche à acheter un (ou plusieurs) livre sur les BDD oracle 11g2 pouvant traiter de SQL, PL/SQL, peux-tu m'en conseiller un ?