読者です 読者をやめる 読者になる 読者になる

Carpe Diem

備忘録。https://github.com/jun06t

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

MySQL
◆ストアド・プロシージャって? MySQLSQL処理を関数化できる機能です。変数だったり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 のストアドプロシージャ