MYSQL开发-存储过程、触发器

灰太狼 2022-12-05 13:58 254阅读 0赞

### 文章目录 ###

*  MYSQL开发-存储过程、触发器
 *   *  1. 存储过程
     *   *  1.1 存储过程的定义和调用
         *  1.2 存储过程的参数介绍
     *  2. 触发器

# MYSQL开发-存储过程、触发器 #

## 1. 存储过程 ##

### 1.1 存储过程的定义和调用 ###

存储过程可以看作是为实现某个功能而设计的sql代码块。存储过程在创建的时候已经编译，在执行和调用的时候效率极高。**一次编译，多次调用。  
优点：**  
①增强sql语言的功能和灵活性  
②标准组件式编程  
③较快的执行速度  
④减少网络流量  
⑤实现访问权限的控制  
**缺点：**  
①可移植性差  
②对于简单的sql语句不适用存储过程。  
③团队开发应统一标准。  
④业务逻辑复杂时，使用存储过程效果不明显。  
⑤在并发访问较大时，不适合写涉及过多运算的存储过程。

**修改sql语句默认结束符；**【在mysql中，分号是sql语句的默认结束符，在创建存储过程时，会因为分号报错。】【语法：`delimiter %`】【在创建存储过程后，恢复默认结束符为分号;】  
**定义存储过程：**

-- 存在则删除【生产的时候慎用，以免误删除】
    drop procedure if exists proc_name;
    
    -- 创建存储过程
    create procedure proc_name(参数1,参数2)
    begin
      sql sentences;
    end;
    
    -- 执行存储过程
    call proc_name(参数1,参数2);

-- 创建一个无参数的存储过程
    delimiter %
    create procedure insert_proc()
    begin
      select * from test;
    end%
    delimiter ;     -- 恢复默认结束符为分号;
    
    -- 执行存储过程
    call insert_proc() %

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center]

### 1.2 存储过程的参数介绍 ###

**in参数：**

-- in 参数：外部传值给存储过程使用
    -- (in id int)
    
    delimiter %
    create procedure proc_id(in id int,in name char(20))
    begin
      insert into test values(id,name);
    end%

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 1]  
**out参数：**

-- out 参数：将存储过程的执行结果回传给其他调用它的程序使用
    -- (out name varchar(10))
    
    delimiter %
    create procedure proc_name(in var_id int,out var_name char(20))
    begin
      select name into @var_name from test where id=var_id;
      select @var_name as '姓名';
    end%
    delimiter ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 2]  
在执行前，要声明一个局部变量，而局部变量要赋予初始值，**用来接收存储过程的输出**。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 3]  
**inout参数：**

-- inout参数：外部传值给存储过程使用，然后存储过程把执行结果回传给其他调用它的程序使用。
    -- (inout num int)
    delimiter %
    create procedure proc_power(inout num int)
    begin
      set num=num*num;
      select num;
    end%
    delimiter ;

-- 变量声明
    create procedure proc_name()
    begin
      delcare var_name var_type [default 默认值];
      sql语句
    end;

delimiter %
    drop procedure if exists proc_name%
    create procedure proc_name(in var_id int)
    begin
    declare var_name char(20);
    set var_name=(select name from test where id=var_id);
    select var_name as name;
    end%
    delimiter ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 5]  
**变量作用域：**

-- 内部存储过程的变量a对外部存储过程是不可见的
    begin    -- 外部存储过程
    declare a int;
    begin    -- 内部存储过程
    declare a int;
    end;
    end;

-- 创建存储过程
    delimiter %
    drop procedure if exists proc_test%
    create procedure proc_test()
    begin
    declare a int default 1;
    begin
    declare a int default 2;
    select a;
    end;
    select a;
    end%
    delimiter ;
    
    -- 执行存储过程
    call proc_test();

-- 创建存储过程
    delimiter %
    drop procedure if exists bjdx%
    create procedure bjdx(in a int,in b int)
    begin
    declare answer varchar(20);
    if a>b then 
    set answer='a';
    elseif a<b then
    set answer='b';
    else 
    set answer='ab';
    end if;
    select answer;
    end%
    delimiter ;
    
    -- 执行存储过程
    call bjdx(1,2);

-- case语法
    case col_name
    when 值1 then 
    操作1;
    when 值2 then 
    操作2;
    else
    操作3;
    end case;
    
    -- 输入1234得四季
    delimiter %
    drop procedure if exists proc_sea%
    create procedure proc_sea(in sea int)
    begin
    declare season varchar(10);
    case sea
    when 1 then
    set season='spring';
    when 2 then
    set season='summer';
    when 3 then
    set season='autumn';
    when 4 then
    set season='winter';
    else
    set season='no sea';
    end case;
    select season;
    end%
    delimiter ;

**③循环语句【`while\repeat\loop`】**  
**while循环**：先判断再循环

