PL/SQL

1. What is PL/SQL?

Imagine you're at a restaurant. SQL is like pointing at a menu item and saying "give me that" — you can ask for data, but that's it. You can't say "if the pasta is sold out, get me pizza instead" or "keep bringing bread until I say stop." SQL has no logic — no decisions, no loops, no error handling.

PL/SQL (Procedural Language / SQL) is Oracle's way of adding a brain to SQL. It wraps programming logic — IF/ELSE decisions, loops, variables, error handling — around your SQL statements so they can run as a complete program inside the database itself.

Why does it matter? Without PL/SQL, your application would need to send dozens of separate SQL commands over the network. With PL/SQL, you bundle all that logic into one block, send it once, and the database handles it. Faster, cleaner, fewer network round trips.

2. Block Structure — The #1 Exam Topic

Every PL/SQL program is organized into blocks. Think of a block like a recipe card:

• Ingredients list (DECLARE) — what you need before you start. Variables, constants, cursors go here. Optional — some recipes need no prep.
• Cooking steps (BEGIN...END) — the actual work. Your SQL statements and logic go here. Mandatory — a recipe with no steps isn't a recipe.
• What to do if something goes wrong (EXCEPTION) — burned the toast? Ran out of sugar? Handle it here instead of crashing. Optional — but smart cooks always have a backup plan.

-- PL/SQL block structure
DECLARE
  -- Variables (OPTIONAL) — "ingredients list"
  v_name VARCHAR2(50);
BEGIN
  -- Your logic (MANDATORY) — "cooking steps"
  v_name := 'Darshan';
  DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name);
EXCEPTION
  -- Error handling (OPTIONAL) — "backup plan"
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error occurred');
END;
/

Anonymous blocks vs Named blocks: The block above is anonymous — it runs once and disappears. When you save a block as a procedure, function, or trigger, it becomes a named block stored in the database. Same structure, just reusable.

3. Variables & Data Types

Why do we need variables? Imagine you're a cashier. A customer buys 3 items. You need a temporary place to hold the running total while you scan each one. That's a variable — a named container that holds a value while your program runs.

In PL/SQL, variables live in the DECLARE section. You give each one a name, a type (what kind of data it holds), and optionally an initial value.

DECLARE
  v_salary   NUMBER(10, 2);          -- number with 2 decimals
  v_name     VARCHAR2(100);          -- variable-length text (up to 100 chars)
  v_dob      DATE;                   -- date + time
  v_active   BOOLEAN;               -- TRUE / FALSE / NULL
BEGIN
  v_salary := 75000;  -- Assignment uses := (not =)
END;
/

Anchor Types — The Smart Way

The problem: You declare v_name VARCHAR2(50), but the actual column in the employees table is VARCHAR2(100). Now your variable is too small and your program crashes. Worse — if someone changes the column size later, your code silently breaks.

The solution: Instead of hardcoding the type, anchor your variable to the column itself. "Whatever type that column is, make me the same."

DECLARE
  -- %TYPE — borrows type from ONE column
  v_emp_name employees.first_name%TYPE;     -- "same type as first_name column"

  -- %ROWTYPE — borrows structure of ENTIRE row
  v_emp_row  employees%ROWTYPE;            -- has .first_name, .salary, .dept_id, etc.

  -- CONSTANT — a value that NEVER changes
  c_tax_rate CONSTANT NUMBER := 0.18;     -- must assign value immediately
BEGIN
  NULL;
END;
/

4. Control Structures

Why? Without control structures, your program runs top to bottom like a conveyor belt — every line executes, no matter what. But real problems need decisions ("is this employee eligible for a bonus?") and repetition ("process every row in this table"). Control structures give your program a brain.

IF Statement — Making Decisions

Works exactly like you'd expect: check a condition, do something if true, something else if false.

IF v_score >= 85 THEN
  DBMS_OUTPUT.PUT_LINE('Distinction');
ELSIF v_score >= 60 THEN    -- Note: ELSIF not ELSEIF
  DBMS_OUTPUT.PUT_LINE('First Class');
ELSE
  DBMS_OUTPUT.PUT_LINE('Pass');
END IF;

CASE Statement — Cleaner Multi-Way Branching

When you have many specific values to check (like a grade or status code), CASE is cleaner than chaining IF/ELSIF.

CASE v_grade
  WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
  ELSE          DBMS_OUTPUT.PUT_LINE('Average');
