Lab 6 – Advanced PL/SQL Programming
SOLVING MORE COMPLEX PROBLEMS
In the last lecture you were introduced to PL/SQL and the unnamed or anonymous procedure blocks. These procedure blocks provide the database developer with the capability to build executable program units that can take some action on the actual data stored in your database. However, we were limited in what we could do. We were unable to for example:
- solve problems that required a decision or repeated steps,
- handle errors in a user friendly manner,
- operate separately on a single row returned from a SELECT execution, or
- execute a procedure with out entering the syntax into the PL/SQL editor.
This time we will explore techniques to solve these problems. By creating reusable modules and by implementing additional control structures, we will create program units that are more robust and flexible. This will allow us to better solve business problems.
First, the only procedural construct that we have explored is Sequential Structures. So far, all we do is simply processed one instruction after another. This construct can only be used to solve simple problems. In order to solve more complex problems, however, we need the ability to make decisions as well as repeat steps. To do this we will use Decision Control Structures to make decisions and the Iterative Control Structures to repeat steps.
Second, in order to handle errors in a more flexible and “user friendly” manner, we will implement Exception Handling. In addition to displaying more descriptive messages, the database designer can provide error handling routines. Programs can provide a way for the user to correct the problem instead of simply exiting the program.
Third, we will discover how to manipulate a single row of data contained in a data set. When executing a SELECT statement in PL/SQL, Oracle establishes a work area that contains the set of data. This work area containing the set of data is referred to as a cursor. A cursor can be thought of as a pointer to a table in the database buffer cache. By declaring a cursor, we can process the returned rows individually. Therefore, using this technique, we can examine and manipulate one row at a time.
Finally, the only was we could execute the anonymous procedural program unit was to enter the instructions (PL/SQL commands) directly into one of the PL/SQL editors or reference a file from within SQL*Plus. To keep the procedure, we stored the commands in a text (.txt) or SQL (.sql) file outside the database. This method, however, is not very practical and prone to error. We will discover how to store the program units in the database by creating named stored procedures.
Learner Outcomes
Management of Solution Development
By completely this lab, you will achieve a deep level of knowledge and comprehension of the disciplines used in the development of information system solutions. You will develop the ability to apply these disciplines to the solution of organizational and business problems. Specifically, after completing this lab, you will be able to:
- Design, construct, and maintain a database and various database objects using procedural language constructs.
- Design and implement a complete problem solution using current database technology (Oracle 11g Database)
To accomplish this you will:
- Implement decision control and iterative control structures
- Create and exception handling routines
- Implement record level processing in Oracle 11g using cursors
- Create and implement callable (stored) procedures in Oracle 11g
So, let’s get started!!!
1. Control Structures
We will extend our knowledge of procedures by using some of the more popular procedural constructs known as decision control structures and iterative control structures. These two families of constructs allow you to change the control flow of a program unit from purely sequential to one where you are in command of how the logic within the procedure flows.
Decision Control
The PL/SQL commands used to make decisions, that is, alter the order in with commands are executed are the IF/THEN, IF/THEN/ELSE and the IF/THEN/ELSIF.
- IF/THEN format
IFconditionTHEN
Commands that you want to execute if the condition is true;
END IF;
The IF portion of the command tests the condition. If the condition is true, control is transferred to the command that immediately follows the THEN. If the condition is false, control is transferred to the command following the END IF.
Here is an example of the IF/THEN command:
DECLARE
DISTANCEVAR NUMERIC (4) CONSTANT:= 500;
BEGIN
IFpolicyType = ‘HO’ AND
fireHydrantLoc > DISTANCEVAR THEN
DBMS_OUTPUT.PUT_LINE(‘Insured property must be within ’||
DISTANCEVAR ||’feet’);
END IF;
END;
- IF/THEN/ELSE format
IFconditionTHEN
Commands that you want to execute if the condition is true;
ELSE
Commands that you want to execute if the condition is false;
END IF;
The IF part of the command tests the condition. If the condition is true, control is transferred to the command that immediately following the THEN. If the condition is false, control is transferred to the command following the ELSE.
Here is an example of the IF/THEN/ELSE command:
DECLARE
DISTANCEVAR Numeric (4) CONSTANT := 500;
BEGIN
IFpolicyType = ‘HO’ AND
fireHydrantLoc > DISTANCEVAR
THEN
DBMS_OUTPUT.PUT_LINE(‘Insured property must be within ’|| DISTANCEVAR || ‘ feet of a fire hydrant’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Insured property is within the
acceptable distance to a fire hydrant);
END IF;
END;
- IF/THEN/ELSIF format
This decision control structure allows you to test for multiple conditions in the control string.
IFcondition1THEN
Commands that you want to execute if the condition1 is true;
ELSIF condition2 THEN
Commands that you want to execute if the new condition2 is true;
ELSE
Commands that you want to execute if the new condition2 is false;
END IF;
The IF portion of the command tests condition1. If condition1 is true, control is transferred to the command that immediately following the THEN. If the condition1 is false, control is transferred to the ELSIF command where condition2 is tested. If condition2 is true control is transferred to the command following the second THEN. If condition2 is false control is transferred to the command following the ELSE.
Here is an example of the IF/THEN/ELSIF command:
DECLARE
DISTANCEVAR Numeric (4) CONSTANT := 500;
BEGIN
IFpolicyType = ‘HO’ AND
fireHydrantLoc > DISTANCEVAR THEN
DBMS_OUTPUT.PUT_LINE(‘Insured property must be within ’||
DISTANCEVAR ||‘ feet of a fire hydrant’);
ELSIF policyType = ‘FO’ THEN
DBMS_OUTPUT.PUT_LINE
(‘Insured property does not require access to a fire hydrant);
ELSE
DBMS_OUTPUT.PUT_LINE
(‘Insured property has required access to a fire hydrant);
END IF;
END;
An alternative to the IF/THEN/ELSIF command would be to use the CASE structure which for the previous example would look like this:
DECLARE
DISTANCEVAR NUMERIC (4) CONSTANT:= 500;
BEGIN
CASE
WHENpolicyType = 'HO' AND fireHydrantLoc > DISTANCEVAR
THEN DBMS_OUTPUT.PUT_LINE ('Property must be within '||
DISTANCEVAR || ' feet of a fire hydrant');
WHENpolicyType = 'FO'
THEN DBMS_OUTPUT.PUT_LINE
('Insured property does not require access to a fire hydrant');
ELSE DBMS_OUTPUT.PUT_LINE
('Insured property has required access to a fire hydrant');
END CASE;
END;
Hands-On Examples
Vote for Pedro. Let’s write a program which asks for the number of votes for Pedro. When Pedro has more than 200 votes, he wins!
Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as vote-for-pedro.sql:
Run this program a couple of times entering different values each time. See if you can make Pedro win and lose the election with different values for vote_countvote
A better Vote for Pedro.Next, let’s write a program which is a little more descriptive with the narrative when Pedro wins or loses.
Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as a-better-vote-for-pedro.sql:
Iterative Control
Sometimes you need to execute a certain logic pattern or a series of commands many times in a row before doing something else. We call this process of managing iterative control, looping. There are two types of looping: pretest and posttest.
You would use pretest looping when you want to evaluate an “exit condition” (that is determining when to stop the looping process) before your commands are executed.
You would use posttest looping when you need to execute the commands in your loop at least once. There are five different looping control structures: LOOP/EXIT, LOOP/EXIT/WHEN, WHILE/LOOP, FOR/LOOP and CURSOR/FOR/LOOPS. For this lab we will discuss the first four. Cursor/For/Loops will be discussed later in the semester.
- LOOP/EXIT format. This can be either a pretest or a posttest loop depending on how you structure your statements.
LOOP
Commands you want to execute
IF exit condition THEN
EXIT;
END IF;
END LOOP;
The LOOP keyword signals the beginning of the loop process followed any commands you want executed. An IF/THEN control structure tests the loop’s exit condition. If the exit condition is true, control is transferred to the EXIT which signals the end of the looping process.
Here is an example of the LOOP/EXIT command:
DECLARE
deptTotalSalaryVarNumeric (7) := 0;
empSalaryVarNumeric (7) := 1000;
BEGIN
LOOP
deptTotalSalaryVar := deptTotalSalaryVar + empSalaryVar;
IFdeptTotalSalaryVar > 1000000 THEN
EXIT;
END IF;
END LOOP;
END;
- LOOP/EXIT/WHEN format. This can be either a pretest or a posttest loop depending on how you structure your statements.
LOOP
Commands you want to execute
EXIT WHEN condition;
END LOOP;
The LOOP keyword signals the beginning of the loop process followed any commands you want executed. WHEN control structure tests the loop’s exit condition. If the exit condition is true, control is transferred to the EXIT which signals the end of the looping process.
Here is an example of the LOOP/EXIT/WHEN command:
DECLARE
deptTotalSalaryVarNumeric (7) := 0;
empSalaryVarNumeric (7) := 1000;
BEGIN
LOOP
deptTotalSalaryVar := deptTotalSalaryVar + empSalaryVar;
EXIT WHEN deptTotalSalaryVar >1000000;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Dept Salary: ' || deptTotalSalaryVar);
END;
- WHILE/LOOP format. This is a pretest loop that evaluates the exit condition before any commands are executed.
WHILE exitcondition
LOOP
Commands you want to execute
END LOOP;
The WHILE evaluates the exit condition before the LOOP keyword signals the beginning of the loop process. If the exit condition is true control is transferred to the commands following the LOOP keyword. If the exit condition is false control is transferred to the END LOOP; which signals an exit from the looping process.
Here is an example of the WHILE/LOOP command:
DECLARE
vDeptTotalSalaryNumeric (7) := 0;
vEmpSalaryNumeric (7) := 1000;
BEGIN
WHILEvDeptTotalSalary <1000000
LOOP
vDeptTotalSalary := vDeptTotalSalary + vEmpSalary;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Dept Salary: ' || vDeptTotalSalary);
END;
- FOR LOOP format. This is a posttest loop that evaluates the exit condition after the commands are executed.
FOR counter_variableINstart_value ..end_value
LOOP
Commands you want to execute
END LOOP;
The FOR evaluates the exit condition before the LOOP keyword signals the beginning of the loop process. Control is transferred to the commands following the LOOP keyword. The loop increments the variable counter by one until it equals the end value. When the end value is reached, i.e. the exit condition, false control is transferred to the END LOOP; which signals an exit from the looping process.
Here is an example of the FOR LOOP command:
DECLARE
deptTotalSalaryVarNumeric (7) := 0;
BEGIN
FOR vLoopCount IN 1 .. 5
LOOP
deptTotalSalaryVar := deptTotalSalaryVar + &empSalarySV;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Dept Salary: ' || deptTotalSalaryVar);
END;
Hands-On Examples
Multiples of. Let’s write a program that uses a loop. This program will ask you for two numbers and generate multiples.
Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as multiples-of.sql:
Run this code a few times to better understand what it is doing. It should be fairly straightforward.
2. Exception Handling
So, now we can really solve complex business problems. We can execute one instruction after another (Sequential Control), make decisions (Decision Control), and even repeat steps (Iterative Control) if necessary. This is great as long as everything is perfect. That is, when executing a select query for a specific record with a Where clause, the record exists and any data requested from the user is entered in a valid format. These are only a few examples of potential errors. There exist various potential problems that could cause program units to not execute properly. For example, what would happen in the previous example if the data in the multiplefield was entered as an alphabetic such as ‘abc’ and not a valid numeric value? It just so happens that Oracle will display an error message similar to the one below and terminate the execution of the code.
There is no way to test for every possible combination of data that might be entered, the Oracle error message is not user friendly, and there is no way to recover. To solve this problem, Oracle has implemented a technique to except and handle errors (situations that should not occur) using an architecture referred to as exception handling.
The format of the exception handling architecture is:
BEGIN
EXCEPTION
END;
Control transfers to the EXCEPTION section when a defined exception occurs.
So, now let’s look at the previous example with an exception section added to trap a VALUE error.
Do This: Connect to SQL Developer as IST469 and open a new sql file. Type the following code into the query window as save it as multiples-of-with-exception-handling.sql:
Try to execute this code and at one of the prompts, enter something which is not a number, for example:
And you should see the following error message at execution:
This is a much nicer way to handle execution errors and there are a variety of predefined exceptions available to use such as NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE. You can find a table of predefined exception in Oracle’s “PL/SQLUser’s Guide and Reference”. A list of common predefined exceptions can also be located in the “Guide to Oracle 91” text.
Undefined Exceptions
There are errors that are not as common that could occur and they have not be assigned a name. There are exceptions that are generated by Oracle but they do not have “user friendly” name that can easily be tested for. When these errors occur, the error message displayed may make sense to someone who knows the database; but, the message would not make sense to an end user. You can handle these exception by defining them in the DECLARE section of your procedure. To do this you would:
- Declare an exception
- Associate the exception with the Oracle Error Number
DECLARE
exceptional_exEXCEPTION;
PRAGMA EXCEPTION_INIT (exceptional_ex, -#####);
BEGIN
Code goes here;
EXCEPTION
WHEN exceptional_exTHEN
do something here;
END;
Let’s look at an example. Suppose, we had a customer table that contained an attribute for the State two character abbreviation. We also have a state table and the State abbreviation code is a foreign key that references the State table. Now, suppose we have an unnamed stored procedure that inserts values into the table based on substitution variables. If we enter data and use a state abbreviation that does not have an entry in the State table, we would get a foreign key constraint violation.This is an Oracle – 02291 and the error message that would be displayed would look something like the following:
To avoid this problem we could create our own exception and let the complier know which Oracle error number to associate with this newly defined exception using the above format. The following PL/SQL unnamed procedure creates a NoStateFoundEX and will now throw an error that has a more meaningful message;
/* PROGRAM:Customer Insert
AUTHOR:Susan Dischiave
DATE:2/28/2006
PURPOSE:To insert a record into the customer table
*/
DECLARE
cIDVar CUSTOMER_T.Customer_ID%TYPE := &cIDSV;
cNameVar CUSTOMER_T.Customer_Name%TYPE := &cNameSV;
cStateVar CUSTOMER_T.Customer_State%TYPE := &cStateSV;
-- Declare an exception for No State Found
NoStateFoundEx EXCEPTION;
-- Associate the Oracle exception with the NOStateFound
-- use the Oracle error code for foreign key constraint error
PRAGMA EXCEPTION_INIT (NoStateFoundEx, -02291);
BEGIN
--
-- Insert values into the customer table
-- using the substitution variables
--
INSERT INTO Customer_T (Customer_ID, Customer_Name, Customer_State)
VALUES (cIDVar, cNameVar, cStateVar);
EXCEPTION
WHEN NoStateFoundExTHEN
DBMS_OUTPUT.PUT_LINE('There is no entry for state ' || cStateVar);
END;
Now the error message that is displayed is
The Oracle developers could not possibly have thought of all possible exceptions that you might want to capture. For example, a grade entry procedure for SyracuseUniversity might want to raise an exception if a grade value of Z was entered. An employer would probably not want an employee’s birth date to be greater than the current date. There are numerous custom exceptions that would cause inaccurate data in the database if they were not caught and handled properly. You can avoid this problem that could potentially result in anomalies by creating your own exceptions.
User-Defined Exceptions
Oracle has provided a simple method for users to implement their own custom, user-defined, exceptions. The steps involved are to:
- declare an exception variable using the EXCEPTION datatype,
- check for the error condition using a decision control structure,
- raise an exception condition using the RAISE command,
- transfer control to the exception handling section,
- and handle the exception.
The format is:
DECLARE
MY_ERROR_EX EXCEPTION;
BEGIN