PL/SQLプログラムでのSQLの使用

PL/SQLは、SQL言語を通じてTimesTenデータベースと密接に統合されています。

この項では、PL/SQLにおける次のSQL機能の使用について説明します。

• 問合せおよびDML文に使用するPL/SQLの静的SQL

• PL/SQLでの動的SQL (EXECUTE IMMEDIATE文)

• FORALL操作およびBULK COLLECT操作

• RETURNING INTO句

• ラージ・オブジェクト(LOB)

問合せおよびDML文に使用するPL/SQLの静的SQL

PL/SQL内から、次の文を静的SQLとして実行できます。

• DML文: INSERT、UPDATE、DELETEおよびMERGE

• 問合せ: SELECT

• トランザクション制御: COMMITおよびROLLBACK

ノート:

• PL/SQLでDDL文を実行するには、動的SQLを使用する必要があります。次の「PL/SQLでの動的SQL (EXECUTE IMMEDIATE文)」の項を参照してください。

• TimesTenのトランザクション動作とOracle Databaseの動作の違いについて詳しくは、「TimesTenにおける相違点: トランザクション動作」を参照してください。

これらのSQL文については、『Oracle TimesTen In-Memory Database SQLリファレンス』のSQL文を参照してください。

次の例は、問合せの実行方法を示しています。PL/SQLでTimesTen SQLを使用する他の例については、「PL/SQLでのTimesTen SQLの使用例」を参照してください。

単一行のデータを取得するには、SELECT... INTO文を使用します。1行も返さないか、または複数の行を返す問合せに対して、TimesTenはエラーを返します。この例では、HRスキーマのemployees表から、employee_id=100の従業員のhire_dateおよびsalaryを取得します。

Command> run selectinto.sql
 
DECLARE
   v_emp_hiredate employees.hire_date%TYPE;
   v_emp_salary   employees.salary%TYPE;

BEGIN
   SELECT hire_date, salary
   INTO   v_emp_hiredate, v_emp_salary
   FROM   employees
   WHERE  employee_id = 100;
   DBMS_OUTPUT.PUT_LINE(v_emp_hiredate || ' ' || v_emp_salary);
END;
/
 
1987-06-17 24000
 
PL/SQL procedure successfully completed.

PL/SQLでの動的SQL (EXECUTE IMMEDIATE文)

ネイティブの動的SQLを使用して、EXECUTE IMMEDIATE文により、複数の操作を実行できます。

• INSERT、UPDATEまたはDELETEなどのDML文を実行します。

• CREATEまたはALTERなどのDDL文を実行します。たとえば、ALTER SESSIONを使用して、PL/SQL初期接続属性を変更できます。

• PL/SQL無名ブロックを実行します。

• PL/SQLストアド・プロシージャまたはファンクションをコールします。

• TimesTen組込みプロシージャをコールする場合。(『Oracle TimesTen In-Memory Databaseリファレンス』の組込みプロシージャを参照してください。)

1つの使用例として、実行時までSQL文の完全なテキストがわからない場合をあげることができます。たとえば、コンパイル時に、SELECT文のWHERE句で使用する列の名前がわからないことがあります。このような場合は、EXECUTE IMMEDIATE文を使用できます。

別の使用例として、DDLの使用が挙げられますが、DDLはPL/SQL内からは静的SQLで実行できません。

結果セットを戻すTimesTen組込みプロシージャをコールするには、レコード型を作成し、BULK COLLECTを使用したEXECUTE IMMEDIATEを使用して、結果を配列にフェッチします。

『Oracle Database PL/SQL言語リファレンス』のEXECUTE IMMEDIATE文を参照してください。

ノート:

• 「TimesTenにおける相違点: トランザクション動作」を参照してください。

• DDL文を解析してプロシージャまたはパッケージを削除するため、そのプロシージャまたはそのパッケージ内のプロシージャが使用中の場合、タイムアウトが発生します。プロシージャをコールすると、実行がユーザー側に戻るまで、そのプロシージャは使用中であるとみなされます。このようなデッドロックは、すぐにタイムアウトになります。

• また、動的SQLにDBMS_SQLパッケージを使用することもできます。「DBMS_SQL」を参照してください。

次に、EXECUTE IMMEDIATEの独立した簡単な例を示します。その他の例は、「EXECUTE IMMEDIATEを使用する例」を参照してください。

表を作成し、PL/SQLブロック内でその表に対してDML文を実行します(USING句で入力パラメータを指定します)。次に、表を選択して結果を表示します。

Command> create table t(i int);
Command> declare
            i number := 1;
         begin
            execute immediate 'begin insert into t values(:j);end;' using i;
         end;
         /
 
PL/SQL procedure successfully completed.
 
Command> select * from t;
< 1 >
1 row found.

PL/SQLプロシージャfooを作成したら、PL/SQLブロックでそのプロシージャを実行します(USING句で入力パラメータを指定します)。

Command> create or replace procedure foo(message varchar2) is
         begin
            dbms_output.put_line(message);
         end;
         /
 
Procedure created.
 
Command> begin
            execute immediate 'begin foo(:b);end;' using 'hello';
         end;
         /
hello
 
PL/SQL procedure successfully completed.

PL/SQLプロシージャmyprintを作成したら、CALL文でそのプロシージャを実行します(USING句で入力パラメータを指定します)。

Command> declare
            a number := 1;
         begin
            execute immediate 'call myprint(:b)' using a;
         end;
         /
myprint procedure got number 1
 
PL/SQL procedure successfully completed.

通常、EXECUTE IMMEDIATEを使用して実行するコードは、Oracle Databaseの場合と同様、外部のPL/SQLブロックと同じ環境を共有します。特に次の点に注意してください。(これらは、EXECUTE IMMEDIATEだけでなく、DBMS_SQLを使用する場合にも該当します。)

• EXECUTE IMMEDIATEで実行されるSQLおよびPL/SQLは、外部のブロックと同じトランザクションで実行されます。

• EXECUTE IMMEDIATE文の実行中に発生した例外は、外部のブロックに伝播されます。そのため、EXECUTE IMMEDIATE文の実行時に発生するエラー・スタックに関するエラーは、外部のブロック内に表示されます。これは、DBMS_UTILITY.FORMAT_ERROR_STACKなどのプロシージャで便利です。

• EXECUTE IMMEDIATE文でPL/SQLブロックを実行する前に発生したエラー・スタックに関するエラーは、たとえばDBMS_UTILITY.FORMAT_ERROR_STACKを使用してそのブロック内に表示されます。

• EXECUTE IMMEDIATE文を実行する実行環境は、外部のブロックの場合と同じです。PL/SQLおよびTimesTenのパラメータ、REF CURSORの状態、およびEXECUTE IMMEDIATE文からのパッケージ状態は、外部のブロック内に表示されます。

FORALL操作およびBULK COLLECT操作

バルク・バインドは、PL/SQLからSQL文を実行する場合に使用される強力な機能であり、SQLとPL/SQL間での大量データの移動に使用されます。(アプリケーション・プログラムからPL/SQLへのパラメータのバインドとは異なります。) バルク・バインドでは、ループを使用してFETCH、INSERT、UPDATE、およびDELETE操作を複数回実行するのではなく、単一の操作で値の配列をバインドします。TimesTenではバルク・バインドがサポートされており、これにより、パフォーマンスを大幅に向上できます。

FORALL文を使用して入力コレクションをバルク・バインドしてから、SQLエンジンに送信します。BULK COLLECTを使用して、SQLから結果のバッチを取得します。VARRAY、ネストした表、連想配列(索引付き表)などの任意のPL/SQLコレクション型に、バルク・コレクトできます。コレクションに関する追加情報は、「PL/SQLでのコレクションの使用」を参照してください。

FORALL文では%BULK_EXCEPTIONSカーソル属性およびSAVE EXCEPTIONS句を使用できます。SAVE EXCEPTIONSを使用すると、UPDATE、INSERTまたはDELETE文で例外(制約エラーなど)が発生した後でも実行を続行できます。例外は配列に収集され、文の実行後に%BULK_EXCEPTIONSを使用して確認できます。SAVE EXCEPTIONSを使用すると、FORALL文の実行中に例外が発生した場合でも、コレクション内のすべての行が処理されます。文が終了するとエラーが発行され、1つ以上の例外が発生したことが示されます。SAVE EXCEPTIONSを使用しなかった場合、FORALL文の実行中に例外が発行されると、文は即時に例外を戻し、その他の行は処理されません。

『Oracle Database PL/SQL言語リファレンス』のFORALL文とBULK COLLECT句の併用を参照してください。

次の例では、一括バインディングとFORALL文の基本的な使用方法を示し、IDが100、102、104または110の従業員の給与を増やします。FORALL文でコレクションをバルク・バインドします。バルク・バインドの例および詳細は、「FORALLおよびBULK COLLECTを使用する例」を参照してください。

Command> CREATE OR REPLACE PROCEDURE raise_salary (p_percent NUMBER) IS
           TYPE numlist_type IS TABLE OF NUMBER
            INDEX BY BINARY_INTEGER;
           v_id  numlist_type;  -- collection
         BEGIN
           v_id(1) := 100; v_id(2) := 102; v_id (3) := 104; v_id (4) := 110;
           -- bulk-bind the associative array
           FORALL i IN v_id.FIRST .. v_id.LAST
            UPDATE employees
             SET salary = (1 + p_percent/100) * salary
             WHERE employee_id = v_id (i);
         END;
         /
 
Procedure created.

raise_salaryプロシージャを実行する前に給与を検索します。

Command> SELECT salary FROM employees WHERE employee_id = 100 OR employee_id = 
102 OR employee_id = 104 OR employee_id = 100;
< 24000 >
< 17000 >
< 6000 >
3 rows found.

プロシージャを実行して、結果を確認します。

Command> EXECUTE raise_salary (10);
 
PL/SQL procedure successfully completed.
 
Command> SELECT salary FROM employees WHERE employee_id = 100 or employee_id = 
102 OR employee_id = 104 OR employee_id = 100;
< 26400 >
< 18700 >
< 6600 >
3 rows found.

RETURNING INTO句

RETURNING INTO句(DML RETURNINGと呼ばれる場合もあります)をINSERT、UPDATEまたはDELETE文で使用すると、処理の影響を受けた行の(オプションでROWIDを含む)特定の列または式を返すことができます。 これにより、影響を確認したり、挿入または更新後のROWIDが必要な場合などに、後続のSELECT文および個々のラウンドトリップが不要になります。

RETURNING INTO句は、動的SQL(EXECUTE IMMEDIATEを使用)または静的SQLのいずれかで使用できます。

PL/SQL BULK COLLECT機能を使用すると、この句は、単一の行から一連のパラメータまたはレコードのいずれかにアイテムを返すことも、複数の行からVARRAY、ネストした表、または連想配列(索引付き表)などのPL/SQLコレクションに列を返すこともできます。句のINTO部分のパラメータには、入力/出力ではなく、出力のみを指定する必要があります。コレクションについては、「PL/SQLでのコレクションの使用」を参照してください。BULK COLLECTについては、「FORALL操作およびBULK COLLECT操作」と「FORALLおよびBULK COLLECTを使用する例」を参照してください。

TimesTenでのRETURNING INTO句のSQL構文および制限事項については、『Oracle TimesTen In-Memory Database SQLリファレンス』のINSERT、UPDATEおよびDELETEに関する説明を参照してください。

「RETURNING INTOを使用する例」も参照してください。

DML RETURNINGに関する追加情報は、『Oracle Database PL/SQL言語リファレンス』のRETURNING INTO句を参照してください。

ラージ・オブジェクト(LOB)

TimesTen ClassicではLOB(ラージ・オブジェクト)がサポートされています。これには、CLOB(文字LOB)、NCLOB(各国語LOB)、およびBLOB(バイナリLOB)などがあります。

PL/SQL言語機能は、特に記述がないかぎり、Oracle Databaseと同様にTimesTenでもLOBをサポートされています。

この項では、LOBの簡単な概要とPL/SQLでの使用方法に関して、次の内容について説明します。

• LOBについて

• LOBロケータ

• 一時LOB

• TimesTenにおけるLOBとOracle DatabaseにおけるLOBの相違点

• LOBの使用

• LOBに関するPL/SQLパッケージのサポート

• パススルーLOB

ノート:

TimesTen Scaleoutでは、LOBをデータベース列としてではなくPL/SQL変数としてサポートしています。

次の情報も参照できます。

• TimesTenでのLOBに関する追加情報は、『Oracle TimesTen In-Memory Database SQLリファレンス』のLOBデータ型を参照してください

• LOBを使用したプログラミングに関する、TimesTen機能に特化されない一般的な情報については、Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド

LOBについて

LOBには、ラージ・バイナリ・オブジェクト(BLOB)または文字オブジェクト(CLOBまたはNCLOB)があります。TimesTenでは、BLOBは最大16MB、CLOBまたはNCLOBのサイズは最大4MBです。TimesTenのLOBには、特に記述がないかぎり、基本的にOracle Databaseと同じ機能があります。

「TimesTenにおけるLOBとOracle DatabaseにおけるLOBの相違点」を参照してください。

LOBは、永続的または一時的のいずれかに設定できます。永続LOBは、データベースのLOB列に存在します。一時LOBはアプリケーション内のみに存在します。

LOBロケータ

PL/SQLでは、LOBは、LOBロケータおよびLOB値で構成されています。ロケータは、値へのハンドルとして機能する不透明構造です。LOBをパラメータとして渡すなどの操作で、アプリケーションがLOBを使用する場合、それは実際の値を渡すのではなく、ロケータを渡すことになります。

ヒント:

LOBロケータを使用するAPIを介ししてLOBを操作すると、TimesTen一時領域を使用することになります。このような操作を非常に多くの回数実行すると、TimesTen一時データ領域サイズの拡大が必要になる場合があります。『Oracle TimesTen In-Memory Databaseリファレンス』のTempSizeを参照してください。

LOBを更新するには、トランザクションで、LOBを含む行が排他的にロックされている必要があります。これを行うには、SELECT ... FOR UPDATE文でLOBを選択します。その結果、ロケータは書込み可能になります。SELECT文の場合、ロケータは読取り専用です。読取り専用および書込み可能ロケータは次のように動作します。

• 読取り専用ロケータには読取り一貫性があり、そのロケータが存続する間は、LOBが選択された時点のコンテンツのみが表示されることを意味します。これには、LOBが選択される前に同じトランザクション内でLOBに対して行われた、コミットされていない更新も含まれることに注意してください。

• 書込み可能なロケータは、そのロケータを介して書込みが行われるたびに、データベースの最新のデータで更新されます。そのため、各書込みはLOBの最新データに対して行われ、これには他のロケータを介して行われた更新内容も含まれます。

次の例で、同一のLOBに対する2つの書込み可能なロケータの動作を説明します。

1. LOB列に、"XY"が含まれています。

2. 更新用にロケータL1を選択します。

3. 更新用にロケータL2を選択します。

4. L1を使用してオフセット1で"Z"を書き込みます。

5. ロケータL1を使用して読み取ります。これにより、"ZY"が戻されます。

6. ロケータL2を使用して読み取ります。L2は、書込みに使用されるまで読取り一貫性を保持しているため、これにより"XY"が戻されます。

7. L2を使用してオフセット2で"W"を書き込みます。

8. ロケータL2を使用して読み取ります。これにより、"ZW"が戻されます。このロケータは、前のステップの書込みが行われる前に、最新データ("ZY")で更新されていたことになります。

一時LOB

PL/SQLブロックは、それ自身で使用する一時LOBを明示的に作成することができます。TimesTenでは、このようなLOBの存続期間は、(TimesTenにおけるLOBロケータの存続期間と同様)そのLOBが作成されたトランザクションの終了時を超えて延長されることはありません。

一時LOBは、TimesTenで暗黙的に作成されることもあります。たとえば、SELECT文によって追加の文字列と連結されたLOBが選択される場合、連結データを含むようTimesTenでは一時LOBを暗黙的に作成します。一時LOBはサーバー側のオブジェクトであることに注意してください。TimesTenには、クライアント側のLOBという概念はありません。

一時LOBは、TimesTen一時データ領域に格納されます。

一時LOBの作成方法については、『Oracle TimesTen In-Memory Database PL/SQLパッケージ・リファレンス』のCREATETEMPORARYプロシージャを参照してください。

TimesTenにおけるLOBとOracle DatabaseにおけるLOBの相違点

TimesTenでのLOB実装とOracle Databaseでの実装の主な違いを示します。

次の点に注意してください。

• TimesTenのLOB実装とOracle Databaseでの実装との主な違いは、TimesTenの場合、LOBロケータはトランザクションが終了すると無効になる点です。すべてのLOBロケータは、明示的か暗黙的かにかかわらず、コミットまたはロールバック後に無効化されます。これにはあらゆるDDL文の後が含まれます。

• TimesTenは、BFILE、SecureFiles、LOBの配列の読取りおよび書込み、またはLOBのコールバック・ファンクションをサポートしていません。

• TimesTenでは、DBMS_LOB FRAGMENTプロシージャはサポートされていないため、データをLOBの中間に書き込むには、以前のデータを上書きするしかありません。LOBの中間にデータを挿入し、それに応じて以前のデータをLOB内のその時点よりも上位に移動する機能はありません。同様に、TimesTenでは、LOBの中間からデータを削除するには、以前のデータをゼロまたはNullデータで上書きする方法しかありません。LOBの中間からデータを削除し、それに応じて以前のデータをLOB内のその時点よりも下位に移動する機能はありません。どちらの場合にも、TimesTenではLOBのサイズは変化しませんが、指定のオフセットから、LOBで利用できる容量が書き込むデータよりも少ない場合は例外です。(Oracle Databaseでは、上書きしてLOBのサイズを変更しないモード、または挿入するか削除してLOBのサイズを変更するモードの機能があります。)

• TimesTenは、LOBの配列のバインドをサポートしていません。

• TimesTenは、LOBのバッチ処理をサポートしていません。

• BLOBに関して、TimesTenの16進リテラルの使用法に違いがあります。『Oracle TimesTen In-Memory Database SQLリファレンス』の定数でHexadecimalLiteralの説明を参照してください。

LOBの使用

次では、CLOBの基本的な使用方法について説明します。次のように、BLOB列(ここでは使用しません)およびCLOB列を含むように定義され、移入された表を想定します。

Command> create table t1 (a int, b blob, c clob);
Command> insert into t1(a,b,c) values(1, 0x123451234554321, 'abcde');
1 row inserted.
Command> commit;

次のように、表からCLOBを選択して表示します。

Command> declare
            myclob clob;
         begin
            select c into myclob from t1 where a=1;
            dbms_output.put_line('CLOB selected from table t1 is: ' || myclob);
         end;
         /
CLOB selected from table t1 is: abcde
 
PL/SQL procedure successfully completed.

次では、commit文でトランザクションを終了した後に一時CLOBを再度表示してみると、その時点でLOBロケータが無効になっていることが示されています。

Command> declare
            myclob clob;
         begin
            select c into myclob from t1 where a=1;
            dbms_output.put_line('CLOB selected from table t1 is: ' || myclob);
            commit;
            dbms_output.put_line('CLOB after end of transaction is: ' || myclob);
         end;
         /
 1806: invalid LOB locator specified
 8507: ORA-06512: at line 8
CLOB selected from table t1 is: abcde
The command failed.

LOBに関するPL/SQLパッケージのサポート

TimesTenは、LOBデータを操作するためのDBMS_LOBパッケージのサブプログラムをサポートしています。

これらのサブプログラムのリストと説明は、このドキュメント内の「DBMS_LOB」を参照してください。『Oracle TimesTen In-Memory Database PL/SQLパッケージ・リファレンス』のDBMS_LOBを参照してください。

パススルーLOB

パススルーLOB (TimesTenを介してアクセスする、Oracle Database内のLOB)は、TimesTenのLOBとして公開され、TimesTenのLOBとほぼ同様にTimesTenでサポートされています。

次の点に注意してください:

• TimesTen LOBのサイズ制限は、パススルーLOBの記憶域には適用されませんが、バインディングには適用されます。また、DBMS_LOB.COPYなどで、パススルーLOBがTimesTen LOBにコピーされる場合、そのコピーにもサイズ制限が適用されます。

  パススルーLOBのサイズがTimesTen LOBのサイズ制限より大きい場合、パススルーLOBをTimesTen LOBにコピーしようとするとエラーが発生します。

• TimesTenローカルLOBと同様、パススルーLOBのロケータは、トランザクションが終了すると無効になります。

「DBMS_LOB」を参照してください。