DB2 Version 10.1 for Linux, UNIX, and Windows

PL/SQL features

PL/SQL statements and scripts can be compiled and executed using DB2® interfaces.

You can execute the following PL/SQL statements:

Anonymous blocks; for example, DECLARE...BEGIN...END
CREATE OR REPLACE FUNCTION statement
CREATE OR REPLACE PACKAGE statement
CREATE OR REPLACE PACKAGE BODY statement
CREATE OR REPLACE PROCEDURE statement
CREATE OR REPLACE TRIGGER statement
CREATE OR REPLACE TYPE statement
DROP PACKAGE statement
DROP PACKAGE BODY statement

PL/SQL procedures and functions can be invoked from other PL/SQL statements or from DB2 SQL PL statements. You can call a PL/SQL procedure from SQL PL by using the CALL statement.

The following statements and language elements are supported in PL/SQL contexts:

Type declarations:
- Associative arrays
- Record types
- VARRAY types
Subtype declarations
Variable declarations:
- %ROWTYPE
- %TYPE
Basic statements, clauses, and statement attributes:
- Assignment statement
- NULL statement
- RETURNING INTO clause
- Statement attributes, including SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT
Control statements and structures:
- CASE statements:
  - Simple CASE statement
  - Searched CASE statement
- Exception handling
- EXIT statement
- FOR statement
- GOTO statement
- IF statement
- LOOP statement
- PIPE ROW statement
- RETURN statement
- WHILE statement
Static cursors:
- CLOSE statement
- Cursor FOR loop statement
- FETCH statement (including FETCH INTO a %ROWTYPE variable)
- OPEN statement
- Parameterized cursors
- Cursor attributes
REF CURSOR support:
- Variables and parameters of type REF CURSOR
- Strong REF CURSORs
- OPEN FOR statement
- Returning REF CURSORs to JDBC applications
Error support:
- RAISE_APPLICATION_ERROR procedure
- RAISE statement
- SQLCODE function
- SQLERRM function