Mysql存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

好处：

  1、如果每次都要执行3个语句，那使用存储过程包装就可以一次性完成了，提高代码重用性

  2、如果编译过了这个存储过程，第二次调用就不用编译了

  3、减少了和数据库服务器的连接次数，提高了效率

一、创建语法

create procedure 存储过程名(参数列表)
begin
存储过程体（一组合法的SQL语句）
end

注意：

1、参数列表包含三部分：参数模式 参数名 参数类型

举例：

IN stuname varchar(20)

参数模式：

• IN 进口，该参数可以作为输入，需要调用方传入值

• OUT 出口，该参数可以作为输出，也就是这个参数可以作为返回值，不像函数一样有return

• INOUT 进出口，该参数即可以作为输入又可以作为输出，即要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，BEGIN END 可以省略（和函数一样，只有一条可以圣洛大括号）

  存储过程体中每条SQL语句的结尾要求必须加分号，存储过程的结尾可以使用 DELIMITER 重新设置

语法：

DELIMITER 结束标记

DELIMITER $

二、调用语法

# 使用位置参数
CALL 存储过程名(实参列表) 结束符号

三、具体使用

1、空参列表

#案例：插入到 admin 表中5条记录

create table admin( id int, username varchar(30), `password` varchar(30) );

# 注意这个$ 定义为了结束符，调用的时候也需要写上

DELIMITER $ create procedure myp1() 
BEGIN 
  insert into admin(username,`password`) 
  values('join1','000'),('join2','000'),('join3','000'),('join4','000'),('join5','000'); 
END $

上面到sql窗口运行后就可以生成存储过程了，并且可以在 workbench 中查到。

1.png

执行调用后在 admin 表就有5条数据了

call myp1()$

2.png

注意这个结束符号也变成 $ 了，再用分号会查不出来语句。

3.png

2、创建一个带 in 模式参数的存储过程

要求1：根据学生信息，查找学生的全部信息以及成绩、

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

create procedure myp2(in stuName varchar(20))
begin
  select
    s.*,
    c.*
  from student s
  inner join sc c
  on s.sid=c.sid
  where s.name=stuname;
end $

# 调用

call myp2('赵雷')$

4.png

要求2：传入多个参数

create procedure myp3(in sex varchar(20),in sage varchar(20))
begin
  declare result varchar(20) default '';# 声明变量并初始化
  select
    count(1) into result # 赋值给变量
  from student s
  where s.ssex=sex
  and s.sage>sage;
  select result; #使用变量
end $

# 调用存储过程
call myp3('男','1989-01-01')$

5.png

3、创建一个带 out 模式参数的存储过程

要求：根据性别，返回对应学生名字

DELIMITER $

drop procedure myp5$
create procedure myp5(in name varchar(20),out sex varchar(20))
begin
  select
    s.ssex into sex
  from student s
  where s.sname=name;
end $

# 定义用户变量，并且在调用函数的参数里面接收

call myp5('赵雷',@bsex)$
select @bsex$

6.png

drop procedure myp6$
create procedure myp6(in sex varchar(20),out sname varchar(20))
begin
  select
    s.sname into sname
  from student s
  where s.ssex=sex;
end $

# 如果有多个值会报错

call myp6('男',@sname)$
select @sname$

7.png

要求：使用多个 out 值，要求根据姓名返回性别和年龄

# 注意赋值的写法，不能是 s.ssex into sex , s.sage into age

create procedure myp7(in name varchar(20),out sex varchar(20),out age varchar(20))
begin
  select
    s.ssex ,s.sage into sex,age
  from student s
  where s.sname=name;
end $

# 定义用户变量，并且在调用函数的参数里面接收

call myp7('赵雷',@bsex,@age)$
select @bsex,@age$

8.png

4、创建一个带 inout 模式参数的存储过程

要求：传入 a、b 两个值，最终 a 和 b 都翻倍并返回

# a、b 是局部变量，不用加 @

create procedure myp8(inout a int,inout b int)
begin
  set a=a*2;
  set b=b*b;
end $

# 调用并传入，不能直接传，需要定义变量并传入，这样变量的内容传入后再接收返回的值

set @m=10$
set @n=20$
call myp8(@m,@n)$
select @m,@n$

9.png

四、存储过程的删除

# 注意一次只能删除一个

drop procedure myp1;

五、存储过程的查看

# 查看存储过程的信息

show create procedure myp2;

注意存储过程不能修改里面的逻辑，要修改也只能是一些参数