Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
21 août 2013 3 21 /08 /août /2013 15:19

L'utilisateur : "Salut, je voudrais un filtre à sélection multiple sur les pays  pour mon rapport XYZ... idéalement avec des cases à cocher" Notez que ce n'est pas un rapport XXX, on fait pas ça chez nous.

Moi :            "Désolé, notre framework est pourri, ça va pas être jouable. Par contre si ça te dit, je te laisse saisir ta sélection, par exemple sous forme de liste de code pays sur 2 caractères, séparés par des virgules. En bonus, un bouton pour afficher la liste des correspondances libellé / code pays."

L'utilisateur : "Vendu !"

 

Donc je me mets à transformer des chaînes de caractères représentant des listes en lignes, afin de pouvoir faire des filtres "IN (SELECT ...)"

Sujet classique, juste l'occasion de passer en revue les méthodes, et "expliquer" mes choix.

 

Spliter une chaîne de caractères, ça se fait de manière itérative.

 

1) Implémenter sa boucle avec CONNECT BY level

 

Toujours la même astuce : CONNECT BY level sur une sélection d'une seule ligne, ça génère des lignes à la volée, et elles sont "indexables" par level ou rownum : 

 

define p_str = 'FR,DE,PL,BE'

 

SELECT substr(str,  instr(str, del, 1, level) + 1, instr(str, del, 1, level+1) - instr(str, del, 1, level) - 1)

FROM (select '&p_str' p_str, ',' || '&p_str' || ',' str, ',' del from dual)

CONNECT BY level <= length(p_str) - length(replace(p_str, del, '')) + 1; 

 

SUBSTR(STR,IN

-------------

FR

DE

PL

BE

 

On rajoute un délimiteur en début en fin de liste (mais on pourrait aussi s'embourber dans plein de CASE pour traiter le début et la fin de liste), puis on peut extraire avec substr en s'appuyant sur la position des délimiteurs dans la chaîne.

 

A partir de 10g, on a la version bien plus élégante avec REGEXP_SUBSTR : 

 

SELECT regexp_substr('&p_str',  '[^,]+', 1, level)

FROM dual

CONNECT BY level <= length(regexp_replace('&p_str', '[^,]+')) + 1; 

 

La regexp [^,]+ matche toutes les occurences multiples de caractères qui sont différents de ','... les deux derniers paramètres sont équivalents à INSR, c'est à dire position de départ pour la recherche, et numéro d'occurence à traiter.

 

Pourquoi je n'ai pas choisi cette solution ?

- Parce que CONNECT BY level, c'est du bugusing (CONNECT BY est sensé faire des arbres, pas du café...)

- Parce que ça fait des mochetés à recoder à chaque fois, et que ça n'arrange pas mes collègues non autistes du SQL 

 

2) Implémenter sa boucle avec MODEL

 

A présent, on reprend l'extraction regexp_substr parce que c'est joli, mais on itère avec la clause MODEL

 

SELECT tk 

FROM (

  SELECT 1 n, p_str, p_str tk, length(regexp_replace(p_str, '[^,]+')) + 1 n_item

  FROM (SELECT  '&p_str' p_str FROM dual) t

)

MODEL 

DIMENSION BY (n)

MEASURES (p_str, tk, n_item)

RULES (

tk[FOR n FROM 1 TO n_item[1] INCREMENT 1] = regexp_substr(p_str[1],  '[^,]+', 1, cv(n))

);

 

Cependant, je me demande si utiliser la clause model dans du code de production ne pourrait pas être un motif valable de licenciement...

Y a un gars (qui devrait se reconnaître s'il passe par là), qui disait : "As 10g introduced MODEL, which is mostly used to impress your colleagues but seldom used in production, ..."

 

3) XML Table

 

On peut faire plein de trucs assez loufoques avec XMLTable sous Oracle.

Notamment tout simplement : 

 

SELECT *

FROM xmltable('&p_str');

 

C'est tout simple, tout compact, mais j'ai décidé de ne pas l'utiliser parce qu'il y a "XML" dans l'instruction. (Oui oui, c'est purement psychologique)

 

 

4) Pipelined function

 

Une fonction pl/sql, ça sait très bien faire des boucles, et ça sait aussi renvoyer des tableaux.

Et puis, ça peut aussi aussi retourner des lignes au fil de l'eau en étant "pipelined".

C'est tout mignon, on a l'impression de cracher des petites aiguilles empoisonnées avec une sarbacane : 

 

 

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF varchar2(4000);

/

CREATE OR REPLACE FUNCTION split_string2(p_str IN VARCHAR2, p_del IN VARCHAR2) 

RETURN varchar2_ntt

PIPELINED

IS

n_item NUMBER;

   s_str VARCHAR2(32767);

   s_ret VARCHAR2(4000);

BEGIN

  s_str := trim(both p_del from p_str);

n_item := length(s_str) - coalesce(length(replace(s_str, p_del, '')), 0) + 1;

  s_str := p_del || trim( both p_del from p_str) || p_del;

 

FOR i IN 1..n_item LOOP    

s_ret := substr(s_str, instr(s_str, p_del, 1, i) + 1, instr(s_str, p_del, 1, i + 1) - instr(s_str, p_del, 1, i) -1);

    pipe row(s_ret);

END LOOP;

END split_string2;

/

 

 

SELECT *

FROM table(split_string('&p_str',','))

 

Cela dit, bien qu'étant un grand fan de pipes, je trouve ça stupide de context switch n fois alors que l'exécution de la boucle en elle-même prend un temps négligeable.

 

 

5) Function

 

Bon ben voilà, j'ai choisi au final de faire une petite fonction toute simple (j'ai cependant gardé la méthode d'extraction pourrie à base de substr/instr), qui renvoit tout un tableau : 

 

CREATE OR REPLACE FUNCTION split_string(p_str IN VARCHAR2, p_del IN VARCHAR2) 

RETURN varchar2_ntt

IS

t_ret varchar2_ntt;

n_item NUMBER;

   s_str VARCHAR2(32767);

BEGIN

  s_str := trim(both p_del from p_str);

n_item := length(s_str) - coalesce(length(replace(s_str, p_del, '')), 0) + 1;

  s_str := p_del || trim( both p_del from p_str) || p_del;

t_ret := varchar2_ntt();

  t_ret.extend(n_item);

 

FOR i IN 1..n_item LOOP    

t_ret(i) := substr(s_str, instr(s_str, p_del, 1, i) + 1, instr(s_str, p_del, 1, i + 1) - instr(s_str, p_del, 1, i) -1);

END LOOP;

RETURN t_ret;

END split_string;

/

 

SELECT *

FROM table(split_string2('&p_str',','))

 

Il y a des gens qui vous diront qu'appeler des fonctions PL/SQL dans du SQL c'est mal parce que ça context switch.

Mais ici, dans une requête comme celle-ci, la fonction est à priori appelée une seule fois, donc sincèrement, je m'en cogne.

 

SELECT *

FROM ma_table 

WHERE code_pays IN 

  (SELECT column_value

    FROM table(split_string2('&p_str',',')))

 

 

 

 

Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires

Laurent Schneider 18/09/2013 10:07

select*from xmltable(regexp_replace('&p_str','([^,]+)','"1"'))