MySQL 语法整理（5）——函数，存储过程以及触发器

本篇讲解函数，存储过程和触发器的创建和基本使用。

MySQL的函数用于实现一定功能，受返回参数只能单个的限制，实现的功能相对有限。

/*返回单个查询值的函数，MySQL 只能使用这种函数
给定一个系的名字,返回该系的教师人数*/
USE university; 
SET GLOBAL log_bin_trust_function_creators = 1; -- 没有开启主从复制 
DELIMITER // -- 设置命令段的结束符号为// 
CREATE FUNCTION dept_count( dept_name VARCHAR(20)) -- 函数的参数
	RETURNS INT /*返回值类型*/ 
	BEGIN	
		DECLARE d_count INT;  /*声明参数及其类型*/ 
		SELECT COUNT(*) 
		FROM instructor 
		WHERE instructor.dept_name = dept_name
		INTO d_count; /*给参数赋值*/ 
		RETURN d_count; /*返回单个查询值的函数，MySQL 只能使用这种函数*/
	END;// 
DELIMITER ;

/*调用函数*/
SELECT dept_count('Comp. Sci.');

-- 这是另一种方式，将函数的结果装入@ss中
SELECT dept_count('Comp. Sci.') INTO @ss; 
SELECT @ss

/*这里提供了一种提供表记录放入函数的操作
返回教师数大于 1 的所有系的名称和预算*/
SELECT dept_name,budget 
FROM department 
WHERE dept_count(department.`dept_name`) > 1;

刚刚我们在编写函数中，提到了变量的定义，接下来就说一下变量的定义。declare：定义局部变量。set：给变量赋值。

/*declare 语句用来定义局部变量，可以使用 default 来说明默认值，其作用
域仅限于该语句块，常用于函数、存储过程或触发器中。*/
DECLARE age INT DEFAULT 0; -- 直接执行会报错
SET age = 18;

/*mysql 用户变量，mysql 中用户变量不用提前申明，直接用“@变量名”使用
就可以了，其作用域为当前连接。*/
SET @age=19;

/*全局变量影响服务器整体操作。当服务启动时，它将所有全局变量初始化为
默认值。要想更改全局变量，必须具有 super 权限。@@表示全局变量。*/
SET GLOBAL SQL_WARNINGS = ON; -- global 不能省略 
-- 或者另一种写法
SET @@global.sql_warnings = OFF -- 一旦开机就有该变量存在

delimiter：重新定义结束符。

/*重新定义命令结束符号
SQL 默认的结束符为分号;*/
DELIMITER // -- 设置命令段的结束符号为 //
DELIMITER ; -- 最后记得将结束符换回;

接下来再说一下MySQL常用的函数。

字符串函数： CONCAT(), LOWER(),UPPER(),LEFT(),TRIM(),REPEAT()
数值函数： ABS(),CEIL(),FLOOR(),MOD(),RAND() 
日期时间函数： CURDATE(),NOW(),YEAR()
系统变量： DATABASE(),VERSION(),USER() ,MD5()

接下来我们要将MySQL中的存储过程，其实存储过程与函数类似，我们先来讲一下两者的区别。

一、含义不同

1、存储过程：存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。

2、函数：是由一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用。 函数限制比较多，如不能用临时表，只能用表变量等

二、使用条件不同

1、存储过程：可以在单个存储过程中执行一系列 SQL 语句。而且可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

2、函数：自定义函数诸多限制，有许多语句不能使用，许多功能不能实现。函数可以直接引用返回值，用表变量返回记录集。 但是，用户定义函数不能用于执行一组修改全局数据库状态的操作。

三、执行方式不同

1、存储过程：存储过程可以返回参数，如记录集，函数只能返回值或者表对象。存储过程的参数有in，out，inout三种，存储过程声明时不需要返回类型。

2、函数：函数参数只有in，而函数需要描述返回类型， 且函数中必须包含一个有效的return语句。

了解了存储过程后，我们大概知道，函数只能完成简单的操作，而存储过程可以完成对多个表的操作。

/*给定一个系的名字,返回该系的教师人数。*/
DROP PROCEDURE IF EXISTS dept_count_p; 
DELIMITER // 
CREATE PROCEDURE dept_count_p(IN dept_name VARCHAR(20),OUT d_count INT ) 
/*IN 表示输入的参数，OUT 表示输出的参数，输出参数名字为d_count*/
BEGIN 
	SELECT COUNT(*) INTO d_count 
	FROM instructor 
	WHERE instructor.dept_name=dept_name ; 
END;// 
DELIMITER ;

