MariaDB / MySQLでストアドプロシージャを作成する方法｜DECLARE・カーソル・ループ処理の実例付き
MariaDBやMySQLでストアドプロシージャ（CREATE PROCEDURE）を作成する方法を、DECLARE文・カーソル・ループ処理を含めて実例付きで解説します。
このページでは、DELIMITERの使い方や、CALLによる実行、DROP PROCEDUREによる削除方法まで、実務で役立つ構文を網羅しています。
「mariadb procedure example」「mysql カーソル」「mariadb declare」などのキーワードでお探しの方におすすめです。
ストアドプロシージャはデータベース内で動作するプログラムです。
変数を使うこともできますし、条件分岐、繰り返し構文も使えます。
また、カーソルを使うと１行ずつ処理することもできます。
テスト用データベースとテーブルの作成
説明のためにテスト用データベースとテーブル構造の作成です。
-- データベース作成 CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE db_test; -- テーブル作成 CREATE TABLE t_item(item_id INT, kind_id INT, item_name VARCHAR(100)); -- テーブルにレコードを挿入 INSERT INTO t_item values(1,1,'キャベツ'),(2,1,'レタス'),(3,1,'人参'),(4,1,'大根'),(5,2,'洗剤'),(6,2,'シャンプー');
ストアドプロシージャの作成
ストアドプロシージャの作成は以下の構文となります。
CREATE PROCEDURE 作成するストアドプロシージャの名前(引数)
BEGIN
プログラム
END;
以下はSELECT文を実行するストアドプロシージャ名「proc_test1」として作成します。
DELIMITER // -- 「;」を「//」に一時的に変更する CREATE PROCEDURE proc_test1(IN in_item_name VARCHAR(100)) BEGIN SELECT * FROM t_item WHERE item_name LIKE in_item_name; END; // DELIMITER ; -- 「//」を「;」に戻す
ストアドプロシージャを実行するコマンドは以下です
CALL ストアドプロシージャ名;
MariaDB [db_test]> CALL proc_test1('%キャベ%');
+---------+---------+--------------+
| item_id | kind_id | item_name |
+---------+---------+--------------+
| 1 | 1 | キャベツ |
+---------+---------+--------------+
1 row in set (0.001 sec)
Query OK, 0 rows affected (0.006 sec)
カーソルを使用したストアドプロシージャの作成
ストアドプロシージャは変数やカーソルやループを使用することもできます。
DELIMITER // -- 「;」を「//」に一時的に変更する CREATE PROCEDURE proc_test2(IN in_item_name VARCHAR(20000)) BEGIN -- 変数の宣言 DECLARE txt VARCHAR(20000) DEFAULT ''; DECLARE done BOOLEAN DEFAULT false; DECLARE a_item_id INT; DECLARE a_kind_id INT; DECLARE a_item_name VARCHAR(100); -- カーソルの宣言 DECLARE mycursor CURSOR FOR SELECT item_id,kind_id,item_name FROM t_item WHERE item_name LIKE in_item_name; -- カーソルが最終レコードに到達したら「done=true」に設定する DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; -- カーソルを開く OPEN mycursor; -- ループの開始 loop_label: LOOP FETCH mycursor INTO a_item_id, a_kind_id, a_item_name; -- カーソルが最終レコードに到達したらループを抜ける IF done = true THEN LEAVE loop_label; END IF; SET txt = CONCAT(txt, a_item_name, ','); -- ループの終了 END LOOP; -- カーソルを閉じる CLOSE mycursor; SET txt = LEFT(txt, CHAR_LENGTH(txt) - 1); SELECT txt; END; // DELIMITER ; -- 「//」を「;」に戻す
ストアドプロシージャを実行します。
CALL proc_test2('%');
MariaDB [db_test]> CALL proc_test2('%');
+-------------------------------------------------------------+
| txt |
+-------------------------------------------------------------+
| キャベツ,レタス,人参,大根,洗剤,シャンプー |
+-------------------------------------------------------------+
1 row in set (0.002 sec)
Query OK, 0 rows affected (0.009 sec)
ストアドプロシージャの一覧
ストアドプロシージャの一覧表示は以下の構文となります。
SHOW PROCEDURE STATUS WHERE DB='データベース名';
MariaDB [db_test]> SHOW PROCEDURE STATUS WHERE DB='db_test'; +---------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +---------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | db_test | proc_test2 | PROCEDURE | root@localhost | 2024-06-28 15:23:53 | 2024-06-28 15:23:53 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb3_general_ci | +---------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 1 rows in set (0.015 sec)
ストアドプロシージャの確認
ストアドプロシージャの確認は以下の構文となります。
SHOW CREATE PROCEDURE ストアドプロシージャの名前;
MariaDB [db_test]> show create procedure proc_test1; +------------+------------------------------+------------------+----------------------+------+ | Procedure | sql_mode | Create Procedure | character_set_client | 省略 | +------------+------------------------------+------------------+----------------------+------+ | proc_test1 | STRICT_TRANS_TABLES,省略・・ | | | | |CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_test1`(IN in_item_name VARCHAR(100)) | |BEGIN | | SELECT * FROM t_item WHERE item_name LIKE in_item_name; | |END | | | utf8mb4 | | +------------+------------------------------+------------------+----------------------+------+ 1 row in set (0.000 sec)
ストアドプロシージャの削除
ストアドプロシージャの削除は以下の構文となります。
DROP PROCEDURE ストアドプロシージャの名前;
MariaDB [db_test]> DROP PROCEDURE proc_test1; Query OK, 0 rows affected (0.007 sec)