【PL/SQL】WITH FUNCTION句の使い方 | コーディングライフスタイル

WITH FUNCTION句は、Oracle Database 12c以降で使える、SQL文の中に一時的なPL/SQL関数を定義する書き方です。そのSQLだけで使う小さな変換処理、判定処理、整形処理を、スキーマレベルのファンクションとして作成せずに閉じ込められます。

便利な一方で、通常の WITH 句、スキーマレベルのファンクション、CASE 式とは使いどころが違います。また、INSERT / UPDATE / DELETE / MERGE の中で使う場合は WITH_PLSQL の指定が必要です。この記事では、基本構文からDMLでの使い方、性能面の注意、避けたいパターンまで実務向けに整理します。通常のWITH句は OracleのWITH句完全ガイド、ファンクション全体の設計は PL/SQLプロシージャ・ファンクション完全ガイドも参考にしてください。

この記事で扱うこと

WITH FUNCTION の基本構文
複数関数を定義する書き方
サブクエリやDMLで使う場合の WITH_PLSQL
CASE式・通常ファンクションとの使い分け
大量データでの性能注意
実務で避けたいアンチパターン

WITH FUNCTION句とは
基本構文
複数の関数を定義する
CASE式で足りるなら関数化しない
WITH句のサブクエリと組み合わせる
DMLで使う場合はWITH_PLSQLを指定する
UPDATEで使う例
通常ファンクションとの違い
性能面の注意点
避けたいアンチパターン
エラーになりやすいポイント
実務での使いどころ
よくある質問
まとめ

WITH FUNCTION句とは

WITH FUNCTION は、SELECT文の WITH 句にPL/SQL関数を宣言する機能です。OracleのSQLリファレンスでは plsql_declarations として扱われ、定義した関数はそのSQL文と、その中のサブクエリから参照できます。関数オブジェクトをデータベースに残さないため、一時的な抽出SQLや検証SQLで特に便利です。

スコープ定義したSQL文の中だけで使えます。他のSQLからは参照できません。

優先順位同名のスキーマレベル関数があっても、WITH句内の関数名が優先されます。

用途一時的な変換、分類、検証ロジックをSQLに同梱したい場合に向いています。

注意点大量行で行ごとに呼ばれる関数は、純粋なSQL式より重くなることがあります。

基本構文

基本形は、WITH の直後に FUNCTION を定義し、その後のSELECT本体で呼び出します。以下は、税抜価格から税込価格を計算するローカル関数の例です。

with-function-basic.sql

