8動的SQL

動的SQLは、実行時に動的にSQLステートメントを作成できるようにするプログラミング手法です。 SQLステートメントの全文がコンパイル時に不明になる可能性があるため、動的SQLを使用して、より汎用的で柔軟なアプリケーションを作成できます。たとえば、動的SQLを使用すると、実行時まで名前がわからないテーブルを操作するプロシージャを作成できます。

Oracleの過去のリリースでは、PL / SQLアプリケーションに動的SQLを実装する唯一の方法はDBMS_SQLパッケージを使用します。 Oracle8iでは、DBMS_SQLパッケージの代わりとなるネイティブ動的SQLが導入されています。ネイティブ動的SQLを使用すると、動的SQL文をPL / SQLブロックに直接配置できます。

この章では、次のトピックについて説明します。

• 動的SQLとは何ですか。
• 動的SQLを使用する場合
• ネイティブ動的SQLを使用した動的SQLシナリオ
• ネイティブ動的SQLとDBMS_SQLパッケージ
• アプリケーションPL / SQL以外の開発言語

動的SQLとは

動的SQLを使用すると、実行時まで全文がわからないSQLステートメントを参照するプログラムを作成できます。動的SQLについて詳しく説明する前に、静的SQLを明確に定義することで、動的SQLを理解するための良い出発点が得られる場合があります。静的SQLステートメントは実行ごとに変更されません。静的SQLステートメントの全文はコンパイル時に認識され、次の利点があります。

• コンパイルが成功すると、SQLステートメントが有効なデータベースオブジェクトを参照していることが確認されます。
• コンパイルが成功すると、データベースオブジェクトにアクセスするために必要な権限が設定されていることが確認されます。
• 静的SQLのパフォーマンスは、一般的に動的SQLよりも優れています。

これらの利点があるため、静的SQLを使用して目標を達成できない場合、または静的SQLの使用が動的SQLに比べて面倒な場合にのみ、動的SQLを使用する必要があります。ただし、静的SQLには、動的SQLで克服できる制限があります。 PL / SQLプロシージャで実行する必要のあるSQL文の全文を常に知っているとは限りません。プログラムは、実行するSQLステートメントを定義するユーザー入力を受け入れる場合があります。または、プログラムは、正しいアクションのコースを決定するために、いくつかの処理作業を完了する必要がある場合があります。このような場合は、動的SQLを使用する必要があります。

たとえば、実行時まで正確なテーブル名が不明なデータウェアハウス環境のテーブルに対して標準クエリを実行するレポートアプリケーションについて考えてみます。データウェアハウス内の大量のデータに効率的に対応するために、四半期ごとに新しいテーブルを作成して、四半期の請求書情報を格納します。これらのテーブルはすべてまったく同じ定義を持ち、四半期の開始月と年に従って名前が付けられます。たとえば、INV_01_1997、INV_04_1997、 INV_07_1997、INV_10_1997、INV_01_1998など。このような場合、動的を使用できます。実行時にテーブル名を指定するためのレポートアプリケーションのSQL。

静的SQLでは、プログラムのSQLステートメントによって参照されるテーブル定義などのすべてのデータ定義情報がコンパイル時に認識されている必要があります。データ定義が変更された場合は、プログラムを変更して再コンパイルする必要があります。 SQLステートメントは実行時に「オンザフライ」で変更できるため、動的SQLプログラムはデータ定義情報の変更を処理できます。したがって、動的SQLは静的SQLよりもはるかに柔軟性があります。動的SQLを使用すると、使用する特定のSQLステートメントに依存しないプロセスをコードで定義するため、再利用可能なアプリケーションコードを記述できます。

さらに、動的SQLを使用すると、静的でサポートされていないSQLステートメントを実行できます。データ定義言語（DDL）ステートメントなどのSQLプログラム。これらのステートメントのサポートにより、PL / SQLプログラムでより多くのことを達成できます。

