PL/SQLを基本からまとめてみた

Last updated at 2024-07-20Posted at 2024-06-30

第1章: はじめに

PL/SQLとは

PL/SQL (Procedural Language/Structured Query Language) は、Oracle Databaseのためのプロシージャル拡張言語です。SQLの強力な機能に加え、プロシージャルな制御構造を提供します。これにより、データベース操作を効率的に行うためのプログラムを作成できます。

PL/SQLの歴史

PL/SQLはOracle社によって1980年代後半に導入されました。最初に商用リリースされたのはOracle 6.0で、その後のバージョンアップにより機能が強化され続けています。

PL/SQLの利点

高パフォーマンス: PL/SQLはSQLとネイティブに統合されており、効率的なデータベース操作が可能です。
高い生産性: 豊富な機能セットにより、複雑なビジネスロジックを簡潔に記述できます。
セキュリティ: データベース内部で実行されるため、外部からのアクセスや攻撃を防ぎやすくなります。

PL/SQLの用途

ストアドプロシージャやファンクションの作成
- 業務ロジックをデータベース内にカプセル化し、再利用可能なコードを作成します。
- 例: 請求書の生成、給与計算、在庫管理のロジックなど。
トリガーの定義
- データベースイベント（INSERT、UPDATE、DELETE）に対する自動的な応答を設定します。
- 例: データの監査ログ、データの整合性チェック、自動通知の送信。
パッケージの作成
- 関連するプロシージャやファンクションをグループ化し、モジュール化されたコードを提供します。
- 例: 従業員管理パッケージ、在庫管理パッケージ、財務計算パッケージ。
データベースジョブのスケジューリング
- 定期的なタスクを自動化し、メンテナンスやレポート生成などの反復作業を効率化します。
- 例: 夜間のデータバックアップ、月次レポートの生成、定期的なデータクリーニング。

第2章: PL/SQLの基本概念

PL/SQLブロックの構造

PL/SQLプログラムはブロック単位で構成されます。基本的なPL/SQLブロックは以下の3つの部分からなります：

宣言部（任意）
変数や定数を宣言します。

実行部（必須）
実際の処理を記述します。

例外処理部（任意）
エラー処理を行います。

DECLARE
    -- 宣言部: 変数や定数を宣言します
    v_number NUMBER := 10;
    v_divisor NUMBER := 0;
    v_result NUMBER;
BEGIN
    -- 実行部: 実際の処理を記述します
    v_result := v_number / v_divisor; -- 0による除算を試みる
    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
    -- 例外処理部: エラー処理を行います
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('0による除算はできません');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

匿名ブロックと命名ブロック

匿名ブロック

匿名ブロックは、一時的な処理を行うためのブロックです。データベースに保存されず、実行時にのみ使用されます。匿名ブロックは特定の名前を持たず、即時実行されるため、主にデバッグや一時的なタスクに使用されます。

例: 簡単な匿名ブロック

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;

命名ブロック

命名ブロックは、ストアドプロシージャやファンクションとしてデータベースに保存されるブロックです。命名ブロックは名前を持ち、後で呼び出すことができます。これにより、再利用可能なコードを作成し、メンテナンスが容易になります。

例: ストアドプロシージャとしての命名ブロック

CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_user;

呼び出し例:

BEGIN
    greet_user('Alice');
END;

コメントの書き方

シングルラインコメント: -- ここにコメント
マルチラインコメント: /* ここにコメント */

ベストプラクティス

変数名や定数名は意味のある名前を付ける。
適切な初期値を設定する。(初期値を指定することは必須ではありません。ただし、初期化されていない変数を使用すると、NULL値が代入されます。)
使用しない変数は宣言しない。

第3章: PL/SQLのデータ型

スカラー型

スカラー型は、単一の値を保持するための基本的なデータ型です。

NUMBER: 数値型。整数および実数を格納できます。
- 例: v_number NUMBER := 100;
VARCHAR2: 可変長文字列型。最大4000バイトまでの文字列を格納できます。
- 例: v_text VARCHAR2(50) := 'Hello, World!';
CHAR: 固定長文字列型。定義された長さの文字列を格納します。
- 例: v_char CHAR(10) := 'A';
DATE: 日付型。日付と時刻を格納できます。
- 例: v_date DATE := SYSDATE;
BOOLEAN: 真偽値型。TRUE、FALSE、NULLの3つの値を持ちます。
- 例: v_boolean BOOLEAN := TRUE;

