Back Up Next

Chapter 5 *

Introducing PL/SQL *

Overview of PL/SQL *

Using PL/SQL to Access Oracle *

Exercises *

PL/SQL Program Constructs *

Modularity *

Procedures, Functions, Triggers, and Packages *

Components of a PL/SQL Block *

Process Flow and Logic Constructs *

Cursors *

Error Handling *

Exercises *

Developing a PL/SQL Block *

Declaring and Using Variables *

Database Datatypes *

Nondatabase Datatypes *

%TYPE *

%ROWTYPE *

Constant Declaration *

Exercises *

Variable Value Assignment *

Exercises *

Interacting with the Oracle Database *

Using SQL Statements in PL/SQL *

Exercises *

Using Implicit Cursor Attributes *

Exercises *

Transaction Processing in PL/SQL *

Exercises *

Controlling PL/SQL Process Flow *

Conditional Statements and Process Flow *

Exercises *

Using Loops *

LOOP-EXIT Statements *

WHILE-LOOP Statements *

FOR-LOOP Statements *

Exercises *

Explicit Cursor Handling *

Implicit vs. Explicit Cursors *

Exercises *

Declaring and Using Explicit Cursors *

Exercises *

Parameters and Explicit Cursors *

Exercises *

Writing CURSOR FOR Loops *

Exercises *

Error Handling *

The Three Basic Types of Exceptions *

Predefined Exceptions *

User-defined Exceptions *

The EXCEPTION_INIT Pragma *

Exercises *

Identifying Common Exceptions *

Exercises *

Coding the Exception Handler *

Exercises *

Chapter Summary *

Two-Minute Drill *

 

Chapter 5

Introducing PL/SQL

In this chapter, you will understand and demonstrate knowledge in the following areas:

Overview of PL/SQL
Developing a PL/SQL block
Interacting with the Oracle database
Controlling PL/SQL process flow
Explicit cursor handling
Error handling

In Oracle, there is a special language available for developers to code stored procedures that seamlessly integrate with database object access via the language of database objects, SQL. However, this language offers far more execution potential than simple updates, selects, inserts, and deletes. This language offers a procedural extension that allows for modularity, variable declaration, loops and other logic constructs, and advanced error handling. This language is known as PL/SQL. This chapter will present an overview of PL/SQL syntax, constructs, and usage. This information is tested on OCP Exam 1, and comprises 22 percent of the test material.

Overview of PL/SQL

In this section, you will cover the following topics related to overview of PL/SQL:

Using PL/SQL to access Oracle
Variable value assignment

PL/SQL offers many advantages over other programming languages for handling the logic and business rule enforcement of database applications. It is a straightforward language with all the common logic constructs associated with a programming language, plus many things other languages don’t have, such as robust error handling and modularization of code blocks. The PL/SQL code used to interface with the database is also stored directly on the Oracle database, and is the only programming language that interfaces with the Oracle database natively and within the database environment. This overview will cover the details of benefits associated with using PL/SQL in the Oracle database and the basic constructs of the PL/SQL language.

Using PL/SQL to Access Oracle

Many applications that use client/server architecture have one thing in common—a difficulty maintaining the business rules for an application. When business rules are decentralized throughout the application, the developers must make changes throughout the application and implement system testing that will determine whether the changes are sufficient. However, in tight scheduling situations, the first deployment item to get left off is almost invariably testing. One logical design change that should be implemented in this scenario is the centralization of logic in the application to allow for easier management of change. In systems that use the Oracle database, a "middle layer" of application logic can be designed with PL/SQL. The benefits are as follows:

PL/SQL is managed centrally within the Oracle database. The DBA manages source code and execution privileges with the same syntax used to manage other database objects.
PL/SQL communicates natively with other Oracle database objects.
PL/SQL is easy to read and has many features for code modularity and error handling.

The features of PL/SQL that manage centralized code management make PL/SQL the logical choice for a database-centric client/server application that uses stored procedures for business logic and allows the client application developer to focus mainly on the user interface. Storing application logic centrally means only having to compile a change once, and then it is immediately accessible to all users of the application. With business logic stored in the client application, the effort of distributing code includes the recompilation of the client application (potentially on several different platforms). There is an additional distribution cost to getting the new executable version of the client on every user’s desktop, as well as overhead for communication and support to make sure all users of the application are on the right version. Decentralized computing has increased the capacity of organizations to provide easy-to-use and fast applications to their customers. But some centralization improves the job even further by allowing the application development shop the ability to eliminate distribution channels for business logic changes and focus the client-side developers’ efforts on the client application. Figure 5-1 shows an example of the difference between centralized and decentralized business logic code management.

Common copy of executed code

Decentralized execution
(increased maintenance and more distribution effort)

Fig05-01.jpg (24876 bytes)

Figure 1: Centralized vs. decentralized business logic code management

Exercises

  1. What are some advantages of using PL/SQL to access the database?
  2. Where is PL/SQL compiled and stored?

PL/SQL Program Constructs

There are many different programming constructs to PL/SQL, from the various types of modules available, to the components of a PL/SQL block, to the logic constructs that manage process flow. This section will identify each component of the PL/SQL language and give some highlights about each area of the language.

Modularity

PL/SQL allows the developer to create program modules to improve software reusability and to hide the complexity of the execution of a specific operation behind a name. For example, there may be a complex process involved to an employee record to a corporate database, which requires records to be added to several different tables for several different applications. Stored procedures may handle the addition of records to each of the systems, making it look to the user that the only step required is entering data on one screen. In reality, that screen’s worth of data entry may call dozens of separate procedures, each designed to handle one small component of the overall process of adding the employee. These components may even be reused data entry code blocks from the various pension, health care, day care, payroll, and other HR applications, which have simply been repackaged around this new data entry screen. Figure 5-2 shows how modularity can be implemented in PL/SQL blocks.

Fig05-02.jpg (15509 bytes)

Figure 2: Modularity and PL/SQL blocks

Procedures, Functions, Triggers, and Packages

The modules of PL/SQL code are divided into four categories. Those categories are stored procedures, functions, packages, and triggers. To summarize, the four types of PL/SQL code blocks are as follows.

Procedure—A series of statements accepting and/or returning zero or more variables.
Function—A series of statements accepting zero or more variables that returns one value.
Package—A collection of procedures and functions that has two parts, a specification listing available procedures and functions and their parameters, and a body that contains the actual code for the procedures and functions.
Trigger—A series of PL/SQL statements attached to a database table that execute whenever a triggering event (select, update, insert, delete) occurs.

Components of a PL/SQL Block

There are three components of any PL/SQL block named in the previous section. Those components are the variable declaration section, the executable section, and the exception handler. The declaration section contains identification of all variable constructs that will be used in the code block. A variable can be of any datatype available in the Oracle database, as well as of some other types exclusive to PL/SQL. The executable section of a PL/SQL block starts with the begin keyword and ends either with the end keyword for the entire code block or with the exceptions keyword. The final component of a PL/SQL block is the exception handler. This code portion defines all errors that may occur in the block and specifies how they should be handled. The exception handler is optional in PL/SQL. There are two types of code blocks in PL/SQL—named blocks and anonymous blocks. The first example in this section is a named block of PL/SQL code—a function. It contains a declaration block, an executable block, and an exception handler.

FUNCTION convert_money
(amount IN NUMBER,
from_currency IN VARCHAR2,
to_currency IN VARCHAR2
) IS /* denotes the beginning of the declaration section—if no variables */
/* to declare, use the AS keyword instead of IS. */
My_new_amt number := 0;
BEGIN /* begins the executable section of a code block. /
IF (b>3) THEN
DBMS_OUTPUT.PUT_LINE(‘Hello, World.’);
ELSE
RAISE bad_data;
END IF;
EXCEPTIONS /*Begins the Exception Handler */
WHEN bad_data THEN
DBMS._OUTPUT.PUT_LINE(‘Error condition’);
END;

