熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

10g樹形查詢新特性

2013-11-13 15:25:13  來源: Oracle 

  在g中Oracle提供了新的偽列CONNECT_BY_ISCYCLE通過這個偽列可以判斷是否在樹形查詢的過程中構成了循環這個偽列只是在CONNECT BY NOCYCLE方式下有效

  這一篇描述一下解決問題的思路

  CONNECT_BY_ISCYCLE的實現和前面兩篇文章中CONNECT_BY_ROOT和CONNECT_BY_ISLEAF的實現完全不同

  因為要實現CONNECT_BY_ISCYCLE就必須先實現CONNECT BY NOCYCLE而在i中是沒有方法實現這個功能的

  也就是說首先要實現自己的樹形查詢的功能而僅這第一點就是一個異常困難的問題何況後面還要實現NOCYCLE最後再加上一個ISCYCLE的判斷

  所以總的來說這個功能的實現比前面兩個功能要復雜得多由於樹形查詢的LEVEL是不固定的所以采用鏈接的方式實現基本上是不現實的換句話說用純SQL的方式來實現樹形查詢的功能基本上不可行而為了解決這個功能只能通過PL/SQL配合SQL來實現

  仍然是首先構造一個例子

  SQL> CREATE TABLE T_TREE (ID NUMBER FATHER_ID NUMBER NAME VARCHAR());

  表已創建

  SQL> INSERT INTO T_TREE VALUES ( A);

  已創建

  SQL> INSERT INTO T_TREE VALUES ( BC);

  已創建

  SQL> INSERT INTO T_TREE VALUES ( DE);

  已創建

  SQL> INSERT INTO T_TREE VALUES ( FG);

  已創建

  SQL> INSERT INTO T_TREE VALUES ( HIJ);

  已創建

  SQL> INSERT INTO T_TREE VALUES ( KLM);

  已創建

  SQL> INSERT INTO T_TREE VALUES ( NOPQ);

  已創建

  SQL> INSERT INTO T_TREE VALUES ( ROOT);

  已創建

  SQL> INSERT INTO T_TREE VALUES ( FG);

  已創建

  SQL> COMMIT;

  提交完成

  SQL> SELECT * FROM T_TREE;

  ID FATHER_ID NAME

  

   A

   BC

   DE

   FG

   HIJ

   KLM

   NOPQ

   ROOT

   FG

  已選擇

  上面構造了兩種樹形查詢循環的情況一種是當前記錄的自循環另一種是樹形查詢的某個子節點是當前節點的祖先節點從而構成了循環在這個例子中記錄ID為和ID為且FATHER_ID等於的兩條記錄分別構成了上述的兩種循環的情況

  下面就來看看CONNECT_BY_ISCYCLE和CONNECT BY NOCYCLE的功能

  SQL> SELECT *

   FROM T_TREE

   START WITH ID =

   CONNECT BY PRIOR ID = FATHER_ID;

  ERROR:

  ORA: 用戶數據中的 CONNECT BY 循環

  未選定行

  SQL> SELECT *

   FROM T_TREE

   START WITH ID =

   CONNECT BY PRIOR ID = FATHER_ID;

  ERROR:

  ORA: 用戶數據中的 CONNECT BY 循環

  未選定行

  這就是不使用CONNECT BY NOCYCLE的情況查詢會報錯指出樹形查詢中出現循環g中可以使用CONNECT BY NOCYCLE的方式來避免錯誤的產生

  SQL> SELECT *

   FROM T_TREE

   START WITH ID =

   CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

  ID FATHER_ID NAME

  

   ROOT

   A

   BC

   HIJ

   DE

   FG

   KLM

   NOPQ

  已選擇

  使用CONNECT BY NOCYCLEOracle自動避免循環的產生將不產生循環的數據查詢出來下面看看CONNECT_BY_ISCYCLE的功能

  SQL> SELECT ID

   FATHER_ID

   NAME

   CONNECT_BY_ISCYCLE CYCLED

   FROM T_TREE

   START WITH ID =

   CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

  ID FATHER_ID NAME CYCLED

  

   ROOT

   A

   BC

   HIJ

   DE

   FG

   KLM

   NOPQ

  已選擇

  可以看到CONNECT_BY_ISCYCLE偽列指出循環在樹形查詢中發生的位置

  為了實現CONNECT_BY_ISCYCLE就必須先實現CONNECT BY NOCYCLE方式而這在i中是沒有現成的辦法的所以這裡嘗試使用PL/SQL來自己實現樹形查詢的功能

  SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR) RETURN VARCHAR AS

   V_STR VARCHAR() := / || P_VALUE;

  

   PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR P_STR IN OUT VARCHAR) AS

   BEGIN

   FOR I IN (SELECT ID FROM T_TREE WHERE FATHER_ID = P_FATHER AND FATHER_ID != ID) LOOP

   IF INSTR(P_STR || / / || IID || /) = THEN

   P_STR := P_STR || / || IID;

   P_GET_CHILD_STR(IID P_STR);

   END IF;

   END LOOP;

   END;

   BEGIN

   P_GET_CHILD_STR(P_VALUE V_STR);

   RETURN V_STR;

   END;

   /

  函數已創建

  構造一個函數在函數中遞歸調用過程來實現樹形查詢的功能

  下面看看調用這個函數的結果

  SQL> SELECT F_FIND_CHILD() FROM DUAL;

  F_FIND_CHILD()

  

  ////////

  SQL> SELECT F_FIND_CHILD() FROM DUAL;

  F_FIND_CHILD()

  

  //

  SQL> SELECT F_FIND_CHILD() FROM DUAL;

  F_FIND_CHILD()

  

  ///

  雖然目前存在的問題還有很多但是已經基本上實現了一個最簡單的NOCYCLE的SYS_CONNECT_BY_PATH的功能

  有了這個函數作為基礎就可以逐步的實現最終的目標了


From:http://tw.wingwit.com/Article/program/Oracle/201311/16670.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.