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

# はじめに

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

# サンプルスキーマ

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

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

![名称未設定ファイル.drawio (2).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/105859/0aaf24a7-3961-fb15-bb0c-85c2686c6066.png)

```sql
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) による順序を指定することができます。

## 深さ優先探索

```SQL
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|

## 幅優先探索

```sql
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句を利用します。

```sql
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句との併用も可能です。

```sql
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|


# 参考

https://docs.oracle.com/cd/F19136_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__BCEJGIBG

# 環境情報

```sql
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<br>Version 21.3.0.0.0|Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production|0|