Selecting Data from UPDATE, INSERT, or DELETE Statements


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.



    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net