複合型

複合型は、複数の値を一つにまとめたデータ型です。

RECORD: 複数のフィールドを持つレコード型。フィールドには異なるデータ型を持たせることができます。

DECLARE
    TYPE emp_record IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(50),
        emp_salary NUMBER
    );
    v_emp emp_record;
BEGIN
    v_emp.emp_id := 1001;
    v_emp.emp_name := 'John Doe';
    v_emp.emp_salary := 50000;
END;

TABLE: 配列型。スカラー型または複合型のコレクションを格納します。

DECLARE
    TYPE num_table IS TABLE OF NUMBER;
    v_numbers num_table;
BEGIN
    v_numbers := num_table(1, 2, 3, 4, 5);
END;

参照型

参照型は、他のデータ型やオブジェクトを参照するためのデータ型です。

詳細は第5章へ

LOB型(使っている場面を見たことない)

LOB型（Large Object）は、大きなデータオブジェクトを格納するためのデータ型です。

BLOB: バイナリラージオブジェクト。バイナリデータを格納します。
- 例: v_blob BLOB;
CLOB: 文字ラージオブジェクト。大きな文字データを格納します。
- 例: v_clob CLOB;
NCLOB: 国家文字ラージオブジェクト。国家文字セットを使用する大きな文字データを格納します。
- 例: v_nclob NCLOB;
BFILE: 外部ファイルの参照。データベース外部のファイルを参照します。
- 例: v_bfile BFILE := BFILENAME('DIR', 'example.pdf');

その他のデータ型

RAW: バイナリデータを格納する可変長データ型。最大2000バイトまで格納可能。
- 例: v_raw RAW(2000);
ROWID: データベース内の行のアドレスを格納します。
- 例: v_rowid ROWID;
UROWID: 汎用行ID。拡張されたROWIDで、外部データソースの行IDも格納可能。
- 例: v_urowid UROWID;

第4章: 制御構造

条件分岐、ループ、CASE文

第5章: カーソル

カーソル

第6章: 例外処理

例外処理

第7章: ストアドプロシージャとファンクション

ストアドプロシージャとファンクション

第8章: パッケージ

パッケージ

第9章: トリガー

トリガー

第10章: トランザクション管理

トランザクション管理

第11章: VIEWの基礎

VIEWの基礎

第12章: 応用トピック

この章では、PL/SQLのいくつかの高度な機能とテクニックについて説明します。

動的SQL

動的SQLは、実行時にSQL文を構築し実行する技術です。PL/SQLでは、EXECUTE IMMEDIATE文を使用して動的SQLを実行します。動的SQLを使用することで、柔軟なクエリの構築や実行が可能になります。

例: 動的SQLの使用

DECLARE
    v_table_name VARCHAR2(50) := 'employees';
    v_column_name VARCHAR2(50) := 'salary';
    v_sql VARCHAR2(200); -- 動的SQL文を格納する変数
    v_avg_salary NUMBER; -- 結果を格納する変数
BEGIN
    -- 動的SQL文を構築
    v_sql := 'SELECT AVG(' || v_column_name || ') INTO :1 FROM ' || v_table_name;
    
    -- EXECUTE IMMEDIATE文を使用して動的SQLを実行し、結果をv_avg_salaryに格納
    EXECUTE IMMEDIATE v_sql INTO v_avg_salary;
    
    -- 結果を出力
    DBMS_OUTPUT.PUT_LINE('Average Salary: ' || v_avg_salary);
END;

BULK COLLECTとFORALL

大量のデータを効率的に処理するために、BULK COLLECTとFORALLを使用します。これらの機能を使用することで、ループのオーバーヘッドを削減し、パフォーマンスを向上させることができます。

例: BULK COLLECTの使用

DECLARE
    -- employeesテーブルの行タイプを持つコレクションタイプを宣言
    TYPE emp_tab IS TABLE OF employees%ROWTYPE;
    v_employees emp_tab; -- コレクション変数を宣言
