MySQL:存储过程

时间:2024-03-25 13:25:13

1. 概念

MySQL中的存储过程指的是存储在数据库中的SQL语句集合。当创建好存储过程后,在运行时提供所需参数,存储过程就可以以代码指定的方式使用参数执行并返回值。

存储过程的特点包括:

  1. 封装与复用:可以把某一业务SQL封装在存储过程中,需要用到的时候直接调用即可,减少了网络交互,提升了通信速率。
  2. 接收参数与返回数据:在存储过程中,可以传递参数,也可以接收返回值。
  3. 编译与执行效率:存储过程只在创建时进行编译,以后每次执行存储过程都不需要在重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2. 基本语法 

2.1 创建 

-- 创建存储过程
create procedure 存储过程名 (参数没有就不传)
begin
    SQL语句
end;
-- 调用存储过程
call 存储过程名(传的参数,没有就不传);

栗子 

-- 创建存储过程
create procedure p1 ()
begin
    select * from emp;
    select count(*) from emp;
end;

-- 调用存储过程
call p1();

 2.2 查看

-- 查看存储过程
-- select 字段名 from information_schema.ROUTINES where ROUTINE_SCHENA = 存储过程名
    -- 查询指定数据库存的储过程及状态信息
-- show create procedure 存储过程名
    -- 查询存储过程的定义

栗子

select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'study';
show create procedure p1;

 2.3 删除

-- 删除存储过程
-- drop procedure (if exists) 存储过程名; 括号内容可省略
drop procedure if exists p1;

 2.4 命令行注意事项:

-- 注意:在命令行中执行存储过程需修改SQL语句结束符号,修改结束符用关键字delimiter
-- delimiter 符号
delimiter $$

 3. 变量

 概念

  1. 系统变量:系统变量是MySQL服务器定义的,用户只能修改其值,不能创建新的系统变量。系统变量分为全局变量和会话变量。全局变量对所有会话都有效,修改全局变量会影响所有新会话(但已存在的会话不受影响),直到MySQL服务器重启。会话变量则是在当前会话级别有效,其变更只会影响到当前会话。需要注意的是,对系统变量的修改在MySQL服务器重启后会恢复为默认值,要想永久修改,需要更改配置文件。
  2. 用户变量:用户变量是用户根据需要自己定义的变量,用户变量不用提前声明,在使用的时候直接用“@变量名”就可以。其作用域为当前连接。
  3. 局部变量:局部变量是根据需要在局部生效的变量,访问之前,需要DECLARE声明。在存储过程和函数中都可以定义和使用局部变量。

3.1 系统变量 

-- 系统变量
global全局系统变量 :所有会话生效
session当前会话变量:当前会话生效

-- 查看系统变量(括号二选一)
-- show (global/session) variables;  查看所有的系统变量
show session variables;
show global variables;
-- show (global/session) variables like ''; 模糊匹配系统变量
show global variables like 'auto%';
-- select @@系统变量名 查看指定系统变量
select @@autocommit;
-- 设置系统变量(不加global或者session会默认修改当前会话的变量)
    -- 当服务器重启会自动回复到初始值,若要永久修改可在配置文件 /etc/my.cnf 中修改
-- set (session/global) 系统变量名 = '值'; 设置指定系统变量的值
set @@autocommit = 1;

 3.2 用户变量

-- 用户变量
        -- 用户变量不需要提取声明或者初始化,未初始化的用户变量默认赋值null;
-- 设置用户变量
-- set @用户变量名1 = 值, @用户变量名2 = 值, ...;
-- set @用户变量名1 := 值, @用户变量名2 := 值, ...;
    -- SQL中= 和 := 都能赋值
select @niubi;
set @niubi := 666;

-- select @用户变量名1 := 值, @用户变量名2 := 值, ...;
select @niuhai := 999;
select @niuhai;

-- select count(*) into @用户变量名 from 表名;
    -- 将查询表统计返回的结果赋值给用户变量
select count(*) into @赋值结果 from emp;
select @赋值结果;

 3.3 局部变量

    -- 作用域只要begin和end之间生效,需要declare提前声明
    -- declare 变量名 变量类型 default 默认值
    -- 赋值 变量名 = 值
create procedure p1()
begin
    declare emp_count int default 0;
    select count(*) into emp_count from emp;
    select emp_count;
