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:
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.
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:
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.
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.
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.