BEGIN
    -- BULK COLLECTを使用して、一度に複数行をフェッチ
    SELECT * BULK COLLECT INTO v_employees FROM employees WHERE department_id = 10;

    -- コレクション内の各行に対してループを実行
    FOR i IN 1..v_employees.COUNT LOOP
        -- 現在の従業員のIDと名前を出力
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employees(i).employee_id || 
                             ', Name: ' || v_employees(i).first_name || ' ' || v_employees(i).last_name);
    END LOOP;
END;

BULK COLLECTのポイント

パフォーマンスの向上: BULK COLLECTを使用することで、複数行を一度にフェッチするため、パフォーマンスが向上します。これは、各行を個別にフェッチするよりも効率的です。
メモリ使用量: BULK COLLECTは、一度に多くの行をメモリにロードするため、大量のデータを扱う場合にはメモリ使用量に注意が必要です。
可読性: BULK COLLECTとループを組み合わせることで、コードの可読性が向上し、データの処理が直感的になります。

例: FORALLの使用

DECLARE
    -- employeesテーブルのemployee_idカラムのデータ型を持つコレクションタイプを宣言
    TYPE emp_id_table_type IS TABLE OF employees.employee_id%TYPE;
    
    -- コレクション変数を宣言し、特定の従業員IDを初期化
    v_emp_ids emp_id_table_type := emp_id_table_type(101, 102, 103);
BEGIN
    -- FORALL文を使用して、一度に複数行を削除
    FORALL i IN v_emp_ids.FIRST .. v_emp_ids.LAST
        DELETE FROM employees WHERE employee_id = v_emp_ids(i);
END;

FORALLのポイント

パフォーマンスの向上: FORALL文を使用することで、ループのオーバーヘッドを削減し、一度に複数のDML操作（INSERT、UPDATE、DELETE）を実行するため、パフォーマンスが向上します。
可読性: FORALL文を使用することで、複数行のDML操作をシンプルかつ効率的に記述でき、コードの可読性が向上します。
バルクバインド: FORALL文はバルクバインドと呼ばれる技術を使用して、複数のDML操作を一度に実行し、データベースへのラウンドトリップを減らします。

パフォーマンスチューニング

PL/SQLプログラムのパフォーマンスを向上させるためには、以下のポイントに注意します。

テクニック:

適切なインデックスの使用: インデックスを適切に使用することで、クエリのパフォーマンスを向上させます。
バインド変数の使用: 動的SQLでバインド変数を使用することで、パースのオーバーヘッドを削減します。

SQL文をデータベースに送信する際、データベースはそのSQL文を解析して実行計画を作成します。バインド変数を使用すると、SQL文の構造が一定に保たれ、異なる値が入っても再解析を回避できます。これにより、同じSQL文を複数回実行する場合の処理が効率化されます。

PL/SQLコンパイラの最適化: PL/SQLコンパイラの最適化オプションを使用して、パフォーマンスを向上させます。

例: バインド変数の使用

DECLARE
    v_sql VARCHAR2(200);
    v_avg_salary NUMBER;
BEGIN
    v_sql := 'SELECT AVG(salary) FROM employees WHERE department_id = :dept_id';
    EXECUTE IMMEDIATE v_sql INTO v_avg_salary USING 10; -- バインド変数を使用
    DBMS_OUTPUT.PUT_LINE('Average Salary: ' || v_avg_salary);
END;

プロファイリングとデバッグ

PL/SQLプログラムのパフォーマンスをプロファイリングし、デバッグするためのツールと技法があります。

DBMS_PROFILER: PL/SQLコードのプロファイリングを行います。
DBMS_TRACE: PL/SQLコードのトレースを行います。

DBMS_PROFILER

DBMS_PROFILERは、PL/SQLコードの各部分がどれだけの時間を消費しているかを分析するためのツールです。これを使用することで、パフォーマンスのボトルネックを特定し、最適化のポイントを見つけることができます。

DBMS_PROFILERの手順

プロファイラの初期化: プロファイリングを行う前に、プロファイラを初期化します。
プロファイリングの開始: プロファイリングを開始するための識別子を指定します。
対象コードの実行: プロファイリング対象のPL/SQLコードを実行します。
プロファイリングの終了: プロファイリングを停止し、結果を保存します。
結果の分析: 保存されたプロファイリング結果を分析します。