WITH
  FUNCTION tax_price(p_price NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ROUND(p_price * 1.10);
  END;
SELECT
  product_id,
  product_name,
  price,
  tax_price(price) AS price_incl_tax
FROM products
WHERE category_id = 10;

この関数はSQL文の中だけで有効です。CREATE FUNCTION で作成したわけではないため、実行後にデータベースオブジェクトとして残りません。

複数の関数を定義する

1つの WITH 句の中に複数の関数を定義できます。ただし、関数が増えすぎるとSQL全体の見通しが悪くなるため、2〜3個を超えるなら通常のパッケージ化も検討します。

with-function-multiple.sql

WITH
  FUNCTION normalize_code(p_code VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN UPPER(TRIM(p_code));
  END;

  FUNCTION price_rank(p_price NUMBER) RETURN VARCHAR2 IS
  BEGIN
    IF p_price >= 100000 THEN
      RETURN 'HIGH';
    ELSIF p_price >= 10000 THEN
      RETURN 'MIDDLE';
    ELSE
      RETURN 'LOW';
    END IF;
  END;
SELECT
  product_id,
  normalize_code(product_code) AS normalized_code,
  price_rank(price) AS rank_name
FROM products;

CASE式で足りるなら関数化しない

単純な条件分岐であれば、WITH FUNCTION より CASE 式のほうが読みやすく、SQLエンジンにも素直です。たとえば価格帯を返すだけなら、次のように CASE で十分です。CASE式の詳しい使い方は Oracle CASE式完全ガイドにまとめています。

prefer-case-expression.sql

SELECT
  product_id,
  CASE
    WHEN price >= 100000 THEN 'HIGH'
    WHEN price >= 10000  THEN 'MIDDLE'
    ELSE 'LOW'
  END AS rank_name
FROM products;

WITH FUNCTION が向いているのは、同じ判定を複数箇所で使う、文字列整形が長い、例外処理を含めたい、SQLだけでは読みづらいロジックを一時的に閉じ込めたい、といった場面です。

WITH句のサブクエリと組み合わせる

WITH FUNCTION は、通常の共通表式と同じ WITH 句の中で使えます。関数で値を整形し、その結果を共通表式で集計するような書き方もできます。

with-function-and-cte.sql

WITH
  FUNCTION normalize_dept(p_name VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN UPPER(REPLACE(TRIM(p_name), ' ', '_'));
  END;

  dept_sales AS (
    SELECT
      normalize_dept(department_name) AS dept_key,
      amount
    FROM sales_source
    WHERE sales_month = :target_month
  )
SELECT
  dept_key,
  SUM(amount) AS total_amount
FROM dept_sales
GROUP BY dept_key
ORDER BY dept_key;

共通表式で段階的にSQLを整理したい場合は、通常のWITH句と役割を分けると読みやすくなります。再帰WITHや階層問い合わせに近い考え方は再帰WITH句と階層問い合わせの実用パターンも参考になります。

DMLで使う場合はWITH_PLSQLを指定する

WITH FUNCTION をトップレベルのSELECTで使う場合は、そのまま書けます。一方、INSERT / UPDATE / DELETE / MERGE の中で使う場合は、トップレベル文に WITH_PLSQL を指定します。これは最適化ヒントではなく、PL/SQL宣言付きWITH句をその文で使うための指定です。

insert-with-plsql.sql

INSERT /*+ WITH_PLSQL */ INTO product_export (
  product_id,
  normalized_code,
  price_incl_tax
)
WITH
  FUNCTION normalize_code(p_code VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN UPPER(TRIM(p_code));
  END;

  FUNCTION tax_price(p_price NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ROUND(p_price * 1.10);
  END;
SELECT
  product_id,
  normalize_code(product_code),
  tax_price(price)
FROM products
WHERE export_flag = 'Y';

動的にSQLを組み立てる場合は、構文がさらに読みづらくなります。動的SQLの基本やバインド変数の扱いは Oracle EXECUTE IMMEDIATE完全ガイドも確認してください。

UPDATEで使う例

更新対象行ごとに整形関数を呼びたい場合も、トップレベルの UPDATE に WITH_PLSQL を付けます。ただし、大量更新では関数呼び出しがボトルネックになりやすいため、単純な変換ならSQL関数だけで書けないか先に検討します。

update-with-plsql.sql

UPDATE /*+ WITH_PLSQL */ products p
SET p.normalized_code = (
  WITH
    FUNCTION normalize_code(p_code VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
      RETURN UPPER(TRIM(REPLACE(p_code, '-', '')));
    END;
  SELECT normalize_code(p.product_code)
  FROM dual
)
WHERE p.normalized_code IS NULL;

注意: DMLで使えるからといって、複雑な業務ロジックをUPDATE文へ詰め込みすぎるのは危険です。障害時の再実行、ロック時間、実行計画、監査ログの見やすさまで含めて判断してください。

通常ファンクションとの違い

WITH FUNCTION は、そのSQL専用のローカル関数です。再利用したい処理、権限設計が必要な処理、テスト対象にしたい処理は、通常のファンクションやパッケージに分けたほうが向いています。

WITH FUNCTION一時的な抽出SQL、検証SQL、スポットのデータ整形に向いています。

スキーマレベル関数複数SQLから再利用する処理、権限やテストを管理したい処理に向いています。

パッケージ関数関連する処理をまとめ、状態や補助関数を整理したい場合に向いています。

SQL式単純な変換や条件分岐なら、CASEや標準関数だけで書くほうが読みやすいです。

DETERMINISTIC や RESULT_CACHE を含むファンクション設計、AUTHID の考え方は、プロシージャ・ファンクション完全ガイド側で詳しく扱っています。

性能面の注意点

WITH FUNCTION は便利ですが、行ごとにPL/SQL関数が呼ばれる形になるため、対象行が多いほど処理時間に影響します。100行程度の抽出では気にならなくても、数十万行・数百万行のバッチでは大きな差になることがあります。

単純な式CASE、COALESCE、REGEXP_REPLACE、標準関数で書けるならSQL式を優先します。

大量データ関数呼び出し回数を見積もり、実行計画と実測時間を確認します。

外部参照関数内で別表参照を繰り返す設計は避けます。JOINや共通表式で表現できないか検討します。

再利用処理複数SQLで使うなら通常ファンクション化し、テストと権限を管理します。

避けたいアンチパターン

WITH FUNCTIONは「SQLに何でも書ける道具」ではありません。特に次のような使い方は、実務では避けたほうが安全です。

副作用のある処理関数内でDML、COMMIT、外部送信のような処理を行う設計は避けます。

巨大な業務ロジック数十行を超える関数をSQLに埋め込むと、レビューも障害調査も難しくなります。

同じ関数のコピペ複数SQLへ同じ関数を貼り付け始めたら、通常ファンクション化の合図です。

性能未確認の大量実行大量行に対して呼び出す前に、CASE式やJOINで代替できるか検討します。

エラーになりやすいポイント

WITH FUNCTIONは構文上の位置が少し特殊なため、エラー時はまず「トップレベル文」「WITH_PLSQL」「関数の終了セミコロン」を確認します。

with-function-checklist.sql

-- 確認ポイント
-- 1. FUNCTION定義はWITH句の先頭側にあるか
-- 2. 各FUNCTIONのENDの後にセミコロンがあるか
-- 3. DMLで使う場合、トップレベル文に /*+ WITH_PLSQL */ があるか
-- 4. 同名の列名・関数名が衝突していないか
-- 5. 関数内でSQLから呼べない処理をしていないか

特にDML内で WITH_PLSQL を忘れると、構文自体はそれらしく見えても失敗します。この指定は実行計画を変えるためのヒントではなく、PL/SQL宣言付きWITH句を許可するためのものだと覚えておくと混乱しにくいです。