MariaDB(MySQL)でストアドプロシージャの作成(CREATE PROCEDURE)とカーソルの使用

テスト用データベースとテーブルの作成

説明のためにテスト用データベースとテーブル構造の作成です。

-- データベース作成
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)

ストアドプロシージャの削除

ストアドプロシージャの削除は以下の構文となります。
DROP PROCEDURE ストアドプロシージャの名前;

MariaDB [db_test]> DROP PROCEDURE proc_test1;
Query OK, 0 rows affected (0.007 sec)