バインド変数

バインド変数を使用して、データベースに渡されるデータまたはデータベースから取得されるデータを制御します。

SQLおよびPL/SQL文には、接頭辞としてコロンが付いた識別子で示されるバインド変数を含めることができます。これらのパラメータは、別個に指定された値が実行時に代入される文内の場所、または実行後に値が戻される場所を示します。

Oracle Databaseには、次の3種類のバインド変数があります:

INバインド変数
OUTバインド変数
IN OUTバインド変数

INバインドは、データベースに渡される値です。OUTバインドは、データベースからデータを取得するために使用されます。IN OUTバインドは渡され、文の実行後に異なる値を戻すことができます。

文字列連結やテンプレート・リテラルを使用してSQLまたはPL/SQL文を構成するよりも、バインド変数を使用することをお薦めします。バインド変数を使用すると、パフォーマンスとセキュリティの両面で有益である可能性があります。バインド変数を使用すると、Oracle Databaseでは、リソースと時間を消費するハード解析操作を実行する必要がありません。かわりに、カーソル・キャッシュにすでに存在するカーソルを再利用できます。

ノート:

バインド変数は、CREATE TABLEなどのDDL文で使用することも、問合せのテキストを代入することもできません。データのみを代入できます。

トピック

名前によるバインドと位置によるバインドの使用
バインド変数は、名前による方法と位置による方法の2つの方法で使用されます。これらのオプションは相互に排他的であるため、指定されたSQLコマンドについて一方を選択する必要があります。
RETURNING INTO句
RETURNING INTO句の使用について説明します。
バッチ操作
connection.execute()ファンクションをコールすることに加えて、connection.executeMany()を使用してバッチ操作を実行することもできます。

親トピック: MLE JavaScript SQLドライバからのPL/SQLおよびSQLのコール

名前によるバインドと位置によるバインドの使用

バインド変数は、名前による方法と位置による方法の2つの方法で使用されます。これらのオプションは相互に排他的であるため、指定されたSQLコマンドについて一方を選択する必要があります。

トピック

名前付きバインド変数
名前によってバインドするには、バインド変数が、接頭辞としてコロンが付いた文字列リテラルである必要があります。
位置指定バインド変数
名前付きバインド・パラメータを使用するかわりに、バインド変数情報を配列として指定することもできます。

親トピック: バインド変数

名前付きバインド変数

名前によってバインドするには、バインド変数が、接頭辞としてコロンが付いた文字列リテラルである必要があります。

名前付きバインドの場合、connection.execute()ファンクションのbindParams引数には、定義される各バインド変数の次のプロパティを指定することをお薦めします。

プロパティ	説明
`dir`	バインド変数の方向
`val`	SQL文に渡される値
`type`	データ型

例7-8 名前付きバインド変数の使用

CREATE OR REPLACE PROCEDURE named_binds_ex_proc(
    "deptName" VARCHAR2,
    "sal" NUMBER
)
AS MLE LANGUAGE JAVASCRIPT
{{
if (deptName === null || sal === null){
    throw new Error(
        `must provide deptName and sal to named_binds_ex_proc()`
    );
}

const result = session.execute(
    `SELECT
        e.first_name ||
        ''    ||
        e.last_name employee_name,
        e.salary
    FROM
        hr.employees e
        LEFT JOIN hr.departments d ON (e.department_id = d.department_id)
    WHERE
        nvl(d.department_name, 'n/a') = :deptName
        AND salary > :sal
    ORDER BY
        e.employee_id`,
    {
        deptName:{
            dir: oracledb.BIND_IN,
            val: deptName,
            type: oracledb.STRING
        },
        sal:{
            dir: oracledb.BIND_IN,
            val: sal,
            type: oracledb.NUMBER
        }
    }
);
console.log(`Listing employees working in ${deptName} with a salary > ${sal}`);
for (let row of result.rows){
    console.log(`${row.EMPLOYEE_NAME.padEnd(25)} - ${row.SALARY}`);
}
}};
/

connection.execute()のbindParams引数は、次の2つの名前付きバインド・パラメータを定義します:

deptName
sal

