【ITニュース解説】Cursor + Trigger in MySQL

データベースを操作する際、特定の条件下でデータを一つずつ処理したり、あるイベントが発生した際に自動的に処理を実行したりする場面がよくある。MySQLには、このような高度なデータ操作を可能にする「カーソル」と「トリガー」という強力な機能が備わっている。これらの機能は、システムエンジニアを目指す上でデータベースの深い理解に役立つため、その仕組みと使い方について解説する。

まず「カーソル」について説明する。 カーソルとは、データベースから取得した複数のデータ行（結果セット）を、プログラムの中で一行ずつ順番に処理するための仕組みである。通常のSQL文は一度に複数の行を操作するが、カーソルは特定の条件に合致する行を一つずつ取り出して、それぞれに個別の処理を行いたい場合に利用される。

カーソルの具体的な利用例として、給料が50000を超える従業員を抽出するケースを考える。 最初に、従業員情報を格納するEmployeeテーブルを作成する。このテーブルには、emp_id（従業員ID）、emp_name（従業員名）、salary（給与）の3つの列がある。その後、いくつかのサンプルデータを挿入し、テーブルにデータを用意する。

Copy
1CREATE TABLE Employee (
2    emp_id INT PRIMARY KEY,
3    emp_name VARCHAR(50),
4    salary INT
5);
6INSERT INTO Employee VALUES
7(1, 'Alice', 40000),
8(2, 'Bob', 55000),
9(3, 'Charlie', 70000),
10(4, 'David', 45000);

次に、このテーブルに対してカーソルを使用するストアドプロシージャを作成する。ストアドプロシージャとは、一連のSQL文をまとめてデータベースに保存し、必要に応じて呼び出して実行できる機能である。

Copy
1DELIMITER //
2CREATE PROCEDURE GetHighSalaryEmployees()
3BEGIN
4    DECLARE done INT DEFAULT FALSE;
5    DECLARE empName VARCHAR(50);
6    DECLARE cur CURSOR FOR SELECT emp_name FROM Employee WHERE salary > 50000;
7    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
8
9    OPEN cur;
10
11    read_loop: LOOP
12        FETCH cur INTO empName;
13        IF done THEN
14            LEAVE read_loop;
15        END IF;
16        SELECT empName AS High_Salary_Employee;
17    END LOOP;
18
19    CLOSE cur;
20END//
21DELIMITER ;

このコードの冒頭にあるDELIMITER //は、複数のSQL文を含むプロシージャ定義ブロックの区切り文字を一時的に//に変更する指示である。これにより、プロシージャ内部のセミコロンが文の区切りとして機能し、CREATE PROCEDURE文全体が一つのまとまりとしてデータベースに登録される。プロシージャの定義が終わった後には、DELIMITER ;で区切り文字を元のセミコロンに戻す。

CREATE PROCEDURE GetHighSalaryEmployees()は、「高給与の従業員を取得する」という名前のプロシージャを定義している。プロシージャの内部では、まずいくつかの変数を宣言する。DECLARE done INT DEFAULT FALSE;はカーソル処理の終了を示すフラグ変数で、初期値はFALSEである。DECLARE empName VARCHAR(50);は、カーソルから取得した従業員名を一時的に保持する変数である。 最も重要なのが、DECLARE cur CURSOR FOR SELECT emp_name FROM Employee WHERE salary > 50000;である。これは、「給与が50000を超える従業員の名前」を選択するクエリの結果を扱うcurという名前のカーソルを宣言している。 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;はエラーハンドラである。カーソルがこれ以上データを取得できなくなった（NOT FOUND）場合に、自動的にdone変数をTRUEに設定する役割を担う。これにより、後続のループ処理が適切に終了するようになる。 OPEN cur;でカーソルを開き、SELECT文の結果がカーソルにロードされる。 read_loop: LOOP ... END LOOP;は、カーソルからデータを一つずつ取り出して処理を繰り返すためのループ構造である。 ループの中で、FETCH cur INTO empName;は、カーソルから次の行のデータ（ここでは従業員名）を一つ取り出し、それをempName変数に代入する。 IF done THEN LEAVE read_loop; END IF;は、done変数がTRUE（つまりデータがもうない）になった場合に、ループを終了させる条件である。 SELECT empName AS High_Salary_Employee;は、取得した従業員名を表示する。 ループが完了したら、CLOSE cur;でカーソルを閉じ、使用していたリソースを解放する。 最後に、CALL GetHighSalaryEmployees();を実行することで、定義したプロシージャが呼び出され、カーソルによって処理された結果が表示される。

