Use of the SQL procedural language to create a stored procedure

With SQL procedural language, you can write stored procedures that consist entirely of SQL statements.

An SQL procedure can include declarations of variables, conditions, cursors, and handlers. The SQL procedure can also include flow control, assignment statements, and traditional SQL for defining and manipulating relational data. These extensions provide a procedural language for writing stored procedures, and they are consistent with the Persistent Stored Modules portion of the SQL standard.

Example

This example shows a simple SQL procedure (the syntax for the CREATE PROCEDURE statement shows only a portion of the statement clauses):

Begin general-use programming interface information.
CREATE PROCEDURE ITERATOR()  LANGUAGE SQL
BEGIN
     ..
     DECLARE not_found CONDITION FOR SQLSTATE '02000';
     DECLARE c1 CURSOR FOR ....;
     DECLARE CONTINUE HANDLER FOR not_found                    (2)
            SET at_end = 1;
     OPEN c1;
     ftch_loop1: LOOP
         FETCH c1 INTO v_dept, v_deptname, v_admdept;          (1)
         IF at_end = 1 THEN
             LEAVE ftch_loop1;                                 (3)
         ELSEIF v_dept = 'D01' THEN 
         INSERT INTO department (deptno, deptname, admrdept)
                VALUES ( 'NEW', v_deptname, v_admdept);
         END IF;
      END LOOP;
      CLOSE c1;
END
End general-use programming interface information.

In this example:

  • Processing goes through ftch_loop1, assuming that a row is found.
  • The first time that the FETCH does not find a row, processing goes to the HANDLER (1).
  • The HANDLER sets the at_end flag. Because the procedure uses a CONTINUE HANDLER, processing continues at the next step after the FETCH (2).
  • Processing continues with the CLOSE SQL statement (3).