The other class of PL/SQL blocks is known as an unnamed or anonymous block. It is easier to identify the declaration section of an anonymous PL/SQL block because the declaration section is preceded by the declare keyword. It to contains a declaration section, an executable section, and an exception handler.

DECLARE /* begins the declaration section in an anonymous block */
My_convert_amt NUMBER;
My_convert_currency VARCHAR2;
My_old_currency VARCHAR2;
BEGIN /* begins the executable section of a code block. /
IF (b>3) THEN
DBMS_OUTPUT.PUT_LINE(‘Hello, World.’);
ELSE
RAISE bad_data;
END IF;
EXCEPTIONS /*Begins the Exception Handler */
WHEN bad_data THEN
DBMS._OUTPUT.PUT_LINE(‘Error condition’);
END;

TIP: The call to DBMS_OUTPUT.PUT_LINE in one of the code blocks above is used to write a line of output to the SQL*Plus interface. In order to view the line of output produced, use the set serveroutput on command.

Process Flow and Logic Constructs

PL/SQL offers the programmer logic constructs such as for loops, while loops, if-then-else statements, assignments, and expressions. Other logic constructs include PL/SQL tables and records. These "procedural" constructs are the items in PL/SQL that allow it to be both a programming language for supporting business rules and a functional language for providing data.

Cursors

One of the real strengths of PL/SQL, however, is its ability to handle cursors. A cursor is a handle to an address in memory that stores the results of an executed SQL statement. They are extremely useful for performing operations on each row returned from a select statement. Therefore, the PL/SQL programmer often finds herself using the looping procedural constructs of PL/SQL in conjunction with cursor manipulation operations.

Error Handling

Errors are called exceptions in PL/SQL, and they are checked implicitly anywhere in the code block. If at any time an error occurs in the code block, the exception corresponding to that error can be raised. At that point, execution in the executable code block stops and control is transferred to the exception handler. There are many different types of exceptions in Oracle, some of which are user defined. Others are defined by the Oracle PL/SQL engine.

Exercises

  1. What is PL/SQL? Name some benefits to accessing the Oracle database with PL/SQL.
  2. What are the three parts of a PL/SQL code block? Name four different types of code blocks in Oracle. What are some program constructs available in PL/SQL?
  3. What is the difference between a named and an anonymous code block?

Developing a PL/SQL Block

In this section, you will cover the following topics related to developing a simple PL/SQL block:

Declaring and using variables
Variable value assignment

A sample PL/SQL block has already been offered. This section will cover in more detail some of the technical aspects of creating PL/SQL blocks. The topics that will be covered in this section include advanced usage and declaration of variables and constants in the declarative section of the PL/SQL block, and a refresher on assigning values to variables in the executable section.

Declaring and Using Variables

PL/SQL offers a great deal of flexibility in variable declaration. So far, two examples of variable declaration in different code blocks have been presented. Both of these examples used simple declaration of datatypes. The ones used were datatypes that have been presented as valid datatypes on the Oracle database.

Database Datatypes

There are several datatypes that can be used in PL/SQL that correspond to the datatypes used on the database. These types are as follows:

NUMBER Used to store any number.
CHAR(size), VARCHAR2(size) Used to store alphanumeric text strings. The CHAR datatype pads the value stored to the full length of the variable with blanks.
DATE Used to store dates.
LONG Stores large blocks of text, up to 2 gigabytes in length.
LONG RAW Stores large blocks of data stored in binary format.
RAW Stores smaller blocks of data stored in binary format.
MLSLABEL Used in Trusted Oracle.
ROWID Used to store the special format of ROWIDs on the database.

Nondatabase Datatypes

There are also several other PL/SQL datatypes that are not designed for use in storing data to a table:

DEC, DECIMAL, REAL, DOUBLE_PRECISION These numeric datatypes are a subset of the NUMBER datatype that is used for column declaration in the Oracle database.
INTEGER, INT, SMALLINT, NATURAL, POSITIVE, NUMERIC These numeric datatypes are a subset of the NUMBER datatype that is used for column declaration in the Oracle database.
BINARY_INTEGER This datatype is considered separately from the others, because it is stored in binary format. A variable in BINARY_INTEGER format cannot be stored in the database without conversion first.
CHARACTER Another name for the CHAR datatype.
VARCHAR Another name for the VARCHAR2 datatype.
BOOLEAN Stores a TRUE/FALSE value.
TABLE/RECORD Tables can be used to store the equivalent of an array, while records store variables with composite datatypes.

%TYPE

In general, the variables that deal with table columns should have the same datatype as the column itself. Rather than look it up, the developer can use PL/SQL’s special syntactic feature that allows the developer simply to identify the table column to which this variable’s datatype should correspond. This syntax uses a special keyword known as %type. When using the %type column, all the developer needs to know is the name of the column and the table to which the variable will correspond. Additionally, a variable can be declared with an initialization value by setting it equal to the value in the declaration section. Notice the characters used to set the variable to a value:

DECLARE
My_employee_id employee.empid%TYPE;
BEGIN …

DECLARE
My_salary employee.salary%TYPE := 0;
My_lastname employee.lastname%TYPE:= ‘SMITH’;

BEGIN …

%ROWTYPE

There is another variable declaration method that uses the same reference principle described in the text above. It is called %rowtype, and it permits the developer to create a composite datatype in which all the columns of a row in the table referenced are lumped together into a record. For example, if the EMPLOYEE table contains four columns—EMPID, LASTNAME, FIRSTNAME, and SALARY—and the developer wants to manipulate the values in each column of a row using only one referenced variable, the variable can be declared with the %rowtype keyword. Compare the use of %rowtype to manual record declaration:

DECLARE
My_employee employee%ROWTYPE;
BEGIN …

or manually:

DECLARE
TYPE t_employee IS RECORD (
My_empid employee.empid%TYPE,
My_lastname employee.lastname%TYPE,
My_firstname employee.firstname%TYPE,
My_salary employee.salary%TYPE);

My_employee t_employee;
BEGIN …

TIP: Blocks of PL/SQL code can be nested, that is to say that a procedure can have subprocedures. In which case, the same principles of variable scope discussed in Chapter 2 also apply to nested PL/SQL blocks.

Constant Declaration

It may be useful for a developer to declare constants in the declaration section of the PL/SQL blocks developed as well. Constants make a good substitute for the use of hard-coded values, or "magic numbers." A magic value in programming is a value that is required to perform an operation or calculation but does not have any sort of meaning in the code block to help others identify why the value is there. Take, for example, a function that calculates the area of a circle, which is the number pi times radius squared. The number pi is well known to most people, but imagine if it was not, how difficult it would be to understand the reason for having the number 3.141592658 in the middle of the function.

CREATE FUNCTION find_circle_area (
p_radius IN circle.radius%TYPE
) RETURN NUMBER IS
My_area number := 0;
Pi constant number := 3.14159265358;
BEGIN
My_area := (p_radius*p_radius)* Pi;
Return my_area;
END;

Exercises

  1. Identify some of the database and nondatabase datatypes in PL/SQL that can be used.
  2. How can the developer declare PL/SQL variables without explicitly identifying the datatype?
  3. How does the developer declare a variable with an initialized value?
  4. How does the developer declare a constant? Why might the developer use a constant?

Variable Value Assignment

As noted, it is possible to assign an initial value to a variable in the declaration section of the code block, and it is also possible to assign a value to a variable at any point during execution by using the assignment character—the colon followed by an equals sign. Note that the use of the equality (=) operation is for comparison only. Note also that variable assignment can be accomplished in a variety of ways in the executable section, including the use of the return value from a function call to populate a variable, or the use of the current value in a variable in an arithmetic equation to produce a new value.

