PL/SQL

PL/SQL（ピーエル・エスキューエル）は、Oracle社が、Oracle Databaseのためにコンピュータのデータベース言語SQLを独自に拡張したプログラミング言語である。

もともと非手続き型言語であるSQLを手続き型言語 (Procedural Language) に拡張するところから「PL/SQL」と命名された経緯を持つ。PL/SQLを使用すると、手続き型言語で埋め込みSQLによる処理と同様に、変数の利用やif、for、loopなどの制御構造を記述して業務ロジックをデータベース内に実装することができ、問い合わせ結果の行を一件ずつ処理する場合には、カーソルによるFETCHループでの処理を行うことが出来る。

他のデータベースの言語との違い

同じくデータベース言語 SQL の拡張として、Sybase ASE や Microsoft SQL Serverで実装されているTransact-SQL と比較されることがあるが、文法・構文の違いだけでなく、命令のSQLエンジンでの処理のされ方が異なる。Transact-SQLは全ての構文をSQLエンジンだけで処理するが、PL/SQLの場合は、PL/SQL構文はPL/SQLエンジンでの解析・実行が行われ、データ操作言語 (DML; SELECT/INSERT/UPDATE/DELETE) 構文は従来のSQLエンジンにて解析・実行するように内部分岐している。このため、Transact-SQLでのSELECT結果は、ストアドプロシージャから結果セットを直接呼び元へ返却することが可能であるが、PL/SQLではそれができないため、パッケージ定義のカーソルを経由した引渡しや配列変数をつかったデータの返却などで回避しなければならない。

また、PostgreSQLのPL/pgSQLは、PL/SQLを参考に実装されたものであるが、データベース管理システム (DBMS) の実装に依存する部分（トランザクション管理の概念が異なる）や、出力引数の扱いが異なるなど完全な互換性はない。

IBMのDB2はOracleからの乗り換えを狙ってV9.7からPL/SQLをサポートしている。またMariaDBも10.3からOracle Database互換機能としてPL/SQLをサポートしている。

文法

Ada言語との関係

PL/SQLの文法・構文はAda言語をベースにしている。 Oracle Corporation CEOのラリー・エリソンが、Oracle を開発する前に国防総省向けのシステム開発をAda言語によって行っていた。そのシステム計画は失敗したが、その経緯からAda言語を元にすることを採用したと推測される。ちなみにそのシステムの計画名は "Oracle" と呼ばれていた。

データ型

データ型については、変数は文字列型などの基本的な型以外に、ネストした表（配列型。ただし添え字は連続していなくてもよく、リスト構造のようなアクセスが可能）、レコード型（構造体のようなもの）、レコード型の配列、索引付き表（連想配列、ハッシュ表のようなもの）が可能。それ以外にPL/SQLでは、「XXテーブルのYY列の型」や「XXテーブルのレコードの型」といった間接的な型指定が可能なため、テーブルなど依存するデータベースオブジェクトの変更に対しても再解析（リコンパイル）だけで対応でき、また、リコンパイルで問題が発生しない場合にかぎり、実行時にPL/SQLエンジンが自動的にリコンパイルを試みるという利点がある。

ブロックと例外

PL/SQLは基本的に、BEGIN ～ END、もしくは DECLARE ～ BEGIN ～ END による「ブロック」の中にコードを記述する。 PL/SQLブロック内で発生する例外・エラー(Exception)をキャッチする場合には、 BEGINとENDの間にさらにEXCEPTION句を差込み、そこからEND句 までの間に例外処理を記述する。このブロックは、入れ子記述をすることができるため、 Java言語の try ～ catch のように利用できる。（※これは、Java言語が元祖ではなく、Ada → C++言語 → Java言語のようにキャッチアップされていった結果である）

変数、定数、カーソルなどの定義は DECLARE ～ BEGINに記述する。

このブロックを直接記述したものを、「無名ブロック」と呼び、即時に実行される。一方、ストアドプロシージャ・ストアドファンクション・データベーストリガ・パッケージはすぐには実行されず、サーバ内に評価済みの形で登録される（動的SQLはのぞく）。

コメント

コメントは、行の先頭に '--' と記述してから改行まで、もしくはC言語のように /* ～ */で囲んだ箇所（この範囲に含まれる改行もコメントとして認識される）に記述する。

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

ストアドプロシージャ・ストアドファンクションは、PL/SQLのコードに名前を付けて、解析したコードをサーバに登録し、呼び出しが可能な状態にすることができる機能。プロシージャは戻り値無し、ファンクションは戻り値があるという違いだけである。 通常のSQL文と比較して、ストアドファンクション・プロシージャの方が、実行時に解析済みのため処理が早いが、参照しているデータベーススキーマを変更すると（テーブルなどの構成を変更すると）、再解析（コンパイル）が必要になる。

ストアドファンクションは通常のSQL文の関数として使用することができる。

データベーストリガ

登録されている表に対して、行追加、更新、削除の前後のタイミングで自動的に処理を走らせるPL/SQLブロックを登録することができる。これをデータベーストリガと呼ぶ。処理されるデータ1件ずつ処理することができ、また変更前と変更後の値を使うことができる。起動条件を細かく設定することも可能である。本来は「制約」定義で管理できない参照整合性を管理するために用いるものである。業務処理を暗黙に組み込みすぎると、業務処理の変更があった場合に他の応用プログラムの挙動に影響を与えやすいため、注意して利用すべきである。

