5.3 Dynamic SQL

< Day Day Up >

In Chapter 4., "Porting with MTK" on page 63, we show you a conversion example of the Oracle DBMS_SQL package, which MTK does not support. You can reference to DBMS_SQL with the dynamic SQL in DB2 UDB. In this section, we present additional examples that show how.

Example 1 - get_emp_name

Example 5-5 and Example 5-6 are two Oracle stored procedures use dynamic SQL. Example 5-5 uses the DBMS_SQL package.

Example 5-5: PL/SQL procedure with usage of DBMS_SQL

 CREATE PROCEDURE get_emp_name_v1(emp_id NUMBER) AS     cursor_name     INTEGER;     rows_processed  INTEGER;     sql_stmt        VARCHAR2(1000); BEGIN     cursor_name := dbms_sql.open_cursor;     sql_stmt := 'SELECT last_name FROM employees WHERE emp_id = :x';     DBMS_SQL.PARSE(cursor_name, sql_stmt, dbms_sql.native);     DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', emp_id);     rows_processed := dbms_sql.execute(cursor_name);     DBMS_SQL.close_cursor(cursor_name); EXCEPTION WHEN OTHERS THEN     DBMS_SQL.CLOSE_CURSOR(cursor_name); END;

Example 5-6: PL/SQL procedure with usage of native dynamic SQL

 CREATE OR REPLACE PROCEDURE get_emp_name_v2(emp_id IN NUMBER) AS    sql_stmt   VARCHAR2(1000);    v_result   VARCHAR2(20); BEGIN    sql_stmt := 'SELECT last_name FROM employees WHERE emp_id = :x';    EXECUTE IMMEDIATE sql_stmt       INTO v_result       USING emp_id;    dbms_output.put_line(v_result.last_name); END;

Example 5-6 shows a procedure with the same behavior, however, it is with dynamic SQL, which is available since Oracle version 8.

When using MTK to convert the procedure get_emp_name_v2(), MTK will report that the EXECUTE IMMEDIATE command cannot be converted because MTK cannot guarantee the correctness of converted dynamic SQL (only a true runtime test can determine correctness.). DB2 supports dynamic SQL in procedures using the same type of syntax supported in Oracle 8 (Example 5-6). If the dynamic SQL statement is an INSERT, UPDATE, or DELETE statement, conversion to DB2 is usually straight forward. If the dynamic statement is a SELECT, however, it needs to be converted to use a dynamic cursor in DB2 (as shown in Example 5-7).

Example 5-7: SQL PL procedure with native dynamic SQL

 CREATE PROCEDURE get_emp_name_v2 ( IN emp_id FLOAT) LANGUAGE SQL BEGIN     DECLARE v_dyn_sql   VARCHAR(1000);     DECLARE v_sql_stmt  STATEMENT;     DECLARE c_employees CURSOR FOR v_sql_stmt;     SET v_dyn_sql = 'SELECT last_name FROM employees WHERE emp_id = '                     || CHAR(emp_id);     PREPARE v_sql_stmt FROM v_dyn_sql;     OPEN c_employees;     -- FETCH ...     CLOSE c_employees; END!

Example 2: update_emp_office

Example 5-8 shows you a DB2 stored procedure with a dynamic UPDATE statement.

Example 5-8: Dynamic UPDATE with EXECUTE IMMEDIATE

 CREATE PROCEDURE update_emp_office_v1 ( IN v_emp_id FLOAT                                        ,IN v_office_id FLOAT                                        ,OUT v_num_changes INTEGER) LANGUAGE SQL BEGIN     DECLARE v_dyn_sql    VARCHAR(1000);     SET v_dyn_sql = 'UPDATE employees' ||                     ' SET office_id = ' || CHAR(v_office_id) ||                     ' WHERE emp_id = ' || CHAR(v_emp_id);     EXECUTE IMMEDIATE v_dyn_sql;     GET DIAGNOSTICS v_num_changes = row_count; END!

In Example 5-5, the variable rows_processed contained the number of rows affected by the dynamic SQL statement. In DB2, the same can be done with GET DIAGNOSTICS. With the GET DIAGNOSTICS statement you get the number of row changed due to the last INSERT, UPDATE, or DELETE.

You use EXECUTE IMMEDIATE if the SQL statement only needs to be executed just once or infrequently. If the SQL statement needs to be executed repeatedly, you should use the PREPARE and EXECUTE statements. With the EXECUTE statement you can use parameter marker. Please note that the EXECUTE statement cannot be used with a SELECT or VALUES statement.

Example 5-9 demonstrates the use of a dynamic SQL statement using PREPARE and EXECUTE instead of EXECUTE IMMEDIATE.

Example 5-9: Dynamic UPDATE with EXECUTE and PREPARE

 CREATE PROCEDURE update_emp_office_v2 ( IN v_emp_id FLOAT                                        ,IN v_office_id FLOAT                                        ,OUT v_num_changes INTEGER) LANGUAGE SQL BEGIN     DECLARE v_dyn_sql    VARCHAR(1000);     DECLARE v_stmt       STATEMENT;     SET v_dyn_sql = 'UPDATE employees' ||                     ' SET office_id = ?' ||                     ' WHERE emp_id = ?';     PREPARE v_stmt FROM v_dyn_sql;     EXECUTE v_stmt USING v_office_id, v_emp_id;     GET DIAGNOSTICS v_num_changes = row_count; END!

Example 3: get_max_band

The next example shows you a user defined function (UDF) with dynamic SQL written in Java. The function uses the invoker's database connection with all its authentications. The prepare is followed by the assignment of an input variable (called inOfficeID) to the value of the parameter marker. The SQL statement may contain a full-select.

Example 5-10: Java User Defined Function with dynamic SQL

 import COM.ibm.db2.app.*; import java.sql.*; public class UDFemp extends UDF {   public void maxBand(int inOfficeID, String outBand)     throws Exception     {       try       {         // Get caller's connection to the database         Connection con =           DriverManager.getConnection("jdbc:default:connection");         String query = "SELECT max(band) " +                        "FROM employees " +                        "WHERE office_id = ?";         PreparedStatement stmt = con.prepareStatement(query);         stmt.setInt(1, inOfficeID);         ResultSet rs = stmt.executeQuery();        while(rs.next())        {          outBand = rs.getString(1);        }        set(2, outBand);        rs.close();        stmt.close();        con.close();      }      catch (SQLException sqle)      {       setSQLstate("38999");       setSQLmessage("SQLCODE = " + sqle.getSQLState());       return;      }     } }

The corresponding CREATE FUNCTION statement for this Java UDF is as follows:

     CREATE FUNCTION get_max_band(INTEGER)     RETURNS CHAR     EXTERNAL NAME 'UDFemp!maxBand'     FENCED     CALLED ON NULL INPUT     VARIANT     READS SQL DATA     PARAMETER STYLE DB2GENERAL     LANGUAGE JAVA     NO EXTERNAL ACTION!

A detailed description of external routines is in the DB2 UDB Application Development Guide: Programming Server Applications.

< Day Day Up >