5.4 Cursor conversion

< Day Day Up >

The MTK generally does a good job of converting cursors in stored procedures to DB2. Because DECLARE CURSOR syntax is not supported in inline SQL PL, some manual conversion of cursors is required for functions and triggers.

In this section, we discuss the cursors conversion in stored procedures. We show you how to convert the Oracle explicit cursor in function, and the methods of converting cursor attributes.

In general, Oracle PL/SQL and DB2 SQL PL are similar in their syntax and support for cursor operations. Table 5-2 shows the similarities/differences between the syntax.

Table 5-2: Mapping Oracle and DB2 Cursor operation
Operation	Oracle	DB2 UDB
Declaring a cursor	CURSOR cursor_name [(cursor_parameter(s))] IS select_statement;	DECLARE cursor_name CURSOR [WITH HOLD] [WITH RETURN] [TO CALLER \| TO CLIENT] FOR select-statement
Opening a cursor	OPEN cursor_name [(cursor_parameter(s))];	OPEN cursor_name [USING host-variable]
Fetching from cursor	FETCH cursor_name INTO variable(s)	FETCH [from] cursor_name INTO variable(s)
Update fetched row	UPDATE table_name SET statement(s)... WHERE CURRENT OF cursor_name;	UPDATE table_name SET statement(s)... WHERE CURRENT OF cursor_name
Delete fetched row	DELETE FROM table_name WHERE CURRENT OF cursor_name;	DELETE FROM table_name WHERE CURRENT OF cursor_name
Closing cursor	CLOSE cursor_name;	CLOSE cursor_name

5.4.1 Converting explicit cursor in procedure

An Oracle SQL cursor is defined similar to a variable in a procedure. The definition is in the stored procedure specification. Example 5-11 is a sample Oracle procedure with an explicit cursor defined in the specification.

Example 5-11: Oracle procedure with explicit cursor

 PROCEDURE get_sum_projects( v_office_id   IN  NUMBER                            ,sum_projects  OUT NUMBER) AS    v_prj   NUMBER(3);    CURSOR c1 IS       SELECT current_projects       FROM employees       WHERE office_id = v_office_id; BEGIN    sum_projects := 0;    OPEN c1;    LOOP       FETCH c1 INTO v_prj;       EXIT WHEN c1%NOTFOUND;       sum_projects := sum_projects + v_prj;    END LOOP; END;

In DB2 UDB, you must define the SQL cursor in the procedure body. To use procedure parameters within the cursor you have to define and prepare the SQL statement for the cursor. Example 5-12 shows the cursor definition and PREPARE statement. This is also the conversion of Example 5-11.

Example 5-12: DB2 store procedure with cursor

 CREATE PROCEDURE get_sum_projects ( IN  v_office_id  INTEGER                                    ,OUT sum_projects INTEGER ) LANGUAGE SQL BEGIN     DECLARE v_no_data   SMALLINT DEFAULT 0;     DECLARE v_prj       SMALLINT DEFAULT 0;     DECLARE v_sql       VARCHAR(1000);     DECLARE v_stmt      STATEMENT;     DECLARE c1          CURSOR WITH RETURN FOR v_stmt;     DECLARE CONTINUE HANDLER FOR NOT FOUND        SET v_no_data = 1;     SET sum_projects = 0;     SET v_sql = 'SELECT current_projects ' ||                 'FROM employees ' ||                 'WHERE office_id = ' || CHAR(v_office_id);     PREPARE v_stmt FROM v_sql;     OPEN c1;     FETCH c1 INTO v_prj;     WHILE (v_no_data=0) DO        SET sum_projects = sum_projects + v_prj;        FETCH c1 INTO v_prj;     END WHILE;     CLOSE c1; END!

5.4.2 Converting explicit cursor in functions and triggers

It is important to understand that DB2 triggers and functions must use inline SQL PL. Oracle cursor operations in triggers and functions, such as explicit cursors, must be converted to the corresponding inline SQL PL syntax. Section 4.11.1, "Triggers" on page 136 provides an example of converting an explicit cursor in a trigger. In the following section, we show you how to convert an Oracle explicit cursor in a function using a FOR LOOP.