この例では、ファンクションの入力パラメータがバインド変数の名前と一致していることで読みやすくなっていますが、このことは必須ではありません。bindParamsのマッピングが正しいかぎり、バインド変数名を割り当てることができます。

親トピック: 名前によるバインドと位置によるバインドの使用

位置指定バインド変数

名前付きバインド・パラメータを使用するかわりに、バインド変数情報を配列として指定することもできます。

配列内の要素の数は、SQLテキスト内のバインド・パラメータの数と一致している必要があります。名前によってマッピングするのではなく、バインド変数と値のマッピングは、テキスト内のバインド変数の位置とバインド配列内の項目の位置に基づきます。

例7-9 位置指定バインド変数の使用

この例は、位置指定バインド変数の使用を示し、例7-8の再実装を表しています

CREATE OR REPLACE PROCEDURE positional_binds_ex_proc(
    "deptName" VARCHAR2,
    "sal" NUMBER
)
AS MLE LANGUAGE JAVASCRIPT
{{
if (deptName === null || sal === null){
    throw new Error(
        `must provide deptName and sal to positional_binds_ex_proc()`
    );
}

const result = session.execute(
    `SELECT
        e.first_name ||
        ''    ||
        e.last_name employee_name,
        e.salary
    FROM
        hr.employees e
        LEFT JOIN hr.departments d ON (e.department_id = d.department_id)
    WHERE
        nvl(d.department_name, 'n/a') = :deptName
        AND salary > :sal
    ORDER BY
        e.employee_id`,
    [
        deptName, 
        sal
    ]
);
console.log(`Listing employees working in ${deptName} with a salary > ${sal}`);
for(let row of result.rows){
    console.log(`${row.EMPLOYEE_NAME.padEnd(25)} - ${row.SALARY}`);
}
}};
/

この例では、bindParamsはオブジェクトではなく、配列です。SQLテキスト内のバインド変数と値の間のマッピングは、位置によって行われます。bindParams配列内の最初の項目は、SQLテキスト内のプレースホルダの最初の出現にマップされ、その後も同様にマップされます。

親トピック: 名前によるバインドと位置によるバインドの使用

RETURNING INTO句

RETURNING INTO句の使用について説明します。

RETURNING INTO句を使用すると、次のことができます

更新中に変更された値をフェッチする
単一行の挿入操作中に自動生成されたキーを戻す
削除された行をリストする

例7-10 RETURNING INTO句の使用

この例では、更新操作後に古い値と新しい値を取得する方法を示します。これらの値を使用して、さらに処理を行うことができます。

CREATE OR REPLACE PROCEDURE ret_into_ex_proc(
    "firstEmpID" NUMBER,
    "lastEmpID" NUMBER
)
AS MLE LANGUAGE JAVASCRIPT
{{
if (firstEmpID === null || lastEmpID === null){
    throw new Error(
        `must provide deptName and sal to ret_into_ex_proc()`
    );
}

const result = session.execute(
    `UPDATE
        hr.employees
    SET
        last_name = upper(last_name)
    WHERE
        employee_id between :firstEmpID and :lastEmpID
    RETURNING
        old last_name
        new last_name
    INTO
        :oldLastName,
        :newLastName`,
    {
        firstEmpID: {
            dir: oracledb.BIND_IN,
            val: firstEmpID,
            type: oracledb.NUMBER
        },
        lastEmpID: {
            dir: oracledb.BIND_IN,
            val: lastEmpID,
            type: oracledb.NUMBER
        },
        oldLastName: {
            type: oracledb.STRING,
            dir: oracledb.BIND_OUT
        },
        newLastName: {
            type: oracledb.STRING,
            dir: oracledb.BIND_OUT
        }
    }
);

if (result.rowsAffected > 1){
    console.log(
        `update() completed successfully:
        - old values: ${JSON.stringify(result.outBinds.oldLastName)}
        - new values: ${JSON.stringify(result.outBinds.newLastName)}`
    );
} else {
    throw new Error(
        `found no row to update in range ${firstEmpID} to ${lastEmpID}`
    );
}
}};
/