end;

call p1;

 4. 流程控制语句

两类语句

条件控制语句:if、case语句

循环控制语句:

4.1 if 

-- if判断条件的使用
    -- if 条件1 then
        -- 满足条件1执行的语句
    -- elseif 条件2 then
        -- 满足条件2执行的语句
    -- else
        -- 不满足以上所有条件执行的语句
    -- end if;

参数

-- 创建过程
-- 传递变量进行判断
create procedure p3(in source int)
begin
    -- 声明局部变量
    declare result varchar(10);

    if source >= 80 then
        set result = '优秀';
    elseif source >= 60 then
        set result = '及格';
    else
        set result = '不及格';
    end if;

    -- 查询结果
    select result;
end;

-- 调用过程
call p3(99);

 练习

-- 练习2:将传入的200分制的分数换算成100分制并返回
create procedure p5(inout score double)
    -- inout修饰的变量可以输入和输出参数
begin
    set score := score * 0.5;
end;

-- 调用
set @score = 91;
call p5(@score);
select @score;

 4.2 case

- 使用规则
-- case开始
 case
     when 条件1 then 满足条件1执行的语句;
     when 条件2 then 满足条件2执行的语句;
     when 条件3 then 满足条件3执行的语句;
     else 不满足以上条件执行的语句;
-- case结束
end case;
-- 输入季度,并进行判断
create procedure p6(in month int)
begin
    declare result varchar(10);

    case
        when 1 <= month and month <= 3 then set result = '第一季度';
        when 4 <= month and month <= 6 then set result = '第二季度';
        when 7 <= month and month <= 9 then set result = '第三季度';
        when 10 <= month and month <= 12 then set result = '第四季度';
        else set result = '非法参数';
        end case;

    select concat('您输入的月份为:', month, ',属于', result);
end;

call p6(19);

 4.3 while

-- 使用规则
while 条件 do
         满足条件执行的SQL语句
         不满足条件退出循环
end while;
-- 练习:累加n的值
create procedure p7(in n int)
begin
    declare sum int default 0;
    while n > 0
        do
            set sum = sum + n;
            set n = n - 1;
        end while;
    select sum;
end;

-- 调用
call p7(10);

 4.4 repeat

-- 使用规则
repeat
    要执行的sql语句
    until 满足条件就退出循环
    end repeat;
end repeat;
-- 练习:计算前n个数的值
create procedure p8(in n int)
begin
    declare sum int default 0;

    repeat
        set sum = sum + n;
        set n = n - 1;
    until n <= 0
        end repeat;

    select sum;
end;

-- 调用
call p8(10);

 4.5 loop

-- 使用规则
-- leaver 跳出循环
-- iterate 中断本次循环,进行下一次循环
create procedure p10(in n int)
begin
    -- 定义变量统计总和
    declare sum int default 0;
    -- 循环
    fun: loop
        -- 满足以下条件跳出循环
        if n <= 0 then
            leave fun;
        end if;
        -- 满足以下条件不进行累加
        if n % 2 = 0 then
            set n = n - 1;
            iterate fun;
        end if;
        -- 赋值
        set sum = sum + n;
        set n = n - 1;
    end loop fun;
    -- 查看最终结果
    select sum;
end;

call p10(5);

 5. 游标(cursor)

-- 用来存储查询结果集的数据类型,在存储过程和函数中可以用游标对结果集进行数据处理
-- 使用规则:声明,open、fetch、close
-- 声明游标 declare 游标名 cursor for 查询语句(查询到的内容将会存储到游标内);
-- open 游标名;
-- fetch 游标名 into 变量1,变量2,...;(游标内的每一条记录都会存储到对应的变量,变量接收的类型要和游标记录的数据类型保持一致)
-- close 游标名;
-- 条件处理程序(handler):处理程序出现问题时该如何执行(如当游标内的记录为空时如何退出循环)
-- 使用规则:declare handler_action handler for condition_value, condition_value,... statement;
-- 声明一个处理异常程序的语句即:遇到上面的状态码(如:02000)时进行什么程序操作(如:exit),中止程序后该游标什么状态(如:关闭游标)

-- handler_action
-- continue:继续执行当前程序;
-- exit:退出程序;

