Oracle Database – PL/SQL | Love Me Some Coding

PL/SQL is a full procedural language — variables, loops, exceptions, packages, its own type system — compiled and stored inside the database. It is the reason a lot of enterprise business logic lives in Oracle rather than in the application in front of it, and it is why "we'll just move off Oracle" is rarely a small project.

You do not have to like that architecture to need to read it. And there is one thing PL/SQL is unambiguously the right tool for: moving a lot of rows without shipping them to the client and back.

The anonymous block

SET SERVEROUTPUT ON     -- otherwise DBMS_OUTPUT goes nowhere

DECLARE
  v_count   PLS_INTEGER := 0;
  v_name    customers.last_name%TYPE;          -- same type as the column
  v_row     customers%ROWTYPE;                 -- same shape as the row
BEGIN
  SELECT count(*) INTO v_count FROM customers;
  dbms_output.put_line('customers: ' || v_count);

  SELECT * INTO v_row FROM customers WHERE id = 1;
  dbms_output.put_line('first: ' || v_row.last_name);
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('no customer with id 1');
END;
/

Three details that are easy to miss:

The trailing / on its own line is what tells the client to submit the block. Without it, nothing happens and you sit there wondering why.
%TYPE and %ROWTYPE anchor your variables to the table definition. Widen the column and the code still compiles. Use them everywhere.
PLS_INTEGER is a machine integer and is faster than NUMBER for loop counters. Free performance.

SELECT INTO, and its two exceptions

SELECT INTO demands exactly one row. Zero rows raises NO_DATA_FOUND; two or more raises TOO_MANY_ROWS. Both are common bugs, and both are silent in the sense that the query itself is perfectly valid.

BEGIN
  SELECT last_name INTO v_name FROM customers WHERE email = p_email;
EXCEPTION
  WHEN no_data_found  THEN v_name := NULL;
  WHEN too_many_rows  THEN raise_application_error(-20001, 'duplicate email: ' || p_email);
END;

An aggregate is a useful trick here: SELECT max(id) INTO v_id … always returns one row, so it returns NULL instead of raising. That is either exactly what you want or a bug that hides itself — decide deliberately.

Control flow

IF v_total > 1000 THEN
  v_tier := 'GOLD';
ELSIF v_total > 100 THEN
  v_tier := 'SILVER';
ELSE
  v_tier := 'BRONZE';
END IF;

CASE v_status
  WHEN 'A' THEN v_label := 'Active';
  WHEN 'C' THEN v_label := 'Closed';
  ELSE          v_label := 'Unknown';
END CASE;

FOR i IN 1 .. 10 LOOP  ... END LOOP;
FOR i IN REVERSE 1 .. 10 LOOP ... END LOOP;

WHILE v_count > 0 LOOP ... END LOOP;

LOOP
  ...
  EXIT WHEN v_done;
  CONTINUE WHEN v_skip;
END LOOP;

Note ELSIF, one E. And a CASE with no ELSE that matches nothing raises CASE_NOT_FOUND rather than doing nothing — unlike SQL's CASE, which returns NULL.

Cursors

The implicit cursor FOR loop is the form to reach for. It declares the record, opens, fetches, closes and handles the end of data, and since 10g it array-fetches 100 rows at a time automatically:

BEGIN
  FOR r IN (SELECT id, total FROM orders WHERE status = 'NEW') LOOP
    dbms_output.put_line(r.id || ': ' || r.total);
  END LOOP;
END;
/

The explicit form is only needed when you must control fetching — passing a cursor around, or fetching in batches:

DECLARE
  CURSOR c_orders (p_status VARCHAR2) IS
    SELECT id, total FROM orders WHERE status = p_status;
  v_row c_orders%ROWTYPE;
BEGIN
  OPEN c_orders('NEW');
  LOOP
    FETCH c_orders INTO v_row;
    EXIT WHEN c_orders%NOTFOUND;
    ...
  END LOOP;
  CLOSE c_orders;
END;
/

Cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN. For the last DML statement, the implicit cursor SQL carries them — SQL%ROWCOUNT after an UPDATE is how you find out how many rows you changed.

Procedures, functions, packages

CREATE OR REPLACE PROCEDURE close_order (p_order_id IN NUMBER,
                                         p_closed   OUT BOOLEAN) IS
BEGIN
  UPDATE orders SET status = 'CLOSED' WHERE id = p_order_id AND status <> 'CLOSED';
  p_closed := SQL%ROWCOUNT > 0;
END close_order;
/

CREATE OR REPLACE FUNCTION order_total (p_order_id IN NUMBER) RETURN NUMBER IS
  v_total NUMBER(12,2);
BEGIN
  SELECT sum(line_total) INTO v_total FROM order_items WHERE order_id = p_order_id;
  RETURN nvl(v_total, 0);
END order_total;
/

Parameter modes are IN (default, read-only), OUT and IN OUT. There is also NOCOPY, a hint that passes large collections by reference instead of copying them — worth knowing when a procedure takes a big array.

Packages are the unit of organisation, and unlike standalone procedures they let you separate interface from implementation:

CREATE OR REPLACE PACKAGE order_api AS
  -- Everything declared here is public.
  e_already_closed EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_already_closed, -20010);

  FUNCTION  total   (p_order_id NUMBER) RETURN NUMBER;
  PROCEDURE close_it(p_order_id NUMBER);
END order_api;
/

