Notes 0009

PL/SQL - SQL

Apparently, we can run SQL statements inside PL/SQL! Isn't that amazing?

We can't use all of SQL though, we can only use DML (Data Manipulation Language) which includes statements like SELECT, INSERT, UPDATE, and DELETE, and transaction control statements, like COMMIT, ROLLBACK, SAVEPOINT.

The only limitation seems to be are DDL statements, which are used to CREATE, ALTER, and DROP tables, and GRANT privileges, just to name a few.

Simple Example

For right now, here's a simple example. We'll do more as we learn PL/SQL. In this example, we'll insert a new PRODUCT into our simple database that we created in Notes 0002.

DECLARE
    PID NUMBER(6);
BEGIN
    PID := 20;
    INSERT INTO product VALUES (PID,'tv',32,199.99);
    PID := PID + 1;
    INSERT INTO product VALUES (PID,'vcr',16,799.98);
    COMMIT;
END;

We can now run a SELECT statement to retrieve the values we've inserted:

SELECT * FROM PRODUCT WHERE PRODUCT_ID >= 20;

Which produces the expected results:

PRODUCT_ID DESCRIPTION
---------- ------------
        20 tv
        21 vcr

Notice that in our example, we used a variable named PID inside our INSERT statement. That's the real power of PL/SQL, where we can use procedural language constructs and variables to drive our database SQL code.

PL/SQL Loops

Just as with IF statements, PL/SQL also has loops. Loops are used to repeat some action multiple times, until some condition is met.

PL/SQL has five looping structures, and we shall talk about each one in more depth as we move along. So without further interruption, I present to you...

LOOP ... EXIT Loop

The general format of such a loop is:

LOOP
    various_statements
    IF condition THEN
        EXIT;
    END IF;
    various_statements
END LOOP;

This loop is very similar to an infinite loop in C/C++, where you use break; to terminate the loop; in this case, the EXIT; command takes the form of break.

Note that we can place various program statements before the exiting IF statement and after, which gives us great flexibility about when and how the condition is evaluated.

An example of such a loop would be:

DECLARE
    I NUMBER(6);
BEGIN
    I := 1;
    LOOP
        DBMS_OUTPUT.PUT_LINE('aI: ' || I);

        I := I + 1;
        IF I > 5 THEN
            EXIT;
        END IF;

        DBMS_OUTPUT.PUT_LINE('bI: ' || I);
    END LOOP;
END;

With the expected output of:

aI: 1
bI: 2
aI: 2
bI: 3
aI: 3
bI: 4
aI: 4
bI: 5
aI: 5

Note that you should SET SERVEROUTPUT ON; in order to see the output in SQL*Plus screen.

Also, it would be VERY helpful if you trace the above program to ensure that you understand how the loop functions and why the results look as they do. I shall not provide the output for the following code, and expect you to run it yourself.

LOOP ... EXIT WHEN Loop

To simplify our writing our the IF statement, there is a simpler form, the EXIT WHEN loop. The general format of such a loop is:

LOOP
    various_statements
    EXIT WHEN condition;
    various_statements
END LOOP;

An example usage of such a loop would be something like this:

DECLARE
    I NUMBER(6);
BEGIN
    I := 1;
    LOOP
        DBMS_OUTPUT.PUT_LINE('aI: ' || I);
        I := I + 1;
        EXIT WHEN I > 5;
        DBMS_OUTPUT.PUT_LINE('bI: ' || I);
    END LOOP;
END;

You should run this code yourself. It would actually be more helpful if you write out the output first, and then compare it to the actual results.

WHILE ... LOOP Loop

Our next loop is the all familiar WHILE loop, except now it is in PL/SQL and not in C/C++. It works nearly identically though. The idea is that you have a condition which is tested each time through the loop, and if it's false, the loop terminates.

The general format of such a loop is:

WHILE condition
LOOP
    various_statements
END LOOP;

Our typical (as in typical for these class notes) would be:

DECLARE
    I NUMBER(6);
BEGIN
    I := 1;
    WHILE I <= 5
    LOOP
        DBMS_OUTPUT.PUT_LINE('aI: ' || I);
        I := I + 1;
        DBMS_OUTPUT.PUT_LINE('bI: ' || I);
    END LOOP;
