top of page
Search

What is PL/SQL? Explained

The full form of PL/SQL is "Procedural Language extensions to SQL".


PL/SQL is a programming language designed specifically for the Oracle Database. It extends the capabilities of SQL by providing procedural constructs such as loops, conditional statements, and exception handling, allowing developers to create robust and complex database applications. In this article, we will delve into the fundamentals of PL/SQL, exploring its architecture, Data types, Control Structure, Program units, benefits, and common use cases. Also, we will provide examples that will help you to understand better and the difference between SQL and PL/SQL. This comprehensive guide will provide you with a solid foundation of knowledge. Let's begin by understanding what PL/SQL is and how it works.

Table of content:

  1. What is PL/SQL?

  2. PL/SQL Architecture

  3. Data Types

  4. Control Structures:

  5. Program units

What is PL/SQL?

PL/SQL is an extension of the SQL language that combines the data manipulation capabilities of SQL with the processing capabilities of a procedural language. It allows for the creation of powerful SQL queries. PL/SQL enhances the security, portability, and robustness of the Database, ensuring the smooth processing of SQL statements.


In PL/SQL, programmers can specify "what to do" through SQL and "how to do" through procedural instructions. Similar to other database languages, PL/SQL provides programmers with more control by incorporating features such as loops, conditions, and object-oriented concepts.


PL/SQL Architecture

The Below PL/SQL Example is a pictorial representation of PL/SQL Architecture.

PL/SQL Architecture

The PL/SQL architecture mainly consists of the following three components:

  1. PL/SQL Block

  2. PL/SQL Engine

  3. Database Server


PL/SQL block:

A PL/SQL block is a unit of code in the PL/SQL programming language that consists of PL/SQL statements and SQL commands. It can be either a named or anonymous block and can be used to implement various program units such as procedures, functions, packages, triggers, or types. A PL/SQL block is divided into three sections: declaration, execution, and exception handling.

  1. Declaration Section: The declaration section is optional and is used to define the variables, constants, cursors, types, and other program elements that will be used within the block. Declarations are enclosed between the keywords DECLARE and BEGIN. In this section, you can declare local variables, define data types, and initialize values.

  2. Execution Section: The execution section contains the executable statements that perform specific actions or operations. It is enclosed between the keywords BEGIN and END. Here, you can write PL/SQL statements to manipulate data, perform calculations, control program flow, interact with the database, and call other program units or SQL commands.

  3. Exception Handling Section: The exception handling section is used to handle and manage errors or exceptional conditions that may occur during the execution of the block. It provides a mechanism to catch and handle exceptions gracefully, allowing you to take appropriate actions, such as logging errors, rolling back transactions, or providing meaningful error messages. The exception handling section is optional and is defined using the keywords EXCEPTION, WHEN, and others like RAISE and RAISE_APPLICATION_ERROR.


PL/SQL Engine

The PL/SQL engine is the component responsible for processing the PL/SQL statements within a block and coordinating the interaction with the database server. It can be installed either within the Oracle database server or in application tools such as Oracle Forms or Oracle Reports. The PL/SQL engine separates the PL/SQL and SQL parts of a block, processing the PL/SQL statements internally and sending SQL commands to the database server for execution. It also manages the flow of control within the block, handles variable scoping and data manipulation, and performs various optimizations to improve performance.


Database Server:

The database server is the component that stores the data and executes the SQL commands received from the PL/SQL engine. It is responsible for managing the physical storage of data, enforcing data integrity rules, and processing SQL statements to perform operations such as retrieving, inserting, updating, and deleting data. The database server also contains the data dictionary, which is a central repository that stores metadata about the database objects, including PL/SQL units. The data dictionary provides information about the structure, organization, and access rights of the database objects, allowing the PL/SQL engine to validate and execute the code effectively.


PL/SQL Data Types:

PL/SQL can handle different types of data, such as numbers, strings, dates, records, and collections. Here's a simplified explanation of each type along with examples of how to declare and use them:

1. Numbers:

These are numeric values used for arithmetic operations. PL/SQL supports various numeric data types, including INTEGER, FLOAT, DECIMAL, and NUMBER. You can specify precision and scale for controlling range and accuracy.

DECLARE   
    x INTEGER := 10;   
    y FLOAT := 3.14;   
    z NUMBER := 1234.56; 
BEGIN   
    DBMS_OUTPUT.PUT_LINE(x + y + z); 
END; 
/

2. Strings:

These are alphanumeric values representing characters or character strings. PL/SQL supports string data types like VARCHAR2, CHAR, and CLOB. You can specify the maximum size of a string.

DECLARE   
    name VARCHAR2(20) := 'John Smith';   
    gender CHAR(1) := 'M';   
    bio CLOB; 
