ShahCh10v3.

qxd 4/16/04 11:59 AM Page 225

PART 3
PL/SQL

10

PL/SQL:
A Programming Language

IN THIS CHAPTER . . .

● You will learn the basics of the PL/SQL programming language.

● The PL/SQL anonymous block is introduced.
● Variables, constants, data types, and declarations are discussed.
● The assignment statement and use of arithmetic operators are covered.
● The scope and use of various types of variables are shown in sample programs.
● You will be prepared to write simple PL/SQL blocks.
In Part 2, you learned Oracle’s nonprocedural language SQL and its various state-
ments to interface with the Oracle database. SQL is a great query language, but it
has its limitations. So, Oracle Corporation has added a procedural language exten-
sion to SQL known as Programming Language Extensions to Structured Query
Language (PL/SQL). It is Oracle’s proprietary language for access of relational
table data. PL/SQL is like any other high-level compiler language. If you are already
familiar with another programming language, you will find PL/SQL constructs to be
similar to those of Pascal, C, or Visual Basic. PL/SQL also possesses features of object-
oriented languages, such as:

● Data encapsulation.
● Error handling.
ShahCh10v3.qxd 4/16/04 11:59 AM Page 226

226 Chap. 10 PL/SQL: A Programming Language

● Information hiding.
● Object-oriented programming (OOP).

PL/SQL also allows embedding of SQL statements and data manipulation in

its blocks. SQL statements are used to retrieve data, and PL/SQL control statements
are used to process data in a PL/SQL program. The data can be inserted, deleted, or
updated through a PL/SQL block, which makes it an efficient transaction-processing
language.
The Oracle Server has an engine to execute SQL statements. The server also
has a separate engine for PL/SQL. Oracle Developer tools have a separate engine
to execute PL/SQL as well. The SQL statements are sent one at a time to the server
for execution, which results in individual calls to the server for each SQL statement.
It may also result in heavy network traffic. On the other hand, all SQL statements
within a single PL/SQL block are sent in a single call to the server, which reduces
overhead and improves performance.

A BRIEF HISTORY OF PL/SQL

Before PL/SQL was developed, users embedded SQL statements into hot languages
like C++ and Java. PL/SQL version 1.0 was introduced with Oracle 6.0 in 1991. Ver-
sion 1.0 had very limited capabilities, however, and was far from being a full-fledged
programming language. It was merely used for batch processing.
With versions 2.0, 2.1, and 2.2, the following new features were introduced:

● The transaction control statements SAVEPOINT, ROLLBACK, and COMMIT.

● The DML statements INSERT, DELETE, and UPDATE.
● The extended data types Boolean, BINARY_INTEGER, PL/SQL records,
and PL/SQL tables.
● Built-in functions—character, numeric, conversion, and date functions.
● Built-in packages.
● The control structures sequence, selection, and looping.
● Database access through work areas called cursors.
● Error handling.
● Modular programming with procedures and functions.
● Stored procedures, functions, and packages.
● Programmer-defined subtypes.
● DDL support through the DBMS_SQL package.
● The PL/SQL wrapper.
● The DBMS_JOB job scheduler.
● File I/O with the UTF_FILE package.
ShahCh10v3.qxd 4/16/04 11:59 AM Page 227

Fundamentals of PL/SQL 227

PL/SQL version 8.0, also known as PL/SQL8, came with Oracle8, the “object-
relational” database software. Oracle allows creation of objects that can be accessed
with Java, C++, Object COBOL, and other languages. It also allows objects and re-
lational tables to coexist. The external procedures in Oracle allow you to compile
procedures and store them in the shared library of the operating system—for exam-
ple, an .so file in UNIX or a .dll (Dynamic Linked Library) file in Windows. Oracle’s
library is written in the C language. It also supports LOB (Large Object) data types.

FUNDAMENTALS OF PL/SQL

A PL/SQL program consists of statements.You may use upper- or lowercase letters in

your program. In other words, PL/SQL is not case sensitive except for character string
values enclosed in single quotes. Like any other programming language, PL/SQL
statements consist of reserved words, identifiers, delimiters, literals, and comments.

Reserved Words

The reserved words, or key words, are words provided by the language that have a
specific use in the language. For example, DECLARE, BEGIN, END, IF, WHILE,
EXCEPTION, PROCEDURE, FUNCTION, PACKAGE, and TRIGGER are some
of the reserved words in PL/SQL.

User-Defined Identifiers

User-defined identifiers are used to name variables, constants, procedures, func-

tions, cursors, tables, records, and exceptions. A user must obey the following rules in
naming these identifiers:

● The name can be from 1 to 30 characters in length.

