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

Structure of SQL procedures

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

Structure of DB2 SQL procedures

The core of a DB2 SQL procedure is a compound statement. Compound statements, also known as compound blocks, are bounded by the keywords BEGIN and END. Listing 1 illustrates the structured format of a DB2 SQL procedure.

            
CREATE PROCEDURE procedure_name (parameters)
LANGUAGE SQL
BEGIN 
   Local variable declarations
   Condition declarations                       
   Cursor declarations                            
   Condition handler declarations         
   Procedural body
        Assignment, 
        flow of control,
        looping
        SQL statements
        cursors
        BEGIN 
        …                     
        END
   Other compound statements either nested or serially placed
END 
    

SQL procedures can consist of one or more compound blocks. These blocks can be nested or serially placed within an SQL procedure. Within each of these blocks, there is a prescribed order for the variables, conditions, and handler declarations. These declarations must precede the introduction of SQL procedural logic. However, cursors can be declared anywhere in the SQL procedural body.

There are two types of compound statements (blocks): atomic and not atomic.

Atomic compound statements can be thought of as a single unit of work within a procedure. If any statement within the block fails, then any statement executed up to that point is also considered to be failed, and all the statements are rolled back. In other words, either all the statements within the block succeed, or none of the statements succeed. The COMMIT, SAVEPOINT, and ROLLBACK statements are not allowed. Those statements are supported only in not atomic blocks.

NOT ATOMIC statement blocks are the default type. Even if one statement within the block fails, other statements might succeed and be committed (or rolled back), as long as the work is explicitly committed either in the procedure or within the unit of work that the procedure is part of.

Listing 2 shows the syntax for ATOMIC and NOT ATOMIC blocks.

            
BEGIN ATOMIC
    … procedure code…
END 

BEGIN NOT ATOMIC
    …procedure code…
END 
    

Structure of solidDB procedures

Like a DB2 procedure, a solidDB procedure has several sections. These sections include a parameter section, a declare section for local variables, and the procedure body section. Listing 3 shows the format of a solidDB procedure.

            
"CREATE PROCEDURE procedure_name (parameter_section)
BEGIN
  declare_section_local_variables
  procedure_body
    assignment
    flow of control
    looping
    cursor processing
    error handling statements
END";
    

You can identify several differences. Unlike DB2, the solidDB procedure does not consist of multiple compound statement blocks. Instead only one pair of BEGIN and END keywords at the beginning and end of the procedure is used.

The solidDB procedure requires that its entire definition be enclosed within a pair of double quotes.

Transactions within solidDB procedures can be committed or rolled back either inside the procedure or outside the procedure. When a solidDB procedure returns to the calling application (JDBC or ODBC) that has autocommit on, the procedure is implicitly committed unless a rollback is specified.

Inside the procedure the commit or roll back statement (along with all other SQL statements) are preceded with the keywords EXEC SQL. This differs from DB2 SQL procedures, which do not require EXEC SQL to precede SQL statements. Listing 4 shows an example of the solidDB syntax.

            
EXEC SQL COMMIT WORK;
EXEC SQL ROLLBACK WORK;
    

As in DB2, cursors can be declared anywhere in the procedure, and local variables must be declared within the declare section following the BEGIN and before the procedural logic.

Back to top

Parameters and calling SQL procedures

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

Parameters for DB2 procedures

Parameters are used to pass scalar values in and out of procedures. DB2 can also pass sets of values as a single parameter in the form of an array. For scalar values there are three types of parameters: IN, INOUT, and OUT. Listing 5 shows an example of creating a procedure that uses all three types of parameters. Parameters p1, p2, and p3 are each declared as INTEGER(INT).

            
CREATE PROCEDURE proc_name (IN p1 INT, INOUT p2 INT, OUT p3 INT) 
LANGUAGE SQL
BEGIN 
 ….
END@ 
    

In addition to the OUT and INOUT parameters, DB2 also returns result sets (multiple rows with multiple columns of values) to the calling program. However, result sets are not returned using parameters in the CREATE PROCEDURE statement.

Parameters for solidDB procedures

Like in a DB2 procedure, a solidDB procedure has three types of parameters: IN, OUT, and INOUT. If the parameter type is not specified, it is IN by default. Listing 6 shows the preceding example using the solidDB syntax.

            
"CREATE PROCEDURE proc_name(p1 INTEGER = 8, OUT p3 INTEGER, INOUT p2 INTEGER)
BEGIN
…
END"
    

