recuperation hierarchique avec tri alphanumérique des fils d

**jlinho** · 23/01/2006, 17h57

Bonjour,

je vais formuler ma question à travers un exemple repris sur internet.

La table

descriptif: contient une liste d'employés identifiés par un numéro ( clé primaire) . Chaque employé a un numéro de manager ( colonne MGR) ce qui créé ainsi une relation hierarchique ( arborescente) entre les employés.

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
 
EMPNO        ENAME             MGR 
--------        --------             -----
7369           SMTIH             7902
7499           ALLEN              7698
7521           WARD             7698
7566           JONES             7839
7654           MARTIN           7698
7698           BLAKE             7839
7782           CLARK            7839
7788           SCOTT            7566
7839           KING               NULL
7844           TURNER           7698
7876           ADAMS            7788

Ce que je veux:
Obtenir une représentation complète de l'arbre représenté dans la table, de la racine aux feuilles ou chaque groupe de fils d'un même sous-noeud est trié sous-forme alphabétique.

Par exemple avec la requête:
SELECT LPAD(' ',3 * LEVEL-3) ||ename AS org_chart,
LEVEL, empno,mgr
FROM emp
START WITH mgr is NULL
CONNECT BY PRIOR empno = mgr;

j'obtient:

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
ORG_CHART                           LEVEL      EMPNO         MGR
---------------------------------    -------      --------         ------
KING                                          1          7839           NULL
   JONES                                     2          7566           7839
      SCOTT                                 3           7788          7566
         ADAMS                              4          7876           7788
      FORD                                   3           7902          7566
         SMITH                               4           7369          7902
   BLAKE                                     2           7698          7839
      ALLEN                                   3          7499           7698
      WARD                                  3           7521           7698
      MARTIN                                3           7654           7698
      TURNER                                3           7844           7698
      JAMES                                  3            7900          7698
   CLARK                                     2           7782          7839
      MILLER                                 3           7934           7782

Or je voudrais avoir la même résultat mais avec;
pour le niveau 2:
d'abord BLAKE puis CLARK puis JONES
et par exemple pour le niveau 3 dans BLAKE ( les subordonnés de blake)
ALLEN, JAMES,MARTIN,TURNER et WARD dans cet ordre. Au final j'aurai celà:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
KING
   BLAKE
      ALLEN
      JAMES
      MARTIN
      TURNER
      WARD
   CLARK
      MILLER
   JONES
      FORD
          SMITH
      SCOTT
           ADAMS

Voilà j'éspère avoir été clair. Merci d'avance pour votre aide.

NB: J'utilise oracle 8i

**Médiat** · 23/01/2006, 18h01

ORDER SIBLINGS BY, mais je ne sais plus à partir de quelle version cette clause est disponible.

**jlinho** · 23/01/2006, 18h18

Je te remercie.
Maintenant que je connais le mot clé SIBLINGS j'ai pu faire une recherche google avec, malheuresement celà ne fonctionne que pour 9i et supérieurs.

Par contre j'ai trouvé celà:
http://www.orafaq.com/forum/t/10476/0/

**Médiat** · 23/01/2006, 19h02

Tu peux aussi utiliser SYS_CONNECT_BY_PATHqui est non document pour la 8i, mais que j'ai utilisé sans problème :

Code :

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

1
2
3
4
5
SELECT ENAME, sys_connect_by_path(ENAME, '*') as Hierarchie
FROM dvp
CONNECT BY MGR = PRIOR EMPNO
START WITH MGR IS NULL
ORDER BY Hierarchie

**jlinho** · 23/01/2006, 19h04

Je ne suis pas un expert des bases de données, j'ai trouvé ce qi suit après. Est-ce que je dois saisir un tel PACKAGE dans SQL PLUS, puis la requête ensuite ou y a t'il des opération sspéciales a effectuer avant? Sachant que j'ai des droits limités sur la base suis-je autorisé à créer un tel package? Enfin le PL SQL est il activé sur une base de donnée Oracle ou pourrait il être désactivé?

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
 
CREATE OR REPLACE
 PACKAGE Hierarchy
  IS
        TYPE BranchTableType IS TABLE OF VARCHAR2(4000)
          INDEX BY BINARY_INTEGER;
        BranchTable BranchTableType;
        FUNCTION Branch(vLevel          IN NUMBER,
                        vValue          IN VARCHAR2,
                        vDelimiter      IN VARCHAR2 DEFAULT CHR(0))
                        RETURN VARCHAR2;
        PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
END Hierarchy;
/
CREATE OR REPLACE
 PACKAGE BODY Hierarchy
  IS
        ReturnValue VARCHAR2(4000);
  FUNCTION Branch(vLevel        IN NUMBER,
                  vValue        IN VARCHAR2,
                  vDelimiter    IN VARCHAR2 DEFAULT CHR(0))
                  RETURN VARCHAR2
   IS
   BEGIN
        BranchTable(vLevel) := vValue;
        ReturnValue := vValue;
        FOR I IN REVERSE 1..vLevel - 1 LOOP
          ReturnValue := BranchTable(I)|| vDelimiter || ReturnValue;
        END LOOP;
        RETURN ReturnValue;
  END Branch;
END Hierarchy;
/
 
Now, if we want to order emp table ny employee name, we use:
 
SQL> SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart,
  2   Hierarchy.Branch(level,ename) branch,
  3   job
  4    FROM  empX
  5    START WITH job = 'PRESIDENT'
  6    CONNECT BY PRIOR empno = mgr
  7*   ORDER BY branch
SQL> /
 
ORG_CHART            BRANCH                                   JOB
-------------------- ---------------------------------------- ---------
KING                 KING                                     PRESIDENT
  BLAKE              KING BLAKE                               MANAGER
    ALLEN            KING BLAKE ALLEN                         SALESMAN
    JAMES            KING BLAKE JAMES                         CLERK
    MARTIN           KING BLAKE MARTIN                        SALESMAN
    TURNER           KING BLAKE TURNER                        SALESMAN
    WARD             KING BLAKE WARD                          SALESMAN
  CLARK              KING CLARK                               MANAGER
    MILLER           KING CLARK MILLER                        CLERK
  JONES              KING JONES                               MANAGER
    FORD             KING JONES FORD                          ANALYST
      SMITH          KING JONES FORD SMITH                    CLERK
    SCOTT            KING JONES SCOTT                         ANALYST
      ADAMS          KING JONES SCOTT ADAMS                   CLERK
 
14 rows selected.

**jlinho** · 23/01/2006, 19h13

OK Médiat j'opte pour ta solution. Je ne l'avais pas vu ... j'étais entrain d'écrire mon post à ce moment.

D'ailleurs les 2 solutions se basent sur le même principe apparemment qui est de trier par chemins.

Encore Merci,

Je teste sa demain et je noterai [Résolu] à ce moment là.

recuperation hierarchique avec tri alphanumérique des fils d

Oracle

Discussions similaires

Partager

Partager