Scinder un champ dont le texte est délimité par un séparateur et réaffecter ses valeurs à la clé primaire

**ceuce** · 23/06/2017, 12h46

Bonjour à tous,

Je cherche à faire quelque chose qui à priori a déjà été demandé, mais mes recherches et quelques tests sont tout de même restés infructueux, donc je cherche de nouvelles pistes pour me creuser les méninges

je souhaiterai passer d'une table qui se présente comme ça :

Code :

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

1
2
3
4
5
 
 
ID	STOP_IDS
1279	StopPoint:4:51,StopPoint:4:47,StopPoint:4:77,StopPoint:4:74,StopPoint:4:84,StopPoint:4:82,StopPoint:4:76
2729	StopPoint:4:55,StopPoint:4:40,StopPoint:4:32,StopPoint:4:34

à une table qui se présenterait comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
ID	STOP_IDS
1279	StopPoint:4:51
1279	StopPoint:4:47
1279	StopPoint:4:77
1279	StopPoint:4:74
1279	StopPoint:4:84
1279	StopPoint:4:82
1279	StopPoint:4:76
2729	StopPoint:4:55
2729	StopPoint:4:40
2729	StopPoint:4:32
2729	StopPoint:4:34

J'ai trouvé une discussion qui correspond tout à fait à ce que je cherche à faire etj e m'en suis inspirée pour essayer d'utiliser les fonctionnalités de PIPELINED, mais je ne maîtrise pas et j'ai du mal à comprendre l'exemple donné pour l'adapter

Je suis preneuse de toutes suggestions ou pistes, ou autres discussions sur le sujet à côté desquelles je serai passée!

Je vous remercie par avance de l'aide que vous pourrez m'apporter

**McM** · 23/06/2017, 17h41

Bonjour,

C'est possible en SQL directement, avec le XML, ça dépend un peu des données.

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
WITH W AS (SELECT 1279	ID, 'StopPoint:4:51,StopPoint:4:47,StopPoint:4:77,StopPoint:4:74,StopPoint:4:84,StopPoint:4:82,StopPoint:4:76' stop_ids FROM dual
UNION ALL SELECT 2729	, 'StopPoint:4:55,StopPoint:4:40,StopPoint:4:32,StopPoint:4:34' FROM dual
)
SELECT w.ID, EXTRACTVALUE(COLUMN_VALUE,'e')
FROM w, TABLE(XMLSEQUENCE(XMLTYPE('<e><e>' || REPLACE(w.stop_ids,',','</e><e>') || '</e></e>').EXTRACT('e/*')))
 
ID	EXTRACTVALUE(COLUMN_VALUE,'E')
1279	StopPoint:4:51
1279	StopPoint:4:47
1279	StopPoint:4:77
1279	StopPoint:4:74
1279	StopPoint:4:84
1279	StopPoint:4:82
1279	StopPoint:4:76
2729	StopPoint:4:55
2729	StopPoint:4:40
2729	StopPoint:4:32
2729	StopPoint:4:34

**McM** · 23/06/2017, 18h14

Tant qu'on y est, autant donner l'autre méthode en plsql avec PIPELINED

Il faut créer un type en base

Code :

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

CREATE OR REPLACE TYPE TYP_TAB_CHAINE AS TABLE OF VARCHAR2(200);

Fonction qui prend une chaine et la découpe suivant les ','

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE FUNCTION wmcFL (p_data IN VARCHAR2)  
RETURN  TYP_TAB_CHAINE PIPELINED AS
	v_chaine VARCHAR2(4000) := p_data;
BEGIN
	IF p_data IS NULL THEN RETURN; END IF;
	v_chaine := v_chaine ||',';
	WHILE v_chaine IS NOT NULL
  LOOP
  	PIPE ROW(SUBSTR(v_chaine, 1, INSTR(v_chaine, ',')-1)); 
    v_chaine := SUBSTR(v_chaine, INSTR(v_chaine, ',')+1);       
	END LOOP;
  RETURN;
END;

l'appel simple se fait comme celà

Code :

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

SELECT * FROM TABLE (wmcfl('123,456,789'));

Avec ta table de points

Code :

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

1
2
3
4
5
WITH W AS (SELECT 1279	ID, 'StopPoint:4:51,StopPoint:4:47,StopPoint:4:77,StopPoint:4:74,StopPoint:4:84,StopPoint:4:82,StopPoint:4:76' stop_ids FROM dual
UNION ALL SELECT 2729	, 'StopPoint:4:55,StopPoint:4:40,StopPoint:4:32,StopPoint:4:34' FROM dual
)
SELECT w.ID, j.COLUMN_VALUE
FROM w, TABLE (wmcfl(w.stop_ids)) j;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
ID	COLUMN_VALUE
1279	StopPoint:4:51
1279	StopPoint:4:47
1279	StopPoint:4:77
1279	StopPoint:4:74
1279	StopPoint:4:84
1279	StopPoint:4:82
1279	StopPoint:4:76
2729	StopPoint:4:55
2729	StopPoint:4:40
2729	StopPoint:4:32
2729	StopPoint:4:34