-- condition_value
-- sqlstate sqlstate_value:状态码(如:02000)
-- sqlwarning:所有以01开头的状态码的简写
-- not found:所有以02开头的状态码的简写
-- sqlexception:所有没有被sqlwarning、not found捕抓到的状态码的简写
create procedure p11(in source varchar(10))
begin
    -- 声明游标内数据的类型
    declare uname varchar(10) default null;
    declare uid int;
    declare uage int;
    declare udept_id int;
    declare uaddress varchar(10);

    -- 声明游标
    declare emp_cursor cursor for select * from emp where address = source;
    -- 声明条件处理程序,当游标满足什么条件自动退出程序(当游标记录的数据为空时退出程序,并关闭游标)
    declare exit handler for not found close emp_cursor;
    -- 或者使用状态码退出
    -- declare exit handler for SQ
    -- declare exit handler for sqlstate '02000' close emp_cursor;

    -- 创建新表存储游表的记录
    drop table if exists tb_emp;
    create table tb_emp(
        id int primary key auto_increment,
        name varchar(10) not null,
        address varchar(10)
    );
    -- 打开游标
    open emp_cursor;
    -- 循环遍历存储游标内的记录
    while true do
        -- 获取游标内的数据存储到定义的临时变量中
        fetch emp_cursor into uid, uname, uage, udept_id,  uaddress;
        -- 将数据存入新表
        insert into tb_emp value (null, uname, uaddress);
        end while;
    -- 关闭游标
    close emp_cursor;
end;

-- 调用存储过程
call p11('广州');

 6. 存储函数

-- 使用规则:函数必须要有返回值
create function 函数名称(参数列表)
returns type(函数返回值类型) character(函数特征)
begin
    函数内部要执行的语句
    return 返回值
end;
-- 使用规则:函数必须要有返回值
create function 函数名称(参数列表)
returns type(函数返回值类型) character(函数特征)
begin
    函数内部要执行的语句
    return 返回值
end;

 7. 触发器

触发器的主要特点和功能包括:

  1. 自动化业务逻辑和操作:触发器能够自动执行存储过程,响应特定事件,如insert、update和delete语句,从而实现约束、默认值或处理业务逻辑的功能。
  2. 响应特定事件:每个触发器都有一个触发事件和响应事件。触发事件通常是数据表上的INSERT、UPDATE或DELETE语句,而响应事件则是在触发事件后MySQL服务器执行的操作。
  3. 处理多种操作:触发器可以处理多种操作,比如设置默认值、展示错误信息、实现复杂的业务逻辑等。

需要注意的是,在MySQL中,只有执行INSERT、UPDATE和DELETE操作时才能激活触发器,其他SQL语句则不会激活触发器。

通过创建和使用触发器,开发人员可以更有效地管理数据库中的数据,确保数据的完整性和一致性,同时减少手动干预的需要,提高应用程序的自动化程度。

创建触发器语法 

create trigger 触发器名称

after/before insert on 触发器关联的表名 for each row

begin

        insert into 触发器的记录插入的哪张表的名称 (插入内容);

end; 

-- 创建触发器
-- 插入数据触发器
-- 创建触发器(trigger) 触发器名字
create trigger tb_emp_insert_trigger
    -- 触发器在emp表操作之后触发
    after insert on emp for each row
-- 触发器内部结构
begin
    insert into emp_logs (id, operation, operation_time, operation_id, operation_prams) values
                         (null, 'insert', now(), new.id,
                          concat('插入数据内容为:id=',new.id,'name=',new.name,'age=',new.age,'address=',new.address )
                          );
end;

create trigger 触发器名称

after/before update on 触发器关联的表名 for each row

begin

        insert into 触发器的记录插入的哪张表的名称 (插入内容);

end; 

-- 创建修改数据触发器
create trigger tb_emp_update_trigger
    -- 触发器在emp表操作之后触发
    after update on emp for each row
-- 触发器内部结构
begin
    insert into emp_logs (id, operation, operation_time, operation_id, operation_prams) values
        (null,'update',now(),new.id,
        concat('更新之前数据:id=', old.id, 'name=', old.name, 'age=', old.age, 'address=', old.address,
               '更新之后数据:id=', new.id, 'name=', new.name, 'age=', new.age, 'address=', new.address                                                                         )
        );
end;

 删除同理