Functions.sql

Basic MySQL CREATE FUNCTION statement
A very basic CREATE FUNCTION example which will produced the famed 'Hello World' output:

DELIMITER $$
CREATE FUNCTION hello_world()
  RETURNS TEXT
  LANGUAGE SQL
BEGIN
  RETURN 'Hello World';
END;
$$
DELIMITER ;
Execute this function as follows:

mysql> SELECT hello_world();
+---------------+
| hello_world() |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)
MySQL function with a parameter
Customize your 'Hello World' output with input from a parameter:

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
  RETURNS TEXT
  LANGUAGE SQL -- This element is optional and will be omitted from subsequent examples
BEGIN
  RETURN CONCAT('Hello ', addressee);
END;
$$
DELIMITER ;
Execute as follows:

mysql> SELECT hello_world('Earth');
+----------------------+
| hello_world('Earth') |
+----------------------+
| Hello Earth          |
+----------------------+
1 row in set (0.00 sec)
MySQL function with a local variable
Use a local variable to perform calculations inside your function:

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
  RETURNS TEXT
BEGIN
  DECLARE strlen INT;
  SET strlen = LENGTH(addressee);
  RETURN CONCAT('Hello ', addressee, ' - your parameter has ', strlen, ' characters');
END;
$$
DELIMITER ;
Output:

mysql> SELECT hello_world('Earth');
+-----------------------------------------------+
| hello_world('Earth')                          |
+-----------------------------------------------+
| Hello Earth - your parameter has 5 characters |
+-----------------------------------------------+
1 row in set (0.00 sec)
MySQL function with a loop
DROP FUNCTION IF EXISTS looptest;
DELIMITER $$
CREATE FUNCTION looptest()
  RETURNS INT READS SQL DATA
BEGIN
  DECLARE v_total INT;

  SET v_total = 0;

  count_loop: LOOP
    SET v_total = v_total + 1;

    IF v_total = 10 THEN
      LEAVE count_loop;
    END IF;
    
  END LOOP;

  RETURN v_total;
END;
$$
DELIMITER ;
MySQL function with a loop and cursor
It is possible - if somewhat inelegant - to iterate through a CURSOR using a loop:

DROP FUNCTION IF EXISTS looptest;
DELIMITER $$
CREATE FUNCTION looptest()
  RETURNS INT READS SQL DATA
BEGIN
  DECLARE v_total INT;
  DECLARE v_counter INT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE csr CURSOR FOR 
    SELECT counter FROM items;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET v_total = 0;
  OPEN csr;
  read_loop: LOOP
    FETCH csr INTO v_counter;

    IF done THEN
      LEAVE read_loop;
    END IF;

    SET v_total = v_total + v_counter;
  END LOOP;
  CLOSE csr;

  RETURN v_total;
END;
$$

DELIMITER ;