DECLARE
My_area circle.area%TYPE := 0;
BEGIN
My_area := find_circle_area(493);
My_area := my_area + 45;
END;

Exercises

  1. Where can a variable be assigned a value?
  2. What is the assignment operator? How does it differ from the equality operator?

Interacting with the Oracle Database

In this section, you will cover the following topics related to interacting with Oracle:

Using select, insert, update and delete in PL/SQL code
Using implicit cursor attributes
Transaction processing in PL/SQL

No usage of PL/SQL is complete without presenting the ease of use involved in interacting with the Oracle database. Any data manipulation or change operation can be accomplished within PL/SQL without the additional overhead typically required in other programming environments. There is no ODBC interface, and no embedding is required for use of database manipulation with PL/SQL.

Using SQL Statements in PL/SQL

Using a select statement in a PL/SQL code block shows how seamless the integration of PL/SQL and the Oracle database is. Note that there are no special characters that must precede the PL/SQL variables in SQL statements. The one concession PL/SQL must make is the into clause, which places the return values from the select statement into the %rowtype record created in the declaration section. Even so, the utility for declaring a complex record with %rowtype has already been proven to be more efficient than manual record declaration. The same ease of use can be seen in update statements. The use of the record.element notation to refer to the components of a record variable is illustrated as well. Using insert statements in PL/SQL is as straightforward as the other statements available in PL/SQL. The same is true for the insert statement. And, as one might expect, the usage of delete is as straightforward as the usage of other SQL statements in PL/SQL.

DECLARE
My_employee employee%ROWTYPE;
My_lastname VARCHAR2 := ‘SAMSON’;
My_firstname VARCHAR2 := ‘DELILAH’;
My_salary NUMBER := 49500;
BEGIN
SELECT *
INTO my_employee
FROM employee
WHERE empid = 49594;

UPDATE employee
SET salary = my_employee.my_salary + 10000
WHERE empid = my_employee.my_empid;

INSERT INTO employee (empid, lastname, firstname, salary)
VALUES (emp_sequence.nextval, my_lastname, my_firstname, my_salary);

My_empid := 59495;

DELETE FROM employee
WHERE empid = my_empid;
END;

Exercises

  1. What special characters are required for using data manipulation statements in PL/SQL?
  2. Explain how Oracle assigns values to elements in a record.

Using Implicit Cursor Attributes

After the SQL statement executes, several things can happen that a developer may care about. For example, assume that a block of code is designed to select data from a table. If there is no data selected, then a special message should appear to let the user of the PL/SQL block know that no data was found. There are two ways to handle this situation. The first option is straightforward enough. Check the variable into which data from the select statement will be placed. If the variable is NULL, no data was found. However, the second option is the more powerful and elegant of the two. The developer can use cursor attributes. Cursor attributes are a set of built-in "checks" that a developer can use to identify when certain situations occur during SQL statement processing in PL/SQL blocks. A list of the cursor attributes that will be discussed in this section are listed in the following series of bullets:

%notfound Identifies whether the fetch executed on the cursor did not return a row. Returns a TRUE or FALSE value, the opposite of that which is returned by %found.
%rowcount Identifies the number of rows that were processed by this cursor. Returns a numeric value.
%found Identifies whether the fetch executed on the cursor did not return a row. Returns a TRUE or FALSE value, the opposite of that which is returned by %notfound.
%isopen Identifies whether the cursor referred to is opened and ready for use. Returns a TRUE or FALSE value.

In order to understand fully the use of cursor attributes, a discussion of cursor processing is required. For now, it is sufficient to know that cursors are generally named something, and that the syntax for using the cursor attributes identified above is to identify the cursor name, followed by the attribute. This syntax is similar to that used for the %type and %rowtype variable declaration attributes. For example, the open or close status of cursor EMPLOYEES can be referred to by its cursor attribute, with the user entering EMPLOYEES%isopen, which will return TRUE if the cursor is open or FALSE if the cursor is closed. More details about using cursor attributes and general cursor processing appears later in the chapter.

Exercises

  1. What value can implicit cursor attributes serve in PL/SQL code?
  2. What are some of the implicit cursor attributes a developer can use in PL/SQL?

Transaction Processing in PL/SQL

The same options for transaction processing that are available in SQL statement processing are available in PL/SQL processing. Those options include specifications that name the beginning, middle, and end of a transaction. The database options that provide lock mechanisms to ensure that only one user at a time has the ability to change a record in the database is still available within the database, regardless of whether SQL or PL/SQL is used to reference the database objects.

The three transaction specifications available in PL/SQL are commit, savepoint, and rollback. An important distinction to make between executing SQL statements in PL/SQL blocks and the iterative entering of SQL statements with SQL*Plus is that the beginning and end of a PL/SQL block does not generally denote the beginning or end of a transaction. The beginning of a transaction in the PL/SQL block is the execution of the first SQL data change statement. In general, in order to guarantee that statements executed that make changes in the database have those changes saved, the PL/SQL code block should explicitly contain a commit statement. Likewise, to discard changes made or to specify a midpoint in a transaction, the developer should code in rollback and savepoint operations appropriately. Also, the set transaction statement is available in PL/SQL to denote the beginning of the transaction, to assign the transaction to a rollback segment, or to set the transaction’s database access to read only. One final option for controlling transactions is the DBMS_TRANSACTION procedure. Within this package, there are several different functions that allow the user to start, end, and moderate the transaction processing within PL/SQL blocks.

Exercises

  1. What transaction processing features are available in PL/SQL?
  2. What is DBMS_TRANSACTION?

Controlling PL/SQL Process Flow

In this section, you will cover the following topics related to controlling PL/SQL process flow:

Conditional statements and process flow
Using loops

No programming language is complete without the use of semantic devices to control the processing flow of its code. Some mention has already been made of the two categories of PL/SQL process flow statements, which are conditional expressions and loops. This section will cover the details of using both conditions and loops to moderate the processing of a PL/SQL block. As these concepts are fairly standard among procedural programming languages such as COBOL or C, most developers with programming experience should have no problem with the concepts. The more specific area of the chapter, and the one that will be tested in OCP Exam 1, is the area of syntax and appropriate usage. The developer should focus on these areas to gain the best background in preparation of the test.

Conditional Statements and Process Flow

A condition in a program equates directly with the idea of making a decision. The fundamental idea behind conditional processing is that of Boolean logic. Boolean logic, named for Charles Boole, a mathematician from the 19th century, is TRUE or FALSE logic. Some of the questions at the end of this chapter illustrate the idea behind TRUE or FALSE logic. The values TRUE and FALSE are conditions that can be applied to certain types of statements, called comparison operations. The statements in the where clause of an update statement that contain the equality or other operation qualify as comparison operations.

3 + 5 = 8
Menorahs hold ten candles
4 = 10
Today is Tuesday

Note that the comparison operations above can all be evaluated for their validity, or whether they are TRUE or FALSE. In the first case, the statement is TRUE because 3 plus 5 equals 8. In the second, a menorah (used to commemorate the Jewish Feast of Lights around the same time as Christmas) usually contains eight or nine candles, but never ten. Therefore, the statement is FALSE. In the third example, 4 definitely does not equal 10, so the statement is FALSE. The final example illustrates an interesting principle about comparison operations; sometimes today is Tuesday, but sometimes it is not. The validity of the statement, then, depends on the day on which the comparison is made.

