【PL/SQL】WITH FUNCTION句を使ってSQL内に関数を埋め込む方法

【PL/SQL】WITH FUNCTION句を使ってSQL内に関数を埋め込む方法 PL/SQL

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を活用する、といった使い分けが効果的です。