Part 7 — What the market actually runs › Chapter 45

Procedural SQL: T-SQL and PL/SQL

Variables, control flow, cursors, error handling and packages — the two procedural dialects adverts name.

From the advert

“Esperienza in sviluppo PL/SQL a livello junior” — “a livello junior” means they expect you to read and modify packages, not architect them. That is a much smaller ask than it sounds.

Why procedural SQL exists

🧒 Simple

SQL is very good at saying what you want. It has no way of saying “try this, and if it goes wrong do that instead, and keep a note of how many times you tried”. The procedural dialects add that — and the first rule of using them is to use as little of them as possible.

✔ The rule that keeps procedural code fast

Write set-based SQL, and use procedural code only for control flow around it. A loop that processes one row per iteration is the commonest reason a stored procedure is slow (chapter 29). Variables, IF, error handling and transactions are the legitimate uses; iteration almost never is.

T-SQL, the shape you will meet

A complete T-SQL procedure: concurrency, the state machine, three distinct failures and an outbox — all in one transaction.

CREATE OR ALTER PROCEDURE dbo.usp_ShipOrder   -- OR ALTER: keeps existing permissions
  @order_id INT,                              -- which order…
  @version  INT,                              -- …and which version the caller read
  @actor    SYSNAME = NULL                    -- optional: defaults to the login below
AS                                            -- the body follows
BEGIN                                          -- open the procedure body
  SET NOCOUNT ON;              -- stop "n rows affected" messages confusing the caller
  SET XACT_ABORT ON;           -- ON ERROR, ROLL BACK THE WHOLE TRANSACTION.
  --  ^ Without this, some errors abort only the STATEMENT and leave the transaction
  --    open and half-applied. It is the single most important line in the file.

  DECLARE @now DATETIME2(3) = SYSUTCDATETIME();      -- one timestamp for every row written
  SET @actor = COALESCE(@actor, SUSER_SNAME());      -- caller's name, or the login

  BEGIN TRY                                          -- everything below is guarded
    BEGIN TRANSACTION;                               -- the unit of work opens here

      UPDATE dbo.orders                             -- the state change itself
      SET status = 'shipped', shipped_at = @now,     -- the change…
          version = version + 1                      -- …and the optimistic-lock bump
      WHERE id = @order_id                    -- the row, by key…
        AND version = @version              -- optimistic concurrency, chapter 24
        AND status  = 'confirmed';          -- and the state machine, chapter 04

      IF @@ROWCOUNT = 0                     -- read it IMMEDIATELY: the next statement resets it
      BEGIN                                    -- …then work out WHICH failure it was
        -- Distinguish "gone", "changed under us" and "wrong state" — the caller
        -- needs different behaviour for each, so do not return one vague error.
        IF NOT EXISTS (SELECT 1 FROM dbo.orders WHERE id = @order_id)   -- does it exist at all?
          THROW 50404, 'Order not found.', 1;                           -- → 404
        THROW 50409, 'Order was modified or is not in a shippable state.', 1;  -- → 409
      END                                      -- end of the failure branch

      INSERT INTO dbo.order_events (order_id, from_status, to_status, at, actor)  -- the audit…
      VALUES (@order_id, 'confirmed', 'shipped', @now, @actor);                   -- …same @now

      INSERT INTO dbo.outbox (topic, payload, created_at)               -- the message to publish
      VALUES ('order.shipped',                             -- the topic…
              (SELECT @order_id AS orderId                              -- build the JSON…
               FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),                   -- …as one object
              @now);                                              -- …and the same timestamp again
      --      ^ the outbox write is in the SAME transaction. Chapter 24.

    COMMIT TRANSACTION;                     -- all three writes become permanent together
  END TRY                                     -- end of the guarded section
  BEGIN CATCH                                  -- the handler
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;   -- check the count; see chapter 21
    THROW;                                     -- rethrow PRESERVING the original error
  END CATCH                                   -- end of the handler
END                                        -- end of the procedure

The five decisions worth defending in an interview

