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

1.存储过程简介

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

存储过程的优点：

(1).增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

2.MySQL 存储过程

2.1MySQL 存储过程的创建

语法

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体DELIMITER //CREATE PROCEDURE myproc()BEGINSELECT COUNT(*)  FROM student;END//
DELIMITER ;

分隔符

MySQL默认以;为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用DELIMITER //声明当前段分隔符，让编译器把两个//之间的内容当做存储过程的代码，不会执行这些代码。DELIMITER ;的意为把分隔符还原。

参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用,分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

• IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
• OUT: 该值可在存储过程内部被改变，并可返回
• INOUT: 调用时指定，并且可被改变和返回

过程体

过程体的开始与结束使用BEGIN与END进行标识。

IN参数例子

DELIMITER //CREATE PROCEDURE in_param(IN p_in int)BEGINSELECT p_in;SET p_in=2;SELECT p_in;END;//
DELIMITER ;#调用
SET @p_in=1;
CALL in_param(@p_in);SELECT @p_in;

执行结果：

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

OUT参数例子

DELIMITER //CREATE PROCEDURE out_param(OUT p_out int)BEGINSELECT p_out;SET p_out=2;SELECT p_out;END;//
DELIMITER ;
#调用
SET @p_out=1;
CALL out_param(@p_out);SELECT @p_out;

执行结果：

INOUT参数例子

DELIMITER //CREATE PROCEDURE inout_param(INOUT p_inout int)BEGINSELECT p_inout;SET p_inout=2;SELECT p_inout;END;//
DELIMITER ;
#调用
SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;

执行结果：

2.3.变量

1. 先通过一个简单的例子来学习变量的声明和赋值

create procedure test2()
begin-- 使用 declare语句声明一个变量declare username varchar(32) default '';-- 使用set语句给变量赋值set username='xiaoxiao';-- 将users表中id=1的名称赋值给usernameselect name into username from users where id=1;-- 返回变量select username;
end;

2. 概括

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

2.4.变量的作用域

1. 变量作用域说明：

• 存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束
• 需要多个块之间传值，可以使用全局变量，即放在所有代码块之前
• 传参变量是全局的，可以在多个块之间起作用

2. 通过一个实例来验证变量的作用域
   需求: 创建一个存储过程，用来统计表users、orders表中行数数量和orders表中的最大金额和最小金额

create procedure test3()
beginbegindeclare userscount int default 0; -- 用户表中的数量declare ordercount int default 0; -- 订单表中的数量select count(*) into userscount from users;select count(*) into ordercount from orders;select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量end;begin declare maxmoney int default 0; -- 最大金额declare minmoney int default 0; -- 最小金额select max(money) into maxmoney from orders;select min(money) into minmoney from orders;select maxmoney,minmoney; -- 返回最金额、最小金额end;
end;

调用以上存储过程，结果如图（3）和图（4）：

（3）

（4）

3. 我将过程test(3)改为如下：

create procedure test3()beginbegindeclare userscount int default 0; -- 用户表中的数量declare ordercount int default 0; -- 订单表中的数量select count(*) into userscount from users;select count(*) into ordercount from orders;select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量end;begin declare maxmoney int default 0; -- 最大金额declare minmoney int default 0; -- 最小金额select max(money) into maxmoney from orders;select min(money) into minmoney from orders;select userscount,ordercount，maxmoney,minmoney; -- 返回最金额、最小金额end;end;

再次调用call test3(); 会报错如图（5）：

图（5）

4. 将userscount,ordercount改为全局变量，再次验证

create procedure test3()begindeclare userscount int default 0; -- 用户表中的数量declare ordercount int default 0; -- 订单表中的数量beginselect count(*) into userscount from users;select count(*) into ordercount from orders;select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量end;begin declare maxmoney int default 0; -- 最大金额declare minmoney int default 0; -- 最小金额select max(money) into maxmoney from orders;select min(money) into minmoney from orders;select userscount,ordercount，maxmoney,minmoney; -- 返回最金额、最小金额end;end;

再次调用call test3(); 不会报错如图（6）和图（7）：

图（6）

图（7）
因此，存储过程中变量的作用域，作用范围在begin和end块之间，end结束变量的作用范围即结束

2.5.存储过程条件语句

1. 基本结构

• (1) 条件语句基本结构：

if() then...else...end if;

• (2) 多条件判断语句：

if() then...
elseif() then...
else ...
end if;

2. 实例
   实例1：编写存储过程，如果用户userId是偶数则返回username,否则返回userId

create procedure test7(in userId int)
begindeclare username varchar(32) default '';if(userId%2=0)then select name into username from users where id=userId;select username;elseselect userId;end if;
end;

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

    create procedure test8(in userid int)begindeclare my_status int default 0;select status into my_status from users where id=userid;if(my_status=1)then update users set score=score+10 where id=userid;elseif(my_status=2)then update users set score=score+20 where id=userid;else update users set score=score+30 where id=userid;end if;end;

2.6.存储过程循环语句

1. while语句

• while语句的基本结构

while(表达式) do ......  
end while;

• 实例
  需求：使用循环语句，向表test1(id)中插入10条连续的记录

    create procedure test9()begindeclare i int default 0;while(i<10) do begin select i;set i=i+1;insert into test1(id) values(i);end;end while;end;

2. repeat语句

• repeat语句基本的结构：

repeat...until...end repeat;

• 示例
  需求：给test1表中的id字段插入数据，从1到10

    create procedure test10()begindeclare i int default 0;repeat begin select i;set i=i+1;insert into test1(id) values(i);end;until i>=10 -- 如果i>=10,则跳出循环end repeat;end;

