【ITニュース解説】Working with Cursors and Triggers in Oracle Live SQL

システムエンジニアを目指す上で、データベースの操作は避けて通れない重要なスキルである。通常のSQL文を使ってデータを取得したり変更したりするだけでなく、より複雑な処理や自動化を実現するためには、さらに踏み込んだ技術が必要となる。Oracleデータベースでその役割を果たすのが、「カーソル」と「トリガー」という機能だ。これらは、データベースのデータを柔軟に扱い、業務プロセスを効率化するために不可欠なツールである。

まず「カーソル」について説明する。通常、SQLのSELECT文を実行すると、条件に合致するすべてのデータがまとめて結果セットとして返される。しかし、時にはこの結果セットの各行に対して、個別に特別な処理を行いたい場合がある。例えば、ある特定の条件を満たす従業員全員の給与を一人ずつ確認し、それぞれに異なる計算を適用したり、特定のフォーマットで出力したりするといった状況だ。このような「結果セットを1行ずつ処理する」というニーズに応えるのがカーソルである。

カーソルを使用する基本的な流れは、いくつかのステップに分けられる。最初のステップは「カーソルの宣言」だ。これは、どのようなデータ（どのテーブルから、どのような条件で）を対象として1行ずつ処理したいかを定義する部分である。具体的には、SQLのSELECT文をカーソルに結びつける。記事の例では、給与が50,000を超える従業員の名前を抽出するSELECT文をemp_cursorという名前のカーソルに定義している。DECLAREキーワードで、従業員名を格納する変数emp_nameの型をテーブルの列に合わせて宣言し、CURSOR emp_cursor IS SELECT Emp_Name FROM Employee WHERE Salary > 50000;でカーソルを宣言している。

次に「カーソルのオープン」を行う。宣言したカーソルが実際にデータベースからデータを取得する準備をする段階だ。この時点で、カーソルは定義されたSQL文を実行し、結果セット全体を内部的に準備する。しかし、この時点ではまだ個々のデータが取り出されているわけではない。コードではOPEN emp_cursor;がこれにあたる。

オープン後、「ループ処理」を開始し、その中で「各行のフェッチ」を行う。フェッチとは、カーソルが準備した結果セットから1行ずつデータを取り出し、あらかじめ用意しておいた変数に格納する操作である。このフェッチを繰り返すことで、結果セットの全行を順次処理できる。ループ内でFETCH emp_cursor INTO emp_name;という記述があるが、これはカーソルから次の行の従業員名を取得し、emp_name変数に代入するという意味だ。

フェッチと同時に重要となるのが「ループの終了条件」である。結果セットの最後の行まで処理し終えたら、それ以上フェッチしてもデータは存在しないため、ループを終了する必要がある。Oracleでは、%NOTFOUNDという特別な属性を使って、カーソルがこれ以上データをフェッチできない状態になったことを検出できる。EXIT WHEN emp_cursor%NOTFOUND;という記述は、「カーソルに次のデータがない場合、ループを終了せよ」という意味になる。ループ内では、DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_name);という行で、取り出した従業員名を画面に出力している。これは、データベースのPL/SQL環境からメッセージを表示する機能である。

最後に「カーソルのクローズ」を行う。すべての行の処理が完了したら、カーソルを閉じて、割り当てられたリソース（メモリなど）を解放することが良い習慣とされている。コードのCLOSE emp_cursor;がこの役割を果たす。この一連のステップを踏むことで、SQLの集合的なデータ操作とは異なり、きめ細やかな行単位の処理が可能となるのだ。

次に「トリガー」について説明する。トリガーは、特定のデータベースイベントが発生したときに、自動的に実行されるPL/SQLのプログラムブロックである。例えば、テーブルに新しいデータが挿入されたとき、データが更新されたとき、あるいはデータが削除されたときなど、あらかじめ定義されたイベントがトリガー（引き金）となって、関連する処理が実行される。これは、ユーザーが意識することなく、データベース側で自動的に特定のタスクをこなさせたい場合に非常に有用な機能だ。

トリガーの典型的な利用例として、「監査ログ」の記録がある。これは、どのユーザーが、いつ、どのようなデータを、どのように変更したかという履歴を自動的に記録する仕組みである。記事の例では、Studentsテーブルに新しい学生情報が追加されるたびに、その情報をStudent_Auditという別の監査テーブルに自動的に記録するというトリガーを作成している。

トリガーを作成する前に、まず監査対象となるStudentsテーブルと、監査ログを保存するStudent_Auditテーブルを準備する。CREATE TABLE文でこれらのテーブルを作成し、Student_Auditテーブルの主キーを自動採番するためにCREATE SEQUENCEで連番を生成するシーケンスも作成している。

トリガー本体の作成はCREATE OR REPLACE TRIGGER文で行う。例のtrg_student_auditトリガーは、いくつかの重要な部分から構成されている。まず、AFTER INSERTという部分は、このトリガーが「挿入イベントの後」に実行されることを示している。つまり、Studentsテーブルに新しいデータが正常に挿入された直後にトリガーが動作する。次にON STUDENTSは、このトリガーがStudentsテーブルに対して設定されていることを示し、FOR EACH ROWは、挿入されたデータが複数行であっても、挿入された「各行ごと」にトリガーが実行されることを意味する。

トリガーの本体であるBEGIN ... END;ブロック内には、実行したいPL/SQLコードを記述する。ここではINSERT INTO STUDENT_AUDIT ...文を使って、監査テーブルにログを挿入している。この際、STUDENT_AUDIT_SEQ.NEXTVALは先ほど作成したシーケンスから新しい連番を取得してAudit_IDに割り当てている。SYSTIMESTAMPは現在のシステム日時を自動的に記録する。そして最も重要なのは:NEW.STUDENT_IDという記述だ。これは、現在挿入されたばかりのStudentsテーブルの行のStudent_ID列の値を取得するという意味である。:NEWは、トリガーが動作したイベントによって変更されたり新しく追加されたりするデータの「新しい値」を参照するための特別なプレースホルダで、:OLDは変更前の値を示す。このようにして、新しく追加された学生のIDを使って、どの学生が追加されたかを監査ログに記録している。

最後に、実際にINSERT INTO STUDENTS VALUES (1, 'Sowmya', 'Biology');を実行すると、このトリガーが自動的に動作し、SELECT * FROM STUDENT_AUDIT;で確認すると、学生の挿入ログが記録されていることがわかる。このようにトリガーは、データの整合性を保ったり、セキュリティ目的で変更履歴を記録したり、あるいは複雑な業務ロジックを自動化したりするために、データベースの裏側で非常に強力な役割を果たす。

まとめると、カーソルはデータベースのクエリ結果を1行ずつ、きめ細かく処理する必要がある場合に非常に便利である。一方、トリガーは特定のデータベースイベント（データの挿入、更新、削除など）が発生した際に、関連する処理を自動的に実行させることで、監査ログの記録やデータの整合性維持など、様々なタスクを自動化する強力な手段となる。システムエンジニアとしてデータベースを扱う上で、これらの機能を理解し適切に使いこなすことは、より堅牢で効率的なシステムを構築するために不可欠なスキルである。