What is PL/SQL?
PL/SQL (Procedural Language/Structured Query Language) is Oracle’s extension of SQL that allows procedural programming capabilities. It integrates SQL with procedural constructs like loops, conditions, and exception handling, enabling more powerful database interactions.
PL/SQL provides features like:
✅ Block structure (divides code into logical sections)
✅ Control structures (loops, conditional statements)
✅ Exception handling (error management)
✅ Stored procedures & functions (reusable code)
Basic Structure of PL/SQL Block
PL/SQL code is written in blocks, which consist of the following sections:
DECLARE -- (Optional) Declare variables, constants, cursors
BEGIN -- Mandatory: Execution section
-- SQL & PL/SQL statements
EXCEPTION -- (Optional) Handle errors
-- Error handling code
END;
Example PL/SQL Block
DECLARE
student_name VARCHAR2(50);
BEGIN
SELECT Name INTO student_name FROM Students WHERE StudentID = 101;
DBMS_OUTPUT.PUT_LINE('Student Name: ' || student_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No student found.');
END;
📌 Explanation:
DECLARE
– Defines a variablestudent_name
.BEGIN
– Executes a SQL query to fetch data.EXCEPTION
– Handles the case where no student is found.
PL/SQL Components
1. Variables and Constants
Variables store data for computation. Constants hold values that do not change.
Declaring Variables:
DECLARE
student_age NUMBER(2);
student_name VARCHAR2(50);
Declaring Constants:
DECLARE
min_pass_mark CONSTANT NUMBER := 40;
2. Control Structures (IF, LOOP, CASE)
PL/SQL provides conditional statements and loops for flow control.
IF-ELSE Statement
DECLARE
age NUMBER := 20;
BEGIN
IF age >= 18 THEN
DBMS_OUTPUT.PUT_LINE('Eligible to vote');
ELSE
DBMS_OUTPUT.PUT_LINE('Not eligible to vote');
END IF;
END;
LOOPS in PL/SQL
Loops are used to execute code multiple times.
WHILE Loop Example:
DECLARE
counter NUMBER := 1;
BEGIN
WHILE counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
counter := counter + 1;
END LOOP;
END;
FOR Loop Example:
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Value: ' || i);
END LOOP;
END;
3. Exception Handling in PL/SQL
PL/SQL handles errors using the EXCEPTION
section.
Types of Exceptions:
- Predefined Exceptions – Errors like
NO_DATA_FOUND
,ZERO_DIVIDE
- User-defined Exceptions – Custom error handling
Example of Exception Handling
DECLARE
v_number NUMBER;
BEGIN
v_number := 10 / 0; -- Error: Division by zero
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Cannot divide by zero.');
END;
Stored Procedures & Functions
PL/SQL allows creating procedures and functions for reusable logic.
Stored Procedure Example
CREATE OR REPLACE PROCEDURE get_student_details (p_id IN NUMBER) AS
v_name VARCHAR2(50);
BEGIN
SELECT Name INTO v_name FROM Students WHERE StudentID = p_id;
DBMS_OUTPUT.PUT_LINE('Student Name: ' || v_name);
END;
📌 Calling the Procedure:
EXEC get_student_details(101);
Function Example
CREATE OR REPLACE FUNCTION square_number (num IN NUMBER) RETURN NUMBER AS
BEGIN
RETURN num * num;
END;
📌 Calling the Function:
DECLARE result NUMBER;
BEGIN
result := square_number(5);
DBMS_OUTPUT.PUT_LINE('Square: ' || result);
END;
PL/SQL Triggers
Triggers automatically execute in response to events (e.g., INSERT
, UPDATE
, DELETE
).
Example: Trigger on INSERT
CREATE OR REPLACE TRIGGER before_student_insert
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('New Student Inserted: ' || :NEW.Name);
END;
Conclusion
PL/SQL enhances SQL with procedural capabilities, enabling complex logic, reusable code, and efficient database management. It is widely used in Oracle databases for automation, data validation, and performance optimization.