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.
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