Oracle PL/SQL for DBAs

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.

Structure of a procedure

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

    PROCEDURE [schema.]name [( parameter [, parameter ...] ) ]
       [AUTHID DEFINER | CURRENT_USER]
    IS
       [declaration statements]
    BEGIN
       executable statements
    [ EXCEPTION
         exception handler statements]
    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

Name of the procedure, which comes directly after the keyword PROCEDURE.

parameters

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 .

declaration statements

Declarations of local identifiers for that procedure. If you do not have any declarations, there will ...