Oracle PL/SQL for DBAs

Procedures, Functions, and Packages

PL/SQL offers the following structures to modularize your code in different ways:

Procedure

A program that performs one or more actions and is called as an executable PL/SQL statement. You can pass information into and out of a procedure through its parameter list.

Function

A program that returns a single value and is used just like a PL/SQL expression. You can pass information into a function through its parameter list.

Package

A named collection of procedures, functions, types, and variables. A package is not really a module (it’s more of a meta-module), but it is so closely related that I mention it here.

Database trigger

A set of commands that are triggered to execute (e.g., log in, modify a row in a table, execute a DDL statement) when an event occurs in the database.

Object type or instance of an object type.

Oracle’s version of (or attempt to emulate) an object-oriented class. Object types encapsulate state and behavior, combining data (like a relational table) with rules (procedures and functions that operate on that data).

In this section, we’ll describe procedures, functions, and packages . Triggers are described in a later section. Object types are not covered in this chapter; few developers and even fewer database administrators use Oracle’s version of object-oriented language features.