● The name must start with a letter.
● Letters (A–Z, a–z), numbers, the dollar sign ($), the number sign (#) and the
underscore (_) are allowed.
● Spaces are not allowed.
● Other special characters are not allowed.
● Key words cannot be used as user-defined identifiers.
● Names must be unique within a block.
● A name should not be the same as the name of a column used in the block.

It is a good practice to create short and meaningful names. Figure 10-1 shows a
list of valid user-defined identifiers. Figure 10-2 shows a list of invalid user-defined
identifiers along with reasons why they are invalid.
ShahCh10v3.qxd 4/16/04 11:59 AM Page 228

228 Chap. 10 PL/SQL: A Programming Language

User-Defined Identifiers

Rate_of_pay
Num
A1234567890
Dollars$_and_cents
SS#

Figure 10-1 Valid user-defined identifiers.

Invalid User-Defined Identifiers Reason

2Number Starts with a number

Employee-name Special character hyphen
END Reserved word
Department number Spaces
Largest_yearly_salary_paid_to_employees Too long
Taxrate% Special character %

Figure 10-2 Invalid user-defined identifiers.

Literals

Literals are values that are not represented by user-defined identifiers. Literals are
of three types: numeric, character, and boolean. For example:

Numeric 100, 3.14, 55, 5.25E7, or NULL

Character ‘A’, ‘this is a string’, ‘0001’, ‘25-MAY-00’, ‘ ’, or NULL
Boolean TRUE, FALSE, or NULL

In this list of values, ‘25-MAY-00’ looks like a date value, but it is a character string. It
can be converted to date format by using the TO_DATE function. The value ‘’ (two
single quotes having nothing within) is another way of entering the NULL value.
PL/SQL is case sensitive regarding character values within single quotation
marks. The values ‘ORACLE’, ‘Oracle’, and ‘oracle’ are three different values in
PL/SQL. To embed a single quote in a string value, two single quote symbols are
entered—for example, ‘New Year”s Day’.
Numeric values can be entered in scientific notation with the letter E or e.
Boolean values are not enclosed in quotation marks.

PL/SQL BLOCK STRUCTURE

PL/SQL is a block-structured language. A program can be divided into logical

blocks. The block structure gives modularity to a PL/SQL program, and each object
within a block has “scope.” Blocks are of two types:

1. An anonymous block is a block of code without a name. It can be used any-

where in a program and is sent to the server engine for execution at runtime.
ShahCh10v3.qxd 4/16/04 11:59 AM Page 229

PL/SQL Block Structure 229

2. A named block is a block of code that is named. A subprogram is a named

block that can be called and can take arguments. A procedure is a subpro-
gram that can perform an action, whereas a function is a subprogram that
returns a value. A package is formed from a group of procedures and func-
tions. A trigger is a block that is called implicitly by a DML statement.

A PL/SQL block consists of three sections:

1. A declaration section.
2. An executable section.
3. An exception-handling section.

Figure 10-3 shows the use of three sections in a PL/SQL block. Of the three sections
in a PL/SQL block, only the executable section is mandatory. The declaration and ex-
ception-handling sections are optional. The general syntax of an anonymous block is

[ DECLARE
Declaration of constants, variables, cursors, and exceptions ]
BEGIN
Executable PL/SQL and SQL statements
[EXCEPTION
Actions for error conditions ]
END;

Section Use

Declaration An optional section to declare variables, constants, cursors, PL/SQL

composite data types, and user-defined exceptions, which are referenced
in executable and exception-handling sections.
Executable A mandatory section that contains PL/SQL statements to manipulate
data in the block and SQL statements to manipulate the database.
Exception handling Specifies action statements to perform when an error condition exists in
the executable section. It is also an optional section.

Figure 10-3 Sections in a PL/SQL block.

The DECLARE and EXCEPTION key words are optional, but the BEGIN and END
key words are mandatory. The declarations made within a block are local to the block.
When a block ends, all objects declared within the block cease to exist. A block
is the “scope” of objects declared in that block. When blocks are nested within each
other, the declarations made in the outer block are global to the inner block. The ob-
ject declarations made in the inner block, however, are local to it and cannot be ref-
erenced by the outer block. A basic PL/SQL block can be embedded in any other
PL/SQL block, named or unnamed. Figure 10-4 shows all the blocks available in the
Oracle server environment.
ShahCh10v3.qxd 4/16/04 11:59 AM Page 230

230 Chap. 10 PL/SQL: A Programming Language

Block Use

Anonymous block An unnamed block, that is independent or embedded within an

application.
Procedure/function A named block that is stored on the Oracle server, can be called by its
name, and can take arguments.
Package A named PL/SQL module that is a group of functions, procedures, and
identifiers.
Trigger A block that is associated with a database table or a view. It is executed
when automatically fired by a DML statement.

Figure 10-4 Programming constructs.

COMMENTS

Comments are used to document programs. They are written as part of a program, but
they are not executed. In fact, comments are ignored by the PL/SQL engine. It is a good
programming practice to add comments to a program, because this helps in readability
and debugging of the program. There are two ways to write comments in PL/SQL:

1. To write a single-line comment, two dashes (--) are entered at the begin-
ning of a new line. For example,
- -This is a single-line comment.

2. To write a multiline comment, comment text is placed between /* and */.

A multiline comment can be written on a separate line by itself, or it can
be used on a line of code as well. For example,

/* This is a
multiline comment
that ends here. */

A programmer can use a comment anywhere in the program.

DATA TYPES

Each constant and variable in the program needs a data type. The data type decides
the type of value that can be stored in a variable. PL/SQL has four data types:

1. Scalar.
2. Composite.
3. Reference.
4. LOB.
ShahCh10v3.qxd 4/16/04 11:59 AM Page 231

Data Types 231

A scalar data type is not made up of a group of elements. It is atomic in nature. The
composite data types are made up of elements or components. PL/SQL supports
three composite data types—records, tables, and varrays, which are discussed in a
later chapter. The reference data types deal with objects, which are briefly introduced
in Appendix D.
There are four major categories of scalar data types:

1. Character.
2. Number.
3. Boolean.
4. Date.

Other scalar data types include raw, rowid, and trusted.

Character

Variables with a character data type can store text. The text may include letters,
numbers, and special characters. The text in character-type variables can be manipu-
lated with built-in character functions. Character data types include CHAR and
VARCHAR2.

CHAR. The CHAR data type is used for fixed-length string values. The allow-
able string length is between 1 and 32,767. (If you remember, the allowable length in
the Oracle database is only 2000.) If you do not specify a length for the variable, the
default length is one. Get into the habit of specifying length along with the data type
to avoid any errors.
If you are going to declare a variable of the CHAR type in PL/SQL code and
that value is to be inserted into a table’s column, the limitation on database size is
only 2000 characters. You will have to find the substring of that character value to
avoid the error message for inserting a character string longer than the length of the
column.
If you specify a length of 10 and the assigned value is only five characters long,
the value is padded with trailing spaces because of the fixed-length nature of this
data type. The CHAR type is not storage efficient, but it is performance efficient.

VARCHAR2. The VARCHAR2 type is used for variable-length string val-

ues.Again, the allowable length is between 1 and 32,767.A column in an Oracle data-
base with a VARCHAR2 type, however, can only take 4000 characters, which is
smaller than the allowable length for the variable.
Suppose you have two variables with data type of CHAR(20) and VAR-
CHAR2(20), and both are assigned the same value, ‘Oracle9i PL/SQL’. The string
value is only 15 characters long. The first variable, with CHAR(20), is assigned a value
padded with five spaces; the variable with VARCHAR2(20) does not get a string value
ShahCh10v3.qxd 4/16/04 11:59 AM Page 232

232 Chap. 10 PL/SQL: A Programming Language

padded with spaces. If the values of both variables are compared in a condition for
equality, FALSE will be returned.
Other character data types are LONG (32,760 bytes, shorter than VARCHAR2),
RAW (32,767 bytes), and LONG RAW (32,760 bytes, shorter than RAW). The RAW
data values are neither interpreted nor converted by Oracle.
VARCHAR2 is the most recommended character data type.

Number

PL/SQL has a variety of numeric data types. Whole numbers or integer values can
be handled by following data types:

BINARY_INTEGER (approximately -231 + 1 to 231 - 1, or - 2 billion to +2

billion)
INTEGER
INT
SMALLINT
POSITIVE (a subtype of BINARY_INTEGER—range, 0 to 231)
NATURAL (a subtype of BINARY_INTEGER—range, 1 to 231)

Similarly, there are various data types for decimal numbers:

NUMBER
DEC (fixed-point number)
DECIMAL (fixed-point number)
NUMERIC (fixed-point number)
FLOAT (floating-point number)
REAL (floating-point number)
DOUBLE PRECISION (floating-point number)

You are familiar with the NUMBER type from the Oracle table’s column type.
The NUMBER type can be used for fixed-point or floating-point decimal numbers.
It provides an accuracy of up to 38 decimal places. When using the NUMBER type,
the precision and scale values are provided. The precision of a number is the total
number of significant digits in that number, and the scale is the number of signifi-
cant decimal places. The precision and scale values must be whole-number integers.
For example,

NUMBER(p, s)

where p is precision and s is scale.

If scale has a value that is negative, positive, or zero, it specifies rounding of the
number to the left of the decimal place, to the right of the decimal place, or to the
nearest whole number, respectively. If a scale value is not used, no rounding occurs.
ShahCh10v3.qxd 4/16/04 11:59 AM Page 233

Other Data Types 233

Boolean

PL/SQL has a logical data type, Boolean, that is not available in SQL. It is used for
Boolean data TRUE, FALSE, or NULL only. These values are not enclosed in single
quotation marks like character and date values.

Date

The date type is a special data type that stores date and time information. The date
values have a specific format. A user can enter a date in many different formats with
the TO_DATE function, but a date is always stored in standard 7-byte format. A
date stores the following information:
Century
Year
Month
Day
Hour
Minute
Second
The valid date range is from January 1, 4712 B.C., to December 31, 9999 A.D.
The time is stored as the number of seconds past midnight. If the user leaves out the
time portion of the data, it defaults to midnight (12:00:00 A.M.).
Various DATE functions are available for date calculations. For example, the
SYSDATE function is used to return the system’s current date.

OTHER DATA TYPES

NLS

The National Language Support (NLS) data type is for character sets in which mul-
tiple bytes are used for character representation. NCHAR and NVARCHAR2 are
examples of NLS data types.

LOB

Like Oracle9i, PL/SQL also supports Large Object (LOB) data types to store large
values of character, raw, or binary data. The LOB types allow up to 4 gigabytes of
data. LOB variables can be given one of the following data types:
● The BLOB type contains a pointer to the large binary object inside the
database.
● The CLOB type contains a pointer to a large block of single-byte character
data of fixed width.
ShahCh10v3.qxd 4/16/04 11:59 AM Page 234

234 Chap. 10 PL/SQL: A Programming Language

● The NCLOB type contains a pointer to a large block of multibyte character

data of fixed width.
● The BFILE type contains a pointer to large binary objects in an external op-
erating system file. It would contain the directory name and the filename.

Oracle provides users with a built-in package, DBMS_LOB, to manipulate the

contents of LOBs.

VARIABLE DECLARATION

A scalar variable or a constant is declared with a data type and an initial value assign-
ment. The declarations are done in the DECLARE section of the program block. The
initial value assignment for a variable is optional unless it has a NOT NULL con-
straint. The constants and NOT NULL type variables must be initialized. The general
syntax is

DECLARE
identifiername [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expression];

where identifiername is the name of a variable or constant. A CONSTANT is an

identifier that must be initialized and the value of which cannot be changed in the
program body. A NOT NULL constraint can be used for variables, and such vari-
ables must be initialized. The DEFAULT clause, or := , can be used to initialize a
constant or a variable to a value. An expression can be a literal, another variable, or
an expression.
The identifiers are named based on rules given previously in this chapter. Dif-
ferent naming conventions can be used. You should declare one variable per line for
good readability. For example,

DECLARE
v_number NUMBER(2);
v_count NUMBER(1) := 1;
v_state VARCHAR2(2) DEFAULT ‘NJ’;
c_pi CONSTANT NUMBER := 3.14;
v_invoicedate DATE DEFAULT SYSDATE;

In this example, you see a naming convention that uses prefix v_ for variables and
prefix c_ for constants.

ANCHORED DECLARATION

PL/SQL uses %TYPE attribute to anchor a variable’s data type. Another variable
or a column in a table can be used for anchoring. In anchoring, you tell PL/SQL to
ShahCh10v3.qxd 4/16/04 11:59 AM Page 235

Anchored Declaration 235

use a variable or a column’s data type as the data type for another variable in the
program. The general syntax is
variablename typeattribute%TYPE [value assignment];

where typeattribute is another variable’s name or table’s column with a table qualifi-
er (e.g., tablename.columnname). It is very useful while retrieving a value of a col-
umn into a variable with a SELECT query in PL/SQL. For example,
DECLARE
v_num1 NUMBER(3);
v_num2 v_num1%TYPE;

in this example, v_num1 is declared with a data type NUMBER(3). The next vari-
able, v_num2, is declared using the anchoring method and the declaration attribute
%TYPE. The variable v_num2 gets the same data type as v_num1.
Two variables can also be declared and assigned data types to match the col-
umn’s data type. The advantage is that you do not have to cross-reference the data
type used in the table. Oracle does that for you. For example,
DECLARE
v_empsal employee.Salary%TYPE;
v_deptname dept.DeptName%TYPE;

Suppose you do not use the anchoring method to declare variables, which are
assigned values directly from table columns. You can use the DESCRIBE command
to list all the data types for columns. Then, you can declare variables in a program
with the same types and lengths. This will work just fine. The problem will arise when
the column lengths are increased to meet future demands. When you assign values
from those columns to variables, VALUE_ERROR will occur—and you will have
to go back to all the programs to change the variable’s data length! Anchoring defi-
nitely is an advantage in such situations.
A %TYPE declaration anchors the data type of one variable based on anoth-
er variable or column at the time of a PL/SQL block’s compilation. If the source or
original column’s data type is changed, the PL/SQL block must be recompiled to re-
anchor all anchored variables.

Nested Anchoring

The %TYPE attribute’s use can be nested. For example,

DECLARE
– – source variable v_commission
v_commission NUMBER(7, 2);
– – anchored variable v_total_commission
v_total_commission v_commission%TYPE;
– – nested anchoring variable v_net_commission
v_net_commission v_total_commission%TYPE;
ShahCh10v3.qxd 4/16/04 11:59 AM Page 236

236 Chap. 10 PL/SQL: A Programming Language

In this example, the original variable v_commission anchors v_total_commission,

which in turn is used to anchor v_net_commission. There is no limit on the number
of layers of nesting in anchored declarations.
The source variable for a %TYPE declaration does not have to be in the same
block. The variable could be a global variable declared at the SQL * Plus environ-
ment, or it could be declared in a block that contains the current block.

NOT NULL Constraint for %TYPE Declarations

If a source variable is declared with a NOT NULL constraint, the %TYPE declara-
tion inherits the NOT NULL constraint from the source, its anchor. The anchored
variable must be initialized with a value in the %TYPE declaration.
If the source for a %TYPE declaration is a table’s column, the NOT NULL
constraint is not inherited by the anchored variable. There is no need to initialize the
anchored variable, and it can be assigned a NULL value.

ASSIGNMENT OPERATION

The assignment operation is one of the ways to assign a value to a variable. You have
already learned that a variable can be initialized at the time of declaration by using
the DEFAULT option or :=. The assignment operation is used in the executable
section of the program block to assign a literal, another variable’s value, or the result
of an expression to a variable. The general syntax is

VariableName := Literal | VariableName | Expression;

For example,

v_num1 := 100;
v_num2 := v_num1;
v_sum := v_num1 + v_num2;

In these examples, the assumption is made that three variables have already
been declared. The first example assigns 100 to the variable v_num1. The second ex-
ample assigns the value of the variable v_num1 to the variable v_num2. The third
example assigns the result of an addition operation on v_num1 and v_num2 to the
variable v_sum.
The following statements are examples of invalid assignment operations and
the reasons for their lack of validity:

v_count = 10; /* Wrong assignment operator, = sign */

v_count * 2 := v_double; /* Expression cannot be on the left. */
v_num1 := v_num2 :=v_num3; /* Cannot use two assignments in one statement. */
ShahCh10v3.qxd 4/16/04 11:59 AM Page 237

Bind Variables 237

BIND VARIABLES

Bind variables are also known as host variables. These variables are declared in the
host SQL * Plus environment and are accessed by a PL/SQL block. Anonymous
blocks do not take any arguments, but they can access host variables with a colon
prefix (:) and the host variable name. Host variables can be passed to procedures
and functions as arguments. A host variable is declared at the SQL 7 prompt with
the SQL * Plus VARIABLE statement. The syntax of a host variable declaration is

VARIABLE variablename datatype

For example,

SQL> VARIABLE double NUMBER

When a numeric variable is declared with VARIABLE command, precision

and scale values are not used. If a VARCHAR2-type variable is declared, length is
not used. A host variable’s value can be printed in the SQL * Plus environment by
using the PRINT command.
Let us put everything together in a program. The program contains a script
that includes SQL * Plus statements and a PL/SQL block.
In Figure 10-5, two types of variables are used, a local variable v_num and a
host variable g_double. The host variable g_double is declared in SQL * Plus with a
VARIABLE statement, and the program block references it with a colon prefix (:).
The local variable v_num is declared in the declaration section of a program block;
there is no need to use the colon prefix with it. The program assigns the value 5 to

SQL> VARIABLE g_double NUMBER

SQL> DECLARE
2 v_num NUMBER(2);
3 BEGIN
4 v_num := 5;
5 :g_double := v_num * 2;
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> PRINT g_double

G_DOUBLE
-----------
10

SQL>

Figure 10-5 Using a host variable in a PL/SQL block.

ShahCh10v3.qxd 4/16/04 11:59 AM Page 238

238 Chap. 10 PL/SQL: A Programming Language

the local variable v_num, doubles it, and stores the result in the host variable
g_double. Finally, the resulting variable is printed in the host environment with a
PRINT statement.

Question: How does a PL/SQL block end?

Answer: It ends with an END and a semicolon on the same line and a slash
(/) on the next line.

The use of host variables should be minimized in a program block, because they
affect performance. Every time a host variable is accessed within the block, the PL/SQL
engine must stop to request the host environment for the value of the host variable.The
variable’s value can be assigned to a local variable to minimize calls to the host.

SUBSTITUTION VARIABLES IN PL/SQL

PL/SQL does not have any input capabilities in terms of having an input statement.
There are no explicit input/output (I/O) statements, but substitution variables of
SQL are available in PL/SQL. Substitution variables have limitations, which be-
come apparent in a loop.
Let us rewrite the program code in Figure 10-5 to the one in Figure 10-6. When
the code in Figure 10-6 is executed, a standard prompt for p_num appears on the
screen for users to type in a value for it. As you see in the example, there is no need

SQL> VARIABLE g_double NUMBER

SQL> DECLARE
2 v_num NUMBER(2);
3 BEGIN
4 v_num := &p_num;
5 :g_double := v_num * 2;
6 END;
7 /
Enter value for p_num: 10

PL/SQL procedure successfully completed.

SQL> PRINT g_double

G_DOUBLE
-----------
20

SQL>

Figure 10-6 Local, host, and substitution variables.

ShahCh10v3.qxd 4/16/04 11:59 AM Page 239

Printing in PL/SQL 239

to declare substitution variables in the program block. The value of the bind/host
variable is printed with the PRINT command. The value of the local variable v_num
cannot be printed with the PRINT command, however, because the scope of a local
variable is within the block where it is declared.
When substitution variable is used in a program, the output contains lines
showing how the substitution was done. You can suppress those lines by using the
SET VERIFY OFF command before running the program.

PRINTING IN PL/SQL

There is no explicit output statement in PL/SQL. Oracle does have a built-in package
called DBMS_OUTPUT with the procedure PUT_LINE to print. An environment
variable named SERVEROUTPUT must be toggled ON to view output from it.
The DBMS_OUTPUT is the most frequently used package because of its ca-
pabilities to get lines from a file and to put lines into the buffer. The PUT_LINE
procedure displays information passed to the buffer and puts a new-line marker at
the end. For example,
DBMS_OUTPUT.PUT_LINE (‘This line will be displayed’);

The maximum size of the buffer is 1 megabyte.The following command enables

you to view information from the buffer by using the DBMS_OUTPUT package and
also sets the buffer size to the number of bytes specified:
SET SERVEROUTPUT ON [on size 10000];

The PL/SQL block in Figure 10-7 shows the use of DBMS_OUTPUT.PUT_

LINE. Another procedure, DBMS_OUTPUT.PUT, also performs the same task of

SQL> VARIABLE NUM NUMBER

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 DOUBLE NUMBER;
3 BEGIN
4 :NUM := 5;
5 DOUBLE := :NUM * 2;
6 DBMS_OUTPUT.PUT_LINE ((’DOUBLE OF ’ ||
7 TO_CHAR(:NUM) || ’ IS ’ || TO_CHAR(DOUBLE));
8 END;
9 /
DOUBLE OF 5 IS 10

PL/SQL procedure successfully completed.

SQL>

Figure 10-7 DBMS_OUTPUT.PUT_LINE.

ShahCh10v3.qxd 4/16/04 11:59 AM Page 240

240 Chap. 10 PL/SQL: A Programming Language

displaying information from the buffer, but it does not put a new-line marker at the
end. If there is another DBMS_OUTPUT.PUT or DBMS_OUTPUT.PUT_LINE
statement following that statement, its output will be displayed on the same line.

ARITHMETIC OPERATORS

Five standard arithmetic operators are available in PL/SQL for calculations. If more
than one operator exists in an arithmetic expression, the following order of prece-
dence is used:

● Exponentiation is performed first, multiplication and division are performed

next, and addition and subtraction are performed last.
● If more than one operator of the same priority is present, they are performed
from left to right.
● Whatever is in parentheses is performed first.

Figure 10-8 shows arithmetic operators and their use.

Arithmetic Operator Use

 Addition
 Subtraction and negation
* Multiplication
/ Division
** Exponentiation

Figure 10-8 Arithmetic operators.

Question: What is the answer from the following expression?

–2 + 3 * (10 – 2 * 3)/6

Answer: 0 (The operations within the parentheses are performed first, with
multiplication followed by subtraction. The result from within the paren-
theses is multiplied by 3, and that result is divided by 6. Finally, the result is
added to -2)

In this chapter, you learned the basics of PL/SQL. In the next chapter, you will
learn about three programming control structures: sequence, selection, and looping.
(You already know one of them. All examples in this chapter were based on sequence
ShahCh10v3.qxd 4/16/04 11:59 AM Page 241

In a Nutshell . . . 241

structure.) You will also learn to interface with the Oracle server by embedding
SQL statements in PL/SQL program blocks.

IN A NUTSHELL . . .

● PL/SQL (Programming Language extension to Structured Query Language)

is Oracle’s proprietary language.
● PL/SQL contains features of modern languages, such as data encapsulation,
error handling, information hiding, and object-oriented programming (OOP).
● PL/SQL is a block-structured language. A block is of two types: anonymous
block and subprogram (procedures and functions).
● A program code contains reserved words, user-defined identifiers, delimiters,
comments, and literals.
● A program block consists of three sections: declaration, executable, and excep-
tion handling.
● A program uses variables and constants to hold values.A variable’s value can
be changed, but a constant’s value remains the same throughout the execu-
tion of the program.
● A variable is declared in the declarative section with a scalar data type. The
standard data types are number, character, Boolean, and date. PL/SQL also
supports other LOB (Large Object) data types.
● A declaration attribute %TYPE is used to anchor a variable with another
variable’s data type or with a table column’s data type.
● A bind variable, or host variable, is global to a PL/SQL block. An anonymous
block refers to it with a colon prefix (:). A bind variable is declared with the
VARIABLE statement and is printed with the PRINT statement in the
SQL * Plus environment.
● An assignment statement is used in the executable section to assign a literal,
a variable’s value, or the result of an expression to a variable. An assignment
uses the := operator.
● PL/SQL does not have an input statement, but substitution variables are al-
lowed in a block to assign a value to a variable.
● A built-in Oracle package and its procedure DBMS_OUTPUT.PUT_LINE
are used to output information. An environment variable SERVEROUTPUT
must be set to ON before using it.
● Arithmetic operators ( + , - , *, /, and **) are used in mathematical expres-
sions. The operations follow rules of precedence for evaluating expressions
with more than one operator.
ShahCh11v3.qxd 4/16/04 12:00 PM Page 244

11

More on PL/SQL:
Control Structures
and Embedded SQL

IN THIS CHAPTER . . .

● You will learn about various programming control structures in PL/SQL.

● Different decision-making statements based on various options are covered.
● Looping statements are introduced to perform a set of statements repetitively.
● SQL statements are embedded within a PL/SQL block to interact with the
Oracle server.

In the previous chapter, you learned the basics of the PL/SQL programming language.
You are now able to write simple programs using local, host, and substitution variables;
can perform simple calculations by using assignment statements; and know how to use
Oracle’s built-in package DBMS_OUTPUT.PUT_LINE in program blocks to display
results from the buffer. The sample programs and lab exercises have a series of state-
ments that are executed from the beginning to the end in a linear fashion. When an
anonymous block is executed, the code is sent to the PL/SQL engine for compilation.
In this chapter, you will see the use of different control structures employed in a high-
level programming language.
In the last chapter, you saw how to write PL/SQL programs independent of a
database. We start with more independent PL/SQL programs, and then show the actual
use of PL/SQL to interact with the Oracle database. A PL/SQL program block “talks”
to the Oracle database by embedding SQL statements in its executable section.
ShahCh11v3.qxd 4/16/04 12:00 PM Page 245

Control Structures 245

CONTROL STRUCTURES

In a procedural language like PL/SQL, there are three basic programming control
structures:

1. In a sequential structure, a series of instructions are performed from the

beginning to the end in a linear order. None of the instructions is skipped,
and none of the instructions is repeated.
2. The selection structure is also known as a decision structure or an IF-
structure. It involves conditions with a TRUE or FALSE outcome. Based
on the outcome, one of the options is performed, and the other option is
skipped. Selection statements are also available for multiple options.
3. In a looping structure, a series of instructions is performed repeatedly.
There are different looping statements appropriate for a variety of situa-
tions. A programmer has to write a loop correctly to make it perform a
specific number of times.

We have already covered sequential statements in the previous chapter. In this

chapter, we will talk about the selection and the looping structures. In actuality, a
program may utilize one or a combination of all control structures.

Selection Structure

There are three selection or conditional statements in PL/SQL. Relational opera-

tors, logical operators, and other special operators are used to create Boolean ex-
pressions or conditions. The tables in Figures 11-1, 11-2, and 11-3 are repeated from
Chapter 5 for reading convenience. Figure 11-1 shows the use of relational opera-
tors, which constitute simple conditions. Figure 11-2 explains the use of logical oper-
ators in compound conditions. Figure 11-3 shows a truth table for the AND, OR, and
NOT operators. The AND and OR operators are binary operators, because they
work on two conditions. The NOT operator is a unary operator, because it works on
a single condition.

Relational Operator Meaning

 Equal to
<>or ! Not equal to
> Greater than
> Greater than or equal to
< Less than
< Less than or equal to

Figure 11-1 Relational operators.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 246

246 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

Logical Operator Meaning

AND Returns TRUE only if both conditions are true.

OR Returns TRUE if one or both conditions are true.
NOT Returns TRUE if the condition is false.

Figure 11-2 Logical operators.

AND OR NOT

TRUE AND TRUE = TRUE TRUE OR TRUE = TRUE NOT TRUE = FALSE
TRUE AND FALSE = FALSE TRUE OR FALSE = TRUE NOT FALSE = TRUE
FALSE AND TRUE = FALSE FALSE OR TRUE = TRUE NOT NULL = NULL
FALSE AND FALSE = FALSE FALSE OR FALSE = FALSE
NULL AND TRUE = NULL NULL OR TRUE = TRUE
NULL AND FALSE = FALSE NULL OR FALSE = NULL
NULL AND NULL = NULL NULL OR NULL = NULL

Figure 11-3 Truth tables for AND, OR, and NOT operators.

Other special operators (IS NULL, IN, LIKE, and BETWEEN Á AND) dis-
cussed in the SQL section are also available in PL/SQL. PL/SQL has five condition-
al or selection statements available for decision making:

1. IF . . .THEN . . . END IF.

2. IF . . . THEN . . . ELSE . . . END IF.
3. IF . . . THEN . . . ELSIF . . . END IF.
4. CASE . . . END CASE.
5. Searched CASE.

IF . . . THEN . . . END IF. The IF . . . THEN . . . END IF statement is also

known as a simple IF statement. A simple IF statement performs action statements
if the result of the condition is TRUE. If the condition is FALSE, no action is per-
formed, and the program continues with the next statement in the block. The gener-
al syntax is

IF condition(s) THEN
Action statements
END IF;

For example, Figure 11-4 shows a simple IF statement with an output statement,
which will be performed if the day entered is ‘SUNDAY’. If the condition is false,
the statement is skipped. In this example, notice the use of the relational operator
equals 1=2 in a Boolean condition and of the assignment operator 1:= 2 in the action
assignment statement.
ShahCh11v3.qxd 4/16/04 12:00 PM Page 247

Control Structures 247

SQL> DECLARE
2 V_DAY VARCHAR2(9) := ’&DAY’;
3 BEGIN
4 IF (V_DAY = ’SUNDAY’) THEN
5 DBMS_OUTPUT.PUT_LINE(’SUNDAY IS A HOLIDAY!’);
6 END IF;
7 END;
8 /
Enter value for day: SUNDAY

SUNDAY IS A HOLIDAY

PL/SQL procedure successfully completed.

SQL> /
Enter value for day: MONDAY

PL/SQL procedure successfully completed.

SQL>

Figure 11-4 Simple IF statement.

You must have noticed the indentation in the program code. All program
statements can start in the first column. In fact, you can write more than one state-
ment on one line with the appropriate punctuation mark (;) separating them. Such
programming practice, however, can make your program difficult to read. In turn, it
is a good practice to indent statements within a block or a compound statement, be-
cause it makes your program more readable. A program will work just the same
without indenting statements or without adding a comment to it, but good program-
ming practices make everybody’s life easier.

IF Á THEN Á ELSE Á END IF. The IF Á THEN Á ELSE Á END

IF statement is an extension of the simple IF statement. It provides action statements
for the TRUE outcome as well as for the FALSE outcome. The general syntax is

IF condition(s) THEN
Action statements 1
ELSE
Action statements 2
END IF;

If the condition’s outcome is TRUE, action statements 1 are performed. If the out-
come is FALSE, action statements 2 are performed. One set of statements is skipped
in any case.
For an example, see Figure 11-5. If the entered age is 18 or older, age is dis-
played with string ADULT; otherwise, age is displayed with string MINOR.
ShahCh11v3.qxd 4/16/04 12:00 PM Page 248

248 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 V_AGE NUMBER(2) := ’&AGE’;
3 BEGIN
4 IF (V_AGE >=18) THEN
5 DBMS_OUTPUT.PUT_LINE(’AGE: ’ || V_AGE || ’ - ADULT’);
6 ELSE
7 DBMS_OUTPUT.PUT_LINE(’AGE: ’ || V_AGE || ’ - MINOR’);
8 END IF;
9 END;
10 /
Enter value for age: 21
AGE: 21 - ADULT
PL/SQL procedure successfully completed.

SQL> /
Enter value for age: 12
AGE: 12 - MINOR
PL/SQL procedure successfully completed.
SQL>

Figure 11-5 IF. . . ELSE . . . END IF statement.

IF . . . THEN . . . ELSIF . . . END IF. The IF . . . THEN . . . ELSIF . . . END

IF statement is an extension to the previous statement. When you have many alter-
natives/options, you can use previously explained statements, but the ELSIF alter-
native is more efficient than the other two. The DECODE function in SQL is not
allowed in PL/SQL, and the IF . . . THEN . . . ELS . . . END IF statement is not al-
lowed in SQL. The general syntax is
IF condition(s)1 THEN
Action statements 1
ELSIF condition(s)2 THEN
Action statements 2
...
ELSIF condition(s)N THEN
Action statement N
[ELSE
Else Action statements]
END IF;

Notice the word ELSIF, which does not have the last E in ELSE. ELSIF is a
single word, but END IF uses two words. For example, let us revisit the DECODE
function example of Chapter 6 (see Figure 11-6). Figure 11-7 shows the ELSIF
equivalent of the DECODE function.
The same statement can be written with a simple IF statement, though less effi-
ciently. You will need five simple IF statements to accomplish the same task as that
performed by a single compound ELSIF statement. Let us take another example
ShahCh11v3.qxd 4/16/04 12:00 PM Page 249

Control Structures 249

SQL> SELECT LName, FName,

2 DECODE (PositionId, 1, Salary*1.2,
3 2, Salary*1.15,
4 3, Salary*1.1,
5 4, Salary*1.05,
6 Salary) “New Salary”
7 FROM employee;

LNAME FNAME New Salary

--------------- --------------- ----------
Smith John 318000
Houston Larry 172500
Roberts Sandi 86250
McCall Alex 73150
Dev Derek 92000
Shaw Jinku 24500
Garner Stanley 51750
Chen Sunny 36750
Viquez Heillyn
9 rows selected.
SQL>

Figure 11-6 DECODE function.

SQL> DECLARE
2 v_pos NUMBER(1) := &Position;
3 BEGIN
4 IF v_pos=1 THEN
5 DBMS_OUTPUT.PUT_LINE(’20% increase’);
6 ELSIF v_pos=2 THEN
7 DBMS_OUTPUT.PUT_LINE(’15% increase’);
8 ELSIF v_pos=3 THEN
9 DBMS_OUTPUT.PUT_LINE(’10% increase’);
10 ELSIF v_pos=4 THEN
11 DBMS_OUTPUT.PUT_LINE(’5% increase’);
12 ELSE
13 DBMS_OUTPUT.PUT_LINE(’No increase’);
14 END IF;
15 END;
16 /
Enter value for position: 2
15% increase
PL/SQL procedure successfully completed.
SQL>

Figure 11-7 ELSIF statement.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 250

250 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

with compound conditions. First, we will use a simple IF statement (see Fig. 11-8),
and then, we will rewrite it by using ELSIF (Figure 11-9).
The example here assigns a grade of A, B, C, D, or F based on v_score. We are
assuming that the score is within the range of 0 to 100. You will need five simple IF
statements with total of 10 conditions or two conditions per each statement. Now,
suppose the value of v_score is 95. The first statement’s condition is TRUE, so
v_grade will be assigned ‘A’. Because all simple IF statements are independent state-
ments, the execution will continue with the next IF, and so on. There is no other
TRUE condition for v_score equal to 95, so v_grade will be ‘A’ after execution of all
five IF statements. This slows down your program’s execution. The ELSIF, on the
other hand, is one compound statement, and it stops as soon as a match is found. Let
us see how the ELSIF statement looks.

SQL> DECLARE
2 S NUMBER(3) := &SCORE;
3 GRADE CHAR;
4 BEGIN
5 IF S >= 90 AND S <= 100 THEN
6 GRADE := ’A’;
7 END IF;
8 IF S >= 80 AND S <= 89 THEN
9 GRADE := ’A’;
10 END IF;
11 IF S >= 70 AND S <= 79 THEN
12 GRADE := ’C’;
13 END IF;
14 IF S >= 60 AND S <= 69 THEN
15 GRADE := ’D’;
16 END IF;
17 IF S >= 0 AND S <= 59 THEN
18 GRADE := ’F’;
19 END IF;
20 IF S < 0 AND S > 100 THEN
21 GRADE := ’U’;
22 END IF;
23 DBMS_OUTPUT.PUT_LINE(’SCORE IS ’ || TO_CHAR(S));
24 DBMS_OUTPUT.PUT_LINE(’GRADE IS ’ || GRADE);
25 END;
26 /
Enter value for score: 93
SCORE IS 93
GRADE IS A
PL/SQL procedure successfully completed.
SQL>

Figure 11-8 Simple IF with multiple conditions.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 251

Control Structures 251

SQL> DECLARE
2 S NUMBER(3) := &SCORE;
3 GRADE CHAR;
4 BEGIN
5 IF S >= 90 AND S <= 100 THEN
6 GRADE := ’A’;
7 ELSIF S >= 80 AND S <= 89 THEN
8 GRADE := ’B’;
9 ELSIF S >= 70 THEN
10 GRADE := ’C’;
11 ELSIF S >= 60 THEN
12 GRADE := ’D’;
13 ELSIF S >= 0 THEN
14 GRADE := ’F’;
15 ELSIF S < 0 AND S > 100 THEN
16 GRADE := ’U’;
17 END IF;
18 DBMS_OUTPUT.PUT_LINE(’SCORE IS ’ || TO_CHAR(S));
19 DBMS_OUTPUT.PUT_LINE(’GRADE IS ’ || GRADE);
20 END;
21 /
Enter value for score: 77
SCORE IS 77
GRADE IS C
PL/SQL procedure successfully completed.
SQL>

Figure 11-9 ELSIF statement.

The ELSIF statement reduces the number of conditions from 10 to 5 and the
number of statements from five to one. Now, let us consider the same value, 95, as be-
fore.The condition is TRUE in the first IF clause, and v_grade is assigned value ‘A’.The
statement will not continue down anymore, because it will not enter the ELSIF part.
The rest of the statement is ignored, thus making ELSIF more efficient than its coun-
terpart (see Fig. 11-9). In this example, there is an added ELSIF statement to check for
invalid scores (below 0 as well as above 100), which result in an undefined (‘U’) grade.

CASE. The CASE statement is an alternative to the IF . . . THEN . . . ELSIF

. . . END IF statement.The CASE statement begins with key word CASE and ends with
the key words END CASE. The body of the CASE statement contains WHEN clauses,
with values or conditions, and action statements.When a WHEN clause’s value/condition
evaluates to TRUE, its action statements are executed.The general syntax is
CASE [variable_name]
WHEN value1|condition1 THEN action_statement1;
WHEN value2\condition2 THEN action_statement2;
…
ShahCh11v3.qxd 4/16/04 12:00 PM Page 252

252 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

WHEN valueN|conditionN THEN action_statementN;

ELSE action_statement;
END CASE;

Searched CASE. A statement with a value is known as a CASE statement,

and a statement with conditions is known as a searched CASE statement. A CASE
statement uses variable_name as a selector, but a searched CASE does not use
variable_name as a selector. Figure 11-10 is an example of a CASE statement that
evaluates if a number is odd or even. Figure 11-11 rewrites the same solution for a
searched CASE statement.

SQL> DECLARE /* Example of Case */

2 V_NUM NUMBER := &ANY_NUM;
3 V_RES NUMBER;
4 BEGIN
5 V_RES := MOD(V_NUM, 2);
6 CASE V_RES
7 WHEN 0 THEN DBMS_OUTPUT.PUT_LINE(V_NUM || ’ IS EVEN’);
8 ELSE DBMS_OUTPUT.PUT_LINE(V_NUM || ’ IS ODD’);
9 END CASE;
10 END;
11 /
Enter value for any_num: 5
5 IS ODD
PL/SQL procedure successfully completed.
SQL>

Figure 11-10 CASE statement.

SQL> DECLARE /* Example of Searched Case */

2 V_NUM NUMBER := &ANY_NUM;
3 BEGIN
4 CASE
5 WHEN MOD(V_NUM, 2)=0 THEN
6 DBMS_OUTPUT.PUT_LINE(V_NUM || ’ IS EVEN’);
7 ELSE
8 DBMS_OUTPUT.PUT_LINE(V_NUM || ’ IS ODD’);
9 END CASE;
10 END;
11 /
Enter value for any_num: 5
5 IS ODD
PL/SQL procedure successfully completed.
SQL>

Figure 11-11 Searched CASE statement.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 253

Control Structures 253

Nested IF. The nested IF statement contains an IF statement within another

IF statement. If the condition in the outer IF statement is TRUE, the inner IF state-
ment is performed. Any IF statement with a compound condition can be written as
a nested IF statement. For example, the program segment in Figure 11-12 assigns an
insurance surcharge based on an individual’s gender and age. There are four cate-
gories:

1. Male 25 or over.
2. Male under 25.
3. Female 25 or over.
4. Female under 25.

SQL> DECLARE
2 V_GENDER CHAR := ’&SEX’;
3 V_AGE NUMBER(2) := ’&AGE’;
4 V_CHARGE NUMBER(3,2);
5 BEGIN
6 IF (V_GENDER = ’M’ AND V_AGE >= 25) THEN
7 V_CHARGE := 0.05;
8 END IF;
9 IF (V_GENDER = ’M’ AND V_AGE < 25) THEN
10 V_CHARGE := 0.10;
11 END IF;
12 IF (V_GENDER = ’F’ AND V_AGE >= 25) THEN
13 V_CHARGE := 0.03;
14 END IF;
15 IF (V_GENDER = ’F’ AND V_AGE < 25) THEN
16 V_CHARGE := 0.06;
17 END IF;
18 DBMS_OUTPUT.PUT_LINE(’GENDER: ’ || V_GENDER);
19 DBMS_OUTPUT.PUT_LINE(’AGE: ’ || TO_CHAR(V_AGE));
20 DBMS_OUTPUT.PUT_LINE(’SURCHARGE: ’ || TO_CHAR(V_CHARGE));
21 END;
22 /
Enter value for sex: F
Enter value for age: 18
GENDER: F
AGE: 18
SURCHARGE: .06

PL/SQL procedure successfully completed.

SQL>

Figure 11-12 Simple IF with multiple conditions.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 254

254 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

Now, we will rewrite the code done with a simple IF in Figure 11-12 by using
nested IF statements (see Fig. 11-13). Again, remember that the nested IF statement
will make the code more efficient than the simple IF version. The THEN portion of
the outer IF calculates the insurance surcharge for male individuals, and the ELSE
portion calculates the same for the female individuals. The inner IF statements in
each portion check for the age.

Looping Structure

Looping means iterations. A loop repeats a statement or a series of statements a spe-

cific number of times, as defined by the programmer. You would use a loop to repeat
a series of statements many times rather than typing the same statements many

SQL> DECLARE
2 V_GENDER CHAR := ’&SEX’;
3 V_AGE NUMBER (2) := ’&AGE’;
4 V_CHARGE NUMBER (3,2);
5 BEGIN
6 IF (V_GENDER = ’M’) THEN /* MALE */
7 IF (V_AGE >= 25) THEN
8 V_CHARGE := 0.05;
9 ELSE
10 V_CHARGE := 0.10;
11 END IF;
12 ELSE /* FEMALE */
13 IF (V_AGE >= 25) THEN
14 V_CHARGE := 0.03;
15 ELSE
16 V_CHARGE := 0.06;
17 END IF;
18 END IF;
19 DBMS_OUTPUT.PUT_LINE(’GENDER: ’ || V_GENDER);
20 DBMS_OUTPUT.PUT_LINE(’AGE: ’ || TO_CHAR(V_AGE));
21 DBMS_OUTPUT.PUT_LINE(’SURCHARGE: ’||TO_CHAR(V_CHARGE));
22 END;
23 /
Enter value for sex: F
Enter value for age: 18
GENDER: F
AGE: 18
SURCHARGE: .06

PL/SQL procedure successfully completed.

SQL>

Figure 11-13 Nested IF statement.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 255

Control Structures 255

times. Three types of looping statements are available in PL/SQL:

1. Basic loop.
2. WHILE loop.
3. FOR loop.

Each loop has different syntax, and each works somewhat differently.

Basic loop. A basic loop is a loop that is performed repeatedly. Once a

loop is entered, all statements in the loop are performed. When the bottom of the
loop is reached, control shifts back to the top of the loop. The loop will continue in-
finitely. An infinite loop, or a “never-ending loop,” is a logical error in programming.
The only way to terminate a basic loop is by adding an EXIT statement inside the
loop. The general syntax is

LOOP
Looping statement1;
Looping statement2;
...
Looping statementN;
EXIT [WHEN condition];
END LOOP;

The EXIT statement in a loop could be an independent statement, or it could

be part of an IF statement. For example,

IF v_count > 10 THEN

EXIT;
END IF;

You can also add a condition with the optional WHEN clause that will end the
loop when the condition becomes true. For example,

EXIT WHEN v_count > 10;

The condition is not checked at the top of the loop, but it is checked inside the
body of the loop. The basic loop is performed at least once, because the condition is
tested after entering the body of the loop. Such a loop is also called a post-test loop.
The example shown in Figure 11-14 uses a counter to control the number of
loop executions. There are three necessary statements in a counter-controlled loop.
The counter must be initialized, the value of the counter must change within the
loop (increment or decrement), and a proper condition must exist in the loop. If
value of the counter is not changed inside the loop, it will result in an infinite loop.
The initial value, the increment/decrement, and the condition control the total num-
ber of loop executions.
ShahCh11v3.qxd 4/16/04 12:00 PM Page 256

256 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 V_COUNT NUMBER(2);
3 V_SUM NUMBER(2) := 0;
4 V_AVG NUMBER(3,1);
5 BEGIN
6 V_COUNT := 1; /* COUNTER INITIALIZED */
7 LOOP
8 V_SUM := V_SUM + V_COUNT;
9 V_COUNT := V_COUNT + 1; /* COUNTER INCREMENTED */
10 EXIT WHEN V_COUNT > 10; /* CONDITION */
11 END LOOP;
12 V_AVG := V_SUM / (V_COUNT -1);
13 DBMS_OUTPUT.PUT_LINE(’AVERAGE OF 1 TO 10 IS ’
14 || TO_CHAR(V_AVG));
15 END;
16 /
AVERAGE OF 1 TO 10 IS 5.5

PL/SQL procedure successfully completed.

SQL>

Figure 11-14 Counter-controlled basic loop.

Question: In a basic loop, if the counter is initialized to one and is incre-

mented within the loop by two, and if the condition at the bottom of the
loop body is EXIT WHEN the counter is less than 10, how many times the
loop is performed?

Answer: One time (the loop is performed once and the first check of condi-
tion returns true, so the loop ends). Tricky, isn’t it?

Question: In a basic loop, the counter is initialized to zero and is incre-

mented within the loop by one. How many times will the loop be performed
if the condition at the bottom of the loop body is EXIT WHEN the count-
er equals five?

Answer: Five times (for counter values equal to 0, 1, 2, 3, and 4).

Question: In a basic loop, the counter is initialized to 10 and is incremented

within the loop by one. How many times will the loop be performed if the
condition at the bottom of the loop body is EXIT WHEN the counter
equals 10?

Answer: The loop is infinite (the condition will never become true).
ShahCh11v3.qxd 4/16/04 12:00 PM Page 257

Control Structures 257

WHILE loop. The WHILE loop is an alternative to the basic loop and is per-
formed as long as the condition is true. It terminates when the condition becomes false.
If the condition is false at the beginning of the loop, the loop is not performed at all.The
WHILE loop does not need an EXIT statement to terminate. The general syntax is
WHILE condition LOOP
Looping statement1;
Looping statement2;
...

Looping statement n;
END LOOP;

In Figure 11-15, you see the same average program of Figure 11-14 rewritten
with the WHILE loop. There are obvious differences between the basic loop and the
WHILE loop. Figure 11-16 explains the differences between them.

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 V_COUNT NUMBER(2);
3 V_SUM NUMBER(2) := 0;
4 V_AVG NUMBER(3,1);
5 BEGIN
6 V_COUNT := 1; /* COUNTER INITIALIZED */
7 WHILE V_COUNT <= 10 LOOP /* CONDITION */
8 V_SUM := V_SUM + V_COUNT;
9 V_COUNT := V_COUNT + 1; /* COUNTER INCREMENTED */
10 END LOOP;
11 V_AVG := V_SUM / (V_COUNT -1);
12 DBMS_OUTPUT.PUT_LINE
13 (’AVERAGE OF 1 TO 10 IS ’ || TO_CHAR(V_AVG));
14 END;
15 /
AVERAGE OF 1 TO 10 IS 5.5
PL/SQL procedure successfully completed.
SQL>

Figure 11-15 Counter-controlled WHILE loop.

Basic Loop WHILE Loop

It is performed as long as the condition is false. It is performed as long as the condition is true.
It tests the condition inside the loop It checks condition before entering the loop
(post-test loop). (pretest loop).
It is performed at least one time. It is performed zero or more times.
It needs the EXIT statement to terminate. There is no need for an EXIT statement.

Figure 11-16 Differences between a basic loop and a WHILE loop.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 258

258 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

FOR loop. The FOR loop is the simplest loop you can write. Unlike the basic
and WHILE loops, you do not have to initialize, test, and increment/decrement the
loop control variable separately.You do it in the loop’s header.There is no need to use
an EXIT statement, and the counter need not be declared. The counter used in the
loop is implicitly declared as an integer, and it is destroyed on the loop’s termination.
The counter may not be used within the loop body in an assignment statement as a
target variable. The general syntax is

FOR counter IN [REVERSE] lower..upper LOOP

Looping statement1
Looping statement2
...

Looping statementN
END LOOP;

The counter varies from the lower value to the upper value, incrementing by
one with every loop execution. The loop can also be used with the counter starting
at a higher value and decrementing by one with every loop execution. The key word
REVERSE is used for varying the counter in the reverse order, or from a higher to
a lower value.
The program in Figure 11-17 does not declare v_count, and there is no condi-
tion or explicit statement to change the counter’s value. The same program with the
counter’s value in reverse order will only change by one line. The FOR statement
will look like

FOR v_count IN REVERSE 1..10 LOOP

SQL> DECLARE
2 V_COUNT NUMBER(2);
3 V_SUM NUMBER(2) := 0;
4 V_AVG NUMBER(3,1);
5 BEGIN
6 FOR V_COUNT IN 1..10 LOOP
7 V_SUM := V_SUM + V_COUNT;
8 END LOOP;
9 V_AVG := V_SUM / 10;
10 DBMS_OUTPUT.PUT_LINE
11 (’AVERAGE OF 1 TO 10 IS ’ || TO_CHAR(V_AVG));
12 END;
13 /
AVERAGE OF 1 TO 10 IS 5.5

PL/SQL procedure successfully completed.

SQL>

Figure 11-17 FOR loop.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 259

Nested Blocks 259

One important point about the loop-control variable is that it is declared, in-
cremented/decremented, and destroyed implicitly, but its value cannot be changed
explicitly within the FOR loop’s body. It cannot be a target variable in an assign-
ment statement.

Nested loops. You can use a loop within another loop. Loops can be nest-
ed to many levels. When the inner loop ends, it does not automatically end the outer
loop enclosing it. You can end an outer loop from within the inner loop by labeling
each loop and then using the EXIT statement. EXIT WHEN exits the current loop,
but EXIT out_loop WHEN also exits the outer loop. The loop labels use the same
naming rules as those used for identifiers. The loops are labeled before the key word
LOOP on the same line or on a separate line. The loop label is enclosed within two
pairs of angle brackets (V and W ). For example,

<<out_loop>>
LOOP
...
EXIT WHEN condition;
<<in_loop>>
LOOP
...
EXIT out_loop WHEN condition; /* exits out_loop */
EXIT WHEN condition; /*exits in_loops */
...
END LOOP in_loop; /* label optional here */
...
END LOOP out_loop; /*label optional here */

NESTED BLOCKS

PL/SQL block may contain another PL/SQL block; in other words, PL/SQL blocks
can be nested. The execution starts with the outer block and continues with the
inner block. The variables declared in the outer block are global to the inner block,
and they are accessible in the inner block. The variables declared in the inner block,
however, are not accessible in the outer block. For example,
DECLARE /* Outer block starts here.*/
Var1 NUMBER; /* known to outer and inner*/
BEGIN
... /* can use Var1 here */
DECLARE /*Inner block starts here.*/
Var2 NUMBER; /* known to inner block */
BEGIN
... /* can use Var1 and Var2 here */
END; /* Inner block ends here.*/
... /* can use Var1 here */
END; /* Outer block ends here.*/
ShahCh11v3.qxd 4/16/04 12:00 PM Page 260

260 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

SQL IN PL/SQL

The PL/SQL statements have control structures for calculations, decision making,
and iterations. You need to use SQL to interface with the Oracle database. When
changes are necessary in the database, SQL can be used to retrieve and change in-
formation. PL/SQL supports all Data Manipulation Language (DML) statements,
such as INSERT, UPDATE, and DELETE. It also supports the Transaction Control
Language statements ROLLBACK, COMMIT, and SAVEPOINT. You can retrieve
data using the data retrieval statement SELECT. A row of data can be used to as-
sign values to variables. More than one row can be retrieved and processed using
cursors (covered in the next chapter). PL/SQL statements can use single-row func-
tions, but group functions are not available for PL/SQL statements. SQL statements
in the PL/SQL block, however, can still utilize those group functions.
PL/SQL does not support Data Definition Language (DDL) statements, such
as CREATE, ALTER, and DROP. The Data Control Language (DCL) statements
GRANT and REVOKE also are not available in PL/SQL.

SELECT Statement in PL/SQL

The SELECT statement retrieves data from Oracle tables. The syntax of SELECT is
different in PL/SQL, however, because it is used to retrieve values from a row into a
list of variables or into a PL/SQL record. The general syntax is
SELECT columnnames
INTO variablenames / RecordName
FROM tablename
WHERE condition;

where columnnames must contain at least one column and may include arithmetic
or string expressions, single-row functions, and group functions. Variablenames must
contain a list of local or host variables to hold values retrieved by the SELECT
clause. The variables are declared either at the SQL * Plus prompt or locally under
the DECLARE section (see Fig. 11-18). The recordname is a PL/SQL record (cov-
ered in the next chapter). All the features of SELECT in SQL are available with an
added mandatory INTO clause.
The INTO clause must contain one variable for each value retrieved from the
table. The order and data type of the columns and variables must correspond. The
SELECT Á INTO statement must return one and only one row. It is your respon-
sibility to code a statement that returns one row of data. If no rows are returned, the
standard exception (error condition) NO_DATA_FOUND occurs. If more than one
row are retrieved, the TOO_MANY_ROWS exception occurs. You will learn more
about exceptions and exception handling in the next chapter.
In Figure 11-18, a few columns from a row of the EMPLOYEE table are re-
trieved into a series of variables. The variables can be declared with data types, but
more appropriately, attribute %TYPE is used to avoid any data-type mismatches.
The SQL statement in PL/SQL ends with a semicolon (;). The INTO clause is
mandatory in a SELECT statement when used in a PL/SQL block. Figure 11-19
ShahCh11v3.qxd 4/16/04 12:00 PM Page 261

SQL IN PL/SQL 261

SQL> DECLARE
2 V_LAST EMPLOYEE.LNAME%TYPE;
3 V_FIRST EMPLOYEE.FNAME%TYPE;
4 V_SAL EMPLOYEE.SALARY%TYPE;
5 BEGIN
6 SELECT LNAME, FNAME, SALARY
7 INTO V_LAST, V_FIRST, V_SAL
8 FROM EMPLOYEE
9 WHERE EMPLOYEEID = 200;
10 DBMS_OUTPUT.PUT_LINE
11 (’EMPLOYEE NAME: ’ || V_FIRST || ’ ’ || V_LAST );
12 DBMS_OUTPUT.PUT_LINE
13 (’SALARY: ’ || TO_CHAR(V_SAL));
14 END;
15 /
EMPLOYEE NAME: Jinku Shaw
SALARY: 24500

PL/SQL procedure successfully completed.

SQL>

Figure 11-18 SELECT-INTO in PL/SQL.

SQL> DECLARE
2 V_ID EMPLOYEE.EMPLOYEEID%TYPE;
3 V_DEPT EMPLOYEE.DEPTID%TYPE := &DEPT_NUM;
4 BEGIN
5 SELECT EMPLOYEEID INTO V_ID
6 FROM EMPLOYEE WHERE DEPTID = V_DEPT;
7 END;
8 /
Enter value for dept_num: 10
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

SQL> /
Enter value for dept_num: 50
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

SQL>

Figure 11-19 SELECT Á INTO with Error.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 262

262 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

shows another example that results in exceptions because the SELECT Á INTO
statement returns either too many rows or no data.

DATA MANIPULATION IN PL/SQL

You can use all DML statements in PL/SQL with the same syntax you used in SQL.
The three DML statements to manipulate data are:

1. The INSERT statement to add a new row in a table.

2. The DELETE statement to remove a row or rows.
3. The UPDATE statement to change values in a row or rows.

INSERT Statement

We will use an INSERT statement to add a new employee in the EMPLOYEE

table. The statement will use sequences created earlier. For simplicity, only a few
columns are used in the statement in Figure 11-20. NEXTVAL uses the next value
from the sequence as the new EmployeeId, and CURRVAL uses the current value
of the department from that sequence. If you also decide to insert today’s date as the
hire date, you could use the SYSDATE function for the value.

SQL> BEGIN
2 INSERT INTO EMPLOYEE
3 (EMPLOYEEID, LNAME, FNAME, SALARY, DEPTID)
4 VALUES
5 (EMPLOYEE_EMPLOYEEID_SEQ.NEXTVAL, ’RAI’,
6 ’AISH’, 90000, DEPT_DEPTID_SEQ.CURRVAL);
7 COMMIT;
8 END;
9 /

PL/SQL procedure successfully completed.

SQL>

Figure 11-20 INSERT in PL/SQL.

DELETE Statement

We will show the use of the DELETE statement in the PL/SQL block to remove
some rows. Suppose the NamanNavan (N2) Corporation decides to remove the IT
Department. All the employees belonging to that department must be removed
ShahCh11v3.qxd 4/16/04 12:00 PM Page 263

Data Manipulation in PL/SQL 263

SQL> DECLARE
2 V_DEPTID DEPT.DEPTID%TYPE;
3 BEGIN
4 SELECT DEPTID
5 INTO V_DEPTID
6 FROM DEPT
7 WHERE UPPER(DEPTNAME) = ’&DEPT_NAME’
8 DELETE FROM EMPLOYEE
9 WHERE DEPTID = V_DEPTID;
10 COMMIT;
11 END;
12 /
Enter value for dept_name: IT

PL/SQL procedure successfully completed.

SQL>

Figure 11-21 DELETE in PL/SQL.

from the EMPLOYEE table. Figure 11-21 shows the DELETE statement in
PL/SQL.

UPDATE Statement

The UPDATE statement can be used in a PL/SQL block for modification of data.
The company decides to give a bonus commission to all the employees who are en-
titled to commission. The bonus is 10% of the commission received. Figure 11-22
shows an example of an UPDATE statement in PL/SQL block to modify commission.

SQL> DECLARE
2 V_INCREASE NUMBER := &DECIMAL_INCREASE;
3 BEGIN
4 UPDATE EMPLOYEE
5 SET SALARY = SALARY * (1 + V_INCREASE)
6 WHERE EMPLOYEEID = &EMP_ID;
7 COMMIT;
8 END;
9 /
Enter value for decimal_increase: 0.15
Enter value for emp_id: 545

PL/SQL procedure successfully completed.

SQL>

Figure 11-22 UPDATE in PL/SQL.

ShahCh11v3.qxd 4/16/04 12:00 PM Page 264

264 Chap. 11 More on PL/SQL: Control Structures and Embedded SQL

TRANSACTION CONTROL STATEMENTS

You know what a transaction is. You also know the transaction control capabilities
in Oracle. (If you don’t remember, review Chapter 9). In Figures 11-18, 11-19, and
11-20, after performing a DML statement, the sample blocks have used a COMMIT
statement. You do not have to commit within a PL/SQL block. If you do decide to
use it, your data will be written to the disk right away, and the locks from those rows
will be released. All transaction control statements are allowed in PL/SQL, and they
are as follows:

● The COMMIT statement to commit the current transaction.

● The SAVEPOINT statement to mark a point in your transaction.
● The ROLLBACK [TO SAVEPOINT n] statement to discard all or part of
the transaction.

IN A NUTSHELL . . .

● The three control structures in PL/SQL are sequence, selection, and looping.
● In a sequence structure, the instructions are performed in a linear order.
● The selection structure involves decision making based on the outcome of a
Boolean expression.
● The looping structure contains a series of instructions that are performed
repeatedly.
● Four selection structure statements in PL/SQL are IF . . . THEN . . . END IF,
IF . . . THEN . . . ELSE . . . END IF, IF . . . THEN . . . ELSIF . . . END IF,
and CASE statements.
● A CASE statement uses a variable as a selector and checks its value in
WHEN clauses.
● A searched CASE statement does not use a variable as a selector, but it
does use conditions in WHEN clauses.
● Boolean expressions or conditions use relational operators 1= , 6 7 or ! = ,
7, 7 = , 6, and 6 = 2, logical operators (AND, OR, and NOT), and other op-
erators (BETWEEN. . .AND, IS NULL, and LIKE).
● It is good practice to add comments to a program and indent statements
within a programming block.
● The three types of loops in PL/SQL are the basic loop, WHILE loop, and
FOR loop.
● The basic loop is performed at least once and for as long as the condition is
false. It is known as a post-test loop. The basic loop needs the EXIT state-
ment to terminate.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 267

12

PL/SQL Cursors
and Exceptions

IN THIS CHAPTER . . .

● You will learn about a private work area for an SQL statement and its active
set, called a cursor.
● You will be introduced to implicit and explicit cursor types.
● You will perform open, fetch, and close actions on explicit cursors.
● Use of cursor FOR loops and its implied statements are explained.
● Cursors with parameters and variable cursors are introduced.
● PL/SQL errors, known as exceptions, and their types are discussed.
● The process of declaring, raising, and handling different types of exceptions
is covered.
In previous chapters, you learned about different control structures: sequence, se-
lection, and looping. All structured programming languages support these struc-
tures. Other statements are also available in most of the languages. One of these
additional statements is the GOTO statement, which allows you to branch uncondi-
tionally. All you have to code is GOTO V labelname W, and the control shifts to the
statement after the label. The GOTO statement, though available, is not preferred,
however, because of its nonstructured nature. You also know how to use an SQL
statement within a PL/SQL block for data retrieval, data manipulation, and transac-
tion control.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 268

268 Chap. 12 PL/SQL Cursors and Exceptions

In this chapter, you will learn about some advanced features of PL/SQL, such
as retrieving more than one row from a database into a work area called a cursor.
One of the strongest benefits of PL/SQL is its error-handling capabilities. The error
conditions, known as exceptions, in PL/SQL, are also covered in this chapter.

CURSORS

When you execute an SQL statement from a PL/SQL block, Oracle assigns a private
work area for that statement. The work area, called a cursor, stores the statement
and the results returned by execution of that statement. A cursor is created either
implicitly or explicitly by you.

Types of Cursors

The cursor in PL/SQL is of two types:

1. In a static cursor, the contents are known at compile time. The cursor ob-
ject for such an SQL statement is always based on one SQL statement.
2. In a dynamic cursor, a cursor variable that can change its value is used.
The variable can refer to different SQL statements at different times.

This chapter covers static cursors in detail. It also introduces you to the new
concept of dynamic cursors using a cursor variable. The static cursors are of two
types as well:

1. You do not declare an implicit cursor. PL/SQL declares, manages, and

closes it for every Data Manipulation Language (DML) statement, such
as INSERT, UPDATE, or DELETE.
2. You declare an explicit cursor when you have an SQL statement in a
PL/SQL block that returns more than one row from an underlying table.
The rows retrieved by such a statement into an explicit cursor make up the
active set. When opened, the cursor points to the first row in the active set.
You can retrieve and work with one row at a time from the active set. With
every fetch of a row, the pointer moves to the next row. The cursor returns
the current row to which it is pointing.

IMPLICIT CURSORS

PL/SQL creates an implicit cursor when an SQL statement is executed from within
the program block. The implicit cursor is created only if an explicit cursor is not at-
tached to that SQL statement. Oracle opens an implicit cursor, and the pointer is set
to the first (and the only) row in the cursor. Then, the SQL statement is fetched and
executed by the SQL engine on the Oracle server. The PL/SQL engine closes the
ShahCh12v3.qxd 4/16/04 12:01 PM Page 269

Explicit Cursors 269

implicit cursor automatically. A programmer cannot perform on an implicit cursor all

the operations that are possible on explicit cursor statements. PL/SQL creates an im-
plicit cursor for each DML statement in PL/SQL code.You cannot use an explicit cur-
sor for DML statements. You can choose to declare an explicit cursor for a SELECT
statement that returns only one row of data, but if you don’t declare an explicit cursor
for a SELECT statement returning one row of data, an implicit cursor is created for it.
You have no control over an implicit cursor. The implied queries perform op-
erations on implicit cursors. PL/SQL actually tries to fetch twice to make sure that a
TOO_MANY_ROWS exception does not exist. The explicit cursor is more effi-
cient, because it does not try that extra fetch. It is possible to use an explicit cursor
for a SELECT statement that returns just one row, because you have control over it.
For example,

CURSOR deptname_cur IS
SELECT DeptName, Location FROM dept WHERE DeptId = 10;

Here, only one row is retrieved by the cursor with two column values, Finance and
Charlotte, which later can be assigned to variables by fetching that row.

EXPLICIT CURSORS

An explicit cursor is declared as a SELECT statement in the PL/SQL block. It is

given a name, and you can use explicit statements to work with it. You have total
control of when to open the cursor, when to fetch a row from it, and when to close it.
There are cursor attributes in PL/SQL to get the status information on explicit cur-
sors. Remember, you can declare an explicit cursor for a SELECT statement that re-
turns one or more rows, but you cannot use an explicit cursor for a DML statement.
Four actions can be performed on an explicit cursor:

1. Declare it.
2. Open it.
3. Fetch row(s) from it.
4. Close it.

Declaring an Explicit Cursor

A cursor is declared as a SELECT statement. The SELECT statement must not

have an INTO clause in a cursor’s declaration. If you want to retrieve rows in a spe-
cific order into a cursor, an ORDER BY clause can be used in the SELECT state-
ment. The general syntax is

DECLARE
CURSOR cursorname IS
SELECT statement;
ShahCh12v3.qxd 4/16/04 12:01 PM Page 270

270 Chap. 12 PL/SQL Cursors and Exceptions

where cursorname is the name of the cursor that follows identifier-naming rules. The
SELECT statement is any valid data-retrieval statement. The cursor declaration is
done in the DECLARE section of the PL/SQL block, but a cursor cannot be used in
programming statements or expressions, as with other variables.
For example, Figures 12-1 and 12-2 show declarations of two cursors. In Figure
12-1, the cursor is based on a SELECT query that will retrieve all rows from the
DEPT table in the work area. In the Figure 12-2, two columns, EmployeeId and
Salary, are selected into the cursor with DeptId equal to 20.

SQL> DECLARE
2 CURSOR DEPT_CUR
3 IS
4 SELECT *
5 FROM DEPT;
6 BEGIN
7 ...
8 END;

Figure 12-1 Explicit cursor.

SQL> DECLARE
2 CURSOR EMPLOYEE_CUR
3 IS
4 SELECT EMPLOYEEID, SALARY
5 FROM EMPLOYEE
6 WHERE DEPTID = 20;
7 BEGIN
8 ...
9 END;

Figure 12-2 Explicit cursor.

A cursor is based on a SELECT statement, so it is linked to at least one table

from the database. The list that follows can contain the names of columns, local vari-
ables, constants, functions, and bind variables. It is possible for a variable to have the
same name as a column in a table. If you try to use both of them together in a SE-
LECT statement, the column gets higher precedence. Though permitted, it is not
advisable to use the same name for a variable that exists in a column retrieved by
the SELECT statement.
In the next section, we will talk about the actions performed on an explicit cursor.

Actions on Explicit Cursors

Actions are performed on cursors declared in the DECLARE section of the block.
Before rows can be retrieved from a cursor, you must open the cursor.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 271

Explicit Cursors 271

Opening a Cursor. When a cursor is opened, its SELECT query is executed.

The active set is created using all tables in the query and then restricting to rows that
meet the criteria. The data retrieved by the SELECT query is brought into the cur-
sor or the work area. The cursor points to the first row in the active set. PL/SQL uses
an OPEN statement to open a cursor. The general syntax is
OPEN cursorname;

For example,
OPEN employee_cur;

You must open a cursor that has not been opened in the program block or is
closed to retrieve data into it. If you try to open a cursor that is already open, the fol-
lowing Oracle error message appears:
PLS-06511:PL/SQL: cursor already open

Notice the change in the error message prefix PLS! The errors with prefix ORA are
Oracle database errors, and the errors with prefix PLS are PL/SQL errors.You will
see later how this can be avoided using the cursor attribute %ISOPEN.

Fetching Data from a Cursor. The SELECT statement creates an active

set based on tables in the FROM clause, column names in the SELECT clause, and
rows based on conditions in the WHERE clause. The cursor is a virtual table that
you can work with. You can retrieve a row that the cursor is pointing to, and the val-
ues from that row are retrieved into variables or into a PL/SQL record to perform
processing. After reading values from a row into variables, the cursor pointer moves
to the next row in the active set. The number of variables must match the number of
columns in the row. In PL/SQL, a FETCH statement is used for this action. The gen-
eral syntax is
FETCH cursorname INTO variablelist / recordname;

where variablelist may include a local variable, a table, or a bind variable and
recordname is the name of a record structure. For example,
FETCH employee_cur INTO v_empnum, v_sal;

or
FETCH employee_cur INTO emp_rec;

where emp_rec is declared with %ROWTYPE declaration attribute:

emp_rec employee_cur%ROWTYPE;

In the first example, two columns, EmployeeID and Salary, are retrieved into
v_empnum and v_sal, respectively. The number of items matches the number of
variables in the SELECT statement. The order of items and variables must also
match. The variables should be declared with a %TYPE declaration variable to en-
sure the correct data type. If the number of items in SELECT does not match the
number of variables, it results in the following compiler error:
PLS-00394:wrong number of values in the INTO list of a FETCH statement
ShahCh12v3.qxd 4/16/04 12:01 PM Page 272

272 Chap. 12 PL/SQL Cursors and Exceptions

The second example of FETCH uses a record. A composite data type can be
used for the record instead of the CursorName%ROWTYPE declaration. You will
learn about the record data type in the next chapter.
Suppose you opened a cursor in a PL/SQL block to retrieve data from a table,
and then inserts, deletes, and updates are performed on that table after the OPEN
statement is executed. Oracle enforces read consistency, and the data manipulation
statements are ignored. You will have the same data from the point of execution of
OPEN to the point of execution of CLOSE statements. Changing data in the under-
lying table does not change data in the work area.

Closing a cursor. When you are done with a cursor, you should close it. A
closed cursor can be reopened again. If you terminate your PL/SQL program with-
out closing an open cursor, it will not result in an exception. In fact, the local cursor
declared in a PL/SQL block is closed automatically when the block terminates. It is
a good habit, however, to close an open cursor before terminating the block. There
is a limit to the number of cursors a user may open simultaneously. The default value
is in a parameter called OPEN_CURSORS, which has default value of 50. A user re-
leases memory by closing a cursor. PL/SQL uses the CLOSE statement to close a
cursor. The general syntax is
CLOSE cursorname;

For example,
CLOSE employee_cur;

EXPLICIT CURSOR ATTRIBUTES

Actions can be performed on cursors with OPEN, FETCH, and CLOSE statements.
You can get information about the current status of a cursor or the result of the last
fetch by using cursor attributes. The four explicit cursor attributes are:

%ISOPEN It returns TRUE if the cursor is open; otherwise, it

returns FALSE.
%FOUND It returns TRUE if the last fetch returned a row; other-
wise, it returns FALSE.
%NOTFOUND It returns TRUE if the last fetch did not return a row;
otherwise, it returns FALSE. It complements the
%FOUND attribute.
%ROWCOUNT It returns total number of rows returned.

%ISOPEN

The %ISOPEN attribute is useful in making sure that you do not open a cursor that
is already open. It is also appropriate for making sure that a cursor is open before
ShahCh12v3.qxd 4/16/04 12:01 PM Page 273

Explicit Cursor Attributes 273

trying to fetch from it. For example, Figure 12-3 tests to see if a cursor is open. If it is
not open, already, the cursor is opened. Then, execution continues with a loop and a
fetch in it.

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 V_LAST EMPLOYEE.LNAME%TYPE;
3 V_FIRST EMPLOYEE.FNAME%TYPE;
4 V_SAL EMPLOYEE.SALARY%TYPE;
5 CURSOR EMPLOYEE_CUR IS
6 SELECT LNAME, FNAME, SALARY
7 FROM EMPLOYEE
8 WHERE DEPTID = 20;
9 BEGIN
10 IF NOT EMPLOYEE_CUR%ISOPEN THEN
11 OPEN EMPLOYEE_CUR;
12 END IF;
13 LOOP
14 FETCH EMPLOYEE_CUR
15 INTO V_LAST, V_FIRST, V_SAL;
16 EXIT WHEN NOT EMPLOYEE_CUR%FOUND;
17 DBMS_OUTPUT.PUT_LINE
18 (V_FIRST || ’ ’ || V_LAST || ’ ’ || V_SAL);
19 END LOOP;
20 DBMS_OUTPUT.PUT_LINE
21 (EMPLOYEE_CUR%ROWCOUNT || ’ EMPLOYEE(S) FOUND’);
22 END;
23 /
Alex McCall 66500
Derek Dev 80000
2 EMPLOYEE(S) FOUND

PL/SQL procedure successfully completed.

SQL>

Figure 12-3 Cursor attributes.

%FOUND

The %FOUND attribute returns a TRUE if the last FETCH returned a row; other-
wise, it returns a FALSE. For example, Figure 12-3 shows a block segment that exits
the loop if a row is not found. The loop continues as long as a row is fetched.

%NOTFOUND

The %NOTFOUND attribute returns a TRUE if the last FETCH did not return a
row; otherwise, it returns a FALSE. It is the opposite of the %FOUND attribute.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 274

274 Chap. 12 PL/SQL Cursors and Exceptions

The statement
EXIT WHEN NOT employee_cur%FOUND;

can be written as
EXIT WHEN employee_cur%NOTFOUND;

%ROWCOUNT

When a cursor is opened and no fetch is done from it, %ROWCOUNT is equal to
zero. With every fetch, %ROWCOUNT is incremented by one. The cursor must be
open before using %ROWCOUNT on it. For example, the code in Figure 12-3 goes
through the loop as long as a row is fetched. A count of the number of rows fetched
is kept by the PL/SQL engine. In the code, we are printing the total number of rows
fetched by the cursor at the end.

IMPLICIT CURSOR ATTRIBUTES

An implicit cursor cannot be opened, fetched from, or closed with a statement. You
do not name implicit cursors. The cursor attributes are available for an implicit cur-
sor with the name SQL as a prefix. The four attributes for a implicit cursor are:

1. SQL%ISOPEN.
2. SQL%ROWCOUNT.
3. SQL%NOTFOUND.
4. SQL%FOUND.

If an implicit cursor is not open, SQL%ROWCOUNT will return NULL. Sim-

ilarly, the other three attributes will return FALSE. You will never get an IN-
VALID_CURSOR error for an implicit cursor. The %ISOPEN attribute will always
return FALSE, because it is open only during the statement’s execution. It is opened
and closed implicitly. When a SELECT statement returns either no or more than
one row, the NO_DATA_FOUND or TOO_MANY_ROWS exception, respective-
ly, is raised. The cursor attribute SQL applies to the last SQL statement executed in
the block.

CURSOR FOR LOOPS

The cursor FOR loop is the easiest way to write a loop for explicit cursors. The cur-
sor is opened implicitly when the loop starts. A row is then fetched into the record
from the cursor with every iteration of the loop. The cursor is closed automatically
when the loop ends, and the loop ends when there are no more rows. The cursor
ShahCh12v3.qxd 4/16/04 12:01 PM Page 275

Cursor FOR Loops 275

FOR loop automates all the cursor actions. The general syntax is

FOR recordname IN cursorname LOOP

Loop statements;
...
END LOOP;

where recordname is the name of the record that is declared implicitly in the loop
and is destroyed when the loop ends and cursorname is the name of declared ex-
plicit cursor.
Figure 12-4 uses a Cursor FOR loop with a record. When the loop starts, the
cursor is opened implicitly. During the loop execution, an implicit fetch retrieves a
row into the record for processing with each loop iteration. When an implicit fetch
cannot retrieve a row, the cursor is closed, and the loop terminates. The OPEN,
FETCH, and CLOSE statements are missing, because these operations are per-
formed implicitly. The record’s columns are addressed with recordname.columnname
notation. If the record is accessed after the END LOOP statement, it will throw an
exception, because the record’s scope is only within the loop body.

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 CURSOR EMPLOYEE_CUR IS
3 SELECT LNAME, FNAME, SALARY
4 FROM EMPLOYEE;
5 BEGIN
6 FOR EMP_REC IN EMPLOYEE_CUR LOOP
7 IF EMP_REC.SALARY > 75000 THEN
8 DBMS_OUTPUT.PUT(EMP_REC.FNAME || ’ ’);
9 DBMS_OUTPUT.PUT(EMP_REC.LNAME || ’ ’);
10 DBMS_OUTPUT.PUT_LINE(EMP_REC.SALARY || ’ ’);
11 END IF;
12 END LOOP;
13 END;
14 /
John Smith 265000
Larry Houston 150000
Derek Dev 80000

PL/SQL procedure successfully completed.

SQL>

Figure 12-4 Cursor FOR Loop.

ShahCh12v3.qxd 4/16/04 12:01 PM Page 276

276 Chap. 12 PL/SQL Cursors and Exceptions

Cursor FOR Loop Using a Subquery

Use of a subquery in the cursor FOR loop eliminates declaration of an explicit cursor.
The cursor is created by a subquery in the FOR loop statement itself. In Figure 12-5,
an explicit cursor is used with implicit actions. One thing that is missing is the cursor
name. The cursor declaration is not necessary, because it is created through the sub-
query. This subquery is similar to the inline view covered in the SQL section of this
text.

SQL> BEGIN
2 FOR EMP_REC IN
3 (SELECT FNAME, LNAME, SALARY, COMMISSION
4 FROM EMPLOYEE
5 WHERE DEPTID = 10) LOOP
6 DBMS_OUTPUT.PUT_LINE
7 (EMP_REC.FNAME || ’ ’ || EMP_REC.LNAME || ’ $’ ||
8 TO_CHAR(EMP_REC.SALARY + NVL(EMP_REC.COMMISSION, 0)));
9 END LOOP;
10 END;
11 /
John Smith $300000
Sandi Roberts $75000
Sunny Chen $35000

PL/SQL procedure successfully completed.

SQL>

Figure 12-5 Cursor FOR loop with a subquery.

SELECT . . . FOR UPDATE CURSOR

When you type a SELECT query, the result is returned to you without locking any
rows in the table. Row locking is kept to a minimum. You can explicitly lock rows for
update before changing them in the program. The FOR UPDATE clause is used
with the SELECT query for row locking. The locked rows are not available to other
users for DML statements until you release them with COMMIT or ROLLBACK
commands. Rows that are locked for update do not have to be updated. The general
syntax is

CURSOR cursorname IS
SELECT columnnames
FROM tablename(s)
[WHERE condition]
FOR UPDATE [OF columnnames] [NOWAIT];
ShahCh12v3.qxd 4/16/04 12:01 PM Page 277

Cursor with Parameters 277

The optional part of a FOR UPDATE clause is OF columnnames, which en-

ables you to specify columns to be updated. You can actually update any column in
a locked row. The optional word NOWAIT tells you right away if another user has
already locked the table and lets you continue with other tasks. If you do not use
NOWAIT and one or more rows are already locked by another user, you will have
to wait until the lock is released. If you have granted UPDATE privilege to another
user on your table, that user can prevent you from performing DML operations on
your own table by locking them indefinitely!

WHERE CURRENT OF CLAUSE

In a cursor, data manipulation in the form of UPDATE or DELETE is performed

on rows fetched. The WHERE CURRENT OF clause allows you to perform data
manipulation only on a recently fetched row. The general syntax is

UPDATE tablename
SET clause
WHERE CURRENT OF cursorname;
_______________________________
DELETE FROM tablename
WHERE CURRENT OF cursorname;

You do not have to use a separate WHERE condition. The WHERE CURRENT
OF clause references the cursor, and changes apply to only the last fetched row.

CURSOR WITH PARAMETERS

A cursor can be declared with parameters, which allow you to pass values to the cur-
sor. These values are passed to the cursor when it is opened, and they are used in the
query when it is executed. With the use of parameters, you can open and close a cur-
sor many times with different values. The cursor with different values will then re-
turn different active sets each time it is opened. When parameters are passed, you
need not worry about the scope of variables. The general syntax is

CURSOR cursorname
[(parameter1 datatype, parameter2 datatype, . . .)]
IS
SELECT query;

where parameter1, parameter2, and so on are formal parameters passed to the cursor
and datatype is any scalar data type assigned to the parameter. The parameters are
assigned only data types; they are not assigned size.
When a cursor is opened, values are passed to the cursor. Each value must
match the positional order of the parameters in a cursor’s declaration. The values
ShahCh12v3.qxd 4/16/04 12:01 PM Page 278

278 Chap. 12 PL/SQL Cursors and Exceptions

can be passed through literals, PL/SQL variables, or bind variables. The parameters
in a cursor are passed in to the cursor, but you cannot pass any value out of the cur-
sor through parameters.
For example, in the PL/SQL program of Figure 12-6, the cursor employee_cur
is declared with a parameter dept_num, which is also used in the cursor SELECT
statement’s WHERE clause. When the program executes, it asks to input a value for
department number with substitution variable DEPARTMENT_ID, which is as-
signed to variable D_ID. The cursor is opened with parameter D_ID, which has
value of 10 as entered by the user. The format parameter DEPT_NUM gets value of
parameter D_ID. The active set is created based on DEPTID = DEPT_NUM.
Then, the cursor loop prints all employees for department number 10. The parameter
can be passed a value with a literal (as done in here), a bind variable, or an expression.

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 V_FIRST EMPLOYEE.FNAME%TYPE;
3 V_LAST EMPLOYEE.LNAME%TYPE;
4 D_ID NUMBER(2) := &DEPARTMENT_ID;
5 CURSOR EMPLOYEE_CUR (DEPT_NUM EMPLOYEE.DEPTID%TYPE) IS
6 SELECT LNAME, FNAME
7 FROM EMPLOYEE
8 WHERE DEPTID = DEPT_NUM;
9
10 BEGIN
11 OPEN EMPLOYEE_CUR(D_ID);
12 DBMS_OUTPUT.PUT_LINE
13 (’EMPLOYEES IN DEPARTMENT ’ || TO_CHAR(D_ID));
14 LOOP
15 FETCH EMPLOYEE_CUR INTO V_LAST, V_FIRST;
16 EXIT WHEN EMPLOYEE_CUR%NOTFOUND;
17 DBMS_OUTPUT.PUT_LINE(V_LAST || ’, ’ || V_FIRST);
18 END LOOP;
19 CLOSE EMPLOYEE_CUR;
20 END;
21 /
Enter value for department_id: 10
EMPLOYEES IN DEPARTMENT 10
Smith, John
Roberts, Sandi
Chen, Sunny

PL/SQL procedure successfully completed.

SQL>

Figure 12-6 Cursor with Parameter.

ShahCh12v3.qxd 4/16/04 12:01 PM Page 279

Cursor Variables: An Introduction 279

A cursor with a parameter can be opened multiple times with a different parameter
value to get a different active set.
When you declare a cursor with one or more parameters, you can initialize it
to a default value as follows:
CURSOR employee_cur (dept_id employee.DeptId%TYPE := 99) IS

CURSOR VARIABLES: AN INTRODUCTION

An explicit cursor is the name of the work area for an active set. A cursor variable is
a reference to the work area. A cursor is based on one specific query, whereas a cur-
sor variable can be opened with different queries within a program. A static cursor
is like a constant, and a cursor variable is like a pointer to that cursor. You can also
use the action statements OPEN, FETCH, and CLOSE with cursor variables. The
cursor attributes %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT
are available for cursor variables. Cursor variables have many similarities with stat-
ic cursors.
The cursor variable has other capabilities in addition to the features of a static
cursor. It is a variable, so it can be used in an assignment statement. A cursor vari-
able can also be assigned to another cursor variable.

REF CURSOR Type

Two steps are involved in creating a cursor variable. First, you have to create a ref-
erenced cursor type. Second, you have to declare an actual cursor variable with the
referenced cursor type. The general syntax is

TYPE cursortypename IS REF CURSOR [RETURN returntype];

cursorvarname cursortypename;

where cursortypename is the name of the type of cursor. The RETURN clause is op-
tional. The returntype is the RETURN data type and can be any valid data structure,
such as a record or structure defined with %ROWTYPE. For example,

TYPE any_cursor_type IS REF CURSOR;

any_cursor_var any_cursor_type;
TYPE employee_cursor_type IS REF CURSOR
RETURN employee%ROWTYPE;
employee_cursor_var employee_cursor_type;

In this example, the first cursor type, any_cursor_type, is called the weak type,
because its RETURN clause is missing. This type of cursor type can be used with
any query. The cursor type declared with the RETURN clause is called the strong
type, because it links a row type to the cursor type at the declaration time.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 280

280 Chap. 12 PL/SQL Cursors and Exceptions

Opening a Cursor Variable

You assign a cursor to the cursor variable when you OPEN it. The general syntax is
OPEN cursorname / cursorvarname FOR SELECT query;

If the cursor type is declared with the RETURN clause, the structure from the SE-
LECT query must match the structure specified in the REF CURSOR declaration.
For example,
OPEN employee_cursor_var FOR SELECT * FROM employee;

The structure returned by the SELECT query matches the RETURN type employ-
ee%ROWTYPE.
The other cursor type, any_cursor_type, is declared without the RETURN
clause. It can be opened without any worry about matching the query’s result to
anything. The weak type is more flexible than the strong type, but there is no error
checking. Let us look at some OPEN statements for the weak cursor variable:
OPEN any_cursor_var FOR SELECT * FROM dept;
OPEN any_cursor_var FOR SELECT * FROM employee;
OPEN any_cursor_var FOR SELECT DeptId FROM dept;

It is possible to have all three statements in one program block. The cursor variable
assumes different structures with each OPEN.

Fetching from a Cursor Variable

The fetching action is same as that of a cursor. The compiler checks the data struc-
ture type after the INTO clause to see if it matches the query linked to the cursor.
The general syntax is
FETCH cursorvarname INTO recordname / variablelist;

(Note: At the end of this chapter, under More Sample Programs, are three more
coding examples on cursors.)

EXCEPTIONS

In PL/SQL, errors are known as exceptions. An exception occurs when an unwanted

situation arises during the execution of a program. Exceptions can result from a sys-
tem error, a user error, or an application error. When an exception occurs, control of
the current program block shifts to another section of the program, known as the
exception section, to handle exceptions. If the exception handler exists, it is per-
formed. If the exception handler does not exist in the current block, control propa-
gates to the outer blocks. If the handler is not in any of the blocks, PL/SQL returns
an error, and the script stops.
A programmer writes a program to perform certain tasks, keeping only the pos-
itive things in mind. Programming is more than just writing statements to perform a
ShahCh12v3.qxd 4/16/04 12:01 PM Page 281

Types of Exceptions 281

task, however. A programmer must think of all the negative situations that may arise
while the program is executed. For example, the system might run out of memory, the
database might not be accessible, or the user might type in the wrong value or press
the wrong key. The programmer must put extra effort into program design to remove
bugs and make the program error-proof with additional code to perform in case of
exceptions. PL/SQL provides ways to trap and handle errors, and it is possible to cre-
ate PL/SQL programs with full protection against errors. When exception-handling
code is written for an exception, that exception can occur anywhere in the block, and
the same handler can deal with it.
The syntax of an anonymous block is given below. Control transfers from the
execution section to the exception section. PL/SQL browses through the section to
look for the handler. If the handler is present, it is executed. The program may have
more than one exception handler, written with WHEN . . . THEN statements like an
ELSIF or CASE structure (as supported by Oracle9i). For example,

DECLARE
Declaration of constants, variables, cursors, and exceptions
BEGIN
/* Exception is raised here.*/
EXCEPTION
/* Exception is trapped here.*/
END;

The general syntax of an exception section is:

EXCEPTION
WHEN exceptionname1 [OR exceptionname2, . . .] THEN
Executable statements
[WHEN exceptionname3 [OR exceptionname4, . . .] THEN
Executable statements]
[WHEN OTHERS THEN
Executable statements]

An exception is handled when the exception name matches the name of the
raised exception. The exceptions are trapped by name. If an exception is raised but
no handler for it is present, the WHEN OTHERS clause is performed (if present). If
there is no handler for an exception and no WHEN OTHERS clause, the error
number and associated message are displayed to the user.

TYPES OF EXCEPTIONS

There are three types of exceptions in PL/SQL:

1. Predefined Oracle server exceptions are exceptions that are named by

PL/SQL and are raised implicitly when a PL/SQL or DBMS error occurs.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 282

282 Chap. 12 PL/SQL Cursors and Exceptions

There are approximately 20 such exceptions. Each has a name and associ-
ated error number.
2. Nonpredefined Oracle server exceptions are standard Oracle server er-
rors that are not named by the system. They can be declared in the decla-
ration section but are raised implicitly by the server. These exceptions do
not have a name, but they do have an associated error number.
3. User-defined exceptions are exceptions that are declared in the declara-
tion section and are raised by the user explicitly. The user decides which
abnormal condition is an exception. The Oracle server does not consider
these conditions to be errors.

Predefined Oracle Server Exceptions

Exceptions that are given names by PL/SQL are declared in a PL/SQL package
called STANDARD. The exception-handling routine is also defined there. The user
does not have to declare or raise predefined server exceptions. Figure 12-7 provides
the exception name, the error code returned by the built-in function SQLCODE,
and a brief description of the exception.

Exception Name Error Number Brief Description

NO_DATA_FOUND ORA-01403 Single-row SELECT returned no data.

TOO_MANY_ROWS ORA-01422 Single-row SELECT returned more than one row.
ZERO_DIVIDE ORA-01476 Attempted to divide by zero.
VALUE_ERROR ORA-06502 Arithmetic, conversion, truncation, or size constraint
error occurred.
STORAGE_ERROR ORA-06500 PL/SQL ran out of memory, or memory is corrupted.
LOGIN_DENIED ORA-01017 Logging on to Oracle with an invalid username or
password.
NOT_LOGGED_ON ORA-01012 PL/SQL program issues a database call without
being connected to Oracle.
PROGRAM_ERROR ORA-06501 PL/SQL has an internal problem.
ACCESS_INTO_NULL ORA-06530 Attempted to assign values to the attributes of an
uninitialized object.
CURSOR_ALREADY_OPEN ORA-06511 Attempted to open an already-open cursor.
DUP_VAL_ON_INDEX ORA-00001 Attempted to insert a duplicate value.
INVALID_CURSOR ORA-01001 Illegal cursor operation occurred.
INVALID_NUMBER ORA-01722 Conversion of a character string to number failed.
ROWTYPE_MISMATCH ORA-06504 Host cursor variable and PL/SQL cursor variable
involved in an assignment have incompatible
return types.
TIMEOUT_ON_RESOURCE ORA-00051 Time-out occurred while Oracle is waiting
for a resource.

Figure 12-7 Predefined/named system exceptions.

ShahCh12v3.qxd 4/16/04 12:01 PM Page 283

Types of Exceptions 283

Suppose a program block generates an error message for exception error

number ORA-01403 that is not handled by the exception section. The error has oc-
curred because of a SELECT statement that did not return any data. You can write
an exception handler as shown in Figure 12-8.

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 V_FIRST EMPLOYEE.FNAME%TYPE;
3 V_LAST EMPLOYEE.LNAME%TYPE;
4 D_ID NUMBER(2) := &DEPARTMENT_ID;
5 BEGIN
6 SELECT LNAME, FNAME
7 INTO V_LAST, V_FIRST
8 FROM EMPLOYEE
9 WHERE DEPTID = D_ID;
10 DBMS_OUTPUT.PUT_LINE(’ ’);
11 DBMS_OUTPUT.PUT_LINE(V_LAST || ’, ’ || V_FIRST);
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 DBMS_OUTPUT.PUT_LINE
15 (’NO SUCH DEPARTMENT WITH EMPLOYEES’);
16 WHEN TOO_MANY_ROWS THEN
17 DBMS_OUTPUT.PUT_LINE
18 (’MORE THAN ONE EMPLOYEE IN DEPT ’ || D_ID);
19 END;
20 /

Figure 12-8 Handling named exceptions (source).

In Figure 12-8, two named exceptions, NO_DATA_FOUND and

TOO_MANY_ROWS, are handled. The NO_DATA_FOUND exception occurs
when a SELECT . . . INTO statement does not retrieve a row. The TOO_MANY_
ROWS exception occurs when a SELECT . . . INTO statement retrieves more than
one row. Figure 12-9 shows execution of the code in Figure 12-8 to demonstrate han-
dling of both exceptions. Input value 10 returned more than one row, resulting in the
TOO_MANY_ROWS exception, which is raised implicitly and handled. Input
value 50 returned 0 rows, resulting in the NO_DATA_FOUND exception, which is
also raised implicitly and handled by the block. Input value 40 returned one em-
ployee, so no exception was thrown.

Nonpredefined Oracle Server Exceptions

A nonpredefined Oracle server exception has an attached Oracle error code, but it is
not named by Oracle. You can trap such exceptions with a WHEN OTHERS clause
or by declaring them with names in the DECLARE section. The declared exception
ShahCh12v3.qxd 4/16/04 12:01 PM Page 284

284 Chap. 12 PL/SQL Cursors and Exceptions

Enter value for department_id: 10

MORE THAN ONE EMPLOYEE IN DEPT 10

PL/SQL procedure successfully completed.

SQL> /
Enter value for department_id: 50
NO SUCH DEPARTMENT WITH EMPLOYEES

PL/SQL procedure successfully completed.

SQL> /
Enter value for department_id: 40
Houston, Larry

PL/SQL procedure successfully completed.

SQL>

Figure 12-9 Handling named exceptions (output).

is raised implicitly by Oracle, or you can raise it explicitly. You can write exception-
handler code for it.

Pragma Exception_Init. PRAGMA is a compiler directive that associates

an exception name with an internal Oracle error code. The PRAGMA directive is
not processed with the execution of a PL/SQL block, but it directs the PL/SQL com-
piler to associate a name with the error code. You can use more than one PRAGMA
EXCEPTION_INIT directive in your DECLARE section to assign names to differ-
ent error codes. You may even assign more than one name to the same error number.
Naming an internal error code makes your program more readable.
Naming and associating are two separate statements in the declaration sec-
tion. First, an exception name is declared as an EXCEPTION. Second, the declared
name is associated with an internal error code returned by SQLCODE with the
PRAGMA directive. The general syntax is

exceptionname EXCEPTION;
PRAGMA EXCEPTION_INIT (exceptionname, errornumber);

where exceptionname is user supplied and errornumber is Oracle’s internal error

code. The error code is a numeric literal with a negative sign 1- 2.
Suppose you tried to remove a department from the DEPT table but the child
rows still exist in the EMPLOYEE table, because there are employees with that
DeptId. You will get Oracle error ORA-02292. You can declare an exception and as-
sociate it with the server error code number -2292. Figure 12-10 shows a PL/SQL
block with a declaration and exception trapping of a nonpredefined Oracle excep-
tion. There is no explicit RAISE statement.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 285

Types of Exceptions 285

SQL> DECLARE
2 emp_remain EXCEPTION;
3 PRAGMA EXCEPTION_INIT (emp_remain, -2292);
4 v_deptid dept.DeptId%TYPE := &p_deptnum;
5 BEGIN
6 DELETE FROM dept
7 WHERE DeptId = v_deptid;
8 COMMIT;
9 EXCEPTION
10 WHEN emp_remain THEN
11 DBMS_OUTPUT.PUT(’DEPARTMENT ’ || TO_CHAR(v_deptid));
12 DBMS_OUTPUT.PUT(’ cannot be removed - ’);
13 DBMS_OUTPUT.PUT_LINE(’Employees in department’);
14 END;
15 /
Enter value for p_deptnum: 10
DEPARTMENT 10 cannot be removed - Employees in department

PL/SQL procedure successfully completed.

SQL> /
Enter value for p_deptnum: 60

PL/SQL procedure successfully completed.

SQL>

Figure 12-10 Nonpredefined Oracle exception.

Exception-Trapping functions. When an exception occurs in your program,

you don’t know the error code for the error and its associated message unless you
take specific action to identify them. Once you know the error code and the mes-
sage, you can modify your program to take action based on the error. The two func-
tions to identify the error code and error message are:

1. SQLCODE. The SQLCODE function returns a negative error code num-

ber. The number can be assigned to a variable of NUMBER type.
2. SQLERRM. The SQLERRM function returns the error message associat-
ed with the error code. The maximum length of error message is 512 bytes.
It can be assigned to a VARCHAR2-type variable.

Figure 12-11 shows the use of SQLCODE and SQLERRM to identify the
error code and message for further modifications of the exception section of a pro-
gram based on information displayed.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 286

286 Chap. 12 PL/SQL Cursors and Exceptions

SQL> DECLARE
2 V_FIRST EMPLOYEE.FNAME%TYPE;
3 V_LAST EMPLOYEE.LNAME%TYPE;
4 D_ID NUMBER(2) := &DEPARTMENT_ID;
5 V_CODE NUMBER;
6 V_MSG VARCHAR2(255);
7 BEGIN
8 SELECT LNAME, FNAME
9 INTO V_LAST, V_FIRST
10 FROM EMPLOYEE
11 WHERE DEPTID = D_ID;
12 DBMS_OUTPUT.PUT_LINE(’ ’);
13 DBMS_OUTPUT.PUT_LINE(V_LAST || ’, ’ || V_FIRST);
14 EXCEPTION
15 WHEN OTHERS THEN
16 V_CODE := SQLCODE;
17 V_MSG := SQLERRM;
18 DBMS_OUTPUT.PUT_LINE(’ERROR CODE: ’ || SQLCODE);
19 DBMS_OUTPUT.PUT_LINE(SQLERRM);
20 END;
21 /
Enter value for department_id: 10
ERROR CODE: -1422
ORA-01422: exact fetch returns more than requested number of rows

PL/SQL procedure successfully completed.

SQL>

Figure 12-11 SQLCODE and SQLERRM.

User-Defined Exceptions

The standard errors covered under the previous two types are in the STANDARD
package with an error code and an accompanying message. Often, however, you will
encounter situations that are specific to a given program. For example, a birth date
falls in the future, a quantity in an invoice is negative, a student registers for course
without satisfying prerequisite, and so on.
You are allowed to define your exceptions in PL/SQL. You must perform
three steps for exceptions you want to define:

1. You must declare the exception in the DECLARE section. There is no

need to use a PRAGMA directive, because there is no standard error
number to associate.
2. You must raise the exception in the execution section of the program with
an explicit RAISE statement.
3. You must write the handler for the exception.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 287

Types of Exceptions 287

Figures 12-12 and 12-13 are examples of the user-defined exceptions

invalid_commission and no_commission. The invalid_commission exception is
raised when the commission value is negative. The no_commission exception is
raised when the commission value is NULL. Figure 12-12 shows the source code,
and Figure 12-13 shows exception handling based on the EmployeeId entered by
the user.

SQL> DECLARE
2 invalid_commission EXCEPTION;
3 no_commission EXCEPTION;
4 v_comm employee.Commission%TYPE;
5 BEGIN
6 SELECT Commission
7 INTO v_comm
8 FROM employee
9 WHERE EmployeeId = &emp_id;
10 IF v_comm < 0 then
11 RAISE invalid_commission;
12 ELSIF v_comm IS NULL THEN
13 RAISE no_commission;
14 ELSE
15 DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_comm));
16 END IF;
17 EXCEPTION
18 WHEN invalid_commission THEN
19 DBMS_OUTPUT.PUT_LINE(’Commission is negative.’);
20 WHEN no_commission THEN
21 DBMS_OUTPUT.PUT_LINE(’No commission value’);
22 WHEN OTHERS THEN
23 DBMS_OUTPUT.PUT_LINE(’No such ID’);
24 END;
25 /

