O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required