Mysql存储过程

2021-01-03 约 3545 字预计阅读 8 分钟

MySQL存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给丁参数（需要时）来调用执行。

存储过程思想上很简单，就是通过SQL语言层面的代码封装与重用。

存储过程是数据库的一个对象，在层次上和索引、视图、触发器、表一个层级。

可以在存储过程中放很多语句（封装sql语句集合），能够处理复杂的sql语句

当用户数据量很大的时候，一个表会放很多数据，这样查询表的操作就会很慢很慢。

对于上面的例子中，通常使用的手段是把这些表按天来单独放，每天的数据，放在一个表中。

这个操作就可以用存储过程来实现。

存储过程是一组为了完成某项特定功能的sql语句集，其实质上就是一段存储在数据库中的代码，他可以由声明式的sql语句（如CREATE,UPDATE,SELECT等语句）和过程式sql语句（如IF…THEN…ELSE控制结构语句）组成。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。作者：爱西考的王同学链接：https://juejin.cn/post/6844903848394702856

在生产环境下可以直接通过修改存储过程的方式修改业务逻辑（或bug）而不用重启服务器
执行速度快，存储过程经过编译之后会比单独执行一个语句要快。
减少网络传输的流量
方便优化

缺点

过程化编程，复杂业务处理的维护成本高

调试不便

不同数据库之间的可移植性差——不同数据库语法不一致

创建存储过程

sql语句中常常以;结尾，但是存储过程会包括很多条sql语句，为了解决这个问题，通常使用DELIMITER命令，将sql语句的结束符临时修改为其他符号。

1、界定符

DELIMITER语法格式

DELIMITER ?

执行上面的语句之后，结束符号就是?了

?是用户定义的结束符，通常这个符号可以是一些特殊的符号，另外应该避免使用反斜杠，因为它是转移字符，如果希望换回默认的分毫作为结束标记，只需在命令行输入下面的sql语句即可

DELIMITER ;

2、存储过程创建

mysql中使用CREATE PROCEDURE语句来创建存储过程

CREATE PROCEDURE p_name([proc_parameter[...]])
routine_body

其中proc_parameter的语法格式是：

[IN|OUT|INOUT] parame_name type

p_name用户指定存储过程的名称
proc_parameter用于指定存储过程中的参数列表。其中，语法项parame_name为参数名 type为参数的类型（类型可以是Mysql中任意的有效的数据类型）。Mysql的存储过程支持三种类型的参数，即输入参数IN，输出参数OUT,输入输出参数INOUT。

输入参数是使数据可以传递给一个存储过程。

输出参数是用于存储过程中需要返回的一个操作结果

输入输出参数及可以充当输入参数，也可以充当输出结果

参数的取名不要和表中的列相同，否则尽管不会返回出错的信息，但储存过程中的sql语句会将参数名当作列名，从而引发不可预知的错误，
语法项routine_body表示存储过程的主体部分，也成为存储过程题，其包含了需要执行的sql，过程体以关键字BEGIN开始，以关键字END结束。若只有一条SQL可以忽略BEGIN...END标志

3、局部变量

在存储过程体重可以声明局部变量，用来存储过程体中的临时结果。在Mysql中使用DECLARE语句来声明局部变量

DECLARE var_name type [DEFAULT value]

var name用于指定局部变量的名称 type用来声明变量的类型，DEFAULT用来指定默认值，如果没有则置为NULL

局部变量只能在存储过程体的BEGIN…END中，局部变量必须在存储过程体的抬头出生命，局部变量的作用范围仅限于声明它的BEGIN…END语句快，其他语句块中的语句不可以使用它

4、用户变量

用户变量一般以@开头

:warning:滥用用户变量会导致程序难以理解及管理

5、SET语句

在Mysql中通过SET语句对局部变量赋值，其格式为

SET var_name = expr[,var_name2 = expr]...

6、SELECT…INTO语句

在mysql中，可以使用SELECT…INTO语句把选定的值存储到局部变量中，格式为

SELECT col_name[,...] INTO var_name[,...] table_expr

