mysql自定义函数如何引用参数 mysql函数定义变量

转载

墨染青衫 2024-05-31 15:29:46

文章标签 mysql自定义函数如何引用参数存储过程 SQL语法规则存储函数游标 文章分类 MySQL 数据库

1.概述
存储程序可以封装一些语句，为用户提供一种简单的方式来调用这个存储程序，从而间接执行其封装的语句。
根据调用方式的不同，可把存储程序分为存储例程、触发器、事件几种类型。其中，存储例程又可被细分为存储函数和存储过程。

2.用户自定义变量
MYSQL中，可通过SET语句来自定义一些自己的变量。
如：SET @a = 1; 在我们的自定义变量前面必须加一个@符号。
在使用SET语句时，如变量名前没有加@符号，则MYSQL会把这个变量当作系统变量来对待。
之后想查看这个变量的值，使用SELECT @变量名; 如：SELECT @a; 同一个变量也可存储不同类型的值。如，再把一个字符串赋给变量a。
如：SET @a = '哈哈哈'; 除了把一个常量赋给一个变量，也可把一个变量赋给另一个变量。
如：SET @b = @a; 变量a，b是独立的。此后a改变不会影响b。

当某个查询的结果集是一行一列时，可将查询结果集赋给变量。
如：SET @a = (SELECT m1 FROM t1 LIMIT 1); 也可用INTO子句完成类似功能。
如：SELECT n1 FROM t1 LIMIT 1 INTO @b;效果等价于SET @b = (SELECT n1 FROM t1 LIMIT 1);

当某个查询的结果集是一行多列。如想将结果集中各列赋值给不同的变量，不能用SET，只能用INTO。
如：SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b;

3.存储函数
存储程序可以分为存储例程，触发器，事件几种类型。
存储例程需我们去手动调用，触发器和事件都是MySQL服务器在特定条件下自己调用的。
存储例程又可分为存储函数，存储过程。

3.1.创建存储函数
存储函数是一种函数，在函数定义中书写MySQL语句。

在MySQL中定义存储函数的语句如下：

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
	函数体内容
END

定义一个存储函数时，需指定存储函数名称，参数列表，返回值类型及函数体内容。
如该函数不需参数，则参数列表可省略。
函数体内容被包裹在BEGIN ... END中，可包括一条或多条语句，每条语句都要以分号（;）结尾。
上述语句中空格，换行也可略去。不会影响含义。

实例：

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
	RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END

由于MySQL默认将;视为语句结束符。
所以，执行上述函数定义应该这样写：

DELIMITER $
CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
	RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END $

DELIMITER ;

上述通过DELIMITER将MySQL的语句结束符临时修改为$，之后再修改回来。

有时，要创建函数，可能得设置下：SET global log_bin_trust_function_creators = TRUE;

3.2.存储函数的调用
函数调用可单独使用，也可作为一个操作数与其他操作数组成复杂的表达式。
如：SELECT avg_score('MySQL是怎样运行的');

3.3.查看和删除存储函数
如：SHOW FUNCTION STATUS [LIKE 需要匹配的函数名];

查看函数定义，如：SHOW CREATE FUNCTION 函数名\G 删除存储函数，如：DROP FUNCTION 函数名;

3.4.函数体的定义
3.4.1.在函数体中定义局部变量
如果我们想在存储函数的函数体中使用变量的话，必须提前使用DECLARE语句声明该变量。
具体语法：DECLARE 变量名1, 变量名2, ... 数据类型 [DEFAULT 默认值]; 这些在函数体内声明的变量只在该函数体内有用，当存储函数执行完成后，就不能访问这些变量了。所以，这些变量也称为局部变量。

可在一条语句中声明多个相同类型的变量。
注意的是，函数体中的局部变量不允许加@前缀（除非使用反引号将变量名引起来），这一点与之前直接使用SET语句自定义变量截然不同。在声明了这个局部变量后，在可使用它。

DELIMITER $
CREATE FUNCTION var_demo()
RETURNS INT
BEGIN
	DECLARE c INT;
	SET c=5;
	RETURN c;
END $

如果不对声明的局部变量进行赋值，它的默认值就是NULL，当然也可通过DEFAULT子句来显式地指定局部变量的默认值，比如：

DELIMITER $
CREATE FUNCTION var_default_demo()
RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 1;
	RETURN c;
END $
DELIMITER ;

与用户自定义变量类似，也可把一个查询的结果赋给局部变量。

DELIMITER $
CREATE FUNCTION var_default_demo()
RETURNS DOUBLE
BEGIN
	DECLARE c DOUBLE DEFAULT 1.0;
	SET c =  (SELECT AVG(score) FROM student_score WHERE subject = s);
	return c;
END $
DELIMITER ;

3.4.2.在函数体中使用用户自定义变量