Input and output parameters are treated like local variables within a procedure. A solidDB parameter can be assigned a default value in the parameter list, as shown in Listing 6. Initialization of a variable in the parameter list is not supported on DB2.

When you call a procedure that has default values for the parameters defined, you do not have to specify the parameters. For example, if all the parameters in proc_name were given default values, you would use call proc_name; to call the command.

You can also assign a value to a parameter when calling the procedure by using the equal sign (=) character, as shown in Listing 7.

            
call proc_name (p1 = 8,  p2,  p3);
    

This command assigns a value of 8 for parameter p1, and it assigns default values for parameters p2 and p3. If parameter names are not used in the call statement, solidDB assumes that the parameters are given in same the order as in the CREATE PROCEDURE statement.

Another way to return values in the solidDB is through the RETURNS clause of the CREATE PROCEDURE statement. The RETURNS clause returns a result sets table, or it just returns output values in general. This method differs from the way that DB2 returns results sets, as described in Part 2 of this article set.

Back to top

Variables and assignments in SQL procedures

This section describes the differences in the variables and assignments of the DB2 and solidDB SQL procedures.

Variables and assignments in DB2 procedures

SQL statements are used to declare variables and to assign values to variables. There are several types of variable-related statements:

• DECLARE <variable_name datatype>
• DECLARE <condition>
• DECLARE <condition handler>
• DECLARE CURSOR <cursor_name> FOR <SQL statement>
• SET (assignment-statement)

Local variables in DB2 procedures are defined using the DECLARE statement. In addition, variables can be initialized to a default value using the DEFAULT keyword with the DECLARE statement. The DEFAULT keyword is not supported with the DECLARE statement on solidDB.

Assignments are performed using the SET statement.

Variables and assignments in solidDB procedures

The syntax to declare a local variable on solidDB and to assign values is similar to DB2: DECLARE <variable_name datatype> . Listing 8 shows an example.

            
"CREATE PROCEDURE …. (parameter list)
BEGIN
   DECLARE i INTEGER;
   DECLARE dat DATE;
END";
    

All variables are initialized to NULL by default. To assign values to variables on solidDB, you can use SET variable_name = expression;, or you can use variable_name := expression;. Listing 9 shows an example.

            
SET i = i + 20;
i := 100;
    

Table 1 shows how various types of DB2 assignment methods can be mapped to solidDB.

Back to top

Procedure body in SQL procedures

This section describes the differences in the structures of the DB2 and solidDB SQL procedure bodies.

The DB2 SQL procedure body

SQL procedures support the following types of statements:

• Conditional statements
• Looping statements
• Transfer of control statements
• Error management statements
• Result set manipulation statements

The following is an overview of the SQL PL statements supported in the procedure body:

Conditional statements:

• CASE (2 forms)
• IF

Looping statements:

• FOR
• LOOP
• REPEAT
• WHILE

Transfer of control statements:

• CALL
• GOTO
• ITERATE
• LEAVE
• RETURN

Error management statements:

• SIGNAL
• RESIGNAL

Result set manipulation statements:

• ASSOCIATE LOCATOR(S)
• ALLOCATE CURSOR

DB2 procedures also support the use of comments within the procedure body, which are denoted by double dashes (--).

The solidDB procedure body

Any valid SQL statement, such as CREATE TEMPORARY TABLE, can be used inside a solidDB stored procedure, including DDL. The CASE statement is an exception, because it is not supported in procedures but is supported in other solidDB application interfaces.

The solidDB procedure uses SQL constructs that are similar to those used in DB2 procedures, including the use of double dashes for commenting. Table 2 shows how DB2 SQL PL statements can be mapped to solidDB procedure statements.

There are two ways to execute SQL inside solidDB procedures:

• The EXECDIRECT syntax
• The cursor syntax

The EXECDIRECT syntax is the preferred method when there are no rows returned and when a variable is not needed as a parameter. For example, the following statement inserts a single row of data: EXEC SQL EXECDIRECT INSERT INTO table1 (id, name) VALUES (1, 'Smith');

The EXECDIRECT statement can also be used with a cursor name. The statement enables you to prepare and execute statements without using a separate PREPARE statement. Listing 10 shows an example.

            
EXEC SQL c1 USING (host_x) INTO (host_y) EXECDIRECT 
   SELECT y from foo where x = ?;
EXEC SQL FETCH c1;
EXEC SQL CLOSE c1;
EXEC SQL DROP c1;
    

