O'Reilly logo

Oracle SQL: the Essential Reference by David C. Kreines

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required