Oracle – for when it was like that when you got there

Main menu

Post navigation

PL/SQL – A Programmer’s Introduction – or Welcome to the Dark Side

This week, the Open Source Karma has been cast-aside. We’re going proprietary in a big way. We’re going to the very heart of Oracle’s power, deep inside the RDBMS – yes – it’s PL/SQL.

This post is dedicated to ( and essentially co-written by) Simon. Yes, my long-time best mate, long-time Luton Town fan, long-time Teradata expert and long time everything really ( we’ll he’s not as young as he was).
After all these years, Simon has become a bit curious about this PL/SQL thing I’m always going on about and would like to know more.
It is this desire – and large amounts of beer – that has persuaded him to play the Igor to my mad scientist and have a wander through this very quick guide to the language at the heart of most Oracle applications. In fact we came up with several possible descriptions of Simon’s role in this post, but he had a “hunch” that this was the right one.
So for him, and any other programmers who want to get up and running with PL/SQL, but don’t need to be told what a variable is, what follows is – not so much a PL/SQL 101 – as a PL/SQL 23-and-a-bit.

I want to get you up and running in PL/SQL as quickly as possible, so I’m not going to go into long explanations about SQLDeveloper, SQL or even too much about the Oracle RDBMS itself. It’s also not a best practice guide. It’s aimed at simply giving you enough information to get playing with PL/SQL. I’ve added some further links at the end that may be of interest.

Assumptions

I’m going to start by making a number of assumptions about you.
First, you have some experience of a high-level programming language ( e.g. C, Java, VB) so, like Simon, you know what a variable is and how to use it.
Secondly, I’m going to assume that you have at least a working knowledge of SQL.
Third – I’ll assume that you have an Oracle database available to play on. Indeed, I’ve made sure that all of the examples here will work on Oracle Express Edition ( Oracle XE) – Oracle’s free version of their RDBMS.
You can get Oracle Express Edition from here.
If you are one of those hardy souls who wants to run it on Ubuntu, alternative instructions on how to download and install can be found here.
SQLDeveloper is also from Oracle and also free. You can get it from here (I’d suggest you go for either 2.1 or 3.0 Early Adopter at the time of writing as 2.1.1 has one or two “quirks”).
Finally, I’m assuming here that your HR user has been unlocked. If not, you need to connect to the database as system and unlock the account. If you connect in SQL*Plus, you need to run :

ALTER USER hr UNLOCK
/

Incidentally, it’s good practice to change the password of these default users ( such as hr) when you unlock them for security reasons. You can do this as follows ( still as system) :

ALTER USER hr IDENTIFIED BY new_pwd
/

Where new_pwd is the password you want to set for the hr user.
For the remainder of this post, I’m going to assume that the hr user has it’s default password. If you do change it then you need to substitute the password “hr” with the one you’ve set for it when connecting using the following examples.

This PL/SQL Thing – What is it ?

Procedural Language SQL is, in essence, the Ada programming language with SQL extensions built into the Oracle RDBMS. It enables you to enforce complex business rules that you couldn’t otherwise implement through SQL or a relational design.
It also allows you to write application logic in the database.

How do I get to it ?

Generally speaking, anywhere you can get to enter an SQL statement. You need to be connected to a running database. Essentially this means SQL*Plus ( from the command line) or SQLDeveloper ( or similar IDE).
A small bit of admin before we get going – you may want to create a separate directory to save your PL/SQL scripts to (e.g. c:\pls_progs on windows or $HOME/pls_progs on Linux).

SQL*Plus

If you’re running an Oracle client ( or XE for that matter), part of the default install will have been to add the software to your path so, whether your on Linux or Windows, you should be able to do the following from Terminal / Command Window :

sqlplus hr/hr@XE

My preferred way of working is to use a text editor to write code and save to files and then run the code in SQL*Plus. This is the method I’m using throughout this post. There are other options open to you, however.

SQLDeveloper

With SQLDeveloper, make sure you’re connected to the database and you’re ready to go.

Run SQL Command Line Menu Option

If you’ve installed Oracle XE, under the Oracle Database 10g Express Edition Menu, you’ll have an option called Run SQL Command Line.
If you open this, you can connect as follows ( in this example, the user your connecting as is hr with a password of hr and the database is called XE) :

conn hr/hr@XE

