💻 Database Programming with PL/SQL

---

1. What is PL/SQL?

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension to SQL.

• It allows writing procedural code (loops, conditions, variables) along with SQL statements.
• Supports modular programming with blocks, procedures, functions, and packages.
• Enables embedding of SQL inside programming logic for complex operations.

---

2. Why Use PL/SQL?

• SQL is declarative (what to do), PL/SQL adds procedural capabilities (how to do it).
• Enables control structures: IF-ELSE, loops (FOR, WHILE).
• Supports exception handling (error management).
• Allows performance improvement by reducing network traffic — multiple SQL statements can be sent as one block.
• Used for writing stored procedures, triggers, functions.

---

3. Basic Structure of a PL/SQL Block

DECLARE
  -- Declaration of variables and constants
  v_employee_id NUMBER := 100;
  v_salary NUMBER;
BEGIN
  -- Executable statements
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id;

  IF v_salary < 5000 THEN
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_employee_id;
  END IF;
  
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred');
END;

---

4. Components of PL/SQL

a) Declarations Section

• Variables, constants, cursors, and types are declared.

b) Executable Section

• Contains procedural code, SQL statements.

c) Exception Handling Section

• Handles runtime errors using predefined or user-defined exceptions.

---

5. Key PL/SQL Features

a) Variables and Data Types

• Supports data types like NUMBER, VARCHAR2, DATE, BOOLEAN.

b) Control Structures

• Conditional statements: IF...THEN...ELSE
• Loops: FOR, WHILE, LOOP...EXIT WHEN

c) Cursors

• Used to fetch multiple rows one at a time.

• Types:

  • Implicit Cursor: Automatic for single SQL DML.
  • Explicit Cursor: Defined by programmer for multi-row queries.

Example of Explicit Cursor:

DECLARE
  CURSOR emp_cursor IS SELECT employee_id, salary FROM employees WHERE department_id = 10;
  v_employee_id employees.employee_id%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_employee_id, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_id || ', Salary: ' || v_salary);
  END LOOP;
  CLOSE emp_cursor;
END;

---

6. Stored Procedures and Functions

• Stored Procedure: Performs an action, may or may not return a value.
• Function: Always returns a value.

Example Procedure:

CREATE OR REPLACE PROCEDURE increase_salary (p_emp_id IN NUMBER, p_increment IN NUMBER) IS
BEGIN
  UPDATE employees SET salary = salary + p_increment WHERE employee_id = p_emp_id;
  COMMIT;
END;

---

7. Triggers

• Special PL/SQL blocks executed automatically on certain events:

  • Before or after INSERT, UPDATE, or DELETE
  • For each row or statement

Example Trigger:

CREATE OR REPLACE TRIGGER trg_salary_check
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
  END IF;
END;

---

8. Exception Handling

• Built-in exceptions (e.g., NO_DATA_FOUND, TOO_MANY_ROWS)
• User-defined exceptions using RAISE and EXCEPTION blocks.

---

9. Benefits of PL/SQL

• Tight integration with SQL
• Improved performance by reducing client-server communication
• Code reuse through modular programming
• Strong error handling
• Portability across Oracle environments

---

📝 Summary

Topic	Description
PL/SQL	Procedural extension of SQL
Blocks	Declare, Execute, Exception sections
Variables	Hold data for processing
Control Structures	Conditional and loops
Cursors	Process multiple rows
Procedures/Functions	Modular code blocks
Triggers	Automatic event-driven code
Exception Handling	Error management

---