PL/SQL Tutorial With Simple Examples

PL/SQL is Oracle’s procedural extension for the SQL language and the Oracle relational database. PL/SQL stands for “Procedural Language extensions to the Structured Query Language”.

What is the need for PL/SQL? Well, if you look at SQL language, it is just a query language. SQL can’t do the things that a programming language like Python or Java can do. To add more functionalities on top of SQL, Oracle has developed a language called PL/SQL which extends SQL by adding constructs found in procedural languages like functions, loops, variables, objects, etc. Thus, PL/SQL became a structural language that is more powerful than SQL.

In this post, let’s understand the fundamentals of PL/SQL and how it works. I’ll share some example codes so that you can understand the concepts practically. Let’s dive right in.

Oracle Live SQL

You don’t need to install anything to follow along with this tutorial. You can use the online editor provided by Oracle, which is Oracle Live SQL. If you don’t have an Oracle account already, sign up for a new account. You can open an SQL worksheet and start coding.

Basic Syntax of PL/SQL

The basic syntax of PL/SQL programs looks like this:

DECLARE 
   <declaration section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

The program starts with a DECLARE section which is optional. In this section, you can declare your variables, constants, records as cursors, etc., which store data temporarily.

After the declaration section, the execution section starts with the BEGIN keyword. You can write the program logic in this section. This section will end with the END keyword. This is a mandatory section and this is where all the action happens.

You can also handle exceptions using the EXCEPTION section. This is an optional section.

Hello World Program in PL/SQL

Let’s see how we can write your first program in PL/SQL. If you want to print something in PL/SQL, you can use dbms_output.put_line() and pass the data that you want to print inside the parenthesis.

Here is a PL/SQL program that will print “Hello World” as the output:

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

You can write the code in your SQL Worksheet, select the code that you want to execute, and click on the “Run” button. You will be able to see the output in the window given below your code.

Output:

Variables in PL/SQL

In PL/SQL, you must declare your variables prior to their use. You can declare and initialize your variables in the declaration section and assign new values to variables in the executable section.

This is how you declare a variable in PL/SQL:

variable_name datatype;

If you want to initialize the variable, you can do it by assigning a value along with the declaration.

variable_name datatype := value;

Note that we use := as the assignment operator in PL/SQL.

You also need to mention the data type of the variable (number, integer, decimal, float, real, char, varchar2, boolean, etc.).

Let’s see an example PL/SQL program to understand how variables are created and used. The following PL/SQL program will find the sum of two numbers.

DECLARE 
   a integer := 10; 
   b integer := 20; 
   sumOfNumbers integer; 
BEGIN 
   sumOfNumbers := a + b; 
   dbms_output.put_line(sumOfNumbers);
END; 

Output:

String Concatenation in PL/SQL

If you want to add a text while displaying the output, you can do that with the help of the || operator. This operator is used for string concatenation in PL/SQL.

Take a look at the following example:

DECLARE 
   a integer := 10; 
   b integer := 20; 
   sumOfNumbers integer; 
BEGIN 
   sumOfNumbers := a + b; 
   dbms_output.put_line('Value of c: ' || sumOfNumbers); 
END; 

Output:

Comments in PL/SQL

Comments are used to make the code easier for humans to understand. The system will ignore comments while executing the code. You can write anything as a comment inside your code.

In PL/SQL, we use — for writing single line comments and /* */ for writing multi-line comments.

-- this is a single line comment

/*  this is a 
    multi-line
    comment   */

Using data from a table in PL/SQL

Let us see how we can connect to a table and use the table data in PL/SQL. To do that, let’s create a table first.

CREATE TABLE EMPLOYEES( 
   ID   INT NOT NULL, 
   NAME VARCHAR(10) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS VARCHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
); 

Output:

Now, let’s insert some values into this table.

INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Anupam', 32, 'Mumbai', 20000.00 );  

INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Aman', 25, 'Delhi', 15000.00 );  

INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'Piyush', 23, 'Bangalore', 30000.00 );