In both Windows and Linux, we do need make some alterations to persuade this menu option to point to our preferred directory ( i.e. c:\pls_progs – where we’re going to save our PL/SQL files to).
In Windows, you simply need to go to the shortcut on the menu and right-click; select properties from the pop-up menu and then enter c:\pls_progs as the startup directory.
In Linux, things are a little more involved – we need to set the SQLPATH environment variable so that the script starting the command line picks it up. To do this, open a Terminal Window and :

The Block with No Name

Actually it’s known as an Anonymous Block, a PL/SQL program that is not stored in the database ( Stored Program Units), but which is normally saved to a file.
Now, for the most part, PL/SQL programs perform I/O against database tables. However, tradition dictates that the first program in a guide such as this must write out some twee message to the screen. So….

Yes, tiresome and tortuous link – Block with No Name, Man with No Name , Clint Eastwood, Dirty Harry.

More immediately relevant, executable statements are terminated with a “;”
We can also see that PL/SQL is a Block Structured Language. The full skeleton structure of a block is :

DECLARE
-- variable declarations go here ( also terminated by a ; )
BEGIN
--executable statements go in this bit. BEGIN is mandatory
EXCEPTION
--any errors are handled in this bit ( optional)
END; --Note the ; after the mandatory END keyword
/

The slash means run the block
Incidentally — is a single line comment. You can also use /* */ for multi-line comments.

One other point to note – PL/SQL is not case sensitive. Begin = begin = BEGIN. You can use uppercase, lowercase, you can even use camel case and PL/SQL won’t get the hump. This also applies to any stored program units you call (we’ll get onto them later). As you’d expect, string comparisons are case sensitive – ‘SIMON’ does not equal ‘Simon’.

Let’s take this example and make it a bit more realistic. First, we’ll create a table and then use PL/SQL to insert a row.NOTE – at this point you may want to use a separate user so that the objects created in these examples don’t get mixed up with the HR schema, which is used in lots of Oracle tutorials.
So, if you’re playing around on XE, you can create a user by connecting to the database as system and running the following :

Note the COMMIT – you need to issue this command to save the change to the database.

This time we’ve declared a variable and assigned it a value, so it’s probably a good time to whiz through :

Datatypes and Operands

Datatypes first – you can use any of the standard oracle database datatypes ( DATE, NUMBER VARCHAR2 etc).
VARCHAR2 is a bit of an odd one. First off, in other databases, it’s VARCHAR. Secondly, in PL/SQL you can define it as up to 32767 characters, but the maximum length in a database table is only 4000.

Additionally, you’ll probably come across the following types quite a lot in PL/SQL :

– BOOLEAN
– PLS_INTEGER ( or it’s predecessor BINARY_INTEGER) – an integer between -2,147,483,647 and 2,147,483,647
– table.column%TYPE – declares a variable to be of the same type as the table.column specified
– CURSOR – I’ll come onto this in a bit.

Operands

All the usual suspects are here ( + – * /) and you can generally use any SQL functions in-line in PL/SQL.
Points to note are that the equality operator is = and the assignment operator is :=
There are no increment/decrement operators ( ++ or – -) . In order to increment a numeric variable by 1, you’d write :

l_num := l_num + 1;

Transactions

When you insert or update data in Oracle, the change isn’t saved until the transaction is either committed or rolled back. A transaction can span multiple DML statements.
To save changes to the database you need to issue a COMMIT ( as above). To undo your changes, it’s ROLLBACK;.
NOTE any DDL statement will also end and commit a transaction. That’s generally any statement beginning with CREATE, ALTER or DROP.
If you don’t COMMIT your changes, then by default, they’ll be rolled back when you end your session – i.e. they won’t be saved.

If Feel Lucky Then…

Moving swiftly on, time for a quick word about conditional statements and loops.
As so much of modern mathematics ( and therefore computing), emanated from the Arab world, it is in some small way fitting that I learned to avoid a common pitfall with the PL/SQL IF THEN ELSE syntax from a picture not unlike this one :

Elsif says, "END IF is TWO words !"

Whilst we’re here, we may as well meet the FOR loop at the same time as it’s probably the most commonly used loop construct in PL/SQL :

We can see that the loop variable bullet is declared implicitly – PL/SQL saves us the bother.

The FOR loop is the most commonly used largely due to it’s relationship to the CURSOR ( see, I told you I’d mention them).

Cursors and For Loops

There are two types of cursor – implicit ( that return only a single row) and explicit.
An explicit cursor can be thought of as a statement that will generate a result set that we can then manipulate in PL/SQL.
Once again, because it’s the law, we’ll have a look at a sensible example, this time using the EMPLOYEES and DEPARTMENTS tables in the HR schema.

