PL/SQL is a simple programming language used for writing bits of code that are ‘embedded’ in a database stored on an Oracle SQL Server. It’s not intended for writing database applications. Lets be clear about this. Its purpose is to allow code to be written to perform tasks on the Oracle server itself. Why should you want to do this?

Let’s look at the client server model for a moment; the client PC will be running an application that makes use of the database. If it wishes to modify a record it must first find it, which it accomplishes by sending a SELECT message to the database server. The database server then finds it and sends the record in question back over the network; the PC modifies it and returns it to the server. This is fine on a small scale, but supposing it was necessary to make a small change to every record in the database – every record must be sent across the network at least twice. It’s easy to see that with a large database this will be very inefficient – especially if it’s over a slow Internet connection.

Using PL/SQL you can write a bit of code that goes through every record, makes a small change and writes I straight back without anything having to travel outside the server itself. It can also be used for many other small jobs, implementing quick fixes and working around design problems as a system evolves.

However, because PL/.SQL runs on a server it has no means of reading from a keyboard or writing to a terminal, other than a debugging window. It’s not and application language, so don’t expect to find application language features.

Chapter 1 – Blocks, Variables and Expressions

What exactly is a computer program?

If you can program in BASIC, Java, ‘C’ or similar language you can skip this next bit.

Still here? Okay: a computer program is a sequence of instructions interpreted by a computer. It’s important to remember that the computer starts with the first instruction in the list and when this has been carried out it starts on the next instruction. There are other paradigms – for example a spreadsheet has cells containing code that are all executed at once (apparently), but languages like PL/SQL execute instructions in order, one at a time.

The next feature of a computer program to consider are constants and variables. A constant is a value that remains the same throughout the execution of a program, a variable is a value that varies. Here’s a short program:

A := 10

A := A+5

Here, A is a variable. 5 and 10 are constants. ‘:=’ is the assignment operator for PL/SQL. It simply means you take what’s on its right hand side an store its value in the variable on its left. The first line is taking the value 10 (a constant) and storing it in variable A.

The second line is storing A+5 in A. It may look confusing, but it’s correct. The same variable can appear on both the left and right hand of the assignment operator.

The best conversion of ‘:=’ into English is ‘becomes equal to’, so the second line reads:

Variable A becomes equal to the value currently stored in variable A with five added.

The variable A contains 10 – we did that in the first line. So after the second line, A holds the value 15.

The general format for an assignment is:

Different languages have their own rules about variable names – those for PL/SQL will be explained in the next section. It’s normal to give a variable a meaningful name, such as TotalHours or NumberOfPeople rather than A, B, C etc.

It should be noted that variables normally have a ‘type’. Typical types are numbers, dates or characters. You cannot store a number in a variable that is supposed to hold a date (for example), nor would you expect to be able to store a date in a character-type variable.

Expressions can be anything that produces a value that can be stored in a variable of the appropriate type – for example 23 or 22/7 or A*B+C/D all produce a single numeric value once they’ve been evaluated (assume A,B,C+D are variables). Mathematical formulae are tricky to deal with on a single line, so mathematician’s shorthand is written explicitly using + and – for addition and subtraction, * and / for multiplication and division and parentheses (brackets) where necessary to enforce an evaluation order. For example, A*B+C/(D-1) means:

You’ll notice that while we may know A and B are two values to be multiplied, the computer will probably look for a single variable called AB – you have to be explicit! However, it will evaluate multiplication and division before addition and subtraction, and exponents before anything. Then it works from left to right as expected.

Most languages have a special case of variable type called a “character string”, commonly referred to as simply “string”. A string variable can hold a sequence of zero or more characters

A variable in PL/SQL has to be declared, but we’ll come to that in the next section. An expression can

If you’re used to other languages you might be used to seeing things like A=A+5. This syntax seems to upset mathematicians, and can lead to confusion, so PL/SQL uses := instead (as do Pascal, Algol and others).

PL/SQL – the Basics

Okay – let’s get on with it. Here’s a simple PL/SQL program:

BEGIN

dbms_output.put_line ('Hello World');

END;

You can type this in to SQL Plus directly (or copy/paste if you’re reading this on-line). However, when you get to the last line it will just sit there – it’s waiting for you to type a ‘/’ to actually run it. Type a ‘/’ followed by ‘Enter’ and you’ll be rewarded with the message:

PL/SQL procedure successfully completed.

What? No ‘Hello World’ on the screen? You might have to type in the following command to SQL Plus:

set serveroutput on size 4096

This tells Oracle that you’re actually going to use its debugging output facilities to write something to the screen – if you try to write anything to the screen without this it’ll be thrown away without warning. The ‘size 4096’ is the amount of memory to use for this facility; I picked 4K bytes because this is generally enough.

To save typing the program again you can simply type ‘/’ again – it will run whatever the last piece of PL/SQL code entered was every time to type it. If you do it now you’ll get:

SQL> /

Hello World

PL/SQL procedure successfully completed.

SQL>

Great! So now we can get on with looking at our program (repeated here for convenience):

BEGIN

dbms_output.put_line ('Hello World');

END;

You’ll notice it starts with BEGIN an ends with END. This is because PL/SQL is ‘block structured’, which means you end up with the program split into blocks of code. (If you’re used to ‘C’ or Java, BEGIN and END correspond to ‘{‘ and ‘}’)

Notice also the ‘;’ at the end of last two lines. These are used to mark the end of a statement. (This will be familiar to ‘C’ or Java programmers; in BASIC a statement normally ends where a line ends).

The idea of statements is really important. A statement is a self-contained instruction – a bit like a sentence in English. The semi-colon (‘;’) is important because the system needs to know when a statement ends, because it really doesn’t care about spaces between words and line breaks. These are inserted by the programmer to make the code more readable but are ignored by the system. Lots of confusion results if you forget this! There’s a tendency for beginners to put extra semi-colons in where they’re not needed – it’s just something you’ll have to get used to.

The program above could just have easily been written on one long line thus:

BEGIN dbms_output.put_line ('Hello World'); END;

(Try it if you don’t believe me).

If you’re still trying to understand the statement and semi-colon business, note that BEGIN is not a statement. It’s considered a statement only when it’s been closed by its corresponding END, which must be followed by a ‘;’ itself. If this still make no sense, don’t worry – you soon get used to it with practice.

That’s the first and last lines taken care of, but what of:

dbms_output.put_line ('Hello World');

This is the only way to get PL/SQL to write anything to the debugging screen, and to explain it fully involves some advanced concepts. Just accept that dbms_output.put_line() will write the value of whatever is between the brackets on to the screen. In this case we’re writing a string of characters (H + e + l + l + o etc.). We know ‘Hello World’ is a string because it has a ‘'’ at either end, which is how PL/SQL identifies constant string values (see constants earlier if you don’t know what a constant is).

The expression can be a variable or expression of any type, thus:

BEGIN

dbms_output.put_line (355/113);

END;

Listing 1 – Simple program to print PI

will calculate and write an approximation of π.

Before leaving BEGIN and END it is worth noting that you would normally have more than one statement inside the block, and that blocks can be nested within blocks (because BEGIN and END themselves constitute a statement). For example:

BEGIN

dbms_output.put_line ('My');

dbms_output.put_line ('name');

dbms_output.put_line ('is');

