What’s the difference between PL/SQL and SQL?

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 –

1. DECLARE – You may start a block with DECLAREif you need to set up any variables, constants, etc that you’ll be using in your code. It’s also valid to not have a DECLARE section at all, so this is option.
2. 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.
3. 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.
4. 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!