Conditional statement processing mechanisms allow the developer to structure code such that certain statements may or may not execute based on the validity of a comparison operation. The general syntax for conditional statements is "if the comparison is TRUE, then do the following." PL/SQL also offers an optional add-on, called else, which says essentially, "otherwise, do whatever the else clause says."

DECLARE
My_hypotenuse NUMBER := 0;
BEGIN
IF TO_DATE(sysdate, ‘DAY’) = ‘TUESDAY’ THEN
Execute find_hypotenuse(56,45,my_hypotenuse);
ELSE
My_hypotenuse := derive_hypotenuse(56,45);
END IF;
END;

Note that single-row operations are allowed in comparison statements, so long as they resolve to a datatype that can be compared properly. If, for example, one side of the comparison operation resolves to a number and the other side is a text string, then that will be a problem. Additionally, note that the else statement can contain another if statement, allowing for nested if statements that amount to a case operation.

DECLARE
My_hypotenuse NUMBER;
BEGIN
IF TO_DATE(sysdate, ‘DAY’) = ‘TUESDAY’ THEN
Execute find_hypotenuse(56,45,my_hypotenuse);
ELSIF TO_DATE(sysdate, ‘DAY’) = ‘THURSDAY’ THEN
My_hypotenuse := derive_hypotenuse(56,45);
ELSE
My_hypotenuse := 0;
END IF;
END;

Once again, if the first condition is TRUE, the first block of PL/SQL will execute. If the second condition is TRUE, then the second block of PL/SQL code will execute. If neither of the preceding code blocks is TRUE, then the third PL/SQL block will execute. To end an if statement, there must be the end if keywords. Otherwise, the code after the conditional expression will be treated as part of the else clause, which will cause the PL/SQL compiler to error out. Any comparison operation that can be used in a where clause is a SQL statement can be used as the comparison operation in an if-then statement.

Exercises

  1. What statement allows the developer to handle conditional statement processing?
  2. What is a comparison operation? What is Boolean logic?

Using Loops

Another situation that arises in programming is the need to execute a set of statements repeatedly. The repetitions can be controlled in two ways: the first is to repeat the code for a specified number of times, and the second is to repeat the code until some condition is met, thus rendering a comparison operation to TRUE. The types of loops that are available in PL/SQL are listed as follows.

Loop-exit statements
While-loop statements
For-loop statements

LOOP-EXIT Statements

The loop-exit statement is the simplest type of loop that can be written in PL/SQL. The loop keyword denotes the beginning of the code block that will be repeated, and the end loop keywords denote the end of the code block that will be repeated. The exit keyword specified by itself denotes that process should break out of the loop, while the exit when keywords denote a comparison operation that will test whether the statement is finished executing.

DECLARE
My_leg NUMBER := 0;
My_hypotenuse NUMBER := 0;
BEGIN
LOOP
My_leg := my_leg + 1;
Execute find_hypotenuse(my_leg,my_leg,my_hypotenuse);
IF my_leg = 25 THEN
EXIT;
END IF;
END LOOP;
END;

The if-then statement is designed to determine if the conditions within the loop are such that the loop should terminate. The exit statement instructs the PL/SQL execution mechanism to leave the loop. An alternative to setting up an if-then statement to determine if the loop should end is to add a when condition to the exit statement. The when condition contains the comparison operation that the if-then statement would have handled. An example of a simple loop statement that uses an exit when statement is listed in the following code block. Note that the code is essentially a revision of the simple loop block.

DECLARE
My_leg NUMBER := 0;
My_hypotenuse NUMBER := 0;
BEGIN
LOOP
My_leg := my_leg + 1;
Execute find_hypotenuse(my_leg,my_leg,my_hypotenuse);
EXIT WHEN my_leg = 25;
END LOOP;
END;

The when clause is very useful for the developer because it offers an elegant solution to defining when the loop will end, as opposed to hiding an exit statement inside an if-then statement. However, there are other possibilities for developing loops to handle repetition in coding.

WHILE-LOOP Statements

The next type of loop that approximates the usage of a loop-exit when statement is the while loop statement. The code in the previous block can be rewritten to include the while loop. The only difference between the while loop statement and the loop-exit when statement is where PL/SQL evaluates the exit condition. In a while loop statement, the exiting condition is evaluated at the beginning of the statement, while in the loop-exit when statement, the exit condition is evaluated wherever the exit when statement is placed. In one sense, the loop-exit when statement offers more flexibility than the while loop statement does because loop-exit when allows the developer to specify the exit condition wherever he wants. However, the flexibility that the while-loop statement may lack is made up for by its comparative elegance, in that there is no need for an exit statement.

DECLARE
My_leg NUMBER := 0;
My_hypotenuse NUMBER := 0;
BEGIN
WHILE my_leg < 25 LOOP
My_leg := my_leg + 1;
Execute find_hypotenuse(my_leg,my_leg,my_hypotenuse);
END LOOP;
END;

FOR-LOOP Statements

The final example of looping constructs to be presented is the for-loop statement. This type of loop allows the developer to specify exactly the number of times the code will execute before PL/SQL will break out of it. To accomplish this process, the for-loop statement specifies a loop counter and a range through which the counter will circulate. Optionally, the developer can circulate through the loop counter in reverse order, or in numeric descending order. The loop counter is then available for use by the statements in the for-loop statement.

DECLARE
My_leg NUMBER := 0;
My_hypotenuse NUMBER := 0;
BEGIN
FOR my_leg IN 1..25 LOOP
Execute find_hypotenuse(my_leg,my_leg,my_hypotenuse);
END LOOP;
END;

Notice that the use of a for-loop statement made this code block even more elegant. No longer necessary is the statement that increments the MY_LEG variable, since the for-loop statement handles the incremental activity automatically. The developer is left in this case with a block of code in the loop that handles no maintenance tasks. There is another type of for-loop statement related to cursor handling that offers the same elegance and utility as the for-loop statement detailed in the previous code block. Its usage, as well as the more general usage of cursors will be covered in the next section of this chapter.

Exercises

  1. How is nested conditional statement processing handled?
  2. What are three different types of loops? What is an exit when statement? What is a loop counter, and for which type of loop is it most commonly used? Which type of loop doesn’t require an explicit exit statement?

Explicit Cursor Handling

In this section, you will cover the following topics related to using cursors in PL/SQL:

Implicit vs. explicit cursors
Declaring and using explicit cursors
Parameters and explicit cursors
Writing cursor for loops

The definition of a cursor has already been presented. To recap, a cursor is an address in memory where a SQL statement is processed. Cursors are frequently used in PL/SQL to handle loop processing for a set of values returned by a select statement, and they have other uses as well. This discussion will present the uses for cursors, along with the different types of cursors available in Oracle. Creation of all types of cursors will be presented, along with a more detailed discussion of creating the special for loop for cursor data handling that was mentioned at the end of the previous section.

Implicit vs. Explicit Cursors

Every time a user executes SQL statements of any sort, there is activity on the database that involves cursors. There are two types of cursors in PL/SQL—implicit and explicit cursors. The implicit cursor is an unnamed address where the SQL statement is processed by Oracle and/or the PL/SQL execution mechanism. Every SQL statement executes in an implicit cursor, including update, insert, and delete statements, and select statements that do not execute in explicit cursors.

TIP: Every SQL statement executed on the Oracle database is an implicit cursor, and any implicit cursor attribute can be used in conjunction with them.

An explicit cursor is one that is named by the developer. The cursor is little more than a select statement that has a name. Any sort of select statement can be used in an explicit cursor using the cursor cursor_name is syntax. When a select statement is placed in an explicit cursor, the developer has more complete control over the statement’s execution.

DECLARE
CURSOR employee_cursor IS
SELECT * FROM employee;
END;
BEGIN …

