MySQL--储存过程与视图

存储过程与视图、触发器

存储过程

1、应用场景

对数据进行计算，分析汇总的时候，尤其是拿到别的公司数据的时候，进行转换为自己系统需要的数据和格式的时候

2、概念

存储过程和函数可以理解为一段 SQL 语句的集合，它们被事先编译好并且存储在数据库中

创建一个存储过程的语法:

 create procedure 储存过程的名字（参数列表）
 begin 
      存储过程体
  end
  call 存储过程名（参数列表）

例子：

 DROP PROCEDURE user_procedure;
 create PROCEDURE user_procedure(in x int)
 BEGIN 
         select * from 'user' where id=x;
 END
 --执行
 call user_procedure(1);

存储过程参数类型
1. 从上面的过程中我们了解到存储过程有参数类型这种说法，它的类型可以取值有三个：in、out、inout。
2. 其中它们的意义如下：

(1) in 表示只是用来输入。

(2) out 表示只是用来输出。

(3) inout 可以用来输入，也可以用作输出。

3.存储过程的理解

1. 调用存储过程与直接执行 SQL 语句的效果是相同的，但是存储过程的一个好处是处理逻辑都封装在数据库端。
2. 当我们调用存储过程的时候，我们不需要了解其中的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，对调用它的程
   序完全无影响。
3. 调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率

4.存储过程中使用变量

1. 存储过程中是可以使用变量的，我们可以通过 declare 来定义一个局部变量，该变量的作用域只是 begin….end 块中。
2. 变量的定义必须写在符合语句的开头，并且在任何其他语句的前面。我们可以一次声明多个相同类型的变量，我们还可以使用default 来赋予默认值。
3. 定义一个变量的语法为：
declare 变量名 1 [,变量名 2…] 变量类型 [default 默认值]
4.变量的类型就是mysql支持的类型，可以直接赋值，也可以通过查询来赋值

• 直接赋值的语法为：
  set 变量名1 = 表达式1；
