MySQL のストアドプロシージャ

ここでは MySQL のストアドプロシージャについてご説明します。

サンプルデータベースのテーブルを使いますので、実際にスクリプトを実行してみたい方は、こちら のスクリプトを実行して school_db データベースを作成しておいてください。

目次
  1. MySQL のストアドプロシージャを作成する
  2. MySQL のストアドプロシージャを実行する
  3. MySQL のストアドプロシージャを削除する
  4. 出力引数のあるストアドプロシージャを実行する

MySQL の ストアドプロシージャを作成する

MySQL でストアドプロシージャを作成するには、CREATE PROCEDURE 文を使います。

CREATE PROCEDURE 文の基本的な構文は次の通りです。

DELIMITER //
CREATE PROCEDURE ストアドプロシージャ名 (
	[ IN | OUT | INOUT ] 引数名 データ型
)
BEGIN
	SQL ルーチンステートメント
END//
DELIMITER ;

まず、ストアドプロシージャの定義の中でデフォルトの DELIMITER の ; を使うので、DELIMITER を定義の中で使わない他の文字（ここでは //）に変更します。

引数はオプショナルで、なくても大丈夫です。

引数のタイプには IN（入力引数）、OUT（出力引数）、INOUT（入出力引数）があり、省略した時は IN になります。


それでは、簡単なストアドプロシージャを作成してみます。

次のような exams テーブルがあります。

MySQL のストアドプロシージャ 1


この in_exam_name_en と in_exam_name_jp を入力引数として受け取り、exams テーブルにレコードを挿入するストアドプロシージは次の通りです。

DELIMITER //
CREATE PROCEDURE insert_exam (
	in_exam_name_en VARCHAR(50),
	in_exam_name_jp VARCHAR(50)
)
BEGIN

	INSERT INTO exams
		( exam_name_en, exam_name_jp )
    VALUES  
		(in_exam_name_en, in_exam_name_jp);
    
END//
DELIMITER ;

このクエリーを実行すると、insert_exam という名前のストアドプロシージャが作成されます。

MySQL Workbench の Schemas タブをリフレッシュすると、Stored Procedures に insert_exam ができていますね。

MySQL のストアドプロシージャ 2


MySQL の ストアドプロシージャを実行する

MySQL のストアドプロシージャを実行するには、CALL 文を使います。

CALL ストアドプロシージャ名([引数1, 引数2, ...]);

例えば、先ほど作成した insert_exam を実行して、exams テーブルにデータを挿入するには次のようにします。

CALL insert_exam ('Chemistry1', '化学１');

このクエリーを実行して、exams テーブルの中身を確認します。

MySQL のストアドプロシージャ 3

MySQL のストアドプロシージャ 4


exams テーブルに入力引数として渡した、'Chemistry1', '化学１'が挿入されています。

MySQL の ストアドプロシージャを削除する

MySQL でストアドプロシージャを削除するには、DROP PROCEDURE 文を使います。

DROP PROCEDURE 文の構文は次の通りです。

DROP PROCEDURE ストアドプロシージャ名;

例えば、先ほど作成した insert_exam という名前のストアドプロシージャを削除したいのであれば次のようにできます。

DROP PROCEDURE insert_exam;

このクエリーを実行すると insert_exam ストアドプロシージャが削除されます。

MySQL のストアドプロシージャ 5


DROP PROCEDURE 文は、実行した時に指定したストアドプロシージャが存在しないとエラーになります。

指定したストアドプロシージャが存在する時のみ削除したい場合は、IF EXISTS を次のように追加します。

DROP PROCEDURE IF EXISTS ストアドプロシージャ名;

出力引数のあるストアドプロシージャを実行する

次は出力引数のあるストアドプロシージャを作成して、そのストアドプロシージャを実行して出力引数の値を確認してみましょう。

例えば、insert_exam ストアドプロシージャを変更して、exams テーブルに挿入したレコードの exam_id を出力引数として取得するには次のようにできます。

DROP PROCEDURE IF EXISTS insert_exam;
DELIMITER //
CREATE PROCEDURE insert_exam (
	in_exam_name_en VARCHAR(50),
	in_exam_name_jp VARCHAR(50),
    OUT out_new_exam_id INT
)
BEGIN

	INSERT INTO exams
		( exam_name_en, exam_name_jp )
    VALUES  
		(in_exam_name_en, in_exam_name_jp);
        
	SET out_new_exam_id = LAST_INSERT_ID();
    
END//
DELIMITER ;

変更した箇所は 1 行目、6 行目、15 行目です。

1 行目では、insert_exam ストアドプロシージャを書き換えるために、insert_exam が存在する時は削除しています。

6 行目では out_new_exam_id という名前の出力引数を定義しています。

15 行目では、out_new_exam_id に LAST_INSERT_ID() 関数を使って、最後に挿入された AUTOINCREMENT カラムの値を代入しています。


このクエリーを実行して、insert_exam ストアドプロシージャを再生成します。

そして、この 出力引数のあるストアドプロシージャを実行するには次のようにできます。

CALL insert_exam ('Physics1', '物理１', @new_exam_id);
SELECT @new_exam_id;

MySQL のストアドプロシージャ 6

MySQL のストアドプロシージャ 7

SELECT 文で @new_exam_id を確認すると、exams テーブルに挿入された Physics1'、'物理' のレコードの exam_id の 5 が取得できていますね。


以上、MySQL のストアドプロシージャについてご説明しました。

© 2024 MySQL 入門