Database Reference
In-Depth Information
PL/SQL
To use the array interface in PL/SQL, the
FORALL
statement is available. With it, you can execute a DML statement that
binds arrays to pass data to the database engine. The following PL/SQL block shows how to insert 100,000 rows with
a single execution. Notice that the first part of the code is used only to prepare the arrays. The
FORALL
statement itself
with the
INSERT
statement takes only the last two lines of the PL/SQL block:
DECLARE
TYPE t_id IS TABLE OF t.id%TYPE;
TYPE t_pad IS TABLE OF t.pad%TYPE;
l_id t_id := t_id();
l_pad t_pad := t_pad();
BEGIN
-- prepare data
l_id.extend(100000);
l_pad.extend(100000);
FOR i IN 1..100000
LOOP
l_id(i) := i;
l_pad(i) := rpad('*',100,'*');
END LOOP;
-- insert data
FORALL
i IN l_id.FIRST..l_id.LAST
INSERT INTO t VALUES (l_id(i), l_pad(i));
END;
It's important to note that even if the syntax is based on the keyword
FORALL
, this isn't a loop. All rows are sent in
a single database call.
The array interface is supported not only in this case, but the
dbms_sql
package and native dynamic SQL also
support it.
OCI
To take advantage of the array interface with OCI, no specific function is needed. In fact, the functions used to bind the
variables,
OCIBindByPos
and
OCIBindByName
, and the function used to execute the SQL statement,
OCIStmtExecute
,
can work with arrays as parameters. The C program in the
array_interface.c
file provides an example.
JDBC
To use the array interface with JDBC,
batch updates
are available. As shown in the following code snippet, which
inserts 100,000 rows in a single execution, you can add an “execution” to a batch by executing the
addBatch
method.
When the whole batch containing several executions is then ready, it can be submitted to the database engine
by executing the
executeBatch
method. Both methods are available in the
java.sql.Statement
interface and,
consequently, in the subinterfaces
java.sql.PreparedStatement
and
java.sql.CallableStatement
as well. The Java
program in the
ArrayInterface.java
file provides a complete example:
sql = "INSERT INTO t VALUES (?, ?)";
statement = connection.prepareStatement(sql);
for (int i=1 ; i<=100000 ; i++)
