◆ストアド・プロシージャって?
MySQLで
SQL処理を関数化できる機能です。変数だったりwhileやifも使えますのでダミーデータを作ったりするのに便利です。
◆大まかな流れ
①create procedure hogehoge() でプロシージャを作る
②call hogehoge() でプロシージャを実行する
です。
◆テスト用テーブルの作成
CREATE TABLE article(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
number INT(10) UNSIGNED NOT NULL,
title VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
);
◆定義(insert)
delimiter //
CREATE PROCEDURE prc_insert(IN n INT, IN t VARCHAR(20))
BEGIN
INSERT INTO article (number, title) VALUES(n, t);
END
//
delimiter ;
◆実行(insert)
call prc_insert(10, 'piyo');
---------------------------------解説---------------------------------
delimiter //
これはデリミター(
複数の要素を列挙する際に、要素の区切りとなる記号や
特殊文字)を切り替えるコマンドです。
mysqlはデフォルトでは「;」がデリミターになっています。
しかしこのままだとプロシージャの定義の時に「;」で実行してしまうので、定義の時は実行しないようにデリミターを変更します。
CREATE PROCEDURE prc_insert(IN n INT, IN t VARCHAR(20))
プロシージャの作成です。引数は
IN/OUT 変数 型
の順に指定します。今回はINSERTなので入力型(IN)を使っています。
BEGIN
INSERT INTO article (number, title) VALUES(n, t);
END
BEGIN、ENDの間に
SQLを入れれば実行できます。
また引数もそのまま使用できます。
delimiter ;
デリミターを元に戻しています。間に半角スペースが無いと変更されないので注意してください。
---------------------------------解説終わり---------------------------------
もう1つ、今度はSELECTの方の実行をします。
結果が出てくるので、引数には出力型(OUT)を使用します。
◆定義(select)
delimiter //
CREATE PROCEDURE prc_select(IN uid INT, OUT result1 INT, OUT result2 VARCHAR(20))
BEGIN
SELECT number, title FROM article WHERE id = uid INTO result1, result2;
END
//
delimiter ;
◆実行(select)
call prc_select (1, @a, @b);
Query OK, 1 row affected (0.00 sec)
SELECT @a, @b;
+------+------+
| @a | @b |
+------+------+
| 10 | piyo |
+------+------+
1 row in set (0.00 sec)
---------------------------------解説---------------------------------
CREATE PROCEDURE prc_select(IN uid INT, OUT result1 INT, OUT result2 VARCHAR(20))
ほぼ変わりません。IN->OUTになったくらいですね。
BEGIN
SELECT number, title FROM article WHERE id = uid INTO result1, result2;
END
ここで出力型の引数にした変数(result1, result2)に入れる部分が必要になります。
※このやり方は複数レコードは取得できません。複数レコードを利用する場合は次の時に紹介します。
call prc_select (1, @a, @b);
またcall時は「@引数」を指定して戻り値を受け取り、実際に値を呼び出すときは
SELECT @a, @b;
として戻り値として受け取ったデータを呼び出す作業が必要です。
なんだか二度手間で面倒ですね。
---------------------------------解説終わり---------------------------------
◆その他
作成したプロシージャの一覧表示
SHOW PROCEDURE STATUS;
作成したプロシージャの詳細表示
SHOW CREATE PROCEDURE prc_insert;
プロシージャの削除
DROP PROCEDURE prc_insert;
ソース:
[Sy] 【技術メモ】MySQLで初めてストアドプロシージャを作ってみた
[Sy] 【技術メモ】MySQLのストアドプロシージャを使ってinsertとかdeleteとかをループさせて実行
MySQL ストアドプロシージャ
MySQL のストアドプロシージャ