Posted at

【MySQL】ストアドプロシージャー①

More than 1 year has passed since last update.

MySQLのストアドプロシージャについてまとめます。


ストアドプロシージャとは

複数のSQL文をまとめて、名前を付けて関数のように呼び出せる。


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

1  mysql> delimiter //                        

2 mysql> create procedure sample()
3 -> begin
4 -> select * from User;
5 -> end
6 -> //
7 Query OK, 0 rows affected (0.00 sec)
8
9 mysql> delimiter ;


L1 delimiterの変更

delimiterとは終端文字のことです。

MySQLでは終端文字が;に設定されており、;があった時点でそこまでが実行されてしまいます。

上記のコードはストアドプロシージャの作成を行っていますが、delimiterを変更しないと、L4でこのスクリプト(CREATE PROCEDURE)が実行されてしまいます。

そのため、最初にdelimiterを変更し、作成が終わったらL9でdelimiterをもとに戻しています。


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

create procedure <PROCEDURE_NAME>でストアドプロシージャの作成を行います。


L3~L5 ストアドプロシージャの本体

begin~endの間にストアドプロシージャの本体を書きます。

今回はL4の1行ですが、複数行のSQLを書くことができます。


L6 終端

終端文字(delimiter)がL1で//に設定されていますので、ここでcreate procedureが実行されます。


L9 delimiterの変更

delimiterを元に戻します。


ストアドプロシージャの呼び出し

1  mysql> call sample();

call <PROCEDURE_NAME>()で作成したストアドプロシージャを呼び出すことができます。


ストアドプロシージャへの操作


ストアドプロシージャの確認

show create procedure <PROCEDURE_NAME>;


ストアドプロシージャの削除

drop procedure <PROCEDURE_NAME>;


ストアドプロシージャの書き方

以下、delimiterの設定等は省略します。


引数ありのストアドプロシージャを作成する

引数に設定されたIDを持つuserを取得するストアドプロシージャを作ります。

1  mysql> create procedure sample(IN id INT)      

2 -> begin
3 -> select * from User where ID = id;
4 -> end
5 -> //

引数の場合は定義にIN <INPUT_NAME> <DATA_TYPE>が必要になります(L1)。

これで、idという名のINT型の引数を定義することができます。

1  mysql> call sample(10);

上記のように呼び出すことができます。


出力をするストアドプロシージャを作成する

出力引数を持つストアドプロシージャも作成することができます。

ストアドプロシージャの実行後、ストアドプロシージャの外からアクセスすることができます。

1  mysql> create procedure sample(OUT res INT)      

2 -> begin
3 -> select count(*) from User into res;
4 -> end
5 -> //

出力引数の場合は、定義にOUT <OUTPUT_NAME> <DATA_TYPE>が必要です(L1)。

L3のように・・・into <OUTPUT_NAME>で出力変数に値を格納できます。

1  mysql> call sample(res);

2 mysql> select res;

実行時の引数に変数を渡し、select <OUTPUT_NAME>でストアドプロシージャ内で、出力変数に格納された値を出力できます。