DELIMITER $
CREATE FUNCTION user_defined_var_demo()
RETURNS INT
BEGIN
	SET @abc = 10;
	RETURN @abc;
END $
DELIMITER ;

3.4.3.存储函数的参数
在定义存储函数的时候，可以指定多个参数，且每个参数都要指定对应的数据类型。
形参名不应与函数体语句中的其他变量中，列名冲突。形参不支持提供默认值。
形参，函数体内定义的参数均为局部变量。全局变量，局部变量使用时无需@。使用自定义变量需添加@。
函数体内需要定义局部变量时，局部变量定义必须出现在函数体内语句的最前方。
函数体内对形参的修改不会传递到外部实参。

3.4.4.判断语句的编写
在存储函数的函数体中可使用判断语句。

IF 表达式 THEN
	语句列表
[ELSEIF 表达式 THEN 语句列表]
...
[ELSE 语句列表]
END IF;

如：

DELIMITER $
CREATE FUNCTION condition_demo(i INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE result VARCHAR(10);
IF i = 1 THEN
	SET result = '结果是1';
ELSEIF i = 2 THEN
	SET result='结果是2';
ELSEIF i = 3 THEN
	SET result='结果是3';
ELSE
	SET result='非法参数';
END IF;
RETURN result;
END $

DELIMITER ;

3.4.5.循环语句的编写
(1).WHILE循环语句
语法：

WHILE 表达式 DO
	语句列表
END WHILE;

实例：

DELIMITER $
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE i INT DEFAULT 1;
	WHILE i <= n DO
		SET result = result +  i;
		SET i = i + 1;
	END WHILE;
	RETURN result;
END $

DELIMITER ;

这样执行SELECT sum_all(3);结果将是6。

(2).REPEAT语句

REPEAT
	语句列表
UNTIL 表达式 END REPEAT

表达式为真时，跳出循环。

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE i INT DEFAULT 1;
	REPEAT
		SET result = result + i;
		SET i = i + 1;
	UNTIL i > n END REPEAT;
	RETURN result;
END

(3).LOOP循环语句

LOOP 
	语句列表
END LOOP;

LOOP下要么在语句列表通过RETURN实现返回，要么通过LOOP添加标志与LEAVE的方式离开。

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE i INT DEFAULT 1;
	LOOP
		IF i > n THEN
			RETURN result;
		END IF;
		SET result = result + i;
		SET i = i + 1;
	END LOOP;
END

上述为通过RETURN实现返回

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE i INT DEFAULT 1;
	flag:LOOP
		IF i > n THEN
			LEAVE flag;
		END IF;
		SET result = result + i;
		SET i = i + 1;
	END LOOP flag;
	RETURN result;
END

4.存储过程
存储函数和存储过程都属于存储例程，都是对某些语句的一个封装。存储函数会给调用它的用户返回一个结果，但存储过程没返回值。

4.1.创建存储过程

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	需要执行的语句
END

存储过程不需声明返回值类型，也无法返回值。

DELIMITER $
CREATE PROCEDURE t1_operation(m1_value INT, n1_value CHAR(1))
BEGIN
	SELECT * FROM t1;
	INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value);
	SELECT * FROM t1;
END $

4.2.存储过程的调用
如果我们需调用某个存储过程，需显式使用CALL：CALL 存储过程([参数列表]); 针对上述过程这样调用：CALL t1_operation(4, 'd');

4.3.查看和删除存储过程
查看：SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]; 查看存储过程定义：SHOW CREATE PROCEDURE 存储过程名称; 删除存储过程：DROP PROCEDURE 存储过程名称;

4.4.存储过程中的语句
存储函数中使用的各种语句，都可用在存储过程中。

4.5.存储过程的参数前缀
比存储函数强大点的是，存储过程在定义参数的时候可选择添加一些前缀：[IN | OUT | INOUT] 参数名数据类型IN参考高级语言值传参，OUT参考高级语言引用传参。
(1).IN

DELIMITER $
CREATE PROCEDURE p_in(IN arg INT)
BEGIN
	SELECT arg;
	SET arg = 123;
END $
DELIMITER ;

上述采用IN修饰形参

SET @a = 1;
CALL p_in(@a);
SELECT @a; // 这里a仍然为1

对IN修饰的形参，可以传递常量实参。

(2).OUT

DELIMITER $
CREATE PROCEDURE p_out(OUT arg INT)
BEGIN
	SELECT arg;
	SET arg = 123;
END $
DELIMITER ;

上述采用OUT修饰形参

SET @b = 2;
CALL p_out(@b);// 这里内部执行SELECT输出值将是NULL，因为OUT修饰的形参不可用于读取值。只能被设置值。
SELECT @b; // 这里b变为123

对OUT修饰的形参，不可以传递常量实参。

