Oracle PL/SQL Language Pocket Reference, 4th Edition

Stored Procedures and Functions

PL/SQL allows you to create a variety of named program units, or containers, for your code. These include:

Procedure

A program that executes one or more statements

Function

A program that returns a value

Trigger

A program that executes in response to database changes

Package

A container for procedures, functions, and data structures

Object type

Oracle’s version of an object-oriented class; object types can contain member procedures and functions

The following sections describe stored procedures and functions. Later sections describe triggers, packages, and object types.

Procedures

Procedures are program units that execute one or more statements and can receive or return zero or more values through their parameter lists. The syntax of a procedure is:

CREATE [OR REPLACE] PROCEDURE name
   [ (parameter
 [,parameter]) ]
   [AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }
   declaration_section
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

Inside a PL/SQL executable section, a procedure is called as a standalone executable statement:

apply_discount(new_company_id, 0.15);

Many execution environments, such as SQL*Plus, also support ANSI SQL’s CALL syntax:

CALL apply_discount(new_company_id, 0.15);

However, SQL*Plus programmers commonly invoke procedures with the SQL*Plus EXECUTE command:

EXECUTE apply_discount(new_company_id, 0.15);

or the equivalent anonymous block:

BEGIN
   apply_discount(new_company_id, 0.15);
END;

Functions

Functions are program units that execute zero ...