PL/SQL Block Types

There are two types of block in PL/SQL, Anonymous Blocks and Subprograms. Subprograms can be further subdivided into Procedures and Functions.

Anonymous Blocks

Anonymous Blocks are unnamed blocks that are declared in an application at the point they are required to execute.

DECLARE
   -- Variables, cursors and user defined functions.
BEGIN
   /* SQL statements
   PL/SQL statements */
EXCEPTION
   -- Actions to perform when errors occur.
END;

As previously explained, the ‘Declare’ and ‘Exception’ sections are optional. The ‘Hello World’ example in the previous section is an example of an Anonymous Block.

Subprograms

Subprograms are named blocks, either Procedures or Functions, which once declared, can be called any number of times. The distinction between Procedures and Functions is that generally Procedures perform an action whereas Functions tend to compute a value. Functions must also return a value. It is possible to pass parameters to both Procedures and Functions.

The basic structure of a Procedure is as follows.

PROCEDURE name
IS
   -- Variables, cursors and user defined functions.
BEGIN
   /* SQL statements
   PL/SQL statements */
EXCEPTION
   -- Actions to perform when errors occur.
END;

As with Anonymous Blocks, the ‘Exception’ section is optional, which is the case for Functions as well. The ‘Declare’ keyword is absent from both also.

The basic structure of a Function takes the following format.

FUNCTION name
RETURN datatype
IS
   -- Variables, cursors and user defined functions.
BEGIN
   /* SQL statements
   PL/SQL statements */
   RETURN value;
EXCEPTION
   -- Actions to perform when errors occur.
END;

Procedures and Functions aren’t currently covered any further here. All subsequent examples will use Anonymous Blocks.