MySQL-存储过程

介绍

基本语法

创建

调用

查看

删除

变量

系统变量

查看

设置

用户定义变量

赋值

使用

局部变量

声明

赋值

流程控制

参数

条件结构

case

循环结构

while

repeat

loop

游标

条件处理程序

介绍

举个简单的例子，我们先select某数据，然后update，再update。这一系列操作指令是在MySQL中

下达的，然后指令传输到应用层，那么导致的问题就是，多次的指令传输会涉及到网络的请求。

所以存储过程就是，由一系列SQL语句经过编译后的指令集合，通过调用这个集合可以简化开发者的流程。最重要的是，能够减少数据在数据库和应用服务器之间的传输，提高数据处理的效率。

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

存储过程的特点：

封装、复用
可以接受参数、也可以返回数据
减少网络层的交互，提升效率

基本语法

创建

create procedure p1() --存储过程名称，也可以添加参数
begin--具体的SQL语句
end;

调用

call p1();

查看

通过MySQL系统中的信息查看

select *from information_schema.ROUTINES where ROUTINE_SCHEMA = 'text';
--数据库名

查看创建语句

show create procedure p1;

删除

drop procedure [if exist] p1;

注意！！！如果使用命令行执行存储过程的创建语句，会报错：

这是因为，在命令行中以分号为一句命令的结束，所以会导致报错。

所以在命令行中执行创建语句时，应该使用delimiter关键字指定命令结束符：

delimiter $$  --指定两个美元符号为结束符号create procedure p1() --存储过程名称，也可以添加参数
begin-----;
end;$$

变量

系统变量

系统变量是MySQL服务器指定的，不是用户定义的，属于服务器层面，Global（全局变量）、Session（会话变量，只代表当前会话）

查看

show variables;
--show global variables;
--show session variables;show variables like 'ac%';
--模糊匹配select @@activate_all_roles_on_login;
--查找指定的系统变量名

设置

set session 系统变量名 = 值;
--set global 系统变量名 = 值;

注意：

当没有指定global和session时，系统默认是session会话变量
当mysql重启时没所有设置的全局参数将会失效，要想不失效，可以在配置文件中配置

用户定义变量

赋值

set @myname = 'John';
set @myage := 10; 
--相当于定义了两个变量，并且赋值set @mynumber = 12345 , @myaddress = '北京市';
--可以同时定义两个用户变量select set @mycolor := '中国红';
--可以使用select进行赋值select count(*) into @mynum from users;
--可以使用其他表的字段来进行赋值

使用

select @mycolor,@myage;

局部变量

声明

declare user_num int;
--user_num是局部变量名

赋值

与用户自定义变量赋值相似

流程控制

参数

create procedure p2(in score int,out ret varchar(10))
beginif score >=15 thenset ret = '优秀';elseset ret = '及格';end if;
end;call p2(18,@ret);
select @ret;

运行结果：

条件结构

create procedure p2()
begindeclare score int default 20;declare ret varchar(10);if score >=15 thenset ret = '优秀';elseset ret = '及格';end if;select ret;
end;call p2();

还是比较好理解的，类似于编程语言中的IF语句。

case

create procedure p3(in month int)
begindeclare ret varchar(10);casewhen month>=1 and month<=3 thenset ret := '第一季度';when month>=4 and month<=6 thenset ret := '第二季度';when month>=7 and month<=9 thenset ret := '第三季度';when month>=10 and month<=12 thenset ret := '第四季度';else set ret := '非法参数';end case;select concat('您输入的月份为：',month,' 所属季度为：',ret);
end;call p3(12);

循环结构

while

WHILE 条件 DO

SQL逻辑语句

END WHILE;

演示

输入一个参数n，返回从1到n的累加

create procedure p4(in n int)
begindeclare total int default 0;while n>=1 doset total = total + n;set n = n - 1;end while;select total;
end;call p4(10);

运行结果

repeat

满足条件退出循环

repeat

SQL逻辑语句

UNTIL 条件

end repeat;

loop

loop一般配合下面两个语句使用：

LEAVE（直接退出循环）
ITERATE（跳过本次循环的剩下语句，然后进入下一次循环）

create procedure p4(in n int)
begindeclare total int default 0;sum loop:if n<=0 thenleave;end if;set total = total + n;set n = n - 1;end loop sum;select total;
end;call p4(10);

游标

游标是用来存储查询数据集的数据类型，在存储过程或函数过程中对结果集进行循环的处理。

声明游标

declare 游标名 cursor 查询结果集;

--输入年龄上限，将所有小于该年龄的数据的部分字段，作为一个游标create procedure p5(in n int)
begindeclare uname varchar(10);declare ugender varchar(10);declare u_cursor cursor for select user_name,user_gender from users where user_age<=n;drop table if exists u_name_gen;create table if not exists u_name_gen(id int primary key auto_increment,name varchar(10),gender varchar(10));open u_cursor;while true dofetch u_cursor into uname,ugender;insert into u_name_gen values (null,uname,ugender);end while;close u_cursor;end;call p5(32);

这里要注意的是，表确实创建成功了，但是MySQL依然会报错：