PL/SQL Architecture, SQL vs. PL/SQL, and Exception Handling

TAKEAWAYS:

• PL/SQL runs in Oracle’s three-layer architecture (SQL engine → PL/SQL engine → database), enabling procedural logic on top of SQL.
• SQL is declarative (what to do), while PL/SQL is procedural (how to do it), with features like loops, conditions, and exception handling.
• Exception handling in PL/SQL uses EXCEPTION blocks to catch errors (predefined like ZERO_DIVIDE or user-defined) and execute recovery code.
• Anonymous blocks execute once, while named blocks (procedures/functions) can be reused and parameterized.
• Predefined exceptions (e.g., NO_DATA_FOUND, TOO_MANY_ROWS) handle common SQL errors; user-defined exceptions require RAISE_APPLICATION_ERROR.
• Oracle’s PL/SQL engine parses, optimizes, and executes PL/SQL code, while the SQL engine processes SQL statements.

PL/SQL Architecture: How It Works Under the Hood

PL/SQL is Oracle’s procedural extension to SQL, designed to run inside the database. Its architecture consists of three key layers:

1. SQL Engine: Executes SQL statements (DML, DDL, DCL).
2. PL/SQL Engine: Handles procedural logic (blocks, loops, exceptions).
3. Database: Stores data and metadata.

flowchart LR
    A["User/Application"] -->|"SQL/PLSQL"| B["PL/SQL Engine"]
    B -->|"Parsing/Validation"| C["SQL Engine"]
    C -->|"Execution"| D["Database"]
    D -->|"Results"| B
    B -->|"Output"| A

Oracle’s multi-layered architecture: PL/SQL sits between SQL and the database. (Image: Brickcompass, CC BY 4.0, via Wikimedia Commons)

How PL/SQL Blocks Execute

PL/SQL processes code in blocks, which can be:

• Anonymous blocks: Run once, no name.

  DECLARE
      v_salary NUMBER := 50000;
  BEGIN
      IF v_salary > 40000 THEN
          DBMS_OUTPUT.PUT_LINE('Eligible for bonus!');
      END IF;
  END;
  

• Named blocks: Stored in the database (procedures, functions, packages).

Key Steps in Execution:

1. Parsing: Checks syntax and semantics.
2. Optimization: Generates execution plan.
3. Execution: Runs the code in the database.

SQL vs. PL/SQL: What’s the Difference?

Example: SQL vs. PL/SQL for Salary Calculation

• SQL (Declarative):

  SELECT employee_name, salary * 1.1 AS new_salary
  FROM employees
  WHERE department_id = 10;
  

• PL/SQL (Procedural):

  DECLARE
      v_raise NUMBER := 1.1;
  BEGIN
      UPDATE employees
      SET salary = salary * v_raise
      WHERE department_id = 10;
      COMMIT;
  END;
  

Why Use PL/SQL?

• Business logic: Implement rules (e.g., "If salary > 50k, auto-approve loan").
• Error handling: Catch and recover from errors gracefully.
• Reusability: Store procedures for repeated tasks (e.g., CALCULATE_BONUS).

In the Real World

1. eSewa (Nepal):

   • Uses PL/SQL to validate transactions before processing payments.
   • Example: A BEFORE INSERT trigger checks if the user’s account balance is sufficient before deducting fees.

     CREATE OR REPLACE TRIGGER check_balance
     BEFORE INSERT ON transactions
     FOR EACH ROW
     BEGIN
         IF :NEW.amount > (SELECT balance FROM users WHERE user_id = :NEW.user_id) THEN
             RAISE_APPLICATION_ERROR(-20001, 'Insufficient balance!');
         END IF;
     END;
     

2. Ncell (Telecom):

   • PL/SQL calculates bill amounts dynamically (e.g., discounts for bulk SMS).
   • Example: A stored procedure CALCULATE_BILL uses CASE statements to apply promotions.

3. NEPSE (Stock Exchange):

   • Uses PL/SQL triggers to log trades and update share prices in real time.
   • Example: A BEFORE UPDATE trigger on the shares table validates price changes against market rules.

Exception Handling: Making Your Code Robust

Exceptions are errors or unexpected events. PL/SQL handles them with:

• Predefined exceptions (e.g., ZERO_DIVIDE, NO_DATA_FOUND).
• User-defined exceptions (custom error messages).

Syntax for Exception Handling

DECLARE
    v_employee_id employees.employee_id%TYPE;
    e_no_record EXCEPTION;