動的SQLを使用する場合

静的SQLがをサポートしていない場合は、動的SQLを使用する必要があります。実行する操作、またはPL / SQLプロシージャによって実行する必要のある正確なSQL文がわからない場合。これらのSQLステートメントは、ユーザー入力に依存する場合もあれば、プログラムによって実行される処理作業に依存する場合もあります。次のセクションでは、動的SQLを使用する必要がある一般的な状況と、動的SQLを使用して解決できる一般的な問題について説明します。

動的DMLステートメントを実行するには

動的SQLを使用して実行できます正確なSQLステートメントが実行時までわからないDMLステートメント。例については、「DBMS_SQLパッケージコードとネイティブ動的SQLコードの例」および「サンプルDML操作」のDMLの例を参照してください。

PL / SQLの静的SQLでサポートされていないステートメントを実行するには

PL / SQLでは、静的SQLを使用して次のタイプのステートメントを実行することはできません。

• CREATE、DROP、GRANT、およびREVOKE
• ALTER SESSIONやSET
  関連項目：

  DDLおよびSCLステートメントについては、Oracle8iSQLリファレンスを参照してください。

PL /内でこれらのタイプのステートメントのいずれかを実行する必要がある場合は、動的SQLを使用します。 SQLブロック。

さらに、PL / SQLの静的SQLでは、SELECTTABLE句を使用できません。 / div>ステートメント。動的SQLにはそのような制限はありません。たとえば、次のPL / SQLブロックには、TABLE句とネイティブ動的SQLを使用するSELECTステートメントが含まれています。

動的クエリを実行するには

動的SQLを使用して、実行時まで全文がわからないクエリである動的クエリを実行するアプリケーションを作成できます。次のような多くの種類のアプリケーションで動的クエリを使用する必要があります。

• 実行時にクエリ検索または並べ替え条件を入力または選択できるアプリケーション
• ユーザーが入力できるアプリケーションまたは、実行時にオプティマイザヒントを選択します。
• テーブルのデータ定義が絶えず変化するデータベースをクエリするアプリケーション
• 新しいテーブルが頻繁に作成されるデータベースをクエリするアプリケーション

例については、「クエリの例」を参照し、「ネイティブ動的SQLを使用した動的SQLシナリオ」のクエリ例を参照してください。

コンパイル時に存在しないデータベースオブジェクトを参照するには

多くの種類のアプリケーションは、定期的に生成されるデータと対話する必要があります。たとえば、新しいテーブルが定期的に生成されるため、コンパイル時にデータベーステーブルの定義を決定できても、テーブルの名前を決定できない場合があります。アプリケーションはデータにアクセスする必要がありますが、実行時までテーブルの正確な名前を知る方法はありません。

動的SQLでは実行時まで待機してを指定できるため、動的SQLでこの問題を解決できます。アクセスする必要のあるテーブル名。たとえば、「動的SQLとは」で説明したサンプルのデータウェアハウスアプリケーションでは、四半期ごとに新しいテーブルが生成され、これらのテーブルの定義は常に同じです。この場合、次のような動的SQLクエリを使用して、実行時にユーザーがテーブルの名前を指定できるようにすることができます。

実行を動的に最適化するには

静的を使用する場合SQLの場合、コンパイル時にSQLステートメントをどのように作成するか、ステートメントにヒントを含めるかどうか、ヒントを含める場合はどのヒントを含めるかを正確に決定する必要があります。ただし、動的SQLを使用して、実行を最適化したり、ヒントをSQLステートメントに動的に連結したりする方法でSQLステートメントを作成できます。これにより、再コンパイルを必要とせずに、現在のデータベース統計に基づいてヒントを変更できます。

たとえば、次の手順では、a_hintという変数を使用してユーザーを許可します。ヒントオプションをSELECTステートメントに渡すには：

この例では、ユーザーはa_hint：

動的PL / SQLブロックを呼び出すには

