Introduction

This article series compares the IBM DB2 9.5 SQL procedure language, also known as SQL PL, with the IBM solidDB 6.3 SQL procedure language. Both DB2 and solidDB procedures are compiled and parsed once and then stored in the database for future execution. Although there are similarities between these languages, there are also notable differences.

Part 1

• Structure of SQL procedures
• Parameters and calling SQL procedures
• Variables and assignments in SQL procedures
• Procedure body in SQL procedures
• Cursors in SQL procedures
• Dynamic SQL procedures

Part 2

• Conditional statements in SQL procedures
• Looping statements in SQL procedures
• Error handling in SQL procedures
• Returning results sets in SQL procedures
• Miscellaneous other information regarding SQL procedures

Conditional statements in SQL procedures

This section describes the differences in the conditional statements of the DB2 and solidDB SQL procedures.

Conditional statements in DB2 SQL procedures

There are two types of conditional statements supported in SQL PL procedures: CASE and IF.

CASE statements

Listing 1 shows an example of an SQL procedure with a CASE statement with a simple-case-statement-when-clause:

            
CREATE PROCEDURE UPDATE_DEPT (IN p_workdept) 
LANGUAGE SQL 
BEGIN 
DECLARE v_workdept CHAR(3); 
SET v_workdept = p_workdept; 
CASE v_workdept 
        WHEN ’A00’ THEN 
           UPDATE department SET deptname = ’D1’; 
        WHEN ’B01’ THEN 
            UPDATE department SET deptname = ’D2’; 
        ELSE UPDATE department SET deptname = ’D3’; 
END CASE 
END 
            

IF statements

The IF statement is logically similar to the CASE statement. There are three forms of the IF statement:

• IF THEN
• IF THEN ELSEIF
• IF THEN ELSE

The IF statement might include the ELSEIF or ELSE clause; an END IF clause is required to indicate the end of the statement. Listing 2 shows an example procedure that contains an IF statement.

            
CREATE PROCEDURE UPDATE_SAL (IN empNum CHAR(6), INOUT rating SMALLINT) 
LANGUAGE SQL 
BEGIN 
    IF rating = 1 THEN 
          UPDATE employee SET salary = salary * 1.10, bonus = 1000 
                 WHERE empno = empNum; 
    ELSEIF rating = 2 THEN 
              UPDATE employee SET salary = salary * 1.05, bonus = 500 
                  WHERE empno = empNum; 
    ELSE
            UPDATE employee SET salary = salary * 1.03, bonus = 0 
                  WHERE empno = empNum; 
    END IF; 
END
    

Conditional statements in solidDB procedures

Like for DB2, there are three forms of the IF statement on solidDB:

• IF THEN
• IF THEN ELSEIF
• IF THEN ELSE

They are equivalent to the IF statement used with DB2. However, unlike DB2, the END IF statement does not require a semicolon.

Although the CASE statement is not supported in solidDB procedures, it can easily be replaced with an IF statement. This is demonstrated in Table 1.

CREATE PROCEDURE update_dept (IN _workdept)
LANGUAGE SQL 
BEGIN 
DECLARE v_workdept CHAR(3); 
SET v_workdept = p_workdept; 
CASE 
    WHEN v_workdept = ’A00’ THEN
       UPDATE department SET 
           deptname = ’D1’; 
    WHEN v_workdept = ’B01’ THEN 
       UPDATE department SET 
               deptname = ’D2’; 
      ELSE UPDATE department SET deptname =
         ’D3’; 
END CASE 
END 

“CREATE PROCEDURE update_dept (IN  
           p_workdept CHAR(3))
  BEGIN
      DECLARE v_workdept CHAR(3);
      v_workdept := p_workdept;
      IF v_workdept = ‘A00’ THEN 
         EXEC SQL EXECDIRECT UPDATE 
            department SET deptname = ‘D1’;
      ELSEIF
          v_workdept  = ‘B01’ THEN
               EXEC SQL EXECDIRECT UPDATE 
                   department SET deptname
                      = ‘D2’;
      ELSE
           EXEC SQL EXECDIRECT UPDATE
               Department SET deptname =
                          ‘D3’;
      END IF
   END”;   

Back to top

Looping statements in SQL procedures

This section describes the differences in the looping statements of the DB2 and solidDB SQL procedures.

Looping statements in DB2 SQL procedures

The following looping statements are supported in SQL PL:

• FOR
• LOOP
• REPEAT
• WHILE

The FOR statement

The FOR statement is used to iterate over rows of a defined result set. When a FOR statement is executed, a cursor is implicitly declared such that for each iteration of the FOR loop, the next row is the result set if fetched. Looping continues until there are no rows left in the result set. The FOR loop corresponds to cursor processing and the WHILE loop in solidDB procedures.

