パススルーSQL

パススルーSQL機能を使用すると、文を最初にOracle Databaseで解析せずに直接Oracle以外のシステムに送ることができます。

この機能は、Oracle以外のシステムでOracleに等価文のない文での操作が見込まれる場合に役立ちます。

DBMS_HS_PASSTHROUGHパッケージ
PL/SQLのDBMS_HS_PASSTHROUGHパッケージを使用すると、Oracle以外のシステムで直接パススルーSQL文を実行できます。
パススルーSQLの使用による影響
Oracle以外のシステムでトランザクションを暗黙的にコミットまたはロールバックするパススルーSQL文を実行すると、そのトランザクションに影響します。
パススルーSQL文の実行
この項では、DBMS_HS_PASSTHROUGHパッケージで提供されるファンクションとプロシージャについて説明します。これらを使用してパススルーSQL文を実行できます。

DBMS_HS_PASSTHROUGHパッケージ

PL/SQLのDBMS_HS_PASSTHROUGHパッケージを使用すると、Oracle以外のシステムで直接パススルーSQL文を実行できます。

このパッケージで実行される文は、標準のSQL文と同じトランザクションで実行されます。

DBMS_HS_PASSTHROUGHパッケージは仮想パッケージです。概念上は、Oracle以外のシステムに常駐します。ただし、実際には、このパッケージのコールは異機種間サービスにより仲介され、1つ以上の異機種間サービス・コールにマップされます。次に、これらの異機種間サービス・コールは、ドライバによりOracle以外のシステムのAPIにマップされます。クライアント・アプリケーションは、Oracle以外のシステムのストアド・プロシージャをコールする場合と同じ方法で、データベース・リンクを介してパッケージ内のプロシージャをコールします。異機種間サービスにより実行される特殊処理は、ユーザーに対して透過的です。

パススルーSQLの使用による影響

Oracle以外のシステムでトランザクションを暗黙的にコミットまたはロールバックするパススルーSQL文を実行すると、そのトランザクションに影響します。

たとえば、一部のシステムでは、データ定義言語(DDL)文を含むトランザクションが暗黙的にコミットされます。Oracle Databaseは迂回されるため、トランザクションがOracle以外のシステムにコミットされたことを認識しません。そのため、Oracle Databaseではトランザクションがコミットされない間に、Oracle以外のシステムでデータがコミットされる可能性があります。

Oracle Databaseでトランザクションがロールバックされると、Oracle DatabaseとOracle以外のシステム間でデータに一貫性がなくなる可能性があります。このような状況では、グローバルなデータ非一貫性が発生します。

アプリケーションで通常のCOMMIT文を実行すれば、Oracle DatabaseはOracle以外のシステムと分散トランザクションを調整できることに注意してください。パススルー機能を使用して実行される文は、分散トランザクションの一部となります。

親トピック: パススルーSQL

パススルーSQL文の実行

この項では、DBMS_HS_PASSTHROUGHパッケージで提供されるファンクションとプロシージャについて説明します。これらを使用してパススルーSQL文を実行できます。

表3-1 DBMS_HS_PASSTHROUGHのファンクションおよびプロシージャ

プロシージャ/ファンクション	説明
`OPEN_CURSOR`	カーソルのオープン。
`CLOSE_CURSOR`	カーソルのクローズ。
`PARSE`	文の解析。
`BIND_VARIABLE`	`IN`変数のバインド。
`BIND_OUT_VARIABLE`	`OUT`変数のバインド。
`BIND_INOUT_VARIABLE`	`IN OUT`変数のバインド。
`EXECUTE_NON_QUERY`	非問合せ文の実行。
`EXECUTE_IMMEDIATE`	バインド変数なしの非問合せ文の実行。
`FETCH_ROW`	問合せからの行のフェッチ。
`GET_VALUE`	`SELECT`文からの列値の取得、または`OUT`バインド・パラメータの取得。

非問合せの実行
非問合せには、INSERT、UPDATE、DELETEおよび他のDDLのタイプの文が含まれます。
問合せの実行
パススルーSQLを使用して問合せを実行します。

親トピック: パススルーSQL

非問合せの実行

非問合せには、INSERT、UPDATE、DELETEおよび他のDDLのタイプの文が含まれます。

非問合せ文を実行するには、EXECUTE_IMMEDIATEファンクションを使用します。たとえば、データベース・リンクsalesdbを使用してアクセスできるOracle以外のシステムでDDL文を実行するには、次のように入力します。

DECLARE
  num_rows INTEGER;

BEGIN
  num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@salesdb
            ('CREATE TABLE dept1 (n SMALLINT, loc CHARACTER(10))');
END;

変数num_rowsには、実行された文によって影響を受ける行数が割り当てられます。DDL文の場合は、0(ゼロ)が戻されます。EXECUTE_IMMEDIATEファンクションでは問合せを実行できず、バインド変数を使用できないことに注意してください。

