8動的SQLステートメントのコーディング

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

Oracleには、動的SQLをPL/SQLアプリケーションに実装するための二つの方法があります。

• 動的SQL文をPL/SQLブロックに直接配置するネイティブ動的SQL。
• DBMS_SQLパッケージ内のプロシージャを呼び出します。

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

• “動的SQLとは何ですか？”
• “なぜ動的SQLを使用するのですか？,”
• “ネイティブ動的SQLを使用した動的SQLシナリオ”
• “ネイティブ動的SQLとDBMS_SQLパッケージの選択”
• “PL/SQL以外の言語での動的SQLの使用”
• “SQL INSERT文およびUPDATE文でのPL/SQLレコードの使用”

DBMS_SQLパッケージの詳細は、Oracle9iが提供する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を使用して、実行時にテーブル名を指定できます。

ユーザーが選択可能な並べ替え順序で複雑なクエリを実行することもできます。, 異なるORDER BY句でクエリを二度コーディングする代わりに、指定されたORDER BY句を含めるようにクエリを動的に構築できます。

動的SQLプログラムは、再コンパイルすることなく、データ定義の変更を処理できます。 これにより、動的SQLは静的SQLよりもはるかに柔軟になります。 動的SQLでは、さまざまな環境に容易に適応できるため、再利用可能なコードを記述できます。.,動的SQLでは、純粋に静的なSQLプログラムではサポートされていないデータ定義言語(DDL)文およびその他のSQL文を実行することもできます。

なぜ動的SQLを使うのですか？

静的SQLが実行する操作をサポートしていない場合、またはPL/SQLプロシージャによって実行される必要がある正確なSQL文がわからない場合は、動的SQL これらのSQL文は、ユーザー入力に依存するか、プログラムによって実行される処理作業に依存する可能性があります。,DL and SCL Statements in PL/SQL

In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:

• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
• Session control language (SCL) statements, such as ALTER SESSION and SET ROLE
  
  See Also:

  Oracle9i SQL Reference for information about DDL and SCL statements.,

また、TABLE句を使用できるのは、SELECTステートメントでのみ、動的SQLを介して使用できます。 たとえば、次のPL/SQLブロックには、SELECT文が含まれています。TABLE句とネイティブ動的SQLを使用します。

動的問合せの実行

動的問合せを実行するアプリケーションを作成するには、動的SQLを使用できますが、実行時まで完全なテキストがわからない動的問合せを実行するアプリケーションを作成することができます。, ユーザーが実行時にクエリ検索またはソート条件を入力または選択できるアプリケーション

実行時にオプティマイザヒントを入力または選択できるアプリケーション

テーブルのデータ定義が絶えず変化しているデータベースをクエリするアプリケーション

新しいテーブルが頻繁に作成されるデータベースをクエリするアプリケーション

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

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

多くの種類のアプリケーションでは、定期的に生成されるデータと対話する必要 たとえば、コンパイル時にテーブル定義を知っていても、テーブルの名前は知らない場合があります。

動的SQLは、実行時までテーブル名を指定するのを待つことができるため、この問題を解決できます。 たとえば、”動的SQLとは何ですか？”、新しいテーブルは四半期ごとに生成され、これらのテーブルは常に同じ定義を持ちます。, 次のような動的SQL問合せを使用して、実行時にテーブルの名前をユーザーに指定させることができます。

動的に実行を最適化する

動的SQLを使用して、ヒントを動的にSQLステートメントに連結することによって実行を最適化する方法でSQLステートメントを構築することができます。 ことができます変更をヒントに基づく現在のデータベースの統計を必要とせず再編簒.,

たとえば、次のプロシージャでは、a_hintという変数を使用して、SELECTステートメントにヒントオプションを渡すことができます。

この例では、ユーザーはa_hintに次のいずれかの値を渡すことができます。

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'またはその他の有効なヒントオプション。 ヒントの使用の詳細については、”Oracle9iデータベース-パフォーマンス-ガイドおよびリファレンス”も参照してください。,

動的PL/SQLブロックの実行

EXECUTEIMMEDIATE匿名PL/SQLブロックを実行するには、EXECUTE文を使用できます。 実行時にブロックの内容を構築することにより、柔軟性を追加できます。

たとえば、ythroughthroughouがイベント番号を受け取り、そのイベントのハンドラにディスパッチするアプリケーションを作成するとします。 ハンドラーの名前は、EVENT_HANDLER_event_numの形式です。event_numはイベントの番号です。, このコードでは、適切なハンドラーを静的呼び出しすることによって各イベントを処理します。 このコードは拡張可能なディスパッチャコードに更新されなければならないためのハンドラに新しいイベントが追加されます。,