There is really no such thing as determining "the best time" to use an implicit cursor, but the developer can determine the best time to use an explicit one. Every time a SQL operation is requested, an implicit cursor is used. When the developer wants to perform some manipulation on each record returned by a select operation, she will use an explicit cursor. Most serious processing of data records is done with explicit cursors; however, there are some operations that work with implicit cursors as well. For example, many of the cursor attributes identified in an earlier section of this chapter can be applied to implicit cursors with useful results. To refresh the discussion, the list of cursor attributes available are %notfound, %found, %rowcount, and %isopen. %notfound identifies whether the fetch executed on the cursor did not return a row. The return value is the opposite of that which is returned by %found, which identifies whether the fetch executed on the cursor returned a row. These two attributes return a TRUE or FALSE value. %rowcount identifies the number of rows that were processed by this cursor and returns a numeric value. %isopen identifies whether the cursor referred to is opened and ready for use, and returns a TRUE or FALSE value.

Using an implicit cursor in conjunction with cursor attributes may consist of executing some statement and then finding out if the results were successful. In the following example, a user attempts to update an employee salary record. If there are no employees in the EMPLOYEE table that correspond with the EMPID he would like to modify, then he wants the process to add an employee record.

DECLARE
My_empid employee.empid%TYPE;
My_salary employee.salary%TYPE;
My_lastname employee.lastname%TYPE;
BEGIN
My_salary := 99000;
My_empid := 59694;
My_lastname := ‘RIDDINGS’;

UPDATE employee
SET salary = my_salary
WHERE empid = my_empid;

IF SQL%NOTFOUND THEN
INSERT INTO EMPLOYEE (empid, lastname, salary)
VALUES(my_empid, my_lastname, my_salary);
END IF;
END;

There are two implicit cursors in this example. The first is the update statement, and the second is the insert statement. If the update statement produces a change on no rows, the if sql%notfound then statement will trap the error and force some operation to happen as a result of the condition. Note that in the situation of an implicit cursor, there is no name to use to precede the cursor attribute. In this situation, the developer should specify sql%notfound, or sql%found, or use "SQL" followed by the cursor attribute. That "SQL" represents the most recently-executed SQL statement producing an implicit cursor.

Exercises

  1. What is an implicit cursor and what is the syntax for creating one?
  2. What is an explicit cursor? Why might a developer use an explicit cursor rather than an implicit one?
  3. What is the syntax for creating an explicit cursor?

Declaring and Using Explicit Cursors

Most of the time, developers spend their efforts working with explicitly defined cursors. These programming devices allow the developer to control processing outcome based on manipulation of individual records returned by a select statement. As stated, a cursor is defined with the syntax cursor cursor_name is, which is then followed by a select statement. Once defined, the cursor allows the developer to step through the results of the query in a number of different ways.

DECLARE
/* extract from a salary review program */
High_pctinc constant number := 1.20;
Med_pctinc constant number := 1.10;
Low_pctinc constant number := 1.05;
My_salary number;
My_empid number;
CURSOR employee_crsr IS
SELECT empid, salary
FROM employee;
BEGIN …

Consider the definition of the EMPLOYEE_CRSR. The two keywords used are cursor and is. Note that the syntactic requirements of the select statement are fairly standard. The declaration of a cursor does not actually produce the cursor, however. At this point, the cursor definition simply stands ready for action. The cursor will not actually exist in memory until it is opened and parsed by the SQL execution mechanism in Oracle. Data will not populate the cursor until the cursor is executed. At the point the SQL statement is executed, the code can begin fetching row values from it if the cursor contains a select statement.

Attention should turn now to the process of invoking the cursor in memory. In this example, the employees of the company will be selected into the cursor for the purpose of salary review. Once selected, the review will be conducted as follows. Every employee of the company will obtain a midlevel raise as defined by the percentage increase listed for mid_pctinc. There are four exceptions: two employees will get a large raise as defined by the percentage increase listed for high_pctinc, while two other employees will get low performance increases as defined by low_pctinc. The process flow will be governed by a conditional statement, along with a loop.

DECLARE
/* extract from a salary review program */
High_pctinc constant number := 1.20;
Med_pctinc constant number := 1.10;
Low_pctinc constant number := 1.05;
My_salary number;
My_empid number;
CURSOR employee_crsr IS
SELECT empid, salary
FROM employee;
BEGIN
/* The following statement creates and executes the cursor in memory */
OPEN employee_crsr;

LOOP /* sets a loop that allows program to step through records of cursor */
FETCH employee_crsr INTO my_empid, my_salary;
EXIT WHEN employee_crsr%NOTFOUND; /* stop looping when no records found */
IF my_empid = 59697 OR my_empid = 76095 THEN
UPDATE employee SET salary = my_salary*high_pctinc
WHERE empid = my_empid;
ELSIF my_empid = 39294 OR my_Empid = 94329 THEN
UPDATE employee SET salary = my_salary*low_pctinc
WHERE empid = my_empid;
ELSE
UPDATE employee SET salary = my_salary*mid_pctinc
WHERE empid = my_empid;
END IF;
END LOOP;
END;

The main cursor manipulation operations are the open, loop-exit when, fetch, and cursor%notfound attribute statements. The cursor is first opened with the open command, which implicitly parses and executes the statement as well. The loop is defined such that it should run until all records from the cursor are processed. The exit condition uses the %notfound attribute, preceded by the name of the explicit cursor. Pay particular attention to the fetch statement. This operation can only be performed on explicit cursors that are select statements. When a call to fetch is made, PL/SQL will obtain the next record from the cursor and populate the variables specified with values obtained from the cursor. If the fetch produces no results, then the %notfound attribute is set to TRUE. The cursor fetch statement can handle variables of two sorts. The fetch command in the preceding code block illustrates use of stand-alone variables for each column value stored in the cursor. The fetch statement depends on positional specification to populate the variables if this option is used. Alternately, the use of a record that contains the same attributes as those columns defined by the cursor is also handled by fetch. Positional specification is used here as well, so it is required for the order of the variables in the declared record to match the order of columns specified in the cursor declaration.

DECLARE
/* extract from a salary review program */
High_pctinc constant number := 1.20;
Med_pctinc constant number := 1.10;
Low_pctinc constant number := 1.05;
TYPE t_emp IS RECORD (
T_salary number,
t_empid number);
my_emprec t_emp;
CURSOR employee_crsr IS
SELECT empid, salary
FROM employee;
BEGIN
/* The following statement creates and executes the cursor in memory */
OPEN employee_crsr;
LOOP /* sets a loop that allows program to step through records of cursor */
FETCH employee_crsr INTO my_emprec;
EXIT WHEN employee_crsr%NOTFOUND; /* stop looping when no records found */
IF my_emprec.t_empid = 59697 OR my_emprec.t_empid = 76095 THEN
UPDATE employee SET salary = my_emprec.t_salary*high_pctinc
WHERE empid = my_emprec.t_empid;
ELSIF my_emprec.t_empid = 39294 OR my_emprec.t_empid = 94329 THEN
UPDATE employee SET salary = my_emprec.t_salary*low_pctinc
WHERE empid = my_emprec.t_empid;
ELSE
UPDATE employee SET salary = my_emprec.t_salary*mid_pctinc
WHERE empid = my_emprec.t_empid;
END IF;
END LOOP;
END;

The additional code required to support records in this case may well be worth it if there are many variables in the PL/SQL block. Records give the developer a more object-oriented method for handling the variables required for cursor manipulation.

Exercises

  1. What must be done in order to make a cursor exist in memory?
  2. What step must be accomplished to put data in a cursor?
  3. How is data retrieved from a cursor?

Parameters and Explicit Cursors

