ホスト配列

8.1 配列を使用する理由

配列を使用すると、プログラミングの所要時間が短縮され、パフォーマンスを改善できます。

配列により、単一のSQL文で配列全体を操作できます。このため、特にネットワーク環境では、Oracleの通信オーバーヘッドが大幅に軽減されます。実行時間の大部分は、ネットワーク上でクライアント・プログラムとサーバー・データベース間のラウンドトリップとに費やされます。配列を使用すると、ラウンドトリップが減少します。

たとえば、およそ300人の従業員に関する情報をEMPという表に挿入する必要があるとします。配列がないと、プログラムは300の個々のINSERT(各従業員に1つ)を実行する必要があります。配列を使用すれば、INSERTの実行は1回で済みます。

8.2 ホスト配列の宣言について

次の例では3つのホスト配列を宣言するとともに、それぞれ要素の最大数を50に設定しています。

char  emp_name[50][10]; 
int   emp_number[50]; 
float salary[50];

VARCHARの配列も有効です。次の宣言は、有効なホスト言語宣言です。

VARCHAR v_array[10][30];

8.2.1 制限事項(ホスト配列の宣言)

オブジェクト型を除き、ポインタのホスト配列は宣言できません。

文字配列(文字列)を除き、SQL文内で参照できるホスト配列は1次元に限定されます。したがって、次の例で宣言されている2次元配列は無効です。

int hi_lo_scores[25][25];   /* not allowed */

8.2.2 配列の最大サイズ

1つのSQL文で1回のフェッチ操作によってアクセスできる配列要素の最大数は、4GBです(使用するプラットフォームと使用可能なメモリーによってはそれ以上)。最大数を超える数のホスト表要素にアクセスすると、パラメータが範囲外であることを示すランタイム・エラーが表示されます。文が無名PL/SQLブロックの場合、アクセス可能な配列要素の数は、4GBをデータ型のサイズで割った数に制限されます。

8.3 SQL文での配列の使用方法について

ホスト配列は、INSERT文、UPDATE文、DELETE文では入力変数として、またSELECT文およびFETCH文のINTO句では出力変数として使用できます。

ホスト配列に使用される埋込みSQL構文は、単純ホスト変数に使用される埋込みSQL構文とほとんど同じです。ただし、オプションのFOR句で配列処理が制御できるという点に違いがあります。また、ホスト配列と単純ホスト変数を1つのSQL文で併用するときにも制限があります。

後続の項では、データ操作文でのホスト配列の使用方法を説明します。

8.3.1 ホスト配列の参照について

単一のSQL文で複数のホスト配列を使用する場合、要素の数は同じにする必要があります。同じでない場合には、プリコンパイル時に「配列サイズが一致しません」という警告メッセージが出ます。この警告を無視すると、プリコンパイラではSQL操作で最小数の要素が使用されます。

次の例では、INSERTされるのは25行のみです。

int    emp_number[50]; 
char   emp_name[50][10]; 
int    dept_number[25]; 
/* Populate host arrays here. */ 

EXEC SQL INSERT INTO emp (empno, ename, deptno) 
    VALUES (:emp_number, :emp_name, :dept_number);

SQL文のホスト配列に添字を付け、それをループで使用することでデータをINSERTまたはフェッチできます。たとえば、次のようなループを使用して、配列内の5番目の要素ごとにINSERTできます。

for (i = 0; i < 50; i += 5) 
    EXEC SQL INSERT INTO emp (empno, deptno) 
        VALUES (:emp_number[i], :dept_number[i]);

ただし、処理する必要のある配列要素が連続している場合は、ループでホスト配列を処理しないでください。単に、添字の付いていない配列名をSQL文で使用してください。要素数nのホスト配列を含むSQL文は、n個の異なるスカラー変数を持つ同じSQL文としてn回実行するのと同様に扱われます。

8.3.2 インジケータ配列の使用方法について

インジケータ配列は、NULLを割り当ててホスト配列を入力し、出力ホスト配列でNULLまたは切り捨てられた値(文字列のみ)を検出する場合に使用できます。次の例は、インジケータ配列でINSERTを行う方法を示しています。

int    emp_number[50]; 
int    dept_number[50]; 
float  commission[50]; 
short  comm_ind[50];       /* indicator array */ 

/* Populate the host and indicator arrays.  To insert a null 
   into the comm column, assign -1 to the appropriate 
   element in the indicator array. */ 
    EXEC SQL INSERT INTO emp (empno, deptno, comm) 
        VALUES (:emp_number, :dept_number, 
        :commission INDICATOR :comm_ind);

8.3.3 Oracle制限事項(ホスト配列用)

VALUES、SET、INTOまたはWHERE句では、スカラーのホスト変数とホスト配列を併用できません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。

ホスト配列は、UPDATE文またはDELETE文で CURRENT OF句とともに使用できません。

8.3.4 ANSIでの制限および要件

配列インタフェースは、ANSI/ISOの埋込みSQL規格に対するOracle拡張機能です。ただし、MODE=ANSIでプリコンパイルすると、配列のSELECTおよびFETCHは使用できます。必要であれば、FIPSフラガー・プリコンパイラ・オプションによって、配列を使用していることをフラグで示すことができます。

配列をSELECTおよびFETCHするときは、必ずインジケータ配列を使用します。このようにして、関連する出力ホスト配列内にNULLがあるかどうかをテストできます。

DBMS=V7またはDBMS=v8のときに、インジケータ配列に対応付けられていないホスト配列にNULL列値をSELECTまたはFETCHすると、Oracleは処理を停止し、sqlerrd[2]に処理済行数を設定しエラー・メッセージを出します。DBMS=V7またはDBMS=v8の場合、Oracleは切捨てをエラーとみなしません。

また、SELECTまたはFETCHの結果、NULLの使用によるORA-24347などの警告が発生した場合や、列にインジケータ配列がない場合には、Oracleは処理を停止します。

ノート:

SELECTまたはFETCHでは、すべての列に標識変数を使用します。インジケータのない列がある場合は、プリコンパイラ・オプションunsafe_null=yesをかわりに使用できます。

8.4 配列への選択について

ホスト配列はSELECT文内の出力変数として使用できます。SELECTによって戻される最大行数がわかっている場合、その数の要素でホスト配列を宣言してください。次の例では、3つのホスト配列へのデータを直接選択します。このSELECTで戻される行が50行以下であることがわかっているため、配列は要素数50で宣言します。

char   emp_name[50][20]; 
int    emp_number[50]; 
float  salary[50]; 
 
EXEC SQL SELECT ENAME, EMPNO, SAL 
    INTO :emp_name, :emp_number, :salary 
    FROM EMP 
    WHERE SAL > 1000;

この例ではSELECT文は50行まで戻します。選択される行数が49行以下の場合、または50行のみを取り出す場合はこの方法を使用します。ただし、選択される行数が51行以上の場合は、この方法ではすべての行を取り出せません。このSELECT文を再実行しても、他に選択対象の行があるとしても、最初の50行のみがまた戻されます。この場合は大きな配列を宣言するか、FETCH文で使用するカーソルを宣言する必要があります。

宣言した要素数を超える行数がSELECT INTO文によって戻されると、SELECT_ERROR=NOを指定していないかぎりエラー・メッセージが出されます。

8.4.1 カーソルのフェッチ

SELECTが戻す最大行数がわからない場合には、カーソルを宣言して、一括でフェッチできます。

ループ内でバッチ・フェッチを実行すると、多数の行を簡単に取り出せます。FETCHを実行するたびに、次に続く行の集合がカレント・アクティブ・セットから戻されます。次の例では、20行ずつまとめて行をフェッチします。

 
int   emp_number[20]; 
float salary[20]; 
 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT empno, sal FROM emp; 
 
EXEC SQL OPEN emp_cursor; 
 
EXEC SQL WHENEVER NOT FOUND do break; 
for (;;) 
{ 
    EXEC SQL FETCH emp_cursor 
        INTO :emp_number, :salary; 
    /* process batch of rows */ 
    ... 
} 
...

