Programs and batches

-- comments run to the end of the line; /* block comments */ also work

PARAM run_date DATE;               -- program inputs (optional section)

DECLARE                            -- variables and types (optional section)
  total NUMERIC(14,2) := 0;
BEGIN
  ...                              -- the body: reads, writes, compute
EXCEPTION                          -- fault handlers (optional section)
  WHEN ZERO_DIVIDE THEN STOP;
  WHEN OTHERS      THEN RAISE;
END;

​

PARAM: program inputs

PARAM want_region TEXT;
PARAM want_date   DATE;

sumatra --dsn my.dsn @rollup.sumatra "North America" 2026-07-01

FOR batch IN (SELECT grp, SUM(amt) AS amt
              FROM sales
              WHERE region = PARAM('want_region')
                AND sale_date = CAST(PARAM('want_date') AS DATE)
              GROUP BY grp)
    BATCH SIZE ALL

• A non-TEXT parameter needs an explicit cast at the point of use: CAST(PARAM('want_date') AS DATE).
• A bare want_date in compute code is an undeclared-variable error — parameters are read-only inputs to your SQL, not variables. The one compute-side position where PARAM('name') is legal is a DECLARE initializer (cutoff NUMERIC(12,2) := PARAM('cutoff');), where it takes the declared type with no cast — the idiom for seeding a carried variable from a program input.

​

Your variables are visible to your SQL

DECLARE
  wm DATE;
BEGIN
  SELECT MAX(loaded_day) INTO wm FROM rollup_target;

  FOR batch IN (SELECT day, SUM(amt) AS amt
                FROM sales
                WHERE day > CAST(PARAM('wm') AS DATE)   -- the variable, not an argument
                GROUP BY day)
      BATCH SIZE ALL
  LOOP
    ...
  END LOOP;
  COMMIT;
END;

• The binding is per-execution: a variable that is unset or NULL at the moment a statement runs is simply absent from that statement’s binds, and referencing it then is the engine’s own loud “parameter was not set” error — never a silent NULL.
• PARAM() returns text, so a non-text variable is read back with a cast, exactly like an argument.
• A variable may not share a name with a declared PARAM or procedure argument — that collision is a compile error.

​

DECLARE: variables and carried state

DECLARE
  running       NUMERIC(14,2) := 0;
  last_customer BIGINT        := NULL;

​

The read block

FOR batch IN (SELECT ...)          -- any SELECT the engine accepts, relayed verbatim
    ORDER KEY (col1, col2)         -- strict total order (when required — see below)
    BATCH SIZE 50000               -- chunk size, or ALL
LOOP
  ...                              -- the batch body
END LOOP;

​

BATCH SIZE

• BATCH SIZE N — fetch the result in chunks of up to N rows, using keyset pagination over the ORDER KEY (the same WHERE key > last ... LIMIT N pattern you would hand-write). Each batch is processed and written as a unit, and memory stays bounded at N rows.
• BATCH SIZE ALL — one whole-set batch, evaluated once. This is the right choice for aggregating reads (a GROUP BY source re-evaluates per chunk under BATCH SIZE N) and for order-independent bodies. On the client-compute path the whole set is held in memory.

​

ORDER KEY

• BATCH SIZE N chunks the read (the key is the pagination cursor), or
• the body is order-dependent — carried state that must see rows in a specific sequence — at any batch size, or
• the loop is a writing client-compute loop.

​

Row loops and batch statements

FOR rec IN batch LOOP
  rec.total := rec.qty * rec.price;      -- rec's fields = the SELECT's columns
  UPDATE orders SET ROW = rec WHERE order_id = rec.order_id;
END LOOP;

INSERT INTO order_totals (id, total)
  SELECT id, qty * price FROM batch;

​

The batch body beyond the row loop

