Stored Objects   «Prev  Next»
Lesson 1

Creating an Oracle Procedure

This module discusses creating procedures using PL/SQL. Procedures are more versatile than functions. While a function returns a single value, a procedure can return no value, or multiple values using outgoing parameters. A function has severe restrictions against modifying database data, while a procedure is allowed to modify data. During this lesson, you will create a procedure and then add a call to a function within that procedure.

Module objectives

By the end of this module, you will know how to:
  1. Distinguish between a function and a procedure
  2. Create a procedure to calculate a percentage and an average for a given sales transaction
  3. List capabilities and restrictions of calling other procedures
  4. Add a function to a procedure
You also will use a simulation to build your own procedure for the House-O-Pets course project.
The next lesson describes a procedure and how it differs from a function.

Why Procedures are needed?

One of the biggest challenges in our profession today is finding ways to reduce the complexity of our environment. When faced with a massive problem to solve, the mind is likely to recoil in horror. Where do I start? How can I possibly find a way through that jungle of requirements and features?
A human being is not a massively parallel computer. Even the brightest of our bunch have trouble keeping track of more than seven tasks (plus or minus two) at one time. We need to break down huge, intimidating projects into smaller, more manageable components, and then further decompose those components into individual programs with an understandable scope. We can then figure out how to build and test those programs, after which we can construct a complete application from these building blocks.
Whether you use top-down design or some other methodology, there is absolutely no doubt that you will find your way to a high-quality and easily maintainable application by modularizing your code into procedures, functions, and object types.

Procedure Declaration

A procedure is a subprogram that can take parameters and be called. Generally, you use a procedure to perform an action. A procedure has two parts:
  1. the specification and
  2. the body.
The specification (spec for short) begins with the keyword PROCEDURE and ends with the procedure name or a parameter list.
Parameter declarations are optional. Procedures that take no parameters are written without parentheses. The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional procedure name.
The procedure body has three parts:
  1. an optional declarative part,
  2. an executable part,
  3. and an optional exception-handling part.
The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution. Note that the procedure declaration in a PL/SQL block or package is not the same as creating a procedure in SQL.