Discussion :

[Tino13]

Bonjour,

j'ai un problème en PL/SQL et j'avoue que je commence à bloquer...

J'aimerais pouvoir faire un truc du genre :

Code :

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

1
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION MA_FONCTION(LST_VAL IN VARCHAR2) RETURN INTEGER IS
    CURSOR MON_CURS IS
        SELECT * FROM MA_TABLE WHERE MON_CHAMP IN (LST_VAL);
    ...
BEGIN
    ...
END;

où le champ MON_CHAMP est de type NUMBER.
En gros, je voudrais pouvoir interpréter la liste de nombre que je passe en paramètres sous forme de clause IN.

Est-ce possible ? Si non, comment arriver à ce type de résultat, sachant que LST_VAL peut contenir un nombre variable de valeurs ? (Existe-t-il des fonctions PL/SQL avec un nombre d'arguments variable ?)

Merci d'avance

[PpPool]

Bonjour,

je serai toi j'alimenterai une temporary table avec tes nombres, au préalable dans ta procédure stockée.
si je te propose cette solution c'est que j'ai déjà lu plusieurs posts sur des pbs de perfs sur les listes + une limite il me semble à 50 items (à vérifier).

@

[orafrance]

en effet, la temporary table est trop souvent oubliée alors que c'est une solution qui peut s'avérer très performante

[laurentschneider]

une approche est d'utiliser une collection

Code :

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

1
2
3
4
5
6
7
8
9
10
 
create or replace type t_n as table of number;
/
 
create or replace function f (lst_val t_n) return number is 
cursor c is select * from emp where empno member of lst_val;
begin
return 0;
end;
/

ensuite tu peux faire

Code :

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

1
2
 
select f(t_n(1,2,3)) from dual;

[laurentschneider]

ou

Code :

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

1
2
3
4
5
6
7
8
 
create or replace function f (lst_val t_n) return number is 
cursor c is select * from emp where empno in (
  select * from table(lst_val));
begin
return 0;
end;
/

[SheikYerbouti]

Voir sans table intermédiaire:
http://fdegrelle.over-blog.com/article-1694534.html

[Tino13]

Ouah !
Tout d'abord merci à vous tous pour le nombre de réponses en si peu de temps !

J'ai finalement utilisé la solution de SheikYerbouti sans table intermédiaire, car ce que je n'avais pas dit est que cette fonction doit être appelée depuis un module Java et que je ne voulais pas laisser de type défini dans la Base...

J'ai donc implémenté 2 méthodes SPLIT() et DYNA_LISTE() que j'appelle pour transformer ma liste sous forme de VARCHAR2 en liste sys.dbms_debug_vc2coll

Ca fonctionne correctement , mais je sens que c'est un peu 'de la bidouille'.

J'aime bien la solution basée sur un type TABLE OF NUMBER mais je n'arrive pas à la mettre en place.
L'idéal serait pour moi de commencer ma fonction par :

Code :

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

TYPE MON_TYPE IS TABLE OF NUMBER

puis de caster ma liste passée sous forme de VACHAR2 (obligé car appelé depuis Java) en MON_TYPE

J'en suis là :

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE OR REPLACE FUNCTION MA_FONCTION(LST_VAL IN VARCHAR2) RETURN INTEGER IS
    TYPE MON_TYPE IS TABLE OF NUMBER
    CURSOR MON_CURS IS
        SELECT * FROM MA_TABLE WHERE MON_CHAMP IN (CAST(LST_VAL AS MON_TYPE));
    ...
BEGIN
    ...
END;

J'ai une erreur de compilation, ce que je peux comprendre.
Suis-je sur la bonne voie, ou dois-je abandonner cette solution ?

[McM]

Autre solution simple vu que monchamp est un NUMBER :
LST_VAL : doit être de la forme 'nb1;nb2;nb3...nb158'

Code :

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

1
2
3
SELECT * 
FROM MA_TABLE 
WHERE INSTR(';'|| MON_CHAMP || ';', ';'|| LST_VAL ||';') > 0;

[Tino13]

Pas mal !
Ca fonctionne relativement vite en plus, ce qui est etonnant.

Par contre j'ai juste corrigé l'ordre dans la requête :

Code :

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

1
2
3
4
 
SELECT * 
FROM MA_TABLE 
WHERE INSTR(';'|| LST_VAL ||';', ';'|| MON_CHAMP || ';') > 0;

Merci beaucoup à tous

[McM]

Oops, écrit à l'arrache, comme d'hab..