Listing 3 shows an example of an SQL procedure that contains only a simple FOR statement.

            
CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN ATOMIC 
   DECLARE fullname CHAR(40); 
   FOR v AS cur1 CURSOR FOR 
      SELECT firstnme, midinit, lastname FROM employee 
   DO
          SET fullname = v.lastname || ’,’ || v.firstnme ||’ ’ || v.midinit; 
          INSERT INTO tnames VALUES (fullname); 
  END FOR; 
END
    

The LOOP statement

The LOOP statement has no terminating condition clause. It defines a series of statements that are executed repeatedly until a transfer of control statement forces the flow of control to jump to some point outside of the loop. The LOOP statement is generally used in conjunction with one of the following transfer control statements:

• LEAVE
• GOTO
• ITERATE
• RETURN

Listing 4 shows an example of a LOOP statement.

            
CREATE PROCEDURE ITERATOR() 
LANGUAGE SQL 
BEGIN 
   DECLARE v_deptno CHAR(3); 
   DECLARE v_deptname VARCHAR(29); 
   DECLARE at_end INTEGER DEFAULT 0; 
   DECLARE not_found CONDITION FOR SQLSTATE ’02000’; 
   DECLARE c1 CURSOR FOR SELECT deptno, deptname
           FROM department ORDER BY deptno; 
   DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; 
   OPEN c1; 
   ins_loop: LOOP 
           FETCH c1 INTO v_deptno, v_deptname; 
           IF at_end = 1 THEN
              LEAVE ins_loop; 
           ELSEIF v_dept = ’D11’ THEN 
               ITERATE ins_loop; 
          END IF; 
          INSERT INTO department (deptno, deptname) VALUES (’NEW’, v_deptname); 
   END LOOP; 
   CLOSE c1; 
END 

The WHILE statement

The WHILE statement defines a set of statements to be executed until a condition that is evaluated at the beginning of the WHILE loop is false. The while-loop-condition is evaluated before each iteration of the loop. The DB2 SQL WHILE loop is equivalent to the WHILE-LOOP in solidDB procedures.

Listing 5 shows an example of an SQL procedure with a WHILE loop.

            
CREATE PROCEDURE sum_mn (IN p_start INT, IN p_end INT , OUT p_sum INT) 
SPECIFIC sum_mn 
LANGUAGE SQL 
smn: BEGIN
   DECLARE v_temp INTEGER DEFAULT 0; 
   DECLARE v_current INTEGER; 
   SET v_current = p_start; 
   WHILE (v_current <= p_end)  DO 
       SET v_temp = v_temp + v_current; 
       SET v_current = v_current + 1; 
   END WHILE; 
   p_sum = v_current; 
END smn; 
    

The REPEAT statement

The REPEAT statement defines a set of statements to be executed until a condition that is evaluated at the end of the REPEAT loop is true.

Listing 6 shows an example of the REPEAT loop.

            
CREATE PROCEDURE sum_mn2 (IN p_start INT, IN p_end INT, OUT p_sum INT)
SPECIFIC sum_mn2 
LANGUAGE SQL 
smn2: BEGIN
 DECLARE v_temp INTEGER DEFAULT 0; 
 DECLARE v_current INTEGER; 
 SET v_current = p_start; 
 REPEAT SET v_temp = v_temp + v_current; 
    SET v_current = v_current + 1; 
    UNTIL (v_current > p_end) 
 END REPEAT; 
END 
    

The transfer of control statements

There are four statements that can transfer control within a DB2 SQL procedure. These statements are:

• GOTO: Transfers control to a specific label in the procedure
  
  
• ITERATE: Used to transfer control to beginning of a labeled loop
  
  
• LEAVE: Used to transfer control out of a loop or a compound statement
  
  
• RETURN: Returns an integer and the flow of control to the caller of the stored procedure

Only the LEAVE and RETURN statements are supported in solidDB procedures.

Looping statements in solidDB procedures

The WHILE-LOOP is the only looping construct supported in solidDB procedures. Most DB2 looping constructs can be translated into the solidDB WHILE-LOOP. The WHILE-LOOP associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP, as shown in Listing 7.

            
WHILE condition LOOP
    statement_list;
END LOOP
    

Before each iteration of the loop, the condition is evaluated. If the condition evaluates to TRUE, the statement list is executed, then control resumes at the top of the loop. If the condition evaluates to FALSE or NULL, the loop is bypassed, and control passes to the next statement.

Listing 8 shows an example of using an iterating WHILE-LOOP.

            
WHILE total <= 25000 LOOP
   ...
   total := total + salary;