BEGIN   
    bio := 'This is a biography of ' || name || '. He is a ' || gender || '.';   
    DBMS_OUTPUT.PUT_LINE(bio); 
END; 
/

3. Dates:

These represent dates and times. PL/SQL supports data types such as DATE, TIMESTAMP, and INTERVAL. Functions and operators can manipulate date and time values.

DECLARE   
    today DATE := SYSDATE;   
    birthday DATE := TO_DATE('1990-01-01', 'YYYY-MM-DD');   
    age NUMBER; 
BEGIN   
    age := FLOOR(MONTHS_BETWEEN(today, birthday) / 12);   
    DBMS_OUTPUT.PUT_LINE('Age: ' || age); 
END; 
/

4. Records:

These are composite data types that store multiple values of different types. You can create a record type using the TYPE keyword or use existing record types like %ROWTYPE or %TYPE.

DECLARE   
    TYPE student_t IS RECORD (     
        id NUMBER,     
        name VARCHAR2(20),     
        grade CHAR(1)   
    );   
    student student_t; 
BEGIN   
    student.id := 1;   
    student.name := 'Alice';   
    student.grade := 'A';      
    
    DBMS_OUTPUT.PUT_LINE('Student ID: ' || student.id);   
    DBMS_OUTPUT.PUT_LINE('Student Name: ' || student.name);   
    DBMS_OUTPUT.PUT_LINE('Student Grade: ' || student.grade); 
END; 
/

5. Collections:

These are composite data types that store multiple values of the same type. PL/SQL supports associative arrays, nested tables, and varrays. Various methods and operators can be used for manipulating collection elements.

DECLARE   
    TYPE numbers_t IS TABLE OF NUMBER;   
    numbers numbers_t := numbers_t(1, 2, 3); 
BEGIN   
    numbers.EXTEND;   
    numbers(4) := 4;      
    
    FOR i IN numbers.FIRST .. numbers.LAST LOOP     
        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || numbers(i));      
    END LOOP; 
END; 
/

Note: In the above examples, the `DBMS_OUTPUT.PUT_LINE` function is used to display the output. Remember to enable and configure the output display to see the results in some environments.


PL/SQL Control Structure:

PL/SQL provides control structures to manage the flow of execution in programs. These include conditional statements, loops, and exception handling. Here's a simplified explanation of each type along with examples and best practices:


1. Conditional statements:

These execute statements based on a condition. PL/SQL supports IF and CASE statements. IF statements have forms like IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. CASE statements choose actions based on a condition.


Example using IF-THEN-ELSE:

DECLARE
  grade CHAR(1) := 'A';
  remark VARCHAR2(20);
BEGIN
  IF grade = 'A' THEN
    remark := 'Excellent';
  ELSIF grade = 'B' THEN
    remark := 'Good';
  ELSIF grade = 'C' THEN
    remark := 'Fair';
  ELSE
    remark := 'Poor';
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('Grade: ' || grade);
  DBMS_OUTPUT.PUT_LINE('Remark: ' || remark);
END;
/

Example using CASE:

DECLARE
  grade CHAR(1) := 'A';
  remark VARCHAR2(20);
BEGIN
  remark := CASE grade
    WHEN 'A' THEN 'Excellent'WHEN 'B' THEN 'Good'WHEN 'C' THEN 'Fair'ELSE 'Poor'END CASE;
  
  DBMS_OUTPUT.PUT_LINE('Grade: ' || grade);
  DBMS_OUTPUT.PUT_LINE('Remark: ' || remark);
END;
/

Best practices for conditional statements:

  • Use indentation and alignment for readability.

  • Clarify complex conditions with parentheses.

  • Prefer CASE over nested IF statements for multiple alternatives.

  • Handle null values using NULLIF and COALESCE functions.

2. Loops:

These repeat statements based on a condition. PL/SQL has basic loop, WHILE loop, FOR loop, and cursor FOR loop.


Example using basic loop:

DECLARE
  x NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('x = ' || x);
    x := x + 1;
    EXIT WHEN x > 10;
  END LOOP;
END;
/

Example using WHILE loop:

DECLARE
  x NUMBER := 1;
BEGIN
  WHILE x <= 10 LOOP
    DBMS_OUTPUT.PUT_LINE('x = ' || x);
    x := x + 1;
  END LOOP;
END;
/

Example using FOR loop:

BEGIN
FOR x IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('x = ' || x);
  END LOOP;
END;
/

Example using cursor FOR loop:

DECLARE
    CURSOR c_emp IS SELECT * FROM employees;
BEGIN
FOR r_emp IN c_emp LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.first_name || ' ' || r_emp.last_name);
  END LOOP;
END;
/

Best practices for loops:

  • Use indentation and alignment for readability.

  • Choose meaningful names for loop variables and cursor variables.

  • Terminate loops using EXIT or EXIT WHEN statements.

  • Skip iterations using CONTINUE or CONTINUE WHEN statements.

  • Use FOR loops when the number of iterations is known.

  • Use cursor FOR loops to process each row in a cursor result set.


