MySQL：存储过程，函数，触发器

最新推荐文章于 2022-04-23 19:45:51 发布

阅读量575

点赞数 2

分类专栏： # MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/weixin_45730091/article/details/103401705

版权

MySQL 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

存储过程,函数的介绍

MySQL从5.0版本开始支持存储过程和函数。

存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合，然后直接通知调用执行即可,所以调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有， 存储过程的参数可以使用IN，OUT，INOUT类型，而函数的参数只能是IN类型的。

创建，删除，修改存储过程或者函数都需要权限，例如创建存储过程或者函数需要CREATEROUNTINE权限，修改或者删除存储过程或者函数需要ALTERROUTINE权限，执行存储过程或者函数需要EXECUTE权限。
小结

存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合
减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的
存储过程的操作要确定是否有权限

一个简单的存储过程

测试数据

CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR ( 20 ),
age INT,
sex VARCHAR ( 5 ),
address VARCHAR ( 100 ),
math INT,
english INT 
);
INSERT INTO student ( NAME, age, sex, address, math, english )
VALUES
	( '马云', 55, '男', '杭州', 66, 78 ),
	( '马化腾', 45, '女', '深圳', 98, 87 ),
	( '马景涛', 55, '男', '香港', 56, 77 ),
	( '柳岩', 20, '女', '湖南', 76, 65 ),
	( '柳 青', 20, '男', '湖南', 86, NULL ),
	( '刘德华', 57, '男', '香港', 99, 99 ),
	( '马德', 22, '女', '香港', 99, 99 ),
	( '德玛西 亚', 18, '男', '南京', 56, 65 );

CREATE TABLE users ( 
id INT PRIMARY KEY AUTO_INCREMENT, 
NAME VARCHAR ( 20 ), 
address VARCHAR ( 30 ) DEFAULT '广州' 
);

-- 添加一条记录,不使用默认地址
INSERT INTO users ( NAME, address ) VALUES ( '李四', '广州' );
INSERT INTO users ( NAME, address ) VALUES ( '王五', '广州' );

一个简单的存储过程

-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE testa ( ) BEGIN
  SELECT * FROM student WHERE id = 2;
END $$ 

-- 调用存储过程
CALL testa ( );

小结
存储过程：

创建格式：create procedure 存储过程名
包含一个以上代码块，代码块使用begin和end 之间
在命令行中创建需要定义分隔符 delimiter $$
存储过程调用使用call命令

存储过程的特点：

能完成较复杂的判断和运算，而且处理逻辑都封装在数据库端，调用者不需要自己处理业务逻辑，一旦逻辑发
生变化，只需要修改存储过程即可，而对调用者程序完全没有影响。
可编程性强，灵活
SQL编程的代码可重复使用
执行速度相对快一些
减少网络之间数据传输，节省开销

存储过程的删除,查看操作

删除存储过程/函数

-- 删除存储过程 
DROP PROCEDURE testa1; 
-- 删除函数 
DROP FUNCTION testa1;

查看存储过程或者函数

-- 查看存储过程或者函数的状态 
SHOW PROCEDURE STATUS LIKE 'testa'; 
-- 查看存储过程或者函数的定义 
SHOW CREATE PROCEDURE testa；

存储过程的变量

需求1: 编写存储过程，使用变量取id=2的用户名.

DELIMITER $$
CREATE PROCEDURE testa3 ( ) 
BEGIN
DECLARE my_uname VARCHAR ( 32 ) DEFAULT '';
SET my_uname = 'javakf';
SELECT NAME INTO my_uname FROM student WHERE id = 2;
SELECT my_uname;
END $$ 

CALL testa3 ( );

小结

变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用
变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能指定默认值、字符集和排序规则等
变量可以通过set来赋值，也可以通过select into的方式赋值
变量需要返回，可以使用select语句,如：select 变量名

需求2：统计表users,student的行数量和student表中英语最高分，数学最高分的注册时间。

DELIMITER $$
CREATE PROCEDURE stats_users_students5 ( ) 
BEGIN
	BEGIN
		DECLARE users_sum INT DEFAULT 0;
		DECLARE students_sum INT DEFAULT 0;
		SELECT COUNT( * ) INTO users_sum FROM users;
		SELECT COUNT( * ) INTO students_sum FROM student;
		SELECTusers_sum,students_sum;
	END;
	BEGIN
		DECLARE max_math INT;
		DECLARE max_english INT;
		SELECT MAX( math ),MAX( english ) INTO max_math,max_english FROM student;
		SELECT users_sum,students_sum,max_math,max_english;
	END;
END;
$$ 

CALL stats_users_students5 ( );

小结

变量是有作用域的，作用范围在begin与end块之间，end结束变量的作用范围即结束。
需要多个块之间传递值，可以使用全局变量，即放在所有代码块之前。
传参变量是全局的，可以在多个块之间起作用