最後のフェッチで実際に戻された行数を必ずチェックして処理してください。

関連項目

フェッチされる行数

8.4.2 sqlca.sqlerrd[2]の使用方法について

INSERT文、UPDATE文、DELETE文およびSELECT INTO文の場合は、sqlca.sqlerrd[2]に処理済行数が記録されます。FETCH文の場合は、処理した行の累積数が記録されます。

FETCHでホスト配列を使用しているときに、その時点での最後のループで戻された行数を確認するには、sqlca.sqlerrd[2]の現在の値と(別の変数内に保存した)前回の値との差分をとります。次の例では、最後のフェッチで戻された行数を確認します。

int  emp_number[100]; 
char emp_name[100][20]; 
 
int rows_to_fetch, rows_before, rows_this_time; 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT empno, ename 
    FROM emp 
    WHERE deptno = 30; 
EXEC SQL OPEN emp_cursor; 
EXEC SQL WHENEVER NOT FOUND CONTINUE; 
/* initialize loop variables */ 
rows_to_fetch = 20;   /* number of rows in each "batch" */ 
rows_before = 0;      /* previous value of sqlerrd[2]  */ 
rows_this_time = 20; 
 
while (rows_this_time == rows_to_fetch) 
{ 
    EXEC SQL FOR :rows_to_fetch 
    FETCH emp_cursor 
        INTO :emp_number, :emp_name; 
    rows_this_time = sqlca.sqlerrd[2] - rows_before; 
    rows_before = sqlca.sqlerrd[2]; 
} 
...

配列の処理中にエラーが発生したときにもsqlca.sqlerrd[2]が役立ちます。処理はエラーが発生した行で停止するため、sqlerrd[2]には正常に処理された行数が格納されます。

8.4.3 フェッチされる行数

各FETCHが戻すのは、最大でも配列の全行数分までです。次のような場合は最大行数より少ない行が戻ります。

アクティブ・セットの最後に達したとき:「データが見つかりません。「データが見つかりません」というOracleエラー・コードがSQLCA内でSQLCODEに戻されます。たとえば、要素数100の配列に行をフェッチしたとき20行しか戻されなかった場合にこれが起こります。
残っているフェッチ対象の行が、一括フェッチの全行数より少ないとき。たとえば、要素数20の配列に70行をフェッチすると、3回目のFETCHの後にはフェッチ対象の行が10しか残っていないため、この状態が発生します。
行の処理中にエラーが検出されたとき。FETCHは失敗し、該当するOracleエラー・コードがSQLCODEに戻されます。

戻された行の累積数は、このガイドではsqlerrd[2]と記載しているSQLCA内のsqlerrdの3番目の要素に保存されます。これはオープン状態のすべてのカーソルに適用されます。次の例では、カーソルの状態がそれぞれ更新されている様子がわかります。

EXEC SQL OPEN cursor1; 
EXEC SQL OPEN cursor2; 
EXEC SQL FETCH cursor1 INTO :array_of_20; 
/* now running total in sqlerrd[2] is 20 */ 
EXEC SQL FETCH cursor2 INTO :array_of_30; 
/* now running total in sqlerrd[2] is 30, not 50 */ 
EXEC SQL FETCH cursor1 INTO :array_of_20; 
/* now running total in sqlerrd[2] is 40 (20 + 20) */ 
EXEC SQL FETCH cursor2 INTO :array_of_30; 
/* now running total in sqlerrd[2] is 60 (30 + 30) */

8.4.4 スクロール可能カーソルのフェッチ

ホスト配列はスクロール可能カーソルとともに使用することもできます。スクロール可能カーソルを使用する場合、sqlca.sqlerrd[2]は処理済最大(絶対)行数を表します。アプリケーションではスクロール可能モードでフェッチを任意の場所に配置できるため、この値が処理済行数の合計である必要はありません。

スクロール可能モードのFETCH文にホスト配列を使用している間は、sqlca.sqlerrd[2]の現在の値と前回の値との差分を取っても、その時点での最後のループで戻された行数を確認することはできません。アプリケーション・プログラムでは、FETCH LASTを実行して、結果セット内の合計行数を判断します。sqlca.sqlerrd[2]の値は、結果セット内の合計行数を示します。

関連項目

サンプル・プログラム: スクロール可能カーソルを使用するホスト配列

8.4.5 サンプル・プログラム3: ホスト配列

この項のデモ・プログラムでは、Pro*C/C++で問合せを作成するときの配列の使用方法を示しています。特に、SQLCA(sqlca.sqlerrd[2])の処理済行数に注目してください。このプログラムは、demoディレクトリのファイルsample3.pcとして、オンラインで使用可能です。

/*
 *  sample3.pc
 *  Host Arrays
 *
 *  This program connects to ORACLE, declares and opens a cursor,
 *  fetches in batches using arrays, and prints the results using
 *  the function print_rows().
 */

#include <stdio.h>
#include <string.h>

#include <sqlca.h>

#define NAME_LENGTH   20
#define ARRAY_LENGTH   5
/* Another way to connect. */
char *username = "SCOTT";
char *password = "TIGER";

/* Declare a host structure tag. */
struct
{
    int    emp_number[ARRAY_LENGTH];
    char   emp_name[ARRAY_LENGTH][NAME_LENGTH];
    float  salary[ARRAY_LENGTH];
} emp_rec;

/* Declare this program's functions. */
void print_rows();              /* produces program output */
void sql_error();          /* handles unrecoverable errors */


main()
{
    int  num_ret;               /* number of rows returned */
  
/* Connect to ORACLE. */
    EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:");

    EXEC SQL CONNECT :username IDENTIFIED BY :password;
    printf("\nConnected to ORACLE as user: %s\n", username);


    EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:");
/* Declare a cursor for the FETCH. */
    EXEC SQL DECLARE c1 CURSOR FOR
        SELECT empno, ename, sal FROM emp;

    EXEC SQL OPEN c1;

/* Initialize the number of rows. */
    num_ret = 0;

/* Array fetch loop - ends when NOT FOUND becomes true. */
    EXEC SQL WHENEVER NOT FOUND DO break;

    for (;;)
    {
        EXEC SQL FETCH c1 INTO :emp_rec;

/* Print however many rows were returned. */
        print_rows(sqlca.sqlerrd[2] - num_ret);
        num_ret = sqlca.sqlerrd[2];        /* Reset the number. */
    }
/* Print remaining rows from last fetch, if any. */
    if ((sqlca.sqlerrd[2] - num_ret) > 0)
        print_rows(sqlca.sqlerrd[2] - num_ret);

    EXEC SQL CLOSE c1;
    printf("\nAu revoir.\n\n\n");

/* Disconnect from the database. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}


void
print_rows(n)
int n;
{
    int i;

    printf("\nNumber   Employee         Salary");
    printf("\n------   --------         ------\n");

    for (i = 0; i < n; i++)
        printf("%-9d%-15.15s%9.2f\n", emp_rec.emp_number[i],
               emp_rec.emp_name[i], emp_rec.salary[i]);

}


void
sql_error(msg)
char *msg;
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s", msg);
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

8.4.6 サンプル・プログラム: スクロール可能カーソルを使用するホスト配列

このプログラムは、スクロール可能カーソルとともにホスト配列を使用する方法を示します。このプログラムは、demoディレクトリのファイルscdemo2.pcとして、オンラインで使用可能です。

ノート:

結果セット内の行数の判断にはFETCH LASTを実行していることに注意してください。

8.4.6.1 scdemo2.pc

/*
 *  A Sample program to demonstrate the use of scrollable 
 *  cursors with host arrays.
 * 
 *  This program uses the hr/hr schema.Make sure
 *  that this schema exists before executing this program
 */


#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

#define ARRAY_LENGTH   4

/* user and passwd */
char *username = "hr";
char *password = "hr";

/* Declare a host structure tag. */
struct emp_rec_array
{
    int    emp_number;
    char   emp_name[20];
    float  salary;
} emp_rec[ARRAY_LENGTH];


/* Print the result of the query */

void print_rows()
{
    int i;

    for (i=0; i<ARRAY_LENGTH; i++)
        printf("%d    %s  %8.2f\n", emp_rec[i].emp_number,
             emp_rec[i].emp_name, emp_rec[i].salary);

}

/*  Oracle error handler */

void sql_error(char *msg)
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s", msg);
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(EXIT_FAILURE);
}

