ストアドプロシージャとの違い

ユーザー定義関数はストアドプロシージャと異なり、単一の値を返すことだけが許されます。結果セットの返却は一切できず、SELECT文を記述してもコンパイルエラーになります。次のコードは無効な例です。

-- エラー：関数から結果セットを返せません
CREATE FUNCTION invalid_func() RETURNS INT
BEGIN
  SELECT * FROM employees;
  RETURN 100;
END;

関数の作成

基本構文は下記の通りです。戻り値のデータ型とRETURNが必須です。

CREATE FUNCTION 関数名([引数リスト]) RETURNS データ型
BEGIN
  SQL文;
  RETURN 値;
END;

引数リストは「パラメータ名 データ型」の形式で宣言します。

単純な定数返却

CREATE FUNCTION const_func() RETURNS INT RETURN 999;
SELECT const_func(); -- 呼び出し

テーブルから値を取得する関数

次の例ではdepartmentsテーブルからdep_name='経理部'のdep_idを返します。

CREATE FUNCTION get_accounting_dept_id() RETURNS INT
BEGIN
  DECLARE target_id INT;
  SELECT dep_id INTO target_id FROM departments
  WHERE dep_name = '経理部';
  RETURN target_id;
END;

パラメータ付き関数

任意の部署名を与えて該当するIDを取得する関数です。

CREATE FUNCTION get_dept_id_by_name(p_name VARCHAR(50)) RETURNS INT
BEGIN
  DECLARE dept_id INT;
  SELECT dep_id INTO dept_id FROM departments
  WHERE dep_name = p_name;
  RETURN dept_id;
END;

補足オプション

RETURNS指定の後、BEGINの前にCOMMENTなどのオプションを付与できます。関数の内部ロジックは変更せずに説明文だけを後から修正することも可能です。

CREATE FUNCTION get_dept_id_by_name(p_name VARCHAR(50)) RETURNS INT
COMMENT '部署名から部署IDを返す'
BEGIN
  ...
END;

関数の呼び出し

関数は式の中で評価されるため、必ずSELECTと組み合わせて呼び出します。単独で関数名だけを記述すると構文エラーになります。

-- 無引数呼び出し
SELECT const_func();

-- 引数付き呼び出し
SELECT get_dept_id_by_name('営業部');

-- クエリ内での利用
SELECT emp_name FROM employees
WHERE dept_id = get_dept_id_by_name('開発部');

関数の確認

定義の確認にはSHOW CREATE FUNCTION、一覧表示にはSHOW FUNCTION STATUSを使用します。

-- 特定の関数の定義を表示
SHOW CREATE FUNCTION get_dept_id_by_name;

-- データベース内のすべての関数を一覧表示
SHOW FUNCTION STATUS;

-- パターン一致で絞り込み
SHOW FUNCTION STATUS LIKE '%dept%';

関数の変更

関数本体のSQL文や引数リストを後から書き換えることはできません。ALTER FUNCTIONで変更可能なのはCOMMENTなどの付加的な属性のみです。

ALTER FUNCTION get_dept_id_by_name COMMENT '部署名からIDを返す（更新済）';

関数の削除

不要になった関数はDROP FUNCTIONで削除します。

DROP FUNCTION get_dept_id_by_name;