Skip to content
Tutorials Center
My learning

Topics

Lesson 97 of 125 ยท Database Systems

Stored Procedures and Functions

Learn stored procedures and user-defined functions in PostgreSQL, MySQL and SQL Server, with parameters, variables, loops, cursors and errors, plus SQLite alternatives.

So far, every statement you've written has been a single declarative command: "give me these rows", "change those rows". Most server databases also let you store programs inside the database: named blocks of code with parameters, variables, IF statements, loops and error handling. A stored procedure performs an action (you CALL or EXEC it), and a user-defined function computes and returns a value that you can use inside queries.

Every major server database has its own procedural language: PL/pgSQL in PostgreSQL, the SQL/PSM-based language in MySQL and MariaDB, T-SQL in SQL Server and PL/SQL in Oracle. They look different, but the ideas are the same. SQLite has no stored procedures at all, so in this lesson the procedural code is read-only, and each idea is paired with the way SQLite apps solve the same problem: with application code, CTEs, views and triggers you can run right here.

Why put code in the database?

Stored procedures have real benefits:

  • One place for business rules. A transfer_funds procedure guarantees that every app, script and report moves money the same way.
  • Fewer round trips. A procedure that runs ten statements makes one network call instead of ten.
  • Security. You can let an app execute a procedure without giving it direct access to the tables underneath (more in Users, Roles and Permissions).

They also have costs. Procedural SQL is harder to test, debug, review and version than application code. It ties you to one database vendor. And database servers are usually the hardest part of a system to scale, so heavy computation there can become a bottleneck. Many modern teams keep logic in the application and use procedures only where they clearly win: data-heavy batch jobs, strict invariants and legacy integration.

Functions vs procedures

Function Procedure
How you run it Inside a query: SELECT order_total(42); As a statement: CALL restock(5, 20); (EXEC in SQL Server)
Returns A value, or a table (table-valued function) Nothing, or values through OUT parameters / result sets
Can change data Discouraged (and often forbidden) Yes, that's the usual purpose
Transaction control No Often yes (COMMIT inside the procedure)

A first function in PostgreSQL

The simplest PostgreSQL function is written in plain SQL. This one computes the total of an order from the shop's order_items table:

SQL
-- PostgreSQL 14+: SQL-standard function body
CREATE OR REPLACE FUNCTION order_total(p_order_id integer)
RETURNS numeric
LANGUAGE sql
STABLE
RETURN (
  SELECT sum(quantity * unit_price)
  FROM order_items
  WHERE order_id = p_order_id
);

SELECT id, order_total(id) AS total
FROM orders
WHERE customer_id = 5;

STABLE tells the planner that the function doesn't modify data and returns the same result for the same input within one statement; IMMUTABLE (same result forever, like a math formula) and VOLATILE (the default, anything goes) are the other choices. The p_ prefix on parameters is a common habit that avoids confusing parameters with column names.

SQLite can't store functions, but you can get most of the benefit in two ways: a view stores a reusable query, and a CTE at the top of a query acts like a parameter list:

SQL A view and a parameter CTE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Customer 5 has seven orders, with totals from 76 to 506. To check another customer, you change one number in params. In real apps, the application passes that value as a bound parameter instead, which is also how you prevent SQL injection.

PL/pgSQL: variables, IF and errors

For anything beyond one query, PostgreSQL uses PL/pgSQL. The body is a string, usually written between $$ dollar quotes so you don't have to escape single quotes inside it. Here is a money transfer as a procedure (CREATE PROCEDURE exists since PostgreSQL 11):