void main()
{
    int noOfRows; /* Number of rows in the result set */

    /* Error handler */
    EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:");

    /* Connect to the data base */
    EXEC SQL CONNECT :username IDENTIFIED BY :password;

    /* Error handle */
    EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:");

    /* declare the cursor in scrollable mode */
    EXEC SQL DECLARE c1 SCROLL CURSOR FOR
        SELECT employee_id, first_name, salary FROM employees;

    EXEC SQL OPEN c1;

    EXEC SQL WHENEVER SQLERROR DO sql_error("Fetch Error:");

    /* This is a dummy fetch to find out the number of rows
       in the result set */
    EXEC SQL FETCH LAST c1 INTO :emp_rec;

    /* The number of rows in the result set is given by 
       the value of sqlca.sqlerrd[2] */

    noOfRows = sqlca. sqlerrd[2];
    printf("Total number of rows in the result set %d:\n", 
             noOfRows);

    /* Fetch the first ARRAY_LENGTH number of rows */
    EXEC SQL FETCH FIRST c1 INTO :emp_rec;
    printf("******************** DEFAULT : \n");
    print_rows();

    /* Fetch the next set of ARRAY_LENGTH rows */
    EXEC SQL FETCH NEXT c1 INTO :emp_rec;
    printf("******************** NEXT  : \n");
    print_rows();

    /* Fetch a set of ARRAY_LENGTH rows from the 3rd row onwards */
    EXEC SQL FETCH ABSOLUTE 3 c1 INTO :emp_rec;
    printf("******************** ABSOLUTE 3 : \n");
    print_rows();

    /* Fetch the current ARRAY_LENGTH set of rows */
    EXEC SQL FETCH CURRENT c1 INTO :emp_rec;
    printf("******************** CURRENT : \n");
    print_rows();

    /* Fetch a set of ARRAY_LENGTH rows from the 2nd offset
       from the current cursor position */
    EXEC SQL FETCH RELATIVE 2 c1 INTO :emp_rec;
    printf("******************** RELATIVE 2 : \n");
    print_rows();

    /* Again Fetch the first ARRAY_LENGTH number of rows */
    EXEC SQL FETCH ABSOLUTE 0 c1 INTO :emp_rec;
    printf("******************** ABSOLUTE 0 : \n");
    print_rows();

    /* close the cursor */
    EXEC SQL CLOSE c1;

/* Disconnect from the database. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(EXIT_SUCCESS);
}

8.4.7 ホスト配列の制限

副問合せ文中を除き、SELECT文のWHERE句ではホスト配列を使用できません。例は、WHERE句の使用方法についてを参照してください。

またSELECT文またはFETCH文のINTO句では、単純ホスト変数とホスト配列を併用できません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。

表8-1は、SELECT INTO文で有効なホスト配列の使用を示しています。

表8-1 SELECT INTOで有効なホスト配列

INTO句	WHERE句	有効?
配列	配列	いいえ
スカラー	スカラー	はい
配列	スカラー	はい
スカラー	配列	いいえ

8.4.8 NULL値のフェッチについて

配列をSELECTおよびFETCHするときは、必ずインジケータ配列を使用します。このようにして、関連する出力ホスト配列内にNULLがあるかどうかをテストできます。

DBMS = V7またはDBMS=v8のときに、インジケータ配列に対応付けられていないホスト配列にNULL列値をSELECTまたはFETCHすると、Oracleは処理を停止し、sqlerrd[2]に処理済行数を設定しエラー・メッセージを出します。

また、SELECTまたはFETCHの結果、NULLの使用によるORA-24347などの警告が発生した場合や、列にインジケータ配列がない場合には、Oracleは処理を停止します。SELECTまたはFETCHのすべての列で標識変数を使用します。インジケータのない列がある場合は、プリコンパイラ・オプションunsafe_null=yesをかわりに使用できます。

8.4.9 切り捨てられた値のフェッチについて

DBMS=V7のときは、切捨てによって警告メッセージが発行されますが、処理は継続されます。

また、配列をSELECTおよびFETCHするときは必ずインジケータ配列を使用します。そうすれば、Oracleで1つ以上の切り捨てられた列値が出力ホスト配列に割り当てられた場合に、関連付けられたインジケータ配列で列値の元の長さがわかります。

8.5 配列での挿入について

ホスト配列はINSERT文内の入力変数として使用できます。プログラムでINSERT文を実行する前に、プログラム内にデータが含まれている配列があるかどうかを確認してください。

配列内に不適切な要素があるときは、FOR句を使用してINSERT対象の行数を制御できます。

ホスト配列による挿入の例は次のとおりです。

char   emp_name[50][20]; 
int    emp_number[50]; 
float  salary[50]; 
/* populate the host arrays */ 
... 
EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL) 
    VALUES (:emp_name, :emp_number, :salary);

挿入された行の累積数は処理済行数sqlca.sqlerrd[2]に保存されます。

次の例では、一度に1行ずつINSERTされます。各行の挿入ごとにサーバーをコールする必要があるため、この方法は前の例に比べるとかなり効率は悪くなります。

for (i = 0; i < array_size; i++) 
    EXEC SQL INSERT INTO emp (ename, empno, sal) 
        VALUES (:emp_name[i], :emp_number[i], :salary[i]);

関連項目

FOR句の使用について

8.5.1 配列での挿入の制限について

INSERT文のVALUES句内ではポインタ配列は使用できません。つまり配列要素はすべてデータ項目である必要があります。

INSERT文のVALUES句では、スカラー・ホスト変数とホスト配列を併用できません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。

8.6 配列での更新について

次の例に示すように、ホスト配列をUPDATE文内の入力変数として使用できます。

int   emp_number[50]; 
float salary[50]; 
/* populate the host arrays */ 
EXEC SQL UPDATE emp SET sal = :salary 
    WHERE EMPNO = :emp_number;

更新された行の累積数はsqlerrd[2]に保存されます。この数には更新カスケードによって処理された行は含まれていません。

配列内に不適切な要素がある場合は、埋込みSQLのFOR句を使用して更新対象の行数を制御できます。

前の例では一意キー(EMP_NUMBER)を使用した一般的な更新を示しています。各配列要素で更新できる行は1行のみです。次の例では、各配列要素で複数の行を操作できます。

char  job_title [10][20]; 
float commission[10]; 
 
... 
 
EXEC SQL UPDATE emp SET comm = :commission 
    WHERE job = :job_title;

8.6.1 配列での更新の制限について

UPDATE文のSET句またはWHERE句では、単純ホスト変数とホスト配列の併用はお薦めしません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。さらに、SET句でホスト配列を使用するときには、WHERE句の要素数と同じ数のものを使用してください。

UPDATE文のCURRENT OF句では、ホスト配列は使用できません。

表8-2は、UPDATE文で有効なホスト配列の使用方法を示しています。

表8-2 UPDATE文で有効なホスト配列

SET句	WHERE句	有効?
配列	配列	はい
スカラー	スカラー	はい
配列	スカラー	いいえ
スカラー	配列	いいえ

関連項目

CURRENT OFの疑似実行について

8.7 配列での削除について

DELETE文でもホスト配列を入力変数として使用できます。これは、WHERE句内のホスト配列の連続した要素を使用して、DELETE文を繰り返し実行するのと同様です。つまり1回の実行で表から0行、1行または複数行が削除されます。

ホスト配列による削除の例は次のとおりです。

... 
int emp_number[50]; 
 
