📚 Module 9: PL/SQL Basics

Blocks, Variables, Control Structures, Exception Handling

BEGIN
Block Structure
DECLARE
Variables
IF/LOOP
Control Flow
EXCEPTION
Error Handling

🏗️ PL/SQL Block Structure

Anonymous Block Syntax

DECLARE
  -- Variable declarations
  v_variable_name data_type [:= initial_value];
  v_name VARCHAR2(100);
  v_count NUMBER := 0;
BEGIN
  -- Executable statements
  NULL; -- Placeholder statement
EXCEPTION
  -- Exception handling
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Block Types

Block Type Purpose Usage
Anonymous One-time execution Script, testing, ad-hoc tasks
Procedure Reusable logic DML operations, business logic
Function Return value Calculations, transformations
Package Organized procedures Module grouping, related logic
Trigger Event-driven Data validation, audit trails

📦 Variables & Data Types

Variable Declaration Examples

DECLARE
  -- Scalar variables
  v_employee_id NUMBER;
  v_name VARCHAR2(100);
  v_salary NUMBER(10,2) := 50000.00;
  v_hire_date DATE;
  v_is_active BOOLEAN := TRUE;
  
  -- Using %TYPE attribute
  v_emp_id employees.employee_id%TYPE;
  v_emp_name employees.first_name%TYPE;
  
  -- Using %ROWTYPE
  v_emp_record employees%ROWTYPE;
  
BEGIN
  v_employee_id := 101;
  v_name := 'John Doe';
  v_hire_date := SYSDATE;
  
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END;
/

🔄 Control Structures

IF/ELSIF/ELSE Statement

DECLARE
  v_salary NUMBER := 75000;
  v_bonus NUMBER;
BEGIN
  IF v_salary >= 100000 THEN
    v_bonus := v_salary * 0.15;
  ELSIF v_salary >= 75000 THEN
    v_bonus := v_salary * 0.10;
  ELSE
    v_bonus := 0;
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
/

Loops - LOOP, WHILE, FOR

-- Simple LOOP with EXIT
DECLARE
  v_counter NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_counter);
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 5;
  END LOOP;
END;
/

-- FOR LOOP with range
BEGIN
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Loop counter: ' || i);
  END LOOP;
END;
/

⚠️ Exception Handling

Predefined Exceptions

Exception Error Code Description
NO_DATA_FOUND ORA-01403 SELECT INTO returns no rows
TOO_MANY_ROWS ORA-01422 SELECT INTO returns multiple rows
ZERO_DIVIDE ORA-01476 Division by zero
DUP_VAL_ON_INDEX ORA-00001 Unique constraint violation

Exception Handling Example

DECLARE
  v_salary employees.salary%TYPE;
  v_employee_id NUMBER := 999;
BEGIN
  SELECT salary INTO v_salary
  FROM employees
  WHERE employee_id = v_employee_id;
  
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple employees found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
💡 Tip: Always use %TYPE and %ROWTYPE for flexible code maintenance. Handle exceptions appropriately to prevent silent failures. Use DBMS_OUTPUT.PUT_LINE for debugging.
✓ Learning Checklist - Module 9