SQL
-- PostgreSQL
CREATE OR REPLACE PROCEDURE transfer(
  p_from integer,
  p_to integer,
  p_amount numeric
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_balance numeric;
BEGIN
  IF p_amount <= 0 THEN
    RAISE EXCEPTION 'Amount must be positive, got %', p_amount;
  END IF;

  -- Lock the source row so two transfers can't spend the same money
  SELECT balance INTO v_balance
  FROM accounts
  WHERE id = p_from
  FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Account % does not exist', p_from;
  END IF;

  IF v_balance < p_amount THEN
    RAISE EXCEPTION 'Insufficient funds: balance %, requested %', v_balance, p_amount;
  END IF;

  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Account % does not exist', p_to;
  END IF;
END;
$$;

CALL transfer(1, 2, 50);

A few things to notice. DECLARE introduces variables. SELECT ... INTO stores a query result in a variable, and the special variable FOUND tells you whether the last statement touched any row. RAISE EXCEPTION aborts with an error, and because the whole CALL runs as one statement, everything it did is rolled back. The % placeholders in the message are filled from the extra arguments.

Doing the same in SQLite

Without procedures, a SQLite app runs the same steps from application code inside a transaction. You can push much of the checking into the database itself: a CHECK constraint guarantees no balance goes negative, and a guarded UPDATE only moves money when it's there:

SQL A transfer as a transaction

Press Run (or Ctrl + Enter) to see the result here.

Amina ends with 180 and Bruno with 160. In the application, you'd check that the first UPDATE changed exactly one row (the database driver reports this) and roll back if it didn't. Here's how that looks in Python with the built-in sqlite3 module:

Python
import sqlite3

def transfer(conn: sqlite3.Connection, from_id: int, to_id: int, amount: int) -> None:
    if amount <= 0:
        raise ValueError("Amount must be positive")
    with conn:  # BEGIN ... COMMIT, or ROLLBACK if an exception escapes
        cur = conn.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
            (amount, from_id, amount),
        )
        if cur.rowcount != 1:
            raise ValueError("Insufficient funds or unknown account")
        cur = conn.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            (amount, to_id),
        )
        if cur.rowcount != 1:
            raise ValueError("Unknown target account")

That function is your "stored procedure": same logic, but versioned, tested and deployed with the rest of the app. See Transactions for why the BEGIN/COMMIT pair matters.

MySQL procedures and the DELIMITER trick

MySQL procedures use BEGIN ... END blocks with semicolons inside. Because the mysql client normally sends a statement at every semicolon, you temporarily change the client's delimiter so the whole procedure arrives in one piece. DELIMITER is a client command, not SQL; GUI tools and application drivers don't need it.

SQL
-- MySQL / MariaDB
DELIMITER //

CREATE PROCEDURE restock(
  IN p_product_id INT,
  IN p_amount INT,
  OUT p_new_stock INT
)
BEGIN
  IF p_amount <= 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Amount must be positive';
  END IF;

  UPDATE products
  SET stock = stock + p_amount
  WHERE id = p_product_id;

  SELECT stock INTO p_new_stock
  FROM products
  WHERE id = p_product_id;
END //

DELIMITER ;

CALL restock(5, 20, @stock);   -- @stock is a session variable
SELECT @stock;

Parameters are IN (the default), OUT (written by the procedure) or INOUT (both). SIGNAL SQLSTATE '45000' raises a custom error; 45000 is the generic "user-defined exception" code. A MySQL function is declared with RETURNS and should state its characteristics:

SQL
-- MySQL: one statement, so no DELIMITER needed
CREATE FUNCTION price_with_tax(p_price DECIMAL(10, 2), p_rate DECIMAL(5, 4))
RETURNS DECIMAL(10, 2)
DETERMINISTIC NO SQL
RETURN ROUND(p_price * (1 + p_rate), 2);

SELECT name, price_with_tax(price, 0.2000) AS gross_price FROM products;

With binary logging enabled (the default), MySQL refuses to create a function unless you declare it DETERMINISTIC, NO SQL or READS SQL DATA, because replicas must be able to replay it safely.

SQLite's answer to SIGNAL is a trigger that calls RAISE(). The rule then applies to every write, no matter which code path performs it:

SQL A trigger instead of SIGNAL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The restock works (stock becomes 20), and the last statement fails on purpose with Error: Stock cannot go below zero. Triggers covers BEFORE, AFTER and INSTEAD OF triggers in depth.

T-SQL procedures in SQL Server

