Resources

• Database Systems: The Complete Book by Hector Garcia, Jeff Ullman, and
Jennifer Widom.
• A First Course in Database Systems by Jeff Ullman and Jennifer Widom.
• Gradiance SQL Tutorial.

Using Oracle PL/SQL

• Basic Structure of PL/SQL
• Variables and Types
• Simple PL/SQL Programs
• Control Flow in PL/SQL
• Cursors
• Procedures
• Discovering Errors
• Printing Variables

Note: The material on triggers that was formerly in this document has been moved to A
New Document on Constraints and Triggers.

Basic Structure of PL/SQL

PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding

constructs found in procedural languages, resulting in a structural language that is more
powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made
up of blocks, which can be nested within each other. Typically, each block performs a
logical action in he program. A block has the following structure:
DECLARE

/* Declarative section: variables, types, and local subprograms. */

BEGIN

/* Executable section: procedural and SQL statements go here. */

/* This is the only section of the block that is required. */

 EXCEPTION

/* Exception handling section: error handling statements go here. */

END;
Only the executable section is required. The other sections are optional. The only SQL
statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and
several other data manipulation statements plus some transaction control. However, the
SELECT statement has a special form in which a single tuple is placed in variables; more
on this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The
executable section also contains constructs such as assignments, branches, loops,
procedure calls, and triggers, which are all described below (except triggers). PL/SQL is
not case sensitive. C style comments (/* ... */) may be used.

To execute a PL/SQL program, we must follow the program text itself by

• A line with a single dot ("."), and then

• A line with run;

As with Oracle SQL programs, we can invoke a PL/SQL program either by typing it in
sqlplus or by putting the code in a file and invoking the file in the various ways we
learned in Getting Started With Oracle.

Variables and Types

Information is transmitted between a PL/SQL program and the database through

variables. Every variable has a specific type associated with it. That type can be

• One of the types used by SQL for database columns

• A generic type used in PL/SQL such as NUMBER
• Declared to be the same as the type of some database column

The most commonly used generic type is NUMBER. Variables of type NUMBER can hold
either an integer or a real number. The most commonly used character string type is
VARCHAR(n), where n is the maximum length of the string in bytes. This length is
required, and there is no default. For example, we might declare:
DECLARE

price NUMBER;

myBeer VARCHAR(20);
Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support
BOOLEAN as a type for database columns.
Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to
manipulate data stored in a existing relation. In this case, it is essential that the variable
have the same type as the relation column. If there is any type mismatch, variable
assignments and comparisons may not work the way you expect. To be safe, instead of
hard coding the type of a variable, you should use the %TYPE operator. For example:

DECLARE

myBeer Beers.name%TYPE;
gives PL/SQL variable myBeer whatever type was declared for the name column in
relation Beers.

A variable may also have a type that is a record with several fields. The simplest way to
declare such a variable is to use %ROWTYPE on a relation name. The result is a record type
in which the fields have the same names and types as the attributes of the relation. For
instance:

DECLARE

beerTuple Beers%ROWTYPE;
makes variable beerTuple be a record with fields name and manufacture, assuming that
the relation has the schema Beers(name, manufacture).

The initial value of any variable, regardless of its type, is NULL. We can assign values to
variables, using the ":=" operator. The assignment can occur either immediately after the
type of the variable is declared, or anywhere in the executable portion of the program. An
example:

DECLARE

a NUMBER := 3;

BEGIN

a := a + 1;

END;

run;
This program has no effect when run, because there are no changes to the database.

Simple Programs in PL/SQL

The simplest form of program has some declarations followed by an executable section
consisting of one or more of the SQL statements with which we are familiar. The major
nuance is that the form of the SELECT statement is different from its SQL form. After the
SELECT clause, we must have an INTO clause listing variables, one for each attribute in
the SELECT clause, into which the components of the retrieved tuple must be placed.

Notice we said "tuple" rather than "tuples", since the SELECT statement in PL/SQL only
works if the result of the query contains a single tuple. The situation is essentially the
same as that of the "single-row select" discussed in Section 7.1.5 of the text, in
connection with embedded SQL. If the query returns more than one tuple, you need to
use a cursor, as described in the next section. Here is an example:

CREATE TABLE T1(

e INTEGER,

f INTEGER

);

DELETE FROM T1;

INSERT INTO T1 VALUES(1, 3);

INSERT INTO T1 VALUES(2, 4);

/* Above is plain SQL; below is the PL/SQL program. */

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

INSERT INTO T1 VALUES(b,a);

END;

run;
Fortuitously, there is only one tuple of T1 that has first component greater than 1, namely
(2,4). The INSERT statement thus inserts (4,2) into T1.

Control Flow in PL/SQL

PL/SQL allows you to branch and create loops in a fairly familiar way.

An IF statement looks like:

IF <condition> THEN <statement_list> ELSE <statement_list> END IF;

