Batch Operations

In addition to calling the connection.execute() function, it is possible to use connection.executeMany() to perform batch operations.

Using connection.executeMany() is like calling connection.execute() multiple times but requires less work. This is an efficient way to handle batch changes, for example, when inserting or updating multiple rows. The connection.executeMany() method cannot be used for queries.

connection.execute() expects an array containing variables to process by the SQL statement. The bindData array in Example 7-11 contains multiple JavaScript objects, one for each bind variable defined in the SQL statement. The for loop constructs the objects and adds them to the bindData array.

In addition to the values to be passed to the batch operation, the MLE JavaScript SQL driver needs to know about the values' data types. This information is passed as the bindDefs property in the connection.executeMany() options parameter. Both old and new last names in Example 7-11 are character strings with the changeDate defined as a date.

Just as with the connection.execute() function, connection.executeMany() returns the rowsAffected property, allowing you to quickly identify how many rows have been batch processed.

Example 7-11 Performing a Batch Operation

This example extends Example 7-9 by inserting the old and new last names into an audit table.

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}`
    );
}
}};
/

After the initial update statement completes, the database provides the old and new values of the last_name column affected by the update in the result object's outBinds property. Both oldLastName and newLastName are arrays. The array length represents the number of rows updated.

Parent topic: Bind Variables