Oracle PL/SQL Programming, 5th Edition

Procedures

A procedure is a module that performs one or more actions. Because a procedure call 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 [schema.]name[( parameter[, parameter...] ) ]
   [AUTHID DEFINER | CURRENT_USER]
IS
   [declarations]

BEGIN
   executable statements 

[ EXCEPTION
     exception handlers]

 END [name];

where each element is used in the following ways:

schema

Optional name of the schema that will own this procedure. The default is the current user. If different from the current user, that user will need privileges to create a procedure in another schema.

name

The name of the procedure.

parameters

An optional list of parameters that you define to both pass information to the procedure, and send information out of the procedure back to the calling program.

AUTHID clause

Determines whether the procedure will execute with the privileges of the definer (owner) of the procedure or the current user. The former is known as the definer rights model, the latter as the invoker rights model. These models are described in detail in Chapter 24.

declarations

The declarations of local identifiers for that procedure. If you do not have any declarations, there will be no statements between the IS and BEGIN statements.

executable statements

The statements ...