PL/SQL Concepts – An Overview

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 variable student_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:

1. Predefined Exceptions – Errors like NO_DATA_FOUND, ZERO_DIVIDE
2. 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.