Oracle Database 12c以降では、SQL文のWITH句の中にPL/SQL関数を埋め込める「WITH FUNCTION句」が利用できます。これにより、そのSQL専用のローカル関数を定義し、外部スキーマに依存しない自己完結したSQLを書くことが可能になります。簡単な集計や条件判定ロジックをSQL内でまとめたいときに非常に便利です。ここでは基本的な書き方から応用例、注意点までを解説します。
基本構文
WITH句の中にFUNCTIONを定義し、その関数を直後のSQL本体で利用します。関数のスコープはそのSQL文内に限定されるため、データベース全体にオブジェクトを残さずに済みます。
WITH
FUNCTION tax_price(p_price NUMBER) RETURN NUMBER IS
BEGIN
RETURN ROUND(p_price * 1.1, 0); -- 税込価格を計算
END;
SELECT product_id, product_name, tax_price(price) AS price_incl_tax
FROM products
WHERE ROWNUM <= 5;
この例では税計算関数をSQLに埋め込み、SELECT句で直接呼び出しています。
複数関数の定義
WITH句の中で複数のFUNCTIONを定義することも可能です。読みやすさのため、各関数はセミコロンで区切ります。
WITH
FUNCTION add_tax(p NUMBER) RETURN NUMBER IS
BEGIN
RETURN p * 1.1;
END;
FUNCTION discount(p NUMBER) RETURN NUMBER IS
BEGIN
RETURN p * 0.9;
END;
SELECT product_id,
add_tax(price) AS price_with_tax,
discount(price) AS price_with_discount
FROM products
WHERE category_id = 10;
PL/SQL的な処理をSQLに閉じ込める
ちょっとした条件分岐やループなどもWITH FUNCTION内で扱えます。例えば、スコアを判定してランクを返す関数をその場で作成できます。
WITH
FUNCTION rank_score(p_score NUMBER) RETURN VARCHAR2 IS
BEGIN
IF p_score >= 90 THEN
RETURN 'A';
ELSIF p_score >= 75 THEN
RETURN 'B';
ELSE
RETURN 'C';
END IF;
END;
SELECT student_id, score, rank_score(score) AS grade
FROM exam_results;
実務での応用例
- 一時的なバッチやデータ抽出SQLに小さな関数を埋め込む
- 再利用性が低いロジックを外部オブジェクトにせずにSQL内に閉じ込める
- BIツールやETLで、1本のSQLとして投げたいときに関数定義を同梱する
注意点
WITH FUNCTION句にはいくつか制限があります。
- SQL文ごとにスコープが限定され、他のSQLからは参照できません
- 副作用のあるDMLは行えません（純粋関数に近い設計が求められます）
- 再利用性が必要な場合は通常のストアドファンクションとして定義した方が適切です
まとめ
WITH FUNCTION句を使うと、SQL内に小規模な関数を埋め込み、自己完結した処理を記述できます。外部スキーマに依存せず、軽量でメンテナンス性も高まります。定期的に使う複雑なロジックは通常のストアドファンクションにまとめ、スポット利用や一時的な抽出にはWITH FUNCTIONを活用する、といった使い分けが効果的です。