col_name用于指定列名，var_name用于指定要赋值的变量名table_expr表示SELECT语句中FROM后面的部分

7、流程控制语句

条件判断语句 if-then-else 语句:

mysql > DELIMITER &&  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> &&  
mysql > DELIMITER ;

case语句:

mysql > DELIMITER &&  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else   
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> &&  
mysql > DELIMITER ; 
复制代码

循环语句 while ···· end while:

mysql > DELIMITER &&  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> &&  
mysql > DELIMITER ;
复制代码

repeat···· end repea： 它在执行操作后检查结果，而 while 则是执行前进行检查。

mysql > DELIMITER &&  
mysql > CREATE PROCEDURE proc5 ()  
     -> begin   
     -> declare v int;  
     -> set v=0;  
     -> repeat  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> until v>=5  
     -> end repeat;  
     -> end;  
     -> &&  
mysql > DELIMITER ;
复制代码
repeat
    --循环体
    until 循环条件  
end repeat;
复制代码

loop ·····endloop: loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

mysql > DELIMITER &&  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> &&  
mysql > DELIMITER ;
复制代码

ITERATE迭代：

mysql > DELIMITER &&  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then   
     -> set v=v+1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v>=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> &&  
mysql > DELIMITER ;
复制代码

8、调用存储过程

上面的流程只是创建了存储过程，真正要用的话还是得call一下

call sp_name[(传参)]

9、删除存储过程

DROP PROCEDURE sp_name;

存储函数

存储过程和存储函数一样，都是sql和语句组成的代码块

存储函数不能有输入函数，可以直接调用，不需要用call语句

游标

MySQL中的游标可以理解成一个可迭代对象(类比Python中的列表、字典等可迭代对象)，它可以用来存储select 语句查询到的结果集，这个结果集可以包含多行数据，从而使我们可以使用迭代的方法从游标中依次取出每行数据。其实本质上就是结果集，当我们对查询的结果进行前一行或者后一行类似的操作时就可以使用到游标

游标的语法

首先需要定义游标； declare 游标名称 cursor for 查询语句；
其次，打开游标； open 游标名称
然后，对查询的结果集即游标进行检索行至变量提供使用
最后关闭游标； close 游标名称

游标使用样例

CREATE PROCEDURE printName()
BEGIN
	-- 订单名称
    declare name varchar(20);
    -- 创建游标
    declare cur cursor for select detail_name from oder_detail where oid = '1';
    -- 打开游标
    open cur;
		fetch cur into name;
		select name;
    -- 关闭游标
    close cur;
END;

调用存储过程

call printName;

输出结果

name
------
毛巾

触发器

触发器是指当表发生改变的时候触发的动作

例如，当A忘表中插入数据的时候此时表发生了改变，现在想要在每次插入数据之前检测所有的入参是否都是小写，此时就可以用触发器来检测；

这里说的表发生改变就是指的增删改，动作可以发生在增删改之前或者之后，触发事件就是我们需要写的的存储过程；

触发器的基本语法

创建触发器 create trgger 触发器名称触发动作 on 表明 for each row[触发事件]
删除触发器 drop trigger 触发器名称
查看触发器 show triggers

触发器是依赖于表的，像视图、临时表这种是没有触发器的

一般来说一个表最多支持六个触发器

触发器用的不多，因为很占用系统资源

样例

创建触发器; 创建一个触发器 getPrice 作用于 oder_detail 表的每行，每当插入数据之后就查询这条订单明细的价格赋值给变量 @price ；NEW是一张虚表，记录者被插入数据的行；故我们能在NEW表中获取每次插入的数据；

-- insert 触发器
CREATE TRIGGER getPrice AFTER INSERT ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;
复制代码

检测插入触发器; 插入一条数据，使用查询语句查询变量显示为 20；

-- 检测插入触发器

INSERT INTO `oder_detail`( `detail_name`, `price`, `oid`) VALUES ( '脸盆', 20.00, 2);

select @price;
复制代码

删除触发器;

-- 删除触发器
drop trigger getPrice;

目录