Don’t forget the set serveroutput on or PL/SQL will get all tight-lipped and not print out the DBMS_OUTPUT lines. Incidentally, Elsif has some strong opinions on the subject of serveroutput. Roughly translated – it’s all one word.
Explicit cursors ( like c_depts in this example) are declared explicitly in the DECLARE section.
You then need to OPEN them, FETCH them INTO the appropriate variables and CLOSE them when you’re done.
You’ll notice that we only get one row back here as we only do a single fetch of the cursor.
Explicit cursors are commonly used with FOR loops ( incidentally, you can also pass parameters to an explicit cursor) :

set serveroutput on
DECLARE
CURSOR c_emp( cp_dept_id hr.employees.department_id%TYPE) IS
SELECT first_name, last_name, hire_date
FROM hr.employees
WHERE department_id = cp_dept_id;
l_emp_count PLS_INTEGER;
BEGIN
--
-- Implicit cursor – returns one row, no more, no less
--
SELECT COUNT(*) INTO l_emp_count
FROM hr.employees
WHERE department_id = 30;
IF l_emp_count > 0 THEN
--
-- Cursor For Loop – r_emp is a record of the type of the select statement in the cursor
--
FOR r_emp IN c_emp(30) LOOP
DBMS_OUTPUT.PUT_LINE( r_emp.first_name||' '|| r_emp.last_name||' '|| r_emp.hire_date);
END LOOP;
END IF;
END;
/

You’ll notice that, once again, the loop variable is implicitly declared. This time however, it’s a record conatining each column returned by the cursor. A cursor can be any valid SELECT statement.
I’ve also used an implicit cursor here. Because we’re doing a SELECT COUNT(*), it will always return exactly one row. If an implicit cursor returns either no rows or more than one, you will get an error.
Anyway, save the code as cursor_for.sql, run it and you should get :

In the previous examples you’ll also notice the variable assignment on declaration ( l_bullets PLS_INTEGER := 1;) and concatenation of variables of different types that can then be inserted into a VARCHAR2 column ( l_msg||’-‘||bullets);.

The Curious Case of CASE

At this point, I should mention the CASE statement ( thanks Si). I’m skimming over it for two reasons. First, it’s a comparatively new construct in PL/SQL and so you probably won’t find too many instances of it in applications that have been around any length of time.
The second is that Bryn Llewellyn, Oracle’s PL/SQL Product Manager, has done a pretty good job of explaining matters here.

Exceptions – When it all goes horribly wrong

The EXCEPTIONS section is where you handle any errors. When an error is encountered in a block, control passes to the EXCEPTION handler, if it exists. Once the error is processed, the block terminates – control does not pass back to the main body of the block.
Oracle has tens of thousands of errors it can throw up, so you’ll probably find the most common EXCEPTION handler is the catch-all WHEN-OTHERS exception.

To demonstrate – first create a table :

CREATE TABLE tiny(
little VARCHAR2(10))
/

Now try to insert a record that’s longer than the column will hold. Save the file as exception.sql.

set serveroutput on
BEGIN
INSERT INTO tiny(little) VALUES('Room for a small one ?');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Oops : '||SQLERRM);
END;
/

All we’ve done here is to output the error via the SQLERRM environment variable, which holds the text of the last error.
You’ll notice that, as we’ve handled the exception, Oracle reports that the PL/SQL block was completed successfully.
Often you will be calling PL/SQL programs from other programs ( PL/SQL and otherwise). In order to make sure that the calling program is aware that there’s been a problem further down the chain, you’ll need to re-raise the error with the RAISE command. Save the following as raise.sql and run it :

BEGIN
INSERT INTO tiny(little) VALUES('Room for a small one ?');
EXCEPTION WHEN OTHERS THEN
RAISE;
END;
/

As you can see, the error is passed back to the caller ( in this case the SQL*Plus command line). As it’s not handled by the caller, it falls over in a heap and PL/SQL doesn’t think it’s completed successfully.
As Simon pointed out, you get two errors reported in the stack – ORA-12899 at line 1 and ORA-06512 at line 4.
In common with many languages, it’s usually the first error reported that you need to worry about in PL/SQL , so you usually want to fix that first and re-run the code before looking for any other problems reported by the compiler.
Have a look here for another example of this phenomenon.

A Cautionary Note on Transaction Control

