Discussion :

[stof]

Bonjour,

Je suis dans une procédure stockée.
Suivant la valeur de ma variable V_PARAM, la liste des identifiants en paramètre va être variable (cf exemple ci-dessous).

Une liste, ça ne marche pas :

Code :

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

1
2
3
4
select t.champ1, t.champ2
from MATABLE t 			
where t.champ3 in 
(select case when V_PARAM=3 then (120, 2) else (5) end from dual)

Une valeur simple, ça marche (mais c'est pas ce que je veux)

Code :

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

1
2
3
4
select t.champ1, t.champ2
from MATABLE t 			
where t.champ3 in 
(select case when V_PARAM=3 then 120 else 5 end from dual)

Si c'est pas possible, je suis aussi preneur d'une solution qui me permette carrément de faire des sélect différents de ce type... à l'intérieur d'une boucle for :

Code :

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

1
2
3
4
5
6
7
8
9
for c_table in (
--cas1 :
select t.champ1, t.champ2
from MATABLE t 			
where t.champ3 in (120, 2) 
--cas2 ?
)loop
--bla, bla
end loop;

Merci pour votre aide.

[skuatamad]

Effectivement tu ne peux pas utiliser CASE avec de multiples valeurs, ou du moins je ne vois pas comment à part remplacer IN par plusieurs OR.

Si c'est pas possible, je suis aussi preneur d'une solution qui me permette carrément de faire des sélect différents de ce type

Tu peux utiliser la syntaxe OPEN FOR et générer une requête dynamiquement :

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
SQL> create table t as
  2  select 1 as col from dual union all
  3  select 2        from dual union all
  4  select 3        from dual
  5  /
 
Table created.
 
SQL> CREATE OR REPLACE procedure p (p_param IN number, c OUT sys_refcursor)
  2  as
  3    l_query varchar2(400);
  4  begin
  5    l_query := 'select * from t where 1 = 1 ';
  6    if p_param = 3 then
  7      l_query := l_query || ' and col in (1,2) ';
  8    else
  9      l_query := l_query || ' and col = 3 ';
 10    end if;
 11    open c for l_query;
 12  end;
 13  /
 
Procedure created.
 
SQL> show err
No errors.
SQL> var rc refcursor
SQL> execute p (3,:rc)
 
PL/SQL procedure successfully completed.
 
SQL> print rc
 
       COL
----------
         1
         2
 
SQL> execute p (1,:rc)
 
PL/SQL procedure successfully completed.
 
SQL> print rc
 
       COL
----------
         3

Mais bon c'est la version rapide sans bind variable, si la nécessité finale est d'exécuter la requête une fois dans un job nocturne c'est valable mais en environnement OLTP, il faut utiliser les binds variables et utiliser une technique plus élaborée comme Varying in lists
Dans ton cas ça pourrait donner :

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
SQL> create or replace context my_ctx using my_ctx_procedure
  2  /
 
Context created.
 
SQL> create or replace procedure my_ctx_procedure ( p_str in varchar2 )
  2  as
  3  begin
  4    dbms_session.set_context ( 'my_ctx', 'txt', p_str );
  5  end;
  6  /
 
Procedure created.
 
SQL> create or replace view IN_LIST
  2  as
  3   select trim( substr (txt,
  4                       instr (txt, ',', 1, level  ) + 1,
  5                       instr (txt, ',', 1, level+1)
  6                     - instr (txt, ',', 1, level) -1 ) ) as token
  7     from (select ','||sys_context('my_ctx','txt')||',' txt
  8            from dual)
  9  connect by level <= length(sys_context('my_ctx','txt'))
 10                             -length(replace(sys_context('my_ctx','txt'),',',''))+1
 11  /
 
View created.
 
SQL> CREATE OR REPLACE procedure p (p_param IN number, c OUT sys_refcursor)
  2  as
  3  begin
  4    if p_param = 3 then
  5      my_ctx_procedure( '1,2' );
  6    elsif p_param = 1 then
  7      my_ctx_procedure( '3' );
  8    else
  9      my_ctx_procedure( '1,2,3' );
 10    end if;
 11    open c for select * from t where col in (select * from IN_LIST);
 12  end;
 13  /
 
Procedure created.
 
SQL> show err
No errors.
SQL> var rc refcursor
SQL> execute p (3,:rc)
 
PL/SQL procedure successfully completed.
 
SQL> print rc
 
       COL
----------
         1
         2
 
SQL> execute p (1,:rc)
 
PL/SQL procedure successfully completed.
 
SQL> print rc
 
       COL
----------
         3
 
SQL> execute p (8,:rc)
 
PL/SQL procedure successfully completed.
 
SQL> print rc
 
       COL
----------
         1
         2
         3

[stof]

Merci pour ta réponse.

1°/Ca confirme qu'on peut donc pas faire avec les CASE

2°/J'avais oublié, mais ça je connais les OPEN FOR, donc ça semble faisable

3°/Je suis désolé mais là je ne comprend absolument rien Je ne reconnais plus du tout l'exemple simple que j'ai donné avec.
-job nocturne : c'est un terme technique? Quel rapport, en fait non ces traitements vont tourner en journée
-j'ai quand-même vaguement essayé de suivre le truc, mais la procédure my_ctx_procedure a des erreurs de compilation à cause du CREATE OR REPLACE context my_ctx USING my_ctx_procedure où j'ai insufficient privileges
-mais où se trouve ma requête dans tout ça?

[skuatamad]

Tu peux peut être aussi te faire une CTE de paramétrage ou -1 correspondrait au ELSE du CASE, quelque chose comme :

Code :

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

1
2
3
4
5
6
7
8
with param (
	select -1 as c_param, 5 as val from dual union all
	select 3            , 120      from dual union all
	select 3            , 2        from dual
)
select t.champ1, t.champ2
  from MATABLE t 			
 where t.champ3 in (select val from param where c_param = v_param)

-job nocturne : c'est un terme technique? Quel rapport, en fait non ces traitements vont tourner en journée

Je me suis mal exprimé, je voulais dire que sans bind variable la requête sera reparsée à chaque fois que la liste des valeurs soumises sera différentes.
Donc si cette requête est fortement utilisée la base parsera inutilement de nombreuses fois la requête.
Si cette requête est assez peu exécutée, c'est moins problématique.

CREATE OR REPLACE context my_ctx USING my_ctx_procedure où j'ai insufficient privileges

CREATE CONTEXT

To create a context namespace, you must have CREATE ANY CONTEXT system privilege.

-mais où se trouve ma requête dans tout ça?

Dans la procédure P :

Code :

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

open c FOR SELECT * FROM t WHERE col IN (SELECT * FROM IN_LIST);

[islamov2000]

Code :

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

1
2
3
4
select * from table t
where t.table like 
decode(&v_param,3,%2%,--si &v_param = 3 alors comprendre 120 et 2
       5)--sinon 5

bon courage

[stof]

Ah, mais attendez, il y a mieux!!

Code :

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

1
2
3
4
select t.champ1, t.champ2
from MATABLE t 			
where t.champ3 in 
(select * from table(SPLITLST(V_MY_LIST))))

