Récupérer un factoriel de ligne

**donny** · 14/10/2008, 17h55

Bonjour,

Je souhaiterai faire la même chose que ceci

http://www.developpez.net/forums/d54...ctoriel-ligne/

sachant que dans mon cas dans la colonne Role, je peux avoir 0 à N valeur;

Après recherche , il me semble que c'est en pl/sql que je pourrai m'en sortir mais je ne sais vraiment pas par ou commencer et ou m orienter car je ne suis vraiment pas expert en pl/sql.

Est-ce que vous pourriez m'aider ou me donner des pistes ?

merci de votre aides à tous

**mnitu** · 14/10/2008, 18h04

Recherchez split sur ce forum.

**donny** · 15/10/2008, 13h01

j'ai essayé ce code que tu as fait

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
 
CREATE OR REPLACE type s_str AS Object (str Varchar2(227))
/
CREATE OR REPLACE type tab_str AS TABLE of s_str
/
CREATE OR REPLACE FUNCTION tab2Str2
( p_str   Varchar2,
  p_sep   Varchar2 DEFAULT ','
) RETURN  tab_str
PIPELINED
IS
  l_tab dbms_utility.uncl_array;
  l_tablen     number;
Begin
  dbms_utility.comma_to_table(REPLACE(p_str,p_sep,','), l_tablen, l_tab);
  FOR i IN 1..l_tablen Loop
    pipe row(s_str(l_tab(i)));
  End Loop; 
  --
  RETURN ;
End;
/
SELECT * FROM TABLE( tab2Str2('one|two|three|four|five|six|seven' ,'|'))
/
 
STR
--------------------------------------------------------------------------
one
two
three
four
five
six
seven

il fonctionne mais le problème est que si tu as plus de deux lignes dans ta table alors ça marche plus;

ma table est comme ceci :

Col1 Col2
1 test
2
3 test5;test6
4 test7;test8;test9

ce que je souhaiterai c'est quelquechose comme ceci

Col1 Col2
1 test
2
3 test5
3 test6
4 test7
4 test8
4 test9

et il ne ramene pas la COl1, il plante si je la met dans la requête

j'ai fait un truc comme sa

Code :

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

SELECT * FROM TABLE( select Col1,tab2Str2(Col2,';') from matable where Col2 like '%;%')

**mnitu** · 15/10/2008, 16h27

Il y a avait des meilleurs solutions.

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
 
SQL> r
  1  With Data As
  2  (
  3    Select 1 nr, 'one|two|three|four|five|six|seven' x from dual
  4    Union All
  5    Select 2 nr, 'einz|zwei|drei|vier|five|six|seven' x from dual
  6  )
  7  Select nr, str
  8*   From Data d, Table(tab2Str2(d.x,'|'))
        NR STR
---------- ----------
         1 one
         1 two
         1 three
         1 four
         1 five
         1 six
         1 seven
         2 einz
         2 zwei
         2 drei
         2 vier
 
        NR STR
---------- ----------
         2 five
         2 six
         2 seven

**donny** · 15/10/2008, 17h08

je comprends pas trop ce code avec le with;

**donny** · 15/10/2008, 17h18

je viens d'essayer cela :

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
CREATE OR REPLACE type s_str AS Object (str1 number,str Varchar2(227))
/
CREATE OR REPLACE type tab_str AS TABLE of s_str
/
 
 
--DROP TYPE tab_str
CREATE OR REPLACE FUNCTION tab2Str2
( p_str   Varchar2,
  p_str1 number,
  p_sep   Varchar2 DEFAULT ','
) RETURN  tab_str
PIPELINED
IS
  l_tab dbms_utility.uncl_array;
  l_tablen     number;
Begin
  dbms_utility.comma_to_table(REPLACE(p_str,p_sep,','), l_tablen, l_tab);
  FOR i IN 1..l_tablen Loop
    pipe row(s_str(l_tab(i)));
     pipe row(s_str(l_tab(i)));
  End Loop; 
  --
  RETURN ;
