If you’ve been doing some work on Oracle databases or just learning more about the RDMS, at some point you’re going to stumble across PL/SQL, or “Procedural Language/SQL“. Most programming curriculum I have seen never touches on anything like PL/SQL so it often comes as a complete surprise – so what exactly is PL/SQL, and how does it differ from SQL?
A Straight Forward Explanation of PL/SQL for Beginners
PL/SQL is basically a programming language that exists on Oracle SQL Servers. It can be viewed as an extension of SQL, offering much greater control over the data in the database and how you interact with it. Other RDMS tend to have their own, similar but very different procedural programming languages like Microsoft SQL Server’s T-SQL and Posgres’ PL/pgSQL.
PL/SQL allows you to declare variables, create functions/procedures/subprograms, use logical conditions (IF…ELSE…), handle for errors, and much more that you’d get out of a typical programming language.
So the key differences between SQL and PL/SQL are the following –
- SQL is generally limited to running one statement at a time, whereas PL/SQL can use variables and complex conditional logic to string more complex conditions together.
- PL/SQL adds programming logic to the database layer that SQL doesn’t have (exception handling, IF/ELSE, loops, etc)
- PL/SQL allows you to save complex logic and query to the database layer so you can easily reuse them in the future.
So far it sounds pretty simple, right? Just a programming language that sits on the database layer. Let’s take a look at a few examples of PL/SQL code so you can get a feel for the syntax.
PL/SQL Code Syntax and Examples for Beginners
The first thing to note about PL/SQL is that the code must be run in a “block”. A block has a few basic components –
- DECLARE – You may start a block with
DECLARE
if you need to set up any variables, constants, etc that you’ll be using in your code. It’s also valid to not have aDECLARE
section at all, so this is option. - BEGIN – this is what indicates to the server that you’re running PL/SQL code and not a standard SQL statement.
BEGIN
statement is required. Everything that follows is the code that gets executed, so this is the beginning of the “execution” section. - EXCEPTION HANDLING – Another optional section, you may handle for exceptions at the end of your block. You can choose to have your error handler work for any and every type of exception, or choose to be more granular and handle different types of exceptions differently. The exception handler starts with
EXCEPTION WHEN ... THEN
with the ellipses being the type of exception you want to handle for. If an exception isn’t handled, then your PL/SQL block will simply exit. - END – Once all the code is written the last item is the
END;
statement.
Here’s a simple example of a PL/SQL valid block that only has BEGIN...END;
components.
BEGIN IF 1 = 2 THEN dbms_output.put_line('ERROR!'); ELSE dbms_output.put_line('CORRECT!'); END IF; END;
(In case you’re not familiar, <pre>dbms_output.put_line</pre> simply prints statements to a screen/debugging window)
Here’s an example with all components –
DECLARE l_employee_id INTEGER; BEGIN SELECT emp_id INTO l_employee_id FROM employees WHERE last_name = 'SMITH'; dbms_output.put_line(l_employee_id); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('Employee not found'); WHEN TOO_MANY_ROWS THEN dbms_output.put_line('More than one row found'); WHEN OTHERS THEN dbms_output.put_line('Unhandled error'); END;
First, in the Declaration section I declare a local variable, l_employee_id
and give it a variable type – INTEGER
.
Next, I begin my Execution section with BEGIN
and run a special query using an INTO
keyword. This lets me select a value from a table into a PL/SQL variable.
In my Exception handler, I’m looking for 3 different possible errors, all of which are indicated by predefined Oracle keywords.
The first error I’m handling for is NO_DATA_FOUND
. This exception is raised when you write a SELECT...INTO...
statement that returns no value. Instead of just returning a NULL
into that variable, an exception would be raised if no employee with the last name Smith is in my table.
The second error I’m looking for is TOO_MANY_ROWS
. A SELECT...INTO...
statement expects a single row/value to be returned when we’re trying to a store a value into a single variable (as in a single integer in this case, not an array or collection of integers). If we had more than one Smith in our table, the query would return more than one value and it would cause this exception to be raised.
The final error is a catch-all for any exception that is raised and doesn’t mean the two above it. OTHERS
will catch literally any other exception.
Functions and Procedures
The above examples were anonymous blocks. An anonymous block is exactly what it sounds like – a block with no name. Anonymous blocks can be great for running one-off scripts here and there, but for code you want to re-use, you’re going to want to create some named blocks in the forms of functions and procedures.
In PL/SQL, a function is a named block that returns a value when called. A procedure on the other hand does not return a value, however, it’s possible to make the parameters passed into a procedure “OUT” or “IN/OUT”, which would update the variable values that were passed into it once the procedure completes.
PL/SQL Function Example
Here’s an example of a PL/SQL named function –
CREATE OR REPLACE FUNCTION get_emp_name(i_emp_id INT) RETURN varchar2 IS l_emp_name VARCHAR2(1000); BEGIN SELECT last_name||', '||first_name INTO l_emp_name FROM employees WHERE emp_id = i_emp_id; RETURN l_emp_name; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'N/A'; END;
The above code will create a function that you can call using get_emp_name(x)
, where x
is an employee’s ID number. This function will be created in the schema from which you run the CREATE
statement unless you specify otherwise. Let’s breakdown this function.
First, CREATE OR REPLACE FUNCTION
tells Oracle we are creating a new function, or if this function already exists then overwrite it with this new code. Then we give our function name and the list of parameters. You don’t need to have any parameters, but most functions will.
In this case, we will be passing in a single integer as a parameter, and we can reference that integer using the name we have given it here – i_emp_id
. Next, we have to specify what data type is going to be returned from this function. In our case, that will be a varchar2 which is a string data type.
Next, we use the IS
keyword, which is just part of the syntax that indicates we are now in the Declaration section if this block. Just like an anonymous block, we aren’t required to declare any variables but in this case I will declare one called l_emp_name
and give it a varchar2 data type.
Then I use BEGIN
to start the execution section. This section is pretty straightforward, but note at the end of it I must use the RETURN
keyword and a value in order for my function to return data.
In the exception handler, I show that it’s also possible to outright return a static value. You don’t need to return a variable, just any data that matches the type you set in the function header.
PL/SQL Procedure Example
Here’s a simple procedure where we won’t pass in any variables –
CREATE OR REPLACE PROCEDURE clear_temp_tables IS BEGIN TRUNCATE TABLE temp_logs; TRUNCATE TABLE temp_debugs; TRUNCATE TABLE temp_data; END;
In this procedure, we follow much of the same syntax as our function, but we don’t specify a RETURN
keyword or data type because procedures don’t return a value.
This procedure has no declared variables and instead just truncates three tables.
PL/SQL Cursors
One other item I need to touch one before wrapping up is one of the most useful aspects of PL/SQL – cursors.
A cursor is basically a collection of rows that return from a query that you can easily loop through. These are heavily used in PL/SQL and the heart of what makes PL/SQL so useful.
Let’s make up an example to see a cursor in action. Let’s say we need to recalculate employee’s salaries at the end of the year based on a performance rating. Here’s how we could easily accomplish that with a cursor in an anonymous block –
DECLARE CURSOR cur_employees IS SELECT * FROM employees; BEGIN FOR rec IN cur_employees LOOP IF rec.performance_rating >= 90 THEN rec.salary = rec.salary + (.1 * rec.salary) --10% raise ELSIF rec.performance_rating >= 80 THEN rec.salary = rec.salary + (.05 * rec.salary) --5% raise ELSIF rec.performance_rating >= 60 THEN rec.salary = rec.salary + (.02 * rec.salary) --2% raise ELSE rec.salary = rec.salary + (.01 * rec.salary) --1% raise END IF; --our rec variables do not update the table unless we explicitly do so --emp_id is our unique primary key UPDATE employees SET salary = rec.salary WHERE emp_id = rec.emp_id; COMMIT; END LOOP; END;
In the above code, I declare my cursor using CURSOR cur_employees
. The cursor is named so I can reference it later. Following the name, I’m telling Oracle what rows should be in my cursor – in this case, every row of the Employees table.
Then I start my execution section and I immediately want to start looping through my cursor records. By typing FOR rec IN cur_employees LOOP...
, I’m saying “Grab all the records from this cursor, loop through them, and to reference the current record within the loop let’s use the variable rec
. This is declaring the record variable on the fly, you can call it anything you want but I typically use rec
.
Within my loop, I’m checking each employees performance rating using my rec
variable which represents the current row of data in my cursor. I do the same thing for each employees’ salary, and I can even update the rec.salary
variable as you see in each IF
block.
However it’s important to note that by changing the value of rec.salary
, it does NOT update the table outright. This is essentially just a local variable, so to update the actual table I need to write an UPDATE statement once my logic is finished.
For that reason, I would advise you declare a new local variable in your anonymous block as it will make the code easier to read and you’ll still be able to reference the records original values if needed. Here’s what I would say is a preferable approach.
DECLARE CURSOR cur_employees IS SELECT * FROM employees; l_new_salary NUMBER; BEGIN FOR rec IN cur_employees LOOP IF rec.performance_rating >= 90 THEN l_new_salary = rec.salary + (.1 * rec.salary) --10% raise ELSIF rec.performance_rating >= 80 THEN l_new_salary = rec.salary + (.05 * rec.salary) --5% raise ELSIF rec.performance_rating >= 60 THEN l_new_salary = rec.salary + (.02 * rec.salary) --2% raise ELSE l_new_salary = rec.salary + (.01 * rec.salary) --1% raise END IF; --our rec variables do not update the table unless we explicitly do so --emp_id is our unique primary key UPDATE employees SET salary = l_new_salary WHERE emp_id = rec.emp_id; COMMIT; END LOOP; END;
More PL/SQL Questions?
Have any other questions or comments about PL/SQL? Anything you’d like to learn about it? Feel free to leave a comment below!
Leave a Reply