前回記事：MySQLでよく見かけるエラーの発生原因と対策方法では、MySQLでよくあるエラーについてご紹介しました。
今回は、MySQLで初めてプロシージャやファンクションなどのストアドルーチンに触れる方へ、基本の書き方やコンパイル方法をご紹介したいと思います。
目次
１．１ ストアドルーチンとは
１．２ ストアドプロシージャとストアドファンクションの違い
２．１ ストアドプロシージャ／ファンクション使用のメリット
２．２ ストアドプロシージャ／ファンクション使用のデメリット
３．１ ストアドプロシージャを定義する
３．２ ストアドプロシージャを登録する
３．３ ストアドプロシージャを実行する
３．４ ストアドプロシージャを削除する
４．１ ストアドファンクションを定義する
４．２ ストアドファンクションを登録する
４．３ ストアドファンクションを実行する
４．４ ストアドファンクションを削除する
１．MySQLのストアドルーチンとは？
１．１ ストアドルーチンとは
ストアドルーチンとは、サーバ上に名前を付けて格納しておけるひと塊のSQLのことです。ストアドプロシージャとストアドファンクション（ユーザ定義関数）の２種類からなり、毎回長いクエリを発行しなくても、いつでも呼び出して使えます。
またストアドルーチンでは、単体で発行するSQLでは使用できない条件分岐やループの処理、変数定義などの処理が使えるなど、プログラム言語のような使用感で自由度の高い処理を記述することができることも大きな特徴となっています。
１．２ ストアドプロシージャとストアドファンクションの違い
ストアドプロシージャとストアドファンクションはほぼ同じ動作をしますが、大きく異なる点がひとつあります。それは「プロシージャには戻り値がないが、ファンクションには戻り値がある」という点です。
ストアドファンクションは別名「ユーザ定義関数」ですが、戻り値があることからデフォルトで用意されている関数と同じような使用感で使うことができます。
またMySQLの場合については「プロシージャは再帰的に呼び出して使用可能だが、ファンクションは再帰的に使用できない」などの固有の特徴もあります。ただしMySQL以外のデータベースではその限りではありません。
２．ストアドルーチン使用のメリットとデメリット
２．１ ストアドプロシージャ／ファンクション使用のメリット
長々とクエリを発行しなくても名前を呼ぶだけで実行することができるので、ネットワークの負荷を軽減することができます。また事前にコンパイルされた状態でサーバに保存されているため、新規にクエリを発行するよりも実行が速くなります。これらのことから、ストアド「ルーチン」という名前が示す通り「何度も実行する共通部品」の作成にとても適した機能となっています。
その他のメリットとして、安全性が高いことも挙げられます。アプリケーションなど外部から直接テーブルを参照することを禁止しストアドルーチンの呼び出しのみを許可することで、不正なアクセスを防止することに役立ちます。
２．２ ストアドプロシージャ／ファンクション使用のデメリット
ストアドルーチンを使用するデメリットとしては、アプリケーションサーバで代行できる処理まで便利だからとストアド側で行うようにしてしまうと、結果としてデータベースサーバの負荷が増大する可能性がある点が挙げられます。しかしその分アプリケーションサーバの負荷は軽減できることを考えると、それほど大きなデメリットにはならないかもしれません。
３．ストアドプロシージャの定義と実行
３．１ ストアドプロシージャを定義する
ストアドプロシージャの定義には、「CREATE PROCEDURE文」を使用します。
CREATE PROCEDURE [プロシージャ名]([IN／OUT] [引数] [データ型])
BEGIN
[・・・一連の処理を記載・・・]
END;
戻り値を持たないプロシージャでは、引数の前にそれが入力引数なのか出力引数なのか、「IN」もしくは「OUT」で指定する必要があります。
また引数は、カンマで区切って複数指定することが可能です。
３．２ ストアドプロシージャを登録する
ストアドプロシージャの登録は「CREATE～END;」までの記述を全てそのまま入力するだけで行うことができますが、そこで問題となるのはデリミタ（終端文字）です。
通常コマンドライン上で使用するデリミタは「;」ですが、ストアドルーチンの場合は本文中の処理で「;」が登場してしまうため、そのまま入力しようとすると一番最初の「;」でエラーとなってしまいます。そこで「DELIMITER」コマンドを使用して終端文字を「//」に変更し、「CREATE～END;」の末尾に「//」を追加して実行します。
DELIMITER //
CREATE PROCEDURE proc_test01(IN input CHAR(5))
BEGIN
SELECT USER_NAME FROM USER_DATA WHERE USER_ID = input;
END;
//
「Query OK,～」と表示されたら、登録完了です。
登録後は、忘れないようデリミタを「;」に戻すようにして下さい。
DELIMITER ;
３．３ ストアドプロシージャを実行する
登録済みのストアドプロシージャを呼び出すには、「CALL」を使用します。
CALL [プロシージャ名]([引数]);
３．４ ストアドプロシージャを削除する
プロシージャを削除したい場合は、「DROP PROCEDURE文」を使用します。
DROP PROCEDURE [プロシージャ名];
４．ストアドファンクションの定義と実行
４．１ ストアドファンクションを定義する
ストアドファンクションの定義には、「CREATE FUNCTION文」を使用します。
CREATE FUNCTION [ファンクション名]([引数] [引数のデータ型])
RETURNS [戻り値のデータ型] [DETERMINISTIC／NOT DETERMINISTIC]
BEGIN
[・・・一連の処理を記載・・・]
RETURN(戻り値);
END;
戻り値を持つファンクションには、引数の前の「IN／OUT」は不要です。その代わりに、「RETURNS」の後に戻り値のデータ型を指定します。
さらに入力値が同じであれば常に戻り値が同じになる場合は末尾に「DETERMINISTIC」を、異なる可能性がある場合は「NOT DETERMINISTIC」を末尾に記載します。
４．２ ストアドファンクションを登録する
こちらも実際に登録する際には、まずデリミタを変更します。
DELIMITER //
CREATE FUNCTION func_test01(input INT) RETURNS FLOAT(10,2) DETERMINISTIC
BEGIN
DECLARE zei_ritsu FLOAT(3,2);
SET zei_ritsu = 1.08;
RETURN input * zei_ritsu;
END;
//
サンプルで使用している「DECLARE文」は、変数の宣言です。
DELARE [変数名] [データ型];
変数などに値を代入するには、「SET文」を使用します。
SET [変数名] = [代入値];
これらはプロシージャでも同様に使用できます。
「Query OK,～」と表示されたら、登録完了です。
なお上図の「AS」を使用することで、一時的に項目名を変更して表示することができます。
ASを使用しない場合は、下図のようにそのまま表示されます。ASは表示をすっきりさせたい場合などにご利用下さい。
４．４ ストアドファンクションを削除する
ファンクションを削除したい場合は、「DROP FUNCTION文」を使用します。
DROP FUNCTION [ファンクション名];
５．登録済みストアドルーチンの確認
５．１ 登録済ストアドルーチンの一覧を表示する
登録済みプロシージャの一覧を表示したい場合は、「information_schema.ROUTINES」というテーブルを参照することで表示することができます。
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';
最後の'PROCEDURE'を'FUNCTION'に変更することで、ファンクションの一覧を表示することもできます。
なおストアドルーチンの名称（ROUTINE_NAME）やストアドルーチンの種類（ROUTINE_TYPE）以外には次のような項目がありますので、必要に応じて追加して下さい。
５．２ 登録済みストアドルーチンのソースをDBから取得する
ストアドプロシージャの内容を確認するためには、「SHOW CREATE PROCEDURE文」を使用します。
SHOW CREATE PROCEDURE [プロシージャ名];
このようにコマンドライン上から閲覧する場合、プロシージャの内容まで表示されるので横表示ではたくさんの改行が発生して見難い状態です。そこでオプション「\G」を付けることによって、縦に並べることもできます。
SHOW CREATE PROCEDURE [プロシージャ名] \G;
当サイトプロエンジニアのコンサルタントが厳選したおすすめのフリーランス案件特集はこちら
特集ページから案件への応募も可能です！
実際にフリーランスエンジニアとして活躍されている方のインタビューはこちら