The ELSE part is optional. If you want a multiway branch, use:
IF <condition_1> THEN ...

ELSIF <condition_2> THEN ...

... ...

ELSIF <condition_n> THEN ...

ELSE ...

END IF;
The following is an example, slightly modified from the previous one, where now we
only do the insertion if the second component is 1. If not, we first add 10 to each
component and then insert:
DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

IF b=1 THEN

INSERT INTO T1 VALUES(b,a);

ELSE

INSERT INTO T1 VALUES(b+10,a+10);

END IF;

END;

run;
Loops are created with the following:
LOOP

<loop_body> /* A list of statements. */

END LOOP;
At least one of the statements in <loop_body> should be an EXIT statement of the form
EXIT WHEN <condition>;
The loop breaks if <condition> is true. For example, here is a way to insert each of the
pairs (1, 1) through (100, 100) into T1 of the above two examples:
DECLARE

i NUMBER := 1;

BEGIN

LOOP

INSERT INTO T1 VALUES(i,i);

i := i+1;

EXIT WHEN i>100;

END LOOP;

END;

run;
Some other useful loop-forming statements are:

• EXIT by itself is an unconditional loop break. Use it inside a conditional if you

like.
• A WHILE loop can be formed with

WHILE <condition> LOOP

<loop_body>

END LOOP;

• A simple FOR loop can be formed with:

FOR <var> IN <start>..<finish> LOOP

<loop_body>

END LOOP;
Here, <var> can be any variable; it is local to the for-loop and need not be
declared. Also, <start> and <finish> are constants.

Cursors

A cursor is a variable that runs through the tuples of some relation. This relation can be a
stored table, or it can be the answer to some query. By fetching into the cursor each tuple
of the relation, we can write a program to read and process the value of each such tuple.
If the relation is stored, we can also update or delete the tuple at the current cursor
position.

The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose
tuples are pairs of integers. The program will delete every tuple whose first component is
less than the second, and insert the reverse tuple into T1.

1) DECLARE

/* Output variables to hold the result of the query: */

2) a T1.e%TYPE;

3) b T1.f%TYPE;

/* Cursor declaration: */

4) CURSOR T1Cursor IS

5) SELECT e, f

6) FROM T1

7) WHERE e < f

8) FOR UPDATE;

9) BEGIN

10) OPEN T1Cursor;

11) LOOP

/* Retrieve each row of the result of the above query

into PL/SQL variables: */

12) FETCH T1Cursor INTO a, b;

/* If there are no more rows to fetch, exit the loop: */

13) EXIT WHEN T1Cursor%NOTFOUND;

/* Delete the current tuple: */

14) DELETE FROM T1 WHERE CURRENT OF T1Cursor;

/* Insert the reverse tuple: */

15) INSERT INTO T1 VALUES(b, a);

16) END LOOP;

/* Free cursor used by the query. */

17) CLOSE T1Cursor;

18) END;

19) .

20) run;
Here are explanations for the various lines of this program:

• Line (1) introduces the declaration section.

• Lines (2) and (3) declare variables a and b to have types equal to the types of
attributes e and f of the relation T1. Although we know these types are INTEGER,
we wisely make sure that whatever types they may have are copied to the PL/SQL
variables (compare with the previous example, where we were less careful and
declared the corresponding variables to be of type NUMBER).
• Lines (4) through (8) define the cursor T1Cursor. It ranges over a relation defined
by the SELECT-FROM-WHERE query. That query selects those tuples of T1 whose
first component is less than the second component. Line (8) declares the cursor
FOR UPDATE since we will modify T1 using this cursor later on Line (14). In
general, FOR UPDATE is unnecessary if the cursor will not be used for
modification.
• Line (9) begins the executable section of the program.
• Line (10) opens the cursor, an essential step.
• Lines (11) through (16) are a PL/SQL loop. Notice that such a loop is bracketed
by LOOP and END LOOP. Within the loop we find:
o On Line (12), a fetch through the cursor into the local variables. In
general, the FETCH statement must provide variables for each component
of the tuple retrieved. Since the query of Lines (5) through (7) produces
pairs, we have correctly provided two variables, and we know they are of
the correct type.
o On Line (13), a test for the loop-breaking condition. Its meaning should be
clear: %NOTFOUND after the name of a cursor is true exactly when a fetch
through that cursor has failed to find any more tuples.
o On Line (14), a SQL DELETE statement that deletes the current tuple using
the special WHERE condition CURRENT OF T1Cursor.
o On Line (15), a SQL INSERT statement that inserts the reverse tuple into
T1.
• Line (17) closes the cursor.
• Line (18) ends the PL/SQL program.
• Lines (19) and (20) cause the program to execute.

Procedures