END;

Just as with the previous code, you should try to figure out what the output is, and then run it to see if your trace was correct. Tracing questions such as these are fair game for quizzes and tests.

FOR Loop

There is also the traditional numeric FOR loop that's commonly found in most procedural languages.

The general format of such a loop is:

FOR countervariable IN startvalue .. endvalue
LOOP
    various_statements
END LOOP;

The start and end values must be integers, and are always incremented by one. An example of such a loop would be:

BEGIN
    FOR I IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE('I: ' || I);
    END LOOP;
END;

Notice that we never actually directly initialize I, or even declare it! It is done implicitly for us by the FOR loop. You should run this code to ensure you understand it.

You can also use other variables to loop on. For example, to loop from J to K, you'd do something like:

DECLARE
    J NUMBER(6);
    K NUMBER(6);
BEGIN
    J := 7;
    K := 2;
    FOR I IN K..J
    LOOP
        DBMS_OUTPUT.PUT_LINE('I: ' || I);
    END LOOP;
END;

Again, notice that we never actually initialize nor declare I. In fact, the I in the loop is a totally different variable. Even if you have an I variable declared, the loop will still use its own version. You can verify that by running this code:

DECLARE
    I NUMBER(6);
BEGIN
    I := 7;
    DBMS_OUTPUT.PUT_LINE('BEFORE LOOP I: ' || I);
    FOR I IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE('IN LOOP I: ' || I);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('AFTER LOOP I: ' || I);
END;

Which interestingly enough, prints out:

BEFORE LOOP I: 7
IN LOOP I: 1
IN LOOP I: 2
IN LOOP I: 3
IN LOOP I: 4
IN LOOP I: 5
AFTER LOOP I: 7

Which illustrates that the value of our declared variable I is unchanged by the loop (and that the loop internally has I declared which is different from our explicitly declared I).

Cursors

Before we move on with our discussion of the next and last loop construct, we must cover the concept of Cursors.

Oracle has two major different types of cursors. One is implicit and the other one is explicit.

Implicit Cursor

Implicit cursors can be generated every time you do a SELECT statement in PL/SQL. The general format goes something like this:

SELECT selectfields INTO declared_variables FROM table_list WHERE search_criteria;

The only catch is that the search criteria must return one and only one result. If it returns zero, or more than one, an error is generated.

For example, lets say we wanted to get the name and price of some specific product (identified by PRODUCT_ID) from the database we created in Notes 0002, we might do something like this:

DECLARE
    NAME PRODUCT.DESCRIPTION%TYPE;
    AMOUNT PRODUCT.PRICE%TYPE;
BEGIN
    SELECT DESCRIPTION,PRICE INTO NAME, AMOUNT
        FROM PRODUCT WHERE PRODUCT_ID = 4;
    DBMS_OUTPUT.PUT_LINE('PRICE OF ' || NAME || ' IS ' || AMOUNT);
END;

Which faithfully displays out:

PRICE OF keyboard IS 19.95

Assuming the "keyboard" is in the database and has PRODUCT_ID = 4 (and has that price).

Note that we used the table's types, which brings up another issue: Now is a pretty good time to illustrate the ROWTYPE type. Let's rewrite the above using that.

DECLARE
    P PRODUCT%ROWTYPE;
BEGIN
    SELECT * INTO P FROM PRODUCT WHERE PRODUCT_ID = 4;
    DBMS_OUTPUT.PUT_LINE('PRICE OF ' || P.DESCRIPTION || ' IS ' || P.PRICE);
END;

Notice that the code got a lot smaller since we don't have to worry about defining every single variable for retrieval purposes. We retrieve a whole row of data at a time. The output of the above code is exactly the same as the previous.

Explicit Cursor

Explicit Cursors are cursors that you have to explicitly declare, and which give you a lot more flexibility than the implicit ones.

To declare an explicit cursor, you have to do it in the DECLARE section. The format looks something like:

CURSOR cursorname IS SELECT_statement;

Where SELECT_statement is any select statement (except a more exotic one which contains a UNION or MINUS.

Opening an Explicit Cursor

In order to use an explicit cursor, you must open it. You do that with a simple:

OPEN cursorname;

(obviously you have to do that inside the code section, between BEGIN and END).

Fetching Data into an Explicit Cursor

Besides opening the cursor, we also have to grab the results of the SELECT statement one by one. We do that with a FETCH. For example:

FETCH cursorname INTO recordvariables;

We shall do some examples when we learn our cursor loops, so hang on...

Closing a Cursor

Closing a cursor is just as easy as opening it. We just say:

CLOSE cursorname;

Cursors will be closed automatically once your code exits, but it's still a good idea to close them explicitly.

LOOP ... EXIT WHEN Loop (Again)

We can use our standard loops in order to go loop through the results returned by the cursor. So, let's move on to our example:

DECLARE
    P PRODUCT%ROWTYPE;
    CURSOR PRODUCTCURSOR IS
        SELECT * FROM PRODUCT;
BEGIN
    OPEN PRODUCTCURSOR;
    LOOP
        FETCH PRODUCTCURSOR INTO P;
        EXIT WHEN PRODUCTCURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('PRICE OF ' || P.DESCRIPTION || ' IS ' || P.PRICE);
    END LOOP;
    CLOSE PRODUCTCURSOR;
END;

Go through the code line by line. First, we declare our P variable which is a ROWTYPE from table PRODUCT. We then declare our CURSOR, which simply selects everything from the PRODUCT table.

Our code then proceeds to OPEN the cursor. We then fall into our standard loop (which we learned about earlier), and FETCH results from the CURSOR. We EXIT the loop if we got no more results (the PRODUCTCURSOR%NOTFOUND condition). If we did not exit the loop, we output product description and product price.

In the end, we just CLOSE the cursor. Depending on what you have in your PRODUCT table, the results of the code may look similar to this:

PRICE OF mice IS 26.99
PRICE OF keyboard IS 19.95
PRICE OF monitor IS 399.99
PRICE OF speakers IS 9.99
PRICE OF stapler IS 14.99
PRICE OF calculator IS 7.99
PRICE OF quickcam IS 99.98
PRICE OF harddrive IS 199.99
PRICE OF tv IS 199.99
PRICE OF vcr IS 799.98 

You should go through the code, trace it, run it, and make sure you understand it.

Cursor Attributes

We've already seen one of the more important cursor attributes, the %NOTFOUND. There are also these:

%NOTFOUND: Evaluates to TRUE when cursor has no more rows to read. FALSE otherwise.

%FOUND: Evaluates to TRUE if last FETCH was successful, and FALSE otherwise.

%ROWCOUNT: Returns the number of rows that the cursor has already fetched from the database.

%ISOPEN: Returns TRUE if this cursor is already open, and FALSE otherwise.

Cursor FOR ... IN ... LOOP Loop

There is also a special loop structure made specifically for working with cursors. It allows for easier cursor handling; it opens and closes the cursor for us, and we don't have to explicitly check for the end.

It is a for loop that has the general format:

FOR variable(s) IN cursorname LOOP
    various_program_statements
END LOOP;

Let us rewrite our example program (presented earlier) to use this new type of loop:

DECLARE
    P PRODUCT%ROWTYPE;
    CURSOR PRODUCTCURSOR IS
        SELECT * FROM PRODUCT;
BEGIN
    FOR P IN PRODUCTCURSOR LOOP
        DBMS_OUTPUT.PUT_LINE('PRICE OF ' || P.DESCRIPTION || ' IS ' || P.PRICE);
    END LOOP;
END;

Notice that the code got quite a bit simpler, with lots of cursor handling code gone; which is now being handled by the loop itself.

If you're really into optimization, you might want to improve the above code not to return the whole %ROWTYPE but invidual fields which we're displaying, for example:

DECLARE
    CURSOR PRODUCTCURSOR IS
        SELECT DESCRIPTION,PRICE FROM PRODUCT;
BEGIN
    FOR P IN PRODUCTCURSOR LOOP
        DBMS_OUTPUT.PUT_LINE('PRICE OF ' || P.DESCRIPTION || ' IS ' || P.PRICE);
    END LOOP;
END;

Notice several things about the code: that we no longer declare P which is used for loop purposes. Also notice that our cursor is no longer returning everything, but just two individual fields which we're displaying.

That's it!

© 2006, Particle