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:
What is PL/SQL?
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.
The Below PL/SQL Example is a pictorial representation of PL/SQL Architecture.
The PL/SQL architecture mainly consists of the following three components:
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.
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.
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.
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.
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.
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:
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; /
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; /
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; /
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; /
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.
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:
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;
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;
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;
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
Used for querying and manipulating databases
Used for writing procedural code and database operations
Single statements or batched queries
Blocks of code executed as a unit
Limited control flow capabilities
Extensive control flow capabilities
No built-in loop constructs
Supports various loop constructs (e.g., FOR, WHILE)
No built-in conditional statements
Supports conditional statements (e.g., IF, CASE)
Limited error-handling capabilities
Comprehensive error handling and exception management
Does not support variables
Supports variables and data types
Not designed for code reusability
Supports reusable code through functions and procedures
Not designed for modular programming
Supports modular programming with packages
Optimized for executing SQL queries
Provides efficient data processing within the database