**ceuce** · 27/06/2017, 09h02

Merci beaucoup, je vais exploiter ces 2 pistes et reviens vers vous

**ceuce** · 27/06/2017, 09h58

C'est vraiment parfait!

J'arrive au résultat escompté en faisant la requête suivante

Code :

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

1
2
3
4
5
6
7
 
insert into matable_detail(id_ss_ligne, stop_ids)	
WITH W AS (SELECT id_ss_ligne, stop_ids FROM matable)
SELECT w.id_ss_ligne, j.COLUMN_VALUE
FROM w, TABLE (wmcfl(w.stop_ids)) j;	
 
commit;

Un grand merci!!!
J'aurai appris quelque chose, je comprends un peu mieux l'utilisation de PIPELINED

Bonne journée,

Cécile

**SQLpro** · 27/06/2017, 10h37

Ou encore de manière récursive :

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
with cte_w (id, stop_ids) as
(
select 1279, 'StopPoint:4:51,StopPoint:4:47,StopPoint:4:77,StopPoint:4:74,StopPoint:4:84,StopPoint:4:82,StopPoint:4:76'
  from dual
 union all 
select 2729, 'StopPoint:4:55,StopPoint:4:40,StopPoint:4:32,StopPoint:4:34'
  from dual
)
  ,  cte_x (id, stopid, stop_ids) as
(
select id
     , substr(stop_ids, 1, instr(stop_ids, ',') - 1)
     , substr(stop_ids, instr(stop_ids, ',') + 1, length(stop_ids) - instr(stop_ids, ','))
  from cte_w
 union all
select id
     , substr(stop_ids, 1, instr(stop_ids, ',') - 1)
     , substr(stop_ids, instr(stop_ids, ',') + 1, length(stop_ids) - instr(stop_ids, ','))
  from cte_x
 where instr(stop_ids, ',') > 0
)
select id, stopid
  from cte_x
 union all
select id, stop_ids
  from cte_x
 where instr(stop_ids, ',') = 0;

A +

**ceuce** · 29/06/2017, 19h02

Bonjour,

Je réouvre mon post car j'ai un petit plus à apporter à cette demande, mais j'ai encore des progrès à faire pour maitriser l'utilisation de pipe row et tout ce qui va avec...

Je me retrouve maintenant avec une table comme ceci

Code :

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

1
2
3
4
 
ID	STOP_IDS                                                                                                          SEQUENCE
1279	StopPoint:4:51,StopPoint:4:47,StopPoint:4:77,StopPoint:4:74,StopPoint:4:84,StopPoint:4:82,StopPoint:4:76    1,2,3,4,5,6,7
2729	StopPoint:4:55,StopPoint:4:40,StopPoint:4:32,StopPoint:4:34                                                   1,2,3,4

Et voudrait donc une table comme ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
ID	STOP_IDS           SEQUENCE
1279	StopPoint:4:51     1
1279	StopPoint:4:47     2     
1279	StopPoint:4:77     3
1279	StopPoint:4:74     4
1279	StopPoint:4:84     5
1279	StopPoint:4:82     6
1279	StopPoint:4:76     7
2729	StopPoint:4:55     1
2729	StopPoint:4:40     2
2729	StopPoint:4:32     3
2729	StopPoint:4:34     4

Impossible de m'y retrouver, savoir si je dois plutôt modifier le type, la fonction, ou mon sql pour arriver à mes fins. J'ai fait une tentative tout à fait rocambolesque qui n'a pas du tout fait ce que je pensais. Et en y réfléchissant ça parait logique

Code :

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

1
2
3
4
5
6
7
 
insert into matable_detail(id_ss_ligne, stop_id,stop_sequence)	
WITH W AS (SELECT id_ss_ligne, stop_ids,stop_sequences FROM matable)
SELECT w.id_ss_ligne, j.COLUMN_VALUE,k.COLUMN_VALUE
FROM w, TABLE (wmcfl(w.stop_ids)) j, TABLE (wmcfl(w.stop_sequences)) k;	
 
commit;

J'ai également tenté de modifier la fonction en rajoutant un paramètre, une nouvelle chaine de caractères, mais ensuite dans la boucle, je me retrouve coincée car je ne comprends pas le fonctionnement de pipe row

De plus je ne saurai comment récupérer le résultat retourné par la fonction. Column_value permet de récupérer la valeur, mais peut-on demander un column.value(2) par exemple?

Un nouvelle fois merci à tous ceux qui pourront m'aiguiller un peu

Cécile

**McM** · 30/06/2017, 11h30

Alors là, il faut retourner un objet qui contient 2 types (2 colonnes, 1 en varchar et 1 en number)
On crée l'objet et le type correspondant à une collection de cet objet

Code :

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

1
2
CREATE OR REPLACE TYPE TYP_REC_CHAINE AS OBJECT (stp VARCHAR2(200), seq NUMBER)
CREATE OR REPLACE TYPE TYP_TAB_CHAINE AS TABLE OF TYP_REC_CHAINE;

