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 不能省略
java有协程的概念吗-- 或者另⼀种写法
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 //
mysql查看所有存储过程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
dialogue反义词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';
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论