Oracle Databaseでは再帰SQLとともにSEARCH句とCYCLE句を利用することができる

はじめに

Oracle Databaseでは、再帰SQLとともにSEARCH句とCYCLE句を利用することができます。この記事では、SEARCH句とCYCLE句の使用例を簡単にまとめています。

サンプルスキーマ

部品マスタテーブルでは、部品名とその親の部品の部品名を管理しています。AAAを根とするツリーのように、通常ある部品は親部品を1つしか持ちませんが、部品WWWについては2つの親が誤って登録されています。そのため、PPPを根とするツリーには閉路が発生しています。

この記事では再帰SQLを利用して、根AAAや根PPPから子部品すべてと、その部品のパスを取得します。

CREATE TABLE 部品マスタ (部品名 CHAR(3) NOT NULL, 親部品名 CHAR(3));

INSERT INTO 部品マスタ VALUES ('AAA', NULL);
INSERT INTO 部品マスタ VALUES ('BBB', 'AAA');
INSERT INTO 部品マスタ VALUES ('CCC', 'AAA');
INSERT INTO 部品マスタ VALUES ('DDD', 'BBB');
INSERT INTO 部品マスタ VALUES ('EEE', 'CCC');
INSERT INTO 部品マスタ VALUES ('FFF', 'CCC');
INSERT INTO 部品マスタ VALUES ('GGG', 'FFF');
INSERT INTO 部品マスタ VALUES ('HHH', 'FFF');
INSERT INTO 部品マスタ VALUES ('PPP', NULL);
INSERT INTO 部品マスタ VALUES ('QQQ', 'PPP');
INSERT INTO 部品マスタ VALUES ('RRR', 'PPP');
INSERT INTO 部品マスタ VALUES ('SSS', 'QQQ');
INSERT INTO 部品マスタ VALUES ('TTT', 'RRR');
INSERT INTO 部品マスタ VALUES ('UUU', 'RRR');
INSERT INTO 部品マスタ VALUES ('VVV', 'UUU');
INSERT INTO 部品マスタ VALUES ('WWW', 'UUU');
INSERT INTO 部品マスタ VALUES ('RRR', 'WWW');

SEARCH句

SEARCH句を利用すると、深さ優先検索(DEPTH FIRST SEARCH; DFS) または 幅優先検索(BREADTH FIRST SEARCH; BFS) による順序を指定することができます。

深さ優先探索

WITH R (部品名, パス) AS (
    SELECT 部品名, 部品名 FROM 部品マスタ WHERE 部品名= 'AAA'
  UNION ALL 
    SELECT B.部品名, R.パス || ' --> ' || B.部品名
    FROM R
    JOIN 部品マスタ B ON R.部品名 = B.親部品名
)
SEARCH DEPTH FIRST BY 部品名 SET 探索順
SELECT 探索順, 部品名, パス FROM R;

探索順	部品名	パス
1	AAA	AAA
2	BBB	AAA --> BBB
3	DDD	AAA --> BBB --> DDD
4	CCC	AAA --> CCC
5	EEE	AAA --> CCC --> EEE
6	FFF	AAA --> CCC --> FFF
7	GGG	AAA --> CCC --> FFF --> GGG
8	HHH	AAA --> CCC --> FFF --> HHH

幅優先探索

WITH R (部品名, パス) AS (
    SELECT 部品名, 部品名 FROM 部品マスタ WHERE 部品名= 'AAA'
  UNION ALL 
    SELECT B.部品名, R.パス || ' --> ' || B.部品名
    FROM R
    JOIN 部品マスタ B ON R.部品名 = B.親部品名
)
SEARCH BREADTH FIRST BY 部品名 SET 探索順
SELECT 探索順, 部品名, パス FROM R;

探索順	部品名	パス
1	AAA	AAA
2	BBB	AAA --> BBB
3	CCC	AAA --> CCC
4	DDD	AAA --> BBB --> DDD
5	EEE	AAA --> CCC --> EEE
6	FFF	AAA --> CCC --> FFF
7	GGG	AAA --> CCC --> FFF --> GGG
8	HHH	AAA --> CCC --> FFF --> HHH

CYCLE句

Oracle DataBaseでは、再帰SQL実行時に閉路を検出した場合、「ORA-32044: 再帰的WITH問合せの実行中にサイクルが検出されました」エラーが発生します。

このエラーを抑制し、閉路をマークしたい場合はCYCLE句を利用します。

WITH R (部品名, パス) AS (
    SELECT 部品名, 部品名 FROM 部品マスタ WHERE 部品名= 'PPP'
  UNION ALL 
    SELECT B.部品名, R.パス || ' --> ' || B.部品名
    FROM R
    JOIN 部品マスタ B ON R.部品名 = B.親部品名
)
CYCLE 部品名 SET 閉路 to 'Y' default 'N'
SELECT 閉路, 部品名, パス FROM R;

閉路	部品名	パス
N	PPP	PPP
N	QQQ	PPP --> QQQ
N	RRR	PPP --> RRR
N	SSS	PPP --> QQQ --> SSS
N	TTT	PPP --> RRR --> TTT
N	UUU	PPP --> RRR --> UUU
N	VVV	PPP --> RRR --> UUU --> VVV
N	WWW	PPP --> RRR --> UUU --> WWW
Y	RRR	PPP --> RRR --> UUU --> WWW --> RRR

もちろん、SEARCH句との併用も可能です。

WITH R (部品名, パス) AS (
    SELECT 部品名, 部品名 FROM 部品マスタ WHERE 部品名= 'PPP'
  UNION ALL 
    SELECT B.部品名, R.パス || ' --> ' || B.部品名
    FROM R
    JOIN 部品マスタ B ON R.部品名 = B.親部品名
)
SEARCH DEPTH FIRST BY 部品名 SET 探索順
CYCLE 部品名 SET 閉路 to 'Y' default 'N'
SELECT 探索順, 閉路, 部品名, パス FROM R;

探索順	閉路	部品名	パス
1	N	PPP	PPP
2	N	QQQ	PPP --> QQQ
3	N	SSS	PPP --> QQQ --> SSS
4	N	RRR	PPP --> RRR
5	N	TTT	PPP --> RRR --> TTT
6	N	UUU	PPP --> RRR --> UUU
7	N	VVV	PPP --> RRR --> UUU --> VVV
8	N	WWW	PPP --> RRR --> UUU --> WWW
9	Y	RRR	PPP --> RRR --> UUU --> WWW --> RRR

参考

環境情報

SELECT * FROM v$version;

BANNER	BANNER_FULL	BANNER_LEGACY	CON_ID
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production	Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0	Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production	0