Chapter 5

Introducing PL/SQL

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

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

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

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

• Using PL/SQL to access Oracle
• Variable value assignment

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

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

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

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

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

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

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

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

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

FUNCTION convert_money
(amount IN NUMBER,
from_currency IN VARCHAR2,
to_currency IN VARCHAR2
) IS /* denotes the beginning of the declaration section. */
my_new_amt number(10) := 0;
bad_data exception;
BEGIN /* begins the executable section of a code block. */
IF my_new_amt > 3 THEN
. . .
ELSE
. . .
END IF;
EXCEPTION /*Begins the Exception Handler */
WHEN bad_data THEN
DBMS_OUTPUT.PUT_LINE('Error condition');
END;

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

DECLARE /* begins the declaration section in an anonymous block */
my_convert_amt NUMBER(10);
my_convert_currency VARCHAR2(5);
my_old_currency VARCHAR2(5);
bad_data EXCEPTION;
BEGIN /* begins the executable section of a code block. */
IF my_convert_amt=6 THEN
. . .
ELSE
. . .
END IF;
EXCEPTION /*Begins the Exception Handler */
WHEN bad_data THEN
DBMS._OUTPUT.PUT_LINE('Error condition');
END;

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

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

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

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

• Declaring and using variables
• Variable value assignment

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

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

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

• NUMBER(size[,precision]) Used to store any number.
• CHAR(size), VARCHAR2(size) Used to store alphanumeric text strings. The CHAR datatype pads the value stored to the full length of the variable with blanks.
• DATE Used to store dates.
• LONG Stores large blocks of text, up to 2 gigabytes in length.
• LONG RAW Stores large blocks of data stored in binary format.
• RAW Stores smaller blocks of data stored in binary format.
• MLSLABEL Used in Trusted Oracle.
• ROWID Used to store the special format of ROWIDs on the database.
• BLOB, CLOB, NCLOB, BFILE Large object datatypes from Oracle8.

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

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

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

DECLARE
my_employee_id employee.empid%TYPE;
BEGIN …

DECLARE
my_salary employee.salary%TYPE := 0;
my_lastname employee.lastname%TYPE:= 'SMITH';
BEGIN …

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

DECLARE
my_employee employee%ROWTYPE;
BEGIN …

or manually:

DECLARE
TYPE t_employee IS RECORD (
my_empid employee.empid%TYPE,
my_lastname employee.lastname%TYPE,
my_firstname employee.firstname%TYPE,
my_salary employee.salary%TYPE);

my_employee t_employee;
BEGIN…

It may be useful for you to declare constants in the declaration section of the PL/SQL blocks developed as well. Constants make a good substitute for the use of hard-coded values, or "magic numbers." A magic value in programming is a value that is required to perform an operation or calculation but does not have any sort of meaning in the code block to help others identify why the value is there. Take, for example, a function that calculates the area of a circle, which is the number pi times radius squared. The number pi is well known to most people, but imagine if it was not, how difficult it would be to understand the reason for having the number 3.14159265358 in the middle of the function. Assume also that in the Oracle database there is a table called CIRCLE with a column called RADIUS, whose datatype you want to refer to in the function, as circle.radius%TYPE, shown here:

CREATE FUNCTION find_circle_area (
p_radius IN circle.radius%TYPE
) RETURN NUMBER IS
my_area number(10) := 0;
pi constant number(15,14) := 3.14159265358;
BEGIN
my_area := (p_radius*p_radius)* pi;
Return (my_area);
END;

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

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

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

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

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

Using a select statement in the sample PL/SQL code block shown shortly illustrates how seamless the integration of PL/SQL and the Oracle database is. Note that there are no special characters that must precede the PL/SQL variables in SQL statements. The one concession PL/SQL must make is the into clause, which places the return values from the select statement into the %rowtype record created in the declaration section. You are not forced to use %rowtype; a manual record declaration or even three stand-alone variables declared as the same datatypes for each of the columns you use in your select statement will suffice. Even so, the utility for declaring a complex record with %rowtype has already been proven to be more efficient than manual record declaration. The same ease of use can be seen in update statements. The use of the record.element notation to refer to the components of a record variable is illustrated as well. Using insert statements in PL/SQL is as straightforward as the other statements available in PL/SQL. The same is true for the insert statement. And, as one might expect, the usage of delete is as straightforward as the usage of other SQL statements in PL/SQL.

DECLARE
my_employee employee%ROWTYPE;
my_lastname VARCHAR2(30) := 'SAMSON';
my_firstname VARCHAR2(30) := 'DELILAH';
my_salary NUMBER(10) := 49500;
BEGIN
SELECT *
INTO my_employee
FROM employee
WHERE empid = 49594;

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

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

my_empid := 59495;

DELETE FROM employee
WHERE empid = my_empid;
END;

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

