【ITニュース解説】SQL Cursor and Trigger Implementation

システムエンジニアを目指す上で、データベースの操作は避けて通れない重要なスキルである。その中でも、より高度なデータ処理や自動化を実現するために知っておくべき概念として、SQLの「カーソル」と「トリガー」がある。これらはデータベースの力を最大限に引き出し、効率的で信頼性の高いシステムを構築するために不可欠な要素だ。

まず、カーソルについて解説する。通常、SQLのSELECT文は条件に合うデータをまとめて取得するが、時には取得した結果を1行ずつ順番に処理したい場面がある。例えば、ある条件を満たす従業員一人ひとりのデータを個別に確認し、特定の処理を施したい場合などだ。このような「行ごとの処理」を可能にするのがカーソルである。記事の例では、給与が50,000を超える従業員の名前と給与を一覧表示する際にカーソルが使われている。

カーソルを使う基本的な流れは、まずどのデータを対象にするかを定義し、次にその定義に基づいてデータ取得を開始し、1行ずつ取り出して処理し、最後にデータ取得を終了するというものだ。具体的には、「DECLARE CURSOR emp_cursor IS SELECT EmpName, Salary FROM Employee WHERE Salary > 50000;」という部分で、emp_cursorという名前のカーソルを定義している。これは、給与が50,000より大きい従業員の名前と給与を取得するSQLクエリを「このカーソルが扱うデータ」として指定していることを意味する。

次に、「emp_record emp_cursor%ROWTYPE;」という行がある。これは、カーソルから1行データを取り出す際に、そのデータを一時的に保持するための「emp_record」という変数（レコード）を宣言している。%ROWTYPEは、指定したカーソルが返す行の構造（今回の場合はEmpNameとSalaryの2つの列）とまったく同じ構造を持つ変数を簡単に作成するための便利な記述だ。

カーソルを実際に使うには、まず「OPEN emp_cursor;」でカーソルを開く必要がある。これは、定義したSQLクエリを実行し、結果セット（処理対象となるデータ群）を準備する段階だと考えると良い。次に、「LOOP ... END LOOP;」という繰り返し処理の中で、データがなくなるまで1行ずつ取り出す作業が行われる。「FETCH emp_cursor INTO emp_record;」という命令は、開いたカーソルから次の1行のデータをemp_recordという変数に取り込むことを意味する。このとき、データがもう存在しない場合には、カーソル内部の%NOTFOUNDというフラグが真になる。そのため、「EXIT WHEN emp_cursor%NOTFOUND;」という行で、データがすべて処理され尽くしたらループを抜けるように指示している。

取り出したデータは、「DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_record.EmpName || ', Salary: ' || emp_record.Salary);」という行で画面に出力されている。emp_record.EmpNameやemp_record.Salaryとすることで、emp_record変数に格納された各列の値にアクセスできる。すべての処理が終わったら、「CLOSE emp_cursor;」でカーソルを閉じる。これは、カーソルが占有していたリソースを解放し、後片付けを行う重要なステップだ。カーソルは、特定の条件に基づいて複雑なビジネスロジックを各行に適用したい場合や、取得したデータを他のシステムに連携させるために加工する場合などに非常に有効な手段となる。

次に、トリガーについて説明する。トリガーは、データベースで特定のイベント（例えば、データの挿入、更新、削除など）が発生したときに、自動的に実行される特別なSQLコードの塊である。システムエンジニアは、トリガーを使ってデータの整合性を自動的に保ったり、変更履歴を記録したり、関連するテーブルのデータを自動的に更新したりといった様々なタスクを自動化できる。

記事の例では、「AFTER INSERT Trigger — Student Registration Audit」として、学生が登録された後に自動的に監査ログを記録するトリガーが紹介されている。これは、Studentsテーブルに新しい学生のデータが挿入されるたびに、その学生IDと名前をStudent_Auditという別の監査用テーブルに記録するという機能だ。

トリガーを作成する際には、「CREATE OR REPLACE TRIGGER trg_student_insert」という部分で、trg_student_insertという名前のトリガーを定義している。OR REPLACEを付けることで、もし同じ名前のトリガーが既に存在していれば、それを新しい定義で上書きできるため、開発中にトリガーの修正を頻繁に行う場合に便利だ。

「AFTER INSERT ON Students」は、このトリガーがどのテーブルのどのイベントの後に実行されるかを指定している。ここでは、「Studentsテーブルに対してINSERT操作が行われた後」に実行されることを意味している。BEFOREを指定すれば、INSERT操作の前に実行することも可能だ。

「FOR EACH ROW」という記述は、挿入された行が複数ある場合でも、それぞれの行に対して個別にトリガーの処理を実行するように指定している。例えば、一度に100件の学生データをINSERTした場合、このトリガーは100回実行されることになる。

トリガーが実際に実行する処理は、「BEGIN ... END;」ブロックの中に記述される。今回の例では、「INSERT INTO Student_Audit (StudentID, StudentName) VALUES (:NEW.StudentID, :NEW.StudentName);」というSQL文がある。ここで重要なのは「:NEW」というキーワードだ。トリガーがINSERTイベントの後に実行される場合、:NEWは、新しく挿入された行のデータ全体を参照するために使われる。つまり、:NEW.StudentIDは新しく挿入された行のStudentID列の値を、:NEW.StudentNameはStudentName列の値を指している。これにより、挿入された学生の情報をそのままStudent_Auditテーブルに記録できるわけだ。

このトリガーをテストするために、「INSERT INTO Students (StudentID, StudentName) VALUES ('S01', 'Arjun');」というSQL文を実行すると、Studentsテーブルに新しいレコードが追加されると同時に、定義したtrg_student_insertトリガーが自動的に発動し、Student_Auditテーブルにも同じ学生の情報がログとして記録される。このように、トリガーは開発者が明示的にログ記録のコードを書かなくても、データベースが自動的に処理を行ってくれるため、アプリケーションコードをシンプルに保ちながら、堅牢なデータ管理を実現できる。

カーソルとトリガーは、それぞれ異なる目的を持つが、どちらもデータベースの操作において非常に強力な機能である。カーソルは複雑な行ごとのデータ処理に柔軟性をもたらし、トリガーは特定のデータベースイベントに対する自動応答を可能にし、データの整合性や監査ログの記録などを効率化する。これらの機能を理解し、適切に使いこなすことは、システムエンジニアとしてデータベースを扱う上で、プログラムの信頼性、効率性、そして動的なデータ管理能力を向上させるために不可欠だ。