(五)、存储过程 (Procedure)
1.存储过程介绍
存储过程是实现经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据的效率还是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
(1).存储过程_特点
- 封装、复用。
- 可以接收参数，也可以返回数据。
- 减少网络交互，效率提升。
2.存储过程_基本语法
(1).存储过程_创建
create procedure 存储过程名称([参数列表]) begin --SQL语句 end;
(2).存储过程_调用
call 名称([参数])
(3).存储过程_查看
1.查询指定数据库的存储过程及状态信息
select *from infommation_schema.routines where routine_schema='数据库名';
2.查询某个存储过程的定义
show create procedure 存储过程名称;
(4).存储过程_删除
drop procedure [if exists] 存储过程名称;
(5).存储过程示列
-- 存储过程基本语法 -- 1.创建 create procedure p1() begin select count(*) from student; end; -- 2.调用 call p1(); -- 3.查看 select *from information_schema.ROUTINES where routine_schema = 'itheima'; show create procedure p1; -- 4.删除 drop procedure if exists p1;
(6).命令行创建存储过程
注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL的结束符。
delimiter 指定符号; ->以指定符号为结束语句
-- 1.命令行方式不让创建，原因分号不对 create procedure p2() begin select count(*) from student; end; -- 2. 修改结尾命令符号之后再次进行创建 delimiter $$; create procedure p2() begin select count(*) from student; end$$ -- 最后一定要恢复成分号 delimiter ;
3.存储过程_系统变量
(1).什么是系统变量
系统变量 是 MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。
(2).查看系统变量
1.查看所有系统变量
show [session|global] variables;
2.可以通过like模糊匹配方式查找变量
show [session|global] variables like '...'
3.查看指定变量的值
select @@[session|global] 系统变量名;
(3).设置系统变量
set [session|global] 系统变量名 = 值;
set @@[session|global] 系统变量 = 值；
(4).系统变量演示
-- 变量 系统变量 -- 1.查看所有的系统变量 show variables; -- 模糊匹配查看系统变量 show session variables like 'auto%'; -- 查找指定变量的值信息 select @@session.autocommit; -- 2.设置系统变量 set session autocommit =1;
注意:
- 如果没有指定session/global,默认是
session,会话变量。 - mysql服务重启之后，所设置的全局参数会失效，想要不失效的，可以在
my.inf中进行配置。
4.存储过程_用户变量
(1).用户变量_介绍
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名"使用就可以。其作用域为当前连接。两个@是系统变量，一个@是用户变量。
(2).用户变量_赋值
1.使用等号赋值
set @varn_name=expr[,@var_name=expr]...;
2.使用 :=进行赋值
set @var_name:=expr[,@var_name:=expr]...;
3.使用seelect 进行赋值
select @var_name:=expr[,@var_name:=expr]...;
4.将返回的字段放进变量中
select 字段名 into @var_name from 表名;
(3).用户变量_使用
select @var_name;
(4).用户变量_示列
-- 变量: 用户变量 -- 赋值 set @myname = 'itheima'; set @myage := 10; set @mygender := '男',@myhobby :='java'; select @mycolor := '中国红'; select count(*) into @myId FROM student; #不能是数组，只能是一个单值 -- 使用 select @myname,@myage,@mygender,@myhobby;
注意:
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为null。
5.存储过程_局部变量
(1).局部变量_介绍
局部变量 是根据需要定义的局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin ..end块。
(2).局部变量_声明
declare 变量名 变量类型[default ...];
变量类型就是数据库字段类型: int 、bigint、char、varchar、date、time等。
(3).局部变量_赋值
-- 1.第一种 set 变量名=值; -- 2.第二种 set 变量名:=值; -- 3.第三种 select 字段名 into 变量名 from 表名...;
(4).局部变量_查看
select 局部变量名;
(5).局部变量_示列
-- 1.声明 create procedure p3() begin #声明局部变量 stu_count默认为0 declare stu_count int default 0; #赋值 set stu_count := 100; select stu_count; end; call p3();
6.存储过程_if (选择结构)
(1).if_语法
if 条件1 then ... elseif 条件2 then ... else ... end if;
(2).if_示列
根据定义的分数score变量，判定当前分数对应的分数等级。
- score >= 85分，等级为优秀。
- score>=60分 且 score <85分，等级为及格。
- score<60分,等级为不及格。
-- if -- 根据定义的分数score变量，判断当前分数对应的分数等级 create procedure p3() begin -- 定义分数并写死 declare `score` int default 58; -- 定义返回值 declare `result_value` varchar(10); if score >=85 then set result_value := '优秀'; elseif score >=60 then set result_value := '及格'; else set result_value := '不及格'; end if; #查询 select result_value; end; call p3;
7.存储过程_参数(in,out,inout)
(1).三大参数介绍
| 类型 | 含义 | 默认 |
| in | 该参数作为输入，也就是需要调用时传入值 | 备注 |
| out | 该类参数作为输出，也就是该参数可以作为返回值 | |
| inout | 即可以作为输入参数，也可以作为输出参数 |
(2).参数_用法
create procedure 存储过程名称([in/out/inout 参数名 参数类型]) begin -- SQL语句 end;
(3).参数_示列
- 根据传入参数score,判定当前分数对应的分数等级，并返回。
-- 根据定义的分数score变量，判断当前分数对应的分数等级 #设置传入的参数 和 输出的参数 ⭐ create procedure p4(in `score` int,out result_value varchar(10)) begin if score >=85 then set result_value := '优秀'; elseif score >=60 then set result_value := '及格'; else set result_value := '不及格'; end if; end; # 2.第一个是 传入的值。第二个是 接受的变量(用户变量) ⭐⭐ call p4(70,@result); -- 3.查看返回的变量 select @result; ⭐⭐⭐
- 将传入的200分制的分数，进行转换为百分制，并返回
-- 1.设置输入输出函数 create procedure p5(inout score float) begin set score := score/2; end; -- 2.先设置默认值 set @score_a =149; -- 3.传入参数并接收值 call p5(@score_a); -- 4.查看返回值 select @score_a;
