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.