ITコーディネータのシュウです。
大変ご無沙汰しています。久しぶりにブログをアップいたします。この写真は、島根の実家に帰省したときに、父親が行ってみたいと言った、宍道湖の隣にある中海に浮かぶ大根島にある「由志園」（ゆうしえん）で撮ったものです。私も大根島に行くこと自体が初めてだったので、由志園というものを知らなかったのですが、牡丹の栽培と雲州人参と呼ばれる高麗人参で有名な大根島にある、池泉回遊式（池をめぐり回る形式）日本庭園です。大根島は日本一の牡丹苗の生産地であり、ゴールデンウィークの時期は、園内の池に三万輪の牡丹が浮かべ、歩道の両脇を牡丹で絨毯のように敷き詰めるなど、とても綺麗だということです。また、日本の高麗人参の産地は、雲州（島根）、信州（長野）、会津（福島）の三地域で、大根島については、かつて高麗人蔘が門外不出の産物であったため、島で栽培をしていることを隠すために「人蔘島」ではなく、「大根島」と呼ぶようになったという逸話があるようです。
父親が行きたいと言ったので訪ねてみましたが、自分が生まれた田舎でも、学生の時から東京に出てきたため、行ったこともなく知らないところがたくさんあることを改めて感じました。
コロナウィルス感染が少し落ち着いてきているこの頃、また第６波がいつ来るかと危惧されていますが、国内産の経口薬などが早く開発されて、以前のように気兼ねなく自然や観光地を訪ねることができるようになればと願います。
＜本日の題材＞
再帰SQLでの最大再帰数（SQL Server）
以前、このブログで取り上げた、WITH句と共通テーブル式(CTE)という内容の中で、再帰SQLの例を上げましたが、今回は、再帰SQLの最大再帰数の設定ができることについて取り上げたいと思います。
例)
2021年の1年間の日付と曜日の一覧（カレンダー）を、WITH句を使って表示するSQLを作成したいと思います。その際に、1年分の日数の再帰を行って抽出すると、以下のようになります。
DECLARE @StartDate date = '20210101'; -- 手抜き (;^_^A
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 1, @StartDate));
;WITH seq(n) AS
(
SELECT 0
UNION ALL
SELECT n + 1
FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d, youbi) AS
(
SELECT DATEADD(DAY, n, @StartDate),
DATENAME(WEEKDAY, DATEADD(DAY, n, @StartDate))
FROM seq
)
SELECT d, youbi
FROM d
ORDER BY d;
最初のWITH句で、@ StartDate（2021/1/1）から、@CutoffDate date（@StartDateから1年後-1日、つまり2021年の年末）までの日数分の 0～364までの365件のレコードを再帰処理を使って作成しています。（DECLARE @StartDate date = '20210101'; は、本来は'2021-01-01'ですが、暗黙の型変換で日付と認識してくれています。）
その後、次のWITH句の内容で、それを使ってその期間の日付と曜日のレコードを作成し、最後にそれを抽出する処理になります。ちなみに、何曜日を表わすのに、DATENAME関数を使っています。
しかし、これを実行すると、最大再帰数に達成したというエラーが表示されます。
「ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。」というエラーメッセージが表示されます。再帰SQLでは、無限ループ等が発生した場合のことを考慮して、再起の出来る回数の上限値が設定されており、100行を超えるとエラーになるようです。
この上限値を変更するのが、「MAXRECURSION」というオプションになります。
設定方法は
OPTION (MAXRECURSION 上限値)
で、設定できる上限値は、0 ～ 32767 で、「0」は上限なし、デフォルトは「100」です。
先ほどのSQLに、OPTION (MAXRECURSION 0) を追加して実行してみます。
実行すると、エラーなく 365件分、2021/1/1～2021/12/31までの日付と曜日が表示されます。今回は、上限なしの「0」を設定しましたが、1年の日数である「365」を設定しても、同様の結果になります。
今日は以上まで
にほんブログ村
