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.
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.
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:
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.
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:
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:
With the expected output of:
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:
An example usage of such a loop would be something like this:
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:
Our typical (as in typical for these class notes) would be:
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.
There is also the traditional numeric FOR loop that's commonly found in most procedural languages.
The general format of such a loop is:
The start and end values must be integers, and are always incremented by one. An example of such a loop would be:
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:
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:
Which interestingly enough, prints out:
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).
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 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:
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.
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 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:
(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:
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:
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:
You should go through the code, trace it, run it, and make sure you understand it.
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:
Let us rewrite our example program (presented earlier) to use this new type of loop:
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:
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.