FOR batch IN (SELECT id, amt, ok FROM feed) ORDER KEY (id) BATCH SIZE 10000
LOOP
  batch_total := 0;                          -- per-batch reset (carried scalar)
  FOR rec IN batch LOOP
    batch_total := batch_total + rec.amt;
    UPDATE feed SET ok = TRUE WHERE id = rec.id;
  END LOOP;
  IF batch_total > 0 THEN
    COMMIT;                                  -- gated checkpoint: a batch that fails
  END IF;                                    -- its gate is discarded, not committed
END LOOP;

​

Lookups inside the batch loop

FOR batch IN (SELECT id, amt FROM feed) ORDER KEY (id) BATCH SIZE 50000
LOOP
  SELECT cutoff INTO v_cutoff FROM control WHERE k = 1;   -- re-read once per batch
  FOR rec IN batch LOOP
    ...
  END LOOP;
  COMMIT;
END LOOP;

​

Stopping a batched read early

FOR batch IN (SELECT id, delta, applied FROM changes)
    ORDER KEY (id) BATCH SIZE 10000
LOOP
  FOR rec IN batch LOOP
    seen := seen + 1;
    rec.applied := TRUE;
    UPDATE changes SET ROW = rec WHERE id = rec.id;
  END LOOP;
  COMMIT;
  EXIT WHEN seen >= 100000;      -- after the checkpoint: this batch is durable
END LOOP;

​

How writes are routed

-- Full-row write-back: writes every column except the matched key
UPDATE orders SET ROW = rec WHERE order_id = rec.order_id;

-- Sparse update: writes only the SET columns; untouched columns are preserved,
-- and the read may project just a subset of the target's columns
UPDATE accounts SET balance = rec.balance + 10 WHERE id = rec.id;

-- Append: INSERT inside a row loop takes VALUES (a record, or a column list)
INSERT INTO audit VALUES ev;
INSERT INTO installments (invoice_id, seq, amount) VALUES (rec.invoice_id, i, per);

-- Delete
DELETE FROM accounts WHERE customer_id = rec.customer_id;

• A write may not change its own key: SET of a merge-key column, or a SET ROW matched on anything but the record’s own key field, is a compile error.
• One change per key per flush: writing the same key twice in one batch is a program error. Statically visible cases are compile errors; data-dependent duplicates surface as the engine’s own MERGE cardinality error at runtime (catchable with WHEN OTHERS). Repeated DELETEs of the same key are idempotent and allowed.
• An upsert is written as its two natural statements — an accumulate UPDATE plus a complement INSERT ... WHERE key NOT IN (...) — and both run server-side; together they behave as the upsert.

​

COMMIT: placement is meaning

-- Per-batch grain: each batch commits as soon as it is written.
FOR batch IN (...) ORDER KEY (id) BATCH SIZE 10000 LOOP
  FOR rec IN batch LOOP ... END LOOP;
  COMMIT;                       -- durable checkpoint per batch
END LOOP;

-- At-end grain: nothing is durable unless the whole program succeeds.
FOR batch IN (...) ORDER KEY (id) BATCH SIZE 10000 LOOP
  FOR rec IN batch LOOP ... END LOOP;
END LOOP;
COMMIT;                         -- one all-or-nothing transaction

-- Per-iteration grain: inside a top-level pipeline loop, each
-- iteration's COMMIT closes that iteration's work (see Pipelines).
FOR d IN 1 .. 31 LOOP
  ...                           -- reads and writes for one slice
  COMMIT;
END LOOP;

• A write with no COMMIT reachable downstream on any path is a compile error — there is no implicit commit to fall back on. (A COMMIT that exists but sits behind a runtime gate is legal — see below.)
• COMMIT cannot appear inside a row loop, or inside a counted/WHILE loop in compute position — per-row commits are inexpressible by grammar. At the top level of a pipeline loop it is the per-iteration grain.
• A server-side read at BATCH SIZE N must commit in the batch body, unconditionally — that COMMIT is each chunk’s transaction bracket.

​