END LOOP

Logic using REPEAT and LOOP constructs on DB2 should be rewritten to a WHILE-LOOP construct on solidDB.

Table 2 compares a FOR loop in a DB2 procedure and the equivalent processing on solidDB.

CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN ATOMIC 
   DECLARE fullname CHAR(40); 
   FOR v AS cur1 CURSOR FOR 
      SELECT firstnme, midinit, lastname FROM
             employee 
   DO
          SET fullname = v.lastname || ’,’ 
           || v.firstnme ||’ ’ ||
                        v.midinit; 
          INSERT INTO tnames VALUES
             (fullname); 
  END FOR; 
END
    

“CREATE PROCEDURE P
BEGIN
   DECLARE fullname CHAR(40);
   DECLARE fn CHAR(15);
     DECLARE ln CHAR (20);
   DECLARE mi CHAR (2);    
   EXEC SQL PREPARE cur1
         SELECT firstnme, midinit, lastname 
           FROM EMPLOYEE;
    EXEC SQL PREPARE cur2
         INSERT INTO tnames VALUES(?); 
    EXEC SQL EXECUTE cur1 INTO (fn, mi,
                    ln);
    EXEC SQL FETCH cur1;
    WHILE (SQLSUCCESS) LOOP
       SET fullname = ln + ’,’ + fn + ’ ’ +
                  mi;
       EXEC SQL EXECUTE cur2 USING
            (fullname);
       EXEC SQL FETCH cur1;
    END LOOP;
  END”;    
    

Leaving loops

It might be necessary to force the procedure to leave a loop prematurely. Like on DB2, you can implement this on solidDB using the LEAVE keyword, as shown in Listing 9.

            
WHILE total < 25000 LOOP
   total := total + salary;
   IF exit_condition THEN
      LEAVE;
   END IF
END LOOP
statement_list2
            

Back to top

Error handling in SQL procedures

This section describes the differences in error handling structures of the DB2 and solidDB SQL procedures.

Error handling in DB2 SQL procedures

SQLCODE and SQLSTATE variables

To use the SQLCODE and SQLSTATE values, you must declare these variables as shown in Listing 10.

            
DECLARE SQLCODE INTEGER DEFAULT 0; 
DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;
    

DB2 implicitly sets these variables whenever a statement is executed. If a statement raises a condition for which a handler exists, the values of the SQLSTATE and SQLCODE variables are available at the beginning of the handler execution. It is a common practice to copy the values of SQLSTATE and SQLCODE into local variables in the first statement of the handler, as shown in Listing 11.

            
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND 
SET retcode = SQLCODE; 
    executable-statements 
END 
            

Condition handlers

Condition handlers determine the behavior of your SQL procedure when a condition occurs. A condition might be one of the following:

• SQL warning (SQLWARNING)
• SQL error (SQLEXCEPTION)
• Not found condition (NOT FOUND)
• A specific SQLCODE or SQLSTATE code

You can declare one or more condition handlers in your SQL procedure.

If a statement in your SQL procedure raises an SQLWARNING or NOT FOUND condition and if you have declared a handler for the respective condition, DB2 passes control to the corresponding handler. Listing 12 shows a handler that will exit the procedure when SQL warnings and the not found conditions are encountered.

            
DECLARE EXIT HANDLER FOR SQLWARNING, NOT FOUND
 BEGIN                
        GET DIAGNOSTICS EXCEPTION 1 err_str = MESSAGE_TEXT;                
        SET sql_err = SQLCODE;                
        SET message = 'Unknown Error - ' || err_str;            
  END;
    

If you have not declared a handler for a condition, DB2 passes control to the next statement in the SQL procedure body. However, if the SQLCODE and SQLSTATE variables have been declared, they will contain the corresponding values for the condition, and you can check them.

If a statement in your SQL procedure raises an SQLEXCEPTION condition and if you declared a handler for the specific SQLSTATE or the SQLEXCEPTION condition, DB2 passes control to that handler. If the SQLSTATE and SQLCODE variables have been declared, their values will be reset to 00000 and 0 respectively after successful execution of the handler.

Listing 13 demonstrates how a specific error can transfer control to the exit handler where a user-defined error message is returned depending on the value of the SQLCODE.

            
DECLARE EXIT HANDLER FOR SQLSTATE '23505', SQLSTATE '23503'
 BEGIN               
        GET DIAGNOSTICS EXCEPTION 1 err_str = MESSAGE_TEXT;                
         SET sql_err = SQLCODE;                
         IF sql_err = -803 THEN 
              SET message = 'Duplicate Account Code';                
         ELSE
               SET message = 'Unknown User Specified';                
         END IF;     
   END;       
    