La procédure en pipeline va renvoyer l'objet pour chaque ligne que tu veux, avec en paramètre tes 2 chaines à découper

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
CREATE OR REPLACE FUNCTION wmcFL (p_stp IN VARCHAR2, p_seq IN VARCHAR2)  
RETURN  TYP_TAB_CHAINE PIPELINED AS
	v_chaine1 VARCHAR2(4000) := p_stp;
	v_chaine2 VARCHAR2(4000) := p_seq;
  v_stp VARCHAR2(200); v_seq NUMBER;
BEGIN
	IF p_stp IS NULL THEN RETURN; END IF;
	v_chaine1 := v_chaine1 ||',';  	v_chaine2 := v_chaine2 ||',';
	WHILE v_chaine1 IS NOT NULL OR v_chaine2 IS NOT NULL
  LOOP
  	v_stp := SUBSTR(v_chaine1, 1, INSTR(v_chaine1, ',')-1);     v_seq := SUBSTR(v_chaine2, 1, INSTR(v_chaine2, ',')-1);
    v_chaine1 := SUBSTR(v_chaine1, INSTR(v_chaine1, ',')+1);       v_chaine2 := SUBSTR(v_chaine2, INSTR(v_chaine2, ',')+1);       
  	PIPE ROW(TYP_REC_CHAINE(stp => v_stp, seq => v_seq)); 
	END LOOP;
  RETURN;
END;

J'ai géré le cas où la séquence ou les stoppoint n'ont pas le même nombre de données

Ensuite il suffit d'appeler la procédure comme avant, il suffit juste de considérer l'objet comme une table avec les colonnes correspondant au type défini (stp et seq)

Code :

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

1
2
3
4
5
WITH W AS (SELECT 1279	ID, 'StopPoint:4:51,StopPoint:4:47,StopPoint:4:77,StopPoint:4:74,StopPoint:4:84,StopPoint:4:82,StopPoint:4:76' stop_ids, '1,2,3,4,5,6' sequences FROM dual
UNION ALL SELECT 2729	, 'StopPoint:4:55,StopPoint:4:40,StopPoint:4:32,StopPoint:4:34', '1,2,3,4' FROM dual
)
SELECT w.ID, j.stp, j.seq
FROM w, TABLE (wmcfl(w.stop_ids, w.sequences)) j;

Résultat (j'ai enlevé la séquence 7 de la première ligne pour montrer aussi ce cas.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
ID	STP	SEQ
1279	StopPoint:4:51	1
1279	StopPoint:4:47	2
1279	StopPoint:4:77	3
1279	StopPoint:4:74	4
1279	StopPoint:4:84	5
1279	StopPoint:4:82	6
1279	StopPoint:4:76	
2729	StopPoint:4:55	1
2729	StopPoint:4:40	2
2729	StopPoint:4:32	3
2729	StopPoint:4:34	4

**ceuce** · 30/06/2017, 12h37

Merci beaucoup! C'est impeccable

J'avais essayé pas mal de choses, dont créer un type avec 2 arguments, mais je ne savais plus comment m'en sortir avec la commande pipe row...
Cela dit je n'étais pas si loin que ça de votre solution dc je ne suis pas mécontente lol

Au final j'avais essayé de tricher en élaborant une fonction qui allait retourner en sortie un tableau avec un champ unique ou le stop et la séquence auraient été concaténés, ça donnait ça (mais sans succès)

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
 
CREATE OR REPLACE FUNCTION TRANSPORT.wmcFL_2 (p_data IN VARCHAR2,p_data_2 IN VARCHAR2)
RETURN  TYP_TAB_CHAINE PIPELINED AS
	v_chaine VARCHAR2(4000) := p_data;
	v_chaine_2 VARCHAR2(4000) := p_data_2;
	v_arret VARCHAR2(200) := null;
	v_seq VARCHAR2(200) := null;
	v_result VARCHAR2(200) := null;
BEGIN
	IF p_data IS NULL THEN RETURN; END IF;
	v_chaine := v_chaine ||',';
	v_chaine_2 := v_chaine_2 ||',';
	WHILE v_chaine IS NOT NULL and v_chaine_2 IS NOT NULL
  LOOP
	v_seq:=SUBSTR(v_chaine_2, 1, INSTR(v_chaine_2, ',')-1);
    v_arret:=SUBSTR(v_chaine, 1, INSTR(v_chaine, ',')-1);
	v_result:= v_seq || '_' || v_arret;
  	PIPE ROW(v_result);
    v_chaine := SUBSTR(v_arret, INSTR(v_chaine, ',')+1);
	v_chaine_2 := SUBSTR(v_arret, INSTR(v_chaine_2, ',')+1);
	END LOOP;
  RETURN;
END;
/

Je ne suis pas arrivée à gérer la boucle (me retournait le mm nb d'enregistrement que ds le tableau initial et à mon avis ce que je mettais dans la commande pipe row n'était pas ce qui est attendu

Quoiqu'il arrive un grand grand merci pour votre savoir et votre réactivité!

Scinder un champ dont le texte est délimité par un séparateur et réaffecter ses valeurs à la clé primaire [11g]

PL/SQL Oracle

Discussions similaires

Partager

Partager