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 (not yet discussed in class). However, the select-statement has a special form in which a single tuple is placed in variables; we discuss this matter later.
Data definition statements like DROP, CREATE, or ALTER are not allowed. C style comments( /* . . . */ ) are allowed. PL/SQL is not case sensitive. We may also find in the executable section certain kinds of statements such as assignments, branches, loops, procedure calls, and triggers, which are all described below.
To execute a PL/SQL program, we must follow the program itself by
As with Oracle SQL programs, we can invoke a PL/SQL program either by typing it to sqlplus or by putting the code in a file and invoking the file in the various ways we learned in the Getting Started With Oracle document.
Information is transmitted between PL/SQL and the database through variables. Every variable has a specific type associated with it. That type can be either
The initial value of any variable, regardless of its type, is NULL.
The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or real quantity.
The most commonly used character type is VARCHAR(L), where L is the maximum length in bytes. The length is required, and there is no default. For example, we might declare:
DECLARE myBeer VARCHAR(20); Price NUMBER;
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. So rather than hard code 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 attribute name 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 manf, assuming Beers(name, manf) is the example relation we've been using in class.
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.
The simplest form of program has some declarations followed by an executable section consisting of one of more of the SQL statements with which we are familiar. The major nuance is that the form of the select-from-where 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 only works if the result of the query is 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; it is based on the following relation:
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 statement */
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.
PL/SQL allows you to branch and create loops in a fairly familar (at least to BASIC programmers :-) 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, then you ``nest'' with:
IF...THEN...ELSIF... etc...ELSIF...ELSE...END IF;
Here is an example, a small modification of the previous example, 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;
We create a loop with the following:
LOOP
loop body (a list of statements)
END LOOP;
At least one of the statements in the loop should be an EXIT statement of the form
EXIT WHEN condition;
The loop breaks if the 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:
WHILE <condition> LOOP
loop body
END LOOP;
FOR i IN a..b LOOP
loop body
END LOOP;
Here, i can be any variable; it is local to the for-loop and need
not be declared.
Also, a and b are constants.
A cursor is a variable whose value is a tuple of some relation; typically that relation is not a stored relation but is the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program that acts on each such tuple.
The example below illustrates a cursor fetch loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program looks at each tuple and, if the first component is less than the second, inserts 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) BEGIN
9) OPEN T1Cursor;
10) LOOP
/* retrieve each row of the result of the above query into PL/SQL
variables */
11) FETCH T1Cursor INTO a, b;
/* If there are no more rows to fetch, exit the loop */
12) EXIT WHEN T1Cursor%NOTFOUND;
13) INSERT INTO T1 VALUES(b, a);
14) END LOOP;
/* Free cursor used by the query */
15) CLOSE T1Cursor;
16) END;
17) .
18) run;
Here are explanations for the various lines of this program.
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 example relation:
CREATE TABLE T2 (
a INTEGER,
b CHAR(10)
);
CREATE OR REPLACE PROCEDURE addtuple1(x IN NUMBER)
AS
BEGIN
INSERT INTO T2 VALUES(x, 'xxx');
END addtuple1;
.
run;
A procedure is introduced by the keywords CREATE PROCEDURE followed by the procedure name and its arguments. 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 arguments, each followed by a mode and a type. The types are as for PL/SQL variables. The possible modes are IN (read-only), OUT (write-only), and INOUT (read and write).
Following the arguments is the keyword AS (IS is a synonym). Then comes a body, bracketed by BEGIN...END;. We have given the name of the procedure after the END; this name is optional.
The contents of the body is essentially a PL/SQL statement. However, the declare-section is not introduced with the keyword DECLARE. Rather, following AS we can have:
declarations of the form ``name type;'' BEGIN procedure body; END;
The run at the end runs the statement that declares the procedure; it does not execute the procedure. To execute the procedure, we use another PL/SQL statement, in which the procedure is invoked as an executable statement. For example:
BEGIN addtuple1(99); END; . run;
The follwing procedure also inserts a tuple into T2, but it takes both components as arguments:
CREATE OR REPLACE PROCEDURE addtuple2(
x T2.a%TYPE,
y T2.b%TYPE)
AS
BEGIN
INSERT INTO T2(a, b)
VALUES(x, y);
END addtuple2;
.
run;
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 OR REPLACE 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;
Please note that if a procedure is OUT or INOUT, the formal parameters are written to their corresponding arguments. Because of this, the arguments must have lvalues.
Warning: In a procedure declaration, it is illegal to constrain CHAR and VARCHAR parameters with a length. However, types such as CHAR or VARCHAR are legal types of parameters. The declared length of the argument will be used as the length of the corresponding parameter.
We can also write functions instead of procedures. For a function, we follow the argument list by RETURN and the type of the return value. RETURN and a value in the body of the function does the obvious return.
Triggers are special PL/SQL statements similar to procedure declarations. However a procedure is executed explicitly from another block via a procedure call, which can also pass arguments. A trigger is executed implicitly whenever the triggering even happens, and a trigger does not accept arguments. The triggering event is either a INSERT, UPDATE, or DELETE command. The timing can be either BEFORE or AFTER. The level can be either row_level or statement_level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement. The general syntax for creating a trigger is:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER}triggering_event ON table_reference
[FOR EACH ROW[WHEN trigger_condition]]
trigger_body;
The body of the trigger is a conventional PL/SQL statement. In principle, this body may perform any action allowed in a PL/SQL statement, but in practice there are limits, do to Oracle's conservative approach to avoiding a situation where one trigger performs an action that triggers a second trigger, which triggers a third, and so on. Roughly, the body should not modify the relation on which the trigger is placed, or any relation linked to it by a constraint such as a foreign key constraint. See the document on Non-SQL2 Features of Oracle for more details.
The example below is based on the two relations:
CREATE TABLE T2 (
a INTEGER,
b CHAR(10)
);
CREATE TABLE T3 (
c CHAR(10),
d INTEGER
);
We create a trigger that may insert a tuple into T3 when a tuple is inserted into T2. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into T3. Note that unless we specify ``FOR EACH ROW,'' the trigger defaults to ``FOR EACH STATEMENT.''
CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON T2
FOR EACH ROW
WHEN(NEW.a <= 10)
BEGIN
INSERT INTO T3 VALUES(:NEW.b, :NEW.a);
END trig1;
.
run;
The special variables NEW and OLD are available to refer to new and old tuples respectively. The concept is the same as for SQL3 triggers described in Section 6.6 of the text. Note, however, that in the body, NEW and OLD must be preceded by a colon, but in the WHEN clause they do not have a preceding colon.
Notice that we end the trigger with a dot and run, as for PL/SQL statements in general. ``Running'' the trigger creates it; it does not execute the trigger. Only a triggering event, insertion into T2 in this example, causes the trigger to execute.
To view information about a trigger, say trig2:
SELECT TRIGGER_TYPE, TABLE_NAME, TRIGGERING_EVENT FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'trig1';
To drop a trigger, do:
DROP TRIGGER trigger_name;
Where trigger_name is the name of the trigger to be dropped. A trigger can also be disabled or enabled:
ALTER TRIGGER trigger_name{DISABLE|ENABLE};
PL/SQL will not always tell you about compilation errors. You may get a cryptic error like ``procedure created with compilation errors.'' If you don't see what is wrong immediately, try issuing the command
SHOW ERRORS PROCEDURE p
To get a listing of the errors for procedure p. Similarly, you can get the errors associated with a created trigger t by
SHOW ERRORS TRIGGER t
Furthermore, SHO ERR is an abbreviation for SHOW ERRORS, and you can omit PROCEDURE p if p is the most recently created procedure.