T-SQL variables start with @, and procedures can use TRY ... CATCH blocks. This is the transfer again, written the way experienced SQL Server developers do it:

SQL
-- SQL Server
CREATE OR ALTER PROCEDURE dbo.transfer
  @from_id INT,
  @to_id INT,
  @amount DECIMAL(12, 2)
AS
BEGIN
  SET NOCOUNT ON;     -- don't send "N rows affected" messages
  SET XACT_ABORT ON;  -- any error rolls back the whole transaction

  IF @amount <= 0
    THROW 50001, 'Amount must be positive.', 1;

  BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.accounts
    SET balance = balance - @amount
    WHERE id = @from_id AND balance >= @amount;

    IF @@ROWCOUNT = 0
      THROW 50002, 'Insufficient funds or unknown account.', 1;

    UPDATE dbo.accounts
    SET balance = balance + @amount
    WHERE id = @to_id;

    IF @@ROWCOUNT = 0
      THROW 50003, 'Unknown target account.', 1;

    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;
    THROW;  -- re-raise the original error to the caller
  END CATCH;
END;
GO

EXEC dbo.transfer @from_id = 1, @to_id = 2, @amount = 50;

@@ROWCOUNT holds the number of rows the previous statement affected, and @@TRANCOUNT the number of open transactions. Output parameters are marked OUTPUT both in the definition and in the call:

SQL
-- SQL Server
CREATE OR ALTER PROCEDURE dbo.count_orders
  @customer_id INT,
  @order_count INT OUTPUT
AS
  SELECT @order_count = COUNT(*)
  FROM dbo.orders
  WHERE customer_id = @customer_id;
GO

DECLARE @n INT;
EXEC dbo.count_orders @customer_id = 5, @order_count = @n OUTPUT;
SELECT @n AS order_count;

Oracle's PL/SQL follows the same pattern (CREATE OR REPLACE PROCEDURE transfer (p_from IN NUMBER, ...) IS ... BEGIN ... EXCEPTION WHEN ... END;), and it adds packages, which group related procedures, functions and types behind a public interface.

Loops and the recursive CTE alternative

Every procedural language has loops. In T-SQL:

SQL
-- SQL Server: a savings balance growing 5% per year
DECLARE @year INT = 1, @balance DECIMAL(12, 2) = 1000;
WHILE @year <= 5
BEGIN
  SET @balance = @balance * 1.05;
  PRINT CONCAT('Year ', @year, ': ', @balance);
  SET @year += 1;
END;

PL/pgSQL has LOOP, WHILE and FOR i IN 1..5 LOOP, and MySQL has WHILE ... DO ... END WHILE, REPEAT and LOOP with LEAVE. In SQLite, and as a set-based alternative everywhere, a recursive CTE produces one row per iteration:

SQL A loop as a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

Year 5 ends at 1276.28. The CTE version is also a query, so you can join it, filter it and chart it. Recursive CTEs shows how far this technique goes.

Cursors, and why to avoid them

A cursor walks through a query result one row at a time inside procedural code. This MySQL procedure writes a restock request for every product with low stock:

SQL
-- MySQL: row-by-row processing with a cursor
DELIMITER //

CREATE PROCEDURE request_restocks()
BEGIN
  DECLARE v_done BOOLEAN DEFAULT FALSE;
  DECLARE v_id INT;
  DECLARE v_name VARCHAR(100);
  DECLARE low_stock CURSOR FOR
    SELECT id, name FROM products WHERE stock < 15;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

  OPEN low_stock;
  read_loop: LOOP
    FETCH low_stock INTO v_id, v_name;
    IF v_done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO restock_requests (product_id, note)
    VALUES (v_id, CONCAT('Low stock: ', v_name));
  END LOOP;
  CLOSE low_stock;
END //

DELIMITER ;

It works, but it's long, and it runs one INSERT per row. Databases are built to process sets of rows, so the single-statement version is shorter and usually much faster, on every database:

SQL The set-based version ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Four products qualify: the Bamboo Cutting Board, the Silicone Spatula Set, the Desk Planner 2026 and the Yoga Mat. Reach for a cursor only when each row truly needs separate procedural work, such as calling an external procedure per row.

