MassBot1: Created page with "=Using Oracle PL/SQL ----= * Basic Structure of PL/SQL * yurttas/PL/DBL/oracle/F/R/#variables and types|Varia..."

2013-11-05T05:35:29Z

Created page with "=Using Oracle PL/SQL ----= * Basic Structure of PL/SQL * yurttas/PL/DBL/oracle/F/R/#variables and types|Varia..."

New page

=Using Oracle PL/SQL ----=

* [[yurttas/PL/DBL/oracle/F/R/#basic structure of pl/sql|Basic Structure of PL/SQL]]
* [[yurttas/PL/DBL/oracle/F/R/#variables and types|Variables and Types]]
* [[yurttas/PL/DBL/oracle/F/R/#simple programs in pl/sql|Simple PL/SQL Programs]]
* [[yurttas/PL/DBL/oracle/F/R/#control flow in pl/sql|Control Flow in PL/SQL]]
* [[yurttas/PL/DBL/oracle/F/R/#cursors|Cursors]]
* [[yurttas/PL/DBL/oracle/F/R/#procedures|Procedures]]
* [[yurttas/PL/DBL/oracle/F/R/#triggers|Triggers]]
* [[yurttas/PL/DBL/oracle/F/R/#errors|Discovering Errors]]

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

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

* A line with a single dot ("<tt>.</tt>"), and then
* A line with <tt>run;</tt>

As with Oracle SQL programs, we can invoke a PL/SQL program either by typing it in <tt>sqlplus</tt> or by putting the code in a file and invoking the file in the various ways we learned in [[yurttas/PL/DBL/oracle/F/R/or-intro.html#executing sql from a file|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 <tt>NUMBER</tt>
* Declared to be the same as the type of some database column

The most commonly used generic type is <tt>NUMBER</tt>. Variables of type <tt>NUMBER</tt> can hold either an integer or a real number. The most commonly used character string type is <tt>VARCHAR(</tt>''n''<tt>)</tt>, 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);

Types in PL/SQL are tricky. Certain SQL types cannot be parameterized in PL/SQL. For example, <tt>CHAR(50)</tt> does not work, but either <tt>CHAR</tt> or <tt>VARCHAR(50)</tt> works fine! Also note that PL/SQL allows <tt>BOOLEAN</tt> variables, even though Oracle does not support <tt>BOOLEAN</tt> as a type for database columns.

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 <tt>%TYPE</tt> operator. For example:

DECLARE
    myBeer Beers.name%TYPE;

gives PL/SQL variable <tt>myBeer</tt> whatever type was declared for the <tt>name</tt> column in relation <tt>Beers</tt>.

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 <tt>%ROWTYPE</tt> 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 <tt>beerTuple</tt> be a record with fields <tt>name</tt> and <tt>manufacture</tt>, assuming that the relation has the schema <tt>Beers(name, manufacture)</tt>.

The initial value of any variable, regardless of its type, is <tt>NULL</tt>. We can assign values to variables, using the "<tt>:=</tt>" 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 <tt>SELECT</tt> statement is different from its SQL form. After the <tt>SELECT</tt> clause, we ''must'' have an <tt>INTO</tt> clause listing variables, one for each attribute in the <tt>SELECT</tt> clause, into which the components of the retrieved tuple must be placed.

Notice we said "tuple" rather than "tuples", since the <tt>SELECT</tt> 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 <tt>T1</tt> that has first component greater than 1, namely (2,4). The <tt>INSERT</tt> statement thus inserts (4,2) into <tt>T1</tt>.

===---- Control Flow in PL/SQL===

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

An <tt>IF</tt> statement looks like:

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

The <tt>ELSE</tt> 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 <tt><loop_body></tt> should be an <tt>EXIT</tt> statement of the form

EXIT WHEN <condition>;

The loop breaks if <tt><condition></tt> is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into <tt>T1</tt> 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:

* <tt>EXIT</tt> by itself is an unconditional loop break. Use it inside a conditional if you like.
* A <tt>WHILE</tt> loop can be formed with     WHILE <condition> LOOP
        <loop_body>
    END LOOP;
* A simple <tt>FOR</tt> loop can be formed with:     FOR <var> IN <start>..<finish> LOOP
        <loop_body>
    END LOOP;<tt><var></tt><tt><start></tt><tt><finish></tt>

===---- Cursors===

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 <tt>T1(e,f)</tt> 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 <tt>T1</tt>.

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;
            /* Insert the reverse tuple: */
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:

* Line (1) introduces the declaration section.
* Lines (2) and (3) declare variables <tt>a</tt> and <tt>b</tt> to have types equal to the types of attributes <tt>e</tt> and <tt>f</tt> of the relation <tt>T1</tt>. Although we know these types are <tt>INTEGER</tt>, 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 <tt>NUMBER</tt>).
* Lines (4) through (7) define the cursor <tt>T1Cursor</tt>. It ranges over a relation defined by the <tt>SELECT</tt>-<tt>FROM</tt>-<tt>WHERE</tt> query. That query selects those tuples of <tt>T1</tt> whose first component is less than the second component.
* Line (8) begins the executable section of the program.
* Line (9) opens the cursor, an essential step.
* Lines (10) through (14) are a PL/SQL loop. Notice that such a loop is bracketed by <tt>LOOP</tt> and <tt>END LOOP</tt>. Within the loop we find:
** On Line (11), a fetch through the cursor into the local variables. In general, the <tt>FETCH</tt> 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.
** On Line (12), a test for the loop-breaking condition. Its meaning should be clear: <tt>%NOTFOUND</tt> after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples.
** On Line (13), a SQL <tt>INSERT</tt> statement that inserts the reverse tuple into <tt>T1</tt>.
* Line (15) closes the cursor.
* Line (16) ends the PL/SQL program.
* Lines (17) and (18) 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 <tt>addtuple1</tt> that, given an integer <tt>i</tt>, inserts the tuple <tt>(i, 'xxx')</tt> 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 <tt>CREATE PROCEDURE</tt> followed by the procedure name and its parameters. An option is to follow <tt>CREATE</tt> by <tt>OR REPLACE</tt>. 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 <tt>IN</tt> (read-only), <tt>OUT</tt> (write-only), and <tt>INOUT</tt> (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, <tt>CHAR(10)</tt> and <tt>VARCHAR(20)</tt> are illegal; <tt>CHAR</tt> or <tt>VARCHAR</tt> 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 <tt>AS</tt> (<tt>IS</tt> is a synonym). Then comes the body, which is essentially a PL/SQL block. We have repeated the name of the procedure after the <tt>END</tt>, but this is optional. However, the <tt>DECLARE</tt> section should ''not'' start with the keyword <tt>DECLARE</tt>. Rather, following <tt>AS</tt> we have:

... AS
<local_var_declarations>
BEGIN
    <procedure_body>
END;
.
run;

The <tt>run</tt> 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 <tt>T2</tt>, 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 <tt>(10, 'abc')</tt> to <tt>T2</tt>:

BEGIN
    addtuple2(10, 'abc');
END;
.
run;

The following illustrates the use of an <tt>OUT</tt> 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 <tt>OUT</tt> or <tt>INOUT</tt> causes the corresponding input arguments to be written. Because of this, the input argument for an <tt>OUT</tt> or <tt>INOUT</tt> parameter should be something with an "lvalue", such as a variable like <tt>v</tt> in the example above. A constant or a literal argument should not be passed in for an <tt>OUT</tt>/<tt>INOUT</tt> parameter.

We can also write functions instead of procedures. In a function declaration, we follow the parameter list by <tt>RETURN</tt> and the type of the return value:

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

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

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

===---- Triggers===

Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a <tt>INSERT</tt>, <tt>DELETE</tt>, or <tt>UPDATE</tt> command. The timing can be either <tt>BEFORE</tt> or <tt>AFTER</tt>. The trigger 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.

Below is the syntax for creating a trigger in Oracle (this syntax has been simplified; for the complete version try <tt>HELP CREATE TRIGGER</tt> in <tt>sqlplus</tt>):

CREATE [OR REPLACE] TRIGGER <trigger_name>
    {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
    [FOR EACH ROW [WHEN (<trigger_condition>)]]
    <trigger_body>

Some important points to note:

* Only <tt>BEFORE</tt> and <tt>AFTER</tt> options are supported; <tt>INSTEAD OF</tt> is not.

* You may specify up to three triggering events using the keyword <tt>OR</tt>. Furthermore, <tt>UPDATE</tt> can be optionally followed by the keyword <tt>OF</tt> and a list of attribute(s) in <tt><table_name></tt>. If present, the <tt>OF</tt> clause defines the event to be only an update of the attribute(s) listed after <tt>OF</tt>. Here are some examples:     ... INSERT ON R ...
    ... INSERT OR DELETE OR UPDATE ON R ...
    ... UPDATE OF A, B OR INSERT ON R ...

* If <tt>FOR EACH ROW</tt> option is specified, the trigger is row-level; otherwise, the trigger is statement-level.

* For a row-level trigger, a trigger restriction can be specified in the <tt>WHEN</tt> clause, enclosed by parentheses. The trigger restriction is a SQL condition that must be satisfied in order for Oracle to fire the trigger. This condition cannot contain subqueries. Without the <tt>WHEN</tt> clause, a trigger is fired by every triggering event.

* <tt><trigger_body></tt> is a PL/SQL block, rather than sequence of SQL statements. Oracle has placed certain restrictions on what you can do in <tt><trigger_body></tt>, in order to avoid situations where one trigger performs an action that triggers a second trigger, which then triggers a third, and so on, which could potentially create an infinite loop. The restrictions on <tt><trigger_body></tt> include:
** You cannot modify the same relation whose modification is the event triggering the trigger.
** You cannot modify a relation connected to the triggering relation by another constraint such as a foreign-key constraint.

We illustrate Oracle's syntax for creating a trigger through an example based on the following two tables:

CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);

We create a trigger that may insert a tuple into <tt>T5</tt> when a tuple is inserted into <tt>T4</tt>. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into <tt>T5</tt>:

CREATE TRIGGER trig1
    AFTER INSERT ON T4
    FOR EACH ROW
    WHEN (NEW.a <= 10)
    BEGIN
        INSERT INTO T5 VALUES(:NEW.b, :NEW.a);
    END trig1;
.
run;

The special variables <tt>NEW</tt> and <tt>OLD</tt> are available to refer to new and old tuples respectively. '''Note:''' In the trigger body, <tt>NEW</tt> and <tt>OLD</tt> must be preceded by a colon ("<tt>:</tt>"), but in the <tt>WHEN</tt> clause, they do not have a preceding colon!

Again, notice that we end the <tt>CREATE TRIGGER</tt> statement with a dot and <tt>run</tt>, as for all PL/SQL statements in general. Running the <tt>CREATE TRIGGER</tt> statement only creates the trigger; it does not execute the trigger. Only a triggering event, such as an insertion into <tt>T4</tt> in this example, causes the trigger to execute.

To view information about your triggers, use the following:

select trigger_name from user_triggers;

select trigger_type, table_name, triggering_event
from user_triggers
where trigger_name = '<trigger_name>';

To drop a trigger:

drop trigger <trigger_name>;

To disable or enable a trigger:

alter trigger <trigger_name> {disable|enable};

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

Similarly, you can get the errors associated with a created trigger by

show errors trigger <trigger_name>;

Furthermore, "<tt>SHO ERR</tt>" is an abbreviation for "<tt>SHOW ERRORS</tt>", and you can omit "<tt>PROCEDURE ...</tt>" or "<tt>TRIGGER ...</tt>" if you just want to see the most recent compilation error.

----

<font size="-2">This document is written originally for Prof. Jeff Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998.</font>

Yurttas/PL/DBL/oracle/F/R/or-plsql.html - Revision history

MassBot1: Created page with "=Using Oracle PL/SQL ----= * Basic Structure of PL/SQL * yurttas/PL/DBL/oracle/F/R/#variables and types|Varia..."