This chain of calls to PL/SQL program units means that you need to be pretty careful where you put your commit and rollback statements. Ideally, transaction control should be done by the topmost program in the chain (In most cases, this will involve the end-user hitting a Save button). Otherwise, you may well get inconsistent data from commits where some of the processing has worked and some has errored.
For the purposes of this article, we’ll assume that the caller is whatever SQL client you’re using ( SQL*Plus or SQLDeveloper etc) and we’ll manually issue the COMMIT and ROLLBACK statements.

All the stuff so far is all very well, but there’s a lot more to PL/SQL than insulting heavily armed Law Enforcement Officers.

Stored Program Units

As I’ve said, PL/SQL is built right into the Oracle RDBMS. Most of the PL/SQL code that is written is stored in the database’s Data Dictionary. These stored program units are Functions, Procedures, Packages and Triggers.

The Wonderful Thing About Triggers…

Triggers are a bit different from other Stored Program Units in that they are (almost always) associated with Tables and the code in them executes when a specified DML action takes place on the relevant table ( i.e. an Insert, Update or Delete).
A trigger can fire either before or after a DML action, either for every row affected by the DML statement, or after the entire statement.
NOTE – in Oracle 11g you can combine row and statement level commands in the same trigger, but we’ll leave that aside for now.

Time to create another table to play with. Dirty Harry’s gone for a lie-down ( like Simon, he’s knocking on a bit), so let’s choose something a bit more contemporary…

Unlike other RDBMSs ( such as MySQL), Oracle does not have the facility to specify an automatically incrementing synthetic key value in a CREATE TABLE statement, so also need a sequence to populate pop_princesses.track_id :

CREATE SEQUENCE pp_track_id_seq
START WITH 1
/

We want to automatically record who created each record in this table and when. We also want to keep track of the user who last modified each record and when.
Bounce forward, the trigger :

In a row level trigger, you can reference existing values in the record being updated by prefixing the table column name with : old and new values by prefixing with :new.
This allows you to make changes to the values being altered. Now let’s put some data into the table and have a look at the effect of the insert portion of the trigger :

As we can see, the created_by and creation_date columns have been populated by the trigger.

Some record exec somewhere has decided that Britney needs to be more grammatically correct in order to get her career back on track. Look, I needed a pretext for demonstrating the update part of the trigger and he owed me a favour – so…

You can use any valid PL/SQL statements in a trigger, including calls to other stored program units.
This also means that they can be used to enforce business rules that couldn’t otherwise be implemented using referential integrity constraints. All of which brings me nicely onto…

Functions

Like other languages, functions in PL/SQL typically follow the pattern of taking in a parameter or parameters, doing something with them, and returning a result.

A couple of items of interest here – we’re using the pre-defined Oracle exception NO_DATA_FOUND in the Exception block. This is what gets raised when an implicit cursor returns no rows. If an implicit cursor were to return more than one row, we’d get a TOO_MANY_ROWS exception.
The other point to note is that the RETURN statement ends processing, irrespective of what comes after.

Save the above as get_artist_fn.sql and run it.

SQL> @get_artist_fn.sql
Function created.

A function can return any PL/SQL datatype. However, if it does return a datatype that SQL recognizes, you can call it directly from within a SQL statement.

Although you don’t specify a length constraint either for a parameter or a return value, you can use the %TYPE declaration for both and the function will still work in exactly the same way. For example, if we specified the return value of this function as :

RETURN pop_princesses.artist_name%TYPE

We can still call this from SQL directly as it resolves to a VARCHAR2.
By contrast, because the following function does not return a SQL datatype, it can only be called from a PL/SQL block :

The RETURN type is defined as BOOLEAN so we can only call it from PL/SQL.
Incidentally, the %FOUND cursor characteristic we’re using here returns TRUE if a row has been fetched from an explicit cursor. The counterpart to it is %NOTFOUND, which returns TRUE if a record has not been found.
In functions, RETURN terminates processing. It’s worth remembering this, if only so you remember to close any explicit cursors before issuing the RETURN statement.
Anyway, save the function and run it. Then we can call it using an anonymous block :

Note the line is_artist_on_album( pa_artist_name => ‘LADY GAGA’)
This illustrates one major difference between calling a function from PL/SQL and from SQL – in PL/SQL, you can pass values by reference rather than position using this syntax.
And run…

To this point, I’ve been working on the basis that all strings inserted into the database are in uppercase. There are some fairly solid reasons for doing this but I’m not going into them now. Suffice to say that, with Simon’s improved version of the function, we can edit the calling script ( call_is_artist_on_album.sql) as follows :