3. Exception handling:

This manages errors during program execution. PL/SQL has predefined and user-defined exceptions. Exceptions are handled using the EXCEPTION clause and the WHEN clause to specify the exception and action.

DECLARE    
    dividend NUMBER :=100;    
    divisor NUMBER :=0;    
    quotient NUMBER;  
BEGIN    
    quotient := dividend / divisor; 
EXCEPTION
    WHEN ZERO_DIVIDE THEN-- handle ZERO_DIVIDE exception      
        DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');      
        quotient :=NULL;  
END;

Best practices for exception handling:

  • Use indentation and alignment for readability.

  • Give meaningful names to user-defined exceptions.

  • Handle exceptions as close as possible to where they occur.

  • Handle only those exceptions that can be recovered from or provide useful information to the user or caller.

  • Reraise unhandled exceptions or propagate them to the outer block or caller using RAISE or RAISE_APPLICATION_ERROR statements.

By using these control structures effectively, you can enhance the flow and error handling capabilities of your PL/SQL programs.


PL/SQL Program Unit:

PL/SQL program units are blocks of code that can be stored in the database and executed by the PL/SQL engine. They are used to implement business logic, data validation, error handling, and performance optimization. PL/SQL supports four types of program units: procedures, functions, packages, and triggers. Here's a simplified explanation of each type and some examples of how to create and use them:


1. Procedures:

These are subprograms that perform a specific action. They can have parameters to pass values to and from the caller. You can create a procedure using the CREATE PROCEDURE statement. Here's an example:

CREATE PROCEDURE add_employee (
  p_empno NUMBER,
  p_ename VARCHAR2,
  p_job VARCHAR2,
  p_mgr NUMBER,
  p_hiredate DATE,
  p_sal NUMBER,
  p_comm NUMBER,
  p_deptno NUMBER
) AS
BEGIN
  INSERT INTO emp VALUES (
    p_empno,
    p_ename,
    p_job,
    p_mgr,
    p_hiredate,
    p_sal,
    p_comm,
    p_deptno
  );
END;

2. Functions:

These are subprograms that return a single value. They can have parameters to pass values to and from the caller. You can create a function using the CREATE FUNCTION statement. Here's an example:

CREATE FUNCTION avg_sal_by_dept (p_deptno NUMBER) RETURN NUMBER AS
  v_avg_sal NUMBER;
BEGIN
SELECT AVG(sal) INTO v_avg_sal FROM emp WHERE deptno = p_deptno;
  RETURN v_avg_sal;
END;

3. Packages:

These are groups of related procedures, functions, variables, constants, cursors, and types. They can be used to organize and modularize your code, improve performance, and enforce information hiding. You can create a package using the CREATE PACKAGE statement. Here's an example:

CREATE PACKAGE math_pkg AS
  FUNCTION add (x NUMBER, y NUMBER) RETURN NUMBER;
  FUNCTION multiply (x NUMBER, y NUMBER) RETURN NUMBER;
END math_pkg;

