SQL Stored Procedures for Beginners: MySQL Examples

# What is a stored procedure? Why you need one: a job that takes several statements, like placing an order, then runs the same way every time, and the app only has to send one CALL. A stored procedure is a set of SQL statements saved in the database under a name. You write it once with CREATE PROCEDURE. After that, anyone can run all of it with one CALL. - `CREATE PROCEDURE get_city_customers` saves a procedure with this name - `(IN p_city VARCHAR(50))` its parameter: a value you pass in each time you run it - `BEGIN` where its statements start - `END` where they stop - `;` ends each statement inside > A procedure is like a function in a programming language, stored in the database. The practice database in your browser can't store procedures, so this course runs the body for you when you CALL it.

CREATE PROCEDURE get_city_customers(
  IN p_city VARCHAR(50))
BEGIN
  SELECT name, email
  FROM customers
  WHERE city = p_city;
END

# Run it with CALL CALL runs a saved procedure. You give its name and, in brackets, a value for each parameter. - `CALL get_city_customers('Leeds')` runs the procedure with p_city = 'Leeds' Inside the procedure, every p_city is replaced by 'Leeds'. So its SELECT returns the customers who live in Leeds. > Give the values in the same order as the parameters in CREATE PROCEDURE. The names don't matter in a CALL, only the order.

CREATE PROCEDURE get_city_customers(
  IN p_city VARCHAR(50))
BEGIN
  SELECT name, email
  FROM customers
  WHERE city = p_city;
END

# Parameters: direction, name, type Each parameter has three parts. - `IN` its direction: the value goes into the procedure - `p_city` its name, used inside the body - `VARCHAR(50)` its type: text up to 50 characters This version names the parameter city, the same as the column. Inside the procedure, MySQL reads every city as the parameter. So WHERE city = city becomes 'Leeds' = 'Leeds', which is always true: every customer comes back. > Start parameter names with p_ and local variables with v_. Then they can never clash with a column name.

CREATE PROCEDURE get_city_customers(
  IN city VARCHAR(50))
BEGIN
  SELECT name, city
  FROM customers
  WHERE city = city;
END

# IN: a value going in The caller passes a value in, and the procedure gets its own copy of it. - `SET @n = 5` the caller's variable holds 5 - `CALL double_it(@n)` p_n starts as a copy of 5 - `SET p_n = p_n * 2` the procedure doubles its copy to 10 - `SELECT p_n AS inside` shows the copy: 10 Afterwards, @n is still 5. Changes to an IN parameter never go back to the caller. > Most parameters are IN. Use IN unless the procedure has to hand a value back: that's OUT, on the next slide.

CREATE PROCEDURE double_it(
  IN p_n INT)
BEGIN
  SET p_n = p_n * 2;
  SELECT p_n AS inside;
END

# OUT: a value coming back An OUT parameter is how a procedure hands a value back to you. - `OUT p_order_count INT` the procedure fills this in; it starts as NULL - `SELECT COUNT(*) INTO p_order_count` puts the query's answer into it - `@order_count` a variable in the CALL that receives the value - `SELECT @order_count` reads it after the CALL > For an OUT parameter, the CALL must pass a variable like @order_count, not a value. CALL count_orders(1, 5) is an error: there's nowhere to put the answer.

CREATE PROCEDURE count_orders(
  IN p_customer_id INT,
  OUT p_order_count INT)
BEGIN
  SELECT COUNT(*) INTO p_order_count
  FROM orders
  WHERE customer_id = p_customer_id;
END

# Change data and report back A procedure can change data and still hand a value back. - `UPDATE … WHERE id = p_order_id` changes one order - `ROW_COUNT()` how many rows the last statement changed - `SET p_rows = ROW_COUNT()` puts that number in the OUT parameter > Return ROW_COUNT() from procedures that change data. If it comes back 0, the id didn't exist, and the app can tell the user.

CREATE PROCEDURE mark_delivered(
  IN p_order_id INT,
  OUT p_rows INT)
BEGIN
  UPDATE orders
  SET status = 'delivered'
  WHERE id = p_order_id;
  SET p_rows = ROW_COUNT();
END

# INOUT: in and back out One parameter carries a value in, and the changed value back out. - `SET @bill = 20.00` put the starting value in the variable first - `CALL add_tip(@bill, 10)` the procedure reads 20 and writes 22 back - `*` `/` `+` times, divide, add > INOUT is rare in real code. One IN and one OUT parameter are usually easier to read.

CREATE PROCEDURE add_tip(
  INOUT p_total DECIMAL(6,2),
  IN p_tip_percent INT)