/* populate the host array */ 
... 
EXEC SQL DELETE FROM emp 
    WHERE empno = :emp_number;

削除された行の累積数はsqlerrd[2]に保存されます。この数には削除カスケードによって処理された行は含まれていません。

この例では一意キー(EMP_NUMBER)を使用した一般的な削除を示しています。各配列要素で削除できる行は1行のみです。次の例では、各配列要素で複数の行を操作できます。

... 
char job_title[10][20]; 
 
/* populate the host array  */ 
... 
EXEC SQL DELETE FROM emp 
    WHERE job = :job_title;
...

8.7.1 配列での削除の制限について

DELETE文のWHERE句では、単純ホスト変数とホスト配列を併用できません。ホスト変数のうち1つでも配列があれば、すべてのホスト変数を配列にする必要があります。

DELETE文のCURRENT OF句ではホスト配列は使用できません。

8.8 FOR句の使用方法について

埋込みSQLでオプションのFOR句を使用すると、次に示すSQL文が処理する配列要素の数を設定できます。

DELETE
EXECUTE
FETCH
INSERT
OPEN
UPDATE

特にUPDATE、INSERTおよびDELETE文内でFOR句を使用すると便利です。これらの文に配列全体を使用する必要がないときもあります。次の例に示すように、FOR句を使用すると、使用する要素数を任意の数に制限できます。

char  emp_name[100][20]; 
float salary[100]; 
int   rows_to_insert; 
 
/* populate the host arrays */ 
rows_to_insert = 25;             /* set FOR-clause variable */ 
EXEC SQL FOR :rows_to_insert   /* will process only 25 rows */ 
    INSERT INTO emp (ename, sal) 
    VALUES (:emp_name, :salary);

FOR句では、配列要素をカウントするための整数型のホスト変数や、整数リテラルも使用できます。整数値を取るC言語の複合式は使用できません。たとえば、整数式を使用する次の文は無効です。

EXEC SQL FOR :rows_to_insert + 5                 /* illegal */ 
    INSERT INTO emp (ename, empno, sal) 
        VALUES (:emp_name, :emp_number, :salary);

FOR句の変数では、処理する配列の要素数を指定します。この数は、最小の配列次元を超えないように設定します。内部では、値は符号なしの数量として扱われます。付号付きのホスト変数を使用して負の値を渡すと、予期せぬ動作が発生します。

8.8.1 FOR句の制限

FOR句のセマンティクスを明確にするための制限が2つあります。FOR句は、SELECT文での使用またはCURRENT OF句との併用はできません。

8.8.1.1 SELECT文での使用

SELECT文中でFOR句を使用すると、エラー・メッセージが戻されます。

FOR句は意味があいまいなため、SELECT文中では使用できません。「このSELECT文をn回実行する」なのか、「このSELECT文を1回実行し、n行戻す」なのかはっきりしません。問題は、前者の場合、実行のたびに複数の行が戻される可能性があることです。後者の解釈では、次に示すように、カーソルを宣言してからFETCH文中でFOR句を使用することをお薦めします。

EXEC SQL FOR :limit FETCH emp_cursor INTO ...

8.8.1.2 CURRENT OF句との併用

次の例に示すように、UPDATEまたはDELETE文でCURRENT OF句を使用すると、FETCH文によって戻される最後の行を参照できます。

EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT ename, sal FROM emp WHERE empno = :emp_number; 
... 
EXEC SQL OPEN emp_cursor; 
... 
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; 
... 
EXEC SQL UPDATE emp SET sal = :new_salary 
WHERE CURRENT OF emp_cursor;

ただし、CURRENT OF句とFOR句は併用できません。次の文は、limitの論理値が1に限定されているため無効です(つまり、現在の行を更新または削除できるのは1回のみです)。

EXEC SQL FOR :limit UPDATE emp SET sal = :new_salary 
WHERE CURRENT OF emp_cursor; 
... 
EXEC SQL FOR :limit DELETE FROM emp 
WHERE CURRENT OF emp_cursor;

8.9 WHERE句の使用方法について

Oracleでは、要素数nのホスト配列を含むSQL文を、同一のSQL文をn個の異なるスカラー変数(個々の配列要素)でn回実行するのと同様に扱います。このような扱いがあいまいなときにかぎり、プリコンパイラによりエラー・メッセージが発行されます。

たとえば、次のような宣言をしたとします。

int  mgr_number[50]; 
char job_title[50][20];

次の文の場合、あいまいになります。

EXEC SQL SELECT mgr INTO :mgr_number FROM emp 
WHERE job = :job_title;

次の仮想の文のように処理されるためです。

for (i = 0; i < 50; i++) 
    SELECT mgr INTO :mgr_number[i] FROM emp 
        WHERE job = :job_title[i];

WHERE句の検索条件を満たす複数行があっても、データの受取りに使用できる出力変数が1つしかないためです。したがって、エラー・メッセージが出力されます。

一方、次の文を使用すると、不明瞭にならない場合があります。

EXEC SQL UPDATE emp SET mgr = :mgr_number 
    WHERE empno IN (SELECT empno FROM emp 
        WHERE job = :job_title);

次の仮想の文のように処理されるためです。

for (i = 0; i < 50; i++) 
    UPDATE emp SET mgr = :mgr_number[i] 
        WHERE empno IN (SELECT empno FROM emp 
            WHERE job = :job_title[i]);

これは、それぞれのjob_titleが複数行に一致する場合でも、WHERE句内のjob_titleに一致する各行についてSET句内にmgr_numberが指定されているためです。それぞれのjob_titleに一致する行すべてに、同一のmgr_numberをSETできます。したがってエラー・メッセージは発行されません。

8.10 構造体配列

スカラー配列を使用すると、1つの列での複数行操作が可能です。また、スカラー構造体を使用すると、1つの行での複数列操作が可能です。

しかし、複数列で複数行を操作する場合、これまでは複数のスカラー並列配列を個別にまたは1つの構造体中でカプセル化して割り当てる必要がありました。この方法よりも、このデータ構造を複数の構造体からなる1つの配列にしなおす方が便利です。

Pro*C/C++では構造体配列がサポートされており、アプリケーション開発者はC構造体の配列を使用して複数行および複数列の操作を実行できます。この機能拡張により、Pro*C/C++ではスカラー構造体の単純な配列を埋込みSQL文でバインド変数として処理できるため、データの処理がより簡単になります。これで、プログラミングがさらに直観的になり、データ編成もはるかに自由にできます。

Pro*C/C++では、構造体配列がバインド変数としてサポートされるのみでなく、インジケータ構造体の配列を構造体配列の宣言と併用できます。

ノート:

構造体をPL/SQLレコードにバインドすることと、構造体の配列をPL/SQLレコードの表にバインドすることは、この新たな機能の一部ではありません。また、構造体の配列を埋込みPL/SQLブロック内で使用することもできません。

構造体の配列は、複数の列で複数の行を操作するために使用され、通常次のように使用されると考えてください。

SELECT文またはFETCH文での出力バインド変数として使用
INSERT文のVALUES句での入力バインド変数として使用

関連項目

構造体の配列の制限

8.10.1 構造体の配列の使用方法

構造体の配列という概念は、C言語の開発者にとって特に目新しいものではありません。しかし、複数の並列配列からなる1つの構造体と比較してみると、データの格納方法に考え方の違いがあります。

複数の並列配列からなる1つの構造体では、個々の列のデータが連続して格納されます。一方、構造体の配列では、列のデータはインタリーブされます。この場合、配列内の各列の間は、その構造体内の他の列に必要な空白で区切られます。この空白はストライドと呼ばれます。

8.10.2 構造体の配列の制限

Pro*C/C++では、構造体配列の使用に次の制限事項があります。

構造体配列(通常の構造体による)を埋込みPL/SQLブロック内で使用できません。
構造体配列をWHERE句やFROM句で使用できません。
構造体配列はOracle動的SQL方法4では使用できません。ANSI動的SQLでは使用できます。
構造体配列をUPDATE文のSET句で使用できません。

