PL/SQL Fundamentals

PL/SQL (Procedural Language/SQL) is Oracle's powerful procedural extension to SQL. It combines the data manipulation power of SQL with the processing power of procedural languages, allowing you to write complex business logic that runs directly in the database.

---

Why PL/SQL?

• Performance — reduces network round trips by processing data in the database
• Portability — runs on any platform that supports Oracle Database
• Integration — seamlessly combines SQL and procedural logic
• Security — encapsulate business logic and grant access via procedures
• Error handling — robust exception handling framework

---

Block Structure

Every PL/SQL program is built from blocks:

DECLARE
  -- Variable declarations (optional)
  v_name VARCHAR2(50);
  v_salary NUMBER(10, 2);
BEGIN
  -- Executable statements (required)
  SELECT first_name, salary
  INTO v_name, v_salary
  FROM employees
  WHERE employee_id = 100;

  DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_salary);
EXCEPTION
  -- Error handling (optional)
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Section	Purpose	Required?
DECLARE	Declare variables, constants, cursors	Optional
BEGIN...END	Executable code	Required
EXCEPTION	Error handling	Optional

---

Variables and Data Types

DECLARE
  -- Scalar types
  v_name      VARCHAR2(100) := 'John';
  v_salary    NUMBER(10, 2) := 50000;
  v_hire_date DATE := SYSDATE;
  v_active    BOOLEAN := TRUE;

  -- Anchored types (match a column's type)
  v_emp_name  employees.first_name%TYPE;
  v_dept_row  departments%ROWTYPE;

  -- Constants
  c_tax_rate  CONSTANT NUMBER := 0.2;
BEGIN
  NULL;
END;
/

Tip: Use %TYPE and %ROWTYPE to anchor variables to table columns — if the column type changes, your code adapts automatically.

---

Control Structures

IF-THEN-ELSIF-ELSE

IF v_salary > 10000 THEN
  v_bonus := v_salary * 0.15;
ELSIF v_salary > 5000 THEN
  v_bonus := v_salary * 0.10;
ELSE
  v_bonus := v_salary * 0.05;
END IF;

CASE Expression

v_grade := CASE
  WHEN v_salary > 10000 THEN 'A'
  WHEN v_salary > 5000  THEN 'B'
  ELSE 'C'
END;

Loops

-- Basic LOOP
LOOP
  EXIT WHEN v_counter > 10;
  v_counter := v_counter + 1;
END LOOP;

-- WHILE loop
WHILE v_counter <= 10 LOOP
  v_counter := v_counter + 1;
END LOOP;

-- FOR loop (numeric)
FOR i IN 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;

-- FOR loop (reverse)
FOR i IN REVERSE 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

---

Cursors

Cursors allow row-by-row processing of query results.

Implicit Cursors

Oracle automatically opens and closes cursors for single-row queries:

SELECT first_name INTO v_name FROM employees WHERE employee_id = 100;

Explicit Cursors

For multi-row queries:

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = 50;

  v_id     employees.employee_id%TYPE;
  v_name   employees.first_name%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_id, v_name, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_name || ' - ' || v_salary);
  END LOOP;
  CLOSE emp_cursor;
END;
/

Cursor FOR Loop (Recommended)

Simplifies cursor processing — no OPEN, FETCH, or CLOSE needed:

FOR rec IN (SELECT employee_id, first_name, salary
            FROM employees WHERE department_id = 50) LOOP
  DBMS_OUTPUT.PUT_LINE(rec.employee_id || ': ' || rec.first_name);
END LOOP;

Cursor Attributes