Part 1：ブロックによる構築

PL/SQLとは

この質問に答えるにあたり覚えておくべき重要なことは、閲覧されるWebサイトや実行されるアプリケーションのすべてが、ソフトウェア・テクノロジーの スタック により構成されているということです。スタックの最上層にはプレゼンテーション・レイヤー、つまりユーザーが直接操作する画面や対話型デバイスが位置します（プレゼンテーション・レイヤー実装用の言語の中で現在もっとも多く利用されているのは、Javaと.NETです）。スタックの最下層には、ハードウェアと通信するマシン・コードが位置します。

このテクノロジー・スタックの中間あたりに位置するものが データベース です。データベースは、大量の複雑なデータを保存、操作するためのソフトウェアです。現在では、SQLを中心として確立されたリレーショナル・データベース・テクノロジーが有力なデータベース・テクノロジーとなっています。

SQLは、非常に強力な集合指向言語で、その唯一の目的はリレーショナル・データベースの内容を操作することです。Oracle Databaseを基盤としたアプリケーションを開発する開発者（またはテクノロジー・スタックのより低いレベルにあたるコードの開発者）は、そのデータベース内にあるデータの取得やデータの変更のためにSQL文を実行する 必要があります 。しかし、SQLを使用して、アプリケーションに必要となるすべてのビジネス・ロジックやエンドユーザー機能を実装することは不可能です。これが、PL/SQLを利用する動機になります。

PL/SQL は、 Procedural Language/Structured Query Language （手続き型言語/構造型問合せ言語）の略です。PL/SQLでは一連の手続き型コマンド（IF文、ループ、代入）が提供されます。これらのコマンドをブロック内で構造化して、SQL機能の範囲を補完、拡張できます。

確かに、PL/SQLを使用 しなくても 、SQLやOracle Databaseの上層にアプリケーションを構築することは可能です。しかし、PL/SQLを利用してデータベース固有の操作（中でもSQL文）を実行することには複数の利点があります。この利点には、SQLとの緊密な統合、ネットワーク・トラフィックの削減によるパフォーマンスの向上、移植性などがあります（PL/SQLプログラムはあらゆるOracle Databaseインスタンスで実行できます）。そのため、多くのアプリケーションのフロントエンド・コードでは、SQL文とPL/SQLブロックの両方を実行して、パフォーマンスの最大化とアプリケーションの保守性の向上を同時に実現しています。

PL/SQLプログラムの構築ブロック

PL/SQLはブロック構造化言語です。PL/SQLブロックは、DECLARE、BEGIN、EXCEPTION、ENDというキーワードにより定義します。これらのキーワードは、ブロックを次の3つのセクションに分割します。

宣言セクション：変数、定数、その他のコード要素の宣言文（宣言後のコード要素はそのブロック内で使用可能）

実行可能セクション：ブロックの実行時に実行される文

例外処理セクション：実行可能セクションの実行時に発生した例外を"キャッチ"（捕捉）するために使用できる特別構造のセクション

実行可能セクションのみが必須です。ブロック内で文を宣言する必要はなく、またそのブロック内で発生する例外を捕捉する必要はありません。

ブロック自体も実行可能文であるため、ブロックを別のブロック内にネストできます。

次にいくつかの例を挙げます。

昔からよくある"Hello World!"ブロックには、画面にテキストを表示するDBMS_OUTPUT.PUT_LINEプロシージャをコールする次のような実行可能セクションが含まれます。

ファンクションとプロシージャ（名前付きブロックの種類）について詳しくは、この記事の後半で取り上げます。パッケージもこれらと同様ですが、簡単に説明すると、パッケージは、複数のファンクションおよびプロシージャを格納するコンテナです。Oracleでは多くの標準提供パッケージや組込みパッケージによってPL/SQLが拡張されます。

次のブロック例では、'Hello World!'という文字列を格納するVARCHAR2（文字列）型の最大長100バイトの変数を宣言します。次に、DBMS_OUTPUT.PUT_LINEで、リテラル文字列ではなくこの変数を受け入れて表示します。

