[Chapter 15] 15.4 Procedures

15.4 Procedures

A procedure is a module performing one or more actions. Because a procedure is a standalone executable statement in PL/SQL, a PL/SQL block could consist of nothing more than a single call to a procedure. Procedures are key building blocks of modular code, allowing you to both consolidate and reuse your program logic.

The general format of a PL/SQL procedure is as follows:

PROCEDURE name [ ( parameter [, parameter ... ] ) ]
IS
   [declaration statements]

BEGIN
   executable-statements

[ EXCEPTION
     exception handler statements]

END [ name ];

where each component is used in the following ways:

name: The name of the procedure comes directly after the keyword PROCEDURE.
parameters: An optional list of parameters that you define to both pass information into the procedure and send information out of the procedure, back to the calling program.
declaration statements: The declarations of local identifiers for that procedure. If you do not have any declarations, then there will not be any statements between the IS and BEGIN statements.
executable statements: The statements that the procedure executes when it is called. You must have at least one executable statement after the BEGIN and before the END or EXCEPTION keywords.
exception handler statements: The optional exception handlers for the procedure. If you do not explicitly handle any exceptions, then you can leave out the EXCEPTION keyword and simply terminate the execution section with the END keyword.

Figure 15.9 shows the apply_discount procedure, which contains all four sections of the named PL/SQL block, as well as a parameter list.

Figure 15.9: The apply_discount procedure

15.4.1 Calling a Procedure

A procedure is called as an executable PL/SQL statement. In other words, a call to a procedure must end with a semicolon ( ; ) and be executed before and after other SQL or PL/SQL statements.

The following executable statement runs the apply_discount procedure:

apply_discount( new_company_id, 0.15 );  -- 15% discount

If the procedure does not have any parameters, then you must call the procedure without any parentheses:

display_store_summary;

15.4.2 Procedure Header

The portion of the procedure definition that comes before the IS keyword is called the procedure header. The header provides all the information a programmer needs to call that procedure, namely:

The procedure name
The parameter list, if any

A programmer does not need to know about the inside of the procedure in order to be able to call it properly from another program.

The header for the apply_discount procedure discussed above is:

PROCEDURE apply_discount
   (company_id_in IN company.company_id%TYPE,
    discount_in IN NUMBER)

It consists of the module type, the name, and a list of two parameters.

15.4.3 Procedure Body

The body of the procedure is the code required to implement the procedure. It consists of the declaration, execution, and exception sections of the function. Everything after the IS keyword in the procedure makes up that procedure's body.

Once again, the declaration and exception sections are optional. If you have no exception handlers, you will leave off the EXCEPTION keyword and simply enter the END statement to terminate the procedure.

If you do not have any declarations, the BEGIN statement simply follows immediately after the IS keyword (see the do_nothing procedure below for an example of this structure.).

You must supply at least one executable statement in a procedure. Here is my candidate for the procedure in PL/SQL with the smallest possible body:

PROCEDURE do_nothing IS
BEGIN
   NULL;
END;

Does the do_nothing procedure seem silly? A procedure that doesn't do anything can, in fact, be very useful when you are creating stubs for modules in a top-down design effort. I have also used this kind of procedure when building templates. My do_nothing procedure acts initially as a placeholder in my code, but then also provides a mechanism for customization of the templates.

15.4.4 The END Label

You can append the name of the procedure directly after the END keyword when you complete your procedure, as shown below:

PROCEDURE display_stores (region_in IN VARCHAR2) IS
BEGIN
   ...
END display_stores;

This name serves as a label that explicitly links up the end of the program with its beginning. You should as a matter of habit use an END label. It is especially important to do so when you have a procedure that spans more than a single page, or is one in a series of procedures and functions in a package body.