再帰を理解するには、まず再帰を理解する必要があります。 おそらくそれが、再帰クエリがほとんど使用されない理由です。 確かに、SQLクエリが何であるか想像できます。再帰クエリが通常のクエリとどのように異なるかを説明します。 トピックは膨大であることが判明し、長い読書の準備ができました。 主にOracleについて説明しますが、他のDBMSも記載されています。
問題の本質
最新のDBMS（データベース管理システム）のほとんどはリレーショナルです。 行（レコード）と列（レコードのフィールド）が存在する2次元テーブルの形式でデータを表します。 しかし実際には、データの異なる組織、つまり階層構造に遭遇することがよくあります。
コンピューター上のファイルのリストを見てください。それらはすべてツリーで構成されています。 同様に、図書館の本を想像できます：図書館->ホール->キャビネット->棚->本。 サイト上の同じものと記事：サイト->セクション->サブセクション->記事。 例は長い間与えることができます。 ただし、ここでもすべてを個別のテーブルに分割できます。ライブラリのリストを格納するテーブル、ホールのリスト用の別のテーブル、キャビネット用の3つ目などです。 ただし、ネストの深さが事前にわからない場合、またはこのネストが変更される可能性がある場合は、階層から逃げることはできません。
問題は、階層構造を持つデータがリレーショナルモデルで非常に不十分に表現されることです。 SQL-92標準には、それらを処理するツールはありません。
しかし、そのようなツールはSQL-1999標準に登場しました。 確かに、それまでにOracleは独自のCONNECT BYステートメントをすでに持っていました。 それにもかかわらず、SQL-1999では、再帰クエリの構文はOracle CONNECT BY構文とは完全に異なり、WITHキーワードを使用します。 他のDBMSでの再帰クエリの実装はやや遅れていたため、MS SQL Serverにはバージョン2005でのみ登場しました。
構文と同様に、用語にも違いがあります。 Oracleでは、一般的に議論されているクエリは「階層的」と呼ばれ、他のクエリはすべて「再帰的」です。 これの本質は変わらず、両方を使用します。
言葉から行動へ！
デモンストレーションでは、ディレクトリ構造を使用します。3つのフィールドで構成されるテストテーブルが必要です。
id-識別子
pid-親識別子（同じテーブル内の別のレコードのIDを参照）、
title-ディレクトリの名前（いくつかのフィールドや他のテーブルへのリンクも含めて、ディレクトリの代わりに何でもかまいません）。
create table test_table (
id int,
pid int,
title varchar(256)
);
mySQL, . , Oracle : int — number, varchar — varchar2.
. , . , , , , .
insert into test_table values (1, null, '');
, . , . -, SELECT * FROM test_table :
ID PID TITLE ---- ---------- -------------------- 1 2 1 3 2 " " 4 1 5 1 6 3 7 3 1 8 3 2 9 8 1 10 5
. .
mySQL
mySQL – , php ( , , , “mySQL tree” ..).
, , , . , .
- php. mySQL. , , ( ). , AJAX. pid , . , , .
SQL-1999
SQL-92, , . :-), (LOB), SIMILAR DISTINCT, , , . , .
, WITH. . :
WITH [recursive] __ [ ( ) ] AS ()
MS SQL RECURSIVE, . . DB2, Sybase iAnywhere, MS SQL, 2005, , SQL 1999.
:
WITH RECURSIVE
Rec (id, pid, title)
AS (
SELECT id, pid, title FROM test_table
UNION ALL
SELECT Rec.id, Rec.pid, Rec.title
FROM Rec, test_table
WHERE Rec.id = test_table.pid
)
SELECT * FROM Rec
WHERE pid is null;
Rec, , test_table. Rec , : WHERE Rec.id = test_table.pid. , , pid , .. .
, MS SQL Server 2005 , . . , .
MS SQL 2008 hierarchyid. XaocCPS .
Oracle
- Oracle! . Oracle 8- , . . . , , .
: . ?
, – CONNECT BY, “” .
START WITH , .. ( ) . , : pid is null, id = 1, substr(title, 1, 1) = ‘’.
CONNECT BY . , . - while . , 10 : rownum<=10 – 10 . ? , – 1- . , rownum , , 1 . . .
? , PRIOR. , + -. “ ” – , . pid = PRIOR id ( PRIOR id = pid, , …).
? , START WITH, . PRIOR. , , (pid) , id . . , , .
, Oracle. , . , , - . id pid. , Oracle LEVEL. , . , 1- 1, 2, — 3 ..
SELECT level, id, pid, title
FROM test_table
START WITH pid is null
CONNECT BY PRIOR id = pid;
LEVEL ID PID TITLE
------ ---------- ---------- -------------------
1 1
2 2 1
3 3 2 " "
4 6 3
4 7 3 1
4 8 3 2
5 9 8 1
2 4 1
2 5 1
3 10 5
. . , -, . , : ORDER BY title.
SELECT level, id, pid, title
FROM test_table
START WITH pid is null
CONNECT BY PRIOR id = pid
ORDER BY title;
LEVEL ID PID TITLE
------ ---------- ---------- -------------------
2 2 1
4 6 3
2 5 1
3 10 5
2 4 1
3 3 2 " "
4 7 3 1
4 8 3 2
1 1
5 9 8 1
, ! . ? ( level), ORDER BY. , , SIBLINGS. ORDER SIBLINGS BY title – .
, , . “” , :
SELECT lpad(' ', 3*level)||title as Tree
FROM test_table
START WITH pid is null
CONNECT BY PRIOR id = pid
ORDER SIBLINGS BY title;
TREE
-----------------------------
" "
1
2
1
, , .
, , : /home/maovrn/documents/ ..? . : Oracle . SYS_CONNECT_BY_PATH(). : -. , : SYS_CONNECT_BY_PATH(title, ‘/’).
, . , , WHERE. . , FROM. “ 1”, id=9:
SELECT SYS_CONNECT_BY_PATH(title, '/') as Path
FROM test_table
WHERE id=9
START WITH pid is null
CONNECT BY PRIOR id = pid;
PATH ---------------------------------------------------- /// " "/ 2/ 1
CONNECT_BY_ISLEAF. , LEVEL. 0 1. – 0. , “”, CONNECT_BY_ISLEAF 1.
? , . PRIOR, . CONNECT_BY_ROOT, ( !) , .. .
SELECT id, pid, title, level,
CONNECT_BY_ISLEAF as IsLeaf,
PRIOR title as Parent,
CONNECT_BY_ROOT title as Root
FROM test_table
START WITH pid is null
CONNECT BY PRIOR id = pid
ORDER SIBLINGS BY title;
ID PID TITLE LEVEL LEAF PARENT ROOT -- --- -------------------- ----- ---- -------------------- ------ 1 1 0 2 1 2 0 3 2 " " 3 0 6 3 4 1 " " 7 3 1 4 1 " " 8 3 2 4 0 " " 9 8 1 5 1 2 5 1 2 0 10 5 3 1 4 1 2 1
, , Oracle . , , – , . “” NOCYCLE CONNECT BY – . . “” , CONNECT_BY_ISCYCLE – 0, , , 1.
, . ; , ( commit – ):
update test_table set pid=10 where id=5;
- , , , , . , , .. . START WITH, – . . :
SELECT CONNECT_BY_ISCYCLE as cycl, id, pid, title
FROM test_table
START WITH id=5
CONNECT BY NOCYCLE PRIOR id = pid;
CYCL ID PID TITLE ---- ---------- ---------- ---------- 0 5 10 1 10 5
, .
, . , id 1, . . , .
. :
DELETE FROM test_table WHERE id IN (3, 5);
? -, 1 , . id , , :
SELECT max(id) FROM test_table
1 max . ! – .
SELECT rownum as rn FROM dual
CONNECT BY level <= (SELECT max(id) FROM test_table);
“SELECT … FROM dual” , , . Dual – , . ‘X’. , .
, , , pivot. , . dual Oracle .
, , , :
SELECT sq.rn
FROM (SELECT rownum as rn FROM dual
CONNECT BY level <= (SELECT max(id) FROM test_table)) sq
WHERE sq.rn not in (SELECT id FROM test_table)
ORDER BY rn;
RN ---- 3 5
. , . ROLLBACK. COMMIT!