set serveroutput on
BEGIN
IF is_artist_on_album( pa_artist_name => 'Motorhead') THEN
DBMS_OUTPUT.PUT_LINE('How did they manage that ?');
ELSE
DBMS_OUTPUT.PUT_LINE('Nope - off making beer adverts');
END IF;
END;
/

And now we can run it and see….

SQL> @call_is_artist_on_album.sql
How did they manage that ?
PL/SQL procedure successfully completed.
SQL>

It seems that Simon has tired of his role as Igor and is now lurching down the path toward the village.

Procedures

Procedures differ from functions in that they don’t return a value ( although they can pass back values to the caller by means of an OUT parameter).
Where PL/SQL comes into it’s own is applying rules that can’t otherwise be enforced by standard RI constraints.
For example, you know how artists can be a little…temperamental…

CREATE OR REPLACE PROCEDURE add_princess(
pa_artist_name IN VARCHAR2, pa_track_name IN VARCHAR2)
IS
l_last_artist pop_princesses.artist_name%TYPE;
CURSOR c_last_artist IS
SELECT artist_name
FROM pop_princesses
WHERE track_id = (
SELECT MAX( track_id)
FROM pop_princesses);
--
-- user defined exception - that's new
--
e_catfight EXCEPTION;
BEGIN
--
-- Get the last artist on the album at present
--
OPEN c_last_artist;
FETCH c_last_artist INTO l_last_artist;
CLOSE c_last_artist;
--
-- Unless the table is empty, we should have one
--
IF l_last_artist IS NOT NULL THEN
--
-- Now check to see what the new artist name is
--
IF l_last_artist = 'BRITNEY SPEARS'
AND pa_artist_name = 'CHRISTINA AGUILERA' THEN
RAISE e_catfight;
END IF;
END IF;
INSERT INTO pop_princesses( track_id, artist_name, track_name)
VALUES( pp_track_id_seq.NEXTVAL, pa_artist_name, pa_track_name);
EXCEPTION
WHEN e_catfight THEN
--
-- Raise our own error - works the same as an Oracle error
--
RAISE_APPLICATION_ERROR( -20000,
'Cannot put '||l_last_artist||' and '||pa_artist_name
||' together. Fur will fly!');
WHEN OTHERS THEN
RAISE;
END;
/

Apart from keeping Divas apart, this procedure also extends the concept of EXCEPTIONS by introducing a user-defined exception, in this case e_catfight.
We can then raise an application error with an appropriate error number ( between -20000 and -20999). Let’s see it in action :

I had to work in a Debbie Harry reference somewhere as my girlfriend looks a bit like her – only much prettier. Honestly, if I could just remember her birthday, I wouldn’t have to write stuff like this!

Packages

Packages are primarily used to group together related functions and procedures.
In many applications, the vast majority of functions and procedures are contained in packages.
The usual way of creating a package is to create a header ( aka specification) and then a body.
The header contains definitions of the functions and procedures in the package that are publicly available.
It also contains package variables which are visible to all package members, and outside of the package itself.
The body contains the actual code for each package member, together with any private package members – callable only from other members of this package. All this talk of members puts me in mind of a bunch of MP s…possibly in a pole dancing club, so I’ll refer to these Package program units by their other generic name of packaged procedures henceforth ( yes, even the functions).
OK – the package header first :

