PL/SQL User's Guide and Reference

Procedures

Description

A procedure is a named PL/SQL block, which can take parameters and be invoked. Generally, you use a procedure to perform an action. For more information, see "Procedures"

A procedure has two parts: the specification and the body. The procedure specification begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.

The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These objects are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.

Syntax

procedure_specification ::=

PROCEDURE procedure_name (parameter_declaration[, 
  parameter_declaration]...)];

procedure_body ::=

PROCEDURE procedure_name [(parameter_declaration[, 
  parameter_declaration]...)] IS
   [[object_declaration [object_declaration] ...]
    [subprogram_declaration [subprogram_declaration] ...]]
BEGIN
    seq_of_statements
[EXCEPTION 
    exception_handler [exception_handler] ...]
END [procedure_name];

parameter_declaration ::=

parameter_name [IN | OUT | IN OUT]
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [{:= | DEFAULT} expression]

object_declaration ::=

{  constant_declaration
 | cursor_declaration
 | cursor_variable_declaration
 | exception_declaration
 | plsql_table_declaration
 | record_declaration
 | variable_declaration}

subprogram_declaration ::=

{function_declaration | procedure_declaration}

procedure_name

This identifies a user-defined procedure. For naming conventions, see "Identifiers"

parameter_name

This identifies a formal parameter, which is a variable declared in a procedure specification and referenced in the procedure body.

IN, OUT, IN OUT

These parameter modes define the behavior of formal parameters. An IN parameter lets you pass values to the subprogram being called. An OUT parameter lets you return values to the caller of the subprogram. An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.

:= | DEFAULT

This operator or keyword allows you to initialize IN parameters to default values.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions"

. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope.

plsql_table_name

This identifies a PL/SQL table previously declared within the current scope.

record_name

This identifies a user-defined record previously declared within the current scope.

scalar_type_name

This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2, which must be specified without constraints. For more information, see "Datatypes"

table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

table_name.column_name

This identifies a database table and column that must be accessible when the declaration is elaborated.

variable_name

This identifies a PL/SQL variable previously declared within the current scope.

%ROWTYPE

This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.

%TYPE

This attribute provides the datatype of a field, record, PL/SQL table, database column, or variable.

constant_declaration

This construct declares a constant. For the syntax of constant_declaration, see "Constants and Variables"

cursor_declaration

This construct declares an explicit cursor. For the syntax of cursor_declaration, see "Cursors"

cursor_variable_ declaration

This construct declares a cursor variable. For the syntax of cursor_variable_declaration, see "Cursor Variables"

exception_declaration

This construct declares an exception. For the syntax of exception_declaration, see "Exceptions"

plsql_table_declaration

This construct declares a PL/SQL table. For the syntax of plsql_table_declaration, see "PL/SQL Tables"

record_declaration

This construct declares a user-defined record. For the syntax of record_declaration, see "Records"

variable_declaration

This construct declares a variable. For the syntax of variable_declaration, see "Constants and Variables"

function_declaration

This construct declares a nested function.

procedure_declaration

This construct declares a procedure. For the syntax of procedure_declaration, see "Procedures"

exception_handler

This construct associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exceptions"

Usage Notes

At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement.

A procedure is called as a PL/SQL statement. For example, the procedure raise_salary might be called as follows:

raise_salary(emp_num, amount);

Inside a procedure, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. An OUT parameter acts like an uninitialized variable. So, its value cannot be assigned to another variable or reassigned to itself. An IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value, and its value can be assigned to another variable. For summary information about the parameter modes, see Table 7 - 1 .

Before exiting a procedure, explicitly assign values to all OUT formal parameters. Otherwise, the values of corresponding actual parameters are indeterminate. If you exit successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values. For more information, see "Parameter Default Values" .

You can write the procedure specification and body as a unit. Or, you can separate the procedure specification from its body. That way, you can hide implementation details by placing the procedure in a package. You can define procedures in a package body without declaring their specifications in the package specification. However, such procedures can be called only from inside the package.

Procedures can be defined using any Oracle tool that supports PL/SQL. To become available for general use, however, procedures must be CREATEd and stored in an Oracle database. You can issue the CREATE PROCEDURE statement interactively from SQL*Plus or Server Manager. For the full syntax of the CREATE PROCEDURE statement, see Oracle7 Server SQL Reference.

Examples

The following procedure debits a bank account:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
   old_balance REAL;
   new_balance REAL;
   overdrawn   EXCEPTION;
BEGIN
   SELECT bal INTO old_balance FROM accts WHERE acctno = acct_id;
   new_balance := old_balance - amount;
   IF new_balance < 0 THEN
      RAISE overdrawn;
   ELSE
      UPDATE accts SET bal = new_balance WHERE acctno = acct_id;
   END IF;
EXCEPTION
   WHEN overdrawn THEN
      ...
END debit_account;

In the following example, you call the procedure using named notation:

debit_account(amount => 500, acct_id => 10261);