ここで、変数にl_messageという名前を付けている点に注意してください。私は通常、 ローカル変数 （コードのブロック内で定義する変数）に対してl_という接頭辞を使用し、パッケージ内に定義する グローバル変数 に対してはg_という接頭辞を使用します。

次のブロック例では、例外セクションを追加します。この例外セクションでは、発生する可能性のある すべて の例外（WHEN OTHERS）を捕捉し、エラー・メッセージを表示します。このエラー・メッセージは、Oracleが提供するSQLERRMファンクションが返すものです。

PL/SQLブロックの実行

PL/SQLコードのブロックを記述したら、次にそのブロックを実行できます。PL/SQLコードを実行するためのさまざまなツールが存在します。もっとも基本的なツールはSQL*Plusです。SQL*Plusは、SQL文やPL/SQLブロックを実行するためのコマンドライン・インタフェースです。図1に、もっとも単純な"Hello World!"ブロック例をSQL*Plusで実行した例を示します。

SQL*Plusでデータベースに接続した後の最初の作業は、サーバー出力をオンにすることです。この操作によって、DBMS_OUTPUT.PUT_LINEをコールするとテキストが画面に表示されるようになります。次に、ブロックを構成するコードを入力します。最後に、スラッシュ（/）を入力して、SQL*Plusにこのブロックを実行させます。

この結果、SQL*Plusでブロックが実行され、画面に"Hello World!"と表示されます。SQL*Plusは、SQL文とPL/SQLブロックを実行するための最低限の機能を持つツールとしてオラクルが提供しています。SQL*Plusのみを使い続ける開発者もいますが、ほとんどの開発者は統合開発環境（IDE）を使用します。トレーニング・セッションで私が独自に行った非公式の調査によれば、そのようなIDEの中でも次のツールが特に人気があります。

Oracle SQL Developer（オラクル）

Toad、SQL Navigator（Quest Software）

PL/SQL Developer（Allround Automations）

PL/SQLブロックの作成、保存、実行、あるいはサーバー出力の有効/無効の切替えを行うためのウィンドウや手順は、それぞれのツールで少しずつ異なります。このシリーズでは、読者がSQL*Plusのみを利用すること、およびSQL*Plusコマンド・ウィンドウで記事のすべての文を実行することを想定します。

ブロックに名前を付ける

これまでに紹介したブロックはすべて"匿名"です。つまり、名前がありません。仮に、文を構造化するために匿名ブロックしか使用できないとすると、PL/SQLにより大規模で複雑なアプリケーションを構築することが非常に難しくなります。しかし、PL/SQLではコードの 名前付き ブロックの定義がサポートされます。名前付きブロックは サブプログラム とも呼ばれます。サブプログラムは、プロシージャにもファンクションにも適用できます。一般的に、プロシージャはアクションを実行するために使用し、ファンクションは計算して値を返すために使用します。サブプログラムについては、このシリーズの今後の記事で詳しく取り上げます。今のところは、サブプログラムの作成の基本的な考え方について理解しましょう。

ここで、アプリケーション内の複数の場所から"Hello World!"と表示する必要があると仮定します。これらすべての場所で同じロジックを繰り返し記述することは本当に避けるべきです。たとえば、このメッセージを"Hello Universe!"に変更する必要がある場合はどうなるでしょうか。このロジックが出現するコード内のすべての場所を検索する必要があります。

そうではなく、次のようなデータ定義言語（DDL）コマンドを実行して、hello_worldというプロシージャを作成することにします。

これで結果的にPL/SQLを拡張したことになります。データベースにインストールされているオラクル製のプログラム（DBMS_OUTPUT.PUT_LINEなど）のコールに加え、自作したサブプログラムもPL/SQLブロック内でコールできます。