構造体配列の宣言では構文に違いはありません。ただし、構造体配列を使用する場合には留意事項がいくつかあります。

関連項目

ANSI動的SQL

8.10.3 構造体の配列の宣言について

Pro*C/C++アプリケーションで使用する構造体配列を宣言する場合、開発者は必ず次の点に留意する必要があります。

構造体には必ず構造体タグを付けてください。たとえば、次のコード・セグメントで

    struct person {
       char name[15];
       int  age;
    } people[10];

このコード・セグメントでは、person変数が構造体のタグです。このタグによって、プリコンパイラは構造体の名前を使用してストライドのサイズを計算できます。

構造体のメンバーは配列にしないでください。ただし、charやVARCHARなどの文字型の場合には、この規則は適用されません。このような型の変数の宣言で配列の構文が使用されるためです。
charおよびVARCHAR型のメンバーは2次元配列にしないでください。
ネストされた構造体は構造体配列のメンバーになることはできません。Pro*C/C++の旧リリースでは、ネストされた構造体はサポートされていなかったため、この制限は新しいものではありません。
構造体自体のサイズは、符号付き4バイト数で表すことのできる最大値を超えないようにしてください。通常、この最大値は2GBです。

構造体配列の使用に関する前述の制限事項を満たしているので、Pro*C/C++では次の宣言は有効です。

struct department {
   int deptno;
   char dname[15];
   char loc[14];
} dept[4];

一方、次の宣言は無効です。

struct {              /* the struct is missing a structure tag */
  int empno[15];      /* struct members may not be arrays */
  char ename[15][10]; /* character types may not be 2-dimensional */
  struct nested {
    int salary;   /* nested struct not permitted in array of structs */
  } sal_struct;
} bad[15];

データ型の同値化を、構造体配列自体や構造体内の個々のフィールドには適用できないことにも注意してください。たとえば、empnoが前述の無効な構造体内の配列として宣言されていない場合、次の文は無効です。

exec sql var bad[3].empno is integer(4);

プリコンパイラには、構造体配列中の個々の構造体要素を追跡し記録する機能はありません。ただし、次を実行すれば、期待した結果が得られます。

typedef int myint;
exec sql type myint is integer(4);

struct equiv {
  myint empno; /* now legally considered an integer(4) datatype */
   ...
} ok[15];

Pro*C/C++の以前のリリースでは、個別の配列項目の同値化がサポートされていなかったため、これは予測できたことといえます。たとえば、次のスカラー配列宣言は何が有効で、何が有効でないかを示しています。

int empno[15];
exec sql var empno[3] is integer(4); /* illegal */

myint empno[15]; /* legal */

基本的には、個々の配列項目を同値化することはできません。

8.10.4 変数のガイドライン

標識変数は、構造体の配列の宣言でも、通常の構造体の宣言の場合とほとんど同じはたらきをします。構造体のインジケータ配列の宣言は、構造体の配列についての次の規則にも従う必要があります。

インジケータ構造体に含まれるフィールド数は、対応する構造体の配列に含まれるフィールド数以下である必要があります。
フィールドの順序は、対応する構造体の配列のメンバーの順序に一致する必要があります。
インジケータ構造体に含まれるすべての要素のデータ型は、shortである必要があります。
インジケータ配列のサイズは、ホスト変数で宣言されたサイズ以上である必要があります。ホスト変数で宣言されたサイズより大きい値は指定できますが、それより小さい値は指定できません。

これらの規則のほとんどには、Pro*C/C++の以前のリリースでの構造体使用規則が反映されています。配列制限も以前使用されたスカラーの配列に対するものと同じです。

これらの規則が適用されている場合に、次のように構造体を宣言するとします。

struct department {
   int deptno;
   char dname[15];
   char loc[14];
} dept[4];

次の標識変数の構造体の宣言は有効です。

struct department_ind {
   short deptno_ind;
   short dname_ind;
   short loc_ind;
} dept_ind[4];

一方、次は標識変数として無効です。

struct{               /* missing indicator structure tag */
  int deptno_ind;     /* indicator variable not of type short */
  short dname_ind[15];/* array element forbidden in indicator struct */
  short loc_ind[14];  /* array element forbidden in indicator struct */
} bad_ind[2];     /* indicator array size is smaller than host array */

関連項目

ANSI動的SQL

8.10.5 構造体配列へのポインタの宣言について

構造体配列へのポインタを宣言する方が適切な場合があります。これにより、構造体配列へのポインタを他の関数に渡したり、埋込みSQL文で直接指定したりできます。

ノート:

構造体配列へのポインタが参照する配列の長さは、プリコンパイル中にはわかりません。このため、埋込みSQL文で構造体の配列へのポインタ型になっているバインド変数を使用するときには、明示的なFOR句を使用してください。

ただし、FOR句は埋込みSQL SELECT文では指定できません。したがって、データを取り出して構造体配列へのポインタに入れる場合には、必ずカーソルとFETCH文をFOR句とともに明示的に指定してください。

8.10.6 例

次の例は、Pro*C/C++での構造体配列の機能について、様々な使用方法を示しています。

8.10.6.1 例1: スカラー構造体の単純な配列

次の構造体の宣言を指定したとします。

struct department {
   int deptno;
   char dname[15];
   char loc[14];
} my_dept[4];

次のようにdeptデータを選択してmy_deptに入れることができます。

exec sql select * into :my_dept from dept;

また、最初にmy_deptにデータを入れてから、dept表に一括して挿入できます。

exec sql insert into dept values (:my_dept);

標識変数を指定するには、構造体のパラレル・インジケータ配列を宣言します。

struct deptartment_ind {
   short deptno_ind;
   short dname_ind;
   short loc_ind;
} my_dept_ind[4];

データの選択に使用される問合せは、標識変数の指定が追加されたこと以外は同じです。

exec sql select * into :my_dept indicator :my_dept_ind from dept;

同様に、データの挿入時にもインジケータを指定できます。

exec sql insert into dept values (:my_dept indicator :my_dept_ind);

8.10.6.2 例2: スカラーの配列と構造体の配列との組合せ使用

Pro*C/C++の旧リリースと同様に、ユーザー・データのバルク処理機能に複数の配列を使用する場合は、各配列のサイズを同じにする必要があります。同じにしないと、最も小さい配列のサイズが選択され、残りの配列は変更されません。

次の宣言を指定したとします。

struct employee {
   int empno;
   char ename[11];
} emp[14];

float sal[14];
float comm[14];

ただ1つの問合せで、すべての列に対して複数行を選択できます。

exec sql select empno, ename, sal, comm into :emp, :sal, :comm from emp;

また、コミッション列の値がNULLかどうかを確認する必要があります。次のように宣言すれば、1つのインジケータ配列を指定するのみで済みます。

short comm_ind[14];
...
exec sql select empno, ename, sal, comm
   into :emp, :sal, :comm indicator :comm_ind from emp;

問合せからのインジケータ情報をすべてカプセル化した構造体の単一インジケータ配列は宣言できません。次の例を考えます。

struct employee_ind {   /* example of illegal usage */
   short empno_ind;
   short ename_ind;
   short sal_ind;
   short comm_ind;
} illegal_ind[15];

exec sql select empno, ename, sal, comm
   into :emp, :sal, :comm indicator :illegal_ind from emp;

この列は無効です(またお薦めできません)。上の文にはSELECT...INTOリスト全体ではなく、comm列しかないインジケータ配列が対応付けられています。

構造体の配列とsal、commおよびcomm_ind配列にデータを挿入する場合、その挿入方法は非常に簡単です。

exec sql insert into emp (empno, ename, sal, comm)
   values (:emp, :sal, :comm indicator :comm_ind);

8.10.6.3 例3: 複数の構造体配列と1つのカーソルとの組合せ使用

次の宣言をこの例として使用します。

struct employee {
   int empno;
   char ename[11];
   char job[10];
} emp[14];

struct compensation {
   int sal;
   int comm;
} wage[14];

