Fast Guide: PL/SQL

PL/SQL is Oracle’s procedural extension to SQL. It lets you combine SQL statements with variables, conditions, loops, reusable subprograms, and exception handling. This quick guide covers the pieces you will use most often.

1. The basic PL/SQL block

DECLARE
  l_message VARCHAR2(100) := 'Hello from PL/SQL';
BEGIN
  DBMS_OUTPUT.PUT_LINE(l_message);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/

A block has an optional declaration section, a required executable section, and an optional exception section. The trailing slash is a client command used by tools such as SQL*Plus and SQLcl to submit the completed block.

2. Variables and SELECT INTO

DECLARE
  l_last_name employees.last_name%TYPE;
BEGIN
  SELECT last_name
    INTO l_last_name
    FROM employees
   WHERE employee_id = 100;

  DBMS_OUTPUT.PUT_LINE(l_last_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Expected one employee, but found several');
END;
/

Using %TYPE keeps a variable aligned with the datatype of a table column. A scalar SELECT INTO must return exactly one row; handle NO_DATA_FOUND and TOO_MANY_ROWS when either result is possible.

3. Procedures and functions

A procedure performs an action. A function returns a value. Group related procedures, functions, types, and constants in a package so callers have a stable interface and implementation details remain private.

CREATE OR REPLACE FUNCTION annual_salary (
  p_monthly_salary IN NUMBER
) RETURN NUMBER
IS
BEGIN
  RETURN p_monthly_salary * 12;
END;
/

4. Practical habits

• Let SQL do set-based work whenever possible; avoid row-by-row processing when one SQL statement will do.
• Handle only exceptions you understand. Log useful context, and re-raise unexpected errors.
• Use bind variables and strongly typed parameters.
• For large batches, consider BULK COLLECT and FORALL to reduce SQL-to-PL/SQL context switches.
• Keep transaction control at a clear application boundary; do not scatter commits through reusable procedures.

Reference

Oracle documents the language structure, exceptions, subprograms, packages, and performance features in the PL/SQL Language Reference.