• 通过查询结果来赋值：

  DROP PROCEDURE user_procedure_2;
  create PROCEDURE user_procedure_2(in x int, out y varchar(10))
  BEGIN
  declare s varchar(10) ;
  select user_name into s from user where id = x;
  set y = s;
  END
  call user_procedure_2(1, [[[[[[[@a](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347));
  select [[[[[[[@a](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)

5.存储过程中的数据类型:

• 数值类型：Int,float,double,decimal
• . 日期类型：timestamp,date,year
• . 字符串：char,varchar,text

5. 游标

理解：即在存储过程中使用游标对结果集进行循环的处理，使用游标的基本步骤为: 声明、打开、取值、关闭
语法:

DECLARE test_cursor CURSOR FOR 结果集;//声明游标
OPEN test_cursor;  //打开
CLOSE test_cursor; //关闭
DECLARE CONTINUE HANDLER  FOR NOT FOUND //结果集查询不到数据就自动跳出

案例：

delimoter $$ 
create proceddure exchange（out count int）
begin 
         declare supply_id1 int default 0;
         declare amount int default 0;
         -- 游标标识
         declare blag int default 1;
         -- 游标
         declare order_cursor cursor for select supply_id,amount from order_group;
         -- not found 异常处理，退出
         declear continue handler for not found set blag=0;
        set count =0;
        --打开游标
        open order_cursor;
        --遍历
        read_loop：LOOP
                       fetch order_cursor into supply_id1,amount1;
                       if blag = 0 then
                                  leave read_loop;
                      end if;
                      if supply_id1 = 1 then 
                                set count = count +amount1;
                     end if;
         end loop  read_loop;
end;
$$
delimiter ;
call exchang(@count);
select @count;               

6.存储过程的优缺点

• 优点
  1. 第一点优势就是执行速度快。因为我们的每个 SQL 语句都需要经
  过编译，然后再运行，但是存储过程都是直接编译好了之后，直接
  运行即可。
  2. 第二点优势就是减少网络流量。我们传输一个存储过程比我们传
  输大量的 SQL 语句的开销要小得多。
  3. 第三点优势就是提高系统安全性。因为存储过程可以使用权限控
  制，而且参数化的存储过程可以有效地防止 SQL 注入攻击。保证了
  其安全性。
  4. 第四点优势就是耦合性降低。当我们的表结构发生了调整或变动
  之后，我们可以修改相应的存储过程，我们的应用程序在一定程度
  上需要改动的地方就较小了。
  44 / 123
  5. 第五点优势就是重用性强。因为我们写好一个存储过程之后，再
  次调用它只需要一个名称即可，也就是”一次编写，随处调用”,而且
  使用存储过程也可以让程序的模块化加强。

• 缺点

1. 第一个缺点就是移植性差。因为存储过程是和数据库绑定的，如
   果我们要更换数据库之类的操作，可能很多地方需要改动。
2. 第二个缺点就是修改不方便。因为对于存储过程而言，我们并不
   能特别有效的调试，它的一些 bug 可能发现的更晚一些，增加了应
   用的危险性。
3. 第三个缺点就是优势不明显和赘余功能。对于小型 web 应用来说，
   如果我们使用语句缓存，发现编译 SQL 的开销并不大，但是使用存
   储过程却需要检查权限一类的开销，这些赘余功能也会在一定程度
   上拖累性能。

视图

1.1概念

视图可以理解为一个虚表，它只是存储了一个表结构，并不存在真是数据，数据是查询过程中动态生成的；使用视图并不能达成优化，只是简化查询语句

1.2 视图操作

语法：

//创建视图
create VIEW user_view as select id,user_name,email from `user`;
//查看视图的每一列
desc user_view;
//查看创建视图的语法
show create view user_view;
//查询视图
select * from user_view;

1.3视图优缺点

优点：
1.简化了sql.
2.\更加安全，可以做权限限制，如只能看到视图，不能看到源数据.
3.\降低耦合，修改原表，只需修改视图，不用更新代码
缺点：表结构修改则需要手动修改视图

1.4视图IUD

表是可以更新数据的，这里的更新，指的是”增删改”,但是对于视图来说 不一定。
以下是视图不可更新的情况
1. 包含聚合函数、distinct、group by、having、union、union all。
2. 常量视图。
3. select 包含子查询。
4. 包含连接操作。
5. from 一个不能更新的视图。
6. where 子句的子查询引用了 from 子句中的表。
有时我们会发现，可以向视图插入数据，但是并不满足我们的需求，那就需要使用 with check option 了
例子:\

DROP VIEW user_view_2;
create view user_view_2 as select id,user_name,password,email,status from `user` where status = 0  with check option;

这里可以理解为 with check option 的作用就是多了一个 check 的功能，即检查的功能，也就是说插入的数据必须满足该视图的条件，才允许被操作。

1.4 物化视图

概念

物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样,物化视图 可以理解成 就是单独再创建一张统计表\

作用

1.物化视图可以帮助加快严重依赖某些聚合结果的查询。
2.如果插入速度不是问题，则此功能可以帮助减少系统上的读取负载。

实现

根据项目的需求 (数据实时性)

要定时更新数据, ==>>使用存储过程 开销小,误差大一点\

实时更新数据, ==>> 使用触发器, 会影响数据库的写操作的性能,开销大

触发器

创建触发器

触发器  类似于 框架 事件\
create trigger [触发器名称] [触发器的执行时间] [执行的动作点] on [表名] for each row [函数 或者动作]

触发器的执行时间 :before, after
执行的动作点:insert, update, delete
函数:begin end;
或者动作:update ,insert\

2.1 案例：

create trigger login_trigger_before
          before insert on login
          for each row
  begin
          update test_trigger set count=count+1 where id=1;
end;

本作品采用《CC 协议》，转载必须注明作者和本文链接