If a statement in your SQL procedure raises an SQLEXCEPTION condition, and you have not declared a handler for the specific SQLSTATE or for the SQLEXCEPTION condition, DB2 terminates the SQL procedure and returns to the caller.

The SIGNAL statement

The SIGNAL statement is used to explicitly raise an error and force the invocation of a handler. It causes an error or warning to be returned with the specified SQLSTATE, along with optional message text. Any valid SQLSTATE value can be used in the SIGNAL statement, including user-defined SQLSTATEs and conditions. However, it is recommended that you define user-defined SQLSTATEs based on specific ranges reserved for applications. SQLSTATE classes that begin with the characters 7 through 9, or I through Z are available for this purpose.

Listing 14 shows an example of the SIGNAL statement.

            
CREATE PROCEDURE submit_order (IN onum INTEGER, IN cnum INTEGER, IN pnum INTEGER,  
               IN qnum INTEGER)
SPECIFIC SUBMIT_ORDER
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
      DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;
      DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
             SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'Customer number is not known';
      INSERT INTO ORDERS (ORDERNO, CUSTNO, PARTNO, QUANTITY) VALUES (ONUM, CNUM, PNUM,
                  QNUM);
END
    

In Listing 14, an attempt to insert into the table fails due to a foreign key constraint violation (SQLSTATE 23503) on the customer number. The INSERT error transfers control to the exit handler for that SQLSTATE code. The exit handler signals a user-defined error code (SQLSTATE 75002) and message text statement. The user-defined error code is returned as an OUT parameter upon exiting the procedure.

The RETURN statement

In an SQL procedure, you can set the status to be returned to the calling program. The status is an integer that indicates the success of the procedure. If you do not set the status, DB2 sets the status to 0 or -1, depending on the value of the SQLCODE. DB2 sets the return status to 0 for an SQLCODE that is greater than or equal to 0 or to -1 for an SQLCODE less than 0.

The following statement shows how a procedure that calls another procedure can retrieve the return status of the nested procedure: GET DIAGNOSTICS RETVAL = RETURN_STATUS ;

Error handling in solidDB procedures

When errors are encountered in a procedure, several error variables are automatically populated. These error variables, which do not have to be explicitly declared, are SQLSUCCESS, SQLERRNUM, and SQLERRSTR.

Depending on the type of error encountered and how the procedure is coded, errors can be handled in any of several ways. Errors that are generated during the PREPARE phase of execution, such as a table does not exist error, are raised immediately and cannot be trapped. In this case, the procedure exits immediately and returns a failing status to the caller. The SQLERRNUM and SQLERRSTR variables are automatically populated with values for the error.

Errors that are raised during the EXECUTE or FETCH phase of execution, such as a unique key constraint violation, can be returned in more than one way. If an error is raised and the RETURN SQLERROR or WHENEVER SQLERROR clause is used, SQLERRNUM and SQLERRSTR are automatically populated, and the procedure returns the error code and a failed execution status to the caller. Alternatively, the procedure can be written to trap the values of SQLERRNUM and SQLERRSTR and return them to the caller by way of a user-defined OUT or RETURNS variables. In the latter case, the procedure returns a successful execution status to the caller.

The following sections further describe each error handling construct in solidDB procedures.

SQLSUCCESS

The result of each EXEC SQL statement executed inside a procedure body is stored into the variable SQLSUCCESS. As mentioned previously, this variable is automatically generated for each procedure. If the previous SQL statement was successful, a value of 1 is stored into SQLSUCCESS. After a failed SQL statement, a value of 0 is stored into SQLSUCCESS.

The value of SQLSUCCESS is often used to determine when the cursor has reached the end of the result set, as shown in Listing 15.

            
EXEC SQL FETCH sel_tab;
WHILE SQLSUCCESS LOOP
    EXEC SQL FETCH sel_tab;
END LOOP
    

When the FETCH cursor statement fails and does not find another row to retrieve, the value of SQLSUCCESS is set to 0 and the WHILE LOOP ends.

SQLERRNUM

This variable contains the error code of the latest SQL statement executed. It is automatically generated for each procedure. After successful execution, SQLERRNUM contains 0. This variable is similar to the SQLCODE on DB2.

In the solidDB example in the right column of Table 3, the status of the INSERT statement is checked using the SQLSUCCESS variable. If the statement is not successful, the value of SQLERRNUM is captured and returned to the caller using the RETURN statement and the RETURNS clause. Table 3 also shows that a user-defined error message is also returned. In this scenario, the procedure completes successful execution and returns a return code of 0 to the caller.