CREATE PACKAGE BODY math_pkg AS
  FUNCTION add (x NUMBER, y NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN x + y;
  END add;
  
  FUNCTION multiply (x NUMBER, y NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN x * y;
  END multiply;
END math_pkg;

4. Triggers:

These are special types of procedures that are automatically executed when a specific event occurs in the database. They can be used to enforce business rules, audit changes, or perform actions based on events. You can create a trigger using the CREATE TRIGGER statement. Here's an example:

CREATE TRIGGER audit_emp_sal_trg 
AFTER UPDATE OF sal ON emp
FOR EACH ROW
DECLARE 
  v_username VARCHAR2(30);
BEGIN
  SELECT user INTO v_username FROM dual;
  
  INSERT INTO audit_emp_sal (
    username,
    empno,
    old_sal,
    new_sal,
    change_date
  ) VALUES (
    v_username,
    :OLD.empno,
    :OLD.sal,
    :NEW.sal,
    SYSDATE
  );
END;

By using these program units, you can structure your PL/SQL code and achieve modularity, reusability, and maintainability in your database applications.


Examples of PL/SQL

Example 1: Display a message

This example shows how to display a message using PL/SQL. It declares a variable of type varchar2 and assigns it a value. Then, it uses the dbms_output.put_line procedure to print the value of the variable.

DECLARE
  var varchar2(40) := 'I love GeeksForGeeks';
BEGIN
  dbms_output.put_line(var);
END;
/

Example 2: Simple FOR loop

This example shows how to use a simple FOR loop to insert ten rows into a database table. It uses a loop index, a counter variable, and two character strings as values. It also uses the MOD function to check if the loop index is even or odd.

DECLARE
  x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
    IF MOD(i,2) = 0 THEN -- i is even
      INSERT INTO temp VALUES (i, x, 'i is even');
    ELSE
      INSERT INTO temp VALUES (i, x, 'i is odd');
    END IF;
    x := x + 100;
  END LOOP;
  COMMIT;
END;
/

Example 3: Select the highest paid employee from the Employee Table

This example shows how to use a cursor to select the five highest paid employees from the emp table. It declares a cursor with an ORDER BY clause and a parameter for the department number. Then, it opens the cursor with a specific value for the parameter and fetches five rows from the result set. It also uses an exit condition to stop the loop when there are no more rows to fetch.

DECLARE
  CURSOR c_emp (dept_no NUMBER) IS
    SELECT ename, sal FROM emp
    WHERE deptno = dept_no
    ORDER BY sal DESC;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  OPEN c_emp(10); -- open cursor for department 10
  FOR i IN 1..5 LOOP -- loop five times
    FETCH c_emp INTO v_ename, v_sal; -- fetch one row
    EXIT WHEN c_emp%NOTFOUND; -- exit when no more rows
    DBMS_OUTPUT.PUT_LINE(v_ename || ': ' || v_sal); 
    -- display employee name and salary
    END LOOP;
  CLOSE c_emp; -- close cursor
END;
/


Advantages of PL/SQL

Some of the advantages of PL/SQL are:

  • Tight integration with SQL: PL/SQL is designed to work seamlessly with SQL, the most widely used database manipulation language. PL/SQL lets you use all SQL features, data types, and functions and also supports both static and dynamic SQL.

  • High performance: PL/SQL can improve the performance of database applications by reducing network traffic, using bind variables, and storing subprograms in executable form. PL/SQL also allows you to process query results row by row or in bulk.

  • High productivity: PL/SQL can enhance the productivity of database developers by providing a rich set of features, such as error handling, conditional and looping statements, modular programming, object-oriented programming, and collections. PL/SQL also integrates well with various Oracle tools and development environments.

  • Portability: PL/SQL is portable across different operating systems and Oracle database versions. PL/SQL programs can run on any platform where Oracle database is installed.

  • Scalability: PL/SQL can scale up to handle large and complex database applications. PL/SQL supports parallel processing, concurrency control, and transaction management. PL/SQL also allows you to create reusable code that can be shared by multiple applications.

  • Manageability: PL/SQL can help you manage your database applications more easily and effectively. PL/SQL allows you to store your code in the database server, where it can be accessed, maintained, and secured centrally. PL/SQL also provides tools for debugging, testing, tracing, and tuning your code.

  • Support for object-oriented programming: PL/SQL supports object-oriented programming concepts, such as abstraction, encapsulation, inheritance, and polymorphism. PL/SQL allows you to create user-defined types, methods, constructors, and operators. PL/SQL also supports object views and object tables that map relational data to objects.


Disadvantages of PL/SQL

Some of the disadvantages of PL/SQL are:

  • Lack of portability: PL/SQL is specific to Oracle databases and may not be compatible with other database management systems. This can limit the use and flexibility of PL/SQL programs.

  • Specialized knowledge: PL/SQL is a specialized programming language that requires developers to invest time and effort in learning and mastering it. It may not be easy for beginners or non-Oracle users to understand and use PL/SQL.

  • Security vulnerabilities: PL/SQL code can introduce security vulnerabilities if it is not properly designed and implemented. For example, PL/SQL code can be vulnerable to SQL injection attacks if it uses dynamic SQL or concatenates user input with SQL statements.

  • Prone to errors: PL/SQL code can be prone to errors, especially if it is complex or poorly written. Errors in PL/SQL code can affect the reliability and stability of database applications.

  • Resource-intensive: PL/SQL code can be resource-intensive, which can affect the performance and scalability of database applications. For example, PL/SQL code can consume more memory and CPU cycles than SQL code.


Difference between SQL and PL/SQL

Factors

SQL

PL/SQL

Purpose

Used for querying and manipulating databases

Used for writing procedural code and database operations

Language Type

Declarative

Procedural

Execution

Single statements or batched queries

Blocks of code executed as a unit

Control Flow

Limited control flow capabilities

Extensive control flow capabilities

Looping

No built-in loop constructs

Supports various loop constructs (e.g., FOR, WHILE)

Conditionals

No built-in conditional statements

Supports conditional statements (e.g., IF, CASE)

Error Handling

Limited error-handling capabilities

Comprehensive error handling and exception management

Variable

Does not support variables

Supports variables and data types

Code Reusability

Not designed for code reusability

Supports reusable code through functions and procedures

Modularity

Not designed for modular programming

Supports modular programming with packages

Performance

Optimized for executing SQL queries

Provides efficient data processing within the database


0 comments

Comments

bottom of page