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.
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).
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 :
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.
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) :
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 :
cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts sudo gedit sqlplus.sh
Then add the definition of the SQLPATH variable. The whole script should look something like this :
#!/bin/bash # # svaggu 09/28/05 - Creation # ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server export ORACLE_HOME ORACLE_SID=XE export ORACLE_SID NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh` export NLS_LANG PATH=$ORACLE_HOME/bin:$PATH export PATH # Added line to set working directory of the SQL command line SQLPATH=/home/mikes/pls_progs export SQLPATH sqlplus /nolog
After all that, it’s finally time to meet…
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….
set serveroutput on BEGIN DBMS_OUTPUT.PUT_LINE('Do ya feel lucky, punk ?'); END; /
Save the file as lucky.sql in the directory you’ve just created – then go to the SQL prompt and run ( or with the file open in SQLDeveloper, hit F5) :
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 17 21:49:11 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> @lucky.sql Do ya feel lucky, punk ? PL/SQL procedure successfully completed. SQL>
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 :
GRANT connect, resource TO igor identified by igor / GRANT SELECT on hr.departments to igor / GRANT SELECT on hr.employees TO igor /
Now connect as the new user in the same way as for the HR user. The connect string in this example is igor/igor@xe
CREATE TABLE silly_messages ( message VARCHAR2(75)) /
Now the script to insert the row…
DECLARE l_msg VARCHAR2(75); BEGIN l_msg := 'Well, do ya ?'; INSERT INTO silly_messages( message) VALUES( l_msg); END; /
Save the file as ins_msg.sql and off we go….
SQL> @ins_msg.sql PL/SQL procedure successfully completed. SQL> select * from silly_messages; MESSAGE -------------------------------------------------------------------------------- Well, do ya ? SQL> commit; Commit complete.
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 :
– 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.
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;
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 :
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 :
set serveroutput on DECLARE l_msg silly_messages.message%TYPE; BEGIN FOR bullet in 1..6 LOOP IF bullet < 5 THEN l_msg := 'BANG!'; ELSIF bullet = 5 THEN l_msg := 'Whatever you say, officer.'; ELSIF bullet = 6 THEN l_msg := q'[That's a big gun. Are you compensating for something ?]'; END IF; INSERT INTO silly_messages( message) VALUES( l_msg); END LOOP; END; /
Save this one as bullets.sql and …
SQL> @bullets.sql PL/SQL procedure successfully completed. SQL> select * from silly_messages; MESSAGE -------------------------------------------------------------------------------- Well, do ya ? BANG! BANG! BANG! BANG! Whatever you say, officer. That's a big gun. Are you compensating for something ? 7 rows selected. SQL> commit;
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.
set serveroutput on DECLARE l_dname hr.departments.department_name%TYPE; l_deptno hr_departments.department_id%TYPE; CURSOR c_depts IS SELECT department_name, department_id FROM hr.departments ORDER BY department_id; BEGIN OPEN c_depts; FETCH c_depts INTO l_dname, l_deptno; CLOSE c_depts; DBMS_OUTPUT.PUT_LINE( 'Department Name - '||l_dname||' id - '||l_deptno); END; /
Save as exp_cursor.sql and run the script…
SQL> @exp_cursor.sql Department Name - Administration id - 10 PL/SQL procedure successfully completed. SQL>
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 :
SQL> @cursor_for.sql Den Raphaely 07-DEC-94 Alexander Khoo 18-MAY-95 Shelli Baida 24-DEC-97 Sigal Tobias 24-JUL-97 Guy Himuro 15-NOV-98 Karen Colmenares 10-AUG-99 PL/SQL procedure successfully completed. SQL>
Other Looping Constructs
Whilst we’re on the subject of loops, we may as well cover the WHILE loop and Simple LOOP as well.
DECLARE l_msg silly_messages.message%TYPE; l_bullets PLS_INTEGER :=1; BEGIN WHILE l_bullets <= 6 LOOP l_msg := 'BANG'; INSERT INTO silly_messages( message) VALUES( l_msg||'-'||l_bullets); l_bullets := l_bullets +1; END LOOP; l_msg := 'Click'; INSERT INTO silly_messages( message) VALUES(l_msg); END; /
Yep, that’s a while loop. Now for a simple loop :
DECLARE l_count PLS_INTEGER; l_msg silly_messages.message%TYPE; BEGIN l_count := 1; LOOP IF MOD( l_count, 2) = 0 THEN l_msg := l_count||' is even'; ELSE l_msg := l_count||' is odd'; END IF; INSERT INTO silly_messages( message) VALUES( l_msg); l_count := l_count + 1; EXIT WHEN l_count > 10; END LOOP; END; /
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; /
Running this, we get :
SQL> @exception.sql Oops : ORA-12899: value too large for column "HR"."TINY"."LITTLE" (actual: 22, maximum: 10) PL/SQL procedure successfully completed. SQL>
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; /
SQL> @raise.sql BEGIN * ERROR at line 1: ORA-12899: value too large for column "HR"."TINY"."LITTLE" (actual: 22, maximum: 10) ORA-06512: at line 4 SQL>
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…
Apologies for the Winnie the Pooh reference – Simon insisted ( honest).
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…
CREATE TABLE pop_princesses( track_id NUMBER PRIMARY KEY, artist_name VARCHAR2(50) NOT NULL, track_name VARCHAR2(50) NOT NULL, created_by VARCHAR2(30) NOT NULL, creation_date DATE NOT NULL, modified_by VARCHAR2(30), modified_date DATE) /
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 :
CREATE OR REPLACE TRIGGER pp_biu BEFORE INSERT OR UPDATE ON pop_princesses FOR EACH ROW BEGIN IF INSERTING THEN :new.created_by := USER; :new.creation_date := SYSDATE; ELSIF UPDATING THEN :new.modified_by := USER; :new.modified_date := SYSDATE; END IF; END; /
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 :
INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'BEYONCE','CRAZY IN LOVE') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'GIRLS ALOUD','SOUND OF THE UNDERGROUND') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'AGNES','RELEASE ME') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'EVANESCENCE','BRING ME TO LIFE') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'SOPHIE ELLIS-BEXTOR','TAKE ME HOME') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'LILLY ALLEN','THE FEAR') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'AMY WINEHOUSE','REHAB') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'MOTORHEAD AND GIRLSCHOOL', q'[PLEASE DON'T TOUCH]') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'LADY GAGA', 'JUST DANCE') / INSERT INTO pop_princesses( track_id, artist_name, track_name) VALUES( pp_track_id_seq.NEXTVAL, 'BRITNEY SPEARS', q'[I LOVE ROCK 'N' ROLL]') /
SQL> select track_id, artist_name, created_by, creation_date 2 from pop_princesses; TRACK_ID ARTIST_NAME CREATED_BY CREATION_DATE ---------- ------------------------------ ---------- ------------------ 1 BEYONCE IGOR 27-OCT-10 2 GIRLS ALOUD IGOR 27-OCT-10 3 AGNES IGOR 27-OCT-10 4 EVANESCENCE IGOR 27-OCT-10 5 SOPHIE ELLIS-BEXTOR IGOR 27-OCT-10 6 LILLY ALLEN IGOR 27-OCT-10 7 AMY WINEHOUSE IGOR 27-OCT-10 8 MOTORHEAD AND GIRLSCHOOL IGOR 27-OCT-10 9 LADY GAGA IGOR 27-OCT-10 10 BRITNEY SPEARS IGOR 27-OCT-10 10 rows selected. SQL>
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…
SQL> UPDATE pop_princesses 2 SET track_name = 'I LOVE ROCK AND ROLL' 3 WHERE track_id = 10 4 / 1 row updated. SQL> SELECT track_name, modified_by, modified_date 2 FROM pop_princesses 3 WHERE track_id = 10 4 / TRACK_NAME MODIFIED_BY MODIFIED_DATE -------------------- ------------ ------------------ I LOVE ROCK AND ROLL IGOR 27-OCT-10 SQL> commit;
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…
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.
CREATE OR REPLACE FUNCTION get_artist( pa_track_id IN pop_princesses.track_id%TYPE) RETURN VARCHAR2 IS 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; /
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.
SQL> select get_artist(1) from dual; GET_ARTIST(1) --------------------- BEYONCE
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 :
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 :
CREATE OR REPLACE 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; /
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 :
SQL> @is_artist_on_album_fn.sql Function created.
Now create the caller ( save as call_is_artist_on_album.sql)
set serveroutput on BEGIN IF is_artist_on_album( pa_artist_name => 'LADY GAGA') THEN DBMS_OUTPUT.PUT_LINE('That Girl gets everywhere'); ELSE DBMS_OUTPUT.PUT_LINE('Vegetarians only then'); END IF; END; /
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.
SQL> @call_is_artist_on_album.sql That Girl gets everywhere PL/SQL procedure successfully completed. SQL>
It was at this point that Simon proved that a Winnie The Pooh fixation is no bar to programming aptitude and came up with a far more elegant version of this function :
CREATE OR REPLACE FUNCTION is_artist_on_album( pa_artist_name pop_princesses.artist_name%TYPE) RETURN BOOLEAN IS l_dummy PLS_INTEGER; l_found BOOLEAN := FALSE; CURSOR c_on_album( cp_artist_name pop_princesses.artist_name%TYPE) IS SELECT 1 FROM pop_princesses WHERE UPPER(artist_name) LIKE cp_artist_name; BEGIN OPEN c_on_album('%'||UPPER(pa_artist_name)||'%'); FETCH c_on_album INTO l_dummy; l_found := c_on_album%FOUND; CLOSE c_on_album; RETURN l_found; END; /
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 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 :
BEGIN add_princess( pa_artist_name => 'CHRISTINA AGUILERA', pa_track_name => 'BEAUTIFUL'); END; /
With the script saved as call_add_princess.sql :
SQL> @call_add_princess.sql BEGIN * ERROR at line 1: ORA-20000: Cannot put BRITNEY SPEARS and CHRISTINA AGUILERA together. Fur will fly! ORA-06512: at "IGOR.ADD_PRINCESS", line 41 ORA-06512: at line 2 SQL>
If we try that again, but with a different artist :
BEGIN add_princess( pa_artist_name => 'BLONDIE', pa_track_name => 'CALL ME'); END; /
Save the changes to call_add_princesses.sql and :
SQL> @call_add_princess.sql PL/SQL procedure successfully completed. SQL> SELECT track_id, artist_name, track_name 2 FROM pop_princesses 3 WHERE track_id = ( 4 SELECT MAX( track_id) 5 FROM pop_princesses); TRACK_ID ARTIST_NAME TRACK_NAME ---------- ------------------------------ -------------------- 11 BLONDIE CALL ME SQL> COMMIT;
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 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 manage_princesses AS FUNCTION catfight( pa_new_artist IN pop_princesses.artist_name%TYPE, pa_last_artist IN pop_princesses.artist_name%TYPE) RETURN BOOLEAN; PROCEDURE add_princesses( pa_artist_name IN VARCHAR2, pa_track_name IN VARCHAR2); FUNCTION get_artist( pa_track_id IN pop_princesses.track_id%TYPE) RETURN pop_princesses.artist_name%TYPE; -- -- Global variable -- g_description CONSTANT VARCHAR2(100) := 'Functions and procedures to manage the pop_princesses table'; END manage_princesses; /
Now for the body :
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 :
set serveroutput on DECLARE l_artist pop_princesses.artist_name%TYPE; l_fight BOOLEAN; l_prog_loc VARCHAR2(30); BEGIN l_prog_loc := 'Start'; -- Get the value of the package variable g_description DBMS_OUTPUT.PUT_LINE( manage_princesses.g_description); -- -- find out the artist for track 6 -- l_prog_loc := 'get_artist'; l_artist := manage_princesses.get_artist( pa_track_id => 6); DBMS_OUTPUT.PUT_LINE('Track 6 is by '||l_artist); -- -- Will Lady Gaga and Sophie Ellis Bextor get on ? -- l_prog_loc := 'catfight'; l_fight := manage_princesses.catfight( pa_new_artist => 'LADY GAGA', pa_last_artist => 'SOPHIE ELLIS BEXTOR'); IF l_fight = TRUE THEN DBMS_OUTPUT.PUT_LINE('Dressing room not big enough for both of them'); ELSE DBMS_OUTPUT.PUT_LINE('Purring like kittens.'); END IF; -- -- Now let's add another track -- l_prog_loc := 'add_princesses'; manage_princesses.add_princesses( pa_artist_name => 'THE VERONICAS', pa_track_name => 'UNTOUCHED'); EXCEPTION WHEN OTHERS THEN -- Output a message indicating the location of the error and what it -- was. This is the calling program so we won't re-raise the error -- from here. -- DBMS_OUTPUT.PUT_LINE('Error at '||l_prog_loc||' : '||SQLERRM); END; /
Now run it, check that the insert as worked and then commit. Your output should be something like :
SQL> @call_package.sql Functions and procedures to manage the pop_princesses table Track 6 is by LILLY ALLEN Purring like kittens. PL/SQL procedure successfully completed. SQL> SELECT track_id, artist_name, track_name 2 FROM pop_princesses 3 WHERE track_id = ( 4 SELECT MAX( track_id) 5 FROM pop_princesses); ARTIST_NAME -------------------------------------------------------------------------------- THE VERONICAS SQL> commit; Commit complete. 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 :
SQL> GRANT EXECUTE ON manage_princesses TO public 2 / Grant succeeded. SQL>
If we now open another session as the HR user, we should be able to see the package :
SQL> conn hr/hr@xe Connected. SQL> set serveroutput on SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE(igor.manage_princesses.g_description); 3 END; 4 / Functions and procedures to manage the pop_princesses table PL/SQL procedure successfully completed. SQL>
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.
- File Handling
- Arrays and Bulk Binding
- Ref Cursors
- Overloading package procedures
- Compound Triggers (from Oracle 11g)
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.