Oracle SQL: the Essential Reference

7.6. Procedures and Packages

Most third-generation languages include the ability to create discrete program units, called subprograms, that can be executed by other programs. PL/SQL also includes this capability. Using PL/SQL, you can implement two types of subprograms: procedures and functions. The characteristics of each are summarized in Table 7-8.

Table 7-8. Characteristics of PL/SQL Procedures and Functions

Characteristic	Procedure	Function
Can accept input parameters	Yes	Yes
Can return parameter values	Yes	Yes
Returns a single value	No	Yes
Can be referenced in an expression	No	Yes
Is stored in the database in compiled form	Yes	Yes

As you can see from Table 7-8, the primary difference between a procedure and a function is that functions return a single value, and can thus be used in a PL/SQL expression anywhere a PL/SQL variable or constant may be used. PL/SQL procedures, on the other hand, do not return values and cannot form part of an expression.

7.6.1. Procedures

A PL/SQL procedure is much like a subroutine, or subprogram, in other languages. It can accept one or more parameters or arguments as input, can operate on these parameters, and may replace the values in one or more of these parameters with new values. The program that invoked the procedure (the calling program) can then access these new values. PL/SQL procedures are created and maintained using the SQL DDL ...