/*直接调用存储过程：在数据库服务器端执行存储过程*/
CALL dept_count_p('Comp. Sci.',@d_count); -- 调用后输出的值为d_count，引用要加@
SELECT @d_count

/*在函数中调用存储过程*/
DELIMITER // 
CREATE FUNCTION test(dept_name VARCHAR(20)) RETURNS VARCHAR(100) 
BEGIN 
	CALL dept_count_p(dept_name,@d_number); -- 调用存储过程 
	IF @d_number >= 1 THEN 
		RETURN CONCAT('the number of this department teacher is: ',
		CAST(@d_number AS CHAR)); -- 连接两个字符串 
	ELSE 
		RETURN 'this department teacher is 0'; 
	END IF; 
END;// 
DELIMITER ;

/*调用函数*/
SELECT test('Music');

存储过程除了可以对多个表进行操作，还可以通过T-SQL语言（类似于python）实现一些功能

/*用 T-SQL 语言完成 1+2+3……+n，并打印出结果*/
DELIMITER // 
CREATE PROCEDURE MY_Sum(IN n INT, OUT result INT) 
BEGIN 
	DECLARE i INT DEFAULT 1; 
	DECLARE SUM INT DEFAULT 0; 
	WHILE i <= n DO 
		SET SUM = SUM + i;  -- 每一个变量的赋值都要用set
		SET i = i + 1; 
	END WHILE; 
	SET result = SUM; 
END;// 
DELIMITER ;

/*调用存储过程*/
CALL my_sum(50,@result); 
SELECT @result;

/*存储过程相较于函数，最大不同就是不能被其他调用，而函数可以。
一般用函数实现单一功能，然后用存储结构实现一段业务逻辑（报名，交班费，订单入库）*/

触发器：可以理解成在执行某些操作后就执行的程序，比如当修改某个表的数据时，我可以创建一个触发器来把修改前的数据记录到另外一个表中。

触发器涉及多个参数：

• 对于INSERT语句, 只有NEW是合法的
• 对于DELETE语句，只有OLD才合法
• 对于UPDATE语句，NEW、OLD可以同时使用

/*为触发器创建两个实验表*/
USE university; 
CREATE TABLE test1(id VARCHAR(5)PRIMARY KEY ); -- 创建表 
CREATE TABLE test2(id VARCHAR(5)PRIMARY KEY ); -- 创建表

/*创建触发器 tr1,触发事件为 INSERT，作用是记录tr1新加入的id*/
DELIMITER // -- 设置命令段的结束符号为//，不加上这个可能会报错 
CREATE TRIGGER tr1 
AFTER INSERT ON student -- 触发时间是在插入student表后
FOR EACH ROW -- mysql不支持语句触发器,所以必须写foreachrow
BEGIN 
	INSERT INTO test1 VALUES(New.ID); -- 将新记录的 id 值插入 test1 表 
END;//

/*创建触发器 tr2,触发事件为 update*/
DELIMITER //
CREATE TRIGGER tr2 
AFTER UPDATE ON student -- 触发时间是在更新student表之后
FOR EACH ROW 
BEGIN 
	INSERT INTO test2 VALUES(OLD.ID); -- 将修改之前的 id 值插入 test2 表 
END;// 
DELIMITER ;

-- 测试触发器tr1
INSERT INTO student VALUES('s0001','Jack','Music',NULL);
-- 测试触发器tr2
UPDATE student SET NAME='Mary' WHERE id='s0001';

/*设置一个触发器tr3，使得检测新输入section表time_slot_id必须已经存在，否则
就报错*/
DELIMITER // 
CREATE TRIGGER tr3 
BEFORE INSERT ON section  -- 这里的操作时间为before，即在插入之前
FOR EACH ROW 
BEGIN 
	DECLARE P_CNT INT; 
	SELECT COUNT(*) INTO P_CNT 
	FROM time_slot 
	WHERE time_slot_id = NEW.time_slot_id; 
	-- 条件不满足，不能在 section 表中插入记录，需要处理新记录 
	IF (P_CNT <= 0) THEN 
		SET new.time_slot_id=''; -- 设置为空值 
		-- 故意执行错误语句，让系统报错，自动回滚新记录 
		INSERT INTO xxxx VALUES (1); 
	END IF; 
END; // 
DELIMITER ;

接下来测试触发器三

-- 先查看原来表
SELECT * FROM section; 
-- 插入一条错误记录
INSERT INTO section VALUES ('CS-321', '2', 'Spring', '2014', 'Taylor', '3128', 'J'); 
-- 发现报错后该记录没有插入
SELECT * 
FROM section 
WHERE course_id='CS-321';