この例では、INとOUTの両方のバインド変数を扱っています:

firstEmpIDおよびlastEmpIDでは、更新するデータ範囲を指定します
oldLastNameは、更新前の姓をすべて含む配列です
newLastNameは、新しい値を含む別の配列です

親トピック: バインド変数

バッチ操作

connection.execute()ファンクションをコールすることに加えて、connection.executeMany()を使用してバッチ操作を実行することもできます。

connection.executeMany()を使用することはconnection.execute()を複数回コールすることと似ていますが、必要な作業が少なくなります。これは、複数の行を挿入または更新するときなど、バッチ変更を処理するための効率的な方法です。connection.executeMany()メソッドを問合せに使用することはできません。

connection.execute()には、SQL文で処理する変数を含む配列が必要です。例7-11のbindData配列には、SQL文で定義されたバインド変数ごとに1つずつ、複数のJavaScriptオブジェクトが含まれます。forループによって、それらのオブジェクトが構成され、bindData配列に追加されます。

バッチ操作に渡される値に加えて、MLE JavaScript SQLドライバは値のデータ型を把握する必要があります。この情報は、connection.executeMany()のオプション・パラメータでbindDefsプロパティとして渡されます。例7-11の古い姓と新しい姓はどちらも、日付として定義されたchangeDateを持つ文字列です。

connection.execute()ファンクションと同様に、connection.executeMany()はrowsAffectedプロパティを戻すため、バッチ処理された行の数を簡単に識別できます。

例7-11 バッチ操作の実行

この例では古い姓と新しい姓を監査表に挿入して例7-9を拡張しています。

CREATE OR REPLACE PROCEDURE ret_into_audit_ex_proc(
    "firstEmpID" NUMBER,
    "lastEmpID" NUMBER
)
AS MLE LANGUAGE JAVASCRIPT
{{
if (firstEmpID === null || lastEmpID === null){
    throw new Error(
        `must provide deptName and sal to ret_into_audit_ex_proc()`
    );
}

let result = session.execute(
    `UPDATE
        hr.employees
    SET
        last_name = upper(last_name)
    WHERE
        employee_id between :firstEmpID and :lastEmpID
    RETURNING
        old last_name,
        new last_name
    INTO
        :oldLastName,
        :newLastName`,
    {
        firstEmpID: {
            dir: oracledb.BIND_IN,
            val: firstEmpID,
            type: oracledb.NUMBER
        },
        lastEmpID: {
            dir: oracledb.BIND_IN,
            val: lastEmpID,
            type: oracledb.NUMBER
        },
        oldLastName: {
            type: oracledb.STRING,
            dir: oracledb.BIND_OUT
        };
        newLastName: {
            type: oracledb.STRING,
            dir: oracledb.BIND_OUT
        }
    }
);

if (result.rowsAffected > 1){
    // store the old data and new values in an audit table
    let bindData = [];
    const changeDate = new Date();
    for (let i = 0; i < result.outBinds.oldLastName.length, i++){
        bindDate.push(
            {
                oldLastName: result.outBinds.oldLastName[i],
                newLastName: result.outBinds.newLastName[i],
                changeDate: changeDate
            }
        );
    }
    // use executeMany() with the newly populated array
    result = session.executeMany(
        `insert into EMPLOYEES_AUDIT_OPERATIONS(
            old_last_name,
            new_last_name,
            change_date
        ) values (
            :oldLastName,
            :newLastName,
            :changeDate
        )`,
        bindData,
        {
            bindDefs: {
                oldLastName: {type: oracledb.STRING, maxSize: 30},
                newLastName: {type: oracledb.STRING, maxSize: 30},
                changeDate: {type: oracledb.DATE}
            }
        }
    );

} else {
    throw new Error(
        `found no row to update in range ${firstEmpID} to ${lastEmpID}`
    );
}
}};
/

初期更新文が完了すると、データベースは、更新の影響を受けたlast_name列の古い値と新しい値をresultオブジェクトのoutBindsプロパティで提供します。oldLastNameとnewLastNameはどちらも配列です。配列の長さは、更新された行の数を表します。

親トピック: バインド変数