WEEK 1

In Review

Here you are at the end of Week 1. By now you should have a good grasp of the basics of PL/SQL. On the first day, you read about the relationship of PL/SQL to other Oracle products, and you also saw your first example of a short, but still useful, PL/SQL function. On the second day, you had a chance to learn about some of the choices you have in terms of a development environment. Day 3 covers the various PL/SQL datatypes and gives examples of how they are used. During the following day, Day 4, you went over PL/SQL expressions and operators. You learned how operator precedence affects an expression's order of evaluation and how you can control that by using parentheses. You learned about three-valued logic and the effect of null values on an expression's evaluation, and you learned how data is converted from one type to another. Days 5 and 6 cover the PL/SQL statements that control program flow and execution. These include IF statements, FOR loops, and WHILE loops. The week concluded with Day 7 and a discussion of the many powerful, built-in functions that Oracle provides as part of PL/SQL.

WEEK 2

At a Glance

Congratulations! You completed Week 1 and are now ready to take on Week 2. During Week 1, you learned many of the building blocks and core features of PL/SQL.

Where You Are Going

During Week 2 you will take some of the features you learned in Week 1 and learn more complex methods of using them. In this second week, you will begin to integrate your PL/SQL code with the Oracle database as well as begin to store your code inside the database. You will create some sample tables and then use your PL/SQL code to manipulate data in these tables. By the end of the week, you will know how to perform

multiple row queries and process the rows one at a time, all within PL/SQL. On Day 13, you will be introduced to the new Oracle8 database objects. Additionally, in Week 2, you will learn how to code your programs to anticipate and handle many different runtime errors that can occur in your PL/SQL code.

Great job in completing Week 1! Now continue with Week 2, starting with Day 8.

Week 2

Day 8

Procedures, Packages,
Errors, and Exceptions

by Tom Luers

Procedures and packages enable you to organize your code into logical groups for easier maintenance and implementation. Likewise, these groups have built-in error trapping to prevent the code from abnormally stopping during processing.

Procedures

A procedure is a logically grouped set of SQL and PL/SQL statements that perform a specific task. It's a miniature self-contained program. A stored procedure is a procedure that has been compiled and stored inside the database. At this point, the procedure is a schema (specific database) object.

Procedures have several parts. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. Procedures can be declared in
PL/SQL blocks, packages, and other procedures. The executable part contains statements that control execution and manipulate data. Occasionally, the procedure will contain an exception-handling part to deal with exceptions raised during execution. Procedures can be defined and executed by using any Oracle tool that supports PL/SQL, like SQL*Plus.

Why Use Procedures?

Procedures are created to solve a specific problem or task. PL/SQL procedures offer the following advantages:

• In PL/SQL, you can tailor your procedure to suit your specific requirements.
• These procedures are modular; that is, they let you break a program down into manageable, well-defined units.
• Because procedures are stored in the database, they are reusable. Once validated, procedures can be used over and over without recompiling or distributing them over the network.
• Procedures improve database security. You can restrict database access by allowing them to access data only through stored procedures.
• Procedures improve memory by taking advantage of shared memory resources.

Procedures Versus Functions

Procedures and functions are PL/SQL subprograms that are stored in the database. The significant difference between the two is simply the types of output the two objects generate. A function returns a single value, whereas a procedure is used to perform complicated processing when you want a substantial amount of information back.

Creating Procedures

The CREATE PROCEDURE command creates a procedure.

The Syntax for the CREATE PROCEDURE Command

CREATE OR REPLACE PROCEDURE procedure_name
(arguments)
AS
 [pl/sql body code]

In this syntax, the keywords and parameters are as follows:

• OR REPLACE—This keyword is optional, but I strongly suggest you always use it. This keyword will re-create the procedure if it already exists. You can use this keyword to change an existing procedure without having to drop and re-create the procedure.
• procedure_name—The name you assign to the procedure being created.
• arguments—The arguments in the procedure, which can be the following:
• in—This parameter specifies that you must pass a value to the subprogram being called. The in parameter might not be assigned a value because it acts like a constant. The actual value that corresponds to the parameter can be a constant, literal, initialized variable, or expression.
• out—Specifies that the procedure returns a value to the calling program. This parameter acts like an uninitialized parameter; therefore, its value cannot be assigned to another variable. The actual value that corresponds to the parameter must be a variable. It cannot be a literal, constant, or expression. Within your subprogram, the out parameter must be assigned a value.
• inout—Specifies that you must pass a value to the procedure and that the procedure returns a value to its calling environment after execution.
• pl/sql body code—The logic of the procedure.

The code shown in Listing 8.1 creates a simple stored procedure. This procedure accepts two arguments, the part_id and the qty.

INPUT
Listing 8.1. Creating a stored procedure.

CREATE OR REPLACE PROCEDURE parts (part_id number, qty number)

AS BEGIN
UPDATE journal
set journal.qty = journal.qty + qty
WHERE journal_id = part_id
END;

Normally, procedures are created as standalone schema objects. However, you can create a procedure as part of a package; this topic is discussed later in this chapter in the section titled "Packages."

The RETURN Statement

The RETURN statement causes a subprogram to immediately complete its execution and return to the calling program. Execution in the calling program resumes with the statement following the procedure call.