Oracle PL/SQL Programming, Third 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, which 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.

AUTHID clause

Determines whether the procedure will execute under the authority of the definer (owner) of the procedure or under the authority of the current user. The former is known as the definer rights model, the latter as the invoker rights model.

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. ...