End;
/

ma fonction ne compile pas;
si quelqu'un pouvait m'aider ou m'orienter,
je suis vraiment perdu,
je tourne en rond

**plaineR** · 15/10/2008, 17h26

Fais :

Code :

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

show err function tab2str2

Je pense qu'ensuite tu devrais comprendre ton erreur.

**mnitu** · 15/10/2008, 17h34

Envoyé par donny

je comprends pas trop ce code avec le with;

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
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as mni
 
 
SQL> 
SQL> create table Data (
  2    nr number,
  3    x  varchar2(100)
  4  )
  5  /
 
Table created
 
SQL> Insert into Data
  2    Select 1 nr, 'one|two|three|four|five|six|seven' x from dual
  3    Union All
  4    Select 2 nr, 'einz|zwei|drei|vier|five|six|seven' x from dual
  5  /
 
2 rows inserted
 
SQL> Select nr, column_value
  2    From Data d, Table(str2tbl(d.x,'|'))
  3  /
 
        NR COLUMN_VALUE
---------- --------------------------------------------------------------------------------
         1 one
         1 two
         1 three
         1 four
         1 five
         1 six
         1 seven
         2 einz
         2 zwei
         2 drei
         2 vier
         2 five
         2 six
         2 seven
 
14 rows selected
 
SQL>

**salim11** · 16/10/2008, 16h34

SALUT,

Solution avec la 10g(uniquement du sql).

L'idée :
REGEXP_SUBSTR(X[0],'[^|]+' ,1,B[ITERATION_NUMBER]+1) permet de récupérer la premier mot avant le caractère '|', après je l'ai mis dans une boucle pour récupérer les autres mots de la même phrase

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
 
SQL> SELECT * FROM DATA;
 
        NR X
---------- -----------------------------------------------------------------------------------------
         1 one|two|three|four|five|six|seven|nine
         2 einz|zwei|drei|vier|five|six|seven|eight
 
SQL> SELECT  nr, chaine
  2  FROM   DATA
  3  MODEL
  4    RETURN UPDATED ROWS
  5  PARTITION BY ( NR  )
  6  DIMENSION BY ( 0 POSITION  )
  7  MEASURES     (   CAST( ' ' AS VARCHAR2(50))  AS  CHAINE ,X ,1 B )
  8  IGNORE NAV
  9  RULES ITERATE(1000) 
 10  UNTIL ( REGEXP_SUBSTR(X[0],'[^|]+'  ,1,B[ITERATION_NUMBER]+1) is null )
 11  (  B[ITERATION_NUMBER]=   ITERATION_NUMBER+1,
 12  CHAINE[ANY] = REGEXP_SUBSTR(X[0],'[^|]+',1,B[CV()])) 
 13  ORDER BY nr,position;
 
        NR CHAINE
---------- --------------------------------------------------
         1 one
         1 two
         1 three
         1 four
         1 five
         1 six
         1 seven
         1 nine
         2 einz
         2 zwei
         2 drei
 
        NR CHAINE
---------- --------------------------------------------------
         2 vier
         2 five
         2 six
         2 seven
         2 eight
 
16 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=74)
   1    0   SORT (ORDER BY) (Cost=4 Card=2 Bytes=74)
   2    1     SQL MODEL (ORDERED) (Cost=4 Card=2 Bytes=74)
   3    2       TABLE ACCESS (FULL) OF 'DATA' (TABLE) (Cost=3 Card=2 B
          ytes=74)
 
 
 
 
SQL>

**mnitu** · 16/10/2008, 16h40

Salut Salim,

Donc tu pense que Donny en dépit d'avoir quelque difficultés avec l'instruction WITH va digérer tranquillement le Regexp plus le Model.

Sinon pourquoi pas: encore une solution en "pure" SQL.

**salim11** · 16/10/2008, 16h46

Salut mnitu,

Le but du forum c'est une personne pose ca question et tout le monde en profite

