Oracle for Absolute Beginners David Njoku

Copyright David Njoku 2014 The right of David Njoku to be identified as the author of this work has been asserted by him in accordance with the Copyright, Designs and Patents Act 1988. All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written consent of the publisher. Any person who does any unauthorized act in relation to this publication may be liable to criminal prosecution and civil claims for damages. This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent, resold, hired out, or otherwise circulated without the publisher's prior consent in any form other than that in which it is published and without a similar condition including this condition being imposed on the subsequent publisher.

Introduction Alongside my day job as an Oracle database and applications developer, team leader and general evangelist, I have moonlighted for many years as a technical author, writing not only for my personal site – Dancing and Wrestling with Oracle Apex – but also as an expert contributor for All Things Oracle and Simple Talk. However, in recent years I have grown disillusioned with the echo chamber of experts producing dry, technical essays to be consumed only by other experts. Who was writing books to introduce new converts to Oracle programming? That is what this book attempts to do. Written in a deliberately light-hearted manner, it covers the basics of Oracle programming, breaking down complex topics, providing practical real-world examples. This work began life as a series of articles on All Things Oracle – and, indeed, is still available for free on the site, albeit in a slightly rougher form. It was the enthusiastic response that the series received that convinced me to expand upon it and compile it into this book. As the title of the book states, it is aimed at absolute beginners. Unlike most Oracle books, this one does not assume any previous programming knowledge. The only prerequisite is a willingness to work at a number of exercises. Do this and, by the end of the book, you should have a solid foundation in Oracle programming, from the basics of database design to the complexities of PL/SQL. David Njoku Berkshire, 2014

Chapter 1: Databases An expert, a wise man* once said, is someone who uses big words and acronyms where simple phrases would do just as nicely. So stand back and listen to this: Database, Relational Database, DBMS, RDBMS, SQL, Sub-queries, normalization. [* that wise man was me.] So now that I’ve established my credentials by bamboozling you with arcane words and capital letters, let me tell you what the purpose of this book is. By the end of it, you will be able to re-read that first paragraph and understand every word; or, if you would prefer that in more practical terms, you will be able to read – and write – SQL, which is the programming language of databases.

Definitions Let’s meet the main characters of our story: I’ll give you a couple of definitions; one building on the other. A Database is an organised collection of data. Not yet sure what that means? Well, do you own an address book, either on your phone or in a physical book? That’s a database. After all, the addresses and phone numbers are organised – with all friends whose names start with A being grouped separately from people whose names start with B or C or D. A Relational Database is a database in which the data is organised according to type with the relationships being maintained between the differing types. Okay, that sounds a bit like Greek (or Dutch, if you’re Greek; or Tagalog, if you’re Dutch; or Xhosa if you’re Tagalog…), but it makes sense if you let me explain. Dig out your address book again. Imagine all the names grouped together; and all the phone numbers grouped together in another list; and all the addresses in a third. On their own these individual lists might be interesting but not useful; but if we establish the relationship between the lists – this address is where that person lives and that’s their phone number – then our database takes shape. Make sense? Don’t worry about it too much if it doesn’t; we’ll come back to it a little later. Let’s talk about Oracle now.

Oracle You’ve probably heard the word Oracle mentioned in discussions about databases, but you possibly do not know that Oracle is a corporation. It makes software to create and manage databases – socalled Database Management Systems. That’s the DBMS acronym from way back in paragraph 1; and an RDBMS is, of course, a Relational Database Management System.

