The Exception Section

An exception is an error condition that occurs during the execution of a PL/SQL program. An exception might be predefined—for instance, an INSERT statement attempts to add a duplicate row to a table, resulting in the DUP_VAL_ON_INDEX exception being raised. You can also define your own exceptions that are specific to your application. The exception section defines the exception handlers invoked for both predefined and user-defined exceptions. Each exception handler consists of one or more PL/SQL statements.

Declaring Variables with PL/SQL

Another capability provided by PL/SQL—but not SQL—is additional datatypes. In addition to the normal Oracle SQL datatypes, PL/SQL enables you to declare variables with these datatypes:

In addition, PL/SQL provides two composite datatypes: TABLE and RECORD. You'll learn more about them on Day 10.

Using %TYPE to Declare a Variable

The syntax for declaring a variable with %TYPE is

variable-name   table-name.column-name%TYPE;

The variables are defined as follows:

variable-name is the variable that you are declaring.

table-name is the table that contains a column whose type the variable will assume.

column-name is a column defined in table-name.

For example, you declare a variable to store a repair depot technician's name in this way:

Tech_Name   Depot_Estimate.Technician%TYPE;

The benefit of using %TYPE in a variable declaration is that the PL/SQL code is dependent on the definition of the Technician column in the Depot_Estimate table.

Using %ROWTYPE to Declare a Variable

The syntax for declaring a variable with %ROWTYPE is

variable-name   table-name%ROWTYPE;

The variables are defined as follows:

variable-name is the composite variable that you are declaring.

table-name is the table whose structure the composite variable will assume.

For instance, a composite variable that stores a row from the Depot_Estimate table is declared like this:

Depot_Est_Row    Depot_Estimate%ROWTYPE;

An element of Depot_Est_Row can be accessed in this manner:

Depot_Est_Row.Technician :=  `RICHARDSON';

Some Familiar Control Structures

Several PL/SQL statements control the flow of execution of a PL/SQL subprogram.

• IF-THEN-ELSIF
• LOOP
• EXIT
• WHILE LOOP
• FOR LOOP
• GOTO
• NULL

Before you can build stored procedures and triggers, you should be familiar with the basics of PL/SQL programming. The next part of this lesson explores the detailed use of these statements.

The IF Statement

The syntax of PL/SQL's IF-THEN-ELSIF statement differs somewhat from that of the comparable statement in the C programming language.

The PL/SQL syntax is

IF condition THEN
   statement; ...  statement;
[ELSIF condition THEN
statement; ...  statement;]
...
[ELSIF condition THEN
statement; ...  statement;]
[ELSE
statement; ...  statement;]
END IF;

The variables are defined as follows:

condition is a valid PL/SQL condition.

statement is a valid PL/SQL statement.

Regarding the IF-THEN-ELSIF statement, be aware of these facts:

• The ELSIF and ELSE clauses are optional.
• An IF statement can have multiple ELSIF clauses but only one ELSE clause.
• Note the spelling: ELSIF, not ELSEIF.

Listing 9.3 demonstrates a simple example of the IF-THEN-ELSIF statement.

Listing 9.3. Using the IF-THEN-ELSIF statement.

if MOD(i,5) = 0 then
  rec_number := 5;
elsif MOD(i,7) = 0 then
  rec_number := 7;
else
  rec_number := i;
end if;

The Simple LOOP Statement

The most basic type of loop is the LOOP statement without any additional qualifiers.

LOOP

  statement; ... statement;

END LOOP;

The variable is defined as follows:

statement is any valid PL/SQL statement, including another LOOP.

Obviously, this loop is an infinite loop. To exit this loop when a required condition is satisfied, use the EXIT statement.

The EXIT Statement

The EXIT statement has two forms:

• EXIT without any other clauses—an unconditional exit
• EXIT [label-name] WHEN condition

The first form of EXIT causes control to exit the loop that encloses the EXIT statement.

The second form of EXIT causes control to exit the enclosing loop when the specified condition is met, as shown in Listing 9.4.

Listing 9.4. Using the EXIT statement.

SQL> declare
  2
   2  i  positive := 1;
    3  max_loops constant positive := 100;
  4
  4  begin
  5
  5  loop
  6
  6    i := i + 1;
   7    exit when i > max_loops;
  8
  8  end loop;
  9
  9  end;
 10  /
PL/SQL procedure successfully completed.

The WHILE-LOOP Statement

The WHILE-LOOP statement adds a condition to a loop.

WHILE condition LOOP
  statement; ... statement;
END LOOP;

The variables are defined as follows:

condition is a valid PL/SQL condition.

statement is a valid PL/SQL statement.

Listing 9.5 demonstrates the use of the WHILE-LOOP statement.

Listing 9.5. Using the WHILE-LOOP statement.

WHILE I < 100 LOOP
  I := I + 1;
  insert into temp_table (rec_number) values (I);
END LOOP;

The FOR-LOOP Statement

The FOR-LOOP is quite similar to the WHILE-LOOP.

Here's the syntax:

FOR loop-variable IN [REVERSE] lower-bound..upper-bound LOOP
  statement;  ...  statement;
END LOOP;

The variables are defined as follows:

loop-variable is an integer variable that serves as a counter.

lower-bound is the lower bound of the increment range.

upper-bound is the upper bound of the increment range.

REVERSE is an optional keyword that, if used, causes the loop to decrement from upper-bound to lower-bound.

Listing 9.6 demonstrates the use of a FOR-LOOP statement.

Listing 9.6. Using a FOR-LOOP statement.

for i in 1..max_loops loop
  j := j + j;
  dbms_output.put_line(`j: ` || to_char(j));
end loop;