MySQL Procedures

• Simple Procedure
  • OUT Parameter
• Variables
• Control flow
  • IF
  • CASE
  • WHILE
  • REPEAT
  • LOOP

This has partially been stolen here: https://www.dolthub.com/blog/2024-01-17-writing-mysql-procedures/
Reference: MySQL stored procedure syntax

Simple Procedure

Insert values across multiple Tables

mysql> CREATE TABLE employees (
    id bigint primary key auto_increment,
    first_name varchar(100),
    last_name varchar(100));

mysql> CREATE TABLE birthdays (
    emp_id bigint,
    birthday date,
    constraint foreign key (emp_id) references employees(id)
);

mysql> delimiter \\

mysql> CREATE procedure new_employee(
    first char(100),
    last char(100),
    birthday date)
BEGIN
    INSERT INTO employees (first_name, last_name) VALUES (first, last);
    SET @id = (SELECT last_insert_id());
    INSERT INTO birthdays (emp_id, birthday) VALUES (@id, birthday);
END;
//

mysql> delimiter ;

mysql> call new_employee("Thor", "Himself", "1973-01-01");
Query OK, 1 row affected (0.02 sec)

mysql> mysql> SELECT * FROM birthdays;
+--------+------------+
| emp_id | birthday   |
+--------+------------+
|      1 | 1973-01-01 |
+--------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM employees;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Thor       | Himself   |
+----+------------+-----------+
1 row in set (0.00 sec)

OUT Parameter

delimiter //

CREATE PROCEDURE birthday_count(
    IN bday date,
    OUT count int)
BEGIN
    SET count = (SELECT count(*) FROM birthdays WHERE birthday = bday);
END
//

delimiter ;

-- couple other birthdays
CALL new_employee('Thyron', 'Himself', '1975-01-10');
CALL new_employee('Sif', 'Herself', '1975-01-10');

SET @count = 0;

call birthday_count('1975-01-10', @count);
Query OK, 0 rows affected (0.00 sec)

SELECT @count;
+--------+
| @count |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

Variables

You can use variables in procedures by just declaring them.
INT values are initialized with a value of NULL, use ‘default’ keyword to initialize with a number.

DECLARE count1, count2 int;

DECLARE count1, count2 int default 1;

Control flow

IF

CREATE PROCEDURE birthday_message(
    bday date,
    OUT message varchar(100))
BEGIN
    DECLARE count int;
    DECLARE name varchar(100);
    SET count = (SELECT count(*) FROM birthdays WHERE birthday = bday);
    IF count = 0 THEN
        SET message = "Nobody has this birthday";
    ELSEIF count = 1 THEN
        SET name = (SELECT concat(first_name, " ", last_name)
            FROM employees join birthdays
            on emp_id = id
            WHERE birthday = bday);
        SET message = (SELECT concat("It's ", name, "'s birthday"));
    ELSE
        SET message = "More than one employee has this birthday";
    END IF;
END

CASE

CREATE PROCEDURE birthday_message(
    bday date,
    OUT message varchar(100))
BEGIN
    DECLARE count int;
    DECLARE name varchar(100);
    SET count = (SELECT count(*) FROM birthdays WHERE birthday = bday);
    CASE count
        WHEN 0 THEN
            SET message = "Nobody has this birthday";
        WHEN 1 THEN
            SET name = (SELECT concat(first_name, " ", last_name)
                FROM employees join birthdays
                on emp_id = id
                WHERE birthday = bday);
            SET message = (SELECT concat("It's ", name, "'s birthday"));
        ELSE
            SET message = "More than one employee has this birthday";
    END CASE;
END

WHILE

CREATE PROCEDURE fib(n int, out answer int)
BEGIN
    DECLARE i int default 2;
    DECLARE p, q int default 1;
    SET answer = 1;
    WHILE i < n DO
        SET answer = p + q;
        SET p = q;
        SET q = answer;
        SET i = i + 1;
    END WHILE;
END;

REPEAT

CREATE PROCEDURE fib(n int, out answer int)
BEGIN
    DECLARE i int default 1;
    DECLARE p int default 0;
    DECLARE q int default 1;
    SET answer = 1;
    REPEAT
        SET answer = p + q;
        SET p = q;
        SET q = answer;
        SET i = i + 1;
    UNTIL i >= n END REPEAT;
END;

LOOP

CREATE PROCEDURE fib(n int, out answer int)
BEGIN
    DECLARE i int default 2;
    DECLARE p, q int default 1;
    SET answer = 1;
    loop1: LOOP
        IF i >= n THEN
            LEAVE loop1;
        END IF;
        SET answer = p + q;
        SET p = q;
        SET q = answer;
        SET i = i + 1;
    END LOOP loop1;
END;

Example using ‘ITERATE’

CREATE PROCEDURE fib(n int, out answer int)
BEGIN
    DECLARE i int default 2;
    DECLARE p, q int default 1;
    SET answer = 1;
    loop1: LOOP
        IF i < n THEN
            SET answer = p + q;
            SET p = q;
            SET q = answer;
            SET i = i + 1;
            ITERATE loop1;
        END IF;
        LEAVE loop1;
    END LOOP loop1;
END;