(3).INOUTINOUT修饰的变量，即可在过程内部被读取，又可被设置值且值会影响到外部实参。
如果不写明参数前缀，默认前缀是IN。

4.5.存储函数与存储过程异同
(1).存储函数定义时需通过RETURNS指定返回类型，函数体中需用RETURN指定返回值；存储过程不需要。
(2).存储函数形参不支持IN，OUT，INOUT修饰；存储过程支持。
(3).存储函数只可返回一个值；存储过程可通过OUT或INOUT返回零个或多个。
(4).存储函数不允许在函数内执行单独的select（select xxx into xxx;这样利用select作为中间结果的允许）；存储过程执行过程允许支持单独的select且产生的结果集会显示在客户端。
(5).存储函数以函数调用形式调用；存储过程使用CALL形式。

5.游标
前面SELECT ... INTO ...将SELECT子句执行得到结果集中一行作为数据源赋值给INTO子句中变量时，要求SELECT子句结果集只能有一行。
为了在结果集存在多行下，访问其内容引入游标。
游标用来标记结果集中我们正访问的某一条记录。
初始下，标记结果集中的第一条记录。可以取出游标对应记录的信息。移动它，使其指向下条记录。
游标可用于存储函数，存储过程。

5.1.创建游标
如：DECLARE 游标名称 CURSOR FOR 查询语句;这样

CREATE PROCEDURE cursor_demo()
BEGIN
	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;// 如存在局部变量声明，局部变量声明放最前面
END

5.2.打开和关闭游标
如：OPEN 游标名称; 如：CLOSE 游标名称;

5.3.通过游标获取记录
使用游标获取结果集中记录：FETCH 游标名 INTO 变量1, 变量2, ... 变量n;

CREATE PROCEDURE cursor_demo()
BEGIN
	DECLARE m_value INT;
	DECLARE n_value CHAR(1);
	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
	OPEN t1_record_cursor;
	FETCH t1_record_cursor INTO m_value, n_value;
	SELECT m_value, n_value;
	CLOSE t1_record_cursor;
END $

上述只能利用光标取出一行记录。

CREATE PROCEDURE cursor_demo()
BEGIN
	DECLARE m_value INT;
	DECLARE n_value CHAR(1);
	DECLARE record_count INT;
	DECLARE i INT DEFAULT 0;
	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
	SELECT COUNT(*) FROM t1 INTO record_count;
	OPEN t1_record_cursor;
	WHILE i < record_count DO
		FETCH t1_record_cursor INTO m_value, n_value;
		SELECT m_value, n_value;
		SET i = i+1;
	END WHILE;
	CLOSE t1_record_cursor;
END

上述将获取并输出表中每一行记录。
FETCH子句一方面将光标当前指向行记录内容赋值给INTO子句中变量，一方面将光标更新到下一位置。

5.4.遍历结束时的执行策略
5.3.中利用COUNT计数方式来遍历所有行。还有另一种可选方式。
当游标当前已经指向尾后位置时执行FETCH会报错。默认下，此时会停止存储函数或存储过程的执行，直接向客户端返回一个错误提示。

但，可以在存储函数或存储过程中事先声明一种针对某种错误的处理方式。这样在存储函数或存储过程执行期间若触发了此类错误，将采用事先声明的错误处理方式去处理。
针对FETCH获取不到记录时清空：DECLARE CONTINUE HANDLER FOR NOT FOUND 处理语句;
处理语句可以是简单的一条语句，也可是由BEGIN ... END包裹的多条语句。

CREATE PROCEDURE cursor_demo()
BEGIN
	DECLARE m_value INT;
	DECLARE n_value CHAR(1);
	DECLARE done INT DEFAULT 0;
	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
	DECLARE CONTINUE HANDLER FOR NOT FOUNT SET done = 1;
	OPEN t1_record_cursor;
	flag:LOOP
		FETCH t1_record_cursor INTO m_value, n_value;
		IF done = 1 THEN
			LEAVE flag;
		END IF;
		SELECT m_value, n_value, done;
	END LOOP flag;
	CLOSE t1_record_cursor;
END

上述过程也可完成SELECT子句结果集所有行的遍历。可以看出FETCH执行出错类似高级语言抛异常，DECLARE CONTINUE HANDLER类似高级语言捕获指定类型异常。异常捕获处理后将继续执行异常语句下条语句。

6.触发器

比方说，有下列需求：
(1).向t1表插入或更新数据之前自动对数据进行校验，要求m1列的值必须在1~10之间：
a.如插入的记录的m1列的值小于1，则按1插入。
b.如m1列的值大于10，则按10插入。
(2).在向t1表中插入记录之后自动把这条记录插入到t2表。
即在对表中记录进行增，删，改操作的前和后，都可能需让MySQL服务器自动执行一些额外的语句，这就是所谓的触发器的应用场景。

