IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Oracle Discussion :

Oracle : Case dans Where qui retourne une liste


Sujet :

Oracle

  1. #1
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2004
    Messages
    759
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2004
    Messages : 759
    Points : 159
    Points
    159
    Par défaut Oracle : Case dans Where qui retourne une liste
    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.

  2. #2
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    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

  3. #3
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2004
    Messages
    759
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2004
    Messages : 759
    Points : 159
    Points
    159
    Par défaut
    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?

  4. #4
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    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)
    Citation Envoyé par stof Voir le message
    -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.
    Citation Envoyé par stof Voir le message
    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.
    Citation Envoyé par stof Voir le message
    -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);

  5. #5
    Membre expérimenté
    Avatar de islamov2000
    Homme Profil pro
    Ingénieur d'études & developpement en informatique
    Inscrit en
    Septembre 2007
    Messages
    814
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Ingénieur d'études & developpement en informatique
    Secteur : Industrie

    Informations forums :
    Inscription : Septembre 2007
    Messages : 814
    Points : 1 717
    Points
    1 717
    Billets dans le blog
    6
    Par défaut
    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
    d'avoir Pensé à voter positivement pour ceux qui vous ont aidés et surtout à mettre si le cas.
    ça encourage.

  6. #6
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2004
    Messages
    759
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2004
    Messages : 759
    Points : 159
    Points
    159
    Par défaut
    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.

  7. #7
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    Oui ça fonctionne aussi, c'est la version "If you are in Oracle 8i" du lien "Varying in lists" proposé ci-dessus.

  8. #8
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2004
    Messages
    759
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2004
    Messages : 759
    Points : 159
    Points
    159
    Par défaut
    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...

  9. #9
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    Citation Envoyé par stof Voir le message
    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

  10. #10
    Membre habitué
    Profil pro
    Inscrit en
    Décembre 2004
    Messages
    759
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2004
    Messages : 759
    Points : 159
    Points
    159
    Par défaut
    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)

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 2
    Dernier message: 16/01/2012, 10h28
  2. Methode qui retourne une liste de DateTime
    Par justine68 dans le forum C#
    Réponses: 2
    Dernier message: 06/07/2009, 16h52
  3. Select case avec then qui retourne une variable
    Par dinette dans le forum SQL
    Réponses: 6
    Dernier message: 06/07/2009, 12h03
  4. methode serveur qui retourne une liste d'objet
    Par harbi dans le forum Windows Communication Foundation
    Réponses: 1
    Dernier message: 01/12/2008, 10h11
  5. methode qui retourne une liste d'objets du meme type
    Par anoukhan dans le forum Oracle
    Réponses: 8
    Dernier message: 12/01/2006, 19h38

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo