mysql 函数 局部变量_mysql创建一个简单的函数

基本的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 ;