ストアド・プロシージャを使う

◆ストアド・プロシージャって？ 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 ;

デリミターを元に戻しています。間に半角スペースが無いと変更されないので注意してください。 ---------------------------------解説終わり--------------------------------- もう１つ、今度は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 のストアドプロシージャ

Carpe Diem

備忘録

ストアド・プロシージャを使う