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>でストアドプロシージャ内で、出力変数に格納された値を出力できます。