Oracle7 Server Concepts

An Introduction to Stored Procedures and Packages

Oracle allows you to access and manipulate database information using procedural schema objects called PL/SQL program units. Procedures, functions, and packages are all examples of PL/SQL program units.

Stored Procedures and Functions

A procedure or function is a schema object that logically groups a set of SQL and other PL/SQL programming language statements together to perform a specific task. Procedures and functions are created in a user's schema and stored in a database for continued use. You can execute a procedure or function interactively using an Oracle tool, such as SQL*Plus, or call it explicitly in the code of a database application, such as an Oracle Forms or Precompiler application, or in the code of another procedure or trigger. Figure 14 - 1 illustrates a simple procedure stored in the database, being called by several different database applications.

Figure 14 - 1. A Stored Procedure

The stored procedure in Figure 14 - 1, which inserts an employee record into the EMP table, is shown in Figure 14 - 2.

Figure 14 - 2. The HIRE_EMP Procedure

All of the database applications in Figure 14 - 1 call the HIRE_EMP procedure. Alternatively, a privileged user might use Server Manager to execute the HIRE_EMP procedure using the following statement:

EXECUTE hire_emp ('TSMITH', 'CLERK', 1037, SYSDATE, \

	              500, NULL, 20);

This statement places a new employee record for TSMITH in the EMP table.

Packages

A package is a group of related procedures and functions, together with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users. Figure 14 - 3 illustrates a package that encapsulates a number of procedures used to manage an employee database.

Figure 14 - 3. A Stored Package

Database applications explicitly call packaged procedures as necessary. After being granted the privileges for the EMP_MGMT package, a user can explicitly execute any of the procedures contained in it. For example, the following statement might be issued using Server Manager to execute the HIRE_EMP package procedure:

EXECUTE emp_mgmt.hire_emp ('TSMITH', 'CLERK', 1037, \

				SYSDATE, 500, NULL, 20);

Packages offer several development and performance advantages over standalone stored procedures. These advantages are described in the section "Packages" .

PL/SQL

PL/SQL is Oracle's procedural language extension to SQL. It extends SQL with flow control and other statements that make it possible to write complex programs in it. The PL/SQL engine is the tool you use to define, compile, and execute PL/SQL program units. This engine is a special component of many Oracle products, including Oracle Server.

While many Oracle products have PL/SQL components, this chapter specifically covers the procedures and packages that can be stored in an Oracle database and processed using the Oracle Server PL/SQL engine. The PL/SQL capabilities of each Oracle tool are described in the appropriate tool's documentation.

For more information about PL/SQL, see the section "PL/SQL" .