Example 5-13 shows the Oracle function source code.

Example 5-13: Function with an explicit cursor in Oracle

 CREATE OR REPLACE FUNCTION CountProjects (   /* Returns the number of projects in which the employee      identified by p_emp_ID is currently engaged */   p_empID IN employees.emp_ID%TYPE) RETURN NUMBER AS   v_TotalProjects NUMBER;   -- Total number of projects   v_AccountProjects NUMBER;   -- projects for one account   CURSOR c_DeptAccts IS     SELECT dept_code, acct_id FROM employees       WHERE emp_id = p_empID; BEGIN   FOR v_AccountRec IN c_DeptAccts LOOP     -- Determine the projects for this account.     SELECT num_projects INTO v_AccountProjects       FROM accounts WHERE dept_code = v_AccountRec.dept_code       AND acct_id = v_AccountRec.acct_id;     -- Add it to the total so far.     v_Totalprojects := v_Totalprojects + v_AccountProjects;   END LOOP;   RETURN v_Totalprojects; END CountProjects;

The converted DB2 code is shown in Example 5-14.

Listing 5-14: Conversion using a FOR LOOP in DB2

 CREATE FUNCTION CountProjects(p_empID INTEGER) RETURNS INTEGER LANGUAGE SQL BEGIN ATOMIC    DECLARE v_TotalProjects INT DEFAULT 0;    DECLARE v_AccountProjects INT;    X: FOR v_DeptAccts as                      --[1]       Select dept_code, acct_id       FROM employees       WHERE emp_id = p_empID       DO          SET v_AccountProjects = (          SELECT num_projects                  --[2]          FROM accounts                WHERE dept_code = v_DeptAccts.dept_code                AND acct_id = v_DeptAccts.acct_id);          SET v_Totalprojects = v_Totalprojects + v_AccountProjects;    END FOR X;    RETURN v_Totalprojects; END!

Notes

[1]	The FOR LOOP "X" is declared and the values that will be used in the "where" clause in the "SET" statement are selected.
[2]	SELECT INTO is not supported in inline SQL. The equivalent can be achieved using the SET statement.

5.4.3 Converting cursor attributes

Oracle supports cursor attributes to get information about the current status of a cursor. SQLCODE/SQLSTATE can be used to obtain the analogous information in DB2. The following table shows how to match Oracle cursor attributes with DB2 SQLCODE/SQSTATE values.

Table 5-3: Mapping of cursor attributes
Oracle	DB2 UDB
%ISOPEN	Upon open a cursor, DB2 SQLCODE -502 is returned if the cursor is already open. On FETCH, SQLCODE -501 is returned if the cursor is not open yet. However, In DB2 procedure, you will not be able to test for any negative sqlcodes (exceptions) unless they are contained within an exception handler because control will be returned to the calling application or procedure. If %isopen is used to test if a cursor is open before closing it, it may not be needed in DB2. DB2 will close the cursor for you. The SQLSTATE associated with SQLCODE -501 is 24501. The SQLSTATE associated with SQLCODE -502 is 24502
%NOTFOUND	if (SQLCODE = 100) or if SQLSTATE = '02000'
%FOUND	if (SQLCODE = 0) or if SQLSTATE = '00000'
%ROWCOUNT	Use a counter variable to retrieve number of rows fetched from the cursor

The following examples demonstrate how to convert these Oracle attributes to DB2.

%ISOPEN

Let's consider the following Oracle code fragment that uses %ISOPEN:

    IF c1%ISOPEN THEN         fetch c1 into var1;    ELSE                            -- cursor is closed, so open it       OPEN c1;         fetch c1 into var1;    END IF;

You can implement the same logic in DB2 using CONDITION HANDLER:

    DECLARE cursor_notopen CONDITION FOR SQLSTATE 24501;    DECLARE CONTINUE HANDLER FOR cursor_notopen    BEGIN          open c1;          FETCH c1 int var1;    END;      ...    FETCH c1 into var1;