SET XACT_ABORT ON. Without it a runtime error can abort the statement and leave the transaction open, so the connection returns to the pool holding locks. It is one line and it prevents a category of incident.
Three different failures, three different errors. “Not found” is a 404, “version moved” is a 409, “wrong state” is a 409 with a different message. Returning one vague error makes the caller guess.
@@ROWCOUNT is read on the very next line, because anything — including an IF — resets it. This is the single most common T-SQL bug of its kind.
One @now for every row written, so the order, the audit row and the outbox message agree about when it happened. Calling SYSUTCDATETIME() three times gives three timestamps.
THROW; with no arguments re-raises the original error with its number and line intact. THROW 50000, ERROR_MESSAGE(), 1 loses all of that, and RAISERROR loses the severity.

⚠ Three T-SQL traps in that one procedure

Without SET XACT_ABORT ON, several classes of error abort only the current statement. The CATCH block runs with a transaction still open and partially applied — which is how "it half-worked" incidents happen.
THROW, not RAISERROR. Bare THROW inside a CATCH rethrows the original error with its number, message and line. RAISERROR cannot do that: it substitutes its own, and the real cause is lost.
@@ROWCOUNT must be read immediately. Any statement in between — including an IF — resets it. Capture it into a variable on the very next line if you need it twice.

PL/SQL, the same thing in Oracle

The same procedure in Oracle — and the one thing PL/SQL genuinely has that T-SQL does not.

CREATE OR REPLACE PACKAGE orders_pkg AS       -- the SPEC: the public interface
  -- Anything not declared here is private to the body — real encapsulation, and
  -- the thing PL/SQL genuinely has that T-SQL does not.
  PROCEDURE ship_order(p_order_id IN NUMBER,  -- IN: an input parameter…
                       p_version  IN NUMBER); -- …declared, not defined, here
  e_conflict EXCEPTION;                       -- a named exception, part of the interface
  PRAGMA EXCEPTION_INIT(e_conflict, -20409);  -- bind it to an error number
END orders_pkg;                                -- end of the spec
/                                             -- SQL*Plus statement terminator, not SQL

CREATE OR REPLACE PACKAGE BODY orders_pkg AS  -- the BODY: the implementation
  PROCEDURE ship_order(p_order_id IN NUMBER, p_version IN NUMBER) IS  -- the definition, matching the spec
  BEGIN                                        -- the implementation begins
    UPDATE orders                                    -- the same conditional update as before
       SET status = 'shipped', shipped_at = SYSTIMESTAMP,   -- Oracle's "now", with a zone
           version = version + 1                            -- the optimistic-lock bump
     WHERE id = p_order_id AND version = p_version          -- concurrency check…
       AND status = 'confirmed';                            -- …and state check

    IF SQL%ROWCOUNT = 0 THEN                 -- the PL/SQL @@ROWCOUNT
      RAISE_APPLICATION_ERROR(-20409, 'Order modified or not shippable');  -- a 409
    END IF;                                     -- end of the failure branch

    INSERT INTO order_events (order_id, from_status, to_status)  -- the audit row
    VALUES (p_order_id, 'confirmed', 'shipped');    -- the audit values
    COMMIT;                                  -- Oracle does NOT autocommit: say it explicitly
  EXCEPTION                                  -- the handler section
    WHEN OTHERS THEN                         -- any error at all…
      ROLLBACK;                              -- …undo…
      RAISE;                                 -- …and rethrow, preserving the stack
  END ship_order;                              -- end of the procedure
END orders_pkg;                              -- end of the package body
/                                            -- send it to the server

What differs from the T-SQL version, beyond spelling

Spec and body are separate objects. Only what the spec declares is callable, so a package is a genuine module with private helpers. T-SQL has schemas and permissions, which is not the same thing.
SQL%ROWCOUNT is an attribute of the implicit cursor — the statement just executed. Same idea as @@ROWCOUNT, same warning about reading it immediately.
Oracle does not autocommit, so COMMIT is explicit here. In SQL Server the same procedure without BEGIN TRANSACTION would commit each statement separately, which is a different program.
WHEN OTHERS THEN … RAISE; is acceptable because it rethrows. A WHEN OTHERS that swallows the error is the most-criticised pattern in PL/SQL, and for good reason.
The / is not SQL: it tells SQL*Plus to send the block. A missing one produces a confusing error about an incomplete statement.

