A wise man* once said, all software programming is about hoops and loops. You take some variables, give them hoops to jump through and, depending on their success or failure, you give them some actions to loop through a number of times.
Might sound basic, but that’s what all software code – from Angry Birds to the space shuttle – consists of: hoops and loops.
Let me show you how.
[*that wise man was me, by the way]
But first I must introduce you to PL/SQL.
PL/SQL is Oracle’s procedural language extension to SQL. You know how I said programming is taking variables, giving them hoops to jump through and actions to loop through a number of times? SQL is the language of those actions (select, update, delete, insert), but it’s PL/SQL that brings the hoops and loops to the carnival.
SQL is great, but if we want to apply logic to our actions, we need more than it can give us, and that’s where PL/SQL comes in.
Let me give you a scenario. You’ve decided to throw a party and want to invite all your friends. You know you’ve got all their addresses in your database, and you know how to write a select statement to get their names and to get their addresses. But some of your friends live together: instead of writing “I would like to invite Chandler to my party” you would want to write “I would like to invite Chandler and Monica to my party”.
SQL can’t help you; you need PL/SQL.
Let me show you how.
But first I must introduce you to anonymous blocks.
Thus far – in SQL-land – we have executed each action individually, statement by statement. In order to group actions together in PL/SQL, we put them in something called a block. This way, we can ask Oracle to simply execute the block and it will run all the statements in that block in sequence.
Blocks are structured as follows:
DECLARE <Variable declaration section> (Optional) BEGIN <Statements> END;
We haven’t talked about variables yet, so I’d best tell you what they are. A variable is a named storage location which can contain a value. Let me give you an example: if we wanted to find out how many of our friends live with Joey Tribiani, we could write a select statement to find out the address_id of Joey’s residence, store that address_id in a variable, and then use that variable to find the friend_id of anyone else who lived at that address. So variables, in effect, are kinda like Tupperware that you can store values in.
In the declaration section of our block, we must define all the variables that we plan to use: if our block doesn’t need any variables, this section does not need to exist.
To define a variable, we must give it a name, tell Oracle its datatype and, optionally, initialize it with a value (i.e. give it an initial value).
The main body of our block starts with BEGIN and, obviously, must exist.
Let’s go back to our Joey Tribiani example and I’ll show you what I mean.
DECLARE v_addr_id NUMBER; v_joey_id NUMBER; v_friend_count NUMBER; BEGIN -- Find out Joey’s address id and put it in our variable. SELECT fa.address_id, fn.friend_id INTO v_addr_id, v_joey_id FROM friend_name fn, friend_address fa WHERE fn.friend_id = fa.friend_id AND UPPER(fn.LAST_name) = 'TRIBIANI'; /* Now we have the address_id, find out how many other friends live there. We need to exclude Joey himself from our count, obviously. */ SELECT COUNT(*) INTO v_friend_count FROM friend_address WHERE address_id = v_addr_id AND friend_id <> v_joey_id; -- The number of friends is in our variable. Print it out to the screen. DBMS_OUTPUT.PUT_LINE('The number of friends that live with Joey is '||v_friend_count); END;
There are a few things I’d like you to note.
- Hopefully, the point of the declaration section is now clear. We defined 3 variables (they all happen to be of the number datatype, but they could just as easily be dates or varchar2). We gave them sensible names; I followed the convention of prefixing variable names with v_.
- In PL/SQL you can SELECT … INTO a variable. This puts the return value into the variable. (This method of populating variables is fraught with dangers; we’ll talk about them later.)
There are a few other, incidental, things that I would like you to note too:
- You can comment single lines using the double-dash (–). To comment multiple lines we put a /* before the comment, and end it with a */.
- Dbms_output.put_line() is a function that prints text to the screen. With SQL, we could simply run our query and view our output; not so with PL/SQL. Try commenting out the dbms_output.put_line() line and rerunning your anonymous block, and see what output you get.
- You can concatenate (in other words, join) multiple strings using the double-pipe (||). Notice how, in our output line, we use it to print some words with v_friend_count.
But what if we didn’t want a count of the people who have lived with Joey? What if we wanted to loop through each one printing out their name?
Let’s start with a definition, even though you probably don’t need one at this point. A loop controls the execution flow of a program and causes it to iterate through some actions a specified number of times or until a specified condition is met.
When it comes to loops, Oracle have spoilt us for choice. It’s like Ben and Jerry’s Ice Cream – there’s a flavour for every occasion and every taste. Let me show you.
But first I must introduce you to Booleans.
The Boolean – named after the great 19th Century mathematician George Boole, about whom I know absolutely nothing – is a datatype that represents a logical value: TRUE or FALSE. (Or NULL.)
Similar to VARCHAR2s, NUMBERs and DATEs, you can create BOOLEAN variables in PL/SQL and assign values to them (more on value assignment later). But Booleans are useful beyond that. Think about it: every logical expression is a Boolean expression. Want an example? The statement Mike Tyson is a man resolves to TRUE (I dare you to tell him he’s a little girl to his face). And the statement 2 + 2 = 5 is FALSE. And you know those WHERE statements that we use in SELECTs, DELETEs and UPDATEs? They’re just Booleans too: select/delete/update rows from a table where these conditions are TRUE.
Booleans, explicitly or implicitly, are all over PL/SQL. Now that I’ve told you about them, you’ll start noticing them everywhere.
But, for now, let’s get back to those loops of ours.
As I said, there are a few different types of loops, each suited to different scenarios. Below is the names and syntax of a few of them:
|Simple loops||LOOP<<actions to carry out>>EXIT;END LOOP;||Simple loops must contain an EXIT or they will loop endlessly; the EXIT will usually only be called when a specific condition is TRUE.Simple loops always run at least once.|
|While loops||WHILE <<Boolean expression>> LOOP<<actions to carry out>>END LOOP;||While loops iterate for as long as the Boolean expression is TRUE. The expression is tested with each new iteration.While loops may not iterate even once if the Boolean expression is FALSE from the start.|
|For loops||FOR indx IN 1 .. n LOOP<<actions to carry out>>END LOOP;||A For Loop will run n times.|
|Cursor loops||For (SELECT statement) LOOP<<actions to carry out>>END LOOP;||This loop will iterate once for every record returned in the select statement.These loops are useful in cases where you want to use the values selected in your query in the loop actions.Are we allowed to have favourites? This is the loop I use 80% of the time.|
Now that we’ve met the different types of loops, let me show you how to use them.
But first I must introduce you to value assignment.
You’ll remember that I said that variables are like Tupperware containers that we store values in. And I showed you how, using SELECT … INTO, we can put values into our variables. However, this is not the only – or even the most popular – way of assigning values in PL/SQL. That honour goes to the following symbol:
Here are some examples of it in use (assume that we have already declared our variables v_number, v_varchar2, v_date and v_boolean):
v_number := 12; v_number := 2 * 2; v_number := v_number + 21; v_varchar2 := 'Mike Tyson is a snotty little girl'; v_varchar2 := 'I am going to concatenate this string'||' with this one'; v_date := SYSDATE; v_date := SYSDATE + 365; v_boolean := TRUE; -- TRUE is a Boolean value; 'TRUE' is a varchar2 string. v_boolean := FALSE;
It is important to point out that the assignment symbol (:=) is completely different from the equals to sign in PL/SQL. In PL/SQL we use the equals to sign to create Boolean expressions (2+2=4 is TRUE); to assign values to variables we always use :=.
Let’s write an anonymous block in which we loop through all the addresses in our ADDRESS table and print out the names of the people who have ever lived there.
DECLARE v_string VARCHAR2(500); -- when declaring a varchar2 variable you must specify its size. This one can take 500 characters. BEGIN -- Loop round all our addresses. FOR i IN (SELECT * FROM address) LOOP v_string := 'Apartment '||i.apartment||', '||i.house_no||' '||i.street||', '||i.city||' '||i.zipcode; dbms_output.put_line(v_string); -- Now let's find everyone who has lived at this address. FOR j IN (SELECT fn.first_name, fn.last_name FROM friend_name fn, friend_address fa WHERE fa.address_id = i.address_id AND fn.friend_id = fa.friend_id) LOOP v_string := j.first_name||' '||j.last_name; dbms_output.put_line(v_string); END LOOP; END LOOP; END;
Did you notice how we just nested a loop inside another loop? That’s why, for every iteration of the Address loop (for which we have used the index i) we carry out multiple iterations of the Friend name loop (for which we’ve used the index j. When you nest loops you’ve got to use different indexes). Notice also how, to reference the columns selected in the loop, we precede them with the index name.
I promised you loops and hoops. It’s time to meet the hoops.
Conditional statements are, well, exactly what they sound like. We test a Boolean statement and, depending on if it proves to be TRUE or FALSE, we carry out some actions. If the weatherman says it’s going to rain, then we take an umbrella. If the time is not yet half past five, then we stay in the office. And if you’re paying, then of course I’m gonna have another drink!
The syntax for If statements in PL/SQL is as follows:
IF <Boolean statement> THEN <actions> END IF;
IF <Boolean condition> THEN <actions> ELSE <actions> END IF;
IF <Boolean condition> THEN <actions> ELSIF <Boolean condition> THEN <actions> ELSE <actions> END IF;
The kind of if statement you use depends, of course, on what you want to do. Sometimes you will want to carry out an action if a condition is true, and do nothing otherwise; other times you may want to carry out some actions if a condition is true, and other actions if it’s false.
An example might help. What if, rather than list the name of everyone who lives at an address in our previous anonymous block, we want to differentiate between people who currently live there and those who used to live there.
DECLARE v_address VARCHAR2(500); v_curr_resident VARCHAR2(32767); v_past_resident VARCHAR2(32767); BEGIN -- Loop round all our addresses. FOR i IN (SELECT * FROM address) LOOP v_address := 'Apartment '||i.apartment||', '||i.house_no||' '||i.street||', '||i.city||' '||i.zipcode; dbms_output.put_line(v_address); v_curr_resident := NULL; v_past_resident := NULL; -- Now let's find everyone who has lived at this address. FOR j IN (SELECT fn.first_name, fn.last_name, fa.moved_in, fa.moved_out FROM friend_name fn, friend_address fa WHERE fa.address_id = i.address_id AND fn.friend_id = fa.friend_id) LOOP IF (j.moved_out IS NULL OR j.moved_out >= SYSDATE) THEN -- current resident. IF v_curr_resident IS NULL THEN -- because the variable is null, we know this is the first current resident for this address. v_curr_resident := j.first_name||' '||j.last_name; ELSE -- this isn't the first current resident; concatenate so we don't overwrite the previous name. v_curr_resident := v_curr_resident||CHR(10)||CHR(9)||j.first_name||' '||j.last_name; -- chr(10) is a line break. chr(9) is a tab. END IF; ELSE -- past residents IF v_past_resident IS NULL THEN -- because the variable is null, we know this is the first current resident for this address. v_past_resident := j.first_name||' '||j.last_name; ELSE -- this isn't the first past resident; concatenate so we don't overwrite the previous name. v_past_resident := v_past_resident||CHR(10)||CHR(9)||j.first_name||' '||j.last_name; END IF; END IF; END LOOP; -- let's print out the names we've found. IF v_curr_resident IS NOT NULL THEN dbms_output.put_line(CHR(9)||'The following friends live at this address.'); dbms_output.put_line(CHR(9)||v_curr_resident); END IF; IF v_past_resident IS NOT NULL THEN dbms_output.put_line(CHR(9)||'The following friends used to live at this address.'); dbms_output.put_line(CHR(9)||v_past_resident); END IF; END LOOP; END;
So there you have it: hoops and loops. Conditional and iterative statements. PL/SQL, the procedural icing on the SQL cake.
But that’s not all there is to PL/SQL. Next time we’ll talk about functions and procedures. But for now, I’d like you to try writing a few more anonymous blocks. How about you loop through the records in the FRIEND_NAME table and print out your male and female friends in separate lists? And remember how I was talking about printing out a list of each person at each address and concatenating their names (as in Chandler and Monica)? I’ve run out of space, but why don’t you do it?