Oracle PL/SQL Language Pocket Reference

Named Program Units

The PL/SQL programming language allows you to create a variety of named program units (containers for code). They 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

Triggers

Programs that execute in response to database changes

Object type

Oracle8’s version of a SQL3 named row type; 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 ] -- Oracle8i
   [DETERMINISTIC]                   -- Oracle8i
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) --15% discount

Functions

Functions are program units that execute one 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 ] -- Oracle8i [DETERMINISTIC] -- Oracle8i [PARALLEL_ENABLE] -- Oracle8i IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] ...