MySQL进阶 -- 存储过程 和 触发器

存储过程

介绍

存储过程是事先经过编译并存储在数据库中的一段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()
begin
    select 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()
begin
    declare 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()
begin
    declare score int default 70;
    declare result varchar(10) ;
    if score >=85 then
        set result:='优秀';
        elseif score>=60 then
        set result:='及格';
        else
        set result:='不及格';
    end if;
    select result;

    end;
call p1();

参数

存储过程的参数类型分为三类
in，out，inout，

用法：

create procedure 存储过程名称([ in/out/input 参数名 参数类型,...])
begin
。。。
 -- 中间是SQL语句
end;