Cursor attributes, implicit and explicit cursors

Oracle's PL/SQL tracks the state of a cursor in attributes you test after each FETCH: %FOUND (the last fetch returned a row), %NOTFOUND (it did not, so leave the loop), %ROWCOUNT (rows fetched so far) and %ISOPEN (whether the cursor is open). PostgreSQL's FOUND variable and MySQL's NOT FOUND handler, used above, do the same job.

SQL
-- Oracle PL/SQL, read-only
DECLARE
  CURSOR low_stock IS SELECT id, name FROM products WHERE stock < 15;
  v_id   products.id%TYPE;
  v_name products.name%TYPE;
BEGIN
  OPEN low_stock;
  LOOP
    FETCH low_stock INTO v_id, v_name;
    EXIT WHEN low_stock%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(low_stock%ROWCOUNT || ': ' || v_name);
  END LOOP;
  CLOSE low_stock;
END;

A cursor is implicit when the database opens one for you around a single statement (Oracle exposes it as SQL%ROWCOUNT) and explicit when you declare it as above. Cursors give you control (process rows in order, apply per-row logic) and a small memory footprint, since rows arrive one at a time. Their limits are speed (separate work per row instead of one set operation), longer code, and locks held for as long as the cursor stays open.

Comparison at a glance

PostgreSQL MySQL / MariaDB SQL Server SQLite
Language PL/pgSQL (also SQL, PL/Python, โ€ฆ) SQL/PSM style T-SQL None
Create CREATE PROCEDURE / FUNCTION CREATE PROCEDURE / FUNCTION CREATE OR ALTER PROCEDURE โ€”
Run CALL p(...), SELECT f(...) CALL p(...), SELECT f(...) EXEC p ..., SELECT dbo.f(...) โ€”
Variables DECLARE v integer; DECLARE v INT;, @session vars DECLARE @v INT; CTE "params"
Raise an error RAISE EXCEPTION SIGNAL SQLSTATE THROW RAISE() in triggers
Anonymous block DO $$ ... $$ โ€” Any batch โ€”

SQLite apps can also register application-defined functions with their driver. Python's conn.create_function("price_with_tax", 2, fn) makes a Python function callable from SQL on that connection, which covers many of the uses of server-side functions.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main difference between a stored procedure and a user-defined function?

    You call a function inside SELECT, WHERE and other expressions. A procedure is executed with CALL or EXEC and usually changes data.
  2. 2. Why does the mysql client need DELIMITER // before CREATE PROCEDURE?

    DELIMITER is a client command. It changes the end-of-statement marker so the body's semicolons stay inside one statement.
  3. 3. In the recursive CTE demo, what balance does year 5 show?

    1000 ร— 1.05โต โ‰ˆ 1276.28. Each recursive step multiplies the previous row's balance by 1.05.
  4. 4. How can a SQLite database reject an update that would make stock negative, no matter which code runs it?

    SQLite has no procedures or SIGNAL, but triggers and constraints run on every write and can abort it with a message.
  5. 5. Which is usually the best way to insert one row per low-stock product?

    Set-based statements let the database process all rows at once; cursors are slower and longer.

Summary

  • Stored procedures and functions are named programs stored in the database: procedures act (CALL/EXEC), functions return values inside queries.
  • PostgreSQL uses PL/pgSQL with $$ bodies, DECLARE, IF, FOUND and RAISE EXCEPTION.
  • MySQL uses DELIMITER in the client, IN/OUT/INOUT parameters, session @variables and SIGNAL SQLSTATE '45000'.
  • T-SQL uses @variables, TRY ... CATCH, THROW, @@ROWCOUNT and OUTPUT parameters.
  • SQLite has no procedures: use application code with transactions, views, parameter CTEs, recursive CTEs for loops, and triggers or constraints for rules.
  • Prefer set-based statements over cursors and loops.

Next: Users, Roles and Permissions (DCL), where you'll control who can read and change which data.