存储过程的传入参数IN

需求：编写存储过程，传入id，返回该用户的name

-- 需求：编写存储过程，传入id，返回该用户的name
DELIMITER $$
CREATE PROCEDURE getName ( my_uid INT ) 
BEGIN
	DECLARE my_uname VARCHAR ( 32 ) DEFAULT '';
	SELECT NAME INTO my_uname FROM student WHERE id = my_uid;
	SELECT my_uname;
END;
$$ 

CALL getName ( 2 );

小结

传入参数：类型为IN,表示该参数的值必须在调用存储过程时指定，如果不显式指定为IN，那么默认就是IN类型。
IN类型参数一般只用于传入，在调用存储过程中一般不作修改和返回
如果调用存储过程中需要修改和返回值，可以使用OUT类型参数

存储过程的传出参数OUT

需求：调用存储过程时，传入uid返回该用户的uname

-- 需求：调用存储过程时，传入uid返回该用户的uname
DELIMITER $$
CREATE PROCEDURE getName22 ( IN my_uid INT, OUT my_uname VARCHAR ( 32 ) ) 
BEGIN
	SELECT NAME INTO my_uname FROM student WHERE id = my_uid;
	SELECT my_uname;
END;
$$ 
SET @uname := '';
CALL getName22 ( 2, @uname );
SELECT @uname AS myName;

小结

传出参数：在调用存储过程中，可以改变其值，并可返回
OUT是传出参数，不能用于传入参数值
调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量
如果既需要传入，同时又需要传出，则可以使用INOUT类型参数

存储过程的可变参数INOUT

需求：调用存储过程时，参数my_uid和my_uname,既是传入，也是传出参数

-- 需求：调用存储过程时，参数my_uid和my_uname,既是传入，也是传出参数
DELIMITER $$
CREATE PROCEDURE getName33 ( INOUT my_uid INT, INOUT my_uname VARCHAR ( 32 ) ) 
BEGIN
	SET my_uid = 2;
	SET my_uname = 'hxf3';
	SELECT id,NAME INTO my_uid,my_uname FROM student WHERE id = my_uid;
	SELECT my_uid,my_uname;
END;
$$ 
SET @uname := '';
SET @uid := 0;
CALL getName33 ( @uid, @uname );
SELECT @uname AS myName;

小结

可变变量INOUT：调用时可传入值，在调用过程中，可修改其值，同时也可返回值。
INOUT参数集合了IN和OUT类型的参数功能
INOUT调用时传入的是变量，而不是常量

存储过程条件语句

存储过程的条件语句
需求：编写存储过程，如果用户uid是偶数则就给出uname,其它情况只返回uid

-- 需求：编写存储过程，如果用户uid是偶数则就给出uname,其它情况只返回uid
DELIMITER $$
CREATE PROCEDURE getName44 ( IN my_uid INT )
BEGIN
	DECLARE my_uname VARCHAR ( 32 ) DEFAULT '';
	IF( my_uid % 2 = 0 ) 
	THEN
		SELECT NAME INTO my_uname FROM student WHERE id = my_uid;
		SELECT my_uname;
	ELSE 
		SELECT my_uid;
	END IF;
END;
$$ 
CALL getName44 ( 1 );
CALL getName44 ( 2 );
-- 1.条件语句最基本的结构：if() then …else …end if; 
-- 2.If判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式

存储过程的条件语句应用示例
需求：根据用户传入的uid参数判断：
（1）如果状态status为1，则给用户score加10分
（2）如果状态status 为2，则给用户score加20分
（3）其它情况加30分

DELIMITER $$
CREATE PROCEDURE addscore1 ( IN my_uid INT ) 
BEGIN
	DECLARE my_status INT DEFAULT 0;
	SELECT STATUS INTO my_status FROM student WHERE id = my_uid;
	IF( my_status = 1 ) 
	THEN
		UPDATE student SET math = math + 10,english = english + 10 WHERE id = my_uid;
	ELSEIF ( my_status = 2 ) 
	THEN
		UPDATE student SET math = math + 20,english = english + 20 WHERE id = my_uid;
	ELSE     
		UPDATE student SET math = math + 30,english = english + 30 WHERE id = my_uid;
	END IF;
END;
$$ 
CALL addscore1 ( 1 );

存储过程循环语句

while循环
需求：使用循环语句，向表uesrs中插入10条uid连续的记录。

-- 需求：使用循环语句，向表student(uid)中插入10条uid连续的记录。
DELIMITER $$  
CREATE PROCEDURE insertdata ( )   
BEGIN
	DECLARE i INT DEFAULT   0;
	WHILE( i < 10 ) DO
		BEGIN
			SELECT i;
			SET i = i + 1;
		  INSERT INTO users ( NAME, address )VALUES( "孙悟空", "广州" );
		END;
	END WHILE;