ネイティブ動的SQLを使用すると、次のようなより小さく柔軟なイベントディスパッチャを記述できます。

Invoker-Rightsを使用した動的操作の実行

動的SQLでinvoker-rights機能を使用すると、呼び出し元の権限およびスキーマの下で動的SQLステートメントを発行するアプリケーションを構築できます。 呼び出し元の権限と動的SQLの二つの機能により、呼び出し元のデータとモジュールを操作してアクセスできる再利用可能なアプリケーションサブコンポー,呼び出し元権限およびネイティブ動的SQLの使用については、”PL/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操作について説明します。

ネイティブ動的SQLを使用したDML操作のサンプル

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

ネイティブ動的SQLを使用したDDL操作のサンプル

EXECUTE IMMEDIATEステートメントは、DDL操作を実行できます。,

ネイティブ動的SQLを使用した単一行クエリのサンプル

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

ネイティブ動的SQLを使用した複数行クエリのサンプル

OPEN-FOR、FETCH、およびCLOSEステートメントは、動的な複数行クエリを実行できます。,

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

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

To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.

DBMS_SQLパッケージは、SQL文を動的に実行するAPIを提供するPL/SQLライブラリです。 Div id=”1bd158c30b”>パッケージには、カーソルを開いたり、カーソルを解析したり、バインドを提供したりするプロシージャがあります。 DBMS_SQLパッケージを使用するプログラムは、動的SQL操作を実行するためにこのパッケージを呼び出します。

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

ネイティブ動的SQLの利点

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

ネイティブ動的SQLは使いやすい

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

DBMS_SQLパッケージでは、多くのプロシージャと関数を厳密な順序で呼び出す必要があり、単純な操作でも多くのコードが必要になります。 させないように指定できますこの複雑性をネイティブの動的SQLです。

表8-1に、DBMS_SQLパッケージとネイティブ動的SQLを使用して同じ操作を実行するために必要なコード量の違いを示します。,

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

ネイティブ動的SQLはDBMS_SQL

PL/SQLのネイティブ動的SQLは、pl/SQLインタプリタが組み込みのサポートを持っているため、静的SQLのパフォーマンスと同等のパフォーマンスを発揮します。 ネイティブ動的SQLを使用するプログラムは、DBMS_SQLパッケージを使用するプログラムよりもはるかに高速です。 通常、ネイティブ動的SQLステートメントは、同等のDBMS_SQL呼び出しの1.5～3倍のパフォーマンスを発揮します。 （ご連によって異なります。,)

ネイティブ動的SQLは、ステートメントの準備、バインド、および実行ステップを単一の操作にバンドルし、データコピーおよびプロシージャコールのオーバーヘッドを

DBMS_SQLパッケージは手続き型APIに基づいており、手続き呼び出しとデータコピーのオーバーヘッドが高くなります。 変数をバインドするたびに、DBMS_SQLパッケージは、実行中に使用するためにPL/SQLバインド変数をそのスペースにコピーします。, フェッチを実行するたびに、データはDBMS_SQLパッケージによって管理されるスペースにコピーされ、フェッチされたデータは適切なPL/SQL変数に一度に

パフォーマンスのヒント:バインド変数の使用

ネイティブ動的SQLまたはDBMS_SQLパッケージを使用する場合、バインド変数を使用することでパフォーマンスを向上させることができます。,

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

個別のmy_deptno変数ごとに、新しいカーソルが作成され、リソースの競合 代わりに、bindmy_deptnoバインド変数として：

ここでは、同じカーソルがbindmy_deptnoの異なる値に再利用され、パフォーマンスとスケーラビリティ,

ネイティブ動的SQLはユーザー定義型をサポートします

ネイティブ動的SQLは、ユーザー定義オブジェクト、コレクション、REFsなどのユーザー定義型を含む、PL/SQLの静的SQLでサポートされるすべての型をサポートします。 Div id=”1bd158c30b”>パッケージは、これらのユーザー定義型をサポートしていません。

ネイティブ動的SQLはレコードへのフェッチをサポートしています

ネイティブ動的SQLと静的SQLの両方がレコードへのフェッチをサポートしていますが、DBMS_SQLパッケージはサポートしていません。 ネイティブ動的SQLでは、問合せの結果の行をPL/SQLレコードに直接フェッチできます。,

次の例では、クエリの行がemp_recレコードにフェッチされます。