In Listing 10:

• c1 is the cursor name
  
  
• host_x is the variable whose value will be substituted for the ?
  
  
• host_y is the variable into which the value of the column y will be stored when it is fetched.

Also note that, although you do not need to prepare a cursor, you still need to close and drop the cursor.

The cursor syntax treats the SQL as though the result consists of multiple rows, a PREPARE statement, and an EXECUTE statement. On solidDB, cursors are used when:

• Your processing returns multiple rows (SELECT)
• You need to dynamically repeat a single statement with different values supplied by a variable as a parameter. This includes SELECT, UPDATE, INSERT, and DELETE statements.

Back to top

Cursors in SQL procedures

This section describes the differences in using cursors for the DB2 and solidDB SQL procedures.

Using cursors in DB2 SQL procedures

In a DB2 procedure, a cursor is used to define a result set and perform logic on a row-by-row basis. The pointer can only reference one row at a time, but it can point to other rows in the result set as needed. To use cursors in SQL procedures, complete the following steps:

1. Declare a cursor that defines a result set. For example: DECLARE CURSOR cursor_name FOR < sql statement >;
   
   
2. Open the cursor to establish the result set. For example: OPEN cursor_name;
   
   
3. Fetch the data into local variables as needed from the cursor, one row at a time. For example: FETCH FROM cursor_name INTO variable ;
   
   
4. Close the cursor when you are finished. For example: CLOSE cursor_name ;

Using cursors in solidDB SQL procedures

To return multiple rows with cursors, the steps are as follows:

1. Prepare the cursor (the definition)
2. Execute the cursor (executing the statement)
3. Fetch on the cursor for select procedure calls (get the results row-by-row)
4. Close the cursor after use (still enabling it to re-execute)
5. Drop the cursor from memory (removing it)

Now explore these steps in more detail.

1. Prepare the cursor using EXEC SQL PREPARE cursor_name SQL_statement;

   By preparing a cursor, memory space is allocated to accommodate one row of the result set of the statement, and the statement is parsed and optimized, as shown in Listing 11.

   
   
   Listing 11. Example of preparing the cursor
   

   EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE 'SYS%';

   
   

   This statement prepares the cursor named sel_tables, but it does not execute the statement that it contains.

   
   
   
2. Execute the cursor using EXEC SQL EXECUTE cursor_name USING (var1 [var2…]), [ INTO ( var1 [, var2...] ) ];

   After a statement has been successfully prepared, it can be executed. An execute binds possible input and output variables to it, and it runs the actual statement.

   The optional INTO section binds result data of the statement to variables. Variables listed in parentheses after the INTO keyword are used when running a SELECT or CALL statement. The resulting columns of the SELECT or CALL statement are bound to these variables when the statement is executed. The optional USING clause binds data into the SQL statement, such as those used in a WHERE clause. Listing 12 shows an execute statement to the example.

   
   
   Listing 12. Example code including execute statement
   

   EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name LIKE 'SYS%'; EXEC SQL EXECUTE sel_tables INTO (tab);

   
   

   The statement is now executed, and the resulting table names are returned into variable tab in the subsequent Fetch statements.

   
   
   
3. Fetch on the cursor using EXEC SQL FETCH cursor_name;

   When a SELECT or CALL statement has been prepared and executed, it is ready for fetching. Other statements, such as UPDATE, INSERT, and DELETE, do not require fetching, because there will be no result set.

   The example command fetches a single row from the cursor to the variables that were bound with the INTO keyword when the statement was executed.

   Listing 13 shows the completed example code.

   
   
   Listing 13. Example code including fetch statement
   

   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;

   
   

   After running this example, the variable tab contains the table name of the first table found conforming to the WHERE clause. Subsequent calls to fetch on the cursor sel_tables get any subsequent rows.

   To fetch all the table names, a loop construct is used, as shown in Listing 14.

   
   
   Listing 14. Example of loop construct
   

   WHILE expression LOOP EXEC SQL FETCH sel_tables; END LOOP

   
   
   
   
4. Close the cursor using EXEC SQL CLOSE cursor_name;

   Closing the cursor does not remove the actual cursor definition from memory. You can run it again when needed.

   
   
   
5. Drop the cursor using EXEC SQL DROP cursor_name;

   Cursors can be dropped from memory, which releases all resources.

Table 3 compares cursor processing steps when fetching rows.

Table 4 shows how DB2 and solidDB fetch rows using cursors.

