SQL › Code That Lives in the Database

Stored Procedures and Functions

What a stored procedure is

A stored procedure is a block of code saved in the database under a name. It can hold several SQL statements: queries, INSERT, UPDATE, DELETE, even CREATE TABLE.

It can also use things a single query can't: variables, IF … ELSE, loops and error handling.

Use one when a task takes several steps that always go together. For example, a sale adds an order line and lowers the product's stock. The app calls one name instead of sending each statement, and the steps are written in one place.

The parts of a procedure

A procedure has a name, parameters in brackets, and a body between BEGIN and END. Variables are declared before BEGIN. CALL runs it.

In PostgreSQL, the body is text between $$ signs, and LANGUAGE says how to read it: sql for plain statements, plpgsql for variables and IF.

Code
CREATE OR REPLACE PROCEDURE procedure_name(p_name type, p_other type)
LANGUAGE plpgsql
AS $$
DECLARE
  v_name type;                                  -- variables
BEGIN
  SELECT column1 INTO v_name FROM table_name WHERE ...;
  -- more statements, each ending with ;
END;
$$;
 
CALL procedure_name(value, value);

The p_ and v_ prefixes are only a habit: they show at a glance which names are parameters and which are variables.

The idea is the same in every database, but the syntax differs:

Give text parameters a length where the database expects one. In SQL Server, VARCHAR with no length means one character, so a product name is cut to its first letter. MySQL refuses VARCHAR with no length, and Oracle refuses a length on a parameter.

MySQL needs DELIMITER because its client ends a statement at every ;. Changing the delimiter to $$ for a moment keeps the whole procedure as one statement.

Dollar quoting

Text in single quotes must double every ' inside it, which gets messy in a long body. PostgreSQL lets you quote text with $$ instead, and then nothing inside needs escaping.

Let's imagine a product note that has an apostrophe in it:

Code
SELECT 'Kid''s favourite' AS doubled, $$Kid's favourite$$ AS dollar_quoted;

Other databases read a procedure's body as code, not text, so they don't need this.

A procedure without parameters

A procedure doesn't need parameters. In PostgreSQL the empty brackets are still required, both in CREATE and in CALL; Oracle leaves them out when creating one.

Let's imagine the shop thanks everyone who wrote a review with 50 loyalty points and ₹20 in their wallet:

Code
CREATE PROCEDURE pr_reward_reviewers()
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE customers SET loyalty_points = loyalty_points + 50
  WHERE customer_id IN (SELECT customer_id FROM reviews);
 
  UPDATE accounts SET balance = balance + 20
  WHERE customer_id IN (SELECT customer_id FROM reviews);
END;
$$;
 
CALL pr_reward_reviewers();
SELECT c.customer_id, c.loyalty_points, a.balance
FROM customers c
JOIN accounts a ON a.customer_id = c.customer_id
WHERE c.customer_id IN (1, 2)
ORDER BY c.customer_id;

Creating a procedure only saves it; nothing changes until you CALL it. Every CALL runs the body again, so calling it twice gives the reviewers 100 points.

Parameters

Parameters let one procedure work for any value. You pass the values in CALL, in the same order as the parameters.

A parameter is an IN parameter unless you say otherwise: a value passed in. Writing IN is optional. OUT parameters pass values back to the caller.

Now imagine a customer moves to another city, and their pending orders should follow them:

Code
CREATE PROCEDURE pr_move_customer(IN p_customer_id INT, IN p_city VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE customers SET city = p_city WHERE customer_id = p_customer_id;
 
  UPDATE orders SET shipping_city = p_city
  WHERE customer_id = p_customer_id AND status = 'pending';
END;
$$;
 
CALL pr_move_customer(5, 'Goa');
SELECT order_id, status, shipping_city FROM orders WHERE customer_id = 5 ORDER BY order_id;
 
-- Fails: CALL needs a value for every parameter
CALL pr_move_customer(5);

Variables and IF … ELSE

A variable holds a value while the procedure runs. Declare it with a name and a type, then fill it with SELECT … INTO. SQL Server writes SELECT @v_name = column instead.

IF … THEN … ELSE … END IF picks which statements run, the way CASE picks a value in a query. SQL Server leaves out THEN and END IF, and wraps several statements in BEGIN … END.

RAISE NOTICE prints a message; the lab shows only results, so you won't see it there. RAISE EXCEPTION stops the CALL with your own error message. Oracle uses RAISE_APPLICATION_ERROR, SQL Server THROW and MySQL SIGNAL.

Any error, yours or the database's, stops the CALL. PostgreSQL and Oracle then undo everything it changed. In SQL Server and MySQL, the steps that already ran stay, unless the CALL is inside a transaction (Section 10).

Let's imagine a price change is refused when it would cut a price by more than half:

Code
CREATE PROCEDURE pr_set_price(p_product_id INT, p_price NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
  v_old_price NUMERIC;
BEGIN
  SELECT price INTO v_old_price FROM products WHERE product_id = p_product_id;
 
  IF p_price >= v_old_price / 2 THEN
    UPDATE products SET price = p_price WHERE product_id = p_product_id;
  ELSE
    RAISE EXCEPTION 'Product %: % is less than half of %', p_product_id, p_price, v_old_price;
  END IF;
END;
$$;
 
CALL pr_set_price(15, 899);
SELECT product_id, product_name, price FROM products WHERE product_id = 15;
 
-- Fails: the procedure stops with its own message
CALL pr_set_price(15, 199);

Change or remove a procedure

CREATE OR REPLACE swaps in a new body and keeps the name. A plain CREATE fails when the name is taken. DROP PROCEDURE removes it.

Let's imagine the reviewers' bonus goes up from 50 to 80 points:

Code
CREATE PROCEDURE pr_bonus() LANGUAGE sql AS $$ UPDATE customers SET loyalty_points = loyalty_points + 50 $$;
CREATE OR REPLACE PROCEDURE pr_bonus() LANGUAGE sql AS $$ UPDATE customers SET loyalty_points = loyalty_points + 80 $$;
 
-- Fails: pr_bonus already exists
CREATE PROCEDURE pr_bonus() LANGUAGE sql AS $$ SELECT 1 $$;
DROP PROCEDURE pr_bonus;

Functions

A user-defined function also saves code under a name, but it returns a value, and you use it inside a query, just like ROUND or UPPER. RETURNS gives the type of that value.

With LANGUAGE sql, the function returns the result of its query. A plpgsql function uses RETURN instead.

Let's imagine every full 10 loyalty points are worth ₹1, and the shop shows each Goa customer's points in rupees:

Code
CREATE FUNCTION f_points_in_rupees(p_points INT)
RETURNS INT
LANGUAGE sql
AS $$
  SELECT p_points / 10
$$;
 
SELECT full_name, loyalty_points, f_points_in_rupees(loyalty_points) AS worth
FROM customers
WHERE city = 'Goa'
ORDER BY full_name;
 
-- Fails: a function goes inside a query; CALL is for procedures
CALL f_points_in_rupees(500);

The other databases write it like their procedures, with a RETURN statement in the body. SQL Server also wants the schema in front of the name when you use it, as in dbo.f_points_in_rupees(…).

Triggers

A trigger runs code by itself when a table changes: on INSERT, UPDATE or DELETE. Nobody calls it; the change does.

A BEFORE trigger runs before the row is stored, so it can still fix or refuse it. An AFTER trigger runs once the change is made, for example to write a log. FOR EACH ROW runs it once per changed row, FOR EACH STATEMENT once per statement.

In a row trigger, NEW is the row as it will be stored and OLD the row as it was. An INSERT has only NEW, a DELETE only OLD.

In PostgreSQL, the code is a function that RETURNS trigger, and CREATE TRIGGER attaches it to the table. A BEFORE trigger returns NEW, the row to store.

Let's imagine every new customer's email should be stored trimmed and in lowercase, however it was typed:

Code
CREATE FUNCTION f_clean_email()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.email := LOWER(TRIM(NEW.email));
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER trg_clean_email
BEFORE INSERT OR UPDATE OF email ON customers
FOR EACH ROW EXECUTE FUNCTION f_clean_email();
 
INSERT INTO customers (full_name, email, city) VALUES ('Ravi Rao', '  Ravi.Rao@Example.com ', 'Goa');
SELECT full_name, email FROM customers WHERE full_name = 'Ravi Rao';

Now imagine a moderator removes low-rated reviews, and the shop wants to keep a copy of each one:

Code
CREATE TABLE deleted_reviews (review_id INT, product_id INT, rating INT);
 
CREATE FUNCTION f_keep_deleted_review()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO deleted_reviews (review_id, product_id, rating)
  VALUES (OLD.review_id, OLD.product_id, OLD.rating);
  RETURN OLD;
END;
$$;
 
CREATE TRIGGER trg_keep_deleted_review
AFTER DELETE ON reviews
FOR EACH ROW EXECUTE FUNCTION f_keep_deleted_review();
 
DELETE FROM reviews WHERE rating <= 3;
SELECT review_id, product_id, rating FROM deleted_reviews ORDER BY review_id;

MySQL and Oracle write the code inside CREATE TRIGGER itself; Oracle calls the rows :OLD and :NEW. SQL Server has no BEFORE triggers, only AFTER and INSTEAD OF, and runs a trigger once per statement, with the changed rows in tables named inserted and deleted.

A trigger is easy to forget: a statement can do more than it says, which makes problems harder to find. Keep triggers small, for logs and simple checks.

Procedure or function?

Keep changes in procedures and calculations in functions. SQL Server doesn't let a function change a table at all.

Remember

• CALL runs a procedure's steps; a function returns a value and goes inside a query.
• In PostgreSQL, the body sits between $$ … $$, and LANGUAGE plpgsql is needed for variables and IF.
• SELECT … INTO fills a variable; IF … THEN … ELSE … END IF decides which steps run.
• Every CALL runs the body again: two calls make the change twice.
• A plain CREATE fails when the name exists; use CREATE OR REPLACE to change the body.
• A trigger runs by itself on INSERT, UPDATE or DELETE; OLD is the row before, NEW the row after.

Practice

1. A Procedure to Restock Products (Easy)
2. Sell Only What's in Stock (Medium)
3. A Function for Line Totals (Medium)
4. Log Every Price Change (Hard)