基本的MySQL创建函数语句

一个非常基本的例子创建函数,该函数将产生了著名的“Hello World”输出:

DELIMITER $$
CREATE FUNCTION hello_world()
  RETURNS TEXT
  LANGUAGE SQL
BEGIN
  RETURN 'Hello World';
END;
$$
DELIMITER ;

执行这个函数如下:

mysql> SELECT hello_world();
+---------------+
| hello_world() |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)

MySQL函数参数

定制您的“Hello World”的输出与输入参数:

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 ;

执行如下:

mysql> SELECT hello_world('Earth');
+----------------------+
| hello_world('Earth') |
+----------------------+
| Hello Earth          |
+----------------------+
1 row in set (0.00 sec)

MySQL函数局部变量
使用局部变量在函数执行计算:

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函数与一个循环

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函数与一个循环
是可能的——如果有些不难——使用一个循环遍历

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 ;