**mnitu** · 16/10/2008, 16h51

Envoyé par salim11

Salut mnitu,

Le but du forum c'est une personne pose ca question et tout le monde en profite

C'est OK

**salim11** · 20/10/2008, 17h26

Salut,

Solution améliorée,nombre d'itération dynamique.

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
 
SQL> SELECT * FROM DATA;
 
        NR X
---------- -----------------------------------------------------------------------------------------
         1 one|two|three|four|five|six|seven|nine|ten
         2 einz|zwei|drei|vier|five|six|seven|eight|ten
 
 
 
SQL> SELECT  NR, CHAINE
  2  FROM   (SELECT NR, X,LENGTH(REGEXP_REPLACE(X,'[^|]+',''))+1 NB_MOT
  3          FROM DATA)
  4  MODEL
  5    RETURN UPDATED ROWS
  6  PARTITION BY (NR)
  7  DIMENSION BY (0 POSITION)
  8  MEASURES     (CAST( ' ' AS VARCHAR2(50))  AS  CHAINE ,X , NB_MOT)
  9  RULES UPSERT
 10  (CHAINE[FOR POSITION FROM  1 TO NB_MOT[0] INCREMENT 1] = REGEXP_SUBSTR(X[0],'[^|]+',1,CV(POSITION))) ;
 
        NR CHAINE
---------- --------------------------------------------------
         1 one
         1 two
         1 three
         1 four
         1 five
         1 six
         1 seven
         1 nine
         1 ten
         2 einz
         2 zwei
 
        NR CHAINE
---------- --------------------------------------------------
         2 drei
         2 vier
         2 five
         2 six
         2 seven
         2 eight
         2 ten
 
18 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=86)
   1    0   SQL MODEL (ORDERED)
   2    1     TABLE ACCESS (FULL) OF 'DATA' (TABLE) (Cost=3 Card=2 Byt
          es=86)
 
 
 
 
SQL>

**salim11** · 20/10/2008, 23h39

Salut,

Version corrigée:
Enlever la subquery et et mettre LENGTH(REGEXP_REPLACE(X,'[^|]+',''))+1 dans nb_mot à la ligne 7

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
 
 
SQL> select * from test_1;
 
        NR X
---------- -----------------------------------------------------------------------------------------
         1 F01
         2 F02
         3 D01:D02
         4 F03:G45:D34
         5 G56:Y45:U89:E56
 
 
SQL> SELECT  NR, CHAINE
  2  FROM   TEST_1
  3  MODEL
  4  RETURN UPDATED ROWS
  5  PARTITION BY (NR)
  6  DIMENSION BY (0 POSITION)
  7  MEASURES     (CAST( ' ' AS VARCHAR2(50))  AS  CHAINE ,X ,LENGTH(REGEXP_REPLACE(X,'[^:]+',''))+1
 NB_MOT)
  8  RULES 
  9  (CHAINE[FOR POSITION FROM  1 TO NVL(NB_MOT[0],1) INCREMENT 1] = 
 10   CASE WHEN  NB_MOT[0] IS NULL  THEN X[0] ELSE   REGEXP_SUBSTR(X[0],'[^:]+',1,CV(POSITION)) END 
)
 11  ORDER BY 1 ;
 
        NR CHAINE
---------- --------------------------------------------------
         1 F01
         2 F02
         3 D01
         3 D02
         4 G45
         4 F03
         4 D34
         5 G56
         5 Y45
         5 U89
         5 E56
 
11 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=5 Bytes=325
          )
 
   1    0   SORT (ORDER BY) (Cost=5 Card=5 Bytes=325)
   2    1     SQL MODEL (ORDERED) (Cost=5 Card=5 Bytes=325)
   3    2       TABLE ACCESS (FULL) OF 'TEST_1' (TABLE) (Cost=4 Card=5
           Bytes=325)
 
 
 
 
SQL>

Récupérer un factoriel de ligne

PL/SQL Oracle

Discussions similaires

Partager

Partager