END;
$$ 
CALL insertdata ( );
-- 1.while语句最基本的结构：while() do…end while;  
-- 2.while判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式

repeat循环语句
需求：使用repeat循环向表users插入10条uid连续的记录

DELIMITER $$
CREATE PROCEDURE insertdata2 ( ) 
BEGIN
	DECLARE i INT DEFAULT   100;
	REPEAT
		  BEGIN
		    SELECT i;
				SET i = i + 1;
	    	INSERT INTO users ( NAME )VALUES( '黑马' );
			END;
		UNTIL i >= 110 
	END REPEAT;
END;
$$ 
CALL insertdata3 ( );
-- 1.repeat语句最基本的结构：repeat…until …end REPEAT; 
-- 2.until判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式   只有当until语句为真时，循环结束。

光标的使用

在存储过程和函数中，可以使用光标（有时也称为游标）对结果集进行循环的处理，光标的使用包括了：

光标的申明
OPEN
FETCH
CLOSE

需求：编写存储过程，使用光标，把id为偶数的记录逐一更新用户名。

-- 编写存储过程，使用光标，把id为偶数的记录逐一更新用户名。
DELIMITER $$
CREATE PROCEDURE testcursor ( ) 
BEGIN
	DECLARE stopflag INT DEFAULT   0;
	DECLARE my_uname VARCHAR ( 20 );
	DECLARE uname_cur CURSOR FOR SELECT NAME FROM student WHERE id % 2 = 0;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag = 1;
	OPEN uname_cur;-- 打开游标
	FETCH uname_cur INTO my_uname;-- 游标向前走一步，取出一条记录放到变量my_uname中。
	WHILE
		( stopflag = 0 ) DO -- 如果游标还没有到结尾，就继续  
		BEGIN
			UPDATE student SET NAME = CONCAT( my_uname, '_cur' ) WHERE NAME = my_uname;
			FETCH uname_cur INTO my_uname;
		END;
	END WHILE;
	CLOSE uname_cur;
END;
$$
DELIMITER;
-- 注意 :变量，条件，处理程序，光标，都是通过 DECLARE定义的，它们之间是有先后顺序要求的，变量和条件必须在最 前面声明，然后才能是光标的申明，最后才可以是处理程序的申明。

简单的自定义函数

需求：编写函数，传入一个用户uid，返回用户的uname

-- 需求：编写函数，传入一个用户uid，返回用户的uname
DELIMITER $$
CREATE FUNCTION getFName1 ( my_uid INT ) RETURNS VARCHAR ( 32 ) READS SQL DATA # READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。
BEGIN
	DECLARE my_uname VARCHAR ( 32 ) DEFAULT '';
	SELECT NAME INTO my_uname FROM student WHERE id = my_uid;
	RETURN my_uname;
END;
$$ 
SELECT getFName1 ( 3 );
-- 1.创建函数使用 CREATE FUNCTION 函数名（参数 ） RETURNS 返回类型 
-- 2.函数体放在 BEGIN和END之间 
-- 3.RETURN指定函数的返回值 
-- 4.函数调用 : SELECT getuname ( )

自定义函数综合应用

需求：输入用户ID，获得address, id, name组合的UUID值，在游戏中作为用户的唯一标识

-- 需求：输入用户ID，获得address, id, name组合的UUID值， 
-- 在全区游戏中作为用户的唯一标识
DELIMITER $$
CREATE FUNCTION getuuid2 ( my_uid INT ) RETURNS VARCHAR ( 30 ) CHARSET utf8 READS SQL DATA # READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。
BEGIN
	DECLARE UUID VARCHAR ( 30 ) DEFAULT '';
	SELECT CONCAT( address, id, NAME ) INTO UUID FROM student WHERE id = my_uid;
	RETURN UUID;
END;
$$ 
SELECT getuuid2 ( 2 );

需求：输入参数id，查询全部学生中数学分数高于id学生数学成绩的总和

-- 需求：输入参数id，查询出数学成绩高于该参数的学生的数学成绩总和
DELIMITER $$
CREATE FUNCTION mathAll ( my_uid INT ) RETURNS INT READS SQL DATA 
BEGIN
	DECLARE math_id INT DEFAULT 0;
	DECLARE math_all INT DEFAULT 0;
	SELECT math INTO math_id FROM student WHERE id = my_uid;
	SELECT SUM( math ) INTO math_all FROM student WHERE math > math_id;
	RETURN math_all;
END;
$$ 
SELECT mathAll ( 2 );

MySQL触发器