Output:

Now, the table looks like this:

Let’s see how we can bring this data into our PL/SQL code.

You can use %type to declare a variable with the same type as that of a specified table’s column.

Let’s see an example:

DECLARE 
   e_id employees.id%type := 1; 
   e_name  employees.name%type; 
   e_addr employees.address%type; 
   e_sal  employees.salary%type; 
BEGIN 
   SELECT name, address, salary INTO e_name, e_addr, e_sal 
   FROM employees 
   WHERE id = e_id;  
   dbms_output.put_line 
   ('Employee ' ||e_name || ' from ' || e_addr || ' earns ' || e_sal); 
END; 

Output:

As you can see in the above code, you can select the data from the table and put it into the PL/SQL variables.

IF-ELSIF-ELSE Statements in PL/SQL

You can use the IF and ELSE statements in PL/SQL to tell the computer that if the condition is true, do this. Otherwise, if the condition is false, do another thing. You can check for multiple conditions with the help of the ELSIF statement.

Let’s look at an example of the use of these conditional statements in PL/SQL.

DECLARE 
   a number(2) := 21; 
   b number(2) := 10; 
BEGIN 
   IF (a = b) then 
      dbms_output.put_line('a is equal to b'); 
   ELSIF (a < b) then 
      dbms_output.put_line('a is less than b'); 
   ELSE 
      dbms_output.put_line('a is greater than b'); 
   END IF; 
END; 

Output:

You can add multiple ELSIF statements if you want to check multiple conditions. The IF block should end with the END IF; statement.

Loops in PL/SQL

In programming, we use loops to execute a sequence of instructions that is continually repeated until a certain condition is reached. In PL/SQL, we have different types of loops, such as the basic PL/SQL loop, while loop, and for loop.

Let’s say we want to print numbers from 1 to 5. Instead of writing dbms_output.put_line() 5 times, we can run a loop to do it easily. Let’s see how we can implement it using the different loops in PL/SQL.

Basic Loop in PL/SQL

declare
    i number;
begin
    i:=1;
    loop
        dbms_output.put_line(i);
        i:=i+1;
        exit when i>5;
    end loop;
end;

Output:

The loop will start with the LOOP statement and end with the END LOOP; statement. In this example, every time the loop runs, we increment the value of the variable i by 1.

You can add an exit condition before the END LOOP statement. If this exit condition becomes true, then the loop will stop the execution.

While Loop in PL/SQL

DECLARE 
   i number; 
BEGIN
    i:=1;
    WHILE i <= 5 LOOP 
      dbms_output.put_line(i);
      i:=i+1; 
   END LOOP; 
END; 

Output:

The while loop starts with the keyword WHILE, a condition, and the keyword LOOP. The loop will end with the END LOOP; statement. As long as the condition is true, the while loop will execute.

For Loop in PL/SQL

DECLARE 
   i number;  
BEGIN 
   FOR i in 1 .. 5 LOOP 
      dbms_output.put_line(i); 
  END LOOP; 
END; 

Output:

The for loop starts with the FOR keyword, the variable name, the IN keyword, the range of numbers, and the LOOP keyword. In for loop, you don’t need to increment the variable manually. It will get automatically incremented after each iteration. The loop will end with the END LOOP; statement.

Subprograms in PL/SQL

A subprogram in PL/SQL (also called PL/SQL block) is a program unit/module that performs a particular task when it is invoked.

PL/SQL provides two kinds of subprograms:

Functions	Functions return a single value. These are mainly used to compute and return a value.
Procedures	Procedures do not return a value directly. These are mainly used to perform an action.

Procedures in PL/SQL

Let’s see an example of a procedure in PL/SQL.

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 

Output:

We have created a simple procedure that will print “Hello World” when it is called.

Now let’s see how to call a procedure. You can call the procedure using the EXECUTE command.

EXECUTE greetings; 

Output:

You can also call it in the following way.

BEGIN 
   greetings; 
END; 

Output:

IN and OUT Modes