DBMS_SQLパッケージの利点

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

DBMS_SQLはクライアント側プログラムでサポートされています。

DBMS_SQLパッケージは、クライアント側のプログラムでサポートされていますが、ネイティブ動的sqlはありません。, クライアント側プログラムからのDBMS_SQLパッケージの呼び出しはすべて、PL/SQL remote procedure call(RPC)に変換されます;これらの呼び出しは、変数のバインド、変数の定義、またはステートメントの実行が必要な場合に発生します。

DBMS_SQLはDESCRIBEをサポートしています

DESCRIBE_COLUMNSDBMS_SQLパッケージのDBMS_SQLで開かれ解析されたカーソルの列を記述するために使用できます。 この機能は、SQL*PlusのDESCRIBEコマンドに似ています。, ネイティブ動的SQLには、DESCRIBE機能がありません。

DBMS_SQLは、RETURNING句による複数行の更新および削除をサポートします

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

DBMS_SQL Supports SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

DBMS_SQL Lets You Reuse SQL Statements

The PARSE procedure in the DBMS_SQL package parses a SQL statement once., 最初の解析の後、異なるバインド引数のセットでステートメントを複数回使用することができます。

ネイティブ動的SQLは、ステートメントが使用されるたびにSQLステートメントを準備し、通常は解析、最適化、および計画生成が含まれます。 余分な準備操作によってパフォーマンスの低下は小さくなりますが、通常はネイティブ動的SQLのパフォーマンス上の利点によって減速が上回ります。,

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

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

• クエリ
• DML操作
• DML返す操作

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

動的SQLを使用したクエリ:例

次の例には、一つのバインド変数(:jobname)と二つの選択列(enameとsal)を持つ動的クエリステートメントが含まれています。

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

この例SALESMANjobempテーブルの列にジョブの説明SALESMANを持つ従業員に対するクエリ。 表8-2に、DBMS_SQLパッケージとネイティブ動的SQLを使用してこのクエリを実行するサンプルコードを示します。,

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

動的SQLを使用したDMLの実行例

次の例には、動的な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パッケージとネイティブ動的SQLを使用したDML操作

動的SQLを使用したRETURNING句を使用したDMLの実行例

次の例では、動的なUPDATEステートメントを使用して部門の場所を更新し、部門の名前を返します。

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

表8-4にサンプルコードを示しますこれにより、DBMS_SQLパッケージとネイティブ動的sqlの両方を使用してこの操作が実行されます。,

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

PL/SQL以外の言語での動的SQLの使用

この章では、動的SQLのPL/SQLサポートについて説明しますが、他の言語から動的SQLを呼び出すことができます。

• C/C++を使用する場合は、Oracle Call Interface(OCI)を使用して動的SQLを呼び出すか、Pro*C/C++プリコンパイラを使用して、動的SQL拡張を追加することができます。cコード。COBOLを使用する場合は、Pro*COBOLプリコンパイラを使用して、COBOLコードに動的SQL拡張を追加できます。,
• Javaを使用する場合は、JDBCで動的SQLを使用するアプリケーションを開発できます。OCI、Pro*C/C++またはPro*COBOLを使用して動的SQLを実行するアプリケーションがある場合は、PL/SQLストアド-プロシージャおよびファンクション内でネイティブ動的SQLに切り替えることを検討する必要があります。 クライアント側アプリケーションから動的SQL操作を実行するために必要なネットワークラウンドトリップ 保管手続きに住むことができ、サーバを排除し、ネットワークオーバーヘッド。, PL/SQLストアド-プロシージャおよびストアド-ファンクションは、OCI、Pro*C/C++またはPro*COBOLアプリケーションからコールできます。,Oracle Call Interface Programmer’S Guide
• Pro*C/C++プリコンパイラ-プログラマ-ガイド
• Pro*COBOLプリコンパイラ-プログラマ-ガイド
• Oracle9i Javaストアド-プロシージャ開発者ガイド

SQL INSERT文およびUPDATE文でのPL/SQLレコードの使用

表に行を挿入または更新するときに、pl/sqlレコードの各フィールドを列挙できますが、結果のコードは特に読み取りや保守が可能ではありません。, 代わりに、これらの文でPL/SQLレコードを直接使用できます。 最も便利な手法は、%ROWTYPE属性を使用してレコードを宣言し、SQLテーブルとまったく同じフィールドを持つようにすることです。この手法は、PL/SQL変数および型をSQL DML文とより密接に統合するのに役立ちますが、動的SQL文のバインド変数としてPL/SQLレコードを使用することはでき