例: DBMS_PROFILERの使用

BEGIN
    -- プロファイラの初期化
    DBMS_PROFILER.START_PROFILER('profiler_run');
    
    -- プロファイリング対象のPL/SQLコード
    FOR i IN 1..100 LOOP
        NULL; -- 実際の処理が入る部分
    END LOOP;
    
    -- プロファイリングの終了
    DBMS_PROFILER.STOP_PROFILER;
END;

プロファイリング結果の取得と分析

プロファイリング結果はデータベースのテーブルに保存されます。以下のSQLクエリを使用して、結果を取得し分析できます。

-- プロファイリング結果を表示するクエリ
SELECT u.unit_name, d.line#, d.total_occur, d.total_time
FROM plsql_profiler_units u, plsql_profiler_data d
WHERE u.runid = d.runid AND u.unit_number = d.unit_number
ORDER BY d.total_time DESC;

以下は、プロファイリング結果の例です。

UNIT_NAME LINE# TOTAL_OCCUR TOTAL_TIME

MY_PROCEDURE 10 100 0.01
MY_PROCEDURE 11 100 0.02
MY_PROCEDURE 12 100 0.15
MY_PROCEDURE 13 100 0.03

この例では、MY_PROCEDUREの各行に対して、どれだけの回数実行されたか（TOTAL_OCCUR）と、その行がどれだけの時間を消費したか（TOTAL_TIME）が表示されています。LINE#12で最も時間がかかっていることがわかるため、この行の最適化を検討します。

DBMS_TRACE

DBMS_TRACEは、PL/SQLコードの実行フローを追跡するためのツールです。これを使用することで、コードのどの部分が実行されているかを詳細に把握し、デバッグの際に役立てることができます。

DBMS_TRACEの手順

トレースの開始: トレースを開始します。
対象コードの実行: トレース対象のPL/SQLコードを実行します。
トレースの停止: トレースを停止し、結果を保存します。
結果の分析: 保存されたトレース結果を分析します。

例: DBMS_TRACEの使用

BEGIN
    -- トレースの開始
    DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ENABLED);
    
    -- トレース対象のPL/SQLコード
    FOR i IN 1..100 LOOP
        NULL; -- 実際の処理が入る部分
    END LOOP;
    
    -- トレースの停止
    DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_DISABLED);
END;

トレース結果の取得と分析

トレース結果はデータベースのテーブルに保存されます。以下のSQLクエリを使用して、結果を取得し分析できます。

-- トレース結果を表示するクエリ
SELECT text
FROM dba_trace_file_contents
WHERE trace_filename = 'tracefile.trc';

以下は、トレース結果の例です。

TRACE# TIMESTAMP CALL_TYPE OBJECT_NAME LINE#

1 2024-06-30 10:00:00 CALL MY_PROCEDURE 1
2 2024-06-30 10:00:01 RETURN MY_PROCEDURE 1
3 2024-06-30 10:00:02 CALL MY_PROCEDURE 2
4 2024-06-30 10:00:03 RETURN MY_PROCEDURE 2

この例では、MY_PROCEDUREが実行され、各行で呼び出された時刻（TIMESTAMP）、呼び出しの種類（CALL_TYPE）、オブジェクト名（OBJECT_NAME）、および行番号（LINE#）が記録されています。この情報を使用して、コードの実行フローを詳細に分析し、問題を特定します。

プロシージャやファンクションのオーバーロード

PL/SQLでは、同じ名前のプロシージャやファンクションを異なるパラメータリストで定義することができます。これをオーバーロードと言います。

例: プロシージャのオーバーロード

-- パッケージ仕様の作成
CREATE OR REPLACE PACKAGE overload_pkg IS
    PROCEDURE process_data(p_data IN NUMBER);
    PROCEDURE process_data(p_data IN VARCHAR2);
END overload_pkg;

-- パッケージ本体の作成
CREATE OR REPLACE PACKAGE BODY overload_pkg IS

    PROCEDURE process_data(p_data IN NUMBER) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Processing number: ' || p_data);
    END process_data;

    PROCEDURE process_data(p_data IN VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Processing string: ' || p_data);
    END process_data;

END overload_pkg;

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up