この記事の要点

ストアド・ファンクション = 必ず戻り値を返す名前付きルーチン。SELECT / WHERE 句から普通の関数のように呼べる 構文: CREATE FUNCTION name(args) RETURNS type AS ... (PostgreSQL / Oracle) や CREATE FUNCTION ... RETURNS type (MySQL) ストアド・プロシージャとの違い: Procedure は戻り値なし (OUT 引数で返す)、SQL 中に書けない / トランザクション制御可 MySQL は DETERMINISTIC / READS SQL DATA 等の特性宣言が必須、副作用の強い処理は禁止 性能観点: 行数の多い SELECT で関数呼び出しを増やすと致命的 — Deterministic 宣言 や 関数索引で軽減

ストアド・ファンクションとは

必ず戻り値を 1 つ返す、データベース内に保存される名前付きルーチン (関数) です。SQL の中で組み込み関数 (UPPER や SUBSTR など) と同じように呼び出せます。

-- Oracle / PL/SQL
CREATE OR REPLACE FUNCTION calc_tax(p_amount IN NUMBER)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
  RETURN ROUND(p_amount * 0.10);
END;
/

-- 使い方 — SELECT 中で普通の関数として呼べる
SELECT order_id, amount, calc_tax(amount) AS tax
FROM orders;

プロシージャとの違い

項目	ファンクション	プロシージャ
戻り値	必ず 1 つ返す	戻り値なし (OUT 引数で複数返す)
SELECT / WHERE 句での呼び出し	可	不可
呼び出し構文	SELECT my_func(...)	CALL my_proc(...) / EXEC my_proc
トランザクション制御 (COMMIT / ROLLBACK)	原則不可 (Oracle はオプション)	可
副作用 (INSERT / UPDATE)	非推奨 / DB によっては禁止	推奨利用ケース
典型用途	値の計算、変換、参照	業務処理、複数表更新

各 DB の方言

Oracle PL/SQL

CREATE OR REPLACE FUNCTION get_full_name(p_emp_id IN NUMBER)
RETURN VARCHAR2
IS
  v_name VARCHAR2(100);
BEGIN
  SELECT last_name || ' ' || first_name
    INTO v_name
    FROM employees
   WHERE emp_id = p_emp_id;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

-- 呼び出し
SELECT get_full_name(1234) FROM dual;

PostgreSQL (plpgsql)

CREATE OR REPLACE FUNCTION get_full_name(p_emp_id INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE                          -- 同じ引数なら同じ結果
AS $$
DECLARE
  v_name TEXT;
BEGIN
  SELECT last_name || ' ' || first_name
    INTO v_name
    FROM employees WHERE emp_id = p_emp_id;
  RETURN v_name;
END;
$$;

-- 呼び出し
SELECT get_full_name(1234);

PostgreSQL は plpgsql 以外に plpython3u / plperl / SQL 等を選択できます。簡単な集計なら LANGUAGE SQL の方が高速。

-- SQL 言語版 (インライン展開されやすく速い)
CREATE OR REPLACE FUNCTION add_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE
AS $$
  SELECT ROUND(amount * 1.10, 0)
$$;

MySQL

DELIMITER //

CREATE FUNCTION get_full_name(p_emp_id INT)
RETURNS VARCHAR(100)
DETERMINISTIC                       -- 同じ引数なら同じ戻り値
READS SQL DATA                      -- DML せず SELECT する宣言
BEGIN
  DECLARE v_name VARCHAR(100);
  SELECT CONCAT(last_name, ' ', first_name) INTO v_name
  FROM employees WHERE emp_id = p_emp_id;
  RETURN v_name;
END //

DELIMITER ;

-- 呼び出し
SELECT get_full_name(1234);

MySQL の制限 — DETERMINISTIC / NO SQL / READS SQL DATA / MODIFIES SQL DATA / CONTAINS SQL のいずれかを必ず宣言する必要があります。バイナリログを有効化している場合、宣言が無いと作成エラー。

SELECT 内で呼び出す注意 — N+1 と関数索引

1 行ごとに重い処理を呼ぶと、件数に比例して劇的に遅くなります。

-- ❌ 100 万行 SELECT 内で関数呼び出し
SELECT order_id, calc_tax(amount) AS tax
FROM orders;
-- → 100 万回の関数呼出 + 100 万回のコンテキストスイッチ

-- ✅ Deterministic 宣言で結果キャッシュが効くケースがある
CREATE OR REPLACE FUNCTION calc_tax(p_amount NUMBER)
  RETURN NUMBER DETERMINISTIC      -- ← Oracle はクエリ実行中に結果キャッシュ
AS BEGIN
  RETURN ROUND(p_amount * 0.10);
END;
/

-- ✅ 関数索引 (Function-Based Index) を張ってインデックスを効かせる
CREATE INDEX idx_orders_tax ON orders(calc_tax(amount));

-- ✅ 列に持たせて更新時に計算 (Generated Column)
ALTER TABLE orders
  ADD tax_amount NUMBER GENERATED ALWAYS AS (ROUND(amount * 0.10));
CREATE INDEX idx_orders_tax ON orders(tax_amount);

表を返すファンクション (PIPELINED / Table-Valued)

1 つの値だけでなく、結果セット (表) を返すこともできます。FROM 句から呼べるので、ビューより柔軟。

-- PostgreSQL: SETOF / TABLE
CREATE OR REPLACE FUNCTION recent_orders(p_user_id INT)
RETURNS TABLE(order_id INT, amount NUMERIC, created_at TIMESTAMP)
LANGUAGE SQL
AS $$
  SELECT order_id, amount, created_at
  FROM orders
  WHERE user_id = p_user_id
  ORDER BY created_at DESC
  LIMIT 100
$$;

-- FROM 句から呼べる
SELECT * FROM recent_orders(1234);

削除と一覧

-- 一覧
-- Oracle
SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION';
-- PostgreSQL
\df

-- MySQL
SHOW FUNCTION STATUS WHERE Db = 'mydb';

-- 削除
DROP FUNCTION calc_tax;

FAQ

Q: ファンクションとビューの使い分け？
A: ビューは「保存された SELECT」で引数を取れません。引数で結果を変えたい場合はファンクション (Table-Valued) を選びます。

Q: 副作用 (INSERT/UPDATE) を起こすファンクションを書ける？
A: 技術的には可ですが 推奨されません。SELECT 中に呼ばれると意図しない件数更新が走り、再現困難なバグになります。副作用が必要なら Procedure に分けましょう。

Q: DETERMINISTIC を付けるとどう変わる？
A: 「同じ入力なら同じ結果」と宣言したことで、オプティマイザがキャッシュや関数索引を使えるようになります。実態が条件次第で結果が変わる場合に宣言すると、結果不整合のバグになるので注意。