Lab 12.1 Creating Procedures

Lab Objectives

After this Lab, you will be able to:

Create Procedures

Query the Data Dictionary for Information on Procedures

Benefits of Modular Code

A PL/SQL module is any complete logical unit of work. There are four types of PL/SQL modules: (1) anonymous blocks that are run with a text script (this is the type you have used until now), (2) procedures, (3) functions, and (4) packages.

There are two main benefits to using modular code: (1) It is more reusable and (2) it is more manageable.

You create a procedure either in SQL*Plus or in one of the many tools for creating and debugging stored PL/SQL code. If you are using SQL*Plus, you will need to write your code in a text editor and then run it at the SQL*Plus prompt.

Block Structure

The block structure is common for all the module types. The block begins with a header (for named blocks only), which consists of (1) the name of the module, and (2) a parameter list (if used).

The Declaration section consists of variable, cursors , and subblocks that will be needed in the next section.

The main part of the module is the Execution section, where all the calculations and processing is performed. This will contain executable code such as IF-THEN-ELSE, LOOPS, calls to other PL/SQL modules, and so on.

The last section of the module is an optional exception handler, which is where the code to handle exceptions is placed.

Anonymous Block

Until this chapter, you have only been writing anonymous blocks. Anonymous blocks are very much the same as modules, which were just introduced (except anonymous blocks do not have headers). There are important distinctions, though. As the name implies, anonymous blocks have no name and thus cannot be called by another block. They are not stored in the database and must be compiled and then run each time the script is loaded.

The PL/SQL block in a subprogram is a named block that can accept parameters and can be invoked from an application that can communicate with the Oracle database server. A subprogram can be compiled and stored in the database. This allows the programmer to reuse the program. It also provides for easier maintenance of code. Subprograms are either procedures or functions.

Procedures

A procedure is a module performing one or more actions; it does not need to return any values. The syntax for creating a procedure is as follows :

 CREATE OR REPLACE PROCEDURE name    [(parameter[, parameter, ...])] AS    [local declarations] BEGIN    executable statements [EXCEPTION    exception handlers] END [name]; 

A procedure may have 0 to many parameters. This will be covered in the next lab. Every procedure has two parts : (1) the header portion, which comes before AS (sometimes you will see IS ”they are interchangeable), keyword (this contains the procedure name and the parameter list), and (2) the body, which is everything after the IS keyword. The word REPLACE is optional. When the word REPLACE is not used in the header of the procedure, in order to change the code in the procedure, the procedure must be dropped first and then re-created. Since it is very common to change the code of the procedure, especially when it is under development, it is strongly recommended to use the OR REPLACE option.