次に「トリガー」について説明する。 トリガーとは、データベースで特定のイベント（データの挿入、更新、削除など）が発生した際に、自動的に実行される特別な処理のことである。開発者が明示的に呼び出す必要はなく、データベースシステム自身がイベントを検知して起動する。これにより、データの整合性を保ったり、履歴を自動的に記録したりする際に非常に便利である。

トリガーの具体的な利用例として、学生が登録された際にその履歴を自動的に記録する「監査ログ」を考える。 まず、学生情報を格納するStudentsテーブルと、学生の登録履歴を記録するためのStudent_Auditテーブルを作成する。Student_Auditテーブルのregistered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMPは、行が挿入された現在の時刻を自動的に記録するように設定されている。

Copy
1CREATE TABLE Students (
2    student_id INT PRIMARY KEY,
3    name VARCHAR(50)
4);
5CREATE TABLE Student_Audit (
6    audit_id INT AUTO_INCREMENT PRIMARY KEY,
7    student_id INT,
8    name VARCHAR(50),
9    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
10);

次に、Studentsテーブルにデータが挿入された後に自動的に動作するトリガーを作成する。

Copy
1DELIMITER //
2CREATE TRIGGER after_student_insert
3AFTER INSERT ON Students
4FOR EACH ROW
5BEGIN
6    INSERT INTO Student_Audit (student_id, name)
7    VALUES (NEW.student_id, NEW.name);
8END //
9DELIMITER ;

DELIMITER //とDELIMITER ;の役割はカーソルの例と同様である。 CREATE TRIGGER after_student_insertは、「after_student_insert」という名前のトリガーを定義している。 AFTER INSERT ON Studentsは、このトリガーがStudentsテーブルに新しい行が挿入された「後で」実行されることを指定している。 FOR EACH ROWは、挿入される行ごとにトリガーの処理が実行されることを意味する。もし複数の行が一つのINSERT文で挿入されたとしても、各行に対して個別にトリガーが起動する。 トリガーの本体BEGIN ... ENDの中では、INSERT INTO Student_Audit (student_id, name) VALUES (NEW.student_id, NEW.name);というSQL文が記述されている。 ここで重要なのはNEW.student_idとNEW.nameという特別なキーワードである。これらは、「新しくStudentsテーブルに挿入された行」のstudent_idとnameの値を参照するために使用される。つまり、トリガーは、新しく登録された学生のIDと名前を自動的に取得し、それをStudent_Auditテーブルに挿入することで、登録履歴を記録するのである。

このトリガーが作成された後、実際にStudentsテーブルにデータを挿入してみる。

Copy
1INSERT INTO Students VALUES (1, 'John Doe');
2INSERT INTO Students VALUES (2, 'Jane Smith');

これらのINSERT文が実行されると、after_student_insertトリガーが自動的に起動し、John DoeとJane Smithの登録情報がStudent_Auditテーブルに記録される。 最後に、SELECT * FROM Student_Audit;を実行することで、自動的に記録された監査ログを確認できる。

まとめると、カーソルはデータベースのクエリ結果を一つ一つの行として取り出し、それぞれの行に対して特定の条件に基づいた詳細な処理を行いたい場合に有効である。一方、トリガーは、データベースの特定のイベント（データの追加、更新、削除など）が発生した際に、関連する処理を自動的に実行するための機能である。これにより、データの自動的な記録や整合性の維持が可能になる。 これら二つの機能は、データベースの操作をより柔軟にし、データ管理の自動化とデータの正確性を保つ上で、非常に強力な手段となる。システムエンジニアとして、これらの機能を理解し使いこなすことは、効率的で信頼性の高いシステムを構築するために不可欠である。