存储过程

介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，可以直接调用这些集合（存储过程），调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的

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

这里的p1，p2就是SQL语句的集合，想要调用这些SQL语句时直接调用集合即可

• 存储过程的特点：
  1.封装，复用
  2.存储过程可以接收参数，也可以返回数据
  3.减少网络交互，效率提升

存储过程的基本语法

• 创建一个存储过程

CREATE PROCEDURE 存储过程名称([参数列表])-- 参数可有可无
BEGIN
。。。-- 中间是SQL语句-- 所有的逻辑都在begin和end之间
END;create procedure 存储过程名称([参数列表])
begin
。。。-- 中间是SQL语句
end;

• 存储过程的调用

CALL 名称([参数]);-- 参数也是可有可无

• 存储过程的查看

-- 查询指定数据库的存储过程及状态信息
SELECT *FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xxx'; 
-- 查询某个存储过程的定义(查询创建存储过程的SQL语句)
SHOW CREATE PROCEDURE 存储过程名称;

• 存储过程的删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

例子：

-- 存储过程的基本语法--  创建
create procedure oneFunction()
beginselect count(*)from student;
end;-- 调用
call oneFunction();-- 查看存储过程
select *from information_schema.ROUTINES where ROUTINE_SCHEMA='test';show create procedure oneFunction;
-- 删除存储过程
drop procedure if exists oneFunction;

注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符，否则就会报错

语法：delimiter 指定的结束符 例如：delimiter $$

变量

在MySQL中变量分为三类，系统变量，用户自定义变量，局部变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面，分为全局变量（GLOBAL），会话变量（SESSION）

全局变量是针对所有的会话有效的，在所有的控制台都是有效的

会话变量仅在当前会话内有效

• 查看系统变量

-- 下面的session就是查看所有的会话全局变量，global就是查看全局变量
-- 不指定默认是session会话变量-- 查看所有的系统变量
SHOW [SESSION | GLOBAL] VARIABLES; 
-- 可以通过like模糊匹配的方式查找变量  
SHOW [SESSION | GLOBAL] VARIABLES LIKE '......'; 
-- 查看指定变量的值
SELECT @@[SESSION | GLOBAL] 系统变量名;show [session |global]varibles;
show [session |global]varibles like '......';
select @@[session | global]系统变量名;

• 设置系统变量

-- 下面的session就是查看所有的会话全局变量，global就是查看全局变量SET [SESSION |GLOBAL] 系统变量名=值;SET @@[SESSION | GLOBAL] 系统变量名=值;set [session |global] 系统变量名=值;set @@[session |global] 系统变量名=值;

注意：全局变量设置后，所有的会话都会生效，当时当mysql服务器重启后，所有的全局变量都会回到初始值
如果想永久修改全局变量，就需要修改配置文件

例：

show session variables;show session variables like 'auto%';show global variables like 'auto%';select @@global.autocommit;select @@session.autocommit;
set session autocommit=0;
set global autocommit=0;
set session autocommit=1;

用户自定义变量

注意，两个@@指的是系统变量，一个@指的是系统自定义变量

用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候用‘@变量名’使用就可以，其作用域为当前连接（会话），也就是当前控制台。

• 用户自定义变量的赋值

-- @var_name:变量名， expr：值 
SET @var_name=expr[, @var_name=expr]...;-- 推荐使用这种方式赋值
SET @var_name:=expr[, @var_name:=expr]...; SELECT @var_name:=expr[,@var_name:=expr]...;SELECT 字段名 INTO @var_name FROM 表名;

• 用户自定义变量的使用

SELECT @var_name[,@var_name]...;

例如：

set @myname='欧肖';
set @myage:=18;
set @mygender='男',@myhobby='java';select @mycolor:='red';
-- 将查询结果赋值给自定义变量
select count(*) into @mycount from student;select @myage;
select @mycolor;
select @mycount;

注意：用户自定义变量不需要声明或初始化，只不过获取到的值为null

局部变量

局部变量是根据需要定义在局部生效的变量，访问之前，需要DECLARE声明，可用作存储过程内的局部变量和输入参数，局部变量的范围是在其存储过程声明的BEGIN。。。END块内。

• 局部变量的声明

-- 这里的变量类型就是数据库字段类型
-- default后是初始值，可以不加
DECLARE 变量名 变量类型 [DEFAULT...];

• 局部变量的赋值

SET 变量名=值;
SET 变量名:=值;
SELECT 字段名 INTO 变量名 FROM 表名...;

例如：

create procedure oneTest()
begindeclare stu_count int default 0;select count(*) into stu_count from student;select stu_count;
end;call oneTest();

if判断

语法：

IF 条件1 THEN
...
ELSEIF 条件2 THEN -- 可选
...
ELSE -- 可选
...
END IF;

跟之前的if语句类似

案例：

create procedure p1()
begindeclare score int default 70;declare result varchar(10) ;if score >=85 thenset result:='优秀';elseif score>=60 thenset result:='及格';elseset result:='不及格';end if;select result;end;
call p1();