EXECUTE IMMEDIATE文。動的PL / SQLブロックを呼び出す機能は、実行するモジュールが実行時に動的に決定されるアプリケーションの拡張とカスタマイズに役立ちます。

たとえば、イベントを受け取るアプリケーションを作成するとします。番号を付けて、イベントのハンドラにディスパッチします。ハンドラーの名前は、EVENT_HANDLER_ event_numの形式です。ここで、event_numはイベントの番号です。 1つのアプローチは、以下に示すように、ディスパッチャーをswitchステートメントとして実装することです。この場合、コードは適切なハンドラーを静的に呼び出すことで各イベントを処理します。

このコードは、ディスパッチャーコードのため、あまり拡張できません。新しいイベントのハンドラーが追加されるたびに更新する必要があります。ただし、ネイティブ動的SQLを使用すると、次のような拡張可能なイベントディスパッチャを作成できます。

呼び出し元の権利を使用して動的操作を実行するには

動的で呼び出し元の権利機能を使用するSQLの場合、呼び出し側の特権とスキーマの下で動的SQLステートメントを発行するアプリケーションを構築できます。これらの2つの機能、invoker-rightsとdynamic SQLを使用すると、呼び出し元のデータとモジュールを操作してアクセスできる再利用可能なアプリケーションサブコンポーネントを構築できます。

ネイティブ動的SQLを使用した動的SQLシナリオ

このセクションで説明するシナリオは、パワーと柔軟性を示しています。ネイティブ動的SQLの。このシナリオには、ネイティブ動的SQLを使用して次の操作を実行する方法を示す例が含まれています。

• DDLおよびDML操作を実行する
• 単一行および複数行のクエリを実行する

データモデル

このシナリオのデータベースは、次のデータモデルを持つ企業の人事データベース（hrという名前）です。 ：

officesという名前のマスターテーブルにはすべての会社の場所のリストが含まれています。officesテーブルには次の定義があります。 ：

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

複数のemp_ロケーションテーブルには、従業員情報が含まれています。ロケーションは、オフィスがある都市の名前です。たとえば、emp_houstonという名前のテーブルには、会社のヒューストンオフィスの従業員情報が含まれ、emp_bostonという名前のテーブルには従業員が含まれます。会社のボストンオフィスの情報。

各emp_ロケーションテーブルの定義は次のとおりです。

次のセクションでは、hrのデータに対して実行できるさまざまなネイティブ動的SQL操作について説明します。データベース。

サンプルDML操作

次のネイティブ動的SQLプロシージャは、特定の役職を持つすべての従業員に昇給を提供します。

サンプルDDL操作

EXECUTE IMMEDIATEステートメントはDDL操作を実行できます。たとえば、次の手順でオフィスの場所を追加します。

次の手順でオフィスの場所を削除します。

動的単一行クエリのサンプル

EXECUTE IMMEDIATEステートメントは動的な単一行クエリを実行できます。 USING句でバインド変数を指定し、結果の行をステートメントのINTO句で指定されたターゲットにフェッチできます。

次の関数は、指定されたジョブを実行している特定の場所の従業員数を取得します。

動的複数行クエリのサンプル

OPEN-FOR、FETCH、およびCLOSEステートメントは、動的な複数行のクエリを実行できます。たとえば、次の手順では、指定された場所で特定のジョブを持つすべての従業員を一覧表示します。

ネイティブ動的SQLとDBMS_SQLパッケージ

Oracleには、動的SQLを使用するための2つの方法があります。 PL / SQL内：ネイティブ動的SQLおよびDBMS_SQLパッケージ。ネイティブ動的SQLを使用すると、動的SQL文をPL / SQLコードに直接配置できます。これらの動的文には、DML文（クエリを含む）、PL / SQL匿名ブロック、DDL文、トランザクション制御文、およびセッション制御文が含まれます。