• %notfound Identifies whether the fetch executed on the cursor returned any row data. If no data returned, this attribute evaluates to TRUE; otherwise, it evaluates to FALSE.
• %rowcount Identifies the number of rows that were processed by this cursor. Returns a numeric value.
• %found Identifies whether the fetch executed on the cursor returned any row data. If no data returned, this attribute evaluates to TRUE; otherwise, it evaluates to FALSE.
• %isopen Identifies whether the cursor referred to is opened and ready for use. Returns a TRUE if cursor is open, and FALSE if cursor is not.

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

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

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

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

• Conditional statements and process flow
• Using loops

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

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

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

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

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

BEGIN
IF TO_CHAR(sysdate,'DAY') = 'TUESDAY' THEN
find_hypotenuse(56,45,my_hypotenuse);
ELSE
My_hypotenuse := derive_hypotenuse(56,45);
END IF;
END;

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

BEGIN
IF TO_CHAR(sysdate,'DAY') = 'TUESDAY' THEN
find_hypotenuse(56,45,my_hypotenuse);
ELSIF TO_CHAR(sysdate,'DAY') = 'THURSDAY' THEN
my_hypotenuse := derive_hypotenuse(56,45);
ELSE
my_hypotenuse := 0;
END IF;
END;

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

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

• Loop-exit statements, also called basic loops
• While-loop statements
• For-loop statements

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

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

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

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

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

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

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

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

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

Notice that the use of a for-loop statement made this code block even more elegant. No longer necessary is the statement that increments the my_leg variable, since the for-loop statement handles the incrementation activity automatically. There is another type of for-loop statement related to cursor handling that offers the same elegance and utility as the for-loop statement detailed in the previous code block. Its usage, as well as the more general usage of cursors, will be covered in the next section of this chapter. The following code block shows the previous anonymous PL/SQL block again, this time with the for-loop statement executing in reverse order. Notice that you don’t need to change the beginning and end digit value any differently, Oracle handles everything properly with the reverse keyword.

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

PL/SQL does not allow you to increment or decrement your counter in the for-loop statement by anything other than 1 natively. You can build this functionality with the use of a mod( ) function in your for-loop statement, as shown in this following code block:

DECLARE
My_leg NUMBER(10) := 0;
My_hypotenuse NUMBER(10) := 0;
BEGIN
FOR my_leg IN 1..25 LOOP
IF mod(my_leg,2) = 0 THEN
find_hypotenuse(my_leg,my_leg,my_hypotenuse);
END IF;
END LOOP;
END;

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

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

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

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

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

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

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

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

DECLARE
my_empid employee.empid%TYPE := 59694;
my_salary employee.salary%TYPE := 99000;
my_lastname employee.lastname%TYPE := 'RIDDINGS';
BEGIN
UPDATE employee
SET salary = my_salary
WHERE empid = my_empid;

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

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

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

DECLARE
/* extract from a salary review program */
high_pctinc constant number(10,5) := 1.20;
med_pctinc constant number(10,5) := 1.10;
low_pctinc constant number(10,5) := 1.05;
my_salary employee.salary%TYPE;
my_empid employee.empid%TYPE;
CURSOR employee_crsr IS
SELECT empid, salary
FROM employee;
BEGIN …

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

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

DECLARE
/* extract from a salary review program */
high_pctinc constant number(10,5) := 1.20;
med_pctinc constant number(10,5) := 1.10;
low_pctinc constant number(10,5) := 1.05;
my_salary employee.salary%TYPE;
my_empid employee.empid%TYPE;
CURSOR employee_crsr IS
SELECT empid, salary
FROM employee;
BEGIN
/* The following statement creates and */
/* executes the cursor in memory */
OPEN employee_crsr;

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

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

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

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

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

DECLARE
/* extract from a salary review program */
high_pctinc constant number(10,5) := 1.20;
med_pctinc constant number(10,5) := 1.10;
low_pctinc constant number(10,5) := 1.05;
TYPE t_emp IS RECORD (
t_salary employee.salary%TYPE,
t_empid employee.empid%TYPE);
my_emprec t_emp;
CURSOR employee_crsr(low_end in VARCHAR2, high_end in VARCHAR2) IS
SELECT empid, salary
FROM employee
WHERE substr(lastname,1,1) BETWEEN UPPER(low_end) AND UPPER(high_end);
BEGIN …

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

DECLARE
my_empid employee.empid%TYPE := 59694;
my_emp_record employee%ROWTYPE;
my_salary_null EXCEPTION;
BEGIN
SELECT * FROM employee
INTO my_emp_record
WHERE empid = my_empid;

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

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

The list of predefined exceptions is limited, and overall they really do nothing other than associate a named exception with an Oracle error. You can extend the list of exceptions associated with Oracle errors within your PL/SQL code with the use of pragma exception_init keywords. This statement is a compiler directive that allows the developer to declare the Oracle-numbered error to be associated with a named exception in the block. This usage allows the code to handle errors that it might not have handled previously, without requiring the developer to program an explicit raise statement for the exception. For example, assume that the developer is inserting data on the EMPLOYEE table, and this table defined a NOT NULL constraint on SALARY. Instead of allowing the PL/SQL block to terminate abnormally if an insert occurs that does not name a value for the SALARY column with an ORA-01400 error, the declaration of an exception allows the PL/SQL block to handle the error programmatically.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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