エラー処理など考慮すべき点は多いが、非正規化した表の同期に使用すると便利である。

パッケージ

PL/SQLがAda言語の特徴を色濃く示すところは「パッケージ」の概念を取り入れているところである。 これは、C言語による静的変数やヘッダファイルによるアクセスコントロールの機能に似たものであり、永続性のある変数や、他のパッケージからの参照を許さない変数やストアドプロシージャ等を作ることができる。

しかし、インスタンス化ができないため、オブジェクト指向言語におけるクラスとは本質的に異なる。 ただ、そのパッケージが初めて呼ばれたときにだけ実行されるという「自動初回コード（INITブロック）」が記述できる点は、C言語などには見られない特徴的な点である。

ビルトイン/ユーティリティパッケージ

主に、データベース定義（CREATE DATABASE文）完了後に実行するSQLスクリプトとしてcatproc.sqlがあるが、この時点ではユーティリティパッケージ(DBMS_XXX)どころか標準SQL関数のCOUNT()なども未定義の状態である。これらの機能は通常、データベースカタログを定義するスクリプトcatalog.sqlの実行後に、catproc.sqlというスクリプトを実行することで定義される。 パッケージSTANDARDで定義されているプロシージャ・ファンクションは、呼び出すときにパッケージ名を修飾せずとも呼び出せる。

このcatprocは、OUIでプロダクトインストールとともにデータベース定義も行う場合や、 別途DBCA(Database Configuraton Assistant)にてデータベースを定義する場合、DBCAでデータベース定義スクリプトを生成した場合にスクリプトの中に含まれた形で実行されるためあまり意識する必要はないが、定義したデータベースの動作がおかしい場合はこのあたりを見直し、場合によってはスクリプトの再実行を行う必要もある。PSR（Patch Set Release：いわゆる集積パッチ）を適用した場合にもこのスクリプトの再実行をする必要がある。

ユーティリティパッケージはバッファ出力用のDBMS_OUTPUTパッケージやファイル出力用のUTL_FILEパッケージ、応用プログラム間の同期通信に利用できるDBMS_PIPE、DBMS_ALERTパッケージ、動的SQLを実現するためのDBMS_SQLパッケージなどがある。

ユーティリティパッケージの名前はDBMS_またはUTL_で始まるため、ユーザーが新たにパッケージを作成する場合はこれらの名前を避けて作成するべきである。

例

※注 DBMS_OUTPUTの出力先を、SQL*PlusというSQLの対話型インターフェースにする場合は、以下のSQL*Plusの設定を行うこと。端末など標準出力への操作はPL/SQLエンジンがネイティブに持つ機能ではなく、ユーティリティ・ツールの機能に依存するためである。

 SET SERVEROUTPUT ON

無名ブロックの例

 DECLARE
    hw    VARCHAR2(100) := 'Hello World!';
 BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World!') ;
    DBMS_OUTPUT.PUT_LINE('VARIABLE hw = '||hw) ;
 END;
 /

ストアドプロシージャの例

 CREATE OR REPLACE PROCEDURE helloworld (str IN VARCHAR2)
 AS
    /* 定義部は AS から BEGIN となる。*/
    hw    VARCHAR2(100) := 'Hello World!';
 BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World!') ;
    DBMS_OUTPUT.PUT_LINE('VARIABLE hw = '||hw) ;
    DBMS_OUTPUT.PUT_LINE('Parameter str = '||str) ;
 END;
 /

例外処理の例

 DECLARE
     FNAME    EMP.ename%TYPE ; 
 BEGIN
     SELECT ename INTO fname FROM emp ;
 EXCEPTION
     WHEN TOO_MANY_ROWS THEN  -- 定義済み例外識別子
        DBMS_OUTPUT.PUT_LINE('行が多すぎます') ;
     WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('レコードが1件もありません') ;
     WHEN OTHERS THEN -- その他の例外はすべてここでキャッチ
        DBMS_OUTPUT.PUT_LINE('Oracle エラー :'||SQLERRM) ;
 END;
 /

カーソル処理の例(制御構造も多少)

 DECLARE
     CURSOR c1 IS
         SELECT ename FROM EMP ;
     FNAME    EMP.ename%TYPE ; 
 BEGIN
     /* カーソルc1をオープンし、全てのレコードをフェッチしきるまでループする 
         フェッチ毎に 表empのカラムenameを取得しつつ表示する */
     OPEN c1 ;
     LOOP
         FETCH c1 INTO fname ;
         EXIT WHEN c1%NOTFOUND; -- カーソル状態をチェック
          DBMS_OUTPUT.PUT_LINE(fname);
         IF length(fname) < 5 THEN  -- 文字列の長さチェック
             DBMS_OUTPUT.PUT_LINE('→ TOO SHORT') ;
         ELSE
             DBMS_OUTPUT.PUT_LINE('→ TOO LONG!');
         END IF ;
     END LOOP ;
     CLOSE c1 ;
 EXCEPTION
     WHEN OTHERS THEN -- その他の例外はすべてここでキャッチ
        DBMS_OUTPUT.PUT_LINE('Oracle エラー :'||SQLERRM) ;
 END ;
 /