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, re-
sold, 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 – so-
called 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 <comma-separated column list>

FROM <table name>
WHERE <condition(s)>;

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 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.
o 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 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;
o 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.
o 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 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.
o 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.
o 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.
o 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.
o List the middle name and last name of all your friends who have a middle name, in descending
order of surname.
o 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 <table name >

SET <column name>= <value>
WHERE <condition(s)>;

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 <table_name> (<comma-separated column list>)

VALUES ( <comma-separated value list>);

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(<string>,<format mask>)

The <string> is the character string that we want converted to a date, and the <format mask> 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 <table name>

WHERE <condition>;

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 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.
o 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.
o Try inserting the details of your real-world friends into the FRIEND_NAME table. Remember that
each record must have a unique friend_id.
o 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.
o 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 <comma-separated column list>

FROM <comma-separated table list>
WHERE <conditions>

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 <comma-separated column list>

FROM <table1>
[left|right] outer join <table2>
ON <join condition>
[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) > SYSDATE
LEFT OUTER JOIN PHONE_NUMBER PN
ON FP.PHONE_ID = PN.PHONE_ID
ORDER BY FRIEND_ID;
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
 <Variable declaration section> (Optional)
BEGIN
<Statements>
END;

We haven’t talked about variables yet, so I’d best tell you what they are. A variable is a named
storage location which can contain a value. Let me give you an example: if we wanted to find out how
many of our friends live with Joey Tribiani, we could write a select statement to find out the
address_id of Joey’s residence, store that address_id in a variable, and then use that variable to find
the friend_id of anyone else who lived at that address. So variables, in effect, are kinda like
Tupperware that you can store values in.

In the declaration section of our block, we must define all the variables that we plan to use: if our
block doesn’t need any variables, this section does not need to exist.

To define a variable, we must give it a name, tell Oracle its datatype and, optionally, initialize it with a
value (i.e. give it an initial value).

The main body of our block starts with BEGIN and, obviously, must exist.

Let’s go back to our Joey Tribiani example and I’ll show you what I mean.

DECLARE
v_addr_id NUMBER;
v_joey_id NUMBER;
v_friend_count NUMBER;
BEGIN
-- Find out Joey’s address id and put it in our variable.
SELECT fa.address_id, fn.friend_id
INTO v_addr_id, v_joey_id
FROM friend_name fn, friend_address fa
WHERE fn.friend_id = fa.friend_id
AND UPPER(fn.last_name) = 'TRIBIANI';

/*
Now we have the address_id, find out how many other friends live there.
We need to exclude Joey himself from our count, obviously.
*/

SELECT COUNT(*)
INTO v_friend_count
 FROM friend_address
WHERE address_id = v_addr_id
AND friend_id <> v_joey_id;

-- The number of friends is in our variable. Print it out to the screen.