• 概括：
  until判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式，只有当until语句为真是，循环结束。

2.7.存储过程游标的使用

1. 什么是游标
   游标是保存查询结果的临时区域
2. 示例
   需求：编写存储过程，使用游标，把users表中 id为偶数的记录逐一更新用户名

    create procedure test11()begindeclare stopflag int default 0;declare username VARCHAR(32);-- 创建一个游标变量，declare 变量名 cursor ...declare username_cur cursor for select name from users where id%2=0;-- 游标是保存查询结果的临时区域-- 游标变量username_cur保存了查询的临时结果，实际上就是结果集-- 当游标变量中保存的结果都查询一遍(遍历),到达结尾，将变量stopflag设置为1，用于循环中判断是否结束declare continue handler for not found set stopflag=1;open username_cur; -- 打卡游标fetch username_cur into username; -- 游标向前走一步，取出一条记录放到变量username中while(stopflag=0) do -- 如果游标还没有结尾，就继续begin -- 在用户名前门拼接 '_cur' 字符串update users set name=CONCAT(username,'_cur') where name=username;fetch username_cur into username;end;end while; -- 结束循环close username_cur; -- 关闭游标end;

3.自定义函数

函数与存储过程最大的区别是函数必须有返回值，否则会报错

1. 一个简单的函数

create function getusername(userid int) returns varchar(32)reads sql data  -- 从数据库中读取数据，但不修改数据begindeclare username varchar(32) default '';select name into username from users where id=userid;return username;end;# 运行函数：
select getusername(1)

2. 概括：

• 创建函数使用create function 函数名(参数) returns 返回类型；
• 函数体放在begin和end之间；
• returns指定函数的返回值；
• 函数调用使用select getusername()

3. 示例
   需求：根据userid，获取accoutid,id,name组合成UUID作为用户的唯一标识

create function getuuid(userid int) returns varchar(64)reads sql data  -- 从数据库中读取数据，但不修改数据begindeclare uuid varchar(64) default '';select concat(accontid,'_',id,'_',name) into uuid from users where id=userid;		return uuid;
end;select getuuid(1);

4. 触发器

1. 什么是触发器
   触发器与函数、存储过程一样，触发器是一种对象，它能根据对表的操作时间，触发一些动作，这些动作可以是insert,update,delete等修改操作。

2. 实例1

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

    create trigger tr_users_insert after insert on usersfor each row begin insert into oplog(userid,username,action,optime)values(NEW.id,NEW.name,'insert',now());end;

创建成功后，给uses表中插入一条记录：

insert into users(id,name,age,status,score,accontid)values(6,'小周',23,1,'60','10001')；

执行成功后，打开oplog表，可以看到oplog表中插入了一条记录如图(22)

2. 总结

• 创建触发器使用create trigger 触发器名
• 什么时候触发？after insert on users,除了after还有before,是在对表操作之前（before）或者之后（after）触发动作的。
• 对什么操作事件触发？ after insert on users,操作事件包括insert,update,delete等修改操作；
• 对什么表触发? after insert on users
• 影响的范围？for each row

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

    create trigger tr_users_delete before delete on usersfor each row begin insert into oplog(userid,username,action,optime)values(OLD.id,OLD.name,'delete',now());end;

删除users表中的一条记录

delete from users where id=6;

执行成功后，打开oplog表，可以看到oplog表中插入了一条记录如图（23）

5.流程控制

1. case分支

• 基本语法结构

case ...
when ... then....
when.... then....
else ... 
end case;

• 示例
  users表中，根据userid获取status值，如果status为1，则修改score为10；如果status为2，则修改为20，如果status3，则修改为30；否则修改为40。

 create procedure testcate(userid int)begin declare my_status int default 0;select status into my_status from users where id=userid;case my_statuswhen 1 then update users set score=10 where id=userid;when 2 then update users set score=20 where id=userid;when 3 then update users set score=30 where id=userid;else update users set score=40 where id=userid;end case;end;

6.存储过程+event（事件）

1. 使用存储过程+事件事件一个简单的实现福彩3D开奖
   需求：设计一个福彩的开奖过程，没3分钟开奖一次
   第一步：先编写一个存储过程open_lottery,产生3个随机数，生成一条开奖记录
   第二步：编写一个时间调度器，每3分钟调用一次这个过程

create procedure open_lottery()begin insert into lottery(num1,num2,num3,ctime)select FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,now();end;

    create event if not exists lottery_event -- 创建一个事件on schedule every  3 minute  -- on schedule 什么时候来执行，每三分钟执行一次on completion preserve do call open_lottery;

运行结果如图(25)

注意，如果event之一没有运行，请按照以下办法解决：

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

• alert event lottery_event enable;

2. 解析event的创建格式

• 基本语法

create event[IF NOT EXISTS]event_name -- 创建使用create eventON SCHEDULE schedule -- on schedule 什么时候来执行[ON COMPLETION [NOT] PRESERVE] -- 调度计划执行完成后是否还保留[ENABLE | DISABLE] -- 是否开启事件，默认开启[COMMENT 'comment'] -- 事件的注释DO sql_statement; -- 这个调度计划要做什么？

• 执行时间说明

3. 单次计划任务示例

在2019年2月1日4点执行一次
on schedule at    '2019-02-01 04:00:00'

4. 重复计划执行

on schedule every 1 second 每秒执行一次
on schedule every 1 minute 每分钟执行一次
on schedule every 1 day 每天执行一次

5. 指定时间范围的重复计划任务

每天在20:00:00执行一次
on schedule every 1 day starts '2019-02-01 20:00:00'

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！