PL/SQL Cheat Sheet

Every PL/SQL pattern you reach for daily, on one page: block structure, cursors, exception handling, procedures/functions/packages, triggers, collections, and the BULK COLLECT/FORALL performance pattern. Bookmark it, or work through the underlying concepts in the free PL/SQL tutorial.

Block structure

Every PL/SQL block has the same three parts; DECLARE and EXCEPTION are optional.

DECLARE
  v_salary NUMBER;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_salary);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such employee');
END;
/

Common data types

Control structures

Cursors

An implicit cursor (SQL%...) is created automatically for every DML statement; an explicit cursor gives you control over a multi-row query.

Exception handling

BEGIN
  ...
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001, 'Unexpected: ' || SQLERRM);
END;

Procedures & functions

A function must return exactly one value and can be called from inside a SQL expression; a procedure performs an action and is invoked with CALL/EXEC.

CREATE OR REPLACE PROCEDURE give_raise(p_emp_id IN NUMBER, p_pct IN NUMBER) IS
BEGIN
  UPDATE employees SET salary = salary * (1 + p_pct/100) WHERE employee_id = p_emp_id;
END;
/

CREATE OR REPLACE FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER IS
  v_salary NUMBER;
BEGIN
  SELECT salary * 12 INTO v_salary FROM employees WHERE employee_id = p_emp_id;
  RETURN v_salary;
END;
/

Packages

A package groups related procedures, functions, and variables behind a public spec, hiding the implementation in the body.

CREATE OR REPLACE PACKAGE emp_pkg IS
  PROCEDURE give_raise(p_emp_id NUMBER, p_pct NUMBER);
  FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER;
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  PROCEDURE give_raise(p_emp_id NUMBER, p_pct NUMBER) IS BEGIN
    UPDATE employees SET salary = salary * (1 + p_pct/100) WHERE employee_id = p_emp_id;
  END;
  FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER IS
    v_salary NUMBER;
  BEGIN
    SELECT salary * 12 INTO v_salary FROM employees WHERE employee_id = p_emp_id;
    RETURN v_salary;
  END;
END emp_pkg;
/

Triggers

CREATE OR REPLACE TRIGGER trg_salary_audit
  BEFORE UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_on)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

Collections

TYPE t_salary_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_salaries t_salary_tab;
v_salaries(1) := 5000;

BULK COLLECT & FORALL

Row-by-row PL/SQL loops over SQL are slow because of the repeated context switch between the SQL and PL/SQL engines. BULK COLLECT and FORALL batch that switch into one round trip.

DECLARE
  TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
  v_ids t_ids;
BEGIN
  SELECT employee_id BULK COLLECT INTO v_ids FROM employees WHERE department_id = 60;

  FORALL i IN v_ids.FIRST .. v_ids.LAST
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_ids(i);
END;
/

Dynamic SQL

Use EXECUTE IMMEDIATE when the table/column name or statement shape isn't known until runtime. Always bind values with USING instead of concatenating them -- concatenation is the classic SQL-injection mistake.

EXECUTE IMMEDIATE 'SELECT salary FROM ' || p_table_name || ' WHERE employee_id = :1'
  INTO v_salary USING p_emp_id;