At times, there may be opportunities for the reuse of a cursor definition. However, the cursors demonstrated thus far either select every record in the database or, alternately, may be designed to select from a table according to hard-coded "magic" values. There is a way to configure cursors such that the values from which data will be selected can be specified at the time the cursor is opened. The method used to create this cursor setup is the use of parameters. For example, assume the developer wanted to set up so that the cursor would select a subset of values from the database to run the salary review program on, based on the first letter of the last name. This process could be accomplished with the use of cursor parameters. The developer could allow the cursor to accept a low and high limit, and then select data from the table for the cursor using that range.

DECLARE
/* extract from a salary review program */
High_pctinc constant number := 1.20;
Med_pctinc constant number := 1.10;
Low_pctinc constant number := 1.05;
TYPE t_emp IS RECORD (
T_salary number,
t_empid number);
my_emprec t_emp;
CURSOR employee_crsr(low_end VARCHAR2, high_end VARCHAR2) IS
SELECT empid, salary
FROM employee
WHERE substr(lastname,1,1) BETWEEN UPPER(low_end) AND UPPER(high_end);
BEGIN …

With the parameter passing defined, the developer can set up the cursor with more control over the data that is ultimately processed. For example, if the developer wants only to process salary increases for employees whose last names start with A through M, she can develop the following code block:

DECLARE
/* extract from a salary review program */
High_pctinc constant number := 1.20;
Med_pctinc constant number := 1.10;
Low_pctinc constant number := 1.05;
TYPE t_emp IS RECORD (
T_salary number,
t_empid number);
my_emprec t_emp;
CURSOR employee_crsr(low_end VARCHAR2, high_end VARCHAR2) IS
SELECT empid, salary
FROM employee
WHERE substr(lastname,1,1) BETWEEN UPPER(low_end) AND UPPER(high_end);
BEGIN
/* The following statement creates and executes the cursor in memory */
OPEN employee_crsr(‘A’,‘M’);
LOOP /* sets a loop that allows program to step through records of cursor */
FETCH employee_crsr INTO my_emprec;
EXIT WHEN employee_crsr%NOTFOUND; /* stop looping when no records found */
IF my_emprec.t_empid = 59697 OR my_emprec.t_empid = 76095 THEN
UPDATE employee SET salary = my_emprec.t_salary*high_pctinc
WHERE empid = my_emprec.t_empid;
ELSIF my_emprec.t_empid = 39294 OR my_emprec.t_empid = 94329 THEN
UPDATE employee SET salary = my_emprec.t_salary*low_pctinc
WHERE empid = my_emprec.t_empid;
ELSE
UPDATE employee SET salary = my_emprec.t_salary*mid_pctinc
WHERE empid = my_emprec.t_empid;
END IF;
END LOOP;
END;

Notice that this code block—the open statement that opens, parses, and executes the cursor—now contains two values passed into the cursor creation as parameters. This parameter passing is required for the cursor to resolve into a set of data rows.

Exercises

  1. What value does passing parameters to a cursor provide?
  2. How can a cursor be defined to accept parameters?

Writing CURSOR FOR Loops

As given evidence in the previous examples, quite a bit of usage surrounding cursors involves selecting data and performing operations on each row returned by the cursor. The code examples presented thus far illustrate how to perform this activity. However, each one of the examples illustrates also that there is some overhead for handling the looping process correctly. Depending on the type of loop used, the overhead required can be substantial. Take, for example, the use of a simple loop-exit statement. Not only must the code that will execute repeatedly be enclosed in the loop syntax construct, but the test for the exit condition must be defined explicitly. Other looping statement examples do simplify the process somewhat.

There is one other loop that will be presented that is ideal for the situation where a developer wants to pull together a set of rows and perform a specified set of operations on them. This loop statement is called the cursor for loop. The cursor for loops handle several activities implicitly related to loop creation. The items handled implicitly by a cursor for loop are the opening, parsing, executing, and fetching of row data from the cursor, and the check to determine if there is more data (and thus if the loop should exit). Moreover, the declaration of a record variable to handle the data fetched from the cursor by the cursor for loop is also handled implicitly. The sample PL/SQL block is reprinted with the addition of a cursor for loop statement to handle all cursor processing:

DECLARE
/* extract from a salary review program */
High_pctinc constant number := 1.20;
Med_pctinc constant number := 1.10;
Low_pctinc constant number := 1.05;
CURSOR employee_crsr(low_end VARCHAR2, high_end VARCHAR2) IS
SELECT empid, salary
FROM employee
WHERE substr(lastname,1,1) BETWEEN UPPER(low_end) AND UPPER(high_end);
BEGIN
/* The following statement creates and executes the cursor in memory */
/* sets a loop that allows program to step through records of cursor */
FOR my_emprec in employee_crsr(‘A’,‘M’) LOOP
IF my_emprec.empid = 59697 OR my_emprec.empid = 76095 THEN
UPDATE employee SET salary = my_emprec.salary*high_pctinc
WHERE empid = my_emprec.empid;
ELSIF my_emprec.empid = 39294 OF my_emprec.empid = 94329 THEN
UPDATE employee SET salary = my_emprec.salary*low_pctinc
WHERE empid = my_emprec.empid;
ELSE
UPDATE employee SET salary = my_emprec.t_salary*mid_pctinc;
WHERE empid = my_emprec.t_empid;
END IF;
END LOOP;
END;

Take an extra moment to review the code block detailing a cursor for loop and confirm the following features the loop handles implicitly. Note that the benefit of using a cursor for loop is that there are fewer requirements to set up the loop, resulting in fewer lines of code, fewer mistakes, and easier-to-read programs. The features that cursor for loops handle implicitly are listed in the following:

The cursor for loop handles opening, parsing, and executing the cursor automatically.
The cursor for loop fetches row data implicitly for each iteration of the loop.
The cursor for loop handles the cursor_name%notfound condition implicitly and appropriately terminates the loop when the attribute is TRUE
The cursor for loop handles the definition of a record to store the row values returned by the cursor fetch automatically, resulting in a smaller declaration section.

Exercises

  1. What steps in cursor loop handling does a cursor for loop handle implicitly?
  2. How is the exit condition defined for a cursor for loop?

Error Handling

In this section, you will cover the following areas related to error handling:

The three basic types of exceptions
Identifying common exceptions
Coding the exception handler

The handling of errors in PL/SQL is arguably the best contribution PL/SQL makes to commercial programming. Errors in PL/SQL need not be trapped and handled with if statements directly within the program, as they are in other procedural languages like C. Instead, PL/SQL allows the developer to raise exceptions when an error condition is identified and switch control to a special program area in the PL/SQL block, called the exception handler. The code to handle an error does not clutter the executable program logic in PL/SQL, nor is the programmer required to terminate programs with return or exit statements. The exception handler is a cleaner way to handle errors.

The Three Basic Types of Exceptions

The three types of exceptions in Oracle PL/SQL are predefined exceptions, user-defined exceptions, and pragma exceptions. Exception handling in PL/SQL offers several advantages. These advantages are simplicity and flexibility. Predefined exceptions offer the developer several built-in problems that can be checked. User-defined and pragma exceptions allow for additional flexibility to build in a level of support for errors defined by the user into PL/SQL. The following discussions will illustrate the use of predefined, user-defined, and pragma exceptions.

Predefined Exceptions

In order to facilitate error handling in PL/SQL, Oracle has designed several "built-in" or predefined exceptions. These exceptions are used to handle common situations that may occur on the database. For example, there is a built-in exception that can be used to detect when a statement returns no data, or when a statement expecting one piece of data receives more than one piece of data. There is no invoking a predefined exception—they are tested and raised automatically by Oracle. However, in order to have something done when the predefined error occurs, there must be something in the exception handler both to identify the error and to define what happens when the error occurs. In the next discussion, "Common Exceptions," several of the most common exceptions will be presented. All common exceptions in this discussion are predefined.