CREATE OR REPLACE PACKAGE BODY manage_princesses AS
--
-- This function is not declared in the package header and so
-- is PRIVATE - available only to other members of the package body
--
FUNCTION is_artist_on_album( pa_artist_name pop_princesses.artist_name%TYPE)
RETURN BOOLEAN
IS
l_dummy PLS_INTEGER;
CURSOR c_on_album( cp_artist_name pop_princesses.artist_name%TYPE) IS
SELECT 1
FROM pop_princesses
WHERE artist_name = cp_artist_name;
BEGIN
OPEN c_on_album( pa_artist_name);
FETCH c_on_album INTO l_dummy;
IF c_on_album%FOUND THEN
CLOSE c_on_album;
RETURN TRUE;
END IF;
CLOSE c_on_album;
RETURN FALSE;
END is_artist_on_album;
--
-- Now the Public Package members
--
FUNCTION catfight( pa_new_artist IN pop_princesses.artist_name%TYPE,
pa_last_artist IN pop_princesses.artist_name%TYPE)
RETURN BOOLEAN
IS
BEGIN
IF pa_new_artist = 'CHRISTINA AGUILERA'
AND pa_last_artist = 'BRITNEY SPEARS'
THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END catfight;
--
-- Here's our procedure - with some minor adjustments
--
PROCEDURE add_princesses( pa_artist_name IN VARCHAR2,
pa_track_name IN VARCHAR2)
IS
l_last_artist pop_princesses.artist_name%TYPE;
CURSOR c_last_artist IS
SELECT artist_name
FROM pop_princesses
WHERE track_id = (
SELECT MAX( track_id)
FROM pop_princesses);
e_catfight EXCEPTION;
--
-- Oh look, another user-defined exception
--
e_on_album EXCEPTION;
BEGIN
OPEN c_last_artist;
FETCH c_last_artist INTO l_last_artist;
CLOSE c_last_artist;
IF l_last_artist IS NOT NULL THEN
--
-- call the catfight function
--
IF catfight( pa_new_artist => pa_artist_name,
pa_last_artist => l_last_artist) THEN
RAISE e_catfight;
END IF;
END IF;
--
-- Now check to make sure the artist isn't already on the album
--
IF is_artist_on_album( pa_artist_name => pa_artist_name) THEN
RAISE e_on_album;
END IF;
INSERT INTO pop_princesses( track_id, artist_name, track_name)
VALUES( pp_track_id_seq.NEXTVAL, pa_artist_name, pa_track_name);
EXCEPTION
WHEN e_catfight THEN
RAISE_APPLICATION_ERROR( -20000,
'Cannot put '||l_last_artist||' and '||pa_artist_name
||' together. Fur will fly!');
WHEN e_on_album THEN
RAISE_APPLICATION_ERROR( -20001, 'Give someone else a chance.');
WHEN OTHERS THEN
RAISE;
END add_princesses;
--
-- And here's our get_artist function - no change here
--
FUNCTION get_artist( pa_track_id IN pop_princesses.track_id%TYPE)
RETURN pop_princesses.artist_name%TYPE
AS
l_artist pop_princesses.artist_name%TYPE;
BEGIN
SELECT artist_name INTO l_artist
FROM pop_princesses
WHERE track_id = pa_track_id;
RETURN l_artist;
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN 'No track with this ID';
WHEN OTHERS THEN
RAISE;
END get_artist;
END manage_princesses;
/

Note that you can specify the name of the packaged procedure you’re ending in the end statement.
You also need to END the package body when you’re done.
Calling a packaged procedure from outside of the package, you’ll need to prefix the package name to the packaged procedure you’re calling. Save the following as call_package.sql :

Grants ( that’s Permissions to you)

Whether packages, procedures or functions, it’s probable that you’ll want them to be available to users other than the one that owns them. You’ll need to grant EXECUTE privileges on these stored program units.

Igor wants everyone to be able to use the manage_princesses package (if only so that he gets a break from all those artistic temperament), so :

NOTE – it’s not necessarily a great idea to grant permissions to every user on the database. However, a discussion of Oracle security is a bit outside the scope of this tutorial. Suffice to say that replacing PUBLIC in the grant statement with a specific user or a role will have the effect of granting execute privileges to that user or users who have that role.

Odds and Ends

Just to wrap things up…

Things to remember

A few key points to re-emphasise :

You can pretty much use any SQL functions in PL/SQL
Explicit cursors can return multiple rows, implicit cursors should only return one row
Be careful where you put your COMMITs and ROLLBACKs
Exceptions are your friend. They tell you when something’s gone wrong. They tell you what that something is.

Other stuff I’ve not covered here

As I said at the start, this has been more of a quick introduction to PL/SQL than a comprehensive guide. There are other posts here which may be of interest.

PL/SQL can also be pretty handy when used in conjunction with Oracle’s Data Dictionary Views. There are a couple of examples here and here ( this is the definitive “Killer App”).

Further Reading

There’s loads of information available about Oracle in general, and PL/SQL in particular. Here’s just a few places you can find some useful stuff :
The Oracle documentation is freely available online.
There’s lots of good stuff about individual Oracle PL/SQL commands and utilities at the PSOUG site.Steven Feuerstein is generally regarded as the foremost authority on all things PL/SQL. He also says stuff I agree with occasionally, so I guess he knows what he’s talking about.
Last, but by no means least, there is my nemisis – Tom Kyte ( long story, have a look here if you really want to know).
I’m not saying which one of us is the good one, but only one of us has been photographed sporting anything approaching a goatee !

That obscure Star Trek reference would seem to be a suitable point to end. If you have any problems…and you still think I know what I’m talking about after reading this, feel free to get in touch.