Oracle began making RDBMS in the 70s. Today, the Oracle database is, by most metrics, the most popular in the world (it does have some strong competition; we’ll talk about them later. This isn’t like football; now you’re part of #TeamOracle, it doesn’t mean you have to hate the opposition. Not much, at least). The latest version of the database is Oracle 12C. You don’t particularly need to remember that now – in fact, you don’t particularly need to remember anything from this section. We’re just painting in the background; the juicy stuff is what comes next.

Databases It’s time to roll up our sleeves and get our hands dirty. Go get your address book again. Remember I’d said the data in databases is organised in groups – all the names over here, the phone numbers over there, the addresses over in that other place? Well, those groupings are called tables. So in our little database we have a FRIEND_NAME table, a PHONE_NUMBER table, and an ADDRESS table. Got that? Cool. Tables are made up of vertical columns and horizontal rows. The columns contain data of the same type; while rows contain the data that makes up an item. In our example FRIEND_NAME table, the Last_Name column contains all the surnames – Geller, Bing, Tribiani, Geller-Bing, Green and Buffay – while the rows contain the full names, such as Ross Geller.

Our database will be pretty boring – and not relational – if it contained only one table. Let’s knock up our PHONE_NUMBER and ADDRESS tables.

Figure 1: PHONE_NUMBER

Figure 2: ADDRESS

Data Types You will have noticed that we’ve got different types of data in our tables – from the PHONE_NUMBER table that contains nothing but numbers to FRIEND_NAME and ADDRESS that both contain character strings, numbers and, in the case of the ZIPCODE column, a combination of both. The Oracle database needs to know the types of all the data you keep. (That way, for instance, if you ask it to subtract the value in the ADDRESS.CITY column from the value in the ADDRESS.HOUSE_NO column, it’ll be able to tell you that you’re crazy.) There is a long list of data types that Oracle recognises, but we’ll only focus on the 3 main types.

NUMBER: This one’s self-explanatory. If a column is created as a NUMBER column, only numbers can be stored in it. It can be whole numbers, decimals, negative or positive.

VARCHAR2: Okay, this one’s a little weird. There’s a lot of history packed into the name of this data type; however, it’s mostly boring, so I won’t go into it. What you need to know is that it stands for VARiable CHARacter and is the data type required to store character strings, such as the data in FRIEND_NAME.FIRST_NAME, FRIEND_NAME.MIDDLE_NAME and FRIEND_NAME.LAST_NAME. There is one interesting difference between the VARCHAR2 and NUMBER data types, and that is that you can only store numbers in NUMBER columns; however, you can record any string of alphanumeric characters in VARCHAR2 columns. For example, with its combination of numbers and letters, we cannot record ADDRESS.ZIPCODE in a NUMBER column, but we can save it as a VARCHAR2.

DATE: Another self-explanatory data type. We haven’t used any dates in our hypothetical database thus far – but we will; I’m saving that pleasure for later.

The One About Primary Keys I’ve got another term for you: Primary Key. A primary key is a key – a column or combination of columns – that uniquely identifies a row. Let me explain. Say one day, you’re chilling out at a café and you start chatting with a stranger. Turns out you’ve got lots in common and you really like that joke they told about a politician, a monkey and a water pistol. When eventually, you rise to leave, you exchange names and numbers and promise to stay in touch. They say their name is Ross Geller. You add it to your address book. But you already had a friend named Ross Geller! How will you know which is which when you want to phone them up and laugh about the monkey joke again?

That’s where primary keys come in. Names – even rare ones like Ross Geller - do not uniquely identify a record, so we need something that does. In our NAME table it is the number in FRIEND_ID. We simply need to give the new row, the new Ross, a new – unique – number in the FRIEND_ID column. Databases rule the world, and thus, primary keys are all around us. Your passport number, your social security number, your phone number, the number on your driving license – they’re all primary keys, they all uniquely identify you - and only you - in those organisations' databases.

Relational Databases We now have all the pieces of the puzzle. We can now redefine – and understand – relational databases. A Relational Database is a database in which the data is organised in tables with the relationships being maintained between the different tables. Our database has a table for names, another for phone numbers, and a third for addresses. However, there is no way of knowing which of our friends lives at what address and when, or what their phone number might be. We’ve built a database, but it’s not yet relational. Let’s create two further tables that address that problem.

Figure 3: FRIEND_ADDRESS

Figure 4: FRIEND_PHONE

Take a minute to study the tables. Notice how useful primary keys are? Instead of typing out the friend’s name in full or typing the full address, all we need are the primary keys. And so, armed with our burgeoning knowledge of databases, we can look at the following:

And after relating this table to the FRIEND_NAME and ADDRESS tables, we know that it is saying between September 1994 and October 2000, Chandler Bing lived at Apartment 19, 90 Bedford Street, New York, NY 10014. And the reason we know that is because we now implicitly understand the concept of foreign keys. Here’s a definition: A Foreign Key is a column (or combination of columns) that uniquely identifies a row in another table. Foreign keys are the invisible threads that knit all the tables in our database together. It is the foreign keys, telling us how the rows in one table are related to the rows in another table, that turn a database into a relational database. It is the foreign key that takes data and begins to turn it into information. Without foreign keys, a database is like a dull room, full of bored people. With foreign keys, it’s a party.

Recap Here’s what I would like you to remember: what is a database? What is a relational database? What are tables, columns and row? What are the main data types? What are primary keys and foreign keys? Got that? Great. In the next chapter, we’ll be learning SQL, the language of databases.

Chapter 2: SQL A wise man* once said, no one’s ever learned how to cook just by reading recipes. And so, since we painted in the background in Chapter 1, we are now going to roll up our sleeves and dive in. By the end of this chapter you'll be reading and writing the lingua franca of databases - SQL - like a native. [* that wise man was me.]

SQL SQL stands for Structured Query Language (pronounced ess-cue-ell or sequel) and is the programming language used in the management of relational databases. And not just Oracle RDBMS; the code we are about to learn will work just as well with Microsoft’s SQL Server, IBM’s Informix, MySQL and dozens of others. SQL is very much the "English" of the database world; it is spoken in many environments. This is one reason why the skills you are about to learn are very valuable; they are eminently transferrable. SQL consists of a data definition language (DDL) and data manipulation language (DML). What this means is that we use SQL not only to define the tables into which we plan to put our data, but to manipulate (query, edit, delete, stuff like that) the data once it’s in place. Manipulating data using SQL is easy, as the syntax isn’t a million miles from the way we speak. For instance, to select all the data from a table you would use the SELECT … FROM table_name command. If, on the other hand, you wanted to update data, you’d use the UPDATE command; and the DELETE and INSERT commands pretty much do what you’d expect them to, too. It’s easy. Let me show you.

Creating An Environment Obviously we can’t write database code without first having a database, so we’re going to have to take something of a detour here and set ourselves up with an Oracle database that we can use. We’ve got options: we can download one from the Oracle.com website. Only problem with that choice is that I like you and I don’t want to put you through the torture of installing a database on our second date. So I’d recommend that we go for option 2, and use one of the hosted workspaces that Oracle makes available on their Apex.Oracle.com website. (Apex – or Application Express – is this really great software development tool. It is beyond the scope of what we’re doing now, but I’d recommend that you look into it when you’re more confident with your SQL and PL/SQL. Now, however, we’ll just take advantage of the database space available for free on the Apex website.) Requesting and setting up a workspace is reasonably straightforward, no harder than setting up a Facebook profile. You’ll need an email address and a name for your workspace and schema (a schema name is reasonably analogous to a username) – and no, unlike Facebook, you don’t need to trawl through all your photos looking for a selfie in which you’re not making a silly face to use as your profile pic. Once you’ve created a workspace and logged in, you’ll arrive at a screen with a number of choices; for our purposes, I need you to click the SQL Workshop button. (The rest are to do with building

Apex applications.)

We now have, effectively, what is pretty much an empty database (it’s not completely empty; there are some demo tables). Before we can really start tearing into SQL, we need to create our tables. I’ve put together a script (Oracle_For_Absolute_Beginners.sql) that will create and populate the objects we require, and that I’ll need you to run. To do this, click on the SQL Scripts button, upload the file, and run it. Once that is done, go back to the SQL Workshop: this time click the Object Browser button. You’ll notice that in amongst the demo and Apex tables are our tables – FRIEND_NAME, ADDRESS, PHONE_NUMBER, FRIEND_ADDRESS and FRIEND_PHONE. Go on, click on them: go to the Data tab; you should recognise the records. All there? Good. Now, roll up your sleeves; we’re going to write some SQL.

SQL Cont’d Let’s talk data manipulation: there are four main ways in which we can manipulate our data – we can SELECT it, we can INSERT new data, we can DELETE data, or we can UPDATE it. I’ll get us started with SELECT. Imagine you decide to enter a team into this all-male basketball tournament, and you’re wondering if you actually have enough male friends. Here’s how you find out:

SELECT first_name, middle_name, last_name FROM friend_name WHERE gender = 'M';

Click on the SQL Commands button, and paste the above code into the upper window. Hit run. You should see a list of all your male friends. Chandler’s probably really rubbish at basketball, but that’s not the point. You can probably see a clear line from the code to the resultset, but it’s important that we study the syntax of SELECT statements to find out how they’re stitched together.

SELECT FROM

WHERE ;

The required keywords are the SELECT – which must be followed by the name of one column, a comma-separated list of columns or an asterisk (*) which indicates that you want ALL columns – and the FROM – which must be followed by one table name or a comma-separated list of tables. The WHERE clause isn’t mandatory; if you do not apply any conditions to your query, it’ll return ALL records. Here’s what I mean. You take a look at your male friends, realise that Chandler Bing couldn’t play ball to save his life, and decide that you need to build your team from all your friends, not just the male ones.

SELECT * FROM FRIEND_NAME;

Notice the difference? This time we use the asterisk (*) in the SELECT clause indicating that we want all columns; and we do not include a WHERE clause, indicating we do not want to limit our resultset with any criteria. Oh, and did I forget to mention that you must end your statement with a semi-colon? Yeah, that’s mandatory for all SQL statements. We’ll talk a bit more about the WHERE clause a little later, but I should stop hogging the limelight and let you have a go. Let’s say you need to view all the addresses your friends live at. Write a query to select all the columns from the ADDRESS table (hint: you don’t need a WHERE clause for this). Done that? Good work. Now, try writing another query selecting only the HOUSE_NO and STREET columns from the ADDRESS table (hint: to select specified columns you’ll need to put them in a comma-separated list in the SELECT clause). Hopefully you’ve managed that and are now looking, slightly smugly, at a list of addresses. However, what if you did not want a list of ALL addresses; what if you only wanted a list of addresses at House 90? Then we’ll need to write a WHERE clause telling Oracle not to return ALL the records in the table, but only those matching the condition we set. Let me show you what I mean.

SELECT HOUSE_NO, STREET FROM ADDRESS WHERE HOUSE_NO = 90;

The WHERE clause comes after the FROM clause and is made up of one or more conditions that may be true or false with Oracle returning all records that evaluate as true. Talking about where clauses gives me the opportunity to mention a few other things that you’ll want to remember. I’ll group these facts according to the different data types.

VARCHAR2: o

o

If you want to compare a VARCHAR2 character string you must enclose it in single quotes. (Where street ='Bedford Street' is correct; where street = “Bedford Street” is not.) You must use the straight quote marks ('); if you use curly quotes, Oracle will error. Comparisons of varchar2 strings are case-sensitive. (Where street = 'Bedford Street' is not the same thing as where street = 'BEDFORD STREET'.) Often you will not want your query to be so finicky with cases; in those instances you can use the following function: where UPPER(street) = UPPER('Bedford Street'). What this does, is convert both strings to upper case. (You can use the LOWER function – where LOWER(street) = LOWER('Bedford Street') – which converts them to lowercase).

NUMBER: o o

o

You do not need to enclose numbers in quotes to compare them. That is why, in our example, we could say where house_no = 90; The equals sign isn’t the only operator you can use with numbers (or other data types). All the following also make sense: o House_no < 90; o House_no <= 90 o House_no > 90; o House_no >= 90; o House_no != 90; – which, in case you’re unsure, means the house number is NOT equal to 90. You can also use ranges in your comparisons. If, for example, you knew the house number you were looking for was above (or equal to) 80 but below (or equal to) 100, there are two ways you could write your query.

SELECT HOUSE_NO, STREET FROM ADDRESS WHERE HOUSE_NO >= 80 AND HOUSE_NO <= 100;

However, the following works just as well:

SELECT HOUSE_NO, STREET FROM ADDRESS WHERE HOUSE_NO BETWEEN 80 AND 100;

Got that? There is one more concept that I would like to discuss, and that is the NULL. A NULL is the term we use to describe something that is undefined, that has no value. It is not the same thing as the number 0 (because 0 itself is a value); it is undefined, nothing. Look at the data in our FRIEND_NAME table again (navigate to it by going back to the SQL Workshop screen and clicking the Object Browser button). Some of our friends have middle names: Joey’s middle name is Francis, but Phoebe’s middle name is undefined, nothing. It is NULL. MIDDLE_NAME is a VARCHAR2 column, but you can have nulls in all types of columns from VARCHAR2 to NUMBER to DATE. Because nulls have no value, the usual comparison operators (=, >, < and the rest) do not work with them (think about it; how can anything be equal to or greater than something that is undefined?). For this reason, if we wanted to write a query to return all our friends who have a middle name, we would have to phrase it as follows:

SELECT * FROM FRIEND_NAME WHERE MIDDLE_NAME IS NOT NULL; Conversely, if we wanted to return only those who do NOT have a middle name, it would be:

SELECT * FROM FRIEND_NAME WHERE MIDDLE_NAME IS NULL; The IS NULL and IS NOT NULL operators work with columns of all data types – VARCHAR2, NUMBER and DATE. Speaking of the Date data type, I know I should now speak about the operators that work with them, but I would rather we go back to discussing Where clauses. Once we’re old pros with them, we’ll return to Dates. You may have noticed that we can have more than one condition in our Where clauses. In fact, using ANDs and ORs, we can build rather complex Where statements. I’ll show you what I mean. We’ve already established that your friend Chandler is rubbish at basketball. So when you see a flyer for an all-female cheerleading team, you think it’ll be funny to send it to all your female friends and to

Chandler. But how do we write a query that’ll give us all the names of your female friends – and Chandler. Here’s how.

SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME FROM FRIEND_NAME WHERE GENDER = 'F' OR UPPER(FIRST_NAME) = 'CHANDLER';

You might want to pay attention to how we’ve used the OR to link two conditions in our Where clause. It’s also worth noting how we’ve used the UPPER function to make our query case-insensitive. We’ve talked about the SELECT clause (in which we list the columns we want our query to return), the FROM clause (in which we list the table(s) we want to query) and the WHERE clause (in which we apply conditions to our resultset). There is one further clause that we need to discuss – the ORDER BY clause. Using the order by clause we can sort the resultset according to rules we tell Oracle. Run the two following queries and notice the difference.

SELECT LAST_NAME, FIRST_NAME FROM FRIEND_NAME; SELECT LAST_NAME, FIRST_NAME FROM FRIEND_NAME ORDER BY LAST_NAME;

You can use more than one column in your ORDER BY clause; if you do, Oracle will sort your resultset using the first named column and, in cases where the values in the first column are identical, will sort by the second column (and then the third etc). You can also instruct Oracle to order your resultset in descending order (the default order is ascending).

SELECT LAST_NAME, FIRST_NAME FROM FRIEND_NAME WHERE MIDDLE_NAME IS NULL ORDER BY LAST_NAME DESC;

Please note that, as in the example above, your ORDER BY clause must be the final clause, coming after your WHERE clause (if you have one).

Conclusion This is all getting very exciting, but I think we should stop and take a breath here. We’ve learned how to construct select statements, modify the results we get and control its order. In the next chapter we’ll talk about adding data to our tables, deleting it, modifying it, and writing select statements that get data from more than one table. But just so you don’t miss me too much in the meantime, here are some teasers for you to wrap your head around. o o o o o o

Write a query selecting the first name, middle name and last name of all your male friends and any friend named Phoebe. Make your query case-insensitive. Amend the query you’ve written above to order the result set by your friends’ genders. Find out if you can order using a column that you have not selected. Write a query selecting all columns in the PHONE_NUMBER table for records with a phone_id between 2 and 6. Using the phone_number column, rearrange your resultset in descending order. Rewrite your query to select only the phone_number column for all records with a phone_id that is greater than or equal to 2. List the middle name and last name of all your friends who have a middle name, in descending order of surname. Write a query to find out if you have any friends that do not have a last name.

Chapter 3: Update, Insert, Delete A wise man* once said: So no-one told you life was gonna be this way. Your job’s a joke, you’re broke, your love life’s DOA. It’s like you’re always stuck in second gear. And when it hasn’t been your day, you week, your month, or even your year. [*That wise man wasn’t me; but stick with me, I promise to bring this back around to Oracle SQL imminently.] So – since it hasn’t been your day, your week, your month, or even your year – you decide to go to Central Perk with your friends Ross, Rachel, Monica, Phoebe, Chandler and Joey. Oh, and that new friend you made that’s also named Ross Geller. The music is loud, the atmosphere is great, and a good time is had by everyone. You wake up the next morning with a bit of a sore head and try to recall everything that went on the night before: you’d discovered that Chandler’s middle name is Muriel, and that Ross’ is Eustace. Rachel gave you her new phone number. Phoebe gave you her new address. Oh, and you had a big fight with new Ross and decided you no longer want to be friends with him. You roll out of bed and groggily wipe your eyes: all this new information needs recording; you’d better fire up your database.

UPDATE Since we thoroughly looked into SELECT statements in the previous chapter, we can now turn our attention to UPDATE statements. In SQL we use update statements to change existing records – not to create new records or to delete them – just to change them. The syntax for update statements is as follows:

UPDATE SET = WHERE ; To see an update statement in action, log into your Oracle environment and run a query to select Chandler’s middle name (middle names are stored in the MIDDLE_NAME column) from the FRIEND_NAME table. You should get a null. Now run the following update statement:

UPDATE friend_name SET middle_name = 'Muriel' WHERE UPPER(first_name) = 'CHANDLER';

Done that? Now try running your select statement querying his middle name again. We also need to update Ross Geller’s middle name to Eustace. Knowing our data as intimately as you do by now, can you tell me what’s wrong with running the following update statement?

UPDATE friend_name SET middle_name = 'Eustace' WHERE UPPER(first_name) = 'ROSS' AND UPPER(last_name) = 'GELLER';

Figured what’s wrong with it yet? It’s not the syntax; that’s as right as rain. No, the issue – in this specific case – is that our WHERE clause identifies two people, since we’ve got two friends named Ross Geller. Copy that where clause into a select query and you’ll see that it returns more than one record. However, we only want to update one record, since we only have one friend named Ross Eustace Geller. Forgive me for sneaking in a lesson about primary keys when we’re talking about update statements, but this is why it is doubly important to use primary key columns in your where clauses when updating (or deleting) records – cos if we do not uniquely identify the records we want to change (or delete), the consequences can be critical. So, now that we’ve reinforced that point, I should probably rewrite our update statement as:

UPDATE friend_name SET middle_name = 'Eustace' WHERE friend_id = 1;

Let’s try a couple more updates. It turns out Monica goes by the last name Bing and really hates being called Geller-Bing. She also mysteriously reveals that her middle name is … E. Just E. We could, of course, write two update statements – one for her surname and the second for her middle name. However, with SQL it is possible to update more than one column in a single update statement as long as the columns are in the same table. We simply need to separate the columns in the SET clause with a comma. Let me demonstrate:

UPDATE friend_name SET middle_name = 'E', Last_name = 'Bing'

WHERE friend_id = 4;

This method works just as well for three, four or however many columns; do bear in mind, however, that SQL will use the same WHERE clause to identify the record(s) to update.

INSERT Rachel has a new phone number. I don’t know how well you remember our database structure, but to record this information we will need to add the new number to the PHONE_NUMBER table (this will call for an INSERT statement), and then add a record (another INSERT) to the FRIEND_PHONE linking this new number with Rachel. Let’s add the phone number. She says her new number is 5550789. Assuming the country and area codes are unchanged and the phone id is 9, our insert statement would be as follows:

INSERT INTO phone_number

(phone_id, country_code, area_code, phone_number)

VALUES (9, 1, 212, 5550789); The syntax for a basic insert statement is as follows:

INSERT INTO () VALUES ( );

The number of values in the comma-separated value list must match the number of columns in the column list. Obviously. Got that? Good. Then maybe you should have a go. Last night, Phoebe mentioned that she’d moved apartments (again!) and has a new address. It is Apartment 10, House 12, Morton Street, New York City, New York, NY 10014. Create an insert statement to put a new record (with an address_id of 4) into the ADDRESS table. You’ll want to remember that you’ll need to enclose VARCHAR2 strings in single quotes.

DATES And now, just to keep you on your toes, I’m going to interrupt our conversation about insert statements to keep a promise I made you to tell you more about dates. (Don’t panic, we’ll return to inserts once this detour is over.) The problem with dates, if you recall, is how to use them in SQL; if you enclose them in single quotes they’ll be taken as VARCHAR2 strings and if you don’t they might be mistaken for numbers. The trick is to take a character string and tell SQL to convert it to a date; to do that we’ll need to use the TO_DATE function. The syntax is as follows:

TO_DATE(,)

The is the character string that we want converted to a date, and the is the pattern it matches. So if I wanted to use a date – say 25 March, 1999 – in a where clause or an insert statement, I might write the following: TO_DATE(’25/03/1999′,’DD/MM/YYYY’) Alternatively, if I were American, I might write TO_DATE(’03/25/1999′,’MM/DD/YYYY’) Or I could say TO_DATE(’1999-03-25′,’YYYY-MM-DD’) You get the idea. As long as the format mask tells SQL what pattern we’re using, Oracle doesn’t really limit us. Let me show you how we might use the to_date function in a select statement. If I wanted a list of all my friends who’d been living at the same address since 25 March, 1999, I might say:

SELECT FRIEND_ID FROM FRIEND_ADDRESS WHERE MOVED_IN >= TO_DATE('25/03/1999','DD/MM/YYYY');

INSERT Cont’d Let’s get back to Rachel. We were recording the fact that she has a new phone number. We’ve inserted the phone number into the PHONE_NUMBER table with a phone_id of 9; now we need to insert a row into FRIEND_PHONE, using what we’ve learned about dates.

INSERT INTO FRIEND_PHONE (friend_id, phone_id, start_date) VALUES (5, 9, TO_DATE('01/01/2014','DD/MM/YYYY'));

We also need to insert a row into FRIEND_ADDRESS to record the fact that Phoebe has a new address. Her friend_id is 6 and the address_id is 4. Using any date of your choosing in the MOVED_IN column, write the insert statement.

DELETE The syntax for delete statements is:

DELETE FROM WHERE ; Since you’ve had a big bust-up with the other Ross Geller, you’ve decided to delete his name from your address book. Using the syntax above – and remembering to use the primary key identifier instead of the name – the statement we need is:

DELETE FROM friend_name WHERE friend_id = 7; Copy the statement, paste it in your SQL window, click the Run button, and…

ERRORS Did you get the following error?

ORA-02292: integrity constraint (ALLTHINGSORACLE.FRIEND_PHONE_FRIEND_FK) violated - child record found

I’m sorry; I set you up. The truth is, we cannot honestly speak about coding without talking about errors. Every programmer – no matter how good she is – runs up against errors, often quite frequently. So it is important that you learn to understand – and not fear – them.

Oracle errors usually begin with an ORA-, followed by a numeric code and a description. If you find the description to be inadequate, try googling the error code, there are numerous sites where you can plug in the error number and receive advice on what to do next. Our error – ORA-02292 – tells us that an integrity constraint has been violated because a child record has been found. What this means is that we cannot delete Ross from our FRIEND_NAME table when there are records that depend on him (child records) in another table.

DELETE Cont’d Before we can delete Ross Geller from FRIEND_NAME, we must delete the child record from FRIEND_PHONE. Using his friend_id (7), why don’t you write and run a delete statement to do that using the syntax we learned earlier? And once you’ve done that, you can try deleting him from FRIEND_NAME again.

CONCLUSION And that’s it. We’ve covered the basics; we know how to select, update, insert and delete. Plus, we know how to manipulate the various data types and how to stare down error messages. When I was a child there was a cartoon series on TV named Voltron: Defender of the Universe in which there were 4 robot lions who, individually, were pretty good fighters. But when things got tough they would come together to form one giant robot who was pretty much undefeatable. Think of what we’ve learned so far – select, update, insert, delete – as our four robot lions. In the next chapter we’re gonna join them together and build our humongous unbeatable robot of advanced selects, joins, sub-queries, functions and procedures. I. Cannot. Wait.

PRACTICE Here are a few teasers for you to get your teeth into. o o o o

o

Since we’ve added a new phone number for Rachel (friend_id 5) we need to update our records to indicate that she’s no longer using her old phone number. Update FRIEND_PHONE to do this. Since Phoebe (friend_id 6) has moved to a new address, we need to update our records to indicate that she no longer lives at her old address. Update FRIEND_ADDRESS to do this. Try inserting the details of your real-world friends into the FRIEND_NAME table. Remember that each record must have a unique friend_id. Insert some phone numbers into the PHONE_NUMBER table, and then insert records into FRIEND_PHONE associating them with your friends, ensuring that you put dates in the START_DATE column. Delete some of the records you have just added.

Chapter 4: Multi-table Queries A wise man* once said: To build the Great Wall of China, you must start with a brick. In previous chapters we acquainted ourselves with our bricks; now it’s time to build. If we consider what we’ve learned so far – Select, Update, Insert, Delete – as unicellular organisms, what we’re about to do next is multicellular, big and beautiful – it’s like going from a bacterium to a bee, a butterfly, to Beyoncé. [*that wise man was me] Consider a real-world requirement that we might have of our Addressbook database; since it contains a list of our friends and their phone numbers, we will naturally want to see a list of their names and their phone numbers. Ah, but that presents a problem. Our friends’ names are in the FRIEND_NAME table, while their phone numbers are in the PHONE_NUMBER table. And complicating things further, we can only tell which number belongs to which friend by looking in the FRIEND_PHONE table. Aaargh!

Joins We could, of course, get the information by running a series of queries: one select to find our friends’ names and their friend_id; a second to find the phone_id of the phone number linked to each friend in FRIEND_PHONE; and a third query to find the number from PHONE_NUMBER using the phone_id we identified in our second query. So yes, it can be done. But hey, you can probably ride a unicycle across Siberia – but that didn’t stop them from inventing the car. What we need are joins. We need a select statement that can query multiple tables at the same time. The syntax for a multi-table select statement is as follows:

SELECT FROM WHERE

Let me translate that into an actual query for you.

SELECT first_name, last_name, phone_number FROM friend_name, friend_phone, phone_number;

Run that and see what you get. You’ve probably already guessed that I’m setting you up, but it’s important that we make these mistakes now, so we can learn about them. The above query will give you many, many rows that look identical; however, if you replace the column list with an asterisk (*)

and rerun the query, you’ll notice that the records aren’t exactly identical, each has one column different. What we have here is a Cartesian Product, and you’ve probably already guessed that it has something to do with our missing Where clause. You’re right. Whenever we don’t tell Oracle how our tables are related to each other it simply joins every record in every table to every record in every other table. This doesn’t only happen when our Where clause is completely missing; the same thing would happen if we were joining 3 tables, but only included 2 in our Where clause or if we joined the tables ambiguously (always join using key columns where possible). So if you ever notice that your query is returning more rows that you anticipated, look for a Cartesian join. Got that? Good, let’s continue. Run the following statement:

SELECT friend_id, first_name, last_name, phone_number FROM friend_name, friend_phone, phone_number WHERE friend_name.friend_id = friend_phone.friend_id AND friend_phone.phone_id = phone_number.phone_id;

I set you up again; sorry. Running the query will result in the following error:

ORA-00918: column ambiguously defined

The issue here is that if two or more columns of the same name (such as friend_id in our select list) exist in two or more of our tables, we must always tell Oracle which one we are referring to every time we use that column name. We do this by preceding the column name with the table name. In fact, it is good practice to do this with all columns in a multi-table statement. Makes it easier to read. Although if you had to type out the full table names each time you referred to a column in a long statement, you’ll soon be in hospital with RSI. To get around this problem, you can use table aliases. These are short nicknames you can give to tables (to do this you follow the table name immediately with the alias in your From statement). Let me rewrite our query to illustrate this:

SELECT FN.friend_id, FN.first_name, FN.last_name, PN.phone_number FROM friend_name FN, friend_phone FP, phone_number PN WHERE FN.friend_id = FP. friend_id AND FP.phone_id = PN.phone_id ORDER BY FN.first_name, FN.last_name;

Run the above statement. It’s perfect, isn’t it?

SYSDATE Not quite. My data and yours might now be very different because of all the practising you’ve been doing (you have been practising, haven’t you? This book might be good, but you may as well be reading a Dan Brown novel if you don’t put what you’re learning into practise). However, when I run the query, I get 2 rows for Rachel – one for her current phone number and another for her previous one. But how can we tell which is which? Of course we can simply add fp.start_date and fp.end_date to our column list and look to see which record is current. Alternatively, we can add the condition “AND FP.END_DATE IS NULL” to our Where clause and exclude end-dated rows in that way. But what if Rachel had told us that she was changing her phone number next month and we’d put in a future end-date? Excluding all end-dated rows now would give us the wrong result. What we need to do is query against today’s date. But we don’t want to hard-code the date into our query, in case we want to rerun the query next week. What we need is SYSDATE.

Run the following query:

SELECT SYSDATE FROM DUAL;

It should return today’s date. And no matter which day you run it, it should return the correct date. Now we can rewrite our query as:

SELECT FN.friend_id, FN.first_name, FN.last_name, PN.phone_number FROM FRIEND_NAME FN, FRIEND_PHONE FP, PHONE_NUMBER PN WHERE FN.FRIEND_ID = FP.FRIEND_ID AND FP.PHONE_ID = PN.PHONE_ID AND (FP.START_DATE IS NULL OR FP.START_DATE<= SYSDATE) AND (FP.END_DATE IS NULL OR FP.END_DATE > SYSDATE);

And that’s it. Perfect.

NVL() Actually, maybe we can tighten it a little more. We’ve guarded against Cartesian joins, we’re using table aliases, and comparing our dates to SYSDATE, but we can use a function named NVL() to tidy up this condition: and (fp.end_date is null or fp.end_date > sysdate). NVL() is a function used to test if a value is null and to replace it with an alternative value if it is. Its syntax is as follows:

NVL(value, replacement_value)

The NVL function will return value if it is not null, but will return replacement_value if it is. If both value and replacement_value are null, NVL will return null. Value and replacement_value can be of whichever datatype you like. Let us use NVL in our query.

SELECT FN.FRIEND_ID, FN.FIRST_NAME, FN.LAST_NAME, PN.PHONE_NUMBER FROM FRIEND_NAME FN, FRIEND_PHONE FP, PHONE_NUMBER PN WHERE FN.FRIEND_ID = FP.FRIEND_ID AND FP.PHONE_ID = PN.PHONE_ID AND NVL (FP.START_DATE,SYSDATE) <= SYSDATE AND NVL (FP.END_DATE,SYSDATE+1)

> SYSDATE;

Our query is a thing of beauty, but you must have noticed that it does not return a complete list of our friends. You may have already figured out why; it only returns friends that have a phone number. But what if we wanted to see a full list of our friends with a null if they do not have a phone number? To do this I’ll need to tell you about outer joins.

Outer Join The joins we’ve been using thus far are effectively simple inner joins. When two tables are joined using a simple join (e.g. fn.friend_id = fp.friend_id) records in both tables must satisfy the condition to appear in our resultset. However, with an outer join we can ask Oracle to impose our rule on one of our tables and return nulls whenever the other table fails the test. In other words, we can say, we want to see all of our friends (all records in friend_name), and we don’t mind seeing nulls whenever they don’t have a phone number. There are two types of outer joins; a left outer join allows nulls in the second table in our join, while a right outer join allows nulls in the first table (while showing all records from the table on the right).

The syntax is as follows:

SELECT FROM



[left|right] outer join ON [WHERE clause];

Our query is a little complex since it joins three tables; you might find it more helpful if I illustrate the point by starting small, with only two tables – FRIEND_NAME and FRIEND_PHONE.

SELECT FN.FRIEND_ID, FN.FIRST_NAME, FN.LAST_NAME, FP.PHONE_ID FROM FRIEND_NAME FN LEFT OUTER JOIN FRIEND_PHONE FP ON FN.FRIEND_ID = FP.FRIEND_ID AND NVL (FP.START_DATE,SYSDATE) <= SYSDATE AND NVL (FP.END_DATE,SYSDATE+1)

> SYSDATE

ORDER BY FN.FRIEND_ID;

Run the query and notice how the resultset now contains all your friends, including those who do not have a phone number. Notice how, by using a left outer join, we got all the rows in friend_name (the table on the left of the join); change it to a right outer join and see how that changes the output. Interesting, isn’t it? But we need to add a third table – PHONE_NUMBER – to our query to make it useful. Let me show you how to do that.

SELECT FN.FRIEND_ID, FN.FIRST_NAME, FN.LAST_NAME, PN.PHONE_NUMBER FROM FRIEND_NAME FN LEFT OUTER JOIN FRIEND_PHONE FP ON FN.FRIEND_ID = FP.FRIEND_ID AND NVL (FP.START_DATE,SYSDATE) <= SYSDATE AND NVL (FP.END_DATE,SYSDATE+1) LEFT OUTER JOIN PHONE_NUMBER PN ON FP.PHONE_ID = PN.PHONE_ID ORDER BY FRIEND_ID;

> SYSDATE

Joining multiple tables in this way is a little like baking a cake; you apply your joins layer by layer. (NB: I’ve never baked a cake before, but I once saw someone bake a cake on TV, and I remember thinking, “Hey, that’s just like writing an outer join query!”) If you’re up for a challenge, why don’t you add a few more layers to our query? Expand the query to display our friends’ addresses: to do this you’ll need to join the FRIEND_ADDRESS and ADDRESS tables. While you’re doing that, I’ll go eat some cake. I’m suddenly hungry for some reason.

Chapter 5: PL/SQL 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]

PL/SQL 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.

Anonymous Blocks 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 within that block in sequence. Blocks are structured as follows:

DECLARE

(Optional) BEGIN 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. o

o

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 a 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: o o

o

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.

I would like you to take some time to go back over our anonymous block, as it is important that you understand every single line of it. Take as much time as you need; I'll wait.

Loops 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.

Booleans But first I must introduce you to Booleans. th

The Boolean – named after the great 19 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.

Loops cont’d As I said, there are a few different types of loops, each suited to different scenarios. Below are the names and syntax of a few of them:

Simple loops

LOOP <> 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 iterate for as long as the Boolean expression is TRUE. The expression is tested with each new iteration.

While loops

WHILE <> LOOP <> END LOOP;

While loops may not iterate even once if the Boolean expression is FALSE from the start.

For loops

FOR indx IN 1 .. n LOOP <> END LOOP;

A For Loop will run n times.

Cursor loops

For (SELECT statement) LOOP

This loop will iterate once for every record returned in the

<> END LOOP;

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.

Value Assignment 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 - i.e created - 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 :=.

You will also want to remember that in PL/SQL we can overwrite the value in a variable by simply assigning a new value (or even a null) to it. You'll see that in our next example.

Loops cont’d 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.

Conditional statements 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 THEN END IF; IF THEN ELSE END IF; IF THEN ELSIF

THEN

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

Conclusion 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. In the next chapter, 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?

Chapter 6: Procedures and Functions A wise man once said, insanity is doing the same thing over and over again but expecting different results. If that is the case then no one’s saner than a computer programmer*, because programming is executing the same code over and over again and expecting the same results. We’ve already talked about bundling up statements in anonymous PL/SQL blocks; now it’s time to bring out the big dogs of rerunnable code – procedures and functions. (* which is strange – most programmers, in my experience, are crazier than a fly trapped in a bottle! Except me, of course.)

Definitions Procedures and functions are named PL/SQL blocks. Having names bestows them with certain advantages: they can be stored within the database, which means they can be reused easily; they can call each other; and they can accept parameters that modify their behaviour. But what, you may ask, is the difference between a procedure and a function? Good question, my friend. A procedure is a named PL/SQL block that carries out one or more actions. A function is a named PL/SQL block that returns a value. They’re close cousins and, based on those definitions, it may be difficult to tell them apart. An example might help: we might write a procedure named insert_new_friend that will accept a new friend’s name and a new phone number as parameters and will insert a new record in both the FRIEND_NAME and FRIEND_PHONE tables for that friend and number. So if, we go to a party and make ten new friends, instead of writing ten sets of long-winded insert statements, we would simply call insert_new_friend ten times with ten different sets of parameters. Our procedure will carry out the actions for us. We might also write a function named get_friend_phone_number that will accept a friend’s name as a parameter, use it to query the FRIEND_NAME table to find out their friend_id, use that to query the FRIEND_PHONE table, and then return to us their phone number. So if we came across a juicy piece of gossip that we just had to share, we might call get_friend_phone_number ten times with different parameters to get ten different phone numbers. In each case, our function will return a value to us. Procedures carry out action(s); functions return a value.

Syntax CREATE [OR REPLACE] PROCEDURE [parameter(s)] AS

[Variable declaration section] BEGIN END []; You’ll notice that the structure of a stored procedure is pretty much identical to that of an anonymous block; the only differences are that stored procedures must be given a name, can accept parameters, and they dispense with the necessity of the keyword DECLARE at the start of the declaration section. Here’s the syntax for functions.

CREATE [OR REPLACE] FUNCTION [parameter(s)] RETURN AS [variable declaration section] BEGIN END []; Functions have the same syntactical skeleton as anonymous blocks and procedures; the important difference is that, since all functions must return a value, in defining a function we must specify the datatype of the value that we are planning to return. Following on from this, the body of all functions must contain a RETURN statement: this isn’t shown in our syntactical wireframe above, but we’ll talk about it a little later.

Creating Oracle Objects So far in this book, we’ve talked about Oracle objects – mostly tables, but now procedures and functions – but I haven’t said anything about how these objects are created in the first place. We’ll go into it in more detail in a future chapter, but you’ll notice that the syntax is always CREATE [OR REPLACE] object_type [AS].

Sequences I’ll give you a quick example. In Oracle we have something called a sequence. A sequence is an object that is used to generate a list of numbers. They are very useful when we need unique numbers to populate id columns like friend_id in FRIEND_NAME or phone_id in PHONE_NUMBER. The syntax for creating a sequence is as follows:

CREATE SEQUENCE friend_id_seq START WITH 100 INCREMENT BY 1; Run the code to create the friend_id_seq sequence, and then write another one to create a sequence called phone_id_seq, also starting with 100 and incrementing by 1. We’ll use them later.

However, let’s get back to procedures.

Procedures Let’s start with a simple example. Why don’t we create a procedure that will analyse our database for us and report on the breakdown of our friends? We can call it something like friends_analysis.

CREATE OR REPLACE PROCEDURE friends_analysis AS BEGIN FOR i IN (SELECT COUNT(*) cnt, gender FROM friend_name GROUP BY gender) LOOP IF i.gender = 'M' THEN dbms_output.put_line('I have '||i.cnt||' male friends.'); ELSIF i.gender = 'F' THEN dbms_output.put_line('I have '||i.cnt||' female friends.'); END IF; END LOOP; /* Assume the value in friend_name.friend_id represents the order in which we became friends. */ FOR i IN (SELECT first_name, middle_name, last_name FROM friend_name WHERE friend_id = (SELECT MIN(friend_id) FROM friend_name ) ) LOOP dbms_output.put_line('Our oldest friend is '||i.first_name||' '||i.middle_name||' '||i.last_name); END LOOP; FOR i IN (SELECT first_name, middle_name, last_name FROM friend_name WHERE friend_id = (SELECT MAX(friend_id) FROM friend_name ) ) LOOP dbms_output.put_line('Our newest friend is '||i.first_name||' '||i.middle_name||' '||i.last_name);

END LOOP; END friends_analysis;

Okay, maybe we lied a little when we said it’d be simple, but you should be able to follow the logic. Notice how, in finding our oldest and newest friends, we embedded a select statement within another to get the friend_id? That is called a sub-query and is a great time-saving trick. If sub-queries did not exist, I would have had to select the minimum friend_id and save it in a variable before being able to use it to find our oldest friend. But back to our procedure. When we run the code above it will not output an analysis of our friends; instead it will create a procedure named friends_analysis in our database, ready for us to use whenever we want to – and reuse as often as we want. Procedures can be called from other procedures, from anonymous blocks, from functions – wherever they’re needed in your PL/SQL. Let’s call ours from an anonymous block.

BEGIN friends_analysis; END;

The fact that procedures – and functions – can be called repeatedly from numerous places is what makes them so useful. Think about it: it saves the developer the trouble of all that typing; it makes bug-fixing easier since you only need to correct an error in a single place; if your requirement changes you only have to make a change in one place; it makes code easier to read (the anonymous block above is only 3 lines long – and yet it does so much!).

Parameters We’ve been throwing the word parameter around like it’s confetti at a wedding. A parameter is a special kind of variable which is used to pass data into a procedure or function. Earlier, we talked about creating a procedure that would accept a new friend’s name and a phone number – as parameters – and insert the details into the right tables. Let’s write it to illustrate the usefulness of parameters.

CREATE OR REPLACE PROCEDURE insert_new_friend (pFirst_name

VARCHAR2,

pLast_name

VARCHAR2,

pGender

VARCHAR2,

pPhone_country

NUMBER,

pPhone_area

NUMBER,

pPhone_number

NUMBER

)

AS -- declare our variables. v_friend_id

NUMBER;

v_phone_id

NUMBER;

BEGIN -- add a record to the friend_name table. INSERT INTO friend_name (friend_id, first_name, last_name, gender) VALUES (friend_id_seq.nextval, pFirst_name, pLast_name, pGender) RETURNING friend_id INTO v_friend_id; -- Next we need to add a new record to the PHONE_NUMBER table. INSERT INTO phone_number( phone_id, country_code, area_code, phone_number) VALUES (phone_id_seq.nextval, pPhone_country, pPhone_area, pPhone_number) RETURNING phone_id INTO v_phone_id; -- Finally, we need to associate our new friend with this phone number. INSERT INTO friend_phone (friend_id, phone_id, start_date) VALUES (v_friend_id, v_phone_id, SYSDATE); END insert_new_friend;

And that’s it. So now if, at our party, we made a friend from London and another from Lagos, Nigeria, we might simply call our procedure from an anonymous block, passing in the right parameters.

BEGIN insert_new_friend ('Jane', 'Simpson', 'F', 44, insert_new_friend ('Ola',

'Sanusi',

207, 555551);

'M', 234, 1,

890555);

END;

By calling our new procedure with the names of our new friends, we are populating our parameters – pFirst_name, pLast_name etc – and so we can use them in our insert statements. This is how we are able to use the exact same procedure for Jane Simpson, for Ola Sanusi and for however many new friends we make in the future. There are a few other new things that I sneaked into our procedure:

o

o

To get the next number from a sequence, we use the following syntax: .nextval. This always gets the next number; so if your sequence is at 100 and you call .nextval three times in three select statements, you will (probably) get 101, 102 and 103. After you’ve run .nextval, you can run .currval to get the current value, rather than the next one. The RETURNING … INTO clause can be used with insert and update statements to place a value in a variable. In our procedure, we’re adding a new friend_id using friend_id_seq.nextval; however, we want to assign that number to our v_friend_id variable so we can use it later.

Functions Functions, as we said earlier, must return a value. Basically, functions must answer a single, specific question. You can write a function for the following: o Find out a friend’s phone number o Return a friend’s gender o Test a premise. For example, return TRUE if you have a friend in London, or FALSE if you don’t. But you cannot write a function for the following: o

Find a friend’s phone number and gender.

Because functions must always answer a single, specific question. (It is possible, using more complex datatypes to return a single value comprised of other bits of information, but that is outside the scope of this book.) We talked earlier about creating a function named get_friend_phone_number to answer the specific question: what is the parameterised friend’s phone number. Let’s try creating it now.

CREATE OR REPLACE FUNCTION get_friend_phone_number (pFirst_name pLast_name

VARCHAR2, VARCHAR2)

RETURN NUMBER AS V_phone_no

NUMBER;

BEGIN FOR i IN (SELECT pn.phone_number FROM phone_number pn, friend_name fn, friend_phone fp WHERE UPPER(fn.first_name) = UPPER(pFirst_name) AND UPPER(fn.last_name) = UPPER(pLast_name) AND fn.friend_id = fp.friend_id AND fp.start_date <= SYSDATE AND NVL(fp.end_date, SYSDATE + 1) > SYSDATE AND fp.phone_id = pn.phone_id) LOOP v_phone_no := i.phone_number; END LOOP; -- All functions MUST return something (even if it is a null).

RETURN v_phone_no; END get_friend_phone_number; Procedures, we have said, carry out actions, while functions return a value. For this reason, functions are called a little differently from procedures, which are simply executed. A function can be assigned to a variable or used with an SQL statement. I’ll show you what I mean.

DECLARE v_joey_phone

NUMBER;

BEGIN -- Assign our function to a variable. v_joey_phone

:=

get_friend_phone_number('Joey','Tribiani'); dbms_output.put_line('Joey''s phone number is '||v_joey_phone); -- Use our function in a select statement FOR i IN (SELECT first_name, last_name, get_friend_phone_number(first_name, last_name) telno FROM friend_name) LOOP dbms_output.put_line(i.first_name||': '||i.telno); END LOOP; END; Functions are amazingly versatile; they can return any datatype and can be used pretty much anywhere any value can be. But you already knew that. You’ve been using functions, pretty much from day one. Don’t believe me? What do you think SYSDATE, UPPER, TO_DATE, COUNT, MIN, MAX and NVL are? They are, respectively, a function that returns the current date; a function that accepts a string as a parameter and returns the uppercase value; a function that takes a string as a parameter and returns a date; an aggregate function that counts the parameter; an aggregate function that returns the minimum (or maximum) value; and a function that accepts two parameters and returns either the first or the second if the first is null. The only difference between them and get_friend_phone_number is that they are built-in Oracle functions – but they’re functions no less. And dbms_output.put_line? That’s a built-in procedure: it accepts a string as a parameter and carries out the action of printing it to the screen.

Conclusion So there you have it – procedures and functions. They are, arguably, the most important things in all of computer programming; they’re the Lego bricks with which everything else must be built. All wellwritten applications are made up of procedures which contain other procedures and call functions which, in turn, may call other functions and execute other procedures.

The keywords are reusability and modularity. If you have an action that you know you will carry out more than once (insert a new friend, for instance) you must create a procedure to do it; if you have a question you know you will ask more than once (what is a friend’s phone number?) you must create a function to answer it. And, as much as possible, you should keep your procedures and functions single-purposed and small. That way, when you need to build a complex algorithm, all you have to do is construct it by calling one procedure after the other in whatever sequence you decide. The more focused your procedures, the easier it is to build complex structures. It’s just like Lego. Everything is awesome.

Chapter 7: Creating Tables, Constraints and Triggers A wise man once said: start at the beginning and go on till you come to the end: then stop. We’ve done things a little differently; all this time we’ve been talking about examining and manipulating the data in our tables, but we have never paused to consider where these tables come from, or discuss how they are created. Time to fix that.

Creating a Table Here’s the syntax:

CREATE TABLE

(

, , …); There are a few rules that you’ll need to bear in mind: o

The table name must be unique. You cannot, for example, have two tables named FRIEND_NAME. After all, you wouldn’t have two children and name them both John. Or maybe you would; I don’t know you that well. o Your column names must be unique within your table. While you cannot have two columns in FRIEND_NAME called friend_id; it is possible to have friend_id in FRIEND_NAME and FRIEND_ADDRESS. o Each column must have a data type. You’ll recall that we’ve discussed a number of different data types – VARCHAR2, NUMBER, DATE. With VARCHAR2 columns you must additionally specify the length; you should specify the precision for NUMBER columns too. o The names you give your table – and your columns – must be valid Oracle identifiers. Actually, every name you give objects you create in your database – and this includes our stored procedures, functions and sequences – must obey a number of set rules, or Oracle will throw its toys out of the cot and give you an error. Here are those rules.

Valid Oracle Identifiers An acceptable Oracle name must: o o o o

Not be a reserved keyword. You cannot, for example, name your table TABLE or PROCEDURE or VARCHAR2. Be no longer than 30 characters. Begin with an alphabetical character. You can have numbers and certain special characters in the name, but the first character must be an alpha character. Contain only alphabetical characters, numbers, or one of the following special characters: # $ _

That’s the law and you must obey it; however, I would recommend that you come up with some rules – conventions – of your own that you follow in naming objects in your database. The surest shortcut to confusion in a database is a mishmash of incomprehensible object names. Take our sample database for example: we’ve got a table named FRIEND_NAME, and others named FRIEND_ADDRESS, FRIEND_PHONE and PHONE_NUMBER. If we needed to create a new table to hold email addresses, wouldn’t you expect it to be named … EMAILS_4_OUR_PALS? See how that jars? We instinctively know it should be named something like FRIEND_EMAIL; the dissonance caused by an unexpected name is often the first step to a badly-designed database. Let’s talk about database design a bit more later; it's a really fun subject.

Creating Tables cont'd Now we know the syntax, the rules and the conventions, let us produce the code needed to create the FRIEND_NAME table.

CREATE TABLE friend_name ( friend_id

NUMBER(3),

first_name

VARCHAR2(30),

middle_name VARCHAR2(30), last_name

VARCHAR2(30),

gender

VARCHAR2(1)

); You won’t be able to successfully run the code because the table FRIEND_NAME already exists (remember: you can’t have two children named John), so maybe we should try writing a script that we will be able to run.

CREATE TABLE friend_email ( friend_id NUMBER(3), email

VARCHAR2(50)

); That’s pretty straightforward. Try running it. Did it work? Good. But what if, after creating our table, we realise that we’d like some date columns to map the life of the email address? We have some choices. We can drop the table (in order to rebuild it with the columns we want). Here’s the syntax.

DROP TABLE ;

DROP TABLE friend_email;

However, if our table already contained valuable data, this option might not be open to you. A less drastic option would be to ALTER the table.

ALTER TABLE ADD ( , );

ALTER TABLE friend_email ADD ( start_date DATE, end_date

DATE

);

The alter command has more tricks in its backpack than just adding columns. For example, if you realise that the start_date column is redundant (email addresses do not really have start dates), you can drop the column.

ALTER TABLE DROP COLUMN ;

ALTER TABLE friend_email DROP COLUMN start_date;

There are other things we can do with the alter command. Let’s talk about another one.

Constraints We talked about constraints in previous chapters, and I told you how all tables should have a primary key to describe uniqueness. For our new table, a unique record would probably be described by a pair of columns – friend_id and email. To add this constraint to our table we’ll need to depend on our trusty alter command again.

ALTER TABLE ADD CONSTRAINT PRIMARY KEY (, );

ALTER TABLE friend_email ADD CONSTRAINT friend_email_pk PRIMARY KEY (friend_id, email);

What this means is that every record we insert into this table must have an email address and must have a friend id, and this coupling of data must not be repeated. Talking about the friend_id column brings us to the foreign key constraint. Every friend_id we use in friend_email must already exist in the friend_name table (in other words, a record in friend_name must be the parent to records in friend_email).

The syntax for creating a foreign key constraint is as follows:

ALTER TABLE ADD CONSTRAINT FOREIGN KEY () REFERENCES ();

ALTER TABLE friend_email ADD CONSTRAINT friend_name_friend_email_fk FOREIGN KEY (friend_id) REFERENCES friend_name (friend_id);

What this now means is that if you try to insert a record with a friend_id that does not exist in friend_name, Oracle will smack you with an error.

Triggers There is another piece of our jigsaw that I would like to talk about, but it’s probably best if we pause for a quick recap. We’ve talked about creating tables, and on the back of that we’ve created primary keys and foreign keys; we’ve talked about PL/SQL blocks, and on the back of that we’ve created named procedures and named functions; and we’ve talked about creating sequences. And if you’re thinking, I wish there was a way we could tie all of this together, then sit back cos I’m about to blow your socks off. Since friend_id is the primary key column in friend_name and is populated by our friend_id_seq sequence, wouldn’t it be nice if we could write some PL/SQL that would automatically populate the next value from our sequence into the column each time a new row is inserted? And that’s where triggers come in. An Oracle trigger is a procedure that is automatically executed on the occurrence of some other specified event. Since we can code a trigger to fire on a table insert, we can use it to get friend_id_seq.nextval from our sequence and put it in the friend_id column. (Triggers can also fire on updates and deletes.) Here is the syntax for a table trigger.

CREATE OR REPLACE TRIGGER [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON FOR EACH ROW DECLARE -- variable declarations BEGIN -- trigger code END;

There are a few things to note. You can choose whether you want your trigger to fire before or after the row is inserted/updated/deleted. This is important as it determines what table values are available to your code; you wouldn’t expect to be able to access a record after it has been deleted, would you? Talking about referencing a table’s values brings us to a construct that is specific to triggers – :NEW. and :OLD. Since triggers execute when a table’s values may be in flux, we need to tell Oracle what value we want. If, for example, you are updating the last_name column for your friend, Monica, from Geller to Bing, :OLD.last_name will be Geller, while :NEW.last_name will be Bing.

With INSERT triggers, only :NEW values are available. Since inserts create brand new records, there are no :OLD values to reference. Similarly, with DELETE triggers, you can only reference :OLD values. Another thing worth noting is the phrase FOR EACH ROW; it means that your trigger will fire for every row affected. If you update 1000 records, your update trigger will fire 1000 times. Our trigger to populate the friend_id column in FRIEND_NAME will look something like this.

CREATE OR REPLACE TRIGGER friend_name_id_trg BEFORE INSERT ON friend_name FOR EACH ROW BEGIN SELECT friend_id_seq.nextval INTO :new.friend_id FROM dual; END; /

Using Triggers Triggers are powerful – and much despised – tools. Simple triggers to populate primary key columns from sequences are pretty uncontroversial; however, there is no upper limit to their potential complexity. They can be hundreds of lines long and contain convoluted conditions. Many developers dislike them because they fire silently in the background, seeming to live just beyond the fingertips of their control. My advice is this: o o

always use them to populate primary key columns that depend on sequences; always use them for auditing. (You could, for example, create an audit table into which you automatically insert a record each time a major change is carried out on FRIEND_NAME. In your audit table you could record what the :OLD value was - in case you need to revert to it - and who made the change - in case you need to fire them!)

Beyond that, use them only when they are clearly the best available option.

Chapter 8: Database Design and Normalization A wise man once said, I know one thing: that I know nothing. I sincerely hope that, by now, you’ve gone from knowing absolutely nothing about Oracle database programming to this point where you know the foundations, know how the parts fit together – basically, know enough to know that there’s a whole lot more that you don’t yet know. Welcome to my world; I’ve been doing this for 15 years and I still feel there’s a lot to learn. In this final chapter we will talk briefly about database design, tie up some loose ends, and I will act the role of the wise old man and bore you with some advice based on those 15 years of mine.

Database Design Before I became a software developer, I worked as a shoemaker (this is a total lie; I’m just trying to make a point). I once got an order to make 3 high-heeled shoes for Jennifer Aniston (since this is a lie, it might as well be a big one). And I only had 2 days! Not enough time! I realised that I could either use one day to design the shoes and risk only making 2 pairs; or I could work without planning and deliver all 3 pairs – except Jennifer Aniston will have to wear shoes with the right heel shorter than the left to the Oscars. The moral of that story is – obviously – that you should spend as much time as you can getting the design of your database just right. Poorly-designed databases are harder to write code for and often run queries inefficiently; and since databases often survive decades, those costs pile up astronomically over the years.

Three Rules of Normalization There is a process called normalization that supplies us with rules which help us design efficient tables. 1. First Normal Form: An entity is 1NF when it contains no repeating groups of data. What this means is that, using our FRIEND_NAME table as an example, no record in the table should contain more than one of the same kind of data. You shouldn’t record both Joey Tribiani and Chandler Bing in the same record, even though they live together. They must have separate, individual records. 2. Second Normal Form: An entity is 2NF when no records contain non-key attributes that are not dependent on the primary key and apply to multiple records. What this means, basically, is that your FRIEND_NAME records must not contain address details. Firstly, the address is not dependent on the person (since they can move); secondly, multiple people can live at one address and we do not want to end up repeating those address details in our record

for Joey and our record for Chandler. The correct thing to do with such data is to move it into a table of its own, an ADDRESS table. 3. Third Normal Form: An entity is 3NF when no records contain key attributes that are not dependent on the primary key. An example might help here. Imagine we wanted to know how many housemates each of our friends had. If we add a column to FRIEND_NAME named housemate_count our table will not be 3NF. This is because housemate_count is not wholly dependent on our primary key. There are many, many books repeating the rules of normalization (which is ironic, since normalization is all about not repeating stuff), and there’s no way I can explain the subject fully here. But if you can remember my 3 rules of database design, you’re on to a good start. 1. The world is made up of things. Create tables for your various things – friend, address, phone number, email address – and never have two different types of thing in the same table. 2. Things often interact with other things (or themselves). Create tables to record these interactions – FRIEND_PHONE, FRIEND_ADDRESS, FRIEND_EMAIL. Never have two different types of interactions in the same table. 3. Things and interactions sometimes need descriptions. Create tables to record these descriptions. (If, for example, our ADDRESS table had a column named address_type which could be ‘apartment’, ‘townhouse’ , ‘bungalow’, then address type is a description of our thing, and should be in a separate lookup table.) And there you have it: things, interactions, descriptions – my 3 rules of database design. There’s obviously more reading for you to do, but this is a good start.

Further reading Now that I think of it, while this book has, hopefully, given you a solid foundation, there are some important omissions that you might want as the starting point for your further reading: the dual table, case statements, the timestamp data type, packages. There is always more to learn, more to study. It’s like that scene in the movie Kill Bill where every time she beat up 5 bad guys, 10 more would turn up. And, if we’re being honest – about Oracle, not about Kill Bill – you probably aren’t going to remember everything I’ve told you this far. But if you don’t know all there is to know, and you don’t even remember the things that you do know, what makes (or will make) you a good Oracle programmer? Thank you for asking: I’ve got 3 rules.

Three rules every programmer should remember 1. Your best programming should be done before your coding starts. The more time you spend planning, thinking, researching, the better your eventual code will be. Coding is exciting and the temptation will be to dive straight in. Resist it. 2. Clever rewards you now; boring rewards you later. When writing procedures, functions, triggers, producing the code is the interesting work, the clever work. Commenting, instrumenting and formatting that code is the boring work that you probably do not

want to do. But believe me, when you return a year later to maintain your code you won’t be cooing over how clever your code is, you’ll be cursing yourself for not commenting enough. 3. Cleverer is not necessarily better. Or, as my computer programming lecturer once said: never write one line of code where you can write two. PL/SQL is a beautiful language and, with each release, Oracle buttress it with new functionality and clever ways to do stuff. But I would advise against being seduced by the clever new function that you’ve just learned unless it is definitely more efficient and as easy to understand as what it is replacing. The two things we always strive for with our code must be efficiency and legibility. For example, at work, I rarely use the NULLIF function because my colleagues are not all that familiar with it (and I need them to be able to maintain my code); instead I would use a much longer case statement. Not necessarily as clever, but definitely better.

Conclusion And those, my friend, are the foundations of Oracle database programming from tables through SQL all the way to PL/SQL. As I have said often, there is more to know, but everything new you learn will be no more than an extension to the lessons in these 8 chapters. And that’s exciting. So let's end this book the same way we begun; with these words:

An expert, a wise man once said, is someone who uses big words and acronyms where simple phrases would do just as nicely. So stand back and listen to this: Database, Relational Database, DBMS, RDBMS, SQL, Sub-queries, normalization. Sounds like you're now an expert, my friend.

---

Oracle for Absolute Beginners complete.pdf

pdf-1416\absolute-beginners-drums-book-dvd-edition-absolute ...

absolute-beginners-guide-to-databases.pdf

Download Android Apps for Absolute Beginners PDF ...

pdf-20103\seo-for-beginners-an-absolute ... - Drive

Oracle for Absolute Beginners complete.pdf

accordance with the Copyright, Designs and Patents Act 1988. ... Oracle for Absolute Beginners complete.pdf. Oracle for Absolute Beginners complete.pdf. Open.

---

Download PDF

952KB Sizes 1 Downloads 200 Views

Report

Recommend Documents

Oracle for Absolute Beginners complete.pdf

Download. Connect more apps... Try one of the apps below to open or edit this item. Oracle for Absolute Beginners complete.pdf. Oracle for Absolute Beginners ...

---

pdf-1416\absolute-beginners-drums-book-dvd-edition-absolute ...

Try one of the apps below to open or edit this item. pdf-1416\absolute-beginners-drums-book-dvd-edition-absolute-beginners-from-hal-leonard.pdf.

---

absolute-beginners-guide-to-databases.pdf

Trina Wurst. Page Layout. Michelle Mitchell. Page 3 of 325. absolute-beginners-guide-to-databases.pdf. absolute-beginners-guide-to-databases.pdf. Open.

---

Download Android Apps for Absolute Beginners PDF ...

Download Android Apps for Absolute Beginners PDF Online. Book Synopsis ... simple apps for the Android platform, and this ... best possible start in Android.

---

pdf-20103\seo-for-beginners-an-absolute ... - Drive

There was a problem loading more pages. pdf-20103\seo-for-beginners-an-absolute-beginners-g ... ge-search-engine-optimization-by-mike-macdonald.pdf.

---

×

Report Oracle for Absolute Beginners complete.pdf

Your name

Email

Reason -Select Reason- Pornographic Defamatory Illegal/Unlawful Spam Other Terms Of Service Violation File a copyright complaint

Description

Close Save changes

×

Sign In

Email

Password

Remember Password Forgot Password?

Sign In

---