世界にどのようにあいさつするかの詳細についてはすべて、プロシージャの本体、すなわち実装内に隠しました。このhello_worldプロシージャをコールすれば、DBMS_OUTPUT .PUT_LINEへのコールを記述しなくても、あるいは文字列の適切な整形方法を検討しなくても、必要なメッセージを表示できます。このプロシージャは、アプリケーションのどの場所からでもコールできます。さらに、この文字列を変更する必要がある場合にも、この文字列の 唯一の定義ポイント の1箇所を変更するだけで済みます。

hello_worldプロシージャは非常に単純です。実際に作成するプロシージャにははるかに多くのコードが含まれ、たいていは パラメータ も伴います。パラメータは、サブプログラムがコールされたときにその 内部に 情報を渡すものです。パラメータを使用すれば、より柔軟で汎用的なサブプログラムを作成できます。パラメータは、さまざまなコンテキストで使用できます。

前述のとおり、"Hello World!"の代わりに"Hello Universe!"と表示したくなる日が来るかもしれません。そのような場合に、次のようにhello_worldプロシージャをコピーして、表示する文字列を変更することも 可能ではあります 。

しかし、この方法では、"同じ"helloプロシージャの大量のバリエーションを作成することになりかねません。そうなれば、アプリケーションの保守が非常に難しくなります。もっと良いアプローチがあります。それは、プロシージャを分析し、メッセージを変更する必要がある場合にも同一のままの部分（静的な部分）と、変更される部分を識別することです。変更される部分をパラメータとして渡せば、1つのプロシージャを異なる状況で使用できます。

したがって、hello_world（およびhello_universe）を、hello_placeという新しいプロシージャに変更します。

プロシージャ名の直後に左括弧と右括弧を追加し、その括弧の中に1つのパラメータを指定しています。複数のパラメータを指定することもできますが、各パラメータは次のような同じ基本形に従います。

つまり、パラメータの名前、パラメータの使用モードまたは使用方法（IN=読取り専用）、およびこのパラメータによりサブプログラムに渡すデータの型を指定する必要があります。

この例の場合は、hello_placeプロシージャに読取り専用の文字列を渡します。

これで、世界（world）と宇宙（universe）の 両方に 対してあいさつできるようになります。

このシリーズの今後の記事で、再利用の考え方と繰り返しを避ける方法について詳しく確認しますが、この単純な例からでも、名前付きブロックの裏にロジックを隠す利点が分かるはずです。

次に、単に"Hello"メッセージを表示するだけでは満足できなくなったと仮定します。あるときには、データベース表にメッセージを保存する必要があり、またあるときには、文字列をWebブラウザに表示するためにホスト環境に返す必要があります。言いかえれば、"Hello"メッセージを構成する方法と、"Hello"メッセージを使用する（表示する、保存する、別のプログラムに送信するなど）方法を区別する必要があるのです。この必要な柔軟性レベルは、メッセージを構成するコードを専用のファンクション内に移動することで実現できます。

プログラムの型がPROCEDUREからFUNCTIONに変更されています。

サブプログラム名が、実行されるアクションではなく返すデータを示すようになっています。

サブプログラムの本体（実装）に、メッセージを構成してコール元のブロックに返すRETURN句が追加されています。

パラメータ・リストの後のRETURN句では、ファンクションによって返されるデータの型を設定しています。

メッセージの構成に必要となるコードをhello_messageファンクション内に配置することで、このメッセージを複数の方法で使用できます。たとえば、このファンクションをコールしてメッセージを取得し、変数に代入できます。

hello_messageファンクションをPL/SQL文の一部（この例では、文字列を変数に代入している部分）としてコールしている点に注意してください。hello_messageファンクションは文字列を返すため、任意の実行可能文で文字列の代わりに使用できます。

また、hello_placeプロシージャで、文字列の作成に使用するコードをこのファンクションのコールに置き換えることもできます。

BEGIN INSERT INTO message_table (message_date, MESSAGE_TEXT) VALUES (SYSDATE, hello_message('Chicago'));

"hello place"ロジックは非常に単純ですが、1つ以上の実行可能文（アルゴリズム）に名前を割り当てて、その名前と必要なパラメータを指定するだけでそのアルゴリズムを参照できるという利点が分かるようになっています。

