Oracle PL/SQL Language Pocket Reference, Second Edition

Named Program Units

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

Procedure

A program that executes one or more statements

Function

A program that returns a value

Package

A container for procedures, functions, and data structures

Trigger

A program that executes in response to database changes

Object type

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

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 } ]
   [DETERMINISTIC] 
{ IS | AS }
   declaration_section
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A procedure is called as a standalone executable PL/SQL statement:

apply_discount(new_company_id, 0.15);

Functions

Functions are program units that execute zero or more statements and return a value through the RETURN clause. Functions can also receive or return zero or more values through their parameter lists. The syntax of a function is:

CREATE [OR REPLACE] FUNCTION name 
   [ (parameter [,parameter]) ] 
   RETURN return_datatype
   [AUTHID { CURRENT_USER | DEFINER } ] 
   [DETERMINISTIC] 
   [PARALLEL_ENABLE] 
   [PIPELINED]
   [AGGREGATE USING]
{ IS | AS }
   [declaration_section]
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A function must have at ...