BEGIN
  SET p_total = p_total + p_total *
    p_tip_percent / 100;
END

# IN, OUT and INOUT side by side - `IN` you give a value, the procedure reads it - `OUT` the procedure gives a value back, starting as NULL - `INOUT` you give a value, the procedure changes it, you get it back - `--` a comment: a note for people, ignored by the database > In a CALL, IN can take a plain value like 1 or 'Leeds'. OUT and INOUT always need an @variable.

CREATE PROCEDURE example(
  -- you give it, it reads it:
  IN p_customer_id INT,
  -- it gives it back to you:
  OUT p_order_count INT,
  -- you give it, it changes it,
  -- you get it back:
  INOUT p_total DECIMAL(6,2)
)
BEGIN
  -- …
END

# Local variables: DECLARE A local variable holds a value while the procedure runs, like scrap paper. - `DECLARE v_food DECIMAL(8,2)` creates v_food, a decimal number - `DEFAULT 0` its starting value (NULL without DEFAULT) Every DECLARE goes right after BEGIN, before any other statement. The variable disappears at END. > MySQL refuses a DECLARE that comes after another statement. If you get a syntax error near DECLARE, move it to the top.

CREATE PROCEDURE customer_total(
  IN p_customer_id INT,
  OUT p_total DECIMAL(8,2))
BEGIN
  DECLARE v_food DECIMAL(8,2)
    DEFAULT 0;
  DECLARE v_tips DECIMAL(8,2)
    DEFAULT 0;

  -- the work goes here, using
  -- v_food and v_tips
END

# Putting a value in a variable - `SET v = …` when you can work the value out - `SELECT … INTO v` when the value comes from a table SELECT … INTO needs exactly one row. No rows: the variable keeps its old value. Two or more rows: an error. > Use SUM, COUNT or MAX in a SELECT … INTO, or filter by the primary key. Both always give exactly one row.

CREATE PROCEDURE customer_total(
  IN p_customer_id INT,
  OUT p_total DECIMAL(8,2))
BEGIN
  DECLARE v_food DECIMAL(8,2)
    DEFAULT 0;
  DECLARE v_tips DECIMAL(8,2)
    DEFAULT 0;

  SELECT SUM(o.qty * p.price)
  INTO v_food
  FROM orders o
  JOIN pizzas p ON p.id = o.pizza_id
  WHERE o.customer_id =
    p_customer_id;

  SELECT SUM(tip) INTO v_tips
  FROM orders
  WHERE customer_id = p_customer_id;

  SET p_total = v_food + v_tips;
END

# Test the query on its own first This is the first SELECT from customer_total, without INTO, for customer 1. It returns the one number that goes into v_food. > Before you put a query in a procedure, run it on its own with real values. Most procedure bugs are really query bugs.

SELECT SUM(o.qty * p.price) AS
  v_food
FROM orders o
JOIN pizzas p ON p.id = o.pizza_id
WHERE o.customer_id = 1

# Session variables and local variables - `DECLARE v_food` local: exists only between BEGIN and END - `@total` session: no DECLARE, lasts until you disconnect > Use @variables to catch OUT values and to test. Inside a procedure, use DECLARE: an @variable left over from an earlier CALL causes very confusing bugs.

-- a session variable: no DECLARE,
-- lasts until you disconnect
SET @city = 'Leeds';
SELECT name
FROM customers
WHERE city = @city;

CALL customer_total(1, @total);
-- 41.00, still here after the CALL
SELECT @total;
-- error: v_food only existed inside
-- the procedure
SELECT v_food;

# DELIMITER: creating a procedure in a script In a MySQL script, ; ends a statement. A procedure has ; inside it, so the script would stop in the middle. DELIMITER changes the end marker while you create it. - `DELIMITER //` from now on, // ends a statement - `END //` the end of CREATE PROCEDURE - `DELIMITER ;` back to normal > You need this in the mysql command line and in .sql script files. phpMyAdmin has a separate Delimiter box instead.

DELIMITER //
CREATE PROCEDURE get_pizza(
  IN p_id INT)
BEGIN
  SELECT *
  FROM pizzas
  WHERE id = p_id;
END //
DELIMITER ;

# Why use stored procedures? - The logic lives in one place, next to the data - The app sends one CALL instead of many statements - An app can be allowed to CALL a procedure without access to the tables - `DROP PROCEDURE IF EXISTS` deletes a procedure > In Appian you run them with a!executeStoredProcedureForQuery to read data and a!executeStoredProcedureOnSave to change it.

DROP PROCEDURE IF EXISTS
  get_city_customers;