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

データベースを操作する際、特定のデータを効率的に処理したり、データの変更を自動的に記録したりする方法は、システム開発において非常に重要だ。Oracleデータベースでは、このようなニーズに応える強力な機能として「カーソル」と「トリガー」が提供されている。これらはシステムエンジニアとしてデータベースを扱う上で避けては通れない基本的な概念であり、その使い方を理解することは、堅牢で効率的なシステムを構築する第一歩となる。

まず「カーソル」について説明する。通常のデータベースクエリ、例えばSELECT * FROM Employeeのような文は、条件に合致するすべてのデータを一度に結果セットとして返却する。しかし、時には取得した結果の各行を一つずつ順番に処理したい場合がある。例えば、ある条件を満たす従業員一人ひとりの情報を取り出して、それぞれの従業員に対して個別の計算を実行したり、特定の処理を行ったりする場合などだ。このような「行ごとの処理」が必要なときにカーソルが使われる。カーソルは、クエリの結果を一時的に保持し、その中の各行にアクセスするためのポインタのようなものだと考えればよい。

具体的な例として、従業員テーブル（Employee）の中から、給与が50,000を超える従業員の名前だけを抽出して表示するケースを考えてみよう。この処理は通常、PL/SQLというOracle独自のプログラミング言語ブロックの中で行われる。まず、DECLAREセクションでカーソルを定義する。CURSOR emp_cursor IS SELECT emp_name FROM Employee WHERE salary > 50000;という記述は、「emp_cursor」という名前のカーソルを宣言し、それが「給与が50,000より大きい従業員の名前を選択する」というクエリの結果を扱うことを示している。また、v_emp_name Employee.emp_name%TYPE;という行は、カーソルから取り出した従業員名を一時的に格納するための変数v_emp_nameを宣言している。%TYPEは、元のテーブルの列のデータ型を自動的に引き継ぐため、型の間違いを防ぐ便利な機能だ。

次に、BEGINセクションで実際の処理が始まる。まずOPEN emp_cursor;という文で、定義したカーソルを「開く」。これは、指定されたクエリが実行され、その結果がカーソルにロードされる準備が整った状態を意味する。その後、LOOP文を使って繰り返し処理を開始する。ループの中でFETCH emp_cursor INTO v_emp_name;という文が実行されるたびに、カーソルから次の行が1つ取り出され、その中の従業員名がv_emp_name変数に代入される。もし取り出すべき行がもう存在しない場合、つまりすべての行を処理し終えた場合には、emp_cursor%NOTFOUNDという特別な状態が真（TRUE）になる。EXIT WHEN emp_cursor%NOTFOUND;という文は、この状態が真になったときにループを終了させる役割を果たす。ループの中でDBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);という文が実行され、取り出した従業員名が画面に表示される。すべての行の処理が完了しループを抜けた後、最後にCLOSE emp_cursor;という文でカーソルを「閉じる」。これはカーソルが使用していたリソースを解放し、後片付けを行う重要なステップだ。このように、カーソルを使うことで、大量のデータの中から特定の条件に合うデータを1行ずつ丁寧に取り出し、複雑な処理を適用することが可能になる。

次に「トリガー」について解説する。トリガーは、特定のデータベースイベントが発生したときに自動的に実行されるプログラムのことだ。このイベントとは、例えばテーブルにデータが追加されたり（INSERT）、更新されたり（UPDATE）、削除されたり（DELETE）することなどを指す。トリガーは、データベースの整合性を保ったり、データの変更履歴を自動的に記録したりする際に非常に強力なツールとなる。手動で処理を記述する手間を省き、エラーの可能性を減らすことができるため、データベースアプリケーションの信頼性向上に貢献する。

具体的な例として、学生テーブル（Students）に新しい学生が追加されるたびに、その情報を自動的に監査ログテーブル（Student_Audit）に記録するケースを考えてみよう。まず、StudentsテーブルとStudent_Auditテーブルを作成する。Student_Auditテーブルには、監査ID、学生ID、学生名、そしてアクションが記録された日付が格納されるように設計されている。特にaudit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEYは、新しい行が挿入されるたびに自動的に一意の番号が生成される仕組みで、主キーとして機能する。action_date DATE DEFAULT SYSDATEは、特に指定がなければシステム日付が自動的に入ることを意味する。

そして、CREATE OR REPLACE TRIGGER trg_after_student_insertという文でトリガーを定義する。AFTER INSERT ON Studentsという部分は、このトリガーが「Studentsテーブルにデータが挿入された後に」実行されることを指定している。FOR EACH ROWというキーワードは、挿入された行ごとにトリガーが起動することを意味し、これがない場合は文レベル（複数の行が一度に挿入されてもトリガーは一度しか起動しない）で動作する。BEGINとENDの間には、トリガーが実行される実際の処理が記述される。この例では、INSERT INTO Student_Audit (student_id, student_name) VALUES (:NEW.student_id, :NEW.student_name);という文が記述されている。ここで注目すべきは:NEWというキーワードだ。トリガー内では、:NEWは挿入（または更新）されようとしている新しい行のデータを参照するために使われる。つまり、:NEW.student_idは新しく挿入された学生のIDを、:NEW.student_nameは新しく挿入された学生の名前を指している。このトリガーが有効になっている状態で、INSERT INTO Students (student_id, student_name) VALUES (1, 'Alice');のような文を実行すると、StudentsテーブルにAliceのデータが挿入されると同時に、このトリガーが自動的に起動し、Aliceの学生IDと名前がStudent_Auditテーブルにも記録される。Bobのデータが挿入された場合も同様に、自動的に監査ログが追加される。このようにトリガーを利用することで、開発者は手動で監査ログを記録するコードを書く必要がなくなり、データベースレベルでデータの整合性と監査要件を自動的に満たすことができるようになる。

カーソルは、データベースから取得した結果の各行に対して、複雑な計算や特定の条件に基づいた処理を詳細に行いたい場合に非常に有効な手段だ。一方、トリガーは、データの変更イベントを検知して自動的に決められた処理を実行することで、データの一貫性や履歴管理、ビジネスロジックの自動化を実現するための強力なメカニズムを提供する。どちらの機能も、システム開発においてデータベースをより柔軟かつ効率的に、そして堅牢に運用するために不可欠な要素と言える。