struct compensation_ind {
   short sal_ind;
   short comm_ind;
} wage_ind[14];

Oracleのプログラムでは、構造体配列を次のように指定できます。

exec sql declare c cursor for 
   select empno, ename, job, sal, comm from emp;

exec sql open c;

exec sql whenever not found do break;
while(1)
{
  exec sql fetch c into :emp, :wage indicator :wage_ind;
  ... process batch rows returned by the fetch ...
}

printf("%d rows selected.\n", sqlca.sqlerrd[2]);

exec sql close c;

8.10.6.3.1 FOR句の使用方法について

FOR句を使用しても、FETCHで取り出す行数を指定できます。FOR句は、SELECT文を使用している場合には指定できませんが、INSERT文やFETCH文の場合には指定できます。

元の宣言に次を追加します。

int limit = 10;

次は、それに対応したコードの例です。

   exec sql for :limit
      fetch c into :emp, :wage indicator :wage_ind;

8.10.6.4 例4: 個々の配列メンバーおよび構造体メンバーの参照

これまでのPro*C/C++リリースで、構造体配列内の1構造体に対する配列参照が可能となっています。これによりバインド式はスカラーの単純な構造体で解決できるため、次は有効です。

exec sql select * into :dept[3] from emp;

次の例に示すように、構造体配列内の特定の構造体のスカラー・メンバーを個別に参照することも可能です。

exec sql select dname into :dept[3].dname from dept where ...;

この場合には当然、問合せは単一行問合せにする必要があり、1行のみを選択してこのバインド式で表される変数に代入します。

8.10.6.5 例5: 標識変数の使用(特殊な場合)

これまでのPro*C/C++リリースでは、インジケータ構造体のフィールド数はそれに対応するバインド変数構造体と同じにする必要がありました。構造体を通常に使用する場合には、この制限は緩和されています。前述の構造体のインジケータ配列についてのガイドラインに従っているので、次の例が可能です。

struct employee {
    float comm;
    float sal;
    int empno;
    char ename[10];
} emp[14];

struct employee_ind {
    short comm;
} emp_ind[14];

exec sql select comm, sal, empno, ename 
   into :emp indicator :emp_ind from emp;

標識変数はバインド値と1対1でマップされます。これらは、最初のフィールドから、対応した順番にマップされます。

ただし、他のフィールドでフェッチされた値がNULLであったり、インジケータが付いていなかったりすると、次のエラーが発生するため注意してください。

ORA-1405: fetched column value is NULL

たとえば、salがNULL値可能の場合にsalにはインジケータがないため、このエラーが発生します。

次のように構造体の配列を変更したとします。

struct employee {
   int empno;
   char ename[10];
   float sal;
   float comm;
} emp[15];

しかし、同じ構造体のインジケータ配列がまだ使用されているとします。

インジケータのマップは対応した順番に実行されるため、commインジケータがempnoフィールドにマップされて、commバインド変数にインジケータがないままとなり、再度ORA-1405エラーとなります。

対応するバインド変数の構造体よりもフィールド数の少ないインジケータ構造体を指定したときに、ORA-01405エラーが発生しないようにするには、NULL値可能の属性を最初から順番に並べる必要があります。

この例は、非配列の構造体を使用すれば複数列の単一行フェッチに簡単に変更でき、インジケータ構造体が次のように宣言されている場合と同等のはたらきをすると考えることができます。

struct employee_ind {
   short comm;
   short sal;
   short empno;
   short ename;
} emp_ind;

Pro*C/C++ではインジケータ構造体のフィールド数と対応する値の構造体のフィールド数が同じである必要がなくなったため、前述の例はこれまでPro*C/C++で無効でしたが現在は有効になりました。

Oracleのインジケータ構造体は、次のように簡単に指定できます。

struct employee_ind {
   short comm;
} emp_ind;

次のように配列なし構造体であるempおよびemp_indを使用すると、単一行フェッチを実行できます。

exec sql fetch comm, sal, empno, ename
   into :emp indicator :emp_ind from emp;

この場合にも、commインジケータがcommバインド変数にどのようにマップされるかに注意してください。

8.10.6.6 例6: 構造体配列へのポインタの使用

この例では、構造体配列へのポインタの使用方法を示します。

次の型の宣言を考えます。

typedef struct dept {
   int deptno;
   char dname[15];
   char loc[14];
} dept;

その型の構造体配列へのポインタを操作すると、様々な処理を実行できます。たとえば、構造体配列へのポインタを他の関数に渡すことができます。

void insert_data(d, n)
   dept *d;
   int n;
{
    exec sql for :n insert into dept values (:d);
}

void fetch_data(d, n)
   dept *d;
   int n;
{
   exec sql declare c cursor for select deptno, dname, loc from dept;
   exec sql open c;
   exec sql for :n fetch c into :d;
   exec sql close c;
}

このような関数をコールするには、例に示すように構造体配列のアドレスを渡します。

dept d[4];
dept *dptr = &d[0];
const int n = 4;

fetch_data(dptr, n);
insert_data(d, n); /* We are treating '&d[0]' as being equal to 'd' */

一部の埋込みSQL文では、構造体配列へのポインタを直接使用することができます。

exec sql for :n insert into dept values (:dptr);

FOR句の使用方法を間違えないように、十分に注意してください。

8.11 CURRENT OFの疑似実行について

DELETE文またはUPDATE文でCURRENT OF cursor句を使用すると、カーソルから最後にフェッチされた行を参照できます。ただし、CURRENT OF句とホスト配列の併用はできません。かわりに各行のROWIDを取得し、更新または削除するときにその値を使用して現在行を識別してください。

たとえば:

char  emp_name[20][10]; 
char  job_title[20][10]; 
char  old_title[20][10]; 
char  row_id[20][19]; 
... 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
SELECT ename, job, rowid FROM emp FOR UPDATE; 
... 
EXEC SQL OPEN emp_cursor; 
EXEC SQL WHENEVER NOT FOUND do break; 
for (;;) 
{ 
    EXEC SQL FETCH emp_cursor 
        INTO :emp_name, :job_title, :row_id; 
    ... 
    EXEC SQL DELETE FROM emp 
        WHERE job = :old_title AND rowid = :row_id; 
    EXEC SQL COMMIT WORK; 
}

関連項目

CURRENT OF句

8.12 追加の配列の挿入/選択構文の使用について

Oracleプリコンパイラでは、ホスト表に対するDB2のINSERTおよびFETCH構文もサポートされています。サポートされている追加の配列の挿入およびフェッチ構文は、次の図にそれぞれ示しています。

図8-1 追加の挿入構文

「図8-1 追加の挿入構文」の説明

図8-2 追加のフェッチ構文

「図8-2 追加のFETCH構文」の説明

オプションでROWSET句とROWSET STARTING AT句がフェッチの方向(FIRST, PRIOR, NEXT, LAST, CURRENT, RELATIVEおよびABSOLUTE)で使用されます。次の例を参考にしてください。

FIRST ROWSET
PRIOR ROWSET
NEXT ROWSET
LAST ROWSET
CURRENT ROWSET
ROWSET STARTING AT RELATIVEn
ROWSET STARTING AT ABSOLUTEn

DB2配列の挿入/フェッチ構文の例と、対応するOracleプリコンパイラ構文との比較を、表8-3に示します。

表8-3 DB2配列構文とOracleプリコンパイラ構文の比較

DB2配列構文	Oracleプリコンパイラの構文
EXEC SQL INSERT INTO dsn8810.act (actno, actkwd, actdesc) VALUES (:hva1, :hva2, :hva3) FOR :NUM_ROWS ROWS;	EXEC SQL FOR :num_rows INSERT INTO dsn8810.act (actno, actkwd, actdesc) VALUES (:hva1, :hva2, :hva3);
EXEC SQL FETCH NEXT ROWSET FROM c1 FOR 20 ROWS INTO :hva_empno, :hva_lastname, :hva_salary;	EXEC SQL FOR :twenty FETCH c1 INTO :hva_empno, :hva_lastname, :hva_salary;