CREATE PROCEDURE sum_salaries (OUT sum 
      INTEGER)
 LANGUAGE SQL
 BEGIN 
    DECLARE p_sum INTEGER; 
    DECLARE p_sal INTEGER; 
    DECLARE c CURSOR FOR SELECT SALARY 
            FROM EMPLOYEE; 
    DECLARE SQLSTATE CHAR(5) DEFAULT 
            ’00000’; 
    SET p_sum = 0; 
    OPEN c; 
    FETCH FROM c INTO p_sal; 
    WHILE(SQLSTATE = ’00000’) 
    DO 
       SET p_sum = p_sum + p_sal; 
       FETCH FROM c INTO p_sal; 
    END WHILE; 
   CLOSE c; 
   SET sum = p_sum; 
END@
    

“CREATE PROCEDURE sum_salaries (OUT sum
           INTEGER)
BEGIN
    DECLARE p_sum INTEGER;
    DECLARE p_sal INTEGER;
    EXEC SQL PREPARE c  
        SELECT SALARY FROM EMPLOYEE;
    p_sum := 0;
    EXEC SQL EXECUTE c INTO (p_sal);
    EXEC SQL FETCH c;
    WHILE (SQLSUCCESS) LOOP
          p_sum := p_sum + p_sal;
          EXEC SQL FETCH c ;
     END LOOP;
     EXEC SQL CLOSE c;
     EXEC SQL DROP c;
     sum := p_sum;
 END”;          
    

In order to make a cursor dynamic, solidDB uses parameter markers to bind values to the actual parameter values at execute time. The question mark (?) is used as a parameter marker. Listing 15 shows an example.

            
EXEC SQL PREPARE sel_tabs
     SELECT table_name FROM sys_tables
        WHERE table_name LIKE ? AND table_schema LIKE ?;
    

The execution statement makes use of the USING keyword to accommodate the binding of a variable to the parameter marker, such as EXEC SQL EXECUTE sel_tabs USING ( var1, var2 ) INTO (tabs);

In this way a single cursor can be used multiple times without having to re-prepare the cursor. Because preparing a cursor involves the parsing and optimizing of the statement, significant performance gains can be achieved by using reusable cursors.

Only the USING list accepts variables; data cannot be directly passed in this way. So if, for example, you want to make an insert into a table, in which one column value should always be status = 'NEW', this statement would be wrong: EXEC SQL EXECUTE ins_tab USING (nr, desc, dat, 'NEW');

The correct approach is to define the constant value in the prepare section, as shown in Listing 16.

            
EXEC SQL PREPARE ins_tab 
	INSERT INTO my_tab (id, descript, in_date, status) 
             VALUES (?,?,?,'NEW');
EXEC SQL EXECUTE ins_tab USING (nr, desc, dat);
    

As mentioned previously, in solidDB procedures, anytime SQL statements such as INSERT, UPDATE, or DELETE contain variables as parameters, use cursor processing syntax. In contrast, variables in DB2 can be used as parameters without cursor processing.

Table 5 shows an example of a DB2 procedure that has been translated into a solidDB procedure that uses inserts with cursor processing.

CREATE PROCEDURE p2
LANGUAGE SQL
BEGIN
   DECLARE id INT;
   CREATE TABLE table1 (id_col INT);
   INSERT INTO table1 (id_col) VALUES(1);
   SET id = 2;
   INSERT INTO table1 (id_col) VALUES(id);
   WHILE id <= 10 DO
         INSERT INTO table1(id_col)
              VALUES(id);
         SET id = id + 1;
    END WHILE;
 END@    
    

"CREATE PROCEDURE p2
  BEGIN
     DECLARE id INT;
     EXEC SQL EXECDIRECT create table
             table1(id_col INT);
     EXEC SQL EXECDIRECT insert into table1
           (id_col) values (1);
     EXEC SQL PREPARE cursor1
           INSERT INTO table1 (id_col)
                  values (?);
      id := 2;
      WHILE id <= 10 LOOP
         EXEC SQL EXECUTE cursor1 USING
                  (id);
         id := id + 1;
      END LOOP;
      EXEC SQL CLOSE cursor1;
      EXEC SQL DROP cursor1;
END";