In the case of the DB2 example in the left column of Table 3, if a particular SQL statement fails and generates an error, control is passed to the condition handler. A handler is declared for two specific errors identified by SQLSTATE. When the INSERT statement fails with the error code specified in the DECLARE EXIT HANDLER statement, control passes to the handler, and the procedure is exited. The error code and message are returned to the caller as OUT parameters.

CREATE PROCEDURE account(IN…, OUT ret_val1 VARCHAR(20), OUT ret_val2 INT)
…
BEGIN
  DECLARE SQLCODE ….;
  DECLARE EXIT HANDLER FOR SQLSTATE 
    '23505', SQLSTATE '23503'
  BEGIN               
         SET sql_err = SQLCODE;                
         IF sql_err = -803 THEN 
              SET msg = 'Duplicate Account 
                Code';                
         ELSE
             SET msg = 'Unknown User
                   Specified';                
         END IF;   
        INSERT statement…. …
  END;       
  SET ret_val1 = msg;
  SET ret_val2 = sql_err
END
    

“CREATE PROCEDURE account (..) RETURNS 
      (msg VARCHAR, sql_err INT)
BEGIN  
    EXEC SQL PREPARE cursor INSERT
                 statement….
    EXEC SQL EXECUTE …insert statement….
    IF NOT SQLSUCCESS THEN
         sql_err := SQLERRNUM;
    IF SQLERRNUM = 10005 THEN
            msg := ‘Duplicate Account 
              Code’;
     ELSEIF SQLERRNUM = 10029 THEN
            msg := ‘Unknown User
                    Specified’;
     END IF;
    . . .
   RETURN;
END”;
    

Note: DB2 and solidDB error codes differ.

SQLERRSTR

This variable contains the error string from the last failed SQL statement.

On DB2, the error string of the latest failed SQL statement can be retrieved using the GET DIAGNOSTICS statement, as shown in Table 4.

DECLARE err_str VARCHAR(40);
GET DIAGNOSTICS EXCEPTION 1 err_str =  
           MESSAGE_TEXT; 

DECLARE err_str VARCHAR(40);
err_str := SQLERRSTR;

SQLROWCOUNT

After the execution of the UPDATE, INSERT, and DELETE statements, an additional variable is available to check the result of the statement. Variable SQLROWCOUNT contains the number of rows affected by the last statement.

On DB2 the row count of the last executed UPDATE, INSERT, or DELETE statement can be retrieved using the GET DIAGNOSTICS statement, as shown in Table 5.

DELETE FROM T; 
GET DIAGNOSTICS v_rcount = ROW_COUNT;
    

DELETE FROM T;
SET v_rcount = SQLROWCOUNT;
    

SQLERROR

To generate user errors from procedures, you can use the SQLERROR variable to return an actual error string that caused the statement to fail to the calling application, as shown in Listing 16.

            
RETURN SQLERROR 'error string'
RETURN SQLERROR char_variable
    

The error is returned in the following format: User error: error_string. This statement is the closest solidDB equivalent to the DB2 SIGNAL statement.

In Table 6, the INSERT into the Orders table fails due to a foreign key constraint violation on custno. When the RETURN SQLERROR statement is executed, processing halts and the error code 10029 is returned to the caller. The procedure does not complete successfully, and the user-defined message of Customer number is not known is also returned.

In the DB2 procedure example in Table 6, the SIGNAL statement is used to generate a user-defined SQLSTATE and a message that is returned to the caller through the SQLCA.

CREATE PROCEDURE submit_order (IN onum
   INTEGER, IN cnum INTEGER, IN pnum
           INTEGER, IN qnum INTEGER)
SPECIFIC SUBMIT_ORDER
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
      DECLARE SQLSTATE CHAR(5) DEFAULT 
                  ‘00000’;
      DECLARE EXIT HANDLER FOR SQLSTATE
        VALUE '23503' SIGNAL SQLSTATE
        '75002' SET MESSAGE_TEXT = 
            'Customer number is not known';                        
      INSERT INTO ORDERS (ORDERNO, CUSTNO, 
         PARTNO, QUANTITY) VALUES (ONUM,
              CNUM, PNUM, QNUM);
END
    

“CREATE PROCEDURE submit_order (onum 
       INTEGER, cnum INTEGER, pnum INTEGER,
           qnum INTEGER) 
 BEGIN
   DECLARE err_str VARCHAR(30);
    EXEC SQL PREPARE c1 
        INSERT INTO orders (orderno, 
           custno, partno, quantity) 
               VALUES(?,?,?,?);   
     EXEC SQL EXECUTE c1 USING (onum, 
         cnum, pnum, qnum);
     IF SQLERRNUM = 10029 THEN
        err_str := ‘Customer number is not 
                known’; 
        RETURN SQLERROR err_str;
    END IF;
    EXEC SQL CLOSE c1;
    EXEC SQL DROP c1;
