The following are the more commonly used predefined exceptions that you can trap in the exception-handling section of your PL/SQL block:

• no_data_found—Singleton SELECT statement returned no data.
• too_many_rows—Singleton SELECT statement returned more than one row of data.
• invalid_cursor—Illegal cursor operation occurred.
• value_error—Arithmetic, conversion, or truncation error occurred.
• when others—Used when no other exception is explicitly listed.

Listing 9.6 offers an enhancement to the code in Listing 9.5. In this example, I added another exception to handle the case when no rows are returned from the database.

INPUT
Listing 9.6. Multiple-row SELECT command with several exception-handling routines.

DECLARE
v_emp_id      INTEGER;
BEGIN
SELECT  emp_id
into v_emp_id
from employee
WHERE emp_name = `Jack Richards';
exception
when no_data_found THEN
emp_name:='NO EMPLOYEE';
when too_many_rows THEN
emp_name:='TOO MANY EMPLOYEES FOUND';
END;

ANALYSIS In the example in Listing 9.6, one of several exceptions can be raised. An exception is raised when no rows are returned by the SELECT statement as well as when more than one row is returned by the SELECT statement.

The UPDATE and DELETE Statements

The final two SQL DML statements to cover are the UPDATE and the DELETE statements. You can use these in any PL/SQL block as necessary. The purpose of these commands is synonymous with their names. The UPDATE command enables the user to change the values of an existing row. The DELETE command provides the means to remove or delete a row from a table.

The Syntax for the UPDATE Command

UPDATE table_name
set (column_name = value)
WHERE statement

In this syntax, table_name is the table containing the row you want to update, column_name is the column you want to update, and the WHERE statement identifies the row in the table to be identified.

The Syntax for the DELETE Command

DELETE from table_name
WHERE statement

In this syntax, table_name is the table containing the row to be deleted, and the WHERE statement identifies the row to be deleted.

Refer to the Oracle SQL Language Reference Manual for a more comprehensive syntax diagram for these last two commands.

Methods of Declaring Variables

As you learned in earlier chapters of this book, all PL/SQL variables must be declared in advance of using them. Declaration of variables is accomplished in the declaration portions of the PL/SQL block, function, package, or procedure. When a variable is defined, Oracle allocates memory to hold the variable. This section of this chapter covers several of PL/SQL's methods of declaring variables, including the following:

• Variables based on database columns
• Record variables (which can be based on tables or based on cursor definitions)
• Declarations at the package level

Variables Based on Database Columns

Oracle provides a means to declare a variable and define its datatype based on other variables, constants, or even table columns. You use the %type attribute in variable declarations to accomplish this task. As you recall, the employee table that you created earlier in this chapter in Listing 9.1 has the following structure:

employee:
   emp_id         INTEGER
   emp_name       VARCHAR2(32)
   supervised_by  INTEGER
   pay_rate       NUMBER(9,2)
   pay_type       CHAR
   emp_dept_id    INTEGER

The %type attribute enables you to define a variable based on a table column datatype. For example, in the following variable declarations, you see the avg_rate declared with the %type attribute based on the pay_rate column in the employee table. PL/SQL will treat this declaration as if you explicitly declared avg_rate as NUMBER(9,2).

avg_rate  employee.pay_rate%type

The following are a few more examples of the %type attribute, showing variables based on other variables and constants:

dept_id       INTEGER;
sub_dept_id   dept_id%type;  -- datatype based on a variable

area_id  dept_id%type := 9141; -- used with an initialization clause

PL/SQL allows you to use the %type attribute in a nesting variable declaration. The following example illustrates several variables defined on earlier %type declarations in a nesting fashion:

dept_sales    INTEGER;

area_sales    dept_sales%type;

group_sales     area_sales%type;
regional_sales  area_sales%type;

corporate_sales  regional_sales%type;

The %type attribute gives you several advantages. First, you base a declaration on a table column without having to know definitively what that column's datatype is. In really large applications and large databases (a database with over 500 objects), it does become hard to remember all the datatypes, especially if they change frequently. Second, as the datatypes of the item you based your declaration on do change, then your variables will automatically be changed as well. This can reduce maintenance of your application.

Record Variables

A record is a collection of individual fields that represents a row in a table. These fields are unique and each has its own values. The record as a whole does not have value.

By using records, you can group like data into one structure and then manipulate this structure as one entity or logical unit. This helps reduce coding and keeps the code easier to maintain and understand.

You declare a record in the declaration portion of a PL/SQL block, subprogram, or package. The following example declares a record named emp_sales:

TYPE emp_sales_rec is record        --record declaration
(emp_id       INTEGER,
emp_name      VARCHAR2(32),
commision_rate      employee.pay_rate%type);

After a record is declared, you can reference the record members directly by using dot notation. In the following example, the last field in the record emp_sales_rec is referenced:

IF emp_sales_rec.commision_rate > .05 THEN...

You can also pass a record type variable to a procedure, as in the following example:

procedure monthly_commissions (emp_sales_rec);

Oracle also allows you to assign expressions to a record, as shown in the following example:

emp_sale_rec.commission_rate := .01;

Record Variables Based on Tables
A record type variable based on a table means that each field in the record has the exact same name and datatype as the columns in the specified table. The %rowtype attribute is used to facilitate the declaration of a record based on a table.

The %rowtype is similar to the %type. The %type refers to only a single variable, whereas the %rowtype refers to an entire table row. The following example of the %rowtype attribute declares the record emp_rec, which can store an entire row from the employee table:

DECLARE
employee_name    employee.emp_name%type;
emp_rec          employee%rowtype;

As you did with other records, you use dot notation to reference a specific field. The following code examines the emp_name from the emp_rec just as if the code was interrogating the actual employee record:

IF emp_rec.emp_name like ` Madison% `  THEN...

As with other variables, you can assign a value to the record field directly, as shown following:

IF emp_rec.emp_name like `Madison%'
THEN
emp_rec.pay_rate = 3000.00);

Records based on tables can also be used in a SELECT statement, as seen in the following example:

DECLARE
emp_rec   employee%rowtype;

BEGIN

SELECT *
into emp_rec
from employee
WHERE emp_name like `Madison%';