plsql

Oracle PL/SQL の基礎知識

完全なリファレンス：パイプライン関数、パッケージ、コレクション、BULK COLLECT/FORALL、複合トリガー、高度なカーソルについては、advanced.md を参照してください。

詳細な知識：包括的なドキュメントについては、technology: oracle を指定して mcp__documentation__fetch_docs を使用してください。

基本構造

DECLARE
    -- Variable declarations
    v_count NUMBER := 0;
BEGIN
    -- Executable statements
    DBMS_OUTPUT.PUT_LINE('Hello World');
EXCEPTION
    WHEN OTHERS THEN
        -- Exception handling
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

プロシージャ

基本的なプロシージャ

CREATE OR REPLACE PROCEDURE update_salary(
    p_employee_id IN NUMBER,
    p_percentage  IN NUMBER
)
IS
    v_current_salary NUMBER;
BEGIN
    SELECT salary INTO v_current_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    UPDATE employees
    SET salary = salary * (1 + p_percentage / 100)
    WHERE employee_id = p_employee_id;

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found: ' || p_employee_id);
END update_salary;
/

-- Execute
EXEC update_salary(100, 10);
-- or
BEGIN
    update_salary(100, 10);
END;
/

OUT パラメータを持つプロシージャ

CREATE OR REPLACE PROCEDURE get_employee_info(
    p_employee_id IN  NUMBER,
    p_name        OUT VARCHAR2,
    p_salary      OUT NUMBER,
    p_dept_name   OUT VARCHAR2
)
IS
BEGIN
    SELECT e.first_name || ' ' || e.last_name,
           e.salary,
           d.department_name
    INTO p_name, p_salary, p_dept_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE e.employee_id = p_employee_id;
END;
/

-- Call with OUT parameters
DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
    v_dept VARCHAR2(100);
BEGIN
    get_employee_info(100, v_name, v_salary, v_dept);
    DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
END;
/

関数

スカラー関数

CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary IN NUMBER,
    p_years  IN NUMBER
)
RETURN NUMBER
DETERMINISTIC  -- Same inputs always return same output
IS
    v_bonus NUMBER;
BEGIN
    IF p_years >= 10 THEN
        v_bonus := p_salary * 0.15;
    ELSIF p_years >= 5 THEN
        v_bonus := p_salary * 0.10;
    ELSE
        v_bonus := p_salary * 0.05;
    END IF;

    RETURN v_bonus;
END;
/

-- Usage in SQL
SELECT employee_id, salary, calculate_bonus(salary, years_of_service) as bonus
FROM employees;

カーソル

暗黙カーソル

BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No rows found');
    END IF;
END;
/

明示カーソル

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;

    v_emp emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': ' || v_emp.salary);
    END LOOP;
    CLOSE emp_cursor;
END;
/

カーソル FOR ループ（推奨）

BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name, salary
                    FROM employees WHERE department_id = 10)
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
    END LOOP;
END;
/

コレクション

連想配列（INDEX BY）

DECLARE
    TYPE salary_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    TYPE name_tab IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(20);

    salaries salary_tab;
    names    name_tab;
BEGIN
    salaries(1) := 50000;
    salaries(2) := 60000;

    names('EMP001') := 'John Doe';
    names('EMP002') := 'Jane Smith';

    DBMS_OUTPUT.PUT_LINE(salaries(1));
    DBMS_OUTPUT.PUT_LINE(names('EMP001'));
END;
/

例外処理

事前定義された例外

例外	説明
NO_DATA_FOUND	SELECT INTO が行を返さなかった
TOO_MANY_ROWS	SELECT INTO が複数の行を返した
ZERO_DIVIDE	ゼロ除算
VALUE_ERROR	数値または値のエラー
INVALID_CURSOR	無効なカーソル操作
DUP_VAL_ON_INDEX	一意インデックス上の値が重複している

例外処理

DECLARE
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
        RAISE;
END;
/

RAISE_APPLICATION_ERROR

BEGIN
    IF some_condition THEN
        RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
    END IF;
END;
/

トリガー

行レベルのトリガー

CREATE OR REPLACE TRIGGER trg_emp_salary_check
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;

    IF :NEW.salary > 1000000 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Salary exceeds maximum');
    END IF;
END;
/

ベストプラクティス

推奨事項

• 関連するコードを整理するには、パッケージを使用してください
• 大規模なデータセットには、BULK COLLECT と FORALL を使用してください
• カーソル FOR ループを使用してください（自動で開閉されます）
• パッケージレベルで例外を定義してください
• 型の安全性のために、%TYPE と %ROWTYPE を使用してください
• SQL インジェクションを防ぐために、バインド変数を使用してください

避けるべきこと

• トリガー内で暗黙のコミットを使用する
• 例外を無視する
• NO_DATA_FOUND の処理をせずに SELECT INTO を使用する
• 過剰な数のトリガーを作成する（パフォーマンスに影響します）

このスキルを使用すべきでない場合

• 基本的な Oracle SQL - クエリ、データ型、パーティショニングには oracle スキルを使用してください
• PL/pgSQL（PostgreSQL） - PostgreSQL のプロシージャには plpgsql スキルを使用してください
• T-SQL（SQL Server） - SQL Server のプロシージャには tsql スキルを使用してください
• 基本的な SQL - ANSI SQL の基礎には sql-fundamentals を使用してください

アンチパターン

アンチパターン	問題	解決策
BULK COLLECT を使用しない	1 行ずつ処理する	大規模なデータセットには BULK COLLECT を使用する
例外処理のない SELECT INTO	実行時エラー	NO_DATA_FOUND を処理する
パッケージを使用しない	コードが整理されない	関連するコードをパッケージにまとめる
過剰な数のトリガー	パフォーマンスの問題	トリガーのロジックを最小限にする
RAISE を伴わない WHEN OTHERS	エラーが表面化しない	例外を再送出するか、ログに記録する
大規模な結果セットでの暗黙カーソル	メモリの問題	LIMIT を指定した明示カーソルを使用する

迅速なトラブルシューティング

問題	診断	修正方法
NO_DATA_FOUND	SELECT INTO が行を返さない	例外ハンドラーを追加する
TOO_MANY_ROWS	SELECT INTO が複数の行を返す	WHERE を追加するか、カーソルを使用する
ORA-06502 数値エラー	型変換の失敗	データ型を確認し、TO_NUMBER を使用する
プロシージャが遅い	DBMS_PROFILER	BULK 操作を使用する
パッケージ状態が失われる	セッションのリセット	PRAGMA SERIALLY_REUSABLE を使用するか、再初期化する

リファレンスドキュメント

• プロシージャ
• 関数
• トリガー
• パッケージ
• カーソル