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

最新推荐文章于 2024-07-08 21:32:26 发布

不回头~

最新推荐文章于 2024-07-08 21:32:26 发布

阅读量702

点赞数

分类专栏： MySQL 文章标签：数据库触发器 mysql

原文链接：https://blog.csdn.net/qq_33157666/article/details/87877246

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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()
		BEGIN
			SELECT 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)
    BEGIN
    SELECT 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)
    BEGIN
      SELECT 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)
    BEGIN
      SELECT 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.变量

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

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

概括

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

2.4.变量的作用域

变量作用域说明：

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

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

create procedure test3()
begin
  begin
    declare 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）

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

create procedure test3()
    begin
      begin
        declare 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）

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

create procedure test3()
    begin

        declare userscount int default 0; -- 用户表中的数量
        declare ordercount int default 0; -- 订单表中的数量
        begin
            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(); 不会报错如图（6）和图（7）：
在这里插入图片描述
图（6）

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

2.5.存储过程条件语句

基本结构

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

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

(2) 多条件判断语句：

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

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

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

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

    create procedure test8(in userid int)
    begin
       declare 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.存储过程循环语句

while语句

while语句的基本结构

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

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

    create procedure test9()
    begin
      declare 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;

repeat语句

repeat语句基本的结构：

repeat...until...end repeat;

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

    create procedure test10()
    begin
        declare 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.存储过程游标的使用

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

    create procedure test11()
        begin
            declare 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.自定义函数

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

一个简单的函数

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

# 运行函数：
select getusername(1)

概括：

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

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

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

select getuuid(1);

4. 触发器

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

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

    create trigger tr_users_insert after insert on users
        for 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

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

    create trigger tr_users_delete before delete on users
        for 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.流程控制

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_status
            when 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（事件）

使用存储过程+事件事件一个简单的实现福彩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;

解析event的创建格式

基本语法

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

执行时间说明

单次计划任务示例

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

重复计划执行

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

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

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

不回头~

关注

0
点赞
踩
0

收藏

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

存储过程简介SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。存储过程的优点：(1).增强S
复制链接

扫一扫