名前付きのPL/SQLブロックにより、比較的簡単に理解、保守可能な複雑なアプリケーションを構築できるようになります。

Oracle Databaseにおける名前について

ここまでに、ロジックに名前を割り当てることの重要性を理解できました。次に、PL/SQL、より一般的にはOracle Databaseでの名前（正確に言えば識別子）のルールについてお話ししましょう。

Oracle Databaseで有効な識別子を構成するためのルールは次のとおりです。

最大長は30文字です。

1文字目は英字である必要がありますが、2文字目以降の文字には英字、数字（0～9）、ドル記号（$）、アンダースコア（_）、ナンバー記号（#）のいずれかを使用できます。次の文字列はすべて有効な識別子です。

hello_world

hello$world

hello#world

しかし、次の文字列は無効です。

1hello_world

hello%world

PL/SQLの識別子では、大文字小文字が 区別されません 。PL/SQLでは、次のすべての文字列が同じ識別子として扱われます。

hello_world

Hello_World

HELLO_WORLD

柔軟性を高めるために、Oracle Databaseでは、識別子を二重引用符で囲むことで、上記の2番目と3番目のルールによる制約を取り払うことができます。 引用識別子 には、二重引用符を除くすべての印刷可能文字列を含めることができ、大文字小文字の区別も維持されます。したがって、次の文字列はすべて有効で、異なる識別子になります。

"Abc" "ABC" "a b c"

PL/SQLコードでは引用識別子を使用することはほとんどありません。ただし、ネーミング規則に従うため、あるいは大文字小文字を織り交ぜた文字列の方が読みやすいという理由で引用識別子を使用する開発グループもあります。

同じルールがデータベース・オブジェクト（表、ビュー、プロシージャなど）の名前にも適用され、さらに次の追加ルールも適用されます。それは、データベース・オブジェクトの名前を二重引用符で囲まない限り、Oracle Databaseには名前が大文字で保存されるというルールです。

次のようなプロシージャを作成したとします。

このプロシージャはOracle DatabaseにはHELLO_WORLDという名前で保存されます。

次のブロックでは、このプロシージャを3回コールしています。すべてのコールで名前が異なるように見えますが、同じプロシージャが実行されます。

このコールにより、データベース内部では、HELLO_WORLDではなくhello_worldという名前のプロシージャが検索されます。

大文字で保存したくないサブプログラム名については、サブプログラムの作成時に名前を二重引用符で囲むようにしてください。

PL/SQLブロック内でのSQLの実行

PL/SQLはデータベース・プログラミング言語です。PL/SQLで記述するほぼすべてのプログラムで、SQLを使用してOracle Databaseの読取りまたは書込み、あるいはその両方が実行されます。このシリーズでは読者がSQLの実用的な知識を持っていると想定していますが、PL/SQLブロック内からSQL文をコールする方法を知っている必要があります。

うれしいことに、Oracle Databaseでは、PL/SQLでのSQL文の記述や実行が非常に簡単です。大部分で、PL/SQLブロック内でSQL文を直接記述し、そのSQL文とPL/SQLコードとの連結に必要となるコードを追加するだけで済みます。

たとえば、employeesという表があり、この表にemployee_idという主キー列とlast_nameという列があるとします。この場合、次のようにして、IDが138の従業員の姓を表示できます。

次に、この問合せをPL/SQLブロック内で実行して、姓を表示することにしましょう。そのためには、表から取得した姓をローカル変数に"コピー"する必要があります。この操作は、INTO句を使用して実行できます。

まず、ローカル変数を宣言していますが、そのために、PL/SQLのもう1つの優れた機能である、表の列に変数のデータ型を紐付ける機能（アンカー）を取り入れています（アンカーについて詳しくは、シリーズの今後の記事で取り上げます）。

次に、データベースに対して問合せを実行し、従業員の姓を取得してl_name変数に直接代入しています。