avec :

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
create or replace FUNCTION SPLITLST (
	p_list IN VARCHAR2,
	p_del IN VARCHAR2 Default ','
)
Return SPLIT_TABLE_STR pipelined
Is 
    l_idx pls_integer; 
    l_list varchar2(32767) := p_list;
    l_value varchar2(32767);
Begin
    loop 
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then 
            pipe row(substr(l_list,1,l_idx-1)); 
            l_list := substr(l_list,l_idx+length(p_del));
        else 
        	pipe row(l_list); 
        	exit; 
        end if; 
    end loop;
Return;
End splitlst;

C'est pas mal ça non? J'ai juste à définir ma liste V_MY_LIST en fonction des différents cas '1, 20, 150' dans un cas, '5' dans l'autre, etc.

[skuatamad]

Oui ça fonctionne aussi, c'est la version "If you are in Oracle 8i" du lien "Varying in lists" proposé ci-dessus.

[stof]

A peu de choses près oui, c'est vrai

Bon, par contre c'est bien beau de passer '1, 20, 150' en dur, mais c'est autre chose d'aller chercher une liste d'integer dans une table suivant certains critères à la place...

[skuatamad]

mais c'est autre chose d'aller chercher une liste d'integer dans une table suivant certains critères à la place...

Qu'est ce que tu veux dire par là ?
Si ce sont des id stockés dans une table, il y a forcément moyen de faire plus simple et plus performent que concaténer puis déconcaténer. Donne nous peut être un exemple réaliste de ce que tu veux faire.

Sinon pour concaténer en liste des valeurs d'une colonne, c'est par là :
String Aggregation Techniques

[stof]

OK, bon, pour résumer :
-si c'est 2 requêtes différentes suivant les valeurs de "V_PARAM", je fais un OPEN FOR
-si c'est une condition sur une liste dont les valeurs vont dépendre de "V_PARAM", je peux certes faire selon certaines solutions proposées ici mais plus logiquement, il faut surtout faire une table de correspondance COR_champ3 qui ressemble à

Code :

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

1
2
3
4
5
COR_champ3
V_PARAM    champ3
3               120
3               2
1               5

Code :

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

1
2
3
4
select t.champ1, t.champ2
from MATABLE t 			
where t.champ3 in 
(select champ3 from COR_champ3 where V_PARAM = 3)