Reusing Dynamic SQL Statements with PREPARE and EXECUTE


Reusing Dynamic SQL Statements with PREPARE and EXECUTE

As mentioned in the previous section, EXECUTE IMMEDIATE is ideal if you have a single SQL statement that you need to execute just once. If you have a dynamic SQL statement that needs to be executed many times, however, using EXECUTE IMMEDIATE incurs the unnecessary cost of re-preparing the SQL statement. A better solution is to use PREPARE and EXECUTE separately, but first the concept of parameter markers must be introduced.

Parameter markers are useful if you have a set of very similar queries to execute, but only certain variable values change. Consider the following two UPDATE statements:

 UPDATE EMPLOYEE SET BONUS=500 WHERE EMPNO='000300'; UPDATE EMPLOYEE SET BONUS=1000 WHERE EMPNO='000340'; 

From DB2's perspective, the access plan used to update the row that contains employee 000300 is the same as the one used to fetch and update the row that has employee 000340. Hence, the value of the bonus and the employee number are irrelevant to the access plan being chosen, and the query can be reduced to the following general form:

 UPDATE EMPLOYEE SET BONUS=? WHERE EMPNO=?; 

Here, the question marks are called parameter markers and act as placeholders for variables that can be replaced with values at a later time. The ability to use parameter markers is a feature that is specific to dynamic SQL.

In SQL procedures, parameter markers can be used to avoid the overhead of preparing similar SQL statements that result in the same access plan. To do this, instead of using EXECUTE IMMEDIATE, the process is executed in two commands: PREPARE and EXECUTE. The syntax for PREPARE and EXECUTE is presented in Figures 7.4 and 7.5.

Figure 7.4. The syntax diagram for PREPARE.
 >>-PREPARE--statement-name--FROM--host-variable-------->< 

Figure 7.5. The syntax diagram for EXECUTE.
 >>-EXECUTE--statement-name--------------------------------------> >--+----------------------------------------------+------------->    |         .-,--------------------.             |    |         V                      |             |    '-INTO--+---result-host-variable-+-----------+-'            '-DESCRIPTOR--result-descriptor-name-' >--+----------------------------------------------+------------><    |          .-,-------------------.             |    |          V                     |             |    '-USING--+---input-host-variable-+-----------+-'             '-DESCRIPTOR--input-descriptor-name-' 

The PREPARE statement has two parameters. The statement-name is an identifier for referencing the prepared form of the query. The host-variable is a VARCHAR variable that holds the SQL statement text to be prepared.

The EXECUTE statement takes one or more parameters. In the least, statement-name must be specified, and it must reference a statement object that has been previously prepared. Optionally, if the SQL statement has one or more parameter markers, a comma-delimited list of host-variables can be specified in the USING clause for parameter substitution.

The INTO clause is only used for dynamic CALL statements which are discussed later in this chapter.

Consider the following example in which a company increases bonuses for only department managers. To do this, a cursor is defined for all managers to fetch their current bonuses and then increase them by the amount specified in the p_bonus_increase parameter (see Figure 7.6).

Figure 7.6. A dynamic SQL example using PREPARE and EXECUTE... USING statements.
 CREATE PROCEDURE change_mgr_bonus ( IN p_bonus_increase DECIMAL                                   , OUT p_num_changes INT )     LANGUAGE SQL     SPECIFIC change_mgr_bonus                     -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries cmb: BEGIN     DECLARE v_dynSQL      VARCHAR(200);     DECLARE v_new_bonus   DECIMAL;     DECLARE v_no_data     SMALLINT DEFAULT 0;     DECLARE v_mgrno       CHAR(6);     DECLARE v_bonus       DECIMAL; -- cursor of all employees who are also managers    DECLARE c_managers CURSOR FOR        SELECT e.empno              ,e.bonus          FROM EMPLOYEE e              ,DEPARTMENT d          WHERE e.empno=d.mgrno;    DECLARE CONTINUE HANDLER FOR NOT FOUND        SET v_no_data=1;    SET v_dynSQL = 'UPDATE EMPLOYEE SET BONUS=? WHERE EMPNO=?';    SET p_num_changes=0;    PREPARE v_stmt1 FROM v_dynSQL;           -- (1)    OPEN c_managers;    FETCH c_managers INTO v_mgrno, v_bonus;    WHILE (v_no_data=0) DO        SET p_num_changes = p_num_changes + 1;        SET v_new_bonus = v_bonus + p_bonus_increase;        EXECUTE v_stmt1 USING v_new_bonus, v_mgrno; -- (2)        -- fetch the next row to be processed        FETCH c_managers INTO v_mgrno, v_bonus;    END WHILE;    CLOSE c_managers; END cmb 

In the previous example, there are several concepts to highlight. The SQL procedure starts off with variable declarations. A statement text stored in v_dynSQL is prepared on Line (1) and uses v_stmt1 as the statement name identifier.

Within the WHILE loop, the statement name v_stmt1 is executed repeatedly on Line (2). Because the prepared statement has two parameter markers, the USING clause of EXECUTE is used to substitute the parameter values.

Tip

You can also issue EXECUTE without the USING clause if the dynamic SQL statement has no parameter markers.


Before running the SQL procedure, take a look at the current bonus values for department managers and note their values.

 SELECT e.empno, e.bonus FROM EMPLOYEE e, DEPARTMENT d WHERE e.empno=d.mgrno EMPNO  BONUS ------ ----------- 000010     1000.00 000020      800.00 000030      800.00 000050      800.00 000060      500.00 000070      700.00 000090      600.00 000100      500.00 

Then, call the SQL procedure with the bonus increase value. For example, in LUW you can use the Command Line Process (CLP):

 CALL CHANGE_MGR_BONUS (100,?) 

The output should look something like the following:

 Value of output parameters   --------------------------   Parameter Name  : P_NUM_CHANGES   Parameter Value : 8   Return Status = 0 

In this example, eight rows were updated as indicated by P_NUM_CHANGES. To further verify that the result is correct, query the data once again.

 SELECT e.empno, e.bonus FROM EMPLOYEE e, DEPARTMENT d WHERE e.empno=d.mgrno EMPNO  BONUS ------  ----------- 000010    1100.00 000020     900.00 000030     900.00 000050     900.00 000060     600.00 000070     800.00 000090     700.00 000100     600.00 

Figure 7.6 was used to illustrate repeated EXECUTE statements with one PREPARE statement. This "PREPARE once, EXECUTE many" design will offer much better performance as compared to running a PREPARE and EXECUTE or EXECUTE IMMEDIATE for the same statement over and over again.



    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