-- while循环语法
    while 条件表达式
    do
    循环体
    end while;
    
    -- 求前100个自然数的和
    delimiter %
    drop procedure if exists proc_sum%
    create procedure proc_sum(in count int)
    begin
    declare s int;
    declare i int;
    set s=0;
    set i=1;
    while i<=count
    do
    set s=s+i;
    set i=i+1;
    end while;
    select s;
    end%
    delimiter ;
    -- 执行存储过程
    call proc_sum(100);

-- repeat循环语法
    repeat
    循环体
    until 条件
    end repeat;
    
    -- 求前100个自然数的和
    delimiter %
    drop procedure if exists proc_sum%
    create procedure proc_sum(in count int)
    begin
    declare s int;
    declare i int;
    set s=0;
    set i=0;
    repeat
    set i=i+1;
    set s=s+i;
    until i>=count
    end repeat;
    select s;
    end%
    delimiter ;
    
    -- 执行存储过程
    call proc_sum(100);

-- loop循环语法
    loop_name:loop
    循环体
    if 条件 then    -- 用一个if判断来结束循环
    leave loop_name;
    end if;
    end loop;
    
    
    -- 求前100个自然数的和
    delimiter %
    drop procedure if exists proc_sum%
    create procedure proc_sum(in count int)
    begin
    declare s int;
    declare i int;
    set s=0;
    set i=0;
    loop_name:loop
    set i=i+1;
    set s=s+i;
    if i>=count then
    leave loop_name;
    end if;
    end loop;
    select s;
    end%
    delimiter ;
    
    -- 执行存储过程
    call proc_sum(100);

-- 查看某个存储过程的内容
    show create procedure proc_name\G;
    
    -- 查看所有的存储过程
    show procedure status\G;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 21]

-- 删除存储过程：
    drop procedure proc_name;
    drop procedure if exists proc_name
    -- 在一个存储过程中，允许调用另一个存储过程，但是不允许删除另一个存储过程。

## 2. 触发器 ##

-- 创建触发器的格式
    delimiter %
    create trigger tri_name
    [before | after] tri_event
    on table_name | view_name | user_name | db_name
    [for each row][when tri_condition]
    sql语句
    %
    
    tri_event一般指DML语句，即insert delete update

-- 创建触发器
    delimiter %
    create trigger tri_emp_dept
    after delete
    on dept
    for each row
    delete from emp where deptno=OLD.id;    -- OLD指旧行，即在dept表上执行delete操作所对应的行。
    %
    
    -- 测试触发器效果
    delimiter ;
    delete from dept where id=10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 23]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 24]  
**查看某个触发器的内容：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 25]  
**查看所有的触发器：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 26]

-- 删除触发器
    drop trigger tri_name;
    drop trigger if exists tri_name;

如果对mysql存储过程和触发器不是太了解的，可以参考下[ORACLE存储过程和触发器][ORACLE]，相互验证，加深理解。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/8d57a5be862f4036b553f19b6e56be82.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/acfc57e460f4434d8aeeef38b04de5f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/98d5cc128e1b423e8dc90728edef88ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/fb3c7fcffdf54f4da55f1d57c06b3388.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/1d2295b499ac4462a3d3dc5e65c6e35a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/4fd0ce6fd7344f1da772c89acd6b6fd5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221123/d757bc451b4f4105a660784b5d3076c2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221123/f3d3de81d30c4fbea78b6ba56f2c5d91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221123/87b701cc83a24e209c7ca65cfc1c0264.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221123/49f3250588254e1ba77bdb2fdf00788f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20221123/b37f02513efa4c2f975e3c5635d11fc5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20221123/b29d26ccd98d45b5b05b8c41f3518993.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 12]: /images/20221123/53162f8e6f514c618c4048de7f45f3de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 13]: /images/20221123/951dfe1f61e94bc29db9bd997ddc2713.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 14]: /images/20221123/7d6d2e6e6ebe4703b62bb0e16ec99086.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 15]: /images/20221123/78db6c044fbe4ace9c712780d684d818.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 16]: /images/20221123/6a2bcb0b5d214150b09290002ff38c78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 17]: /images/20221123/e57fdd9ff4a74cd89f4d8a7d5438014f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 18]: /images/20221123/7ab69985ae3e45288a3982c367388ad2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 19]: /images/20221123/34460c010807420a90629e7fdecb833b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 20]: /images/20221123/c37ebf982b834e79aee0973fe8e25d62.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 21]: /images/20221123/6c7d598919254c43b9295e7921470a9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 22]: /images/20221123/8851531465f14c60ad2587550d2b2395.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 23]: /images/20221123/b02880c795814c128abdb340b8625317.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 24]: /images/20221123/2940d5ee897c48ef883f985fd9c872f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 25]: /images/20221123/fbf71f2a7dab465ea9e9b78c13c31e2b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 26]: /images/20221123/ab79c274635f4c459fa8ac840f090d69.png
[ORACLE]: https://blog.csdn.net/weixin_36522099/article/details/105668131