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)

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

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

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