Figure 12-12 User-defined exception (source).

RAISE_APPLICATION_ERROR Procedure

The RAISE_APPLICATION_ERROR procedure allows you to display nonstan-

dard error codes and user-defined error messages from a stored subprogram. The
general syntax is
RAISE_APPLICATION_ERROR (error_code, error_message [, TRUE/FALSE];

where the error_code is a user-specified number between - 20,000 and - 20,999 and
error_message is a user-supplied message that can be up to 512 bytes long. The third
Boolean parameter, TRUE/FALSE, is optional. TRUE means “place the error on
ShahCh12v3.qxd 4/16/04 12:01 PM Page 288

288 Chap. 12 PL/SQL Cursors and Exceptions

Enter value for emp_id: 111

35000

PL/SQL procedure successfully completed.

SQL> /
Enter value for emp_id: 123
No commission value

PL/SQL procedure successfully completed.

SQL> /
Enter value for emp_id: 546
Commission is negative.

PL/SQL procedure successfully completed.

SQL> /
Enter value for emp_id: 321
No such ID

PL/SQL procedure successfully completed.

SQL>

Figure 12-13 User-defined exception (output).

stack of other errors.” FALSE is the default value, and it replaces all previous errors.
For example,

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR
(–20001, ‘Department does not exist’);

You can use a RAISE_APPLICATION_ERROR procedure in the execution

and exception sections of the program. It is very useful in communicating errors be-
tween the client and the server.
In a PL/SQL program with an anonymous block that has nested blocks as well
as procedures and functions, the outermost block nests other blocks and calls the
procedures and functions. Each block can have its own exception-handling section,
and some blocks may not have an exception-handling section. An exception de-
clared in the inner block cannot be raised in the enclosing outer block. If an excep-
tion is declared in the outer block, it can be raised in the block itself or in its inner
subblock. When the exception is raised implicitly or explicitly in an inner block with-
out the exception-handling section, control shifts to the adjacent outer block and
then propagates outward until its handler is found or it ends up being an unhandled
exception.
ShahCh12v3.qxd 4/16/04 12:01 PM Page 289

In a Nutshell . . . 289

The RAISE statement is very much like the GOTO statement. They both
branch to another part of the program. The difference is that the RAISE statement
branches to the exception section, whereas the GOTO statement branches to an-
other statement in an executable block.

MORE SAMPLE PROGRAMS

In this section, you will see the PL/SQL blocks based on the topics covered in this
chapter, such as an explicit cursor, a cursor FOR loop, a cursor with parameters,
and exception handling. The code in Figure 12-14 uses an explicit cursor emp_cur.
The active set contains the employee’s last name, first name, salary, and commis-
sion. The WHILE loop is used to work with one row at a time. Within the loop, an
employee’s salary and commission are added together to find the total income.
Also, note the use of a single-row function NVL in case the commission value is
NULL. Finally, total company wages (the total of all employee salaries and com-
missions) are printed.
When the program in Figure 12-15 is executed, you will be prompted to enter
the date for the substitution variable p_date. When the cursor is opened with v_date
as a parameter, it will retrieve rows that have HireDate after the inputted date. The
information for those employees will be printed. The program also will display the
total number of employees selected.
The program in Figure 12-16 selects employees with PositionId of 2 who are
managers. It locks those rows for future update. Using a cursor FOR loop, each
manager’s salary is modified to give a 7% raise. The WHERE CURRENT OF
clause is used to modify the current row fetched. The rows are released with the
COMMIT command.
The program in Figure 12-17 displays two customized prompts for an employ-
ee’s ID and the percentage increment/raise. First, rows are locked with the FOR
UPDATE clause. The UPDATE statement changes the salary if the ID is correct. If
the employee ID does not exist, a standard exception is raised implicitly. The excep-
tion is handled by displaying an appropriate message.

IN A NUTSHELL . . .

● A cursor is a private work area to store a statement and its active set.
● A static cursor’s contents are known at compile time, and a dynamic cursor
uses a cursor variable, which can refer to different SQL statements at differ-
ent times.
● An implicit cursor is declared, managed, and closed by PL/SQL.
● The programmer declares an explicit cursor for a PL/SQL block that returns
more than one row from the table.