dbms_output.put_line ('Frank');

END;

Listing 2 – Multi-statement program

Will produce the output… well try it for yourself! Also the following code is perfectly legal:

BEGIN

dbms_output.put_line ('Nests are fun!');

END;

dbms_output.put_line ('Behave!');

END;

Listing 3 – Program with nested blocks

(I said it was legal, I didn’t say it was useful). The purpose of blocks will become clear later, but it’s as well to get used to the idea that they can be nested right from the start.

Simple Variables in PL/SQL

So far all the PL/SQL examples have involved constants. This is a bit limited. We need variables to do anything interesting.

Variables generally need a name, and in PL/SQL this must consist of no more than thirty characters – normally the letters A-Z, digits 0-9 and the underscore character (‘_’). It’s a bad idea to start a variable with a digit – some languages will assume it’s the beginning of a numerical constant.

The only other rule is that you mustn’t use a name that’s part of the language already – for example ‘begin’ can’t be used as a variable name. Think about it: how would the computer know it wasn’t the start of a nested block?

Note that PL/SQL is not case sensitive – ‘Example’, ‘EXAMPLE’ and ‘example’ would refer to the same variable. It’s a good idea to write your variables in lower-case and the rest of the code in upper case; that way you can see what the variables are. Other strategies include prefixing variable names with ‘v_’ so you can spot them in the code easily. However you write the code, it won’t matter to PL/SQL but it’s worth getting into consistent habits or you’ll confuse yourself and others.

PL/SQL requires to you declare (create) variables before you use them for the first. This is normal in common most ‘professional’ languages, but not simpler ones like BASIC.

Any variables you need are declared between the word DECLARE and BEGIN. See the following example:

DECLARE

mynumber INTEGER;

BEGIN

mynumber := 10;

mynumber := mynumber * 10 + 1;

dbms_output.put_line ('My number contains');

dbms_output.put_line (mynumber);

END;

Listing 4 – Simple calculation using variables

Variables are declared in the block that they are used. A variable declared in one block exists only within that block. This will become important later.

Note that DECLARE doesn’t end in a ‘;’ – this is because it’s an optional part of the BEGIN…END statement. mynumber is the name of the variable I have created, and I’ve said it is an integer. This means it can hold a whole number (i.e. no fractional part). If I want a real number, with a fractional part, I would have declared it FLOAT, meaning floating point. The remainder of the program should be self-explanatory if you’ve read everything so far.

I’ve already said that PL/SQL isn’t designed for interactive use – if you run this the output will be:

My number contains

101

This looks untidy because it is on two separate lines. To get the output all on one line we have to employ a bit of a bodge. Don’t worry if you don’t understand how it works right now.

In PL/SQL you can join to strings together using ‘||’. For example

dbms_output.put_line ( 'Hello ' || 'world' );

will join the two strings together before printing them as a single string. You could modify the above program with the line

dbms_output.put_line ('My number contains ' || mynumber);

This does work, but perhaps it shouldn’t! If you’re used to programming in other languages it’ll seem odd; if not skip the next paragraph.

Because || is for joining two string-type expressions together and mynumber is an integer. PL/SQL is converting mynumber into a string-type for us using automatic or implicit type conversion. This us a useful feature when it goes our why, but its important to bear in mind it’s happening because it will sometime make the wrong automatic decision. When this happens it’ll be difficult for us to figure out what went wrong. Good programming practice says you should explicitly convert between types, using the tochar() conversion function in this case. But as you as you don’t know about conversion functions yet so we’ll let this go for now.

Once you understand the above program, take a look at the example with multiple variables in Listing 5 below:

DECLARE

number_pc INTEGER;

pc_watts INTEGER;

hours_idle_per_day INTEGER;

days_on_per_year INTEGER;

total_kwh FLOAT;

total_co2 FLOAT;

BEGIN

number_pc := 4000;

pc_watts := 240;

hours_idle_per_day := 24-7;

days_on_per_year := 250;

total_kwh := hours_idle_per_day * days_on_per_year * pc_watts

* number_pc;

-- Amount of CO2 used to produce 1kWh of electricity

total_co2 := total_kwh * 0.43;

dbms_output.put_line ('Total kWh wasted = ' || total_kwh);

dbms_output.put_line ('Total CO2 produced = '

|| total_co2/1000 || ' tons per year');

END;

Listing 5 – Program to calculate energy waste using multiple variables

This program calculates the amount of Carbon Dioxide (CO₂) produced unnecessarily by leaving the computers at UEL on when not in use. It’s introduced a couple new concepts:

Firstly there are the floating point (real number) variables. We need to calculate based on fractions, so it’s convenient to use floating point.

The other new feature is a comment. If you have a ‘--’ in a line then the remainder of the line is ignored (unless the --forms part of a string). Comments can (and should) be used by programmers to make clear what their program is about. Under normal circumstances it is wise to add a comment to variables so you always know what they are for, but this was omitted in Listing 5 so it fitted on a page. There is also a comment on the line above where the calculation is performed so readers will know why the number of kWh used is being multiplied by 0.43.

The program has blank lines inserted simply to make it more readable – remember the whole thing could have been one long line if we weren’t concerned with legibility!

What the variables in Listing 5 actually mean is shown below:

