15天学习MySQL计划-存储过程/变量/判断/循环/游标/函数/触发器(进阶篇)-第九天
存储过程
1.介绍
存储过程是事先经过编译并存储是数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程思路上很简单,就是数据库SQL语言层面的代码封装与重用。
特点
封装,复用
可以接受参数,也可以返回数据
减少网络交互,效率提升
2.存储过程语法
1.创建
注意:在命令行中,执行创建存储过程的SQL时,需要通过关键字delimiter指定SQL语句的结束符
delimiter 结束符(默认为;)
create procedure 存储过程名称([参数列表]) begin --SQL列表; end;
2.调用
call 名称 ([参数]);
3.查看
select * from information_schema.routines where routine_schema = 'xxx' --查询指定数据库的存储过程及状态信息 show create procedure 存储过程名称; --查询某个存储过程的定义
4.删除
drop procedure [if exists] 存储过程名称;
3.变量
系统变量是MySQL服务器提供的,不是用户定义的,属于服务器层面。分为全局变量(GLOBAL),会话变量(SESSION)。
1.查看系统变量
show [session|global] variables; --查看所有系统变量 show [session|global] variables like '%'; --可以通过like模糊匹配方式查找变量 select @@[session|global] 系统变量名; --查看指定变量值
2.设置系统变量
set [session|global] 系统变量名 = 值; set @@[session | global] 系统变量名 = 值;
注意:
- 如果没有指定session/global,默认是session,会话变量。
- MySQL服务重新启动之后,所有设置的全局参数会失效,要不想失效,可以在/etc/my.cnf中配置
3.用户变量
用户定义变量是用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接用“@变量名”就可以。其作用域为当前连接。
赋值(在变量名前加一个@,两个为系统变量)
set @var_name = expr [,@var_name=expr]; set @var_name := expr [,@var_name:=expr]; select 字段名 into @变量名 from 表名;
使用
select @var_name;
注意:
用户定义的变量无需对其进行声明或初始化,只不过获取到的值为NULL。
4.局部变量
局部变量是根据需要定义的局部生效的变量,访问之前,需要declare声明。可用作存储内的局部变量和输入参数,局部变量的范围是在其内声明的begin…end块中。
1.声明
declare 变量名 变量类型 [default ...]; 变量类型:int,bigint,char,varchar,date,time等。
2.赋值
set 变量名=值; select 字段名 into 变量名 from 表名;
4.判断/循环/多分支/游标
1.if判断
语法:
if 条件1 then ... elseif 条件2 then --可选 ... else --可选 ... end if;
例子:
mysql> delimiter $ mysql> create procedure test() begin declare sal int default 80; declare result varchar(10); if sal>=90 then set result='A'; elseif sal >=80 then set result='B'; else set result='C'; end if; select result; end;$ mysql> delimiter ; mysql> call test;
2.参数
例子1:
mysql> delimiter $ mysql> create procedure test1(in sal int,out result varchar(10)) begin if sal>=90 then set result='A'; elseif sal >=80 then set result='B'; else set result='C'; end if; end;$ mysql> delimiter ; mysql> call test1(100,@result); mysql> select @result; +---------+ | @result | +---------+ | A | +---------+ 1 row in set (0.00 sec)
例子2:
mysql> delimiter $ mysql> create procedure test2(inout sal int) begin set sal=sal*0.5; end$ mysql> set @sal=100; mysql> call test2(@sal); mysql> select @sal$ +------+ | @sal | +------+ | 50 | +------+ 1 row in set (0.00 sec)
3.流程控制(case)
语法一:
case case_value when when_value1 then statement_list1 [when when_value2 then statement_list2] [else statement_list] end case;
语法二:
case case_value when search_condition then statement_list1 [when search_condition then statement_list2] [else statement_list] end case;
create procedure quarter(in num int) begin case when num<=3 then select '1'; when num<=6 then select '2'; when num<=9 then select '3'; when num<=12 then select '4'; else select 'error'; end case; end$