ほとんどのネイティブ動的SQL文を処理するには、 IMMEDIATEステートメント。ただし、複数行のクエリ（SELECTステートメント）を処理するには、OPEN-FOR、FETCH、およびCLOSEステートメント。

DBMS_SQLパッケージは、プログラムAPIを提供するPL / SQLライブラリです。 SQL文を動的に実行します。 DBMS_SQLパッケージには、カーソルを開く、カーソルを解析する、バインドを提供するなどのプログラムインターフェイスがあります。DBMS_SQLパッケージを使用するプログラムは呼び出しを行いますこのパッケージに追加して、動的SQL操作を実行します。

次のセクションでは、両方の方法の利点に関する詳細情報を提供します。

パッケージ。PL/ SQLユーザーズ・ガイドおよびリファレンスでは、ネイティブ動的SQLは単に動的SQLと呼ばれています。

ネイティブ動的SQLの利点

ネイティブ動的SQLには、パッケージ：

使いやすさ

ネイティブ動的SQLは、DBMS_SQLパッケージよりもはるかに簡単に使用できます。ネイティブ動的SQLはSQLと統合されているため、PL / SQLコード内で現在静的SQLを使用しているのと同じ方法で使用できます。さらに、ネイティブ動的SQLコードは通常、DBMS_SQLパッケージを使用する同等のコードよりもコンパクトで読みやすくなっています。

DBMS_SQLパッケージは、ネイティブの動的SQLほど簡単には使用できません。厳密な順序で使用しなければならない多くの手順と機能があります。通常、DBMS_SQLパッケージを使用する場合、単純な操作を実行するには大量のコードが必要です。代わりにネイティブ動的SQLを使用することで、この複雑さを回避できます。

表8-1に、DBMS_SQLパッケージとネイティブ動的SQL。

表8-1DBMS_SQLパッケージとネイティブ動的SQLのコード比較

パフォーマンスの向上

PL / SQLインタープリターにはネイティブ動的SQLのサポートが組み込まれているため、PL / SQLでのネイティブ動的SQLのパフォーマンスは静的SQLのパフォーマンスに匹敵します。したがって、ネイティブ動的SQLを使用するプログラムのパフォーマンスは、DBMS_SQLパッケージを使用するプログラムのパフォーマンスよりもはるかに優れています。通常、ネイティブ動的SQLステートメントは、DBMS_SQLパッケージを使用する同等のステートメントよりも1.5〜3倍優れたパフォーマンスを発揮します。もちろん、パフォーマンスの向上はアプリケーションによって異なる場合があります。

DBMS_SQLパッケージは手続き型APIに基づいているため、高いプロシージャコールとデータコピーのオーバーヘッドが発生します。たとえば、変数をバインドするたびに、DBMS_SQLパッケージはPL / SQLバインド変数をそのスペースにコピーして、後で実行中に使用できるようにします。同様に、フェッチを実行するたびに、最初にデータがDBMS_SQLパッケージによって管理されるスペースにコピーされ、次にフェッチされたデータが一度に1列ずつ適切な場所にコピーされます。 PL / SQL変数。データのコピーにより、かなりのオーバーヘッドが発生します。対照的に、ネイティブ動的SQLは、ステートメントの準備、バインド、および実行の各ステップを1つの操作にバンドルするため、データのコピーとプロシージャコールのオーバーヘッドが最小限に抑えられ、パフォーマンスが向上します。

パフォーマンスのヒント

ネイティブ動的SQLまたはDBMS_SQLパッケージのいずれかを使用する場合、バインド変数を使用すると、Oracleが複数のSQLステートメントに対して単一のカーソルを共有できるため、バインド変数を使用してパフォーマンスを向上させることができます。

たとえば、次のネイティブ動的SQLコードはバインド変数を使用しません。

個別のmy_deptno変数ごとに、新しいカーソルが作成されます。リソースの競合やパフォーマンスの低下を引き起こす可能性があります。代わりに、次の例のように、バインド変数としてmy_deptnoをバインドします。

