5.15 INSERT, UPDATE, DELETE returning values
|
| < Day Day Up > |
|
5.15 INSERT, UPDATE, DELETE returning values
DB2 UDB has introduced several new features in FixPak 4. With FixPak 4, you can use SELECT and SELECT INTO statements to retrieve result sets from SQL data-change operations (INSERT, UPDATE, and DELETE) embedded in the FROM clause. This new feature can be used to migration Oracle code using the similar feature. Example 5-36 is a sample Oracle code using RETURNING INTO statement to retrieve value after updating a table. Example 5-37 and Example 5-38 shows two ways to convert this Oracle code into DB2 code.
Example 5-36: Oracle code using RETURNING INTO
 |
Update staff Set salary =10000.0 where id =p_id returning name into p_name;
|
Example 5-37: DB2 code using SELECT INTO
|
SELECT name INTO p_name FROM NEW TABLE ( UPDATE staff SET salary = 10000.0 WHERE id = p_id);
|
Example 5-38: DB2 CODE using SELECT
|
set p_name = (SELECT name FROM NEW TABLE ( UPDATE staff SET salary = 10000.0 WHERE id = p_id));
|
|
| < Day Day Up > |
|