END”;
    

SQLERROR OF cursorname

This construct is used to return to the calling application the actual error that caused the statement to fail, as shown in Listing 17.

            
EXEC SQL PREPARE cursorname sql_statement;
EXEC SQL EXECUTE cursorname;
IF NOT SQLSUCCESS THEN
   RETURN SQLERROR OF cursorname;
END IF
    

Processing stops immediately when this statement is executed, and the procedure return code to the caller is the value of SQLERROR.

Table 7 shows the use of the solidDB RETURN SQLERROR OF cursorname statement compared to exception handling on DB2.

CREATE PROCEDURE tabs_in_schema (schema_nm
        VARCHAR(8), OUT nr_of_rows INT)
language SQL
BEGIN
    DECLARE tab_nm VARCHAR(128);
    DECLARE SQLSTATE CHAR(5) DEFAULT 
               '00000';
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE v_sqlcode integer default 0;  
    DECLARE rownum INTEGER;
    DECLARE rowcount_not_one CONDITION FOR 
            SQLSTATE '90001';
    DECLARE sel_tab CURSOR FOR
      SELECT tabname FROM syscat.tables 
          WHERE tabschema = schema_nm;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN  
          SET v_sqlcode=SQLCODE;
          GET DIAGNOSTICS rownum = 
               ROW_COUNT;
          IF rownum <> 1 THEN
             SIGNAL rowcount_not_one  SET
                MESSAGE_TEXT = 'insert
                        error';
          END IF;
    END;    
    OPEN sel_tab;
    SET nr_of_rows = 0;
    FETCH FROM sel_tab INTO tab_nm;    
    WHILE (SQLCODE = 0) DO
        SET nr_of_rows = nr_of_rows +1;
        INSERT INTO my_table (table_name,
         schema)VALUES (tab_nm, schema_nm); 
        FETCH FROM sel_tab INTO tab_nm;
    END WHILE;
END@         
    

"CREATE PROCEDURE tabs_in_schema (schema_nm 
     VARCHAR) RETURNS (nr_of_rows INTEGER)
BEGIN
   DECLARE tab_nm VARCHAR;
   EXEC SQL PREPARE sel_tab
	SELECT table_name FROM sys_tables
                   WHERE table_schema = ?;
  EXEC SQL PREPARE ins_tab
        INSERT INTO my_table (table_name, 
           schema) VALUES (?,?);
  nr_of_rows := 0;
  EXEC SQL EXECUTE sel_tab USING
         (schema_nm)INTO (tab_nm);
  EXEC SQL FETCH sel_tab;
  WHILE SQLSUCCESS LOOP
      nr_of_rows := nr_of_rows + 1;
     EXEC SQL EXECUTE ins_tab USING
           (tab_nm, schema_nm);
     IF SQLROWCOUNT <> 1 THEN
         RETURN SQLERROR OF ins_tab;
    END IF;
    EXEC SQL FETCH sel_tab;
  END LOOP
END";
    

In the above example, both DB2 and solidDB procedures use <> as the is-not-equal-to comparison operator. DB2 also supports != as an is-not-equal-to comparison operator, but solidDB does not.

EXEC SQL WHENEVER SQLERROR

The EXEC SQL WHENEVER SQLERROR statement is used to decrease the need for IF NOT SQLSUCCESS THEN tests after every executed SQL statement in a procedure.

When this statement is included in a stored procedure, all return values of executed statements are checked for errors. If statement execution returns an error, the procedure is automatically aborted or the transaction is rolled back. SQLERROR of the last cursor is returned, and the error string of the latest failed SQL statement is stored into variable SQLERRSTR.

The WHENEVER SQLERROR statement is added to the beginning of the solidDB procedure just after the BEGIN keyword and is somewhat similar to the behavior of the DB2 EXIT condition handler. Table 8 shows an example of a solidDB procedure using the WHENEVER SQLERROR statement, rather than checking after each SQL statement to exit the procedure if an error is generated. The WHENEVER SQLERROR statement can be compared to declaring a condition handler on DB2.

CREATE PROCEDURE sys_tabs         
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE SQLCODE INTEGER DEFAULT 0;
   DECLARE v_sqlcode INTEGER DEFAULT 0;
  DECLARE sel_tables CURSOR WITH RETURN FOR 
       SELECT tabname FROM syscat.tables
           WHERE tabname LIKE 'SYS%';
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SET v_sqlcode = SQLCODE;
       
    OPEN sel_tables;