DB2配列構文

Oracleプリコンパイラの構文

EXEC SQL
  INSERT INTO dsn8810.act 
  (actno, actkwd, actdesc) 
  VALUES (:hva1, :hva2, :hva3)
  FOR :NUM_ROWS ROWS;

EXEC SQL FOR :num_rows
  INSERT INTO dsn8810.act
  (actno, actkwd, actdesc)
  VALUES (:hva1, :hva2, :hva3);

EXEC SQL
  FETCH NEXT ROWSET FROM c1 
  FOR 20 ROWS 
  INTO :hva_empno, :hva_lastname,
       :hva_salary;

EXEC SQL
   FOR :twenty
   FETCH c1 
   INTO :hva_empno, :hva_lastname,
        :hva_salary;

DB2の構文では、行セットに位置付けられたカーソルは、データの行セットを取得する前に最初に宣言する必要があります。カーソルが行セットをフェッチできるようにするには、DECLARE CURSOR文で「WITH ROWSET POSITIONING」句を使用する必要があります。これは、次の表に示すように、Oracleプリコンパイラの構文では必要なく、関連もありません。

DB2配列構文	Oracleプリコンパイラの構文
EXEC SQL DECLARE c1 CURSOR WITH ROWSET POSITIONING FOR SELECT empno, lastname, salary FROM dsn8810.emp;	EXEC SQL DECLARE c1 CURSOR FOR SELECT empno, lastname, salary FROM dsn8810.emp;

このDB2配列構文のサポートは、プリコンパイラ・オプションdb2_arrayを指定して有効にできます。デフォルトのオプションはnoです。DB2配列構文のサポートは、Oracleプリコンパイラ構文と一緒に使用できません。一度にサポートされるのは、Oracleプリコンパイラ構文またはDB2構文のいずれか一方の構文のみです。

例8-1 DB2配列構文使用による行の挿入およびフェッチ

このプログラムでは、DB2の配列INSERT構文を使用して、EMP表にINSCNT行を挿入します。その後、DB2の配列FETCH構文を使用して、挿入された行をフェッチします。

/* 
 * db2arrdemo.pc 
 */
 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sqlca.h>
 
/* Number of rows to be inserted in one shot */
#define INSCNT 100
/* Number of rows to be fetched in one shot */
#define FETCHCNT 20
 
/* Define a host structure
   for inserting data into the table 
   and for fetching data from the table */
struct emprec
{
  int     empno;
  varchar ename[10];
  varchar job[9];
  int mgr;
  char hiredate[10];
  float sal;
  float comm;
  int deptno;
};
typedef struct emprec empdata;
 
/* Function prototypes */
void sql_error(char *);
void insertdata();
void fetchdata();
void printempdata(empdata *);
 
void main()
{
 
  exec sql begin declare section;
    char *uid = "scott/tiger";
  exec sql end declare section;
 
  exec sql whenever sqlerror do sql_error("ORACLE error--\n");
  exec sql connect :uid;
 
  printf("Inserting %d rows into EMP table using DB2 array insert syntax.\n",
         INSCNT);
  insertdata();
  printf("\nFetching data using DB2 array fetch syntax.\n");
  fetchdata();
 
  exec sql rollback work release;
  exit(EXIT_SUCCESS);
}
 
/* Inserting data into the table using DB2 array insert syntax*/
void insertdata() 
{
  int i, cnt;
  char *str;
  empdata emp_in[INSCNT];
 
  /* To store temporary strings */
  str = (char *)malloc (25 * sizeof(char));
 
  /* Fill the array elements to insert */ 
  for (i = 0; i < INSCNT; i++)
  {
    emp_in[i].empno = i+1;
    sprintf(str, "EMP_%03d", i+1);
    strcpy (emp_in[i].ename.arr, str);
    emp_in[i].ename.len = strlen (emp_in[i].ename.arr);
    sprintf(str, "JOB_%03d", i+1);
    strcpy (emp_in[i].job.arr, str);
    emp_in[i].job.len = strlen (emp_in[i].job.arr);
    emp_in[i].mgr = i+1001;
    sprintf(str, "%02d-MAY-06", (i%30)+1);
    strcpy (emp_in[i].hiredate, str);
    emp_in[i].sal = (i+1) * 10;
    emp_in[i].comm = (i+1) * 0.1;
    emp_in[i].deptno = 10;
  }
 
  free (str);
 
  /* Inserting data using DB2 array insert syntax */
  exec sql insert into emp values (:emp_in) FOR :INSCNT rows;
 
  exec sql select count(*) into :cnt from emp where ename like 'EMP_%';
  printf ("Number of rows successfully inserted into emp table: %d\n", cnt);
}
 
/* Fetches data from the table using DB2 array fetch syntax*/
void fetchdata()
{
  empdata emp_out[FETCHCNT];
 
  /* Declares scrollable cursor to fetch data */
  exec sql declare c1 scroll cursor with rowset positioning for
         select empno, ename, job, mgr, hiredate, sal, comm, deptno
         from emp where ename like 'EMP_%' order by empno;
  
  exec sql open c1;
 
  exec sql whenever not found do break;
  while(1)
  {
    /* Fetches data using DB2 array fetch syntax */
    exec sql fetch next rowset from c1 for :FETCHCNT rows into :emp_out;
    printempdata(emp_out);
  }
  exec sql whenever not found do sql_error("ORACLE ERROR");
 
  exec sql close c1;
}
 
/* Prints the fetched employee data */
void printempdata(empdata *emp_out)
{
  int i;
  for (i=0; i<FETCHCNT; i++)
  {
    emp_out[i].ename.arr[emp_out[i].ename.len] = '\0';
    emp_out[i].job.arr[emp_out[i].job.len] = '\0';
    printf("Empno=%d, Ename=%s, Job=%s, Mgr=%d, Hiredate=%s, Sal=%6.2f,\n"
           "Comm=%5.2f, Deptno=%d\n", emp_out[i].empno, emp_out[i].ename.arr,
 emp_out[i].job.arr, emp_out[i].mgr, emp_out[i].hiredate,
 emp_out[i].sal, emp_out[i].comm, emp_out[i].deptno);
  }
}
 
/* Error handling function. */
void sql_error(char *msg)
{
  exec sql whenever sqlerror continue;
 
  printf("\n%s\n", msg);
  printf("%.70s\n", sqlca.sqlerrm.sqlerrmc); 
  exec sql rollback release;
 
  exit(EXIT_FAILURE);
}

8.13 暗黙的なバッファ済INSERTの使用について

パフォーマンスを高めるために、Pro*C/C++アプリケーション開発者は、埋め込んだSQL文のホスト配列を参照できます。これにより、データベースへの1回のラウンドトリップでSQL文の配列を実行できます。配列の実行によってパフォーマンスが大幅に向上するにもかかわらず、ANSI規格ではないため、この機能を使用しない開発者もいます。たとえば、Oracle製品で配列の実行を使用するように記述されたアプリケーションは、IBMのプリコンパイラを使用してプリコンパイルすることはできません。

対処方法として、バッファ済INSERT文を使用すると、ANSI規格の埋込みSQL構文を保持しながらパフォーマンスを向上させることができます。

コマンドライン・オプション「max_row_insert」は、INSERT文の実行前にバッファする行の数を制御します。このオプションのデフォルトは0で、機能は無効化されています。この機能を有効化するには、0よりも大きい任意の数を指定します。

挿入バッファを有効化すると、プリコンパイラのランタイムが対応するカーソルにフラグを付け、次を実行します。

バインド変数を保持するための追加のメモリーを割当てまたは再割当てします(初回実行時のみ)。
プログラムのホスト変数から内部ランタイム・バインド構造にバインド変数をコピーします。
バッファ済の行カウントを増加させます。
MAX_INSERT_ROWSがバッファされている場合、バッファされたINSERT文をフラッシュします。
MAX_INSERT_ROWSに達していない場合は、フラッシュせずに、内部バインド・バッファに値をコピーした後、戻ります。