Concept	T-SQL	PL/SQL
Declare	`DECLARE @x INT`	`x NUMBER;` in the DECLARE section
Assign	`SET @x = 1`	`x := 1;`
Rows affected	`@@ROWCOUNT`	`SQL%ROWCOUNT`
Error handling	`BEGIN TRY … BEGIN CATCH`	`EXCEPTION WHEN … THEN`
Raise	`THROW`	`RAISE_APPLICATION_ERROR` (-20000 to -20999)
Row type	Table variable	`%ROWTYPE` and `%TYPE` — genuinely nice
Grouping	Schemas only	Packages, with a public spec and a private body
Set-based iteration	Just write the SQL	`FORALL` and `BULK COLLECT`

🎓 The two PL/SQL features worth knowing by name

BULK COLLECT / FORALL — the fix when a cursor loop is genuinely unavoidable. It moves rows between SQL and PL/SQL in batches instead of one at a time, eliminating the per-row context switch. Order-of-magnitude improvements are routine, and it is the first thing to reach for in slow legacy PL/SQL.
%TYPE and %ROWTYPE — declare a variable as "whatever type that column is". The declaration follows the schema automatically, so widening a column does not silently truncate in a procedure nobody remembered.

Cursors, and the honest use for them

If you must write a cursor, declare it cheaply — and write down why.

-- If you must, at least tell the engine what you are doing:
DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ...;  -- the cheapest cursor there is
--            ^ local scope  ^ forward-only, read-only: far cheaper than the default

-- But first ask: could this be one statement? It usually could.
-- The legitimate cases:
--   * each row requires a separate EXTERNAL action (a file, an API call)
--   * you are deliberately batching a huge update to avoid lock escalation
-- And in both cases, write the reason in a comment — because the default
-- assumption on reading a cursor is that the author did not know better.

What those keywords actually save

The default cursor is updatable and scrollable, which means the engine keeps a keyset and supports moving backwards. FAST_FORWARD READ_ONLY gives up both and is dramatically cheaper.
LOCAL scopes it to the batch. A GLOBAL cursor — the default in some configurations — outlives the procedure and can collide with another one of the same name.
None of that changes the real cost: one statement per row, each with its own plan lookup, lock and log record. Two to three orders of magnitude slower than the set-based version is normal.
The comment is part of the recommendation. A cursor with a stated reason is a decision; a cursor without one is a smell.

Reading somebody else's procedure

✔ A five-minute order of attack

The signature. Parameters in, parameters out, return value. That is the contract.
Transaction boundaries. Where does it begin and commit, and is there a path that leaves one open?
The error paths. What happens on failure, and is the original error preserved or swallowed?
Loops and cursors. Anything row-at-a-time is where the time goes.
Only then, the business logic. Which is usually the part that is actually fine.

Set-based SQL for the work, procedural code only for control flow around it.
SET XACT_ABORT ON in every T-SQL procedure with a transaction.
THROW rethrows the original error; RAISERROR replaces it.
@@ROWCOUNT is reset by the next statement. Capture it immediately.
Check @@TRANCOUNT before rolling back in a CATCH.
Distinguish “not found” from “changed under us” from “wrong state” — the caller needs to.
Packages give PL/SQL real encapsulation: a public spec and a private body.
BULK COLLECT and FORALL are the fix for an unavoidable PL/SQL loop.
%TYPE keeps a variable in step with its column.
A cursor needs LOCAL FAST_FORWARD READ_ONLY and a comment explaining itself.

Interview questions this chapter answers

How do you handle errors in a stored procedure?

In T-SQL: SET XACT_ABORT ON, a TRY block containing the transaction, and a CATCH that rolls back if @@TRANCOUNT is non-zero and then bare-THROWs to rethrow the original error with its number and line. In PL/SQL it is an EXCEPTION block with RAISE to rethrow. The two things I care about are not leaving a transaction open and not losing the original error.

What is a package in PL/SQL?

A named container with a public specification and a private body. Anything not declared in the spec is invisible outside, so it gives real encapsulation and a versionable interface — which is the main structural thing PL/SQL has that T-SQL does not.

You inherit a procedure that takes eight minutes. Where do you look first?

For a cursor or a WHILE loop doing row-at-a-time work, because that is usually the whole answer. Then for scalar functions inside a predicate, and for a table variable being joined to — both of which give the optimiser no useful estimate. The business logic is normally fine; the shape around it is not.