Listing 17 shows an example of a solidDB procedure that uses cursor processing for SELECT, UPDATE, or INSERT with a cursor positioned at DELETE>. The procedure merges new data rows with existing data rows. The new data and existing data are matched based on key values of row_type and row_id. When a match is made, the existing price value is updated with the new price value. Next, the matched rows are deleted from the new data so that only the unmatched rows remain. The unmatched new rows are inserted into the existing table.

            
"CREATE PROCEDURE merge_rows RETURNS (new_rows INT, updated_rows INT)
BEGIN
	DECLARE v_row_type int;
	DECLARE v_row_id int;	
	DECLARE v_price float;

	new_rows := 0;
	updated_rows := 0;

	EXEC SQL PREPARE select_cursor
		SELECT n.row_type , n.row_id , n.price 
		    FROM new_rows n, old_rows o
		       WHERE n.row_type = o.row_type AND n.row_id = o.row_id;		
	EXEC SQL PREPARE update_cursor UPDATE old_rows SET price = ? WHERE row_type = ?
                            AND row_id = ?;		
               EXEC SQL EXECUTE select_cursor INTO ( v_row_type, v_row_id, v_price);	
               EXEC SQL FETCH select_cursor;	
               WHILE SQLSUCCESS LOOP
		EXEC SQL EXECUTE update_cursor USING ( v_price ,v_row_type, v_row_id);
		EXEC SQL EXECDIRECT DELETE FROM new_rows
			WHERE CURRENT OF select_cursor;		
               updated_rows := updated_rows + 1;
		EXEC SQL FETCH select_cursor;
	END LOOP;
       EXEC SQL CLOSE select_cursor;
	EXEC SQL DROP select_cursor;
	                      …..
	EXEC SQL PREPARE insert_cursor INSERT INTO old_rows (row_type , row_id , price)
		    (SELECT * FROM new_rows);
       EXEC SQL EXECUTE insert_cursor;
	new_rows := SQLROWCOUNT;
	EXEC SQL CLOSE insert_cursor;
	                     ….
	EXEC SQL EXECDIRECT DROP TABLE new_rows;
	EXEC SQL COMMIT WORK;
END";
            

Back to top

Dynamic SQL procedures

Up to this point, this article has compared DB2 SQL procedures written in static SQL with solidDB procedures. When DB2 procedures are written using static SQL, the SQL is prepared before the procedure is run. The prepared SQL is stored as compiled SQL in a package object on the database. The compiled code is invoked at runtime.

DB2 SQL procedures can also be written using dynamic SQL. When using dynamic SQL, the SQL is prepared at the time the statements in the procedure are invoked. Table 6 compares a solidDB procedure with a DB2 procedure written using dynamic SQL:

CREATE PROCEDURE create_table 
        (IN new_name VARCHAR(10))
LANGUAGE SQL
  BEGIN
    DECLARE stmt VARCHAR(1000);    
    SET stmt = '';    
    SET stmt = 'CREATE TABLE '||new_name||
     '( empno CHAR(6) NOT NULL, '||
     'firstnme VARCHAR(12) NOT NULL, '||
     'midinit CHAR(1) NOT NULL, '||
     'lastname VARCHAR(15) NOT NULL )';    
    EXECUTE IMMEDIATE stmt;    
END@
    

“CREATE PROCEDURE create_table (IN new_name
          VARCHAR(10))
BEGIN
    DECLARE stmt VARCHAR(1000);
    SET stmt = ‘’;
    SET stmt = ‘CREATE TABLE ’ + new_name + 
       ‘(empno CHAR(6) NOT NULL,’ +
        ‘firstnme VARCHAR(12) NOT NULL,’ +
        ‘midinit CHAR(1) NOT NULL,’ +
         ‘ lastname VARCHAR(15) NOT NULL)’;
    EXEC SQL EXECDIRECT stmt;    
END”;
COMMIT WORK;
    

Note: The concatenate operator ( || ) is supported on solidDB when used in the SELECT list. For example: SELECT ‘a' || ‘b', col1 FROM…

DB2’s EXECUTE IMMEDIATE statement prepares and executes the SQL at runtime, and it is equivalent to solidDB’s EXECDIRECT statement. DB2 supports PREPARE and EXECUTE statements as well. By using separate PREPARE and EXECUTE statements, you can prepare the SQL statement once but execute it several times. This eliminates the cost of preparing the same statement repeatedly. Because solidDB procedures do not support static SQL, DB2 dynamic SQL procedures more closely compare with solidDB procedures.

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 2 of this article series.

Resources

Learn

• Use an RSS feed to request notification for the upcoming articles in this series. (Find out more about RSS feeds of developerWorks content.)
  
  
• 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
• 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
• 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

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers