oracle一條語句遞迴查詢父子關係____oracle

1  建表：

CREATE TABLE test_tree (  test_id   INT  NOT NULL,  pid       INT,  test_val  VARCHAR(10),  PRIMARY KEY (test_id));INSERT INTO test_tree VALUES(1, 0,   '.NET');INSERT INTO test_tree VALUES(2, 1,      'C#');INSERT INTO test_tree VALUES(3, 1,      'J#');INSERT INTO test_tree VALUES(4, 1,      'ASP.NET');INSERT INTO test_tree VALUES(5, 1,      'VB.NET');INSERT INTO test_tree VALUES(6, 0,   'J2EE');INSERT INTO test_tree VALUES(7, 6,      'EJB');INSERT INTO test_tree VALUES(8, 6,      'Servlet');INSERT INTO test_tree VALUES(9, 6,      'JSP');INSERT INTO test_tree VALUES(10, 0,  'Database');INSERT INTO test_tree VALUES(11, 10,    'DB2');INSERT INTO test_tree VALUES(12, 10,    'MySQL');INSERT INTO test_tree VALUES(13, 10,    'Oracle');INSERT INTO test_tree VALUES(14, 10,    'SQL Server');INSERT INTO test_tree VALUES(15, 13,    'PL/SQL');INSERT INTO test_tree VALUES(16, 15,    'Function');INSERT INTO test_tree VALUES(17, 15,    'Procedure');INSERT INTO test_tree VALUES(18, 15,    'Package');INSERT INTO test_tree VALUES(19, 15,    'Cursor');INSERT INTO test_tree VALUES(20, 14,    'T-SQL');

使用 START  WITH    CONNECT   BY   語句實現樹狀查詢 使用 SYS_CONNECT_BY_PATH  函數，擷取節點的全路徑. COLUMN   "FullPath"       2:查詢語句

SELECT  LEVEL,  test_id,  test_val,  SYS_CONNECT_BY_PATH(test_val, '\') AS "FullPath"FROM  test_treeSTART WITH  pid =0CONNECT BY PRIOR test_id = pidORDER SIBLINGS BY test_val;

     LEVEL    TEST_ID TEST_VAL             FullPath---------- ---------- -------------------- -----------------------------------         1          1 .NET                 \.NET         2          4 ASP.NET              \.NET\ASP.NET         2          2 C#                   \.NET\C#         2          3 J#                   \.NET\J#         2          5 VB.NET               \.NET\VB.NET         1         10 Database             \Database         2         11 DB2                  \Database\DB2         2         12 MySQL                \Database\MySQL         2         13 Oracle               \Database\Oracle         3         15 PL/SQL               \Database\Oracle\PL/SQL         4         19 Cursor               \Database\Oracle\PL/SQL\Cursor     LEVEL    TEST_ID TEST_VAL             FullPath---------- ---------- -------------------- -----------------------------------         4         16 Function             \Database\Oracle\PL/SQL\Function         4         18 Package              \Database\Oracle\PL/SQL\Package         4         17 Procedure            \Database\Oracle\PL/SQL\Procedure         2         14 SQL Server           \Database\SQL Server         3         20 T-SQL                \Database\SQL Server\T-SQL         1          6 J2EE                 \J2EE         2          7 EJB                  \J2EE\EJB         2          9 JSP                  \J2EE\JSP         2          8 Servlet              \J2EE\Servlet