ストアドプロシージャとは？MySQLでストアドプロシージャを作成する方法をわかりやすく解説

ストアドプロシージャ（Stored Procedure）とは、一般的なプログラミング言語におけるサブプログラムのような「サブルーチン」を、データベース内に保存して利用できるようにしたものです。MySQLにおいては、データベースのカタログ内に格納された宣言的なSQLステートメントの一連のかたまりとして定義されます。

なお、MySQLでストアドプロシージャを使用する前に、必ずバージョンを確認しておきましょう。ストアドプロシージャはMySQL 5.0以降で導入された機能のため、それ以前のバージョンでは利用できません。

ストアドプロシージャを作成する構文

MySQLでストアドプロシージャを作成するには、CREATE PROCEDURE文を使用します。基本的な構文は以下のとおりです。

CREATE [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter: [ IN | OUT | INOUT ] param_name type
type:
任意の有効なMySQLデータ型
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA
| MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
有効なSQLルーチンステートメント

パラメータには、入力専用のIN、出力専用のOUT、入出力両方に使えるINOUTの3種類を指定できます。また、COMMENTで説明を付与したり、DETERMINISTICで同じ入力に対して常に同じ結果を返すことを明示したりすることも可能です。

ストアドプロシージャの作成例

ここでは、「student_info」テーブルからすべてのレコードを取得する簡単なストアドプロシージャを作成してみましょう。まず、対象となるテーブルのデータは以下のとおりです。

mysql> select * from student_info;
+-----+---------+------------+------------+
| id  | Name    | Address    | Subject    |
+-----+---------+------------+------------+
| 100 | Aarav   | Delhi      | Computers  |
| 101 | YashPal | Amritsar   | History    |
| 105 | Gaurav  | Jaipur     | Literature |
| 110 | Rahul   | Chandigarh | History    |
+-----+---------+------------+------------+
4 rows in set (0.00 sec)

続いて、以下のクエリを実行して、すべてのレコードを取得する「allrecords()」という名前のストアドプロシージャを作成します。

mysql> Delimiter //
mysql> Create Procedure allrecords()
    -> BEGIN
    -> Select * from Student_info;
    -> END//
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;

ポイントは、プロシージャ本体の中でセミコロン（;）を使うため、あらかじめDELIMITER //でデリミタを変更している点です。これにより、BEGIN〜ENDブロック内のセミコロンがステートメントの区切りとして誤認識されるのを防ぎます。作成が完了したら、DELIMITER ;で元のデリミタに戻すのを忘れないようにしましょう。

作成したストアドプロシージャを実行するには、次のようにCALL文を使用します。

mysql> CALL allrecords();

このようにストアドプロシージャを活用すれば、頻繁に使う一連のSQL処理をデータベース側に保存し、再利用性と実行効率を高めることができます。