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.
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.
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;