What is PL/SQL ?

4 min readJan 22, 2023

PL/SQL is called as “Procedural Language extensions Structured Query Language”. PL/SQL is basically a procedural language such as Fortran, C and Pascal. It provides condition structures such as if else, loops, triggers and so more on and all of PL/SQL codes is stored in database. PL/SQL was created by Oracle to cover needed structures we may need in our SQLs.

Additionally, It can be used in Oracle Database. It means that it can not be run out of the Oracle Database and then can be said that if Oracle DB doesn’t exist in system, you can’t run your PL/SQL codes.

Assume that you have to write a migration script to modify some old records and the some columns of these records should be changed with starting value. For instance, there is a column stored a string value that is called “ref_id”. This column refers to an id and the value of this id should be started with N0001 and each record added to the table should be increased with 1. For example the ref_id value of first record starts with N0001 and then ref_id value of second record must be N0002 and then N0003. We changed our codes as the value of each new record added is assigned by condition. But what about already added old records ? We should prepare a script to update them. In this situation, PL/SQL will be life saver than SQL using. We can do this easily than SQL.

PL/SQL Block Structure

Each PL/SQL Block consists of SQL and PL/SQL statements and these SQL and PL/SQL creates a block. Each PL/SQL block contains four statements which are called ‘DECLARE’, ‘BEGIN’, ‘END’ and ‘EXCEPTION’.

Example 1:

DECLARE
 v_greeting VARCHAR2(100) := ‘Hello World!’; 
BEGIN
 DBMS_OUTPUT.PUT_LINE(v_greeting);
END;

a variable was declared which is called ‘TEXT’ and then it is assigned to ‘Hello World!’ and then the content of ‘TEXT’ variable is being printed by DBMS_OUTPUT.PUT_LINE expression.

In a nutshell, variables is declared with DECLARE block and then program business logic is declared in between BEGIN and END statements. ‘EXCEPTION’ statement is optional like DECLARE (if you don’t have any variable, DECLARED block will be unnecessary but don’t worry it is optional, too). Our codes may throw exception such as divide zero, or not found data etc and in these situation, we can handle exceptions through ‘EXCEPTION’ block.

Example 2:

DECLARE
  n_loop_max_limit NUMBER;
BEGIN
  n_loop_max_limit := 100;
  FOR n_loop_index IN 0..n_loop_max_limit LOOP
  DBMS_OUTPUT.PUT_LINE(n_loop_index);
  END LOOP;
END;

At the beginning of the code, a variable in number type is defined which is called n_loop_max_limit and then a for loop is defined which is counting from 0 to 100 and these numbers are being printed with DBMS_OUTPUT.PUT_LINE expression.

Example 3:

DECLARE
 n_customer_count NUMBER;
BEGIN
 SELECT COUNT(*) INTO n_customer_count FROM CUSTOMER;
 DBMS_OUTPUT.PUT_LINE(‘Customer Count=’ || n_customer_count);
END;

These code blocks provides customer count in ‘CUSTOMER’ table. Firstly, classic select query is written then variable we specified is assigned value with ‘INTO’ keyword.

Example 4:

CREATE OR REPLACE PROCEDURE division(n_dividend IN NUMBER, n_divisor IN NUMBER) 
IS 
  n_result NUMBER;
BEGIN
  n_result := n_dividend / n_divisor;
  DBMS_OUTPUT.PUT_LINE('Result=' || n_result);
  EXCEPTION WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('The division with 0 can not be done!');
END;

These code block creates a procedure which is called ‘division’. In short, It just does division operation if the user tries to divided by 0, it will take error and this error will be handled.

After this procedure was created, it can be called with following expression

EXECUTE division(4,0);

Example 5:

CREATE OR REPLACE FUNCTION addition(n_a IN NUMBER, n_b IN NUMBER) RETURN NUMBER
IS 
 n_result NUMBER;
BEGIN
 n_result := n_a + n_b;
 RETURN n_result;
END;

This function which was created in the top provides addition of two number. The function created can be called like this.

DECLARE 
  n_result NUMBER := 0;
BEGIN
  n_result := addition(3, 4);
  DBMS_OUTPUT.PUT_LINE('Result=' || n_result);
END;

Probably, you wonder what is the different between procedure and function. Functions have always a return value.

Example 6:

DECLARE 
 first_name customer.first_name%TYPE; 
 last_name customer.last_name%TYPE; 
 CURSOR customer_list IS SELECT first_name, last_name FROM customer;
BEGIN 
 OPEN customer_list;
 LOOP
 FETCH customer_list INTO first_name, last_name; 
 EXIT WHEN customer_list%NOTFOUND; 
 DBMS_OUTPUT.PUT_LINE(‘Customer=’ || first_name || ‘, ‘|| last_name); 
 END LOOP; 
 CLOSE customer_list;
END;

Through this code block, customer records can be fetched. These structure is called as ‘CURSOR’. It has two type which are Implicit Cursor and Explicit Cursor.

Example 7:

CREATE OR REPLACE TRIGGER display_address_changes
BEFORE DELETE OR INSERT OR UPDATE ON customer
FOR EACH ROW
WHEN (NEW.CUSTOMER_ID > 0) 
BEGIN
  DBMS_OUTPUT.PUT_LINE('Old address=' || :OLD.address_line1);
  DBMS_OUTPUT.PUT_LINE('New address=' || :NEW.address_line1);
END;

Through this code block, each delete, insert and update operations in customer table will trigger extra codes that is in between ‘BEGIN’ and ‘END’ blocks in addition to own functionality.

 UPDATE customer SET address_line1 = '70222 Munich' WHERE customer_id = 10;

If this code block that is in the top, the response will be like this

Old address=2345 PETERDRIVE
New address=70222 Munich

As you can see, there are lots of useful structures in PL/SQL. We checked some of them in this story. I hope everything is clear for you. If you have any issue, please feel free to write a message. I’ll reply in short time.

I used various resources that they are indicated in the below to prepare this story. You can check out.