Executing Insert/Update/Delete statements with a Returning clause
For Oracle 8.1.6 and higher, the Oracle driver supports executing Insert, Update, and Delete statements with the Returning clause, which allows your application to return inserted, updated, or deleted values of a row into a variable and eliminate the need to execute additional statements to return this information.
The driver returns the values for each column named in the Returning clause as an output parameter. Your application must execute the Insert, Update, or Delete statement with the Returning clause using a CallableStatement object. In addition, your application must specify the data type of each returned value using the CallableStatement.registerOutParameter() method. The registered data type for a returned value must match the data type of the database column. For example, if the database column is defined with a JDBC type of CHAR, the data type of the returned value for that column must be registered as Types.CHAR.
The Returning clause can return a single row or multiple rows. The method your application uses to retrieve the values of returned columns depends on the number of rows the Returning clause returns as shown in the following examples.
Example A: Retrieving a Result Value From an Insert/Update/Delete of a Single Row
Given the table defined by:
CREATE TABLE employees (id int, name varchar(30))
You can use the following Insert statement with the Returning clause to return the updated ID for Smith:
String sql = "INSERT INTO employees VALUES(100, 'Smith') RETURNING id INTO ?";
CallableStatement callStmt = con.prepareCall(sql);
callStmt.registerOutParameter(1, Types.INTEGER);
int updateCnt = callStmt.executeUpdate();
int newId = callStmt.getInt(1);
System.out.println("The id of the inserted row is: " + newId);
The database server returns a single result value for the requested column. An application can retrieve the result value using any of the following CallableStatement methods: getInt(), getString(), getObject(), and so on. The object type returned by getObject() is based on the data type specified in the registerOutParameter() call for the returned columns. Refer to the JDBC specification for details about JDBC data type to Java object mapping.
Example B: Retrieving Result Values From an Insert/Update/Delete of Multiple Rows
Given the table defined by:
CREATE TABLE employees (id int, name varchar(30))
You can use the following Update statement with the Returning clause to return all rows with an updated ID value.
String sql = "UPDATE employees SET id = id + 1000 RETURNING id INTO ?";
CallableStatement callStmt = con.prepareCall(sql);
callStmt.registerOutParameter(1, Types.INTEGER);
int updateCnt = callStmt.executeUpdate();
Integer[] newIds = (int []) callStmt.getArray(1).getArray();
for (int index = 0; index < newIds.length; index++) {
System.out.println("New Id value: " + newIds[index]);
}
The database server returns multiple result values for the requested column. An application can retrieve the result values using the CallableStatement.getArray() method.
Note: If you use the CallableStatement.getxxx() methods to retrieve the result values, the driver only returns the first result value for the requested column.
The data type of the returned array, and the data type of the array elements, match the data type specified in the registerOutParameter() call for the returned column. The elements of the array are an object type. For example, if the application registered the data type of the returned value as Types.INTEGER, the elements of the array are returned as Integer objects. The result set generated by the CallableStatement.getArray() method is a forward-only result set with a result set concurrency of read only. It contains a single column and has a row for each entry in the array.