BEGIN
    SELECT employee_id INTO v_employee_id
    FROM employees
    WHERE employee_name = 'John Doe';

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE e_no_record;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Common Predefined Exceptions

User-Defined Exceptions

DECLARE
    e_invalid_salary EXCEPTION;
    v_salary NUMBER := -5000;
BEGIN
    IF v_salary < 0 THEN
        RAISE e_invalid_salary;
    END IF;
EXCEPTION
    WHEN e_invalid_salary THEN
        DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be negative!');
END;

Real-World Example: Bank Loan Approval A bank uses PL/SQL to validate loan applications:

CREATE OR REPLACE PROCEDURE APPROVE_LOAN(
    p_customer_id IN NUMBER,
    p_amount IN NUMBER
) AS
    e_invalid_amount EXCEPTION;
BEGIN
    IF p_amount <= 0 THEN
        RAISE e_invalid_amount;
    END IF
    -- Process loan...
EXCEPTION
    WHEN e_invalid_amount THEN
        INSERT INTO audit_log (error_message)
        VALUES ('Invalid loan amount for customer ' || p_customer_id);
        DBMS_OUTPUT.PUT_LINE('Loan rejected: Invalid amount.');
END;

Anonymous vs. Named Blocks

Example: Anonymous Block (One-Time Calculation)

DECLARE
    v_total NUMBER := 0;
BEGIN
    SELECT SUM(salary) INTO v_total
    FROM employees;
    DBMS_OUTPUT.PUT_LINE('Total salary: ' || v_total);
END;

Example: Named Block (Reusable Procedure)

CREATE OR REPLACE PROCEDURE CALCULATE_TAX(
    p_salary IN NUMBER,
    p_tax OUT NUMBER
) AS
BEGIN
    IF p_salary <= 50000 THEN
        p_tax := p_salary * 0.1;
    ELSE
        p_tax := p_salary * 0.2;
    END IF;
END;

Oracle Database Architecture: Where PL/SQL Fits In

Oracle’s architecture has three main layers:

1. Client Layer: Applications (SQL*Plus, TOAD).
2. Oracle Server: PL/SQL engine + SQL engine.
3. Database Layer: Data storage (tables, indexes).

flowchart TD
    A["Client Application"] -->|"SQL/PLSQL"| B["Oracle Server"]
    B --> C["PL/SQL Engine"]
    B --> D["SQL Engine"]
    C -->|"Procedural Logic"| E["Database"]
    D -->|"SQL Queries"| E
    E -->|"Results"| B

Exam Tip

1. Architecture: Draw the three-layer PL/SQL architecture (SQL engine → PL/SQL engine → database) and label each layer’s role.
2. SQL vs. PL/SQL: Compare them in a table (purpose, syntax, error handling) and give one example each.
3. Exception Handling:
   • Know 5 predefined exceptions and when they occur.
   • Write a user-defined exception with RAISE_APPLICATION_ERROR.
   • Explain WHEN OTHERS and SQLERRM.
4. Blocks: Differentiate anonymous vs. named blocks with syntax examples.
5. Real-World Tie: For any question, relate to eSewa/Khalti/Ncell (e.g., "How would you use PL/SQL to validate a Khalti payment?").

Common Pitfalls:

• Forgetting COMMIT in procedures (use AUTONOMOUS_TRANSACTION if needed).
• Not handling NO_DATA_FOUND in SELECT INTO queries.
• Confusing IN, OUT, and IN OUT parameters in procedures.

Worked Example: Loan Interest Calculation with Exception Handling Scenario: A bank uses PL/SQL to calculate loan interest. If the interest rate is negative, raise an error.

CREATE OR REPLACE PROCEDURE CALCULATE_INTEREST(
    p_principal IN NUMBER,
    p_rate IN NUMBER,
    p_years IN NUMBER,
    p_interest OUT NUMBER
) AS
    e_invalid_rate EXCEPTION;
BEGIN
    IF p_rate < 0 THEN
        RAISE e_invalid_rate;
    END IF
    p_interest := p_principal * p_rate * p_years / 100;
EXCEPTION
    WHEN e_invalid_rate THEN
        DBMS_OUTPUT.PUT_LINE('Error: Interest rate cannot be negative.');
        p_interest := NULL;
END;

Test Case:

DECLARE
    v_interest NUMBER;
BEGIN
    CALCULATE_INTEREST(100000, -5, 2, v_interest);
    -- Output: "Error: Interest rate cannot be negative."
END;