ここでは、同じカーソルがバインドmy_deptnoにより、パフォーマンスとスケーラビリティが向上します。

ユーザー定義型のサポート

ネイティブ動的SQLは、PL /の静的SQLでサポートされるすべての型をサポートします。 SQL。したがって、ネイティブ動的SQLは、ユーザー定義オブジェクト、コレクション、REFsなどのユーザー定義型のサポートを提供します。 DBMS_SQLパッケージは、これらのユーザー定義型をサポートしていません。

レコードへのフェッチのサポート

ネイティブ動的SQLと静的SQLはどちらもレコードへのフェッチをサポートしていますが、 DBMS_SQLパッケージはそうではありません。ネイティブ動的SQLを使用すると、クエリの結果の行をPL / SQLレコードに直接フェッチできます。

次の例では、クエリの行をemp_recレコード：

DBMS_SQLパッケージの利点

DBMS_SQLパッケージには、ネイティブ動的SQLに比べて次の利点があります。

クライアント側プログラムのサポート

現在、DBMS_SQLパッケージはクライアント側プログラムでサポートされていますが、ネイティブ動的SQLはサポートされていません。クライアント側プログラムからのDBMS_SQLパッケージへのすべての呼び出しは、PL / SQLリモートプロシージャコール（RPC）に変換されます。これらの呼び出しは、変数をバインドしたり、変数を定義したり、ステートメントを実行したりする必要があるときに発生します。

DESCRIBEのサポート

DBMS_SQLパッケージのDESCRIBE_COLUMNSプロシージャは、次の目的で使用できます。 DBMS_SQLで開いて解析したカーソルの列を記述します。機能は、SQL * PlusのDESCRIBEコマンドに似ています。ネイティブ動的SQLには、DESCRIBE機能がありません。

バルク動的SQLのサポート

バルクSQLは、単一のDMLステートメントで複数行のデータを処理する機能です。バルクSQLは、SQLとホスト言語間のコンテキスト切り替えの量を減らすことにより、パフォーマンスを向上させます。現在、DBMS_SQLパッケージはバルク動的SQLをサポートしています。

ネイティブ動的SQLのバルク操作は直接サポートされていませんが、ネイティブ動的バルクSQLをシミュレートできます。バルクSQLステートメントを「BEGIN … END」ブロックに配置し、ブロックを動的に実行することによるステートメント。この回避策により、ネイティブの動的SQLプログラム内でバルクSQLの利点を実現できます。たとえば、次のネイティブ動的SQLコードは、あるテーブルのename列を別のテーブルにコピーします。

RETURNING句を使用した複数行の更新と削除

DBMS_SQLパッケージは、複数の行を更新または削除するRETURNING句を含むステートメントをサポートします。ネイティブ動的SQLは、単一の行が返される場合にのみRETURNING句をサポートします。

32KBを超えるSQLステートメントのサポート

DBMS_SQLパッケージは32KBを超えるSQLステートメントをサポートします。ネイティブ動的SQLはそうではありません。

SQLステートメントの再利用

DBMS_SQLのPARSEプロシージャdiv>パッケージはSQLステートメントを1回解析します。最初の解析後、ステートメントはバインド引数の異なるセットで複数回使用できます。

対照的に、ネイティブ動的SQLは、ステートメントが使用されるたびに実行するSQLステートメントを準備します。ステートメントの準備には、通常、解析、最適化、および計画の生成が含まれます。使用するたびにステートメントを準備すると、パフォーマンスがわずかに低下します。ただし、Oracleの共有カーソルメカニズムはコストを最小限に抑え、ネイティブ動的SQLのパフォーマンス上の利点と比較した場合、パフォーマンスの低下は通常わずかです。

DBMS_SQLパッケージコードとネイティブ動的SQLコードの例

次の例は、DBMS_SQLパッケージとネイティブ動的SQLを使用して操作を完了するために必要なコードの違いを示しています。具体的には、次のタイプの例を示します。