6.1.创建触发器

CREATE TRIGGER 触发器名
{BEFORE | AFTER}
{INSERT | DELETE | UPDATE}
ON 表名
FOR EACH ROW
BEGIN
	触发器内容
END

上述由{}包裹，内部用|分隔的语句，表示必须在给定的选项中选一个。

名称	描述
BEFORE	表示在具体的语句执行之前就开始执行触发器的内容
AFTER	表示在具体的语句执行后才开始执行触发器的内容

{INSERT | DELETE | UPDATE}表示对哪种语句设置触发器。
FOR EACH ROW BEGIN ... END表示对语句影响的每一条记录执行自定义的触发器内容：
(1).对INSERT，FOR EACH ROW针对的是准备插入的新记录。
(2).对DELETE和UPDATE，FOR EACH ROW针对的是符合WHERE条件的那些记录（没有WHERE则为全部记录）。

针对每一条受影响的记录，MySQL提供了NEW和OLD分别代表新记录和旧记录，它们在不同的语句中含义不同：
(1).对INSERT，NEW代表准备插入的记录，OLD无效。
(2).对DELETE，OLD代表删除前的记录，NEW无效。
(3).对UPDATE，NEW代表修改后的记录，OLD代表修改前的记录。

DELIMITER $
CREATE TRIGGER bi_t1
BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
	IF NEW.m1 < 1 THEN
		SET NEW.m1 = 1;
	ELSEIF NEW.m1 > 10 THEN
		SET NEW.m1 = 10;
	END IF;
END $
DELIMITER ;

6.2.查看和删除触发器
查看触发器，如：SHOW TRIGGERS; 查看某个触发器的定义，如：SHOW CREATE TRIGGER 触发器名; 删除触发器，如：DROP TRIGGER 触发器名;

6.3.触发器使用注意事项
(1).触发器内容中不能有输出结果集的语句

DELIMITER $
CREATE TRIGGER ai_t1
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
	SELECT NEW.m1, NEW.n1; // err
END $

(2).触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改。

DELIMITER $
CREATE TRIGGER bu_t1
BEFORE UPDATE ON t1
BEGIN
	SET OLD.m1 = 1;// err
END $

(3).在BEFORE触发器中，可使用"SET NEW.列名 = 某个值"的形式来更改待插入记录或待更新记录的某个列的值，但这种操作不能在AFTER触发器中使用，因为执行AFTER触发器时记录已经被插入完成或更新完成了。

DELIMITER $
CREATE TRIGGER ai_t1
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
	SET NEW.m1 = 1;// err
END $

7.事件
有时想让MySQL服务器在某个时间点或每隔一段时间自动执行一些语句，这就需要去创建一个事件。
7.1.创建事件

CREATE EVENT 事件名
ON SCHEDULE
{
	AT 某个确定的时间点 |
	EVERY 期望的时间间隔 [STARTS 开始日期和时间] [END 结束日期和时间]
}
DO
BEGIN
	具体的语句
END

事件支持两种类型的自动执行方式：在某个确定的时间点执行；每隔一段时间执行一次。

(1).在某个确定的时间点执行。

CREATE EVENT insert_t1_event
ON SCHEDULE
AT '2021-09-04 15:48:54'
DO
BEGIN
	INSERT INTO t1(m1, n1) VALUES (6, 'f');
END

上述除了'2021-09-04 15:48:54'这样的形式，这样DATE_ADD(NOW(), INTERVAL 2 DAY)也可以。
(2).每隔一段时间执行一次

CREATE EVENT insert_t1_event
ON SCHEDULE
EVERY 1 HOUR
DO 
BEGIN
	INSERT INTO t1(m1, n1) VALUES (6, 'f');
END

在EVERY场景下，也可指定事件开始执行的日期和时间和停止执行的日期和时间。

CREATE EVENT insert_t1_event
ON SCHEDULE
EVERY 1 HOUR STARTS '2021-09-04 15:48:54' ENDS '2021-09-16 15:48:54'
DO 
BEGIN
	INSERT INTO t1(m1, n1) VALUES (6, 'f');
END

时间间隔单位除了HOUR，还可用YEAR，QUARTER，MONTH，DAY，HOUR，MINUTE，WEEK，SECOND，YEAR_MONTH，DAY_HOUR，DAY_MINUTE，DAY_SECOND，HOUR_MINUTE，HOUR_SECOND，MINUTE_SECOND这些单位。

定时执行事件功能需要用SET GLOBAL event_scheduler = ON;开启后才可用。

7.2.查看和删除事件
查看事件，如：SHOW EVENTS; 查看事件定义，如：SHOW CREATE EVENT 事件名; 删除事件，如：DROP EVENT 事件名;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。