CREATE OR REPLACE PACKAGE BODY order_api AS

  -- Private: not in the spec, so nothing outside can call it.
  FUNCTION is_closed (p_order_id NUMBER) RETURN BOOLEAN IS
    v_status orders.status%TYPE;
  BEGIN
    SELECT status INTO v_status FROM orders WHERE id = p_order_id;
    RETURN v_status = 'CLOSED';
  END is_closed;

  FUNCTION total (p_order_id NUMBER) RETURN NUMBER IS
    v_total NUMBER(12,2);
  BEGIN
    SELECT sum(line_total) INTO v_total FROM order_items WHERE order_id = p_order_id;
    RETURN nvl(v_total, 0);
  END total;

  PROCEDURE close_it (p_order_id NUMBER) IS
  BEGIN
    IF is_closed(p_order_id) THEN
      RAISE e_already_closed;
    END IF;
    UPDATE orders SET status = 'CLOSED' WHERE id = p_order_id;
  END close_it;

END order_api;
/

Practical benefits: the body can be recompiled without invalidating anything that depends on the spec, package-level variables persist for the life of the session, and there is one namespace per subject area instead of four hundred loose procedures.

Exceptions

DECLARE
  e_bad_input EXCEPTION;
  e_dup       EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_dup, -1);    -- ORA-00001 unique constraint violated
BEGIN
  ...
EXCEPTION
  WHEN e_dup THEN
    -- handle the duplicate specifically
    NULL;
  WHEN e_bad_input THEN
    raise_application_error(-20002, 'bad input');
  WHEN OTHERS THEN
    -- Log with the full stack, then re-raise. NEVER swallow OTHERS silently.
    dbms_output.put_line(dbms_utility.format_error_stack);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
    RAISE;
END;
/

Rules worth internalising:

PRAGMA EXCEPTION_INIT binds a named exception to an Oracle error number, so you can catch ORA-00001 by name instead of testing SQLCODE.
raise_application_error takes -20000 to -20999. That range is reserved for you; anything else is Oracle's.
WHEN OTHERS THEN NULL is the worst line of code in the Oracle world. It converts every error into silent data corruption. If you catch OTHERS, log and RAISE.
format_error_backtrace gives you the line number where the error was raised. SQLERRM alone does not, which is why so many PL/SQL error logs are useless.

Bulk operations — where PL/SQL earns its keep

Row-by-row PL/SQL is slower than SQL, because every statement inside the loop is a separate context switch between the PL/SQL engine and the SQL engine. BULK COLLECT and FORALL batch those switches, and the difference is not marginal — routinely 10× or more.

DECLARE
  TYPE t_ids IS TABLE OF orders.id%TYPE;
  v_ids  t_ids;
  CURSOR c IS SELECT id FROM orders WHERE status = 'NEW';
BEGIN
  OPEN c;
  LOOP
    -- Fetch 5000 at a time. LIMIT is what keeps this bounded in memory;
    -- a bare BULK COLLECT of 50 million rows will run the PGA out of memory.
    FETCH c BULK COLLECT INTO v_ids LIMIT 5000;
    EXIT WHEN v_ids.COUNT = 0;

    FORALL i IN 1 .. v_ids.COUNT SAVE EXCEPTIONS
      UPDATE orders SET status = 'PROCESSING' WHERE id = v_ids(i);

    COMMIT;
  END LOOP;
  CLOSE c;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -24381 THEN     -- some rows in the FORALL failed
      FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
        dbms_output.put_line('row ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
                             ': '   || SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
      END LOOP;
    ELSE
      RAISE;
    END IF;
END;
/

SAVE EXCEPTIONS lets the whole batch run and collects the failures, instead of stopping on the first bad row. Without it, one constraint violation aborts 5000 updates.

But the real lesson sits above all of that: if the work can be expressed as a single SQL statement, write the SQL statement. A MERGE or an UPDATE … WHERE EXISTS beats the best-tuned bulk loop, because it never leaves the SQL engine at all. Reach for PL/SQL when you need procedural logic per row, not as a way to write loops in a database.

Autonomous transactions

Occasionally you need something to commit even though the caller will roll back — audit logging is the canonical case:

CREATE OR REPLACE PROCEDURE log_event (p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO event_log (logged_at, message) VALUES (systimestamp, p_msg);
  COMMIT;    -- mandatory: an autonomous block must end committed or rolled back
END;
/

Use it sparingly and only for logging. It is a separate transaction, so it cannot see the caller's uncommitted changes, and a careless autonomous transaction that touches the same rows as its caller will deadlock with it.

Finding what is broken

-- Compile errors after CREATE OR REPLACE said "created with compilation errors"
SHOW ERRORS
SELECT line, position, text FROM user_errors WHERE name = 'ORDER_API' ORDER BY sequence;

-- Objects that no longer compile
SELECT object_name, object_type, status FROM user_objects WHERE status = 'INVALID';

-- Source of something you did not write
SELECT text FROM user_source WHERE name = 'ORDER_API' AND type = 'PACKAGE BODY' ORDER BY line;

-- What depends on this table?
SELECT name, type FROM user_dependencies WHERE referenced_name = 'ORDERS';

-- Turn on warnings. It will find unreachable code and unhandled exceptions.
ALTER SESSION SET plsql_warnings = 'ENABLE:ALL';

Indexes and execution plans: how to find out what Oracle is actually doing with your query, and why the plan the optimiser chose is usually a story about statistics.

Oracle Database – PL/SQL