How top-level statements share a transaction

• An IF/CASE is spanned as plain control flow — the arms inherit the transaction state, and writes and COMMIT are legal inside an arm. A gated commit (IF ok THEN COMMIT; END IF;) is a valid construct: on the run where the gate is false, the tail of the transaction simply rolls back at END — the same discard an early RETURN has always meant. Lookups in an arm run zero times or once, and a miss behind a false gate is not a fault.
• A batch loop that starts while a transaction is open joins it on batch 1: the first batch’s COMMIT commits the pending write(s) together with that batch, and later batches bracket per batch as usual. One consequence to know: if the read matches zero rows, the loop’s COMMIT never fires — a pending earlier write then rolls back at END unless a later explicit COMMIT commits it.

​

Pipelines: repeating a read-compute-write

-- Settle each month of the year in its own transaction.
BEGIN
  FOR m IN 1 .. 12 LOOP          -- m is the loop's own variable (a declared
                                 -- variable of the same name would collide)
    FOR batch IN (SELECT id, amt, settled, pass
                  FROM settlements
                  WHERE month_no = CAST(PARAM('m') AS INT)   -- the loop variable, bound per iteration
                    AND settled = FALSE)
        ORDER KEY (id) BATCH SIZE 20000
    LOOP
      FOR rec IN batch LOOP
        rec.settled := TRUE;
        rec.pass    := m;
        UPDATE settlements SET ROW = rec WHERE id = rec.id;
      END LOOP;
      COMMIT;                    -- per-batch inside the iteration
    END LOOP;
  END LOOP;
END;

• The loop variable is visible to the body’s SQL as PARAM('<var>') — bound per iteration like any published variable. It may not collide with a declared parameter or variable name.
• Loop bounds re-evaluate every iteration, and a WHILE pipeline’s predicate is over carried variables — which a per-batch or top-level lookup can update, so “repeat until the data says done” is written as a WHILE over a value the program re-reads each pass.
• A COMMIT in the pipeline body is the per-iteration grain: it closes that iteration’s transaction (and drains that iteration’s staged write-backs). An at-end read whose staged write-back would only be committed by a later iteration is a compile error — each iteration settles its own work.
• EXIT / CONTINUE at pipeline level bind to the pipeline loop — the innermost-loop rule, unchanged.
• The grain is honest: N iterations mean N read-compute-write passes — the bound is the program’s own (a range or a predicate over carried state), never a per-row server call.

​

Dynamic table names

PARAM tgt daily_rollup%TABLE;

FOR batch IN (SELECT id, amt FROM PARAM('tgt') WHERE day = CAST(PARAM('d') AS DATE))
    ORDER KEY (id) BATCH SIZE 20000
LOOP
  FOR rec IN batch LOOP
    ...
    UPDATE PARAM('tgt') SET amt = rec.amt WHERE id = rec.id;
  END LOOP;
  COMMIT;
END LOOP;

• The runtime name must be a plain identifier (lowercase word characters, at most 128 bytes). Quotes, spaces, dots, and semicolons are unspellable — a dynamic name can be anything a static name could have been, and nothing more, which closes off injection by construction.
• At run start the actual table’s shape is checked against the reference table’s compiled shape; a drifted table is refused, never run against stale assumptions.
• A name parameter may appear in the opaque read’s FROM/JOIN and as the target of a row loop’s captured UPDATE/INSERT/DELETE. Everywhere else — a value position, a top-level set-statement target — it is a compile error.

​

What the compiler manages in your database

• Staging tables — client-computed changes land in a per-run scratch table named sumatra_stage_... before the MERGE applies them. They are created, truncated, and dropped automatically; a crashed run’s orphan is reclaimed by the next run of the same program. A companion sumatra_stage_lock_... marker refuses two concurrent runs of the same program with the same parameters.
• sumatra_source — the stored-procedure catalog table (only created once you store a procedure).

​

Program lifecycle