DBMS_OUTPUT.PUT_LINE('The number of friends that live with Joey is
'||v_friend_count);

END;

There are a few things I’d like you to note.

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_.
o 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 You can comment single lines using the double-dash (–). To comment multiple lines we put a /*
before the comment, and end it with a */.
o 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.
o 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 must contain an

EXIT or they will loop
endlessly; the EXIT will
usually only be called when a
LOOP specific condition is TRUE.
<<actions to carry out>>
EXIT; Simple loops always run at
Simple loops END LOOP; least once.

While loops iterate for as long

as the Boolean expression is
TRUE. The expression is
tested with each new
iteration.

WHILE <<Boolean While loops may not iterate

expression>> LOOP even once if the Boolean
<<actions to carry out>> expression is FALSE from the
While loops END LOOP; start.

FOR indx IN 1 .. n LOOP

<<actions to carry out>>
For loops END LOOP; A For Loop will run n times.

For (SELECT statement) This loop will iterate once for

Cursor loops LOOP every record returned in the
 <<actions to carry out>> select statement.
END LOOP;
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 <Boolean statement> THEN

<actions>
END IF;
IF <Boolean condition> THEN
<actions>
ELSE
<actions>
END IF;
IF <Boolean condition> THEN
<actions>
ELSIF <Boolean condition> THEN
<actions>
ELSE
<actions>
END IF;

The kind of if statement you use depends, of course, on what you want to do. Sometimes you will
want to carry out an action if a condition is true, and do nothing otherwise; other times you may want
to carry out some actions if a condition is true, and other actions if it’s false.

An example might help. What if, rather than list the name of everyone who lives at an address in our
previous anonymous block, we want to differentiate between people who currently live there and
those who used to live there?

DECLARE
v_address VARCHAR2(500);
v_curr_resident VARCHAR2(32767);
v_past_resident VARCHAR2(32767);

BEGIN
-- Loop round all our addresses.
FOR i IN (SELECT *
FROM address) LOOP
v_address := 'Apartment '||i.apartment||', '||i.house_no||'
'||i.street||', '||i.city||' '||i.zipcode;
dbms_output.put_line(v_address);
 v_curr_resident := NULL;
v_past_resident := NULL;

-- Now let's find everyone who has lived at this address.

FOR j IN (SELECT fn.first_name, fn.last_name, fa.moved_in,
fa.moved_out
FROM friend_name fn, friend_address
fa
WHERE fa.address_id = i.address_id
AND fn.friend_id = fa.friend_id) LOOP

IF (j.moved_out IS NULL OR
j.moved_out >= SYSDATE) THEN -- current resident.
IF v_curr_resident IS NULL
THEN
-- because the
variable is null, we know this is the first current resident for this
address.
v_curr_resident :=
j.first_name||' '||j.last_name;
ELSE
-- this isn't the
first current resident; concatenate so we don't overwrite the previous
name.
v_curr_resident :=
v_curr_resident||CHR(10)||CHR(9)||j.first_name||' '||j.last_name; --
chr(10) is a line break. chr(9) is a tab.
END IF;
ELSE -- past residents
IF v_past_resident IS NULL
THEN
-- because the
variable is null, we know this is the first current resident for this
address.
v_past_resident :=
j.first_name||' '||j.last_name;
ELSE
-- this isn't the
first past resident; concatenate so we don't overwrite the previous name.
v_past_resident :=
v_past_resident||CHR(10)||CHR(9)||j.first_name||' '||j.last_name;
END IF;
END IF;
 END LOOP;

-- let's print out the names we've found.

IF v_curr_resident IS NOT NULL THEN
dbms_output.put_line(CHR(9)||'The following friends
live at this address.');
dbms_output.put_line(CHR(9)||v_curr_resident);
END IF;

IF v_past_resident IS NOT NULL THEN

dbms_output.put_line(CHR(9)||'The following friends
used to live at this address.');
dbms_output.put_line(CHR(9)||v_past_resident);
END IF;
END LOOP;
END;

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 <procedure_name> [parameter(s)] AS

 [Variable declaration section]
BEGIN
<Statements>
END [<procedure_name>];

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 <function_name> [parameter(s)] RETURN

<datatype> AS
[variable declaration section]
BEGIN
<Statements>
END [<function_name>];

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 <object_name> [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, 207, 555551);
insert_new_friend ('Ola', 'Sanusi', '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 To get the next number from a sequence, we use the following syntax:
<sequence_name>.nextval. This always gets the next number; so if your sequence is at 100 and
you call <sequence_name>.nextval three times in three select statements, you will (probably) get
101, 102 and 103. After you’ve run <sequence_name>.nextval, you can run
<sequence_name>.currval to get the current value, rather than the next one.
o 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 VARCHAR2,

pLast_name 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 well-
written 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 <table name> (

<column name><data type>,
<column name><data type>,
<column name><data type>
…);

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 Not be a reserved keyword. You cannot, for example, name your table TABLE or PROCEDURE or
VARCHAR2.
o Be no longer than 30 characters.
o 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.
o 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 <table-name>;

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 <table name>

ADD (
<column name> <datatype>,
<column name> <datatype>
);

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 <table-name>

DROP COLUMN <column-name>;

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 <table name>

ADD CONSTRAINT <constraint name>
PRIMARY KEY (<column name>, <column name>);

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 <table name>

ADD CONSTRAINT <constraint name>
FOREIGN KEY (<column name>)
REFERENCES <parent table> (<column name>);

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 <trigger name>

[BEFORE|AFTER] [INSERT|UPDATE|DELETE]
ON <table name>
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 always use them to populate primary key columns that depend on sequences;
o 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.