Selecting Data from UPDATE, INSERT, or DELETE Statements
When you are working with complex data manipulation, it can be useful to know how much of an impact the changes have had. In earlier versions of DB2 LUW, you had to issue two separate SQL statements. The first statement would be used to determine what the impact of the INSERT, UPDATE, or DELETE would be, and the second would actually execute the command. This was tedious and required extra code and execution time. You are now able to issue one command where both the data alterations are executed and changes just made can be retrieved.
An INSERT, UPDATE, or DELETE (IUD) statement can now be used as the input for a subselect statement by using the NEW_TABLE table function. The NEW_TABLE function allows your SELECT statements to work with the changes made by the IUD statement as if it were a temporary data table.
The simple example in Figure 5.9 will return the list of all employees, their jobs, and their new salary after all employees with a salary greater than $100,000 receive a pay cut. A more complex example is given in Figure 5.10.
Figure 5.9. An example of a SELECT against an UPDATE on LUW.
SELECT empid, job, salary FROM NEW_TABLE(UPDATE employee WHERE salary > 100000 SET salary = salary * .9) ORDER BY salary
Figure 5.10. An example of a SELECT against an UPDATE on LUW.
CREATE PROCEDURE upd_raise2 ( IN p_paychange DEC(4,2) ,OUT p_littleCount INTEGER) LANGUAGE SQL SPECIFIC upd_raise2 DYNAMIC RESULT SETS 1 ur2: BEGIN -- Declare variables -- Declare returncode DECLARE SQLSTATE CHAR(5); -- Procedure logic DECLARE c_big CURSOR FOR SELECT empno, salary FROM FINAL TABLE(UPDATE employee SET salary = salary * (1 + 10 * p_paychange) WHERE salary >= 1000000); DECLARE c_little CURSOR FOR SELECT COUNT(*) FROM FINAL TABLE(UPDATE employee SET salary = salary * (1 - p_paychange) WHERE salary < 1000000); OPEN c_little; -- (1) FETCH c_little into p_littleCount; -- (2) CLOSE c_little; OPEN c_big; -- (3) END ur2
The example opens two cursors. Both of them update the table and count the number of rows that were affected. The first cursor, c_big, gives a raise to all employees who earn $1 million or more. The second cursor, c_little, reduces the salaries of all the remaining employees. When the first cursor is opened on Line (1), the UPDATE statement is applied to the employee table. The UPDATE statement occurs before the SELECT statement and before a single row is fetched. The 'FINAL TABLE' component of the SQL statement obtains the updated results after all triggers and referential constraints have been applied. It is important to remember that even if you do not fetch any rows, the UPDATE statement (or DELETE or INSERT, depending on the query) will have already occurred. The use of the 'FINAL TABLE' command can be quite powerful because it allows you to work with the results of SQL that manipulates your data without having to create multiple cursors. More information on the use of the NEW TABLE clause can be found in the online help under 'FINAL TABLE'.
The FETCH statement on Line (2) will return the number of employees who received a pay cut. The SELECT is taken against the result set of the UPDATE statement in the FINAL TABLE clause. If no rows had been updated, then the SELECT would have been applied against an empty transition table.
When the second cursor is opened on Line (3), the second UPDATE statement will occur. Even though the stored procedure did not fetch any rows but instead passed the cursor back to the calling program, the UPDATE would have already taken place. It is not encouraged that you pass cursors like this back to other procedures or programs because the caller may not fully understand the impact the cursor may have had. The second UPDATE statement will be applied to the table when the c_big cursor is opened on Line (3).
DB2 for iSeries does not allow INSERT or UPDATE statements in the FROM clause of a query.
DB2 for zSeries allows a SELECT statement to include an INSERT statement as part of the FROM clause. This works similarly to DB2 for LUW, and the FINAL TABLE clause is also required. At this time, only INSERT statements are supported with the FINAL TABLE clause.
Because of these restrictions, the example procedure upd_raise2 in Figure 5.10 will not work on iSeries or zSeries; however, it can be re-written using the GET DIAGNOSTICS statement (discussed in Chapter 6, "Condition Handling") as shown in Figure 5.11.
Figure 5.11. The procedure upd_raise2 rewritten for iSeries and zSeries.
CREATE PROCEDURE upd_raise2iz ( IN p_paychange DEC(4,2) ,OUT p_littleCount INTEGER) LANGUAGE SQL SPECIFIC upd_raise2iz -- applies to iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries DYNAMIC RESULT SETS 1 ur2iz: BEGIN -- Declare variables DECLARE v_rows INT DEFAULT 0; -- Declare returncode DECLARE SQLSTATE CHAR(5); -- Procedure logic DECLARE c_big CURSOR FOR SELECT empno, salary FROM employee WHERE salary >= 1000000; UPDATE employee SET salary = salary * (1 + 10 * p_paychange) WHERE salary >= 1000000; -- (1) UPDATE employee SET salary = salary * (1 - p_paychange) WHERE salary < 1000000; -- (2) GET DIAGNOSTICS v_rows = ROW_COUNT; -- (3) SET p_littleCount = v_rows; OPEN c_big; -- (4) END ur2iz
The UPDATE statements on Lines (1) and (2) apply the updates to the database. The GET DIAGNOSTICS statement on Line (3) returns the number of rows affected by the previous statement, and the OPEN statement on Line (4) will open a cursor with rows that were updated by the first update.