Fonction récursive prenant un VARCHAR2 puis plusieurs VARCHAR2 en paramètre

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour à tous,

Je travaille sous Oracle 10g et ne suis malheureusement pas spécialiste en PL/SQL même si j'ai déjà créé quelques fonctions.

Je vais essayer d'être clair pour vous expliquer mon besoin mais c'est un peu complexe, désolé d'avance... :? Voici le contexte :

Nous commercialisons des produits via des catalogues renouvelés chaque année. Certains produits présentés sur des catalogues passés peuvent être reconduits sur les catalogues suivants, ou pas.

Pour des questions de mise en page des catalogues, les produits d'une même gamme vendus sous différents coloris (ex. : des canapés) sont présentés au client avec une et une seule référence (appelée référence commerciale) à laquelle on associe des codes couleurs. Lorsque le client passe commande, il indique donc la référence commerciale et le code couleur. L'association "référence commerciale + code couleur" permet de retrouver quelle référence physique est concernée (une référence physique pour le canapé rouge, une autre pour le bleu).

Malheureusement, même si les références physiques qui se cachent derrière les références commerciales restent inchangées dans le temps, il n'en va pas de même pour les références commerciales : le canapé présent sur le catalogue de l'année dernière aura une référence commerciale différente de celle du même canapé reconduit sur le catalogue actuel (c'est balot, mais notre outil de production nous l'impose). Par contre, ces 2 références commerciales, qui concernent la même gamme de canapés, pointeront bien vers les mêmes références physiques en fonction des codes couleurs.

Pour compliquer l'affaire s'il en est besoin, un coloris présent sur un catalogue passé peut être abandonné sur le catalogue actuel si ses ventes ont été considérées comme insuffisantes. A contrario, de nouveaux coloris peuvent apparaitre pour cette gamme de canapés dans les catalogues à venir.

Voici à présent ce qu'on m'a demandé : nos chefs de produits voudraient pouvoir suivre la vente de la gamme de canapés (et de tous les autres produits évidemment !) via un code (que nous appellerons code modèle) qui regrouperait toutes les références commerciales successives (et donc toutes les références physiques qui leur sont rattachées) attribuées au canapé, et ce que les coloris soient toujours en vente ou qu'ils aient été abandonnés. Ce code modèle sera porté au final par les références physiques dans notre système d'information.

Je dois donc écrire un fonction PL/SQL qui prendra en paramètre une référence physique et me retournera son code modèle.

Pour faire simple, on dira que le code modèle retourné sera basé sur le code de la référence commerciale la plus récemment créée pour l'ensemble des références physiques de la gamme concernée. Ainsi, si la référence physique passée en paramètre de la fonction correspond à un colori abandonné, la référence commerciale retenue pour déterminer son code modèle ne sera pas la celle à laquelle elle était affectée mais la dernière créée, toutes références physiques de la gamme confondues.

J'ai donc besoin de connaître, en partant d'une référence physique donnée, la liste de toutes les références physiques de la gamme concernée pour au final obtenir la liste de toutes les références commerciales auxquelles elles ont été affectées. Lorsque j'aurai cette liste de références commerciales, je saurai déterminer celle que je dois conserver pour construire le code modèle attendu.

Voici le descriptif (simplifié) de la table ZREFAFF dont je dispose pour effectuer ce travail :
Code:

1 2 3 4 5 6 Name Null Type ------------ -------- ------------ ZCODSUP_0 NOT NULL VARCHAR2(18) --> code catalogue (code support) ITMREF_0 NOT NULL VARCHAR2(60) --> référence physique (item reference) ZREFCOM_0 NOT NULL VARCHAR2(24) --> référence commerciale ZREFCOMDAT_0 NOT NULL DATE --> date de création de la référence commerciale
Voici comment je peux déterminer manuellement, en partant d'une référence physique donnée, la liste exhaustive des références commerciales portant l'ensemble des références physiques de la gamme (chaque ordre reprend l'ordre complet précédent dans son IN) :

Première boucle :

Passe a1 : recherche des références commerciales contenant la référence physique passée en paramètre : 2 références commerciales retournées

Code:

SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B');

Passe a2 : recherche des références physiques portées par les références commerciales retournées par la passe a1 : 4 références physiques retournées

Code:

SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'));

Seconde boucle :

Passe b1 : recherche des références commerciales contenant les références physiques retournées par la passe a2 : 9 références commerciales retournées

Code:

SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B')));

Passe b2 : recherche des références physiques portées par les références commerciales retournées par la passe b1 : 8 références physiques retournées

Code:

SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'))));

Troisième boucle :

Passe c1 : recherche des références commerciales contenant les références physiques retournées par la passe b2 : 10 références commerciales retournées

Code:

SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B')))));

Passe c2 : recherche des références physiques portées par les références commerciales retournées par la passe c1 : 8 références physiques retournées

Code:

SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'))))));

A ce stade, je stoppe les recherches car la passe c2 me retourne un nombre de références physiques identique à celui de la passe b2 -> La liste des références commerciales que je retiendrai est donc celle retournée par la passe c1.

Il semblerait donc que j'aie besoin d'une fonction PL/SQL récursive acceptant en paramètre un VARCHAR2(60) lors de sa première exécution (-> la référence physique dont on recherche le code modèle). Lors de cette première exécution, elle effectuera le travail des passes a1 et a2 décrites ci-dessus puis s'appellera elle-même pour prendre en paramètre la liste des références physiques retournées par la passe a2. Le paramètre passé à la fonction à partir de la seconde passe sera donc une liste de VARCHAR2(60) et non plus un VARCHAR2(60) seul. Elle effectuera alors le travail des passes b1 et b2 et ainsi de suite jusqu'à ce quelle constate que les passes c2 et b2 retournent le même nombre d'enregistrements.

Dernière précision, je souhaiterais pouvoir utiliser cette fonction dans un ordre SQL du type : SELECT f_modele('MA REFERENCE PHYSIQUE') FROM dual;

Je ne sais malheureusement pas comment gérer le type de paramètre dont cette fonction a besoin pour fonctionner. Merci d'avance si êtes arrivés à me lire jusqu'au bout ! et encore plus si vous pouvez m'aider à résoudre ce problème.

N'hésitez pas à me poser des questions si je n'ai pas été clair.

Cordialement.

04/12/2013, 15h52
orafrance

pourquoi pas simplement une requête récursive avec CONNECT BY ?
04/12/2013, 16h03
mnitu

A partir de votre table essayez de fournir un jeu d’essaye avec le résultat attendu en précisant si nécessaire les règles à appliquer

Bonsoir et merci de vous pencher sur mon problème.:ccool:

@orafrance : Simplement parce que je ne connais ni la syntaxe des requêtes récursives, ni le fonctionnement de CONNECT BY (je devrais être formé d'ici quelques mois si tout va bien...), mais s'il y a moyen de s'en sortir sans PL/SQL, je suis preneur d'une solution ! Peut-être pouvez-vous vous appuyer sur le jeu d'essai ci-dessous pour que je comprenne bien de quoi il retourne ? Cela m'intéresse vivement !

@mnitu : Vous avez raison. Voici donc un jeu d'essai pour passer dans le concret. J'ai réduit la table ZREFAFF aux 2 seuls champs qui nous intéressent ici :
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 CREATE TABLE zrefaff ( ITMREF_0 VARCHAR2(60) NOT NULL, ZREFCOM_0 VARCHAR2(24) NOT NULL ); COMMIT; INSERT INTO zrefaff VALUES('465117A','HL00958T'); INSERT INTO zrefaff VALUES('424275A','5046538A'); INSERT INTO zrefaff VALUES('465117A','5046538A'); INSERT INTO zrefaff VALUES('465119N','5046538A'); INSERT INTO zrefaff VALUES('424275A','GI16515P'); INSERT INTO zrefaff VALUES('465117A','GI16515P'); INSERT INTO zrefaff VALUES('465119N','GI16515P'); INSERT INTO zrefaff VALUES('49767005','GI16515P'); INSERT INTO zrefaff VALUES('49767006','GI16515P'); INSERT INTO zrefaff VALUES('49767007','GI16515P'); INSERT INTO zrefaff VALUES('424275A','EX03624D'); INSERT INTO zrefaff VALUES('428317B','EX03624D'); INSERT INTO zrefaff VALUES('465117A','EX03624D'); INSERT INTO zrefaff VALUES('465119N','EX03624D'); INSERT INTO zrefaff VALUES('424275A','HK03816J'); INSERT INTO zrefaff VALUES('465117A','HK03816J'); INSERT INTO zrefaff VALUES('465119N','HK03816J'); INSERT INTO zrefaff VALUES('49767006','HK03816J'); INSERT INTO zrefaff VALUES('49767007','HK03816J'); INSERT INTO zrefaff VALUES('424275A','JH05638H'); INSERT INTO zrefaff VALUES('465119N','JH05638H'); INSERT INTO zrefaff VALUES('49767005','JH05638H'); INSERT INTO zrefaff VALUES('49767006','JH05638H'); INSERT INTO zrefaff VALUES('49767007','JH05638H'); INSERT INTO zrefaff VALUES('49767008','JH05638H'); INSERT INTO zrefaff VALUES('49767005','HK20444N'); INSERT INTO zrefaff VALUES('424275A','6046538A'); INSERT INTO zrefaff VALUES('428317B','6046538A'); INSERT INTO zrefaff VALUES('465117A','6046538A'); INSERT INTO zrefaff VALUES('465119N','6046538A'); INSERT INTO zrefaff VALUES('424275A','HI00064N'); INSERT INTO zrefaff VALUES('465117A','HI00064N'); INSERT INTO zrefaff VALUES('465119N','HI00064N'); INSERT INTO zrefaff VALUES('49767006','HI00064N'); INSERT INTO zrefaff VALUES('49767007','HI00064N'); INSERT INTO zrefaff VALUES('424275A','HW04027J'); INSERT INTO zrefaff VALUES('465117A','HW04027J'); INSERT INTO zrefaff VALUES('465119N','HW04027J'); INSERT INTO zrefaff VALUES('49767006','HW04027J'); INSERT INTO zrefaff VALUES('49767007','HW04027J'); INSERT INTO zrefaff VALUES('12345678','87654321'); COMMIT;
Je souhaite obtenir la liste de toutes les références commerciales (ZREFCOM_0) auxquelles les références physiques (ITMREF_0) d'une gamme donnée ont été affectées, et cela en partant d'une des références physiques de cette gamme, en l'occurence la référence physique 428317B (cf. passe a1) de mon premier post.

Concrètement, je reprends les différentes passes indiquées dans mon premier message, qui vont par paires :

- Passe a1 : elle m'indique que la référence physique 428317B a été affectée à 2 références commerciales : EX03624D et 6046538A.
- Passe a2 : les 2 références commerciales retournées par la passe a1 ont eu 4 références physiques qui leur ont été affectées : 428317B (ma référence physique de départ), 465117A, 424275A et 465119N.

J'ai à présent la liste des 4 références physiques qui ont été affectées aux mêmes références comemrciales que ma référence physique de départ. Je peux recommencer le cycle :

- Passe b1 : elle m'indique que les 4 références physiques retournées par la passe a2 ont été affectées à 9 références commerciales : HL00958T, 5046538A, GI16515P, EX03624D, HK03816J, JH05638H, 6046538A, HI00064N et HW04027J.
- Passe b2 : les 9 références commerciales retournées par la passe b1 ont eu 8 références physiques qui leur ont été affectées : 465117A, 49767005, 428317B (je retrouve encre ici ma référence physique de départ, évidemment...), 49767008, 49767007, 424275A, 465119N et 49767006.

Je recommence un cycle en partant de ces 8 références physiques :

- Passe c1 : elle m'indique que les 8 références physiques retournées par la passe b2 ont été affectées à 10 références commerciales : HL00958T, GI16515P, 5046538A, EX03624D, HK03816J, JH05638H, HK20444N, HW04027J, HI00064N et 6046538A.
- Passe c2 : les 10 références commerciales retournées par la passe c1 ont eu 8 références physiques qui leur ont été affectées : la liste retournée ici est la même que celle retrounée par la passe b2.

Le fait les passes b2 et c2 retournent un nombre de références physiques identique montre que je suis arrivé au bout des recherches : si je continue les itérations, je n'augmenterai plus le nombre de références physiques ou commerciales retournées. Mes listes sont donc exhaustives.

Dans mon exemple, je retiendrai donc la liste des références comemrciales retournées par la passe c1.

Une fois que j'aurai obtenu cette liste, je saurai sélectionner la référence commerciale qui me permettra de retourner mon code modèle.

Voilà, j'espère avoir été plus clair que dans mon premier post. N'hésitez pas à revenir vers moi si vous avez d'autres questions/besoins pour comprendre mon problème et encore merci pour votre aide.

Je vous répondrai demain jeudi car je suis en rade de PC chez moi.:aie:

Bonne soirée et encore merci.

05/12/2013, 10h49
Mafate

Bonjour,

Je suis toujours sur mon problème et j'ai effectué quelques recherches concernant les requêtes récursives et l'ordre CONNECT BY.

Si je ne me trompe pas, d'après ce que j'ai pu lire sur le net, les requêtes récursives sont apparues sous Oracle avec la 11g release 2 et je travaille sous 10g. Je suis donc apparemment coincé de ce côté-ci...

Quant à l'ordre CONNECT BY, si je comprends bien les exemples que je trouve en googlant, il semble nécessiter une dépendance hiérarchique entre les 2champs qu'il utilise. Concrètement, dans l'exemple classique "champ parent / champ enfant", l'enfant doit aussi être un parent à un instant donné, et le parent un enfant (excepté évidemment pour les parents au plus haut de la hiérarchie). Dans mon cas, cela reviendrait à dire qu'une référence physique se trouverait être aussi une référence commerciale, et inversement, or ce n'est jamais le cas : les références physiques sont affectées aux références commerciales mais la hiérarchie s'arrête là.

Ceci dit, je débute dans le domaine donc je n'ai peut-être pas tout saisi. Merci de ne pas me taper sur la tête dans ce cas-là !:aie:

Merci encore pour votre aide et bonne journée.
05/12/2013, 12h05
Mafate

Re-bonjour,

Voilà, j'ai essayé de schématiser ce que je cherche à faire, toujours à partir du même jeu de données.

http://img545.imageshack.us/img545/3503/s1s8.png

Comme on peut le voir, on part de la référence physique 428317B entourrée de rouge. On constate via la passe a1 qu'elle a été affectée aux 2 références commerciales elles aussi entourrées de rouge.

On repart ensuite de ces 2 références commerciales, à présent entourrées de bleu foncé, pour voir les références physiques qui leur sont affectées : c'est ce que nous donne la passe a2. On constate que 4 références physiques leur sont affectées (entourrées en bleu foncé dans la colonne de gauche).

On continue ce jeu de "ping-pong" tant que le nombre de références physiques retourné de cycle en cycle n'est pas stabilisé. Cette stabilisation se produit ici lors de la passe c2 qui retourne la même liste de références physiques que la passe b2. Les listes retournées n'évolueront donc plus et je peux conserver la liste de références commerciales désormais exhaustive retournée par la passe c1.

Je piocherai ensuite dans cette liste, en fonction de règles de gestion que l'on m'a fournies, la référence commerciale à conserver pour fabriquer mon code modèle et l'affecter à ma référence physique de départ entourrée en rouge. Et cette référence commerciale retenue ne sera pas forcément parmi l'une des 2 entourrées de rouge auxquelles la référence physique de départ est affectée. Par exemple, elle pourra être la HK20444N retournée par la passe c1. Par contre, cette référence commerciale sera la même qui servira à construire le code modèle de toutes les références physiques de la gamme (liste de gauche dans mon schéma). Ces références physiques auront donc au final toutes le même code modèle.

Voilà, j'espère que c'est plus clair pour vous car expliquer cela avec des mots n'est pas évident...

Bonne journée et encore merci pour votre aide.
05/12/2013, 13h44
orafrance

CONNECT BY devrait faire l'affaire, il faut que tu vois des exemples, là j'ai pas le temps de le faire pour toi. C'est dispo sur la 10g (cf doc : http://docs.oracle.com/cd/B19306_01/...queries003.htm )

Pour info : http://www.adp-gmbh.ch/ora/sql/connect_by.html
05/12/2013, 14h40
Mafate

Bonjour et merci pour ta réponse. Je vais lire les liens que tu m'as envoyés et faire des essais cet après-midi. Si je trouve la solution, je la posterai ici évidemment. Si je ne poste rien... c'est que je ne m'en sors pas !... :aie:

Bon après-midi.
05/12/2013, 17h12
orafrance

J'y ai un peu réfléchit et même en posant le problème, je ne vois pas comment le faire... pas d'algo, pas de solution... donc là, va falloir que t'arrive un peu mieux à formaliser le problème voir revoir le modèle de données parce que c'est pas catholique ton truc :mouarf:
05/12/2013, 17h32
Mafate

Effectivement ! :mouarf:

Je vais y réfléchir à tête reposée ce soir. La solution serait peut-être une fonction PL/SQL qui accepte en paramètre d'entrée l'équivalent du contenu d'un "IN". Le IN pouvant contenir une ou plusieurs valeurs, cela me permettrait d'appeler la fonction pour sa première itération avec la référence physique de départ seule (428317B), puis lors de ses itérations suivantes avec comme paramètre les différentes références physiques retournées par les itérations précédentes. Mais un tel type de paramètre est-il possible ? Où puis-je trouver des exemples si c'est le cas ?

Petite précision : je n'ai pas le niveau d'habilitation nécessaire pour créer des types sur la base (mais cela peut peut-être s'arranger si nécessaire) donc si on peut s'en passer...

En tout cas, merci de te pencher sur mon problème. :ccool: Rendez-vous demain si tu le veux bien.

Bonne soirée.

Faute de mieux, je t'es trouvé ç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 CREATE TABLE result (res VARCHAR2(60)); BEGIN DELETE FROM result; INSERT INTO result VALUES ('428317B'); WHILE SQL%ROWCOUNT <> 0 loop INSERT INTO result SELECT DISTINCT ZREFCOM_0 FROM zrefaff WHERE ITMREF_0 IN (SELECT res from result) AND ZREFCOM_0 NOT IN (SELECT res from result) order by 1; INSERT INTO result SELECT DISTINCT ITMREF_0 FROM zrefaff WHERE ZREFCOM_0 IN (SELECT res from result) AND ITMREF_0 NOT IN (SELECT res from result) order by 1; END LOOP; END; /
Je crée une table de résultat dans laquelle je mets la première référence. Ensuite, j'insère tous les ZREFCOM_0 qui ont ITMREF_0 dans la table de résultat (tes passes impairs) et j'insère tous les ITMREF_0 qui ont dans la table de résultat ZREFCOM_0 (tes passes pairs).

Je prends garde d'éviter les doublons d'où le DISTINCT et le NOT IN.

Je boucle tant qu'il y a des correspondances.

Résultat :
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 SQL> select * from result order by 1; RES ------------------------------------------- 424275A 428317B 465117A 465119N 49767005 49767006 49767007 49767008 5046538A 6046538A EX03624D GI16515P HI00064N HK03816J HK20444N HL00958T HW04027J JH05638H 18 rows selected.
C'est très laid, je te laisse donc le soin d'arranger ça comme il faut :mouarf:

J'ai réfléchis à ton problème et effectivement c'est pas evident de s'en sortir avec un connect by
Ça doit être possible en faisant un truc avec un left join du genre:
Code:

1 2 3 select SYS_CONNECT_BY_PATH(a.ITMREF_0, '/') "Path" from TEST_MM a left join TEST_MM b on (a.ZREFCOM_0=b.ZREFCOM_0) connect by NOCYCLE prior a.ITMREF_0=b.ITMREF_0;
Mais le résultat ci-dessus est un désastre, il faudrait creuser.

Je pense que l'algo que tu décrit initialement est la bonne solution, voici une façon de le mettre en place:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 DECLARE sqltext VARCHAR2(32767) := NULL; sqlarg VARCHAR2(32767) := '''428317B'''; sqlarg_new VARCHAR2(32767) := ' '; BEGIN dbms_output.put_line('sqlarg:'||sqlarg); dbms_output.put_line('sqlarg_new:'||sqlarg_new); WHILE sqlarg<>sqlarg_new LOOP sqlarg_new:=sqlarg; sqltext:='select wm_concat(''''''''|| itmref_0 || '''''''' ) itmref_0 from (SELECT DISTINCT itmref_0 FROM TEST_MM WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM TEST_MM WHERE itmref_0 IN(' || sqlarg || ')))'; execute immediate sqltext into sqlarg; dbms_output.put_line('sqlarg:'||sqlarg); dbms_output.put_line('sqlarg_new:'||sqlarg_new); END LOOP; END;
Et voici le résultat des variables en sortie:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 sqlarg:'428317B' sqlarg_new: sqlarg:'424275A','428317B','465117A','465119N' sqlarg_new:'428317B' sqlarg:'424275A','428317B','465117A','465119N','49767005','49767006','49767007','49767008' sqlarg_new:'424275A','428317B','465117A','465119N' sqlarg:'424275A','428317B','465117A','465119N','49767005','49767006','49767007','49767008' sqlarg_new:'424275A','428317B','465117A','465119N','49767005','49767006','49767007','49767008'
Il suffit de récupérer la variable "sqlarg" et de refaire une requête après le loop pour récupérer ton résultat final.

Ps: j'ai remplacé le nom de ta table zrefaff par TEST_MM

Ce n'est pas avec des requêtes hiérarchique de type Connect By que vous allez vous en sortir

D'abord on crée une type collection (vecteur ou array dans des autres langages)

Code:

1
2
3
4
5
6
7
8
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> create or replace type varchar2_tt as table of Varchar2(100)
  2  /
 
Type created

Ensuite la fonction récursive qui renvoie la collection des références commerciales

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
 
SQL> 
SQL> CREATE OR REPLACE Function RCollection (
  2    ColPhy      In Out varchar2_tt
  3  ) Return Varchar2_tt Is
  4    l_ColRefPhy     varchar2_tt;
  5    l_ColRefCom     varchar2_tt;
  6  Begin
  7    -- passe 1 collection des références commerciales
  8    Select Set(Cast(Collect(t.zrefcom_0) as varchar2_tt))
  9      Into l_ColRefCom
 10      From zrefaff t
 11     Where t.itmref_0 In (Select column_value
 12                          From Table(ColPhy));
 13    -- passe 2 collection des références physiques
 14    Select Set(Cast(Collect(t.itmref_0) as varchar2_tt))
 15      Into l_ColRefPhy
 16      From zrefaff t
 17     Where t.zrefcom_0 In (Select column_value
 18                          From Table(l_ColRefCom));
 19    -- Si ces sont les mêmes fini
 20    If l_ColRefPhy != ColPhy
 21    Then --amorce la pompe recursive
 22      l_ColRefCom := RCollection(l_ColRefPhy);
 23    End If;
 24    --
 25    return l_ColRefCom;
 26  End;
 27  /
 
Function created

Et ensuite le bloc PL/SQL de test

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
 
SQL> set serveroutput on
SQL> 
SQL> Declare
  2    result varchar2_tt;
  3    colphy varchar2_tt :=varchar2_tt('428317B');
  4  Begin
  5    -- Call the function
  6    result := rcollection(colphy => colphy);
  7    --
  8    For i In 1..result.count() Loop
  9      dbms_output.put_line(result(i));
 10    End Loop;
 11  End;
 12  /
 
HW04027J
HI00064N
JH05638H
HK03816J
GI16515P
6046538A
EX03624D
5046538A
HL00958T
HK20444N
 
PL/SQL procedure successfully completed
 
SQL>

Quelques explication sur le code :

Collect c'est une fonction de group qui constitue une collection à partir des N valeurs individuelles.
Cast force le type du résultat à la collection varchar2_tt
Set c'est pour transformer la collection dans un ensemble (éliminer les doublons)
Select column_value from table (collection) permet de interroger la collection comme une table habituelle pour récupérer les éléments un par un

Pour répondre à ce besoin :

Citation:

Envoyé par Mafate

Dernière précision, je souhaiterais pouvoir utiliser cette fonction dans un ordre SQL du type : SELECT f_modele('MA REFERENCE PHYSIQUE') FROM dual;

Supprimer le out dans

Code:

1
2
3
4
CREATE OR REPLACE FUNCTION RCollection (
  2    ColPhy      IN Out varchar2_tt
  3  )
...

Puis vous pourrez l'appeler comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select column_value from TABLE(cast(RCollection(varchar2_tt('428317B')) as varchar2_tt));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
HW04027J
HI00064N
JH05638H
HK03816J
GI16515P
6046538A
EX03624D
5046538A
HL00958T
HK20444N
 
10 rows selected.
 
SQL>

[edit]
Si vous ne pouvez pas créer le type varchar2_tt, essayez avec le type prédéfini sys.dbms_debug_vc2coll :

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
SQL> CREATE OR REPLACE FUNCTION RCollection (
  2        ColPhy      IN sys.dbms_debug_vc2coll
  3      ) RETURN sys.dbms_debug_vc2coll IS
  4        l_ColRefPhy     sys.dbms_debug_vc2coll;
  5        l_ColRefCom     sys.dbms_debug_vc2coll;
  6      Begin
  7        -- passe 1 collection des références commerciales
  8        SELECT SET(Cast(Collect(t.zrefcom_0) AS sys.dbms_debug_vc2coll))
  9          INTO l_ColRefCom
 10         FROM zrefaff t
 11        WHERE t.itmref_0 IN (SELECT column_value
 12                             FROM TABLE(ColPhy));
 13       -- passe 2 collection des références physiques
 14       SELECT SET(Cast(Collect(t.itmref_0) AS sys.dbms_debug_vc2coll))
 15         INTO l_ColRefPhy
 16         FROM zrefaff t
 17        WHERE t.zrefcom_0 IN (SELECT column_value
 18                             FROM TABLE(l_ColRefCom));
 19       -- Si ces sont les mêmes fini
 20       IF l_ColRefPhy != ColPhy
 21       Then --amorce la pompe recursive
 22         l_ColRefCom := RCollection(l_ColRefPhy);
 23       End IF;
 24       --
 25       RETURN l_ColRefCom;
 26     End;
 27     /
 
Function created.
 
SQL> select column_value from TABLE(cast(RCollection(sys.dbms_debug_vc2coll('428317B')) as sys.dbms_debug_vc2coll));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
HW04027J
HI00064N
JH05638H
HK03816J
GI16515P
6046538A
EX03624D
5046538A
HL00958T
HK20444N
 
10 rows selected.
 
SQL>

06/12/2013, 12h15
Mafate

Bonjour à tous et un grand merci pour vos nombreuses réponses !:ccool:

J'ai maintenant de la matière pour travailler, je vais m'y pencher dès cet après-midi. Je reviendrai évidemment vers vous pour un compte-rendu du résultat obtenu (ou peut-être pour d'autres questions si je patauge... :aie:)

Encore merci et à très bientôt.
06/12/2013, 14h16
mnitu

Citation:

Envoyé par skuatamad

...
Supprimer le out ...

J'avais récemment écrit

Citation:

Je suis toujours pour le "supplice du goudron et des plumes" à appliquer aux programmeurs concevant des fonctions ayant des paramètres Out en sortie.

:lol:

Bonjour,

J'aurais fait qqchose comme cela:

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
 
CREATE OR REPLACE TYPE p_ITMREF_0 AS TABLE OF VARCHAR2 (60 CHAR);
/
 
CREATE OR REPLACE TYPE p_ZREFCOM_0 AS TABLE OF VARCHAR2 (60 CHAR);
/
 
CREATE OR REPLACE FUNCTION tst_dvp (var_ref IN VARCHAR2)
   RETURN p_ZREFCOM_0
   PIPELINED
AS
   itm             p_ITMREF_0;
   zref            p_ZREFCOM_0;
   no_zref_found   INTEGER;
BEGIN
   --
   itm := p_ITMREF_0 ();
   itm.EXTEND;
   itm (itm.LAST) := var_ref;
   --
   zref := p_ZREFCOM_0 ();
 
   FOR cur_init IN (SELECT   ZREFCOM_0
                      FROM   zrefaff
                     WHERE   ITMREF_0 = var_ref)
   LOOP
      zref.EXTEND;
      zref (zref.LAST) := cur_init.ZREFCOM_0;
   END LOOP;
 
   LOOP
      --
      FOR cur_on_zref
      IN (SELECT   ITMREF_0
            FROM   zrefaff
           WHERE   ZREFCOM_0 MEMBER OF zref AND ITMREF_0 NOT MEMBER OF itm)
      LOOP
         itm.EXTEND;
         itm (itm.LAST) := cur_on_zref.ITMREF_0;
      END LOOP;
 
      --
      no_zref_found := 0;
 
      FOR cur_on_itm
      IN (SELECT   ZREFCOM_0
            FROM   zrefaff
           WHERE   ITMREF_0 MEMBER OF itm AND ZREFCOM_0 NOT MEMBER OF zref)
      LOOP
         no_zref_found := 1;
 
         IF cur_on_itm.ZREFCOM_0 NOT MEMBER OF zref
         THEN
            zref.EXTEND;
            zref (zref.LAST) := cur_on_itm.ZREFCOM_0;
         END IF;
      END LOOP;
 
      --
      EXIT WHEN no_zref_found = 0;
   END LOOP;
 
   FOR cur_return IN (SELECT   * FROM table (zref))
   LOOP
      PIPE ROW (cur_return.COLUMN_VALUE);
   END LOOP;
 
 
 
   RETURN;
END tst_dvp;
/

Que l'on peut appeler ensuite de la sorte:

Code:

1
2
 
SELECT   * FROM table (tst_dvp ('428317B'))

Si le paramètre "var_ref" passé a la fonction venait a contenir plusieurs valeurs (séparées par une virgule ou autre), cela se traite aussi sans souci.

J'ai les mêmes 10 enregistrements en retour que skuatamad, donc je ne pense pas être complètement faux.

@+
;)

09/12/2013, 09h43
mnitu

Oui il y en a plusieurs manières pour mal écrire une fonction.

Bonjour à tous,

Voilà, mon problème est résolu grace à vous. :ccool:

J'ai beaucoup appris de vos divers retours, y compris de ceux que je n'ai pas retenu dans le cas présent. Je suis sûr qu'ils me resserviront dans le futur.

J'ai basé ma fonction sur la solution présentée par mapk0. Un grand merci à lui donc ! :ccool:

Voici mon code final. N'étant pas du tout expert en PL/SQL, n'hésitez pas à vous manifester si quelque-chose vous choque ou si vous pensez qu'il est possible d'optimiser !

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
CREATE OR REPLACE FUNCTION f_modele(p_itmref IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_sql VARCHAR2(32767) := NULL;
  v_itmref_lst VARCHAR2(32767) := '''' || p_itmref || '''';
  v_itmref_lst_new VARCHAR2(32767) := ' '; -- Ne doit pas être initialisé à ''.
  v_zrefcom_lst VARCHAR2(32767) := ' ';
  v_modele VARCHAR2(19) := ' ';
  v_nb_itmref NUMBER;
BEGIN
 
  -- Sort de la fonction en retournant ' ' si l'article est absent de ZREFAFF ou présent mais avec un ZSUPVEN.ZTYPSUP_0 <> 2.
  SELECT COUNT(*) INTO v_nb_itmref FROM zrefaff zrf LEFT JOIN zsupven zsv ON zsv.zcodsup_0 = zrf.zcodsup_0 WHERE zrf.zrefcom_0 <> ' ' AND zsv.ztypsup_0 = 2 AND zrf.itmref_0 = p_itmref;
  IF v_nb_itmref = 0 THEN
    GOTO sortie;
  END IF;
 
  -- Liste des différents articles actuellement rattachés ou ayant été rattachés par le passé à toutes les références commerciales décrivant le produit passé en paramètre.
  WHILE v_itmref_lst <> v_itmref_lst_new
  LOOP
    v_itmref_lst_new := v_itmref_lst;
    v_sql := 'SELECT WM_CONCAT('''''''' || itmref_0 || '''''''') AS itmref_0 FROM (SELECT DISTINCT bb.itmref_0 FROM zrefaff aa INNER JOIN zrefaff bb ON bb.zrefcom_0 = aa.zrefcom_0 WHERE aa.itmref_0 IN(' || v_itmref_lst || ') AND aa.zrefcom_0 <> '' '')';
    EXECUTE IMMEDIATE v_sql INTO v_itmref_lst;
  END LOOP;
 
  -- Liste de toutes les références commerciales décrivant le produit passé en paramètre sur les catalogues successifs.
  v_sql := 'SELECT WM_CONCAT('''''''' || zrefcom_0 || '''''''') AS zrefcom_0 FROM (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN (' || v_itmref_lst || ') AND zrefcom_0 <> '' '')';
  EXECUTE IMMEDIATE v_sql INTO v_zrefcom_lst;
 
  -- Sélection de la référence commerciale servant de base à la construction du code modèle.
  -- On privilégie par ordre de préférence :
  -- 1 - Les références commerciales portées par des catalogues en vie ou à venir,
  -- 2 - Parmi ces supports, on privilégie les les références commerciales portées par les catalogues généraux, ou à défaut par les autres supports de type "catalogues VPC".
  -- 3 - parmi les supports ci-dessus, on retient celui dont la date de début de validité client est la plus élevée (donc soit la plus récente pour les catalogues en vie, soit la plus dans le futur pour les catalogues à venir).
  v_sql := 'SELECT ''M_'' || zrefcom_0 FROM (
    SELECT DISTINCT
      zrf.zrefcom_0,
      zrf.zcodsup_0,
      CASE WHEN TRUNC(SYSDATE - 1) <= zsv.zdfinvalcl_0 THEN 1 ELSE 2 END AS encours,      
      DECODE(zsv.zlincat_0, ''01'', 1, 2) AS zlincat_0,
      zsv.zddebvalcl_0,
      DENSE_RANK() OVER (PARTITION BY '''' ORDER BY CASE WHEN TRUNC(SYSDATE - 1) <= zsv.zdfinvalcl_0 THEN 1 ELSE 2 END, DECODE(zsv.zlincat_0, ''01'', 1, 2), zsv.zddebvalcl_0 DESC, zrefcom_0) AS rang
    FROM zrefaff zrf
    LEFT JOIN zsupven zsv ON zsv.zcodsup_0 = zrf.zcodsup_0
    WHERE zrf.itmref_0 <> '' '' AND zrf.zcodsup_0 <> '' '' AND zsv.ztypsup_0 = 2 AND zrf.zrefcom_0 IN (' || v_zrefcom_lst || ')
  ) WHERE rang = 1';
  EXECUTE IMMEDIATE v_sql INTO v_modele;
 
<< sortie >>
 
  RETURN v_modele; 
 
EXCEPTION
 
  WHEN NO_DATA_FOUND THEN RETURN ' ';
 
  WHEN OTHERS THEN RETURN 'Erreur'; 
 
END;

Avec ce code, je génère le code modèle pour plus de 72000 références physiques en moins de 25 minutes, cela répond donc à mon problème.

J'ai cependant une question : dans le code ci-dessus, la variable v_itmref_lst_new est initialisée à ' '. Si à la place je l'initialise à '' (sans espace), cela ne fonctionne plus. Quelqu'un saurait-il me dire pourquoi ?

Merci encore et bonne journée, je passe le sujet en résolu.

12/12/2013, 14h09
mnitu
Pour m'auto-citer
Code:

1 2 il y en a plusieurs manières pour mal écrire une fonction
Mais, si ça vous satisfait ... youppie!

Voir 40 message(s) de cette discussion en une page