Lesson 4 of 5

Stored Procedures & T-SQL vs PL/SQL

T-SQL and PL/SQL do the same job — procedural logic wrapped around SQL — but structure it differently. T-SQL has no block delimiters around the whole procedure body and no package concept at all — it uses schemas for the organizational job. PL/SQL wraps everything in an explicit DECLARE/BEGIN/END block and groups related procedures into packages.

💡 The Bottom Line

A T-SQL procedure just starts running statements after AS — variables are declared inline with DECLARE @var TYPE wherever you need them, no separate declaration section required. A PL/SQL procedure is a DECLARE/BEGIN/END; block with variables declared up front, before any executable code runs. SQL Server organizes procedures using schemas (sales.GetOrder, hr.GetEmployee); Oracle groups related procedures into a named PACKAGE instead — a genuinely different organizational unit with no direct T-SQL equivalent.

Basic procedure structure

SQL Server T-SQL (what you know)

CREATE OR ALTER PROCEDURE hr.GiveRaise
  @EmpId INT,
  @Pct   DECIMAL(5,2)
AS
BEGIN
  DECLARE @OldSalary DECIMAL(10,2);

  SELECT @OldSalary = salary
  FROM employees
  WHERE employee_id = @EmpId;

  UPDATE employees
  SET salary = @OldSalary * (1 + @Pct/100)
  WHERE employee_id = @EmpId;
END;

Oracle PL/SQL (the new equivalent)

CREATE OR REPLACE PROCEDURE give_raise (
  p_emp_id IN NUMBER,
  p_pct    IN NUMBER
) AS
  v_old_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_old_salary
  FROM employees
  WHERE employee_id = p_emp_id;

  UPDATE employees
  SET salary = v_old_salary * (1 + p_pct/100)
  WHERE employee_id = p_emp_id;

  COMMIT;
END give_raise;
/

📋 Key differences

T-SQL parameters are always prefixed with @ (@EmpId) — and that prefix applies to every local variable too. PL/SQL parameters are plain names (p_emp_id), conventionally prefixed p_ by team convention rather than by the language itself, and there's no sigil requirement at all.
T-SQL has no separate declare section — DECLARE @OldSalary DECIMAL(10,2); is just an executable statement that can appear anywhere before first use. PL/SQL separates DECLARE (variables, declared once, up front) from BEGIN (executable code) — you can't declare a new variable partway through the executable section.
T-SQL's SELECT @variable = column ... assigns one row into a variable — the assignment sits to the left of =. PL/SQL's equivalent is SELECT ... INTO variable, which reads more naturally but uses the word INTO for something entirely different than T-SQL's SELECT ... INTO (see the gotcha below).
PL/SQL's %TYPE lets a variable inherit a column's exact data type (employees.salary%TYPE) so it stays in sync automatically if the column's type ever changes — genuinely useful, and something T-SQL has no direct equivalent for; you declare the variable's type explicitly in T-SQL and keep it manually in sync.
SQL Server's default autocommit behavior (every statement commits immediately) is covered in the next lesson — Oracle's explicit COMMIT/ROLLBACK model, shown above, changes when you need to think about transaction boundaries at all.

⚠️ Gotcha

PL/SQL's SELECT column INTO variable FROM ... looks exactly like T-SQL's SELECT ... INTO new_table FROM ... — but T-SQL's version creates a brand new table from a query's results, it doesn't assign anything to a variable. If you try to write PL/SQL-style variable assignment using a T-SQL mental model (SELECT salary INTO v_old_salary FROM employees ...) in an actual PL/SQL block, it works correctly — but if a former T-SQL developer instead reaches for SELECT salary INTO @OldSalary FROM ... in T-SQL thinking it reads the same way, that's a syntax error, not a quiet no-op, because INTO there expects a table name, not a variable.

Schemas vs packages

SQL Server (what you know)

CREATE PROCEDURE hr.GiveRaise
  @EmpId INT, @Pct DECIMAL(5,2) AS ...;

CREATE FUNCTION hr.GetSalary(@EmpId INT)
  RETURNS DECIMAL(10,2) AS ...;

-- called as:
EXEC hr.GiveRaise @EmpId = 100, @Pct = 5;

Oracle (the new equivalent)

CREATE OR REPLACE PACKAGE hr_pkg AS
  PROCEDURE give_raise(p_emp_id NUMBER, p_pct NUMBER);
  FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;
END hr_pkg;
/

-- called as:
hr_pkg.give_raise(100, 5);

📋 Key differences

SQL Server's organizational unit is the schema (like a namespace): hr.GiveRaise, hr.GetSalary, sales.CreateOrder. It groups objects for permissions and naming, but each object is still created, altered, and dropped independently. A PL/SQL package is a fundamentally different unit: it bundles related procedures/functions — plus private helper procedures and package-level shared state — under one name, with a separate spec (the public interface) and body (the implementation), redeployed together.
Calling a procedure uses EXEC (or EXECUTE) in T-SQL, versus calling it directly by name in Oracle — either standalone or, far more commonly in real Oracle codebases, qualified by its package name as shown above.
T-SQL has no equivalent to package-level private state (variables declared in a package body, shared across calls in the same session) — the closest T-SQL substitutes are temp tables, table variables, or session-scoped context info, each with different lifetimes and none quite matching Oracle's package-state model.
T-SQL also has no equivalent to a package's "private" procedures — every T-SQL procedure is independently callable by anyone with permission; Oracle can hide helper procedures/functions entirely from callers by simply not listing them in the package spec.

🔧 Try It Yourself

Take a small T-SQL stored procedure you've written that uses SELECT @var = col FROM ... to read one row and updates it. Rewrite it as a PL/SQL procedure: drop every @ prefix, move all variable declarations up into a DECLARE section before BEGIN, switch to SELECT col INTO var, try anchoring one variable's type with %TYPE instead of an explicit type, and add an explicit COMMIT at the end.

🙋 There Are No Dumb Questions

Q: If Oracle has packages for grouping and hiding helper logic, does that mean T-SQL's schema-only model is worse?
A: It's a real trade-off, not a strict downgrade. Oracle packages give you genuine encapsulation — private procedures a caller literally cannot invoke — plus package-level state that persists for a session. T-SQL trades that away for simplicity: every procedure is its own standalone object, always independently callable, with no hidden implementation layer to reason about. If you're used to hiding helper logic inside a package body, the T-SQL equivalent is schema-level permissions plus naming convention (e.g. an internal schema, or an _internal suffix) — a convention, not an enforced language feature.

✅ Quick Check

You're converting SELECT @Salary = salary FROM employees WHERE employee_id = 100; from T-SQL to PL/SQL. What's the correct PL/SQL equivalent?

Next up: the small surprises that bite even careful developers — empty strings, autocommit behavior, and renamed date functions.

← Back to