もちろん、PL/SQLで実行したいことはSELECT文だけではありません。PL/SQLでは、表への挿入、表の更新、表からの行の削除も実行したいことでしょう。それぞれの種類のデータ操作言語（DML）文について、次に例を示します。

部門10のすべての従業員を削除し、削除した行数を表示します。

この例では、DELETE文の内部でPL/SQL変数を直接参照しています。このブロックを実行すると、変数名が実際の値の10に置き換えられた後に、SQLエンジンによってDELETEが実行されます。SQL%ROWCOUNTは、セッション内で最後に実行されたDML文によって変更された行数を返す特別なカーソル属性です。

部門10のすべての従業員を、給与が20%上がるように更新します。

このブロックでは、すべての列の値を変数ではなくリテラルとして、SQL文の内部で直接指定しています。

PL/SQL開発者向けリソース

PL/SQLを始めたばかりの開発者は、無料のオンライン・リソースが大量にあることを認識して、ぜひ利用してください。使いやすく人気のあるリソースの一部を次に紹介します。

Oracle Technulogy NetworkのPL/SQLのページ（ oracle.com/technetwork/database/features/plsql ）：人気のあるOracle Technulogy Network WebサイトでPL/SQLのリソースを探すための出発点として適しています。

PL/SQL Obsession（ToadWorld.com/SF）：著者のPL/SQL開発に関するさまざまなリソースがあります（トレーニング資料、プレゼンテーション、サンプル・コード、標準、ベスト・プラクティスに関するビデオなど）。

PL/SQL Challenge（plsqlchallenge.com）：PL/SQLの知識をテストして知識を深めるための、PL/SQLクイズが毎日提供されています。

次のステップ：ブロック実行の制御

この記事では、より広範なOracle Databaseの世界に対するPL/SQLの位置付けについて説明しました。また、PL/SQL文を実行するコード・ブロックの定義方法や、アプリケーション・コードでより簡単に利用、維持できるようにするためのブロックの名前の付け方についても説明しました。最後に、PL/SQL内でのSQL文の実行方法について紹介しました。

次のステップ

その他の記事 PL/SQLのコラム

ディスカッション PL/SQL

ダウンロード Oracle Database 11g

テスト PL/SQLの知識

なぜブロックをネストするのか

1つ以上の実行可能文で構成される任意のセットの前にBEGINを配置し、そのセットの後にENDを配置することで、それらの実行可能文をネストするブロックを作成できます。ブロックのネストにはおもに2つの利点があります。(1)ネストされたブロック内でのみ必要となる変数について、メモリの割当てを遅らせることができます。(2)ネストされたブロック内のいずれかの文によって発生する例外が伝播する範囲を限定できます。

次のブロックについて考えてみます。

このコードにより、SYSDATEによって返される今日の日付が2011年の元旦以降であれば"Hello World!"と表示され、2011年の元旦より前であれば"Hello"メッセージのみが表示されます。しかし、このブロックを2010年に実行した場合でも、l_message2変数用のメモリが割り当てられます。

このブロックを次のように再構成した場合、l_message2用のメモリは2010年を過ぎた後でのみ割り当てられるようになります。

この場合、ネストされたブロックではVALUE_ERROR例外が発生します。l_message2が最大5バイトであり、"World!"文字列に対して小さすぎるためです。ネストされたブロックの例外セクションにより、エラーが捕捉されて表示されます。その後、この外側のブロックで実行が継続します。

シリーズの今後の記事では、PL/SQLでの例外処理のしくみについて詳しく説明します。

シリーズの次回の記事では、ブロック内での実行フローの制御方法、IFおよびCASEを使用した条件ロジック、FOR、WHILE、単純なループを使用した反復ロジック、例外の発生と処理について説明します。

Steven Feuerstein （ steven.feuerstein@quest.com ）は、Quest SoftwareのPL/SQLエヴァンジェリストです。これまで、 Oracle PL/SQL Programming や Oracle PL/SQL Best Practices （O'Reilly Media）などを含む、Oracleのプログラミング言語に関する著書を10冊発行しています。

ご意見ご感想をお寄せください。