新しい埋込みSQL文が実行され、バッファ挿入文がフラッシュされる場合、次のことが実行されます。

バッファをフラッシュします。
フラッシュを求めたコールを続行します。

アプリケーションには、標準のプリコンパイラ・エラー・メカニズム(Pro*Cのsqlcaなど)を介して、エラーが知らされます。

「implicit_svpt」オプションは、新しくバッチ処理された挿入を開始する前に、暗黙的なセーブポイントを設定するかどうかを制御します。

YESの場合は、新しい行のバッチを開始する前に、セーブポイントが設定されます。挿入時にエラーが発生すると、暗黙的な「セーブポイントへのロールバック」が実行されます。
NOの場合は、暗黙的なセーブポイントが設定されません。バッファ済INSERTでエラーが発生すると、アプリケーションに通知されますが、ロールバックは実行されません。バッファ済INSERTのエラーは非同期に報告されます。アプリケーションでINSERT文が実行されると、挿入された行のエラーは報告されません。
- 挿入された行の一部のエラーは、INSERT以外の文が初めて実行されたときに後で報告されます。これには、DELETE、UPDATE、(別の表に対する)INSERT、COMMITおよびROLLBACKが含まれます。バッファ済INSERT文をクローズする文は、すべてエラーを報告します。この場合、エラーを報告する文は実行されません。エラーを処理した後で、バッファ済INSERTのエラーを報告した文を再実行する必要があります。それ以外の場合は、トランザクションをロールバックして再実行します。
  
  たとえば、COMMIT文を使用して、バッファ済INSERTのループをクローズする方法について考えてみます。COMMITでは、以前のINSERTの重複キーが原因でエラーが発生します。この場合、COMMITは実行されません。エラーを処理した後で、COMMITを再実行する必要があります。それ以外の場合は、トランザクションをロールバックして再実行します。
- 挿入自体についてもいくつかエラーが報告され、以前に挿入された行のエラーが反映されている可能性があります。そのような場合、それ以上挿入は実行されません。以前挿入された行のエラーを処理し、現行の挿入を継続する必要がありますが、それには時間がかかります。かわりに、トランザクションをロールバックし、再実行してもかまいません。
  
  たとえば、内部バッファの制限が10行で、アプリケーションがループで15行を挿入しているとします。8行目でエラーが発生したとします。エラーは、11行目が挿入されたときに報告され、これ以後にINSERTは実行されません。

バッファ済INSERT中に発生する可能性のあるエラーの一部を次に示します。

ORA-00001: 索引キーが重複しています
ORA-01400: 挿入時に必須列(NOT NULL)がないか、NULLになっています
ORA-01401: 列に挿入した値が大きすぎます。
ORA-01438: 指定した精度を超えた値が列に指定されています

例8-2 表へのバッファ行の挿入

このプログラムでは、EMP表に行のLOOPCNT数を挿入します。loop counter=5の場合、このプログラムは無効なempnoの挿入を試行します。max_row_insertオプションを使用しないと、プログラムでは無効な行を除くすべての行が挿入されます。max_row_insertオプションをLOOPCNTに設定すると、最初の4行のみが挿入されます。

max_row_insertオプションを使用すると、間違った文が削除されるときに、プログラムは配列INSERTプログラムと同様に動作します。

/* 
 * bufinsdemo.pc 
 */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sqlca.h>

/* Number of rows to be inserted into the table */
#define LOOPCNT 100

/* Define a host structure
   for inserting data into the table 
   and for fetching data from the table */
struct emprec
{
  int empno;
  varchar ename[10];
  varchar job[9];
  int mgr;
  char hiredate[10];
  float sal;
  float comm;
  int deptno;
};
typedef struct emprec buffinstyp;

/* Function prototypes */
void sql_error();
void insertdata();
void fetchdata();
void printempdata(buffinstyp);

void main()
{

  exec sql begin declare section;
    char *uid = "scott/tiger";
  exec sql end declare section;

  exec sql whenever sqlerror do sql_error();
  exec sql connect :uid;

  printf("\nInserting %d rows into EMP table.\n", LOOPCNT);
  insertdata();
  printf("\nFetching inserted data from EMP table.\n");
  fetchdata();

  exec sql delete from emp where empno < 1000;

  exec sql commit work release;
  exit(EXIT_SUCCESS);
}

/* Inserting data into the table */
void insertdata() 
{
  int i, cnt;
  char *str;
  buffinstyp emp_in;

  /* To store temporary strings */
  str = (char *)malloc (25 * sizeof(char));

  /*
   * When max_row_insert option is set to LOOPCNT and when the errorneous
   * statement is removed, all the rows will be inserted into the database in
   * one stretch and hence maximum performance gain will be achieved.
 */
  for (i = 1; i <= LOOPCNT; i++)
  {
    if (i != 5)
      emp_in.empno = i;
    else
    /* Errorneous statement. In emp table, empno is defined as number(4). */
      emp_in.empno = 10000;

    sprintf(str, "EMP_%03d", i);
    strcpy (emp_in.ename.arr, str);
    emp_in.ename.len = strlen (emp_in.ename.arr);
    sprintf(str, "JOB_%03d", i);
    strcpy (emp_in.job.arr, str);
    emp_in.job.len = strlen (emp_in.job.arr);
    emp_in.mgr = i+1001;
    sprintf(str, "%02d-MAY-06", (i%30));
    strcpy (emp_in.hiredate, str);
    emp_in.sal = (i) * 10;
    emp_in.comm = (i) * 0.1;
    emp_in.deptno = 10;

    exec sql insert into emp values (:emp_in);
  }

  free (str);

  exec sql commit;

  exec sql select count(*) into :cnt from emp where ename like 'EMP_%';
  printf ("Number of rows successfully inserted into emp table: %d\n", cnt);
}

/* Fetches data from the table*/
void fetchdata()
{
  buffinstyp emp_out;

  /* Declares cursor to fetch only the rows that are inserted */
  exec sql declare c1 cursor for
         select empno, ename, job, mgr, hiredate, sal, comm, deptno
         from emp where ename like 'EMP_%' order by empno;
  
  exec sql open c1;

  exec sql whenever not found do break;
  while(1)
  {
    /* Fetches single row at each call */
    exec sql fetch c1 into :emp_out;
    printempdata(emp_out);
  }
  exec sql whenever not found do sql_error();

  exec sql close c1;
}

/* Prints the fetched employee data */
void printempdata(buffinstyp emp_out)
{
  emp_out.ename.arr[emp_out.ename.len] = '\0';
  emp_out.job.arr[emp_out.job.len] = '\0';
  printf("Empno=%d, Ename=%s, Job=%s, Mgr=%d, Hiredate=%s, Sal=%6.2f,\n"
         "Comm=%5.2f, Deptno=%d\n", emp_out.empno, emp_out.ename.arr,
 emp_out.job.arr, emp_out.mgr, emp_out.hiredate, emp_out.sal,
 emp_out.comm, emp_out.deptno);
}

/* Error handling function. */
void sql_error()
{
   printf("Error %s\n", sqlca.sqlerrm.sqlerrmc);
   printf(" Rows Processed: %d\n", sqlca.sqlerrd[2]);
   printf(" Rows Rolled Back: %d\n", sqlca.sqlerrd[0]);
}

8.14 スクロール可能カーソル

スクロール可能カーソルは、SQL文が実行され、実行中に処理された情報が格納される作業領域です。カーソルを実行すると、問合せの結果は結果セットと呼ばれる一連の行に配置されます。結果セットは、順番にフェッチすることも、順不同でフェッチすることもできます。順不同の結果セットをスクロール可能カーソルと呼びます。スクロール可能カーソルを使用すると、ユーザーは前から、後ろからまたはランダムな方法でデータベース結果セットの行にアクセスできます。これにより、プログラムは結果セットの任意の行をフェッチできます。