More detailed discussion of CONDITION HANDLERS is in 5.6, "Condition handling" on page 180.

%NOTFOUND

Here is an Oracle example that uses %NOTFOUND:

    OPEN cur1;    LOOP      FETCH cur1 INTO v_var1;        EXIT WHEN cur1%NOTFOUND;       ...    END LOOP;

In DB2, this can be implemented using CONDITION HANDLERS or checking the SQLCODE value:

    DECLARE SQLCODE int DEFAULT 0;    ……    OPEN c1;    L1: LOOP          FETCH c1 INTO v_var1;          IF SQLCODE = 100 THEN                  LEAVE L1;          END IF;          ...    END LOOP L1;

%ROWCOUNT

SQL %ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows.

The use of %ROWCOUNT can be demonstrated with following Oracle examples. First, let us consider the example that uses %ROWCOUNT to determine the condition for the loop:

    LOOP       FETCH c1 INTO my_ename, my_deptno;       IF c1%ROWCOUNT > 10 THEN         EXIT;       END IF;       ...    END LOOP;

This Oracle code will process only the first 10 rows for the given cursor. This logic can be implemented in DB2 using the FETCH FIRST N ROWS ONLY clause in the cursor declaration, and processing this cursor till NOT FOUND.

    DECLARE c1 CURSOR FOR        SELECT ename, deptno           FROM  emp_table        FETCH FIRST 10 ROWS ONLY;    DECLARE CONTINUE HANDLER FOR NOT FOUND       BEGIN                SET end-of-fetch = 1;       END;    L1: LOOP          FETCH c1 INTO my_ename, my_deptno;          IF end-of-fetch = 1 THEN               LEAVE L1;          END IF;          …………     END LOOP L1;

If %ROWCOUNT is used to determine how many rows from the cursor have been processed at any given time like the following Oracle code:

     LOOP        FETCH c1 INTO my_ename, my_deptno;        IF c1%ROWCOUNT > 10 THEN           ...        END IF;        ...     END LOOP;

In DB2, you would need to use local variable (counter) to store this information after each fetch from the cursor:

     DECLARE v_CURCOUNT INT DEFAULT 0;         …….         L1: LOOP               FETCH c1 INTO my_ename, my_deptno;               v_CURCOUNT = v_CURCOUNT + 1;               IF vCURCOUNT > 10 THEN               ……               END IF;               ……           END LOOP L1;

In the following example, %ROWCOUNT is used to take action if more than ten rows have been deleted:

    DELETE FROM emp_table WHERE ...    IF SQL%ROWCOUNT > 10 THEN      -- more than 10 rows were deleted       ...    END IF;

DB2 supports GET DIAGNOSTICS statement to return number of rows affected by INSERT, UPDATE, or DELETE statement:

    DECLARE rc INT DEFAULT 0;    …….    DELETE FROM emp_table WHERE ...    GET DIAGNOSTICS rc = ROW_COUNT;    IF rc > 10 THEN       -- more than 10 rows were deleted       ...    END IF;

Please note that the GET DIAGNOSTICS statement is not supported for the SELECT or SELECT INTO statement. SQLCODE will be 100 if no row is selected, SQLCODE will be 0 if one row is selected, and SQLCODE will be -811 (SQLSTATE 21000 - SQLERROR) if more then one row are selected.

%FOUND

Please note that for any SQL statement, Oracle treats it as an implicit cursor. Implicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement. The values of the implicit cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL. In the following example, %FOUND is used to insert a row if a delete succeeds:

    DELETE FROM emp WHERE empno = my_empno;    IF SQL%FOUND THEN    -- delete succeeded       INSERT INTO emp_table VALUES (my_empno, my_ename);

This code can be converted to DB2 as follow:

    DELETE FROM emp WHERE empno = my_empno;    IF SQLCODE = 0  THEN    -- delete succeeded       INSERT INTO emp_table VALUES (my_empno, my_ename);

< Day Day Up >