END@ 
    

"CREATE PROCEDURE sys_tabs RETURNS (tab
        VARCHAR)
BEGIN
  EXEC SQL WHENEVER SQLERROR ABORT;
    EXEC SQL PREPARE sel_tables
        SELECT table_name
             FROM sys_tables
                 WHERE table_name LIKE 
                            ‘SYS%';
    EXEC SQL EXECUTE sel_tables INTO (tab);
    EXEC SQL FETCH sel_tables;
    WHILE SQLSUCCESS LOOP
         RETURN ROW;
         EXEC SQL FETCH sel_tables;
    END LOOP;
    EXEC SQL CLOSE sel_tables;
    EXEC SQL DROP sel_tables;
END";
    

Procedure execution trace

If you want to trace every statement in your stored procedure, you can turn on proctrace, which traces each statement inside the specified stored procedure. The output of the trace is written to the soltrace.out file, which you can find in your solidDB working directory.

You can turn proctrace on for a specified procedure by using the ADMIN COMMAND statement: ADMIN COMMAND 'proctrace { on | off }user username procedure procedure_name'

In addition, you can create a user-defined trace by sending a string to the soltrace.out file by using the writetrace function in the procedure: WRITETRACE (string VARCHAR)

You can turn the output on or off by using the command ADMIN COMMAND 'usertrace { on | off } user username procedure procedure _name'

Note that the IBM Data Studio Developer tool offers a debugger and an SQL profiler component that can be used to analyze execution of SQL PL procedures.

Back to top

Returning results sets of SQL procedures

This section describes the differences in the results sets of the DB2 and solidDB SQL procedures.

Returning results sets in DB2 SQL procedures

In SQL procedures, cursors can be used to do more than iterate through rows of a result set. They can also be used to return result sets to the calling program.

Result sets can be retrieved by SQL procedures (in the case of a nested procedure calls) or client applications programmed in C using the CLI application programming interface, Java®, CLI, or .NET CLR languages.

To return a result set from an SQL procedure, do the following:

1. Specify the DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement.
   
   
2. Declare the cursor using the WITH RETURN clause.
   
   
3. Open the cursor in the SQL procedure.
   
   
4. Keep the cursor open for the client application (do not close it).

Listing 18 shows an example of an SQL procedure that returns only a single result set.

            
CREATE PROCEDURE read_emp() 
SPECIFIC read_emp 
LANGUAGE SQL 
DYNAMIC RESULT SETS 1 
Re: BEGIN 
DECLARE c_emp CURSOR WITH RETURN FOR 
    SELECT salary, bonus, comm. FROM employee WHERE job != ’PRES’; 
OPEN c_emp; 
END Re 
    

If the cursor is closed using the CLOSE statement before the return of the SQL procedure, the cursor result set will not be returned to the caller or client application.

Multiple result sets can be returned from an SQL procedure by using multiple cursors. To return multiple cursors, do the following:

1. Specify the DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement.
   
   
2. Specify the maximum possible number of result sets likely to be returned. The number of results sets actually returned must not exceed this number.
   
   
3. Declare cursors for each of the result sets to be returned that specify the WITH RETURN clause.
   
   
4. Open the cursors to be returned.
   
   
5. Keep the cursor open for the client application (do not close it).

One cursor is required per result set that is to be returned. Result sets are returned to the caller in the order in which they are opened. Once you create the SQL procedure that returns a result set, you might want to call it and retrieve the result set.

Returning results sets in solidDB procedures

The RETURNS variables

You can use stored procedures to return a result set table with several rows of data in separate columns. This is an IBM solidDB proprietary method to return data, and you use the RETURNS structure to do it.

When you use the RETURNS structure, you must separately declare result set column names for the output data rows. There can be any number of result set column names. The result set column names are declared in the RETURNS section of the procedure definition, as shown in Listing 19.

            
"CREATE PROCEDURE procedure_name (IN in_param1 datatype, in_param2 datatype,)   
     RETURNS (out_param3 datatype, out_param4 datatype)
BEGIN
…
END";
    

By default, the procedure returns only one row of data containing the values as they were at the moment when the stored procedure was run or was forced to exit. However, you can also return result sets from a procedure using the following command: RETURN ROW;

Each RETURN ROW call adds a new row into the returned result set where column values are the current values of the result set column names. Listing 20 creates a procedure that has two input parameters and two results set column names for output rows.

            
"CREATE PROCEDURE lookup (IN first_name VARCHAR, last_name VARCHAR) RETURNS
                (phone_nr NUMERIC, city VARCHAR)