Procedures can accept values using the IN mode and output values using the OUT mode.

Let’s look at an example PL/SQL program that finds the maximum of two numbers using a procedure.

CREATE OR REPLACE PROCEDURE findMax(x IN number, y IN number, z OUT number) IS
BEGIN
    IF x>y THEN
        z:=x;
    ELSE
        z:=y;
    END IF;
END;

We have created a procedure that accepts two numbers and outputs the maximum of those two numbers. Now, let’s call the procedure by passing the parameters.

DECLARE
    a number;
    b number;
    c number;
BEGIN
    a := 10;
    b := 5;
    findMax(a,b,c);
    dbms_output.put_line(c);
END;

Functions in PL/SQL

A function is the same as a procedure except that it returns a value.

Let’s create an example function that returns the number of records from the employees table that we have created earlier.

CREATE OR REPLACE FUNCTION totalEmployees RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total FROM employees; 
   RETURN total; 
END; 

We have created a function that returns the count of rows from the employees table. When you define the function, make sure you provide the return type in the function declaration (here, the return type is number).

Let’s call this function.

DECLARE 
   total number(2); 
BEGIN 
   total := totalEmployees(); 
   dbms_output.put_line('Total no. of Employees: ' || total); 
END; 

Cursors in PL/SQL

When we use variables, we generally can’t store multiple rows or records returned by an SQL query. That is why we have cursors in PL/SQL.

A cursor holds multiple rows returned by an SQL statement.

Generally, there are two types of cursors: Implicit and Explicit.

Implicit Cursors

Implicit cursors are automatically generated by Oracle when an SQL query is executed. You can refer to the most recent implicit cursor as the SQL cursor, which always has some attributes. Given below are some of the attributes of SQL cursor and the use cases.

%FOUND	Returns TRUE if a SELECT INTO statement returned one or more rows or an INSERT, UPDATE, or DELETE statement affected one or more rows. Otherwise, it returns FALSE.
%NOTFOUND	Returns TRUE if a SELECT INTO statement returned no rows or an INSERT, UPDATE, or DELETE statement affected no rows. Otherwise, it returns FALSE.
%ROWCOUNT	Returns the number of rows returned by a SELECT statement or affected by an INSERT, UPDATE, or DELETE statement.

Given below is an example of using implicit cursors in PL/SQL.

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE employees SET salary = salary + 1000; 
   IF sql%notfound THEN 
      dbms_output.put_line('No employees selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' employees selected '); 
   END IF;  
END; 

Output:

Explicit Cursors

Explicit cursors are created by the users or programmers. You should declare the explicit cursor in the declaration section of the PL/SQL program.

In general, you do 4 things with explicit cursors: Declare, Open, Fetch, and Close.

Given below is an example of creating an explicit cursor in PL/SQL.

DECLARE
    e_id employees.id%type;
    e_name employees.name%type;
    CURSOR C1 IS select id,name from employees;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 into e_id, e_name;
        EXIT WHEN C1%notfound; 
        dbms_output.put_line(e_id ||' '|| e_name); 
    END LOOP;
    CLOSE C1;
END;

Output:

You can see that the cursor C1 stores multiple records returned by the SQL query and we print them using a loop.

Triggers in PL/SQL

In PL/SQL, triggers are stored programs that are automatically fired/executed when some event occurs. This event can be a DML, DDL, or any DB operation.

Given below is an example of a trigger that is based on the UPDATE operation. If any records in the employees table are updated, this trigger will automatically get fired.

CREATE OR REPLACE TRIGGER displayChanges BEFORE UPDATE ON employees
FOR EACH ROW WHEN (NEW.ID > 0)
BEGIN
    dbms_output.put_line('Old Salary: ' || :OLD.salary);
    dbms_output.put_line('New Salary: ' || :NEW.salary);
END;

Now, let’s update the salary of one employee on the employees table.

UPDATE employees set salary=80000 where name='Anupam';

You can see that the trigger was fired when the UPDATE operation was done. The old and new salaries are displayed in the output.