【Oracle Database】PL/SQLの基本：手続き型でロジックを書く

Oracle Database
2023.12.22

こんにちは、かつコーチです。

Oracle Database編の3本目です。

今回は、Oracle Database最大の特徴といっても過言ではないPL/SQL（Procedural Language extensions to SQL）を解説します。

MySQLのストアドプロシージャやPostgreSQLのPL/pgSQLに触れたことがある方であれば、考え方自体は近いので理解しやすいはずです。

SQL文法の基礎はSQL基礎編で解説済みなので、ここではPL/SQLならではの書き方に絞って説明します。

PL/SQLとは？

手続き型言語としての位置づけ

通常のSQLは「何を取得したいか」を宣言的に書く言語ですが、PL/SQLは変数宣言・条件分岐・繰り返し処理といった手続き型（処理の手順を順番に書いていくスタイル）の要素をSQLに組み込んだ言語です。

Oracle Database内部で動作するため、アプリケーション側とデータベースの間で何度も通信することなく、一連の処理をデータベース内で完結させられるのが大きなメリットです。

なぜPL/SQLが必要なのか

例えば「複数のテーブルを条件分岐しながら更新する」ような複雑な処理を、アプリケーション側（PHPやJavaなど）だけで書こうとすると、SQLの発行回数が増えてパフォーマンスが低下しがちです。

PL/SQLでストアドプロシージャ（データベースに保存しておける処理のかたまり）として実装すれば、複雑なロジックをデータベース側で1回の呼び出しにまとめられます。

金融システムなど、大量データを高速かつ安全に処理する必要がある現場でOracle Databaseが選ばれる理由の1つが、このPL/SQLの存在です。

基本の書き方

無名ブロックの構造

PL/SQLの最小単位が無名ブロックです。

-- 無名ブロック：DECLARE（宣言部）、BEGIN〜END（実行部）で構成する
DECLARE
    v_employee_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_employee_count FROM employees;
    DBMS_OUTPUT.PUT_LINE('従業員数: ' || v_employee_count);
END;
/

DECLAREで変数を宣言し、BEGIN〜ENDの間に実際の処理を書きます。

最後の/（スラッシュ）は、SQL*PlusやSQL Developerに「ここまでがひとまとまりのブロックです」と伝えるための区切り記号で、忘れると実行されないので注意が必要です。

DBMS_OUTPUT.PUT_LINEは、MySQLでいうSELECT結果の確認に近い、コンソールへの出力用パッケージです。

プロシージャ・ファンクションの作成

無名ブロックを、名前を付けて保存できるようにしたものがプロシージャとファンクションです。

-- プロシージャ：戻り値を持たない処理のかたまり
CREATE OR REPLACE PROCEDURE raise_salary(
    p_employee_id IN NUMBER,
    p_rate IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_rate)
    WHERE employee_id = p_employee_id;

    COMMIT;
END;
/

-- ファンクション：戻り値を持つ処理のかたまり
CREATE OR REPLACE FUNCTION get_employee_name(
    p_employee_id IN NUMBER
) RETURN VARCHAR2 AS
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM employees
    WHERE employee_id = p_employee_id;

    RETURN v_name;
END;
/

作成したプロシージャはEXEC raise_salary(100, 0.1);のように呼び出し、ファンクションはSELECT get_employee_name(100) FROM DUAL;のようにSQL文の中で値として利用できます。

カーソル・例外処理

複数行の結果を1行ずつ処理したい場合にはカーソルを使います。

-- カーソルで複数行を1行ずつ処理する
DECLARE
    CURSOR c_employees IS
        SELECT employee_id, salary FROM employees WHERE department_id = 10;
BEGIN
    FOR emp_rec IN c_employees LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.salary);
    END LOOP;
END;
/

FOR ... IN カーソル LOOPという書き方をすると、カーソルのオープン・フェッチ・クローズを自動でやってくれるため、実務ではこの書き方が最もよく使われます。

エラー処理には例外処理（EXCEPTION句）を使います。

-- 例外処理：存在しないデータを検索したときの制御
DECLARE
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 99999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('該当する従業員が見つかりません');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('予期しないエラーが発生しました: ' || SQLERRM);
END;
/

よくあるつまずきポイント・エラー対処

例外処理漏れによるプロシージャの異常終了

筆者が実際のプロジェクトで経験したつまずきが、例外処理を省略したことによるバッチ処理の途中停止です。

❌ Before：例外処理がないため、1件でもエラーが起きると全体が止まる
CREATE OR REPLACE PROCEDURE update_all_salaries AS
BEGIN
    FOR emp_rec IN (SELECT employee_id FROM employees) LOOP
        UPDATE employees
        SET salary = salary * 1.05
        WHERE employee_id = emp_rec.employee_id;
    END LOOP;
    COMMIT;
END;
/

このプロシージャを本番データで実行したところ、一部のデータで想定外の制約違反が発生し、ORA-02290（チェック制約違反）で処理全体が停止してしまいました。

数百件処理したうちの1件がエラーになっただけで、それ以降の全件が未処理のまま止まってしまったのです。

✅ After：ループ内で例外処理をして、1件のエラーで全体を止めない
CREATE OR REPLACE PROCEDURE update_all_salaries AS
BEGIN
    FOR emp_rec IN (SELECT employee_id FROM employees) LOOP
        BEGIN
            UPDATE employees
            SET salary = salary * 1.05
            WHERE employee_id = emp_rec.employee_id;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('エラー: employee_id=' || emp_rec.employee_id || ' - ' || SQLERRM);
        END;
    END LOOP;
    COMMIT;
END;
/

ループの内側にも小さなBEGIN〜EXCEPTION〜ENDブロックを入れることで、1件のエラーをその場で処理し、他のデータの処理を継続できるようになります。

バッチ処理を書くときは「全体を止めたくない処理には、ループの内側にも例外処理を入れる」というのが実務上のセオリーです。

応用・一歩先の使い方

パッケージ化による整理

関連するプロシージャ・ファンクションが増えてきたら、パッケージ（複数のプロシージャ・ファンクションをまとめる仕組み）を使って整理するのが実務での定番です。

パッケージ仕様部（外部に公開する定義）とパッケージ本体（実際の処理）を分けて管理できるため、大規模なシステムでもコードの見通しを保ちやすくなります。

まずは今回解説した無名ブロック・プロシージャ・ファンクションの書き方に慣れてから、パッケージへとステップアップしていくとよいでしょう。

まとめ

この記事のポイント

  • PL/SQLはOracle独自の手続き型拡張で、複雑なロジックをデータベース側で完結できる
  • 無名ブロックはDECLARE〜BEGIN〜END、末尾の/を忘れないことが基本
  • プロシージャ（戻り値なし）とファンクション（戻り値あり）を使い分ける
  • カーソルのFOR ... IN LOOPと、例外処理のEXCEPTION句はセットで覚える
  • バッチ処理では、ループ内側にも例外処理を入れて1件のエラーで全体を止めない設計にする

次に読むべき記事

  • SQL*Plus・SQL Developerの基本操作
  • シーケンスとROWNUM・ROWID、MySQL/PostgreSQLとの違い

タグ: Oracle, 中級者向け, 基本文法

タイトルとURLをコピーしました