• クエリ
• DML操作
• DML戻り操作

一般に、ネイティブ動的SQLコードはより読みやすくコンパクトになり、開発者の生産性を向上させることができます。

クエリの例

次の例には、1つのバインド変数（:jobname）および2つの選択列（enameおよびsal）：

stmt_str := "SELECT ename, sal FROM emp WHERE job = :jobname";

この例では、jを使用して従業員を照会しますempテーブルのjob列のobdescription SALESMAN。表8-2に、DBMS_SQLパッケージとネイティブ動的SQLを使用してこのクエリを実行するサンプルコードを示します。

表8-2DBMS_SQLパッケージとネイティブ動的SQLを使用したクエリ

DMLの例

次の例には、3列の表の動的なINSERTステートメントが含まれています。

stmt_str := "INSERT INTO dept_new VALUES (:deptno, :dname, :loc)";

この例では、新しい行を挿入します。列値がPL / SQL変数deptnumber、deptname、およびlocation。表8-3に、DBMS_SQLパッケージとネイティブ動的SQLを使用してこのDML操作を実行するサンプルコードを示します。

表8-3DBMS_SQLパッケージを使用したDML操作とネイティブ動的SQL

DML戻り例

次の例には、動的UPDATE部門番号（deptnumber）と新しい場所（location）が与えられたときに部門の場所を更新し、その後返すステートメント部門名：

stmt_str := "UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname";

この例では、列値がPL / SQL変数、deptname、およびlocation。表8-4に、DBMS_SQLパッケージとネイティブ動的SQLを使用してこのDML戻り操作を実行するサンプルコードを示します。

表8-4DBMS_SQLを使用したDML戻り操作パッケージおよびネイティブ動的SQL

PL / SQL以外のアプリケーション開発言語

これまでのところ、この章での説明は次のとおりです。動的SQLのPL / SQLサポートについて。ただし、他のアプリケーション開発言語を使用して、動的SQLを使用するプログラムを実装できます。これらのアプリケーション開発言語には、C / C ++、COBOL、およびJavaが含まれます。

C / C ++を使用する場合は、Oracle Call Interface（OCI）で動的SQLを使用するアプリケーションを開発するか、Pro * C / C ++プリコンパイラを使用して動的SQL拡張機能をCに追加できます。コード。同様に、COBOLを使用する場合は、Pro * COBOLプリコンパイラーを使用して、動的SQL拡張機能をCOBOLコードに追加できます。 Javaを使用する場合は、JDBCで動的SQLを使用するアプリケーションを開発できます。

これまで、PL / SQLアプリケーションで動的SQLを使用する唯一の方法は、パッケージ。このパッケージの使用には、パフォーマンスの問題など、いくつかの制限があります。その結果、アプリケーション開発者は、動的SQLを実装するために上記のPL / SQLの代替手段の1つを使用した可能性があります。ただし、PL / SQLにネイティブ動的SQLが導入されたことにより、動的SQLにPL / SQLを使用する際の多くの欠点が解消されました。

OCIを使用するアプリケーションの場合、Pro * C / C ++、または動的SQL実行用のPro * COBOLでは、動的SQL操作を実行するために必要なネットワークラウンドトリップがパフォーマンスを低下させる可能性があります。これらのアプリケーションは通常クライアント上に存在するため、動的SQL操作を完了するにはより多くのネットワーク呼び出しが必要です。このタイプのアプリケーションを使用している場合は、動的SQL機能を、ネイティブ動的SQLを使用するPL / SQLのストアドプロシージャおよびストアド関数に移動することを検討してください。これを行うと、ストアドプロシージャがサーバー上に存在できるため、アプリケーションのパフォーマンスが向上し、ネットワークのオーバーヘッドがなくなる可能性があります。その後、アプリケーションからPL / SQLストアドプロシージャとストアド関数を呼び出すことができます。