TIP: In order to trap a predefined exception, there must be an exception handler coded for it in the exceptions section.

User-defined Exceptions

In addition to predefined exceptions, there can be created a whole host of user-defined exceptions that handle situations that may arise in the code. A user-defined exception may not produce an Oracle error; instead, user-defined exceptions may enforce business rules in situations where an Oracle error would not necessarily occur. Unlike predefined exceptions, which are implicitly raised when the associated error condition arises, a user-defined exception must have explicit code in the PL/SQL block designed to raise it. There is code required for all three sections of a PL/SQL block if the developer plans on using user-defined exceptions. The required code is detailed in the bullets that follow:

Exception declaration In the declaration section of the PL/SQL block, the exception name must be declared. This name will be used to invoke, or raise, the exception in the execution section if the conditions of the exception occur.
Exception testing In the execution section of the PL/SQL block, there must be code that explicitly tests for the user-defined error condition, which raises the exception if the conditions are met.
Exception handling In the exception handler section of the PL/SQL block, there must be a specified when clause that names the exception and the code that should be executed if that exception is raised. Alternately, there should be a when others exception handler that acts as a catchall.

The following code block provides an example for coding a user-defined exception. In the example, assume that there is some problem with an employee’s salary record being NULL. The following code will select a record from the database. If the record selected has a NULL salary, the user-defined exception will identify the problem with an output message.

DECLARE
My_empid number;
My_emp_record employee%ROWTYPE;
My_salary_null EXCEPTION;
BEGIN
My_empid := 59694;
SELECT * FROM employee
INTO my_emp_record
WHERE empid = my_empid;

IF my_emp_record.salary IS NULL THEN
RAISE my_salary_null;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No Data Found’);
WHEN my_salary_null THEN
DBMS_OUTPUT.PUT_LINE(‘Salary column was null for employee’);
END;

Note that code must appear for user-defined exceptions in all three areas of the PL/SQL block. Without one of these components, the exception will not operate properly and the code will produce errors.

The EXCEPTION_INIT Pragma

Sometimes a user-defined exception will occur in conjunction with an Oracle error. In the case where the PL/SQL block experiences errors and the developer would like to code some exception in conjunction with the error, the developer can use the exception_init pragma. The pragma statement allows the developer to declare the Oracle-numbered error to be associated with a named exception in the block. This usage allows the code to handle errors that it might not have handled previously. For example, assume that the developer is inserting data on the EMPLOYEE table, and this table defined a NOT NULL constraint on SALARY. Instead of allowing the PL/SQL block to terminate abnormally if an insert occurs that does not name a value for the SALARY column with an ora-01400 error, the declaration of an exception allows the PL/SQL block to handle the error programmatically.

DECLARE
My_emp_record employee%ROWTYPE;
PRAGMA EXCEPTION_INIT(my_salary_null, -1400);
BEGIN
My_emp_record.empid := 59485;
My_emp_record.lastname := ‘RICHARD’;
My_emp_record.firstname := ‘JEAN-MARIE’;

INSERT INTO employee(empid,lastname,firstname,salary)
VALUES(my_emp_record.empid, my_emp_record.lastname, my_emp_record.firstname,
My_emp_record.salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No Data Found’);
WHEN my_salary_null THEN
DBMS_OUTPUT.PUT_LINE(‘Salary column was null for employee’);
END;

An advantage to using the exception_init pragma when the user-defined error produces some Oracle error is that there is no need for an explicit condition test that raises the exception if the condition is met. Exceptions defined with the exception_init pragma enjoy the same implicit exception handling as predefined exceptions do.

Exercises

  1. What is a predefined error? How are they invoked?
  2. What is a user-defined error? Where must code be defined in order to create a user-defined exception?
  3. What can be used to associate an Oracle error with a user-defined error?

Identifying Common Exceptions

There are many common exceptions that Oracle PL/SQL allows developers to define and handle in their programs. Some of the predefined cursors are listed here:

invalid_cursor Occurs when an attempt is made to close a nonopen cursor.
cursor_already_open Occurs when an attempt is made to open a nonclosed cursor.
dup_val_on_index Unique or primary key constraint violation.
no_data_found No rows were selected or changed by the SQL operation.
too_many_rows More than one row was obtained by a single-row subquery, or in another SQL statement operation where Oracle was expecting one row.
zero_divide An attempt was made to divide by zero.
rowtype_mismatch The datatypes of the record to which data from the cursor is assigned are incompatible.
invalid_number An alphanumeric string was referenced as a number.

Of these operations, the developer may expect to use the no_data_found or too_many_rows exceptions most frequently. In fact, the user can incorporate checks for these areas using cursor attributes. As mentioned, in order to use an exception, the developer must raise it. Raising an exception requires usage of the raise statement. However, one of the best features about the predefined exceptions is that there is no need to raise them. They must simply be included in the exception handler for the PL/SQL block, and if a situation arises where the error occurs, then the predefined exception is raised automatically. The following code block illustrates the use of an exception handler, along with a predefined exception:

DECLARE
My_empid number;
BEGIN
My_empid := 59694;
SELECT * FROM employee
WHERE empid = my_empid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No Data Found’);
END;

Notice that there is no code that explicitly tells PL/SQL to write the output message if no data is found in the particular select statement in the executable portion of the block. Instead, the exception is implicitly raised when a predefined exception condition occurs. This layer of abstraction is useful because the additional if statement required for checking this condition manually is unnecessary.

Exercises

  1. What predefined exception is used to identify the situation where no data is returned by a select statement?
  2. What predefined exception is used to identify when the datatype of the information returned is not the same datatype as the declared variable?

Coding the Exception Handler

Special attention should be paid now to the actual code of the exception handler. So far, the exceptions handled in previous code blocks have had simple routines that display an error message. There are more advanced options than those presented, of course. This discussion will focus on a few of the options provided.

A named or userdefined exception in the declaration and executable section of the PL/SQL block should have an associated exception handler written for it. The best way to handle an exception is to name it specifically using the when clause in the exceptions block of the PL/SQL program. Following the when clause, there can be one or several statements that define the events that will happen if this exception is raised. If there is no code explicitly defined for the exception raised, then PL/SQL will execute whatever code is defined for a special catchall exception called others. If there is no explicit code defined for a particular exception and no code defined for the others exception, then control passes to the exception handler of the procedure that called the PL/SQL code block. The exception handler is perhaps the greatest achievement gained by using PL/SQL to write stored procedures in Oracle. Its flexibility and ease of use make it simple to code robust programs.

EXCEPTION
WHEN NO_ROWS_FOUND THEN …
/* does some work when the NO_ROWS_FOUND predefined exception is
raise implicitly. */
WHEN OTHERS THEN …
/* this code will execute when any other exception is raised,
explicitly or implicitly. */
END;

TIP: once an exception is raised, PL/SQL l flow control passes to the exception handler. Once the exception is handled, the PL/SQL block will be exited. In other words, once the exception is raised, the execution portion of the PL/SQL block is over.

Exercises

  1. What are the components of an exception handler?
  2. What is the others exception, and how is it used?

Chapter Summary