END CASE;

Loops — Doing Things Repeatedly

PL/SQL gives you three types of loops. Think of them as three ways to eat a bag of chips:

• Basic LOOP — keep eating until you decide to stop (you must explicitly say "EXIT"). Dangerous if you forget to stop.
• WHILE LOOP — keep eating while there are chips left (checks before each bite).
• FOR LOOP — eat exactly 10 chips (you know the count upfront).

-- 1. Basic LOOP — needs EXIT to stop (or it runs forever!)
LOOP
  DBMS_OUTPUT.PUT_LINE(v_i);
  v_i := v_i + 1;
  EXIT WHEN v_i > 5;
END LOOP;

-- 2. WHILE LOOP — checks condition FIRST, then executes
WHILE v_j <= 5 LOOP
  DBMS_OUTPUT.PUT_LINE(v_j);
  v_j := v_j + 1;
END LOOP;

-- 3. FOR LOOP — counter managed automatically, no manual increment
FOR i IN 1..5 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

5. Cursors

The problem: When you use SELECT INTO in PL/SQL, it expects exactly ONE row back. But what if your query returns 50 employees? SELECT INTO crashes with TOO_MANY_ROWS. You need a way to process rows one at a time.

That's what a cursor is. Think of it as a bookmark in a book. The database runs your query and gets back a stack of results. The cursor points to the current row — you read it, process it, then move the cursor to the next row. Repeat until done.

Implicit Cursor — Automatic

Every time you run a DML statement, Oracle silently creates an implicit cursor. You don't see it, but you can ask it questions:

BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;

  -- SQL%ROWCOUNT tells you how many rows were affected
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees got a raise');

  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employees in dept 10');
  END IF;
END;
/

Explicit Cursor — 4-Step Pattern

When your SELECT returns many rows, you declare an explicit cursor and follow this strict sequence:

1. DECLARE — define the query the cursor will run
2. OPEN — execute the query and create the result set
3. FETCH — grab one row at a time (in a loop)
4. CLOSE — release the resources

DECLARE
  -- Step 1: DECLARE the cursor (define the query)
  CURSOR c_emp IS
    SELECT first_name, salary FROM employees WHERE dept_id = 10;
  v_name employees.first_name%TYPE;
  v_sal  employees.salary%TYPE;
BEGIN
  OPEN c_emp;           -- Step 2: OPEN (runs the query)
  LOOP
    FETCH c_emp INTO v_name, v_sal;  -- Step 3: FETCH one row
    EXIT WHEN c_emp%NOTFOUND;       -- stop when no more rows
    DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_sal);
  END LOOP;
  CLOSE c_emp;          -- Step 4: CLOSE (free resources)
END;
/

Cursor Attributes

Cursor FOR Loop — The Shortcut

The 4-step pattern works, but it's verbose. PL/SQL offers a shortcut that handles OPEN, FETCH, and CLOSE automatically:

BEGIN
  FOR emp IN (SELECT first_name, salary FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(emp.first_name || ' — ' || emp.salary);
  END LOOP;  -- auto OPEN, FETCH, CLOSE — no manual work
END;
/

6. Procedures

The problem: You've written a PL/SQL block that gives an employee a raise. It works great. But now you need to use the same logic in 5 different places. Are you going to copy-paste the block 5 times? What happens when the logic changes — do you update all 5 copies?

A procedure is a named, reusable block of code stored in the database. Write it once, call it from anywhere. It's like saving a recipe in a cookbook — you don't rewrite it every time you cook; you just say "follow the raise recipe."

CREATE OR REPLACE PROCEDURE give_raise (
  p_emp_id  IN  employees.employee_id%TYPE,  -- input: which employee
  p_amount  IN  NUMBER,                     -- input: raise amount
  p_new_sal OUT NUMBER                      -- output: new salary after raise
) IS
  v_old_sal NUMBER;
BEGIN
  SELECT salary INTO v_old_sal FROM employees WHERE employee_id = p_emp_id;
  p_new_sal := v_old_sal + p_amount;
  UPDATE employees SET salary = p_new_sal WHERE employee_id = p_emp_id;
END give_raise;
/

7. Functions

Functions are like procedures with one key difference: a function MUST return exactly one value.

Think of the difference this way: A procedure is like telling someone "go clean the room" — they do it, but they don't hand you anything back. A function is like asking "what's 2 + 2?" — they MUST give you an answer back.

CREATE OR REPLACE FUNCTION get_annual_salary (
  p_emp_id IN employees.employee_id%TYPE
) RETURN NUMBER IS
  v_monthly NUMBER;
BEGIN
  SELECT salary INTO v_monthly FROM employees WHERE employee_id = p_emp_id;
  RETURN v_monthly * 12;  -- MUST have a RETURN statement
END;
/

The big advantage of functions: You can use them directly inside SQL queries. This is something procedures cannot do.

-- Using a function inside a SELECT — procedures CAN'T do this
SELECT first_name, get_annual_salary(employee_id) AS annual
FROM employees;

8. Triggers

Why do triggers exist? Sometimes you want something to happen automatically every time data changes — without relying on the application to remember. For example: "Every time someone inserts a new employee, automatically set the created_at timestamp." Or: "If someone tries to insert a negative salary, block it."

A trigger is a PL/SQL block that fires automatically when a specific event (INSERT, UPDATE, DELETE) happens on a table. You don't call a trigger — Oracle calls it for you. It's like a burglar alarm: you set it up once, and it activates on its own when someone opens the door.

CREATE OR REPLACE TRIGGER trg_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  -- Validation: reject bad data BEFORE it enters the table
  IF :NEW.salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
  END IF;
  -- Auto-fill: set timestamp automatically
  :NEW.created_at := SYSDATE;
END;
/

:NEW and :OLD — The Two Magic Variables

Inside a row-level trigger, you have access to the data being changed:

• :NEW — the values AFTER the change (what's being inserted or updated TO)
• :OLD — the values BEFORE the change (what was there before)

9. Exception Handling

Why? Programs break. A query returns zero rows when you expected one. Someone divides by zero. A value doesn't fit into a variable. Without exception handling, your entire block crashes and the user sees a cryptic Oracle error. With exception handling, you catch the error, handle it gracefully, and keep control.

The EXCEPTION section goes right before END; and catches specific errors using WHEN:

BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;
  -- If employee 9999 doesn't exist, this crashes... unless we catch it:
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple rows returned.');
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Cannot divide by zero.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' — ' || SQLERRM);
END;
/

SQLCODE and SQLERRM — Debugging Tools

Inside WHEN OTHERS, you often want to know what went wrong:

• SQLCODE — returns the numeric error code (e.g., -1 for unique constraint violation)
• SQLERRM — returns the human-readable error message text

User-Defined Exceptions

Oracle's predefined exceptions cover common cases, but what about your business rules? "Salary can't exceed 500000" isn't a database error — it's YOUR rule. You can create custom exceptions:

DECLARE
  e_salary_too_high EXCEPTION;    -- Step 1: declare it
  v_salary NUMBER := 600000;
BEGIN
  IF v_salary > 500000 THEN
    RAISE e_salary_too_high;       -- Step 2: raise it
  END IF;
EXCEPTION
  WHEN e_salary_too_high THEN    -- Step 3: handle it
    DBMS_OUTPUT.PUT_LINE('Salary exceeds maximum limit!');
END;
/

10. Packages

Why packages? As your database grows, you might have 20 procedures and 15 functions scattered everywhere. A package groups related procedures, functions, variables, and cursors into one organized unit — like putting related files into a folder instead of dumping everything on your desktop.

Every package has two parts:

• Specification (spec) — declares WHAT the package offers (public procedure/function signatures). This is the "menu" other code can call.
• Body — contains the actual implementation code. This is where the real work happens.

-- Specification: the public menu (what others can call)
CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE hire(p_name VARCHAR2, p_sal NUMBER);
  FUNCTION get_salary(p_id NUMBER) RETURN NUMBER;
END emp_pkg;
/

-- Body: the actual implementation (hidden from callers)
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE hire(p_name VARCHAR2, p_sal NUMBER) IS BEGIN
    INSERT INTO employees(first_name, salary) VALUES(p_name, p_sal);
  END;
  FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS v_sal NUMBER;
  BEGIN
    SELECT salary INTO v_sal FROM employees WHERE employee_id = p_id;
    RETURN v_sal;
  END;
END emp_pkg;
/

Calling package members: Use dot notation — emp_pkg.hire('Darshan', 50000);

Practice Questions — PL/SQL (Mixed)

These questions mix all topics. If you can get 80%+ right, you're ready for the FA exam's PL/SQL section.