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 ...

Get Oracle SQL: the Essential Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.