Variable	Purpose/meaning
number_pc	Number of PCs on-site
pc_watts	Power consumption of each PC in Watts
hours_idle_per_day	Hours a day spent doing nothing. Note that when this is initialised (given its initial value) a calculation is used – i.e. 24 hours a day minus seven hours over-night.
days_on_per_year	Days per year when PCs are on (they’re probably off during the Summer break, so this isn’t 365
total_kwh	Used to hold the total number of kWh, or kilowatt-hours of electricity consumed
total_co2	This is the total weight of CO₂ produced in Kg.

Expressions and Variable Types

Most of the expression operators (things like *, +, -) are listed in Appendix 2 – Expression Operators. It is worth studying this now.

There are around 30 PL/SQL (and Oracle) data types, many of which are very obscure. Consult a reference manual for the version of Oracle you plan to use for a full list – and beware, it definitely changes between versions. A useful subset of types is given in Appendix 3 – Useful built-in functions.

Exercises – Chapter 1

Type in and run the examples from Listing 1 to Listing 4
Write a program that prints your name twice.
Cut/Paste or type in Listing 5 and change the assumed power consumption of the PCs to 200W each before running the program. Then reduce the total number of PCs by 20% and run the program again. Experiment!

Chapter 2 – IFs and LOOPs

Conditional Statements (IF)

In the first chapter a computer program was described as a list of instructions carried out (executed) in order. However, in order to do anything useful it is necessary for the computer to carry out repetitive tasks and made decisions. This is achieved by conditional statements and control statements (IFs and LOOPs), which vary the flow of the program from the first to last instruction.

IF Statements

Consider the following code fragment:

IF salary > 30000 THEN

dbms_output.put_line ('Fat Cat!');

END IF;

Listing 6 – Simple IF statement

This does the obvious – if salary > 30000 it prints “Fat Cat!” on the debugging terminal. However, if salary <= 30000 it does nothing.

The format of the simple IF statement is:

IF <expression true> THEN <one or more statements> END IF;

Boolean expression can be anything that evaluates to true or false – or a Boolean variable if you prefer. You can place as many statements between THEN and END IF as you like – remember, a statement ends in ‘;’. The ‘;’ after END IF closes the IF statement.

IF..ELSE

The example in Listing 6 prints a message if the condition is true, but does nothing if it is false. A more complex IF statement does something different depending on the condition and takes the form:

IF <boolean expression> THEN

<one or more statements run when true>

ELSE

<one or more statements run when false>

END IF;

Note that this example has been spread over five lines – it makes no difference to PL/SQL how it’s laid out but it makes it easier for humans to read. Listing 7 has an example of this form of IF.

IF salary > 30000 THEN

dbms_output.put_line ('Fat Cat!');

ELSE

dbms_output.put_line ('You need a pay rise');

END IF;

Listing 7 – Simple IF…ELSE statement

IF statements can be nested (remember BEGIN and END?). For example:

IF salary > 30000 THEN

IF salary > 60000 THEN

dbms_output.put_line ('Extremely Fat Cat!');

ELSE

dbms_output.put_line ('Standard Fat Cat');

END IF;

ELSE

dbms_output.put_line ('You need a pay rise');

END IF;

Listing 8 – Nested IF statements

The example above will print out “Extremely Fat Cat” for salaries greater than 60000, “Standard Fat Cat” for anything between 30001 and 60000, and “You need a pay rise” for anything else. However, writing it this way is starting to get clumsy, so it might be worth considering the ELSIF variation:

IF salary > 60000 THEN

dbms_output.put_line ('Extremely Fat Cat!');

ELSIF salary > 60000 THEN

dbms_output.put_line ('Standard Fat Cat!');

ELSE

dbms_output.put_line ('You need a pay rise');

END IF;

Listing 9 – Demonstration of ELSIF

You can have as many ELSIFs in an IF statement as you like, and it’s very useful if you have a lot of options to consider.

LOOP

It’s often necessary to repeat an operation several times; it’s what computers are good at. This involves the program going around in a loop executing statements more than once. One way to achieve this is using the LOOP statement:

LOOP

dbms_output.put_line ('Hello World!');

END LOOP;

Don’t try this one at home! It will print out Hello World forever. What happens is that when the system gets to END LOOP it goes back the matching LOOP.

Unless you really do want to go on forever, each loop needs an EXIT. There are many forms of loop in PL/SQL, and may ways to exit them. Here is one format:

LOOP

IF <boolean expression> THEN

EXIT;

END IF;

END LOOP;

When the system gets to EXIT it goes to the statement following the END of the loop and carries on from there. Obviously the boolean expression must be something that becomes true eventually. Note that EXIT is not a special part of the IF statement; it can appear anywhere inside the loop.

Here’s a full example of a loop:

DECLARE

counter INTEGER;

BEGIN

counter := 0;

LOOP

counter := counter + 1;

dbms_output.put_line('The counter is ' || counter);

IF counter > 9 THEN

EXIT;

END IF;

END LOOP;

dbms_output.put_line('Program complete');

END;

Listing 10 - Loop with EXIT

This complete program contains a loop, which is terminated when the value of the variable counter exceeds 9. Each time around the loop it will print out the current value of counter.

The next few paragraphs explain each part of the program in turn; if it makes sense already skip to EXIT WHEN.

The program starts by declaring (and creating) a variable we are going to call counter:

DECLARE

counter INTEGER;

Counter is going to be an INTEGER, which means it can hold whole numbers only, but process them quickly. Counter is going to be used to count the number of times we go around the loop.

BEGIN

counter := 0;

We’re setting counter to zero. This may be considered unnecessary as it will have been set to zero when it was created, but it’s good practice to explicitly set variables so there can be no doubt when you’re reading the code. Note that you can create a variable and set its initial value (called initialising) in DECLARE section; we’ll see that later.

LOOP

counter := counter + 1;

dbms_output.put_line('The counter is ' || counter);

The loop has begun. The first thing we do is add one to the value of counter. Secondly we’re printing out the value stored in counter, which will be ‘1’ a this stage.

IF counter > 9 THEN

EXIT

END IF;

This is our exit condition, which stops the loop going on forever. We’re testing to see if counter > 9 – which will become TRUE once counter reaches ‘10’. The first time around the loop it’s still only ‘1’, so the test is FALSE. Eventually it will reach ‘10’, and the IF statement will cause EXIT to be run. However, until this happens the program will continue with the next line:

END LOOP;

When we get there the system knows we’re at the end of the loop, so will to backwards to where the loop started and continue – this means skipping back to the matching LOOP above.

dbms_output.put_line('Program complete');

END;

The final line of the program prints “Program complete”, and we only reach here when the loop EXITs. This happens when the IF statement becomes true and the EXIT statement is run; it’s effect is to go forward to the END LOOP statement and run the statement immediately following this – in this case dbms_output.put_line(). Finally we hit the END that signals the end of the program.

EXIT WHEN

PL/SQL has many different ways of saying the same thing. SQL was written for business managers to use, and given an easy-to-understand English-like syntax. No, honestly it was – or that’s what they thought at the time. PL/SQL carries on this flexibility, so unlike more formal languages, there are multiple ways of expressing the same thing.

EXIT WHEN is an example of this – rather than having an IF…THEN…ENDIF to exit a loop there’s a shorthand syntax, as demonstrated in Listing 11. The program does exactly the same as Listing 10, but uses the EXIT WHEN syntax instead.

DECLARE

counter INTEGER := 0;

BEGIN

LOOP

counter := counter + 1;

dbms_output.put_line('The counter is ' || counter);

EXIT WHEN counter > 9;

END LOOP;

dbms_output.put_line('Program complete');

END;

Listing 11 – Loop using EXIT WHEN

I’m not going to explain this further, as it’s so close to plain English – it does exactly what it says. However, you might have spotted that the counter variable was initialised to zero at the same time it was declared. It’s a matter of taste where a variable is initialised; sometimes it’s clearer to do it at the same time it’s created, as this way you’re less likely to forget. However it could be argued that initialising a variable close to where it’s first used makes that part of the code easier to understand.

WHILE Loop

Another way of exiting a loop is demonstrated in Listing 12 – the WHILE loop. It’s an add-on to the front of a LOOP…END LOOP statement, and simply means that the system should go around the loop until the condition is false. When the condition does become false the system continues after the END LOOP.

DECLARE

counter INTEGER := 0;

BEGIN

WHILE counter < 10 LOOP

counter:=counter+1;

dbms_output.put_line('The counter is ' || counter);

END LOOP;

dbms_output.put_line('Program complete');

END;

Listing 12 – Basic WHILE LOOP

Note that the test (counter<10) is performed before the loop starts and every time the loop repeats. This means that if the test is false to begin with the code inside the loop is never run, and the test happens at a different place. In the previous examples we’ve terminated the loop when counter has reached 10; in this case we’re waiting until we’re testing for anything less than ten, because the counter is incremented inside the loop on the final run through. This is a subtle cause of confusion. ‘C’ and Java programmers with be familiar with do..while() and standard while() loops.

FOR Loop

An example of the final loop is found in Listing 13, and it’s probably the most confusing until you’ve got the hang of it. Again, it goes in front of the LOOP statement.

BEGIN

FOR counter IN 1..10 LOOP

dbms_output.put_line('The counter is ' || counter);

END LOOP;

dbms_output.put_line('Program complete');

END;

Listing 13 – Basic FOR LOOP

This program does exactly the same as the previous examples, which are counting up to ten and printing out a message each time they go around the loop. Counting through something is a very common requirement in a program, and the FOR loop is a convenient shorthand way of doing it.

What FOR counter IN 1..10 actually means is “go around the loop changing the value of the counter variable each time around from 1 to 10”. The start and end number, which must be lowest..highest, can be constants or variables or expressions. You can also make it count backwards by inserting REVERSE after IN.

Note that we’re not declaring the counter variable – the FOR loop does this for us, and it’s always of type INTEGER. However, although it looks like a variable it isn’t quite – you can’t assign it a value, only use its value in other expressions.

Exercises – Chapter 2

Listing 8 and Listing 9 aren’t full programs but can easily be made to run:

DECLARE salary INTEGER := 30001;

BEGIN

IF salary > 30000 THEN

IF salary > 60000 THEN

dbms_output.put_line ('Extremely Fat Cat!');

ELSE

dbms_output.put_line ('Standard Fat Cat');

END IF;

ELSE

dbms_output.put_line ('You need a pay rise');

END IF;

END;

Try running this, and converting Listing 9 in the same way and make sure you’re comfortable with IF statements.

Run Listing 10 to Listing 13 and verify they produce identical results. Then make them count backwards! Hint: use REVERSE in Listing 13

(NB. The code varies only slightly between listings. It will not take long to modify one into the next. The point of these exercises it to concentrate on the differences between each type of loop).

Write a program to print the 7 times multiplication table using a loop (preferably a FOR loop).

1 x 7 = 7

2 x 7 = 14

… and so on up to 12.

Write a program to calculate the change given for any price and amount paid which is less than or equal to £20. Because PL/SQL does not allow input to be read from the keyboard you will have to assign the price and amount tendered to variables near the start of the program (but you might wish to look at the & notation used in Oracle SQL * Plus). For example, if the price was 72p and the amount tendered was £5, the output should be:

The change is £4.18

4 one pound coin

1 10p coin

1 5p coin

1 2p coin

1 1p coin

Chapter 3 – Functions and Procedures

A function is an abstract entity that associates an input to a corresponding output according to some rule. If you want to get further into the mathematic definition of functions, consult a maths textbook. All you need to know here is that a function takes some value or values, does something, and gives you another value back in return.

Functions should be familiar to you from basic mathematics – things such as sine, cosine and tangent are typical. Their use in a PL/SQL expression is very similar. For example:

length = sin (angle) * 25;

When you use the sine function notation it’s shorthand for the calculations necessary to convert the angle supplied into the sine of the angle supplied – i.e:

Just looking at it can give you a headache! It actually means (and this really isn’t important here, unless you want to write your own sine function):

The point is, once a the sin() function has been written you can use it wherever you need to calculate a sine without worrying about how it does its job.

Built-in functions

PL/SQL has functions built in to the language. Lots of functions. Sin(), cos() and tan() are there, but its unlikely you’ll be needing them.

One useful function is UPPER(), which converts a string into it’s upper-case equivalent – thus UPPER('Mixed') returns 'MIXED'. This is really useful if your data contained a mixture of upper and lower case and you need to select a key regardless.

You could use in this way as shown below:

SELECT * FROM table WHERE UPPER(name) = 'FRED';

A useful arithmetical function is mod(), which takes two parameters and returns the their modulus:

MOD(10, 3) returns 1

Clear? Okay, in plain English, MOD divides the first number by the second number and returns the remainder – 10 divided by 3 is 9, with one remainder.

A selection of useful functions is given in Appendix 3 – Useful built-in functions.

User-defined Procedures

Having functions built in to the language is great if there’s one to do what you need, but if there isn’t, PL/SQL allows you to define your own. It also allows you to define a procedure, which is the name given to a function that takes a value but doesn’t give you anything back. In fact functions and procedures don’t necessarily take a value as input either. A quick example:

DECLARE

PROCEDURE hello IS

BEGIN

dbms_output.put_line('Hello world!');

END hello;

BEGIN

hello;

END;

Listing 14 - Simple procedure

This is about as simple as a procedure can be. As you can see, it’s declared in the DECLARE section of a block using the keyword PROCEDURE. “hello” is the name of the procedure, and when the procedure is over you put the name of the procedure after the END statement.

After this the listing gets on with the main part of the program, where it calls the “hello” procedure three times in a row.

Whilst this procedure is very friendly, it’s not much use unless you want to greet people a lot – it only does one thing. It’s more useful if a procedure can take a value and use it to modify its behaviour. This value is called parameter or argument depending on your taste and programming background.

DECLARE

PROCEDURE hello (name VARCHAR) IS

BEGIN

dbms_output.put_line('Hello ' || name);

END hello;

BEGIN

hello ('Fred');

hello ('Mary');

hello ('Jim');

END;

Listing 15 - Procedure with one parameter

Compare this with Listing 14 – the difference is that hello has name VARCHAR in it’s declaration with brackets around it. This means that when hello is used in the main part of the program it expects a VARCHAR (or character string) in brackets following its name. This string value is stored in the variable name and used in the procedure, where it makes its output even more friendly by greeting people in person. Note that you don’t have to specify the length of a VARCHAR when it is being used to declare a parameter in the same way as you do when defining a variable – the procedure can cope with any length.

User Defined Functions

A user-defined function is just like a procedure, except it passes something back to the main program. Here’s an example:

DECLARE

message VARCHAR(32);

FUNCTION hello (name VARCHAR) RETURN VARCHAR AS

BEGIN

dbms_output.put_line('Hello ' || name);

RETURN 'Hi';

END hello;

BEGIN

message := hello ('Alice');

dbms_output.put_line(message);

END;

Listing 16 - Simple function

As you can see, it’s similar to Listing 15 but has had some extra bits added. Firstly it’s declared as a FUNCTION instead of a PROCEDURE, and it’s had RETURN VARCHAR added before the AS. This means it will be returning a value that’s a VARCHAR to the main program.

After printing out the greeting message it has had a line added that say RETURN 'Hi'; - this is the part in the function that returns the value, and the value is the VARCHAR “Hi”.

In the main part of the program we’re calling the hello function and storing its return value in the VARCHAR variable message before we print it.

Most functions would be expected to use their parameters in some way to calculate a return value – this function is just bit of fun to keep it simple.

Next we’ll look at a more conventional mathematical function, to calculate the cube of a number.

DECLARE

FUNCTION calc_cube (n FLOAT) RETURN FLOAT AS

BEGIN

RETURN n*n*n;

END calc_cube;

BEGIN

dbms_output.put_line('The cube of 5 is ' || calc_cube(5) );

END;

Listing 17 - Cube function example

This is a simpler example of a function than Listing 16, although it breaks the printed greeting theme. Notice that this takes a FLOAT parameter and returns as FLOAT. I have called it calc_cube() instead of plain cube() because the latter is a reserved word in PL/SQL (it means something else). However, if you did use cube it would still work – your new definition would have priority over the built-in definition in this instance. However, over-riding built in functions is a bit risky – you’re going to confuse other people even if you don’t confuse yourself.

Multiple Parameters

Both functions and procedures can take more than one parameter as shown in the next example:

DECLARE

PROCEDURE printmessage (msg VARCHAR, times INTEGER) IS

BEGIN

FOR ctr IN 1..times LOOP

dbms_output.put_line(msg);

END LOOP;

END printmessage;

BEGIN

printmessage ('Message 1',5);

printmessage ('Message 2',10);

END;

Listing 18 - Multiple parameters

This simple procedure prints a message it’s supplied with the number of times required. Multiple parameters to functions work in exactly the same way.

Stored Procedures and Functions

All the procedures and functions so far have been “in-line”, which means they’ve been declared in the program that uses them. You can also store procedures permanently in the database so they’re always available.

Stored procedures are really easy - easier than in-line ones.

CREATE OR REPLACE PROCEDURE hello (name VARCHAR) AS

BEGIN

dbms_output.put_line('Hello ' || name);

END hello;

Listing 19 - Stored procedure

That’s all there is to it! Run this and then whenever you use hello(string) in a program you’ll get a greeting:

BEGIN

hello ('Bob');

END;

I have used CREATE OR REPLACE before PROCEDURE because I want to create a new procedure or replace an existing procedure called “hello” if it already exists. I could have used either CREATE or REPLACE alone if I’d known, and it’s safer to do.

To remove this procedure you can issue the command:

DROP PROCEDURE hello;

You can do exactly the same thing if you want to store a function, except you use FUNCTION instead of PROCEDURE. However, note that Oracle won't let you store a function with the same name as an existing procedure.

CREATE FUNCTION calc_cube (n FLOAT) RETURN FLOAT AS

BEGIN

RETURN n*n*n;

END calc_cube;

Listing 20 - Stored Function

This is how you store the calc_cube() function we wrote earlier, assuming there isn’t a calc_cube function or procedure already stored. If there is you can DROP FUNCTON first, or use CREATE OR REPLACE You can test your stored function with the line below:

BEGIN dbms_output.put_line(calc_cube(3)); END;

Notice here how the function is being treated like any other expression - it evaluates to a value and dbms_output.put_line() prints it. As far as dbms_output.put_line() is concerned it’s being asked to print 9; it doesn’t care whether it comes from an expression, function, constant or combination of all three.

Procedure Variables

Procedures and functions can have their own private variables if required (in addition to their parameters). These are declared between IS or AS and BEGIN in the same way as any other variable in a block (appearing between the DECLARE and BEGIN. The trick is that variables declared between IS or AS and BEGIN exist only within the procedure or function in question – you can have a variable of the same name elsewhere in the program without them conflicting. See Listing 25 for an example of this being used.

Exercises – Chapter 3

Try to do at least three of these.

Type in and run Listing 15 and Listing 16 and run them to see that the output is as you expect. Don’t copy/paste them in as you’ll remember how they work much better if you’ve gone through the process of typing them.
Write a program to print the numbers from 1..10 followed by either Odd or Even as appropriate. You might wish to use the built-in mod(n,p)function, which returns n modulus p. In case anyone’s forgotten basic arithmetic through lack of use, this means remainder after an integer division. If you divide by two the modulus will zero for an even number, and one for an odd number.
Re-write the answer to question 4 in Exercises – Chapter 2 (the one that prints change) to use used a new procedure, which you will write. This procedure takes as its parameters a denomination (e.g. ‘1p coin’, ‘50p coin’, ‘5 pound note’) as a string, together with the number of units given. It then prints fully formed line on the screen, taking plurals into account. For example, if it was called with ‘5p coin’ and the number 4 it would print “4 x 5p coins’, if it was called with one 2p coin it would print ‘1 x 2p coin’, or even better, ‘A 2p coin’. Make the output as English-like as you can.
Write a function that takes a date (in Oracle format) and returns a string of either “Weekend” or “Weekday” as appropriate. Look at the TO_CHAR() built in function when extracting the day-of-week from a date – TO_CHAR(date,DAY) returns the day-of-week in text form.
Write a test program to make sure your function works. You can take get today’s date into a DATE variable by assigning it from the system variable SYSDATE. You can work out the date the next day by adding one to your variable (or next week by adding seven) – so print out the days of the week for the next two weeks followed by “Weekend” or “Weekday” as appropriate.

Chapter 4 – Working with Databases

As discussed already, PL/SQL is solely designed for manipulating databases; it has no other input or output facilities (apart from debugging) but when it comes to close integration with SQL it’s got it sorted.

To follow the examples and exercises for the remainder of this tutorial you will need to load an Oracle database using the script found in “Appendix 4 – Sample Data”

Simple Query

Take a look at Listing 21:

--- Print the job title and salary for 'Jones'

DECLARE

jobdesc VARCHAR(20);

jobsal INTEGER(7,2);

BEGIN

SELECT job,sal INTO jobdesc,jobsal FROM emp

WHERE ename='Jones';

dbms_output.put_line ('The job returned is ' || jobdesc

|| ' and it pays ' || jobsal);

END;

Listing 21 - Simple database query

The interesting bit is the SELECT statement – it’s just like the plain SQL version except it has an INTO thrown in there. This means that the fields selected are to be copied into the variables specified. In this case job goes into jobdesc and sal goes into jobsal. The remainder of the program simply prints out the value of these variables.

This is a very simple query and will only return a single record (assuming there is only one ‘Jones’ in the database). There is therefore only one possible value for job and sal. But suppose the query returned more than one like – suppose we changed the query to WHERE mgr=’7698’? This would return several possible records[1]. What happens? Does it use the first one to match? Does it crash? Well yes, your program will crash because what you’ve asked it to do is nonsense.

The fact is that this form of SQL query is only really useful if you’re using an aggregate function[2] – possibly reading the total number of records into a variable.

--- Print the number of Clerks

DECLARE

number_clerks INTEGER;

mean_sal NUMBER(7,2);

BEGIN

SELECT COUNT(*),AVG(sal) INTO number_clerks,mean_sal

FROM emp

WHERE job='Clerk';

dbms_output.put_line ('There are ' || number_clerks

|| ' clerks earning ' || mean_sal

|| ' on average');

END;

Listing 22 - Using SELECT with aggregate functions

Cursors

If you’re reading and processing multiple records you need to use a mechanism called a cursor[3]. An example of this is shown in Listing 23, where we are extracting everyone in department 30. It looks a lot more complicated, so it’s explained below.

-- Print the job description and salary for everyone in

-- department 30

DECLARE

jobdesc varchar(20);

jobsal integer(7,2);

CURSOR jobs IS SELECT job,sal FROM emp WHERE deptno='30';

BEGIN

OPEN jobs;

LOOP

FETCH jobs INTO jobdesc,jobsal;

EXIT WHEN jobs%NOTFOUND;

dbms_output.put_line ('The job returned is ' ||

jobdesc || ' and it pays ' || jobsal);

END LOOP;

CLOSE jobs;

END;

Listing 23 - Using a CURSOR

DECLARE

jobdesc varchar(20);

jobsal integer(7,2);

Here we’re simply definint the variable used to store the recovered data. – just like in Listing 22.

CURSOR jobs IS SELECT job,sal FROM emp WHERE deptno='30';

Here we’re defining the cursor (still in the DELCARE section). Cursors need a name, and we’re calling this one ‘jobs’. This is followed by IS. The remainder of the line is simply an SQL query of the type you’re used to.

BEGIN

OPEN jobs;

Cursors need to be opened and closed. This concept will be familiar to anyone who’s used a programming language to manipulate files or other resources. It’s simply telling the system that it’s about to start reading records from jobs, so get ready to start supplying the data starting with the first record.

LOOP

FETCH jobs INTO jobdesc,jobsal;

Here we’re starting the loop, and the first thing we do is fetch in the first record. If you look back, jobs was defined as returning two values, desc and sal. These two values are copied into the two variables specified after INTO.

EXIT WHEN jobs%NOTFOUND;

This is a clever one – we obviously want to exit the loop once we’ve read all the data. If you FETCH from a cursor and you’ve run out of data, <cursorname>%NOTFOUND becomes true. The % symbol gets used in PL/SQL to specify an attribute of something or other. In this case it’s the not found/found status of a cursor.

dbms_output.put_line ('The job returned is ' ||

jobdesc || ' and it pays ' || jobsal);

Here we’re simply printing out the results for each record we find.

END LOOP;

CLOSE jobs;

END;

This is the end of the program, so we’re closing the loop and closing the jobs cursor before finally ending the program. Cursors must be opened and closed as mentioned above. What’s less obvious at first sight is that they can be opened and closed many times in a program, and you can have multiple cursors open.

Next we’re going to look at a more complex example, in which a record is selected, updated and written back in PL/SQL. But first first we’ll start with a query which will print out the salary for employee 7369.

SELECT ename,sal FROM emp WHERE empno=7369;

You can try this to make sure your database is loaded correctly. Now for the program, which will implement a procedure called raise_salary. Unsurprisingly this procedure takes an employee and raises their salary by the specified amount[4]. The procedure will obviously need two parameters – the employee ID and the amount to add to the salary.

Inserting Data

Adding a new row to a database in PL/SQL is simple – it’s the same as the plain SQL insert command. The twist is that the VALUES can be constants or variables.

INSERT INTO dept VALUES ('50','Skunkworks','Kansas');

It’s common to build a system where SQL insert statements have been replaced by functions or procedures which do the same job. It gives the software design flexibility, as the underlying table may change but the parameters supplied to the procedure can remain the same.

CREATE PROCEDURE NewDepartment ( deptno CHAR,

name VARCHAR,

location VARCHAR) AS

BEGIN

INSERT INTO dept VALUES (deptno, name, location);

END;

Listing 24 - Inserting Data

Then when you want to create a new department you simply need to call:

NewDepartment ('50','Skunkworks','Kansas');

The big advantage is that if you change the dept table by adding a telephone number (for example) you can have the NewDepartment() procedure fill this in as NULL for you. If you used SQL insert statements you’d have to change it everywhere they appeared or the program would crash. For an improved version of this procedure see Listing 28.

Simple Updates

-- Program to demonstrate reading and writing a database

DECLARE

emp_id NUMBER; -- The empno of the employee getting the

-- raise (in the main program)

raise_amount INTEGER(7,2); -- The amount of the salary

-- raise

-- The next line declares a procedure

PROCEDURE raise_salary (emp_id CHAR,amount integer) IS

current_salary NUMBER (7,2); -- This is a variable

-- in the procedure

BEGIN

-- Load the current salary

SELECT sal INTO current_salary

FROM emp WHERE empno = emp_id;

-- Write back the increased salary

UPDATE emp SET sal = current_salary + amount

WHERE empno = emp_id;

END raise_salary;

BEGIN -- main program

emp_id :='7369';

raise_amount := 500;

raise_salary(emp_id, raise_amount);

END;

Listing 25 - SQL SELECT and UPDATE in a procedure

This program will fail if there is more than one employee with the same ID, but ‘this can never happen’ – or if it does, something more fundamental has already gone wrong.

Note the variable current_salary in the procedure. This is the first example in this tutorial of a procedure having its own private variable other than its parameters. The first two variables (emp_id and raise_amount) are declared outside of the procedure and therefore exist in the main body of the program.

The SELECT statement works as described in Listing 21. The UPDATE statement which follows works pretty much as expected if you remember SQL. Note the use of SET to set individual fields in the record.

Exercises – Chapter 4

Write a short program to print the answer to the query:

SELECT ename,job,deptno FROM emp WHERE ename='Turner';

The output should look something like:

Name is Turner, Job is Salesman, department is 30.

Write a program to print a list of department names and locations (from the dept table). The output should look like:

Department, Location

Accounting, New York

Research, Dallas

Sales, Chicargo

Operations, Boston

3. Adapt the program you wrote for question 2 above, so it prints the name and job for every employee in the emp table instead.

4. Write a program to cut every manager’s salary by 20%. (The company has more than one manager for every three workers – we need to encourage a few to leave). However, do not let their salary fall below 2750.

5. Write a program to list every employee and their manager, their manager’s manager and so on until you reach the company president. For example:

Smith works for Ford works for Jones works for King.

Allen works for ... and so on.

Chapter 5 – Advanced Database

An overview of working with databases was found in chapter 4. This chapter contains a few features and tricks you’re likely to find useful – Sequences and Triggers and immediate execution being the main ones.

EXCEPTION

This part still needs to be written – it was covered in lectures.

declare

my_error EXCEPTION;

begin

dbms_output.put_line('Line 1');

dbms_output.put_line('Line 2');

raise my_error;

dbms_output.put_line('We never get to here');

exception

WHEN my_error THEN

dbms_output.put_line('In my_error exception');

WHEN OTHERS THEN -- If some other error happens we get here

dbms_output.put_line('Something else went wrong - this should not happen');

end;

This code declares a a new type of exception called my_error, and half-way through the code causes it to happen simply by using the raise statement. In reality this would be used in an IF... THEN ... END IF statement - it does not make sense to always raise it - it's a bail-out option when things have gone really bad.

The effect is to jump straight to the WHEN my_error bit in the exception section - like a GOTO in some other languages.

The WHEN OTHERS THEN line is the default handler for all exceptions not listed in other WHEN lines. Use it with care - if an error occurs then Oracle will run this instead of printing out an error message which may tell you something about what went wrong.

COMMIT and ROLLBACK

So far we’ve ignored the need for COMMIT. This keyword simply saves all the changes you’ve just done to the database – just like saving a document in a word processor. The reason we haven’t used it is that Oracle will COMMIT everything automatically when you log off.

However, if you don’t use COMMIT you can undo all the changes you’ve made using ROLLBACK – very useful if you’re half way through a complex update of multiple tables and you realise you’ve got a problem and need to start again! ROLLBACK sets things back to the point just after the last COMMIT, and any data you’ve changed (including deleted or inserted rows) goes back to the way it was. Phew!

If that wasn’t good enough, there’s also SAVEPOINT. This saves a snapshot of the database between COMMITs. You can give the snapshot a name, and ROLLBACK to whichever snapshot you wish until you COMMIT.

DECLARE

emp_no CHAR(4);

emp_name VARCHAR(10);

emp_sal NUMERIC(7,2);

BEGIN

------ First we find Blake...

SELECT empno, ename, sal

INTO emp_no, emp_name,emp_sal

FROM emp WHERE ename = 'Blake';

------ then we cut his salary in half!

UPDATE emp SET sal = sal/2 WHERE empno = emp_no;

------ now we're going to cause real damage

SAVEPOINT start_delete;

------ First we delete employee 7782 (Clark)

DELETE FROM emp WHERE empno = '7782';

------ Then we attempt to insert a row with a duplicate key

INSERT INTO emp

VALUES ('7934', 'Patel', 'Analyst', '7782',

'01-apr-86', 1700.00, NULL, '60');

------ We never get to the commit below.

COMMIT;

dbms_output.put_line('Job done');

EXCEPTION

------ This exception will happen, so we roll back

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK TO start_delete;

dbms_output.put_line('It all went wrong!');

END;

Listing 26 - SAVEPOINT and ROLLBACK

Take a look at Listing 26, which demonstrates all these features. Look at the emp table before and after – it will have rolled back to the point after Blake’s salary was cut but the deleted employee 7782 (Clark) will have been restored when the error occurs. You can restore Blake’s original salary by issuing another ROLLBACK from the command line if you wish.

Note that COMMIT has a number of possible parameters that will change the way it operates – see the Oracle manual for the version you are using for a full list, although the default operation is all you need in most cases.

Beware of using COMMIT too often as it will over-stress Oracle eventually. In particular, don’t place it inside a loop where it gets used every time. This may seem attractive, but it will slow the system down considerably as it saves multiple snapshots in case a rollback is needed.

Also note that other users of the database won’t see your changes until you’ve issued COMMIT. Oracle stores up the changes and only makes them when it get’s a COMMIT; if it gets a ROLLBACK it just throws them away.

Sequences

Another useful feature of SQL that you can use in PL/SQL is SEQUENCE. This is a mechanism used to generate a sequence of numbers, normally starting at one and going upwards and their main use is to provide a unique primary key.

You can create a sequence in the same way as you create a table:

CREATE SEQUENCE invoice_number;

To read the next value in the sequence in SQL you can use a line like:

SELECT invoice_number.NEXTVAL FROM DUAL;

SELECT reads and prints a value from a sequence as it does for a table. INVOICE_NUMBER is the sequence and the .NEXTVAL following means the next value (i.e. the next number in the sequence). But what is the FROM DUAL all about? Basically we’re looking at a bodge. The SQL SELECT statement must include a FROM and the name of a table. Try leaving it off and it’ll print an error saying that FROM is missing, in spite of the fact we’re reading from a sequence and not a table.

Technically NEXTVAL is a pseudo-column, which means it’s used like a column bit isn’t. A pseudo-column can be read from any table and Oracle will intercept it and supply the calculated value as required, but you have to fool Oracle by giving it a real table to work with. There is a table called DUAL in every Oracle database – it’s one row by one column and contains one character – it’s a dummy table. By convention, whenever you need to read a pseudo-column you do so from the DUAL table – you know it’s going to be there and any Oracle programmer will understand that it’s being used as a dummy table. There’s probably a very good reason why it’s called DUAL instead of DUMMY, but I’ve yet to find it.

So, if you type in the lines above the first select will return the value 1, the second time you run it you’ll get 2 and so on.

If the business with the dual table wasn’t inconvenient enough, you can’t (currently) read the value of a sequence directly in PL/SQL. Instead you have to use a SELECT statement to read it into a PL/SQL variable (having created the sequence as shown above):

DECLARE

next_invoice NUMERIC;

BEGIN

SELECT invoice_number.NEXTVAL INTO next_invoice FROM DUAL;

dbms_output.put_line ('The next invoice will be '

|| next_invoice);

END;

Listing 27 - using a SQUENCE in PL/SQL

If you want to re-read the current value of a sequence you must used .CURRVAL instead of .NEXTVAL, although this is rarely useful in PL/SQL. To delete a sequence you can DROP it in the same way as a table:

DROP SEQUENCE INVOICE_NUMBER;

There are various options to set the starting value and increment for sequences – see the documentation for the version of Oracle you are using. A default sequence starts at one and goes upwards.

Finally an example of a sequence can be used to improve the NewDepartment() procedure in Listing 24. This starts by creating a sequence (starting at department 60 and incrementing by 10 – the syntax is obvious). Then it creates (or replaces) the NewDepartment procedure with a new one that does not require you to supply a department number. Instead it reads this from the sequence into a variable called deptno and this is then inserted into the table[5].

CREATE SEQUENCE dept_seq START WITH 60 INCREMENT BY 10;

CREATE OR REPLACE PROCEDURE NewDepartment (name VARCHAR,

location VARCHAR) AS

deptno CHAR(2); -- Holds new department number

BEGIN

SELECT dept_seq.NEXTVAL INTO deptno FROM DUAL;

INSERT INTO dept VALUES (deptno, name, location);

END;

Listing 28 - NewDepartment() using sequence

You can call this with:

CALL NewDepartment ('Aliens', 'Area 51');

Triggers

Again – this was covered in lectures and lecture notes. This section will be written properly in due course – in the mean time there are some draft notes in brown:

-- Trigger demo

CREATE OR REPLACE TRIGGER Promotion

AFTER INSERT OR UPDATE OF job ON emp

FOR EACH ROW

BEGIN

dbms_output.put_line ('Someone has a new job');

dbms_output.put_line (:NEW.ename || ' was ' || :OLD.job || ' but is now ' || :NEW.job);

END Promotion;

To test this out, try the line:

update emp set job='IT' where empno='7369';

This piece of code adds a trigger. I've called it 'promotion' so it's got a name and can be deleted again. It says 'CREATE OR UPDATE' so if a trigger of the same name exists it will be overwritten (otherwise it would cause an error).

I've said 'AFTER INSERT OR UPDATE' -you can choose one or many times for the trigger to be active, BEFORE or AFTER. I've put the optional 'OF job' in - this limits it to an update of a particular field, not the whole record. I could have just said 'ON emp', in which case it'd have been called whatever field was changed.

FOR EACH ROW is very common - we're normally interested in dealing with row updates.

I could have put a 'WHEN' clause a the end of the trigger conditions, for example, WHEN (NEW.job='Manager')

Note the : in front of :OLD and :NEW. These are referring to the old values in the record and the new ones (the ones about to be written). Remember, the trigger occurs before the changes are committed. Use OLD and NEW to select which field you're interested in - and you can modify the values of the NEW version before its written. Incidentally, you don't need the colon in the trigger condition - particularly the 'when' clause. It's also normal (and but pointless) to see NEW and OLD aliased in other examples.

Appendix 1 – Variable Types

The following table is a useful sub-set of variable types; for more details (and more types) consult the manual for the version of Oracle you are actually using

Type	Explanation
CHAR(size)	A string no longer than size characters. Will use at least size bytes of storage
VARCHAR2(size)	A variable length string, at most size characters long. If possible the system will use less storage than specified if the string is shorted than size. A VARCHAR2 is slower to process than a CHAR, so if the string is small then a CHAR may be a better choice. Note that it really does end with the digit ‘2’ – an old version of VARCHAR exists and remains for compatibility reasons, but is best not used.
INTEGER	A whole number (no fractional part). A general purpose number, particularly useful for programming purposes.
NUMERIC (p)	A whole numeric value of precision p. (i.e. p digits long). This is different from INTEGER, which may not be able to hold larger values. If you ask for too many digits in a NUMERIC it will cause an error rather than quietly dropping significant digits! The down-side is that a NUMERIC may be slower and use more storage space than an INTEGER.
NUMERIC (p,s)	As numeric, but with an additoial ‘scale’ value ‘s’. This simply means that if p is 10 and s is two then you have a ten digit number with two digits after the decimal point (e.g. 12,345,678.90).
FLOAT	A numeric value in floating point format. Floating point (standard form) means that a number is by definition an approximation. A float is useful for scientific an engineering calculations where very large or small numbers may be needed, but absolute precision is not. Don’t use a float for an amount of money – customers get very upset over errors of a few pence! Use a NUMERIC instead, where the precision is absolute.
DATE	Holds a date – very useful if you want to convert dates to local formats, or perform calculations based on dates (e.g. what is the date in 30 days time from an existing date?)
BOOLEAN	True or false. Beware – you can’t store a BOOLEAN in an Oracle database, but you can have BOOLEAN variables in PL/SQL

Appendix 2 – Expression Operators

There a several symbols used in expressions in PL/SQL. Some of the more useful are shown below:

Symbol	Example	Type	Meaning
**	a ** b	Numeric	a raised to the power of b (a^b)
NOT	NOT a	Boolean	True if a is false, or false if a is true
*	a * b	Numeric	Multiply a by b
/	a / b	Numeric	Divide a by b
+	a + b	Numeric	Add a and b
-	a - b	Numeric	Subtract b from a
\|\|	a \|\| b	String	String a with string b added to the end.
>	a > b	Boolean	True if a is greater than b
<	a < b	Boolean	True if a is less than b
>=	a >= b	Boolean	True if a is greater than or equal to b
<=	a <= b	Boolean	True if a is less than or equal to b
=	a = b	Boolean	True if a is the same as b
!=	a != b	Boolean	True if a is not the same as b
AND	a AND b	Boolean	True if a and b are both true
OR	a OR b	Boolean	True if a or b are true

These operators are group in order of precedence – the order they’ll be evaluated in an expression if brackets aren’t used. This means that ** (raise to the power of) will be evaluated before * or / which will be evaluated before + or -. This is what you’d expect in normal mathematical notation, and means the Boolean AND and OR can used in expressions such as this without the need for brackets.

IF a > b/2 AND c <= 10 OR x=99 THEN…

It means:

IF ((a > (b/2)) AND (c<=10) OR (x=99)) THEN…

Note that => is not an operator and will generate some very strange error messages if you use it. (It’s an association operator, if you wish to look that up).

It’s also worth noting that there are other ways of expressing some of these operators, omitted to avoid confusion. For example, != is synonymous with <>, ~= and ^= - however, some of these mean other things in different languages and are best avoided.

Appendix 3 – Useful built-in functions

This appendix is currently blank

Appendix 4 – Sample Data

You will need two tables of sample data for these exercises. These may be created by the following SQL commands, which may be found on UEL Plus in a form that can be copied and pasted into an Oracle command line.

create table dept (deptno char(2) not null, dname varchar(15), loc varchar(15),

primary key(deptno));

insert into dept values ('10','Accounting','New York');

insert into dept values ('20','Research','Dallas');

insert into dept values ('30','Sales','Chicargo');

insert into dept values ('40','Operations','Boston');

create table emp (empno char(4) not null,

ename varchar(10),

job varchar(10),

mgr char(4),

hiredate date,

sal numeric(7,2),

comm integer,

deptno char(2),

primary key(empno),

foreign key(deptno) references dept);

insert into emp values ('7369', 'Smith','Clerk', '7902','17-dec-80', 800.00,NULL,'20');

insert into emp values ('7499', 'Allen','Salesman', '7698','20-feb-81', 1600.00,300,'30');

insert into emp values ('7521', 'Ward','Salesman', '7698','22-feb-81', 1250.00,500,'30');

insert into emp values ('7566', 'Jones','Manager', '7839','02-apr-81', 2975.00,NULL,'20');

insert into emp values ('7654', 'Martin','Salesman', '7698','28-sep-81', 1250.00,1400,'30');

insert into emp values ('7698', 'Blake','Manager', '7839','01-may-81', 2850.00,NULL,'30');

insert into emp values ('7782', 'Clark', 'Manager', '7839', '09-jun-81', 2450.00, NULL, '10');

insert into emp values ('7788', 'Scott', 'Analyst', '7566', '09-dec-81', 3000.00, NULL, '20');

insert into emp values ('7839', 'King', 'President', null, '17-nov-81', 5000.00, NULL, '10');

insert into emp values ('7844', 'Turner', 'Salesman', '7698', '08-sep-81', 1500.00, 0, '30');

insert into emp values ('7876', 'Adams', 'Clerk', '7788', '12-jan-83', 1100.00, NULL, '20');

insert into emp values ('7900', 'James', 'Clerk', '7698', '12-mar-81', 950.00, NULL, '30');

insert into emp values ('7902', 'Ford', 'Analyst', '7566', '03-dec-81', 3000.00, NULL, '20');

insert into emp values ('7934', 'Miller', 'Clerk', '7782', '23-jan-82', 1300.00, NULL, '10');

Table of Listings

Listing 1 – Simple program to print PI 5

Listing 2 – Multi-statement program.. 5

Listing 3 – Program with nested blocks. 5

Listing 4 – Simple calculation using variables. 6

Listing 5 – Program to calculate energy waste using multiple variables. 7

Listing 6 – Simple IF statement 14

Listing 7 – Simple IF…ELSE statement 15

Listing 8 – Nested IF statements. 15

Listing 9 – Demonstration of ELSIF. 15

Listing 10 - Loop with EXIT. 16

Listing 11 – Loop using EXIT WHEN.. 18

Listing 12 – Basic WHILE LOOP. 18

Listing 13 – Basic FOR LOOP. 20

Listing 14 - Simple procedure. 23

Listing 15 - Procedure with one parameter 23

Listing 16 - Simple function. 24

Listing 17 - Cube function example. 24

Listing 18 - Multiple parameters. 25

Listing 19 - Stored procedure. 25

Listing 20 - Stored Function. 25

Listing 21 - Simple database query. 27

Listing 22 - Using SELECT with aggregate functions. 28

Listing 23 - Using a CURSOR.. 28

Listing 24 - Inserting Data. 30

Listing 25 - SQL SELECT and UPDATE in a procedure. 31

Listing 26 - SAVEPOINT and ROLLBACK.. 34

Listing 27 - using a SQUENCE in PL/SQL. 35

Listing 28 - NewDepartment() using sequence. 36

[1] Oracle doesn’t support the TOP clause found in other SQL implementations to limit results

[2] Aggregate functions are those which return a single value from a query – e.g. COUNT(),MAX(),SUM(). They’re part of standard SQL, knowledge of which is a pre-requisite for this tutorial. If you’ve forgotten, look them up!

[3] The term cursor originally meant runner, and was later used for a marker that ‘ran’ along data to mark a position (such as the cursor on a slide-rule). Here it is describing something that runs down a table selecting a row at a time. It’s got nothing to do with text cursors or mice!

[4] It is possible to accomplish the same effect using SQL but this is a PL/SQL tutorial, before anyone points it out

[5] Because INSERT is an SQL statement you can use the value dept_seq.nextval directly in that without reading it into a variable using the SQL SELECT statement. However, in any non-trivial example you need to know the key to the record you’ve just inserted.