SQL PL in SQL procedures

You can use SQL PL to implement procedural logic in SQL procedures.

SQL PL statements are primarily used in SQL procedures. SQL procedures can contain basic SQL statements for querying and modifying data, but they can also include SQL PL statements for implementing control flow logic around the other SQL statements. The complete set of SQL PL statements can be used in SQL procedures.

SQL procedures also support parameters, variables, assignment statements, a powerful condition and error handling mechanism, nested and recursive calls, transaction and savepoint support, and the ability to return multiple result sets to the procedure caller or a client application.

SQL PL, when used within SQL procedures, allows you to effectively program in SQL. The high-level language of SQL PL and the additional features that SQL procedures provide makes programming with SQL PL fast and easy to do.

As a simple example of SQL PL statements being used in a SQL procedure, consider the following example:

  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