バインド変数の概要
バインド変数を使用すると、同じSQL文に様々な値を指定して複数回使用できるため、SQL文に必要な解析回数が減少します。
INバインド変数
文でINバインド変数を指定する方法は、Oracle以外のシステムの構文により決定されます。
OUTバインド変数
Oracle以外のシステムはOUTバインド変数をサポートできます。
IN OUTバインド変数
1つのバインド変数でIN変数とOUT変数を兼ねることができます。

親トピック: パススルーSQL文の実行

バインド変数の概要

バインド変数を使用すると、同じSQL文に様々な値を指定して複数回使用できるため、SQL文に必要な解析回数が減少します。

たとえば、表に4行挿入する場合は、SQL文を1回解析し、行ごとにバインドして実行できます。1つのSQL文に0個以上のバインド変数を使用できます。

バインド変数を使用してパススルーSQL文を実行する手順は、次のとおりです。

カーソルをオープンします。
Oracle以外のシステムでSQL文を解析します。
変数をバインドします。
Oracle以外のシステムでSQL文を実行します。
カーソルをクローズします。

図3-1に、バインド変数を使用した非問合せ実行のフロー図を示します。

図3-1 非問合せ用パススルーSQLフロー図

「図3-1 非問合せ用パススルーSQLフロー図」の説明

親トピック: 非問合せの実行

INバインド変数

文でINバインド変数を指定する方法は、Oracle以外のシステムの構文により決定されます。

たとえば、Oracleシステムでは、先行コロンを使用してバインド変数を定義します。次に例を示します。

...
UPDATE emp
SET sal=sal*1.1
WHERE ename=:ename;
...

この文で、enameはバインド変数です。Oracle以外のシステムでは、バインド変数の指定に疑問符が必要になる場合があります。次に例を示します。

...
UPDATE emp
SET sal=sal*1.1
WHERE ename= ?;
...

バインド変数のステップでは、ホスト・プログラム変数(この場合はPL/SQL)をこれらの各バインド変数と位置的に関連付ける必要があります。たとえば、前述の文を実行するには、次のPL/SQLプログラムを使用します。

DECLARE
  c INTEGER;
  nr INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb;
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c,
        'UPDATE emp SET SAL=SAL*1.1 WHERE ename=?');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@salesdb(c,1,'JONES');
  nr:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@salesdb(c);
  DBMS_OUTPUT.PUT_LINE(nr||' rows updated');
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c);
END;

親トピック: 非問合せの実行

OUTバインド変数

Oracle以外のシステムはOUTバインド変数をサポートできます。

OUTバインド変数を使用すると、その値はSQL文の実行後まで認識されません。

OUTバインド変数はSQL文の実行後に移入されますが、Oracle以外のシステムでは、特定のバインド変数がOUTバインド変数であることをSQL文の実行前に認識する必要があります。バインド変数がOUTバインド変数であることを指定するには、BIND_OUT_VARIABLEプロシージャを使用します。

SQL文の実行後は、GET_VALUEプロシージャを使用してOUTバインド変数の値を取得できます。

親トピック: 非問合せの実行

IN OUTバインド変数

1つのバインド変数でIN変数とOUT変数を兼ねることができます。

つまり、SQL文の実行前にバインド変数の値がわかっている必要があります。また、SQL文の実行後に値を変更できます。

IN OUTバインド変数の場合は、BIND_INOUT_VARIABLEプロシージャを使用してSQL文の実行前に値を提供する必要があります。SQL文の実行後は、GET_VALUEプロシージャを使用してバインド変数の新規の値を取得する必要があります。

親トピック: 非問合せの実行

問合せの実行

パススルーSQLを使用して問合せを実行します。

問合せでは、非問合せとは異なり、SELECT文から結果セットが取得されます。結果セットは、カーソルの使用により取得されます。

図3-2に、パススルーSQL問合せのステップを示します。システムでSELECT文が解析された後、FETCH_ROWプロシージャで結果セットの各行を取得できます。行が取得された後、GET_VALUEプロシージャを使用してSELECT構文のリスト項目をプログラム変数に取り出します。すべての行が取得された後に、カーソルをクローズできます。

図3-2 問合せ用パススルーSQL

「図3-2 問合せ用パススルーSQL」の説明

すべての行を取得する必要はありません。カーソルのオープン後は、いつでもカーソルをクローズできます。

注意:

取得するのは一度に1行ですが、異機種間サービスでは、複数行をバッファに入れて1回のラウンドトリップでOracle以外のデータ・システムからフェッチすることで、Oracle DatabaseとOracle以外のシステム間のラウンドトリップが最適化されます。

次の例では、問合せが実行されます。

DECLARE
   val  VARCHAR2(100);
   c    INTEGER;
   nr   INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb; 
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c, 
    'select ENAME
     from   EMP
     where  DEPTNO=10');
  LOOP
    nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@salesdb(c);
    EXIT WHEN nr = 0;
    DBMS_HS_PASSTHROUGH.GET_VALUE@salesdb(c, 1, val);
    DBMS_OUTPUT.PUT_LINE(val);
  END LOOP;  
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c); 
END;

SELECT文の解析後は、FETCH_ROWファンクションが0(ゼロ)を戻すまで、ループ内で行がフェッチされて出力されます。

親トピック: パススルーSQL文の実行