PL/SQL programming is the topic of this chapter. The subject areas of PL/SQL that were discussed include overview of PL/SQL, modular coding practices, developing PL/SQL blocks, interacting with Oracle, controlling process flow with conditional statements and loops, cursors, and error handling. The PL/SQL areas of OCP Exam 1 comprise about 22 percent of the overall test. PL/SQL is the best method available for writing and managing stored procedures that work with Oracle data. PL/SQL code consists of three subblocks—the declaration section, the executable section, and the exception handler. In addition, PL/SQL can be used in four different programming constructs. The types are procedures and functions, packages, and triggers. Procedures and functions are similar in that they both contain a series of instructions that PL/SQL will execute. However, the main difference is that a function will always return one and only one value. Procedures can return more than that number as output parameters. Packages are collected libraries of PL/SQL procedures and functions that have an interface to tell others what procedures and functions are available as well as their parameters, and the body contains the actual code executed by those procedures and functions. Triggers are special PL/SQL blocks that execute when a triggering event occurs. Events that fire triggers include any SQL statement.

The declaration section allows for the declaration of variables and constants. A variable can have either a simple or "scalar" datatype, such as NUMBER or VARCHAR2. Alternately, a variable can have a referential datatype that uses reference to a table column to derive its datatype. Constants can be declared in the declaration section in the same way as variables, but with the addition of a constant keyword and with a value assigned. If a value is not assigned to a constant in the declaration section, an error will occur. In the executable section, a variable can have a value assigned to it at any point using the assignment expression (:=).

Using PL/SQL allows the developer to produce code that integrates seamlessly with access to the Oracle database. Examples appeared in the chapter of using all SQL statements, including data selection, data change, and transaction processing statements. There are no special characters or keywords required for "embedding" SQL statements into PL/SQL, because SQL is an extension of PL/SQL. As such, there really is no embedding at all. Every SQL statement executes in a cursor. When a cursor is not named, it is called an implicit cursor. PL/SQL allows the developer to investigate certain return status features in conjunction with the implicit cursors that run. These implicit cursor attributes include %notfound and %found to identify if records were found or not found by the SQL statement; %notfound, which tells the developer how many rows were processed by the statement; and %isopen, which determines if the cursor is open and active in the database.

Conditional process control is made possible in PL/SQL with the use of if-then-else statements. The if statement uses a Boolean logic comparison to evaluate whether to execute the series of statements after the then clause. If the comparison evaluates to TRUE, the then clause is executed. If it evaluates to FALSE, then the code in the else statement is executed. Nested if statements can be placed in the else clause of an if statement, allowing for the development of code blocks that handle a number of different cases or situations.

Process flow can be controlled in PL/SQL with the use of loops as well. There are several different types of loops, from simple loop-exit statements to loop-exit when statements, while loop statements, and for loop statements. A simple loop-exit statement consists of the loop and end loop keywords enclosing the statements that will be executed repeatedly, with a special if-then statement designed to identify if an exit condition has been reached. The if-then statement can be eliminated by using an exit when statement to identify the exit condition. The entire process of identifying the exit condition as part of the steps executed in the loop can be eliminated with the use of a while loop statement. The exit condition is identified in the while clause of the statement. Finally, the for loop statement can be used in cases where the developer wants the code executing repeatedly for a specified number of times.

Cursor manipulation is useful for situations where a certain operation must be performed on each row returned from a query. A cursor is simply an address in memory where a SQL statement executes. A cursor can be explicitly named with the use of the cursor cursor_name is statement, followed by the SQL statement that will comprise the cursor. The cursor cursor_name is statement is used to define the cursor in the declaration section only. Once declared, the cursor must be opened, parsed, and executed before its rows can be manipulated. This process is executed with the open statement. Once the cursor is declared and opened, rows from the resultant dataset can be obtained if the SQL statement defining the cursor was a select using the fetch statement. Both loose variables for each column’s value or a PL/SQL record may be used to store fetched values from a cursor for manipulation in the statement.

Executing each of the operations associated with cursor manipulation can be simplified in situations where the user will be looping through the cursor results using the cursor for loop statement. The cursor for loops handle many aspects of cursor manipulation explicitly. These steps include including opening, parsing, and executing the cursor statement, fetching the value from the statement, handling the exit when data not found condition, and even implicitly declaring the appropriate record type for a variable identified by the loop in which to store the fetched values from the query.

The exception handler is arguably the finest feature PL/SQL offers. In it, the developer can handle certain types of predefined exceptions without explicitly coding error-handling routines. The developer can also associate user-defined exceptions with standard Oracle errors, thereby eliminating the coding of an error check in the executable section. This step requires defining the exception using the exception_init pragma and coding a routine that handles the error when it occurs in the exception handler. For completely user-defined errors that do not raise Oracle errors, the user can declare an exception and code a programmatic check in the execution section of the PL/SQL block, followed by some routine to execute when the error occurs in the exception handler. A special predefined exception called others can be coded into the exception handler as well to function as a catchall for any exception that occurs that has no exception-handling process defined. Once an exception is raised, control passes from the execution section of the block to the exception handler. Once the exception handler has completed, control is passed to the process that called the PL/SQL block.

Two-Minute Drill

PL/SQL is a programming environment that is native to the Oracle database. It features seamless integration with other database objects in Oracle and with SQL.
There are three parts to a PL/SQL program: the declaration area, the execution area, and the exception handler.
There are four types of PL/SQL blocks: procedures, functions, packages, and triggers.
Procedures allow the developer to specify more than one output parameter, while functions only allow one return value. Other than that, the two PL/SQL blocks are similar in function and usage.
Variables are defined in the declaration section.
Variables can have a scalar datatype like NUMBER or VARCHAR2, or a referential datatype defined by use of a table and/or column reference followed by %type or %rowtype.
Constants are declared the same way as variables, except for the fact that the constant keyword is used to denote a constant and the constant must have a value assigned in the declaration section.
Variables can have values assigned anywhere in the PL/SQL block using the assignment operator, which looks like (:=).
Any SQL statement is valid for use in PL/SQL. This includes all SQL statements such as select and delete, and transaction control statements such as set transaction or commit.
Conditional processing is handled in PL/SQL with if-then-else statements.
If-then-else statements rely on Boolean logic to determine which set of statements will execute. If the condition is TRUE, the statements in the then clause will execute. If the condition is FALSE, the statements in the else clause will execute.
The if statements can be nested into one another’s else clause.
Several loops also control the repetition of blocks of PL/SQL statements.
The loop-exit statement is a simple definition for a loop that marks the beginning and end of the loop code. An if-then statement tests to see if conditions are such that the loop should exit. An exit statement must be specified explicitly.
The if-then statement can be replaced with an exit when statement, which defines the exit condition for the loop.
The while statement eliminates the need for an exit statement by defining the exit condition in the while loop statement.
If the programmer wants her code to execute a specified number of times, the for loop can be used.
Every SQL statement executes in an implicit cursor. An explicit cursor is a named cursor corresponding to a defined SQL statement.
An explicit cursor can be defined with the cursor cursor_name is statement. Cursors can be defined to accept input parameters that will be used in the where clause to limit the data manipulated by the cursor.
Once declared, a cursor must be opened, parsed, and executed in order to have its data used. This task is accomplished with the open statement.
In order to obtain data from a cursor, the programmer must fetch the data into a variable. This task is accomplished with the fetch statement.
The variable used in the fetch can either consist of several loose variables for storing single-column values or a record datatype that stores all column values in a record.
A special loop exists to simplify use of cursors, the cursor for loop.
The cursor for loop handles the steps normally done in the open statement, and implicitly fetches data from the cursor until the data not found condition occurs. This statement also handles the declaration of the variable and associated record type, if any is required.
The exception handler in PL/SQL handles all error handling.
There are user-defined exceptions, predefined exceptions, and pragma exceptions in PL/SQL.
Only user-defined exceptions require explicit checks in the execution portion of PL/SQL code to test to see if the exception condition has occurred.
A named exception can have a when clause defined in the exception handler that executes whenever that exception occurs.
The others exception is a catchall exception designed to operate if an exception occurs that is not associated with any other defined exception handler.
Hosted by uCoz