PL/SQL procedures behave very much like procedures in other programming language.
Here is an example of a PL/SQL procedure addtuple1 that, given an integer i, inserts
the tuple (i, 'xxx') into the following example relation:
CREATE TABLE T2 (

a INTEGER,

b CHAR(10)

);

CREATE PROCEDURE addtuple1(i IN NUMBER) AS

BEGIN

INSERT INTO T2 VALUES(i, 'xxx');

END addtuple1;

run;
A procedure is introduced by the keywords CREATE PROCEDURE followed by the
procedure name and its parameters. An option is to follow CREATE by OR REPLACE. The
advantage of doing so is that should you have already made the definition, you will not
get an error. On the other hand, should the previous definition be a different procedure of
the same name, you will not be warned, and the old procedure will be lost.

There can be any number of parameters, each followed by a mode and a type. The
possible modes are IN (read-only), OUT (write-only), and INOUT (read and write). Note:
Unlike the type specifier in a PL/SQL variable declaration, the type specifier in a
parameter declaration must be unconstrained. For example, CHAR(10) and VARCHAR(20)
are illegal; CHAR or VARCHAR should be used instead. The actual length of a parameter
depends on the corresponding argument that is passed in when the procedure is invoked.

Following the arguments is the keyword AS (IS is a synonym). Then comes the body,
which is essentially a PL/SQL block. We have repeated the name of the procedure after
the END, but this is optional. However, the DECLARE section should not start with the
keyword DECLARE. Rather, following AS we have:

... AS

<local_var_declarations>

BEGIN

<procedure_body>

END;

run;
The run at the end runs the statement that creates the procedure; it does not execute the
procedure. To execute the procedure, use another PL/SQL statement, in which the
procedure is invoked as an executable statement. For example:
BEGIN addtuple1(99); END;

run;
The following procedure also inserts a tuple into T2, but it takes both components as
arguments:
CREATE PROCEDURE addtuple2(

x T2.a%TYPE,

y T2.b%TYPE)

AS

BEGIN

INSERT INTO T2(a, b)

VALUES(x, y);

END addtuple2;

run;
Now, to add a tuple (10, 'abc') to T2:
BEGIN

addtuple2(10, 'abc');

END;

run;
The following illustrates the use of an OUT parameter:
CREATE TABLE T3 (

a INTEGER,

b INTEGER

);

CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)

AS

BEGIN
 b := 4;

INSERT INTO T3 VALUES(a, b);

END;

run;

DECLARE

v NUMBER;

BEGIN

addtuple3(10, v);

END;

run;
Note that assigning values to parameters declared as OUT or INOUT causes the
corresponding input arguments to be written. Because of this, the input argument for an
OUT or INOUT parameter should be something with an "lvalue", such as a variable like v in
the example above. A constant or a literal argument should not be passed in for an
OUT/INOUT parameter.

We can also write functions instead of procedures. In a function declaration, we follow

the parameter list by RETURN and the type of the return value:

CREATE FUNCTION <func_name>(<param_list>) RETURN <return_type> AS ...

In the body of the function definition, "RETURN <expression>;" exits from the function
and returns the value of <expression>.

To find out what procedures and functions you have created, use the following SQL
query:

select object_type, object_name

from user_objects

where object_type = 'PROCEDURE'

or object_type = 'FUNCTION';
To drop a stored procedure/function:
drop procedure <procedure_name>;

drop function <function_name>;

Discovering Errors

PL/SQL does not always tell you about compilation errors. Instead, it gives you a cryptic
message such as "procedure created with compilation errors". If you don't see what is
wrong immediately, try issuing the command
show errors procedure <procedure_name>;
Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent
compilation error.

Note that the location of the error given as part of the error message is not always
accurate!

Printing Variables

Sometimes we might want to print the value of a PL/SQL local variable. A ``quick-and-
dirty'' way is to store it as the sole tuple of some relation and after the PL/SQL statement
print the relation with a SELECT statement. A more couth way is to define a bind variable,
which is the only kind that may be printed with a print command. Bind variables are the
kind that must be prefixed with a colon in PL/SQL statements, such as :new discussed in
the section on triggers.

The steps are as follows:

1. We declare a bind variable as follows:

VARIABLE <name> <type>

where the type can be only one of three things: NUMBER, CHAR, or CHAR(n).

2. We may then assign to the variable in a following PL/SQL statement, but we must
prefix it with a colon.
3. Finally, we can execute a statement

PRINT :<name>;
outside the PL/SQL statement
Here is a trivial example, which prints the value 1.
VARIABLE x NUMBER

BEGIN

:x := 1;

END;

.
 run;

PRINT :x;
This document was written originally by Yu-May Chang and Jeff Ullman for CS145, Autumn 1997; revised by Jun Yang for Prof. Jennifer
Widom's CS145 class in Spring, 1998; additional material by Jeff Ullman, Autumn 1998; further revisions by Jun Yang, Spring 1999; minor
revisions by Jennifer Widom, Spring 2000.