BEGIN
--procedure_body
    WHILE …LOOP
          RETURN ROW;
          EXEC SQL FETCH….
    END LOOP;
END";
    

For example, if you enter: call lookup ('SUE','DAVIS'); the result is shown in Listing 21.

            
3433555 CHICAGO
2345226 BOSTON
            

Back to top

Miscellaneous topics in SQL procedures

Procedure ownership, access rights, and privileges of SQL procedures

Stored procedures on both DB2 and solidDB are owned by the creator. They are part of the creator's schema. Users who need to run stored procedures in other schemas need to be granted EXECUTE privilege on the procedure with the following command: GRANT EXECUTE ON proc_name TO { USER | ROLE };

Using TIMESTAMPDIFF

The procedure in Listing 22 demonstrates the solidDB scalar function TIMESTAMPDIFF, used in an assignment statement.

            
"CREATE PROCEDURE grown_up (birth_date DATE) RETURNS (description VARCHAR)
BEGIN
   DECLARE age INTEGER;
   age := {fn TIMESTAMPDIFF(SQL_TSI_YEAR, birth_date, now())};
   IF age >= 18  THEN
      description := 'ADULT';
   ELSE
       description := 'MINOR';
   END IF
END";
    

In Listing 22, the native solidDB function TIMESTAMPDIFF in is written using the format of {fn TIMESTAMPDIFF(…) }. This format represents syntax used with ODBC functions. solidDB runs SQL statements through the ODBC parser to convert {fn <function>} into native solidDB format. Usually this results in just dropping the {fn } portion of the syntax. For example, {fn CURDATE()} is converted to CURDATE(). You can use either the ODBC form or a natively supported form of the function in a solidDB procedure.

Using an escape sequence

solidDB strings are delimited by single quotes. To create an actual apostrophe, put two single quote marks side-by-side (‘’) to produce one quote mark in your output. This is commonly known as an escape sequence. Listing 23 shows this technique.

            
"CREATE PROCEDURE quotes
RETURNS (string_var VARCHAR(20))
BEGIN
string_var := 'IBM''s solidDB';
END";

CALL quotes;
    

The result is IBM's solidDB.

Executing solidDB procedures from the solidDB SQL editor

When executing SQL scripts from the solidDB SQL editor (solsql) you must commit your work after executing the script or before leaving the editor. The default command for the SQL editor is autocommit off.

You can also start the SQL editor with option -a to automatically commit all SQL statements. The default behavior for the DB2 command line processor (CLP) is autocommit on.

You can use any of three methods to execute procedures from the solidDB SQL editor.

• Connect to solidDB using the solsql command, and then execute the procedure, as shown in Listing 24.
  
  Listing 24. Connect and execute
  

  C:\hsbmaster63>solsql "tcp 1315" dba dba Solid SQL Editor (teletype) v.06.00.1018 Copyright (C) Solid Information Technology Ltd 1993-2007 Connected to 'tcp 1315'. Execute SQL statements terminated by a semicolon. Exit by giving command: exit; call proc(?); Param 1: 10; Command completed successfully, -1 rows affected.

  
  

  In Listing 24, the procedure proc is coded to return an OUT parameter value, which is denoted by the parameter marker (?), and was not coded to accept an input value. However, the solidDB SQL editor prompts you to enter a value for an input parameter. In the example in Listing 24, the dummy value 10 is entered. Any value could have been entered. This behavior is a known limitation of solsql. To avoid this scenario, use the RETURNS clause of the CREATE PROCEDURE statement to return data to the caller.

• Execute the procedure from the solsql command line. You can call the procedure inline with the command: solsql -e "call proc(?)" "tcp 1315" dba dba, assuming solid is listening to tcp 1315, and the user name and password are dba and dba.
  
  
• Place the procedure call in a file, and reference that file with the following solsql command: solsql "tcp 1315" dba dba filename.sql

Back to top

Conclusion

Whether the SQL procedure language you already know is DB2 or solidDB, you're now off to a good start towards learning the other. Continue your education by reading Part 1 of this article series.

Resources

Learn

• Continue your education by reading Part 1 of this article series.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Nora Sokolof is a Consulting Technical Sales specialist with IBM Software Group in North America. She is a member of the Data Servers and Application Development team. Nora specializes in Information Management software products. She holds a Master of Science degree in Software Engineering from Pace University, and she has been an IBM software professional for more than 22 years. She has held positions as a DB2, Informix, Oracle, and PeopleSoft development database administrator.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Conditional statements in SQL procedures
• Looping statements in SQL procedures
• Error handling in SQL procedures
• Returning results sets of SQL procedures
• Miscellaneous topics in SQL procedures
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers