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.<\/p>\n
Might sound basic, but that\u2019s what all software code \u2013 from Angry Birds to the space shuttle \u2013 consists of: hoops and loops.<\/p>\n
Let me show you how.<\/p>\n
[*that wise man was me, by the way<\/i>]<\/p>\nPL\/SQL<\/h5>\n
But first I must introduce you to PL\/SQL.<\/p>\n
PL\/SQL is Oracle\u2019s 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\u2019s PL\/SQL that brings the hoops and loops to the carnival.<\/p>\n
SQL is great, but if we want to apply logic to our actions, we need more than it can give us, and that\u2019s where PL\/SQL comes in.<\/p>\n
Let me give you a scenario. You\u2019ve decided to throw a party and want to invite all your friends. You know you\u2019ve 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 \u201cI would like to invite Chandler to my party\u201d you would want to write \u201cI would like to invite Chandler and Monica to my party\u201d.<\/p>\n
SQL can\u2019t help you; you need PL\/SQL.<\/p>\n
Let me show you how.<\/p>\n
But first I must introduce you to anonymous blocks.<\/p>\n
Thus far \u2013 in SQL-land \u2013 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.<\/p>\n
Blocks are structured as follows:<\/p>\n
DECLARE\r\n <Variable declaration section> (Optional)\r\nBEGIN\r\n <Statements>\r\nEND;<\/pre>\nWe haven\u2019t talked about variables yet, so I\u2019d best tell you what they are. A variable<\/b> 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\u2019s residence, store that address_id in a variable,<\/i> and then use that variable to find the friend_id of anyone else who lived at that address.\u00a0 So variables, in effect, are kinda like Tupperware that you can store values in.<\/p>\n
In the declaration section of our block, we must define all the variables that we plan to use: if our block doesn\u2019t need any variables, this section does not need to exist.<\/p>\n
To define a variable, we must give it a name, tell Oracle its datatype and, optionally, initialize<\/i> it with a value (i.e. give it an initial value).<\/p>\n
The main body of our block starts with BEGIN and, obviously, must exist.<\/p>\n
Let\u2019s go back to our Joey Tribiani example and I\u2019ll show you what I mean.<\/p>\n
DECLARE\r\n v_addr_id\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER;\r\n v_joey_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER;\r\n v_friend_count \u00a0\u00a0NUMBER;\r\nBEGIN\r\n -- Find out Joey\u2019s address id and put it in our variable.\r\n SELECT fa.address_id, fn.friend_id\r\n INTO v_addr_id, v_joey_id\r\n FROM friend_name fn, friend_address fa\r\n WHERE fn.friend_id = fa.friend_id\r\n AND UPPER(fn.LAST_name) = 'TRIBIANI';\r\n\r\n \/*\r\n Now we have the address_id, find out how many other friends live there.\r\n We need to exclude Joey himself from our count, obviously.\r\n *\/\r\n\r\n SELECT COUNT(*)\r\n INTO v_friend_count\r\n FROM friend_address\r\n WHERE address_id = v_addr_id\r\n AND friend_id <> v_joey_id;\r\n\r\n -- The number of friends is in our variable. Print it out to the screen.\r\n DBMS_OUTPUT.PUT_LINE('The number of friends that live with Joey is '||v_friend_count);\r\n\r\nEND;<\/pre>\nThere are a few things I\u2019d like you to note.<\/p>\n
There are a few other, incidental, things that I would like you to note too:<\/p>\n
But what if we didn\u2019t want a count<\/i> of the people who have lived with Joey? What if we wanted to loop<\/i> through each one printing out their name?<\/p>\n
Let\u2019s start with a definition, even though you probably don\u2019t 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.<\/p>\n
When it comes to loops, Oracle have spoilt us for choice. It\u2019s like Ben and Jerry\u2019s Ice Cream \u2013 there\u2019s a flavour for every occasion and every taste. Let me show you.<\/p>\n
But first I must introduce you to Booleans.<\/p>\n
The Boolean \u2013 named after the great 19th<\/sup> Century mathematician George Boole, about whom I know absolutely nothing \u2013 is a datatype that represents a logical value: TRUE or FALSE. (Or NULL.)<\/p>\n 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<\/i> resolves to TRUE (I dare you to tell him he\u2019s a little girl to his face). And the statement 2 + 2 = 5<\/i> is FALSE. And you know those WHERE statements that we use in SELECTs, DELETEs and UPDATEs? They\u2019re just Booleans too: select\/delete\/update rows from a table where these conditions are TRUE.<\/p>\n Booleans, explicitly or implicitly, are all over PL\/SQL. Now that I\u2019ve told you about them, you\u2019ll start noticing them everywhere.<\/p>\n But, for now, let\u2019s get back to those loops of ours.<\/p>\n 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:<\/p>\n Now that we\u2019ve met the different types of loops, let me show you how to use them.<\/p>\n But first I must introduce you to value assignment.<\/p>\n You\u2019ll remember that I said that variables are like Tupperware containers that we store values in. And I showed you how, using SELECT \u2026 INTO, we can put values into our variables. However, this is not the only \u2013 or even the most popular \u2013 way of assigning values in PL\/SQL. That honour goes to the following symbol:<\/p>\n 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):<\/p>\n 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 :=.<\/p>\n Let\u2019s 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.<\/p>\n Did you notice how we just nested a loop\u00a0inside another loop<\/em>? That’s why, for every iteration of the Address loop (for which we have used the index\u00a0i<\/em>) we carry out multiple iterations of the Friend name loop (for which we’ve used the index\u00a0j.<\/em> 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.<\/p>\n 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<\/em> the weatherman says it’s going to rain, then<\/em>\u00a0we take an umbrella. If<\/em> the time is not yet half past five, then\u00a0<\/em>we stay in the office. And if<\/em> you’re paying, then\u00a0<\/em>of course I’m gonna have another drink!<\/p>\n The syntax for If statements in PL\/SQL is as follows:<\/p>\n The kind of\u00a0if<\/em> 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.<\/p>\n An example might help. \u00a0What 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\u00a0used to<\/em> live there.<\/p>\n So there you have it: hoops and loops. Conditional and iterative statements. PL\/SQL, the procedural icing on the SQL cake.<\/p>\n But that’s not all there is to PL\/SQL. Next time we’ll talk about functions and procedures. \u00a0But 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?<\/p>\n <\/p>\n 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\u2019s what all software code \u2013 from Angry Birds to the space shuttle \u2013…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48366],"coauthors":[],"class_list":["post-73207","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-beginners-guide"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73207"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73207\/revisions"}],"predecessor-version":[{"id":91692,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73207\/revisions\/91692"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73207"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Loops cont\u2019d<\/h5>\n
\n\n
\n Simple loops<\/td>\n LOOP<<actions to carry out>>EXIT;END LOOP;<\/td>\n 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.<\/td>\n<\/tr>\n \n While loops<\/td>\n WHILE <<Boolean expression>> LOOP<<actions to carry out>>END LOOP;<\/td>\n 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.<\/td>\n<\/tr>\n \n For loops<\/td>\n FOR indx IN 1 .. n<\/i> LOOP<<actions to carry out>>END LOOP;<\/td>\n A For Loop will run n<\/i> times.<\/td>\n<\/tr>\n \n Cursor loops<\/td>\n For (SELECT statement) LOOP<<actions to carry out>>END LOOP;<\/td>\n 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.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Value Assignment<\/h5>\n
:=<\/pre>\n
v_number := 12;\r\nv_number := 2 * 2;\r\nv_number := v_number + 21;\r\n\r\nv_varchar2 := 'Mike Tyson is a snotty little girl';\r\nv_varchar2 := 'I am going to concatenate this string'||' with this one';\r\n\r\nv_date := SYSDATE;\r\nv_date := SYSDATE + 365;\r\n\r\nv_boolean := TRUE; -- TRUE is a Boolean value; 'TRUE' is a varchar2 string.\r\nv_boolean := FALSE;<\/pre>\n
Loops cont\u2019d<\/h5>\n
DECLARE\r\n\tv_string\tVARCHAR2(500); -- when declaring a varchar2 variable you must specify its size. This one can take 500 characters.\r\n\r\nBEGIN\r\n\t-- Loop round all our addresses.\r\n\tFOR i IN (SELECT *\r\n\tFROM address) LOOP\r\n\t\tv_string := 'Apartment '||i.apartment||', '||i.house_no||' '||i.street||', '||i.city||' '||i.zipcode;\r\n\t\tdbms_output.put_line(v_string);\r\n\r\n\t\t-- Now let's find everyone who has lived at this address.\r\n\t\tFOR j IN (SELECT fn.first_name, fn.last_name\r\n\t\t\t\t\tFROM friend_name fn, friend_address fa\r\n\t\t\t\t\tWHERE fa.address_id = i.address_id\r\n\t\t\t\t\tAND fn.friend_id = fa.friend_id) LOOP\r\n\r\n\t\t\t\t\tv_string := j.first_name||' '||j.last_name;\r\n\t\t\t\t\tdbms_output.put_line(v_string);\r\n\t\tEND LOOP;\r\n\tEND LOOP;\r\nEND;<\/pre>\n
\n<\/i><\/p>\nConditional statements<\/h5>\n
IF <Boolean statement> THEN\r\n <actions>\r\nEND IF;<\/pre>\n
IF <Boolean condition> THEN\r\n <actions>\r\nELSE\r\n <actions>\r\nEND IF;<\/pre>\n
IF <Boolean condition> THEN\r\n <actions>\r\nELSIF <Boolean condition> THEN\r\n <actions>\r\nELSE\r\n <actions>\r\nEND IF;<\/pre>\n
DECLARE\r\n\tv_address\t\t\tVARCHAR2(500);\r\n\tv_curr_resident\tVARCHAR2(32767);\r\n\tv_past_resident VARCHAR2(32767); \r\n\r\nBEGIN\r\n\t-- Loop round all our addresses.\r\n\tFOR i IN (SELECT *\r\n\tFROM address) LOOP\r\n\t\tv_address := 'Apartment '||i.apartment||', '||i.house_no||' '||i.street||', '||i.city||' '||i.zipcode;\r\n\t\tdbms_output.put_line(v_address);\r\n\r\n\t\tv_curr_resident\t\t:= NULL;\r\n\t\tv_past_resident\t\t:= NULL;\r\n\r\n\t\t-- Now let's find everyone who has lived at this address.\r\n\t\tFOR j IN (SELECT fn.first_name, fn.last_name, fa.moved_in, fa.moved_out\r\n\t\t\t\t\tFROM friend_name fn, friend_address fa\r\n\t\t\t\t\tWHERE fa.address_id = i.address_id\r\n\t\t\t\t\tAND fn.friend_id = fa.friend_id) LOOP\r\n\r\n\t\t\t\t\tIF (j.moved_out IS NULL OR j.moved_out >= SYSDATE) THEN -- current resident.\r\n\t\t\t\t\t\tIF v_curr_resident IS NULL THEN\r\n\t\t\t\t\t\t\t-- because the variable is null, we know this is the first current resident for this address.\r\n\t\t\t\t\t\t\tv_curr_resident := j.first_name||' '||j.last_name;\r\n\t\t\t\t\t\tELSE\r\n\t\t\t\t\t\t\t-- this isn't the first current resident; concatenate so we don't overwrite the previous name.\r\n\t\t\t\t\t\t\tv_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.\r\n\t\t\t\t\t\tEND IF;\r\n\t\t\t\t\tELSE -- past residents\r\n\t\t\t\t\t\tIF v_past_resident IS NULL THEN\r\n\t\t\t\t\t\t\t-- because the variable is null, we know this is the first current resident for this address.\r\n\t\t\t\t\t\t\tv_past_resident := j.first_name||' '||j.last_name;\r\n\t\t\t\t\t\tELSE\r\n\t\t\t\t\t\t\t-- this isn't the first past resident; concatenate so we don't overwrite the previous name.\r\n\t\t\t\t\t\t\tv_past_resident := v_past_resident||CHR(10)||CHR(9)||j.first_name||' '||j.last_name; \r\n\t\t\t\t\t\tEND IF;\r\n\t\t\t\t\tEND IF;\r\n\r\n\t\tEND LOOP;\r\n\r\n\t\t-- let's print out the names we've found.\r\n\t\tIF v_curr_resident IS NOT NULL THEN\r\n\t\t\tdbms_output.put_line(CHR(9)||'The following friends live at this address.'); \r\n\t\t\tdbms_output.put_line(CHR(9)||v_curr_resident);\r\n\t\tEND IF;\r\n\r\n\t\tIF v_past_resident IS NOT NULL THEN\r\n\t\t\tdbms_output.put_line(CHR(9)||'The following friends used to live at this address.');\r\n\t\t\tdbms_output.put_line(CHR(9)||v_past_resident);\r\n\t\tEND IF;\r\n\tEND LOOP;\r\nEND;<\/pre>\n
Conclusion<\/h5>\n
Next Article:\u00a0Oracle for Absolute Beginners: Part 6 \u2013 Procedures and Functions<\/a><\/h3>\n","protected":false},"excerpt":{"rendered":"