什么是触发器
Mysql从5.0.2开始支持触发器功能，触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合、触发器这种特性可以协助应用在数据库端确定数据的完整性。接下来以需求的方式演示。

需求：出于审计目的，当有人往表users插入一条记录时，把插入的uid,uname,插入动作和操作时间记录下来。

-- 出于审计目的，当有人往表users插入一条记录时，把插入的uid,uname,插入动作和操作时间记录下来。
DELIMITER $$
CREATE TRIGGER tr_users_insert AFTER INSERT ON users   
FOR EACH ROW
BEGIN
	    INSERT INTO oplog ( uid, uname, ACTION, optime )VALUES( NEW.uid, NEW.uname, 'insert', NOW( ) );
END;
$$
-- 1.创建触发器使用create TRIGGER 触发器名 
-- 2.什么时候触发？After INSERT ON users,除了after还有before, 是在对表操作之前(BEFORE)或者之后(AFTER)触发动作的。 
-- 3.对什么操作事件触发？after INSERT ON users ，操作事件包括insert,UPDATE,DELETE 
-- 4.对什么表触发？after INSERT ON users   
-- 5.影响的范围？For EACH ROW

需求：出于审计目的，当删除users表时，记录删除前该记录的主要字段值

Delimiter $$
CREATE TRIGGER tr_users_delete BEFORE DELETE ON users   
FOR EACH ROW
BEGIN
	INSERT INTO oplog ( uid, uname, action, optime, old_value, new_value )VALUES( OLD.uid, OLD.uname, 'delete', now( ), OLD.regtime,OLD.regtime );
END;
$$

事件调度器（EVENT-SCHEDULE）

事件调度器是MySQL中提供的可做定时操作处理，或者周期操作处理的一个对象。
事件调度器的使用如下：
先确认是否开启了事件调度的支持，事件调度器开启后就可以定义时间调度器使用了

show variables like '%event_scheduler%'; 
set global event_scheduler =on;

事件调度器的入门案例

DELIMITER $$
CREATE EVENT IF NOT EXISTS event_hello 
ON SCHEDULE EVERY 3 SECOND 
ON COMPLETION PRESERVE 
	DO
	    BEGIN
	        INSERT INTO users ( NAME, address )VALUES( '王五', '广州' );
  
			END $$
DELIMITER;
-- CREATE EVENT IF NOT EXISTS event_hello  创建使用 CREATE EVENT 
-- ON SCHEDULE EVERY 3 MINUTE  说明什么时候执行，多长时间执行一次 
-- ON COMPLETION PRESERVE  调度计划执行完成后是否还保留 
-- DO SQL;    这个调度计划要做什么？

事件调度器计划示例

单次计划任务示例： 
On schedule at ‘2016-12-12 04:00:00’ 在 2016-12-12 04:00:00执行一次 
 
重复计划任务 
on schedule every 1 scond 每秒执行一次 
on schedule every 1 minuter 每分钟执行一次 
on schedule every 1 day 每天执行一次 
 
指定时间范围的重复计划任务 
on schedule every 1 day starts ‘2016-12-12 20:20:20’ 每天在20：20：20执行一次 
on schedule every 1  minute  starts ‘2016-12-12 9:00:00’  ends ‘2016-12-12 11:00:00’

综合案例

设计一个福彩3D的开奖存储过程，每3分钟开奖一次。

第一步：创建一张奖号表，存储三个中奖号码，以及生成时间。
第二步：定义一个存储过程，随机生成3个中奖号码存入到奖号表中。
第三步：做一个事件调度器，每隔3s调用一次存储过程。

-- 创建奖号的存储操作
CREATE TABLE lucky_num ( 
id INT PRIMARY KEY AUTO_INCREMENT, 
num1 INT, 
num2 INT, 
num3 INT, 
ctime DATETIME 
)

-- 创建一个存储过程，负责生成3个随机号码给表存储
DELIMITER $$
CREATE PROCEDURE create_lucky_num ( ) 
BEGIN
  INSERT INTO lucky_num ( num1, num2, num3, ctime )   
	SELECT FLOOR( RAND( ) * 9 ) + 1,FLOOR( RAND( ) * 9 ) + 1,FLOOR( RAND( ) * 9 ) + 1,NOW( );
END $$ 

-- 开启事件调度器
DELIMITER $$
CREATE EVENT IF NOT EXISTS create_lucky_num 
	ON SCHEDULE EVERY 3 SECOND 
	ON COMPLETION PRESERVE DO
	    BEGIN
		      CALL create_lucky_num;
			END $$
DELIMITER;

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL：存储过程，函数，触发器

存储过程,函数的介绍MySQL从5.0版本开始支持存储过程和函数。存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合，然后直接通知调用执行即可,所以调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN，OUT，INOUT...
复制链接

扫一扫