MySQL04 视图、函数和过程

        我们先用下面的DDL和DML创建名为hrs的数据库并为其二维表添加如下所示的数据，再通过创建的hrs数据库对视图、函数和过程进行介绍

-- 创建名为hrs的数据库并指定默认的字符集

create database `hrs` default charset utf8mb4;

-- 切换到hrs数据库

use `hrs`;

-- 创建部⻔表

create table `tb_dept`

(

`dno` int not null comment '编号'

, `dname` varchar(10) not null comment '名称'

, `dloc` varchar(20) not null comment '所在地'

, primary key (`dno`)

);

-- 插⼊4个部⻔

insert into `tb_dept` values

  (10, '会计部', '北京')

, (20, '研发部', '成都')

, (30, '销售部', '重庆')

, (40, '运维部', '深圳');

-- 创建员⼯表

create table `tb_emp`

( `eno` int not null comment '员⼯编号'

, `ename` varchar(20) not null comment '员⼯姓名'

, `job` varchar(20) not null comment '员⼯职位'

, `mgr` int comment '主管编号'

, `sal` int not null comment '员⼯⽉薪'

, `comm` int comment '每⽉补贴'

, `dno` int not null comment '所在部⻔编号'

, primary key (`eno`)

, constraint `fk_emp_mgr` foreign key (`mgr`) references tb_emp (`eno`)

, constraint `fk_emp_dno` foreign key (`dno`) references tb_dept (`dno`) );

-- 插⼊14个员⼯

insert into `tb_emp` values

(7800, '张三丰', '总裁', null, 9000, 1200, 20)

, (2056, '乔峰', '分析师', 7800, 5000, 1500, 20)

, (3088, '李莫愁', '设计师', 2056, 3500, 800, 20)

, (3211, '张⽆忌', '程序员', 2056, 3200, null, 20)

,(3233, '丘处机', '程序员', 2056, 3400, null, 20)

, (3251, '张翠⼭', '程序员', 2056, 4000, null, 20)

, (5566, '宋远桥', '会计师', 7800, 4000, 1000, 10)

, (5234, '郭靖', '出纳', 5566, 2000, null, 10)

, (3344, '⻩蓉', '销售主管', 7800, 3000, 800, 30)

, (1359, '胡⼀⼑', '销售员', 3344, 1800, 200, 30)

, (4466, '苗⼈凤', '销售员', 3344, 2500, null, 30)

, (3244, '欧阳锋', '程序员', 3088, 3200, null, 20)

, (3577, '杨过', '会计', 5566, 2200, null, 10)

, (3588, '朱九真', '会计', 5566, 2500, null, 10);

 视图

        视图是关系型数据库中将一组查询指令构成的结果集组合成可查询的数据表的对象，简单的说，视图就是虚拟的表，但与数据表不同的是，数据表是一种实体结构，而视图是一种虚拟结构，也可以将视图理解为保存在数据库中被赋予名字的sql语句。

        使用视图可以获得以下好处：

• 可以将实体数据表隐藏起来，让外部程序无法得知实际的数据结构，让访问者可以使用表得组成部分而不是整个表 ，降低数据库被攻击的风险
• 在大多数的情况下视图是只读的（更新视图的操作通常都有诸多的限制），外部程序无法直接透过视图修改数据
• 重用sql语句，将高度复杂的查询包装在视图表中，直接访问该视图即可取出需要的数据；也可以将视图视为数据表进行连接查询
• 视图可以返回与实体数据表不同格式的数据，在创建视图的时候可以对数据进行格式化处理

1. 创建视图

 create view `vw_emp_simple`

as select `eno`

, `ename`

, `job`

, `dno`

from `tb_emp`;

        提示：因为视图不包含数据，所以每次使⽤视图时，都必须执⾏查询以获得数据，如果你使⽤了连接查询、 嵌套查询创建了较为复杂的视图，你可能会发现查询性能下降得很厉害。因此，在使⽤复杂的视图前，应该 进⾏测试以确保其性能能够满⾜应⽤的需求.

        有了上⾯的视图，我们就可以使⽤之前讲过的 DCL， 限制某些⽤户只能从视图中获取员⼯信息，这样员⼯表中的 ⼯资（ sal ）、补贴（ comm ）等敏感字段便不会暴露给⽤户。下⾯的代码演示了如何从视图中获取数据。

select * from `vw_emp_simple`;

 查询结果：

+------+-----------+--------------+-----+

| eno | ename | job | dno |

+------+-----------+--------------+-----+

| 1359 | 胡⼆⼑ | 销售员    | 30 |

| 2056 | 乔峰     | 分析师    | 20 |

| 3088 | 李莫愁 | 设计师     | 20 |

| 3211 | 张⽆忌 | 程序员     | 20 |

| 3233 | 丘处机 | 程序员     | 20 |

| 3244 | 欧阳锋 | 程序员     | 20 |

| 3251 | 张翠⼭ | 程序员     | 20 |

| 3344 | ⻩蓉     | 销售主管 | 30 |

| 3577 | 杨过     | 会计         | 10 |

| 3588 | 朱九真 | 会计         | 10 |

| 4466 | 苗⼈凤 | 销售员     | 30 |

| 5234 | 郭靖     | 出纳         | 10 |

| 5566 | 宋远桥  | 会计师     | 10 |

| 7800 | 张三丰  | 总裁         | 20 |

+------+-----------+--------------+-----+

         既然视图是⼀张虚拟的表，那么视图的中的数据可以更新吗？视图的可更新性要视具体情况⽽定，以下类型的视图 是不能更新的：

• 使用了聚合函数（sum、min、max、avg、count等）、distinct、group by、having、union或者使用了union all的视图。
• select中包含了子查询的视图
• from子句中包含了一个不能更新的视图的视图
• where子句的子查询引用了from子句中表的视图。

2. 删除视图

drop view if exists `vw_emp_simple`;

       说明：如果希望更新视图，可以先⽤上⾯的命令删除视图，也可以通过 create or replace view 来更新视图。

3. 视图的规则和限制

• 视图可以嵌套，可以利用从其他视图中检索的数据来构造一个新的视图。视图也可以和表一起使用
• 创建视图时可以使用order by子句，但如果从视图中检索数据时也使用了order by，那么该视图中原先的order by会被覆盖
• 视图无法使用索引，也不会激发触发器

函数

        MySQL 中的函数跟 Python 中的函数⼤同⼩异，因为函数都是⽤来封装功能上相对独⽴且会被重复使⽤的代码 的。如果⾮要找出⼀些差别来，那么 MySQL 中的函数是可以执⾏ SQL 语句的。下⾯的例⼦，我们通过⾃定义函数 实现了截断超⻓字符串的功能。

delimiter $$

create function fn_truncate_string(

        content varchar(10000)

        , max_length int unsigned

) returns varchar(10000) no sql

begin

        declare result varchar(10000) default content;

         if char_length(content) > max_length then

                set result = left(content, max_length);

                set result = concat(result, '……');

        end if;

        return result;

end $$

delimiter ;

        说明1：函数声明后⾯的 no sql 是声明函数体并没有使⽤ SQL 语句；如果函数体中需要通过 SQL 读取数 据，需要声明为 reads sql data 。

        说明2：定义函数前后的 delimiter 命令是为了修改终⽌符（定界符），因为函数体中的语句都是⽤ ; 表示 结束，如果不重新定义定界符，那么遇到的 ; 的时候代码就会被截断执⾏，显然这不是我们想要的效果。

在查询中调用自定义函数

select fn_truncate_string('和我在成都的街头⾛⼀⾛，直到所有的灯都熄灭了也不停留', 10) as short_string;

 查询结果

+--------------------------------------+

| short_string                          |

+--------------------------------------+

| 和我在成都的街头⾛⼀……  |

+--------------------------------------+

 过程

        过程（⼜称存储过程）是事先编译好存储在数据库中的⼀组 SQL 的集合，调⽤过程可以简化应⽤程序开发⼈员的 ⼯作，减少与数据库服务器之间的通信，对于提升数据操作的性能也是有帮助的。其实迄今为⽌，我们使⽤的 SQL 语句都是针对⼀个或多个表的单条语句，但在实际开发中经常会遇到某个操作需要多条 SQL 语句才能完成的情 况。例如，电商⽹站在受理⽤户订单时，需要做以下⼀系列的处理。

• 通过查询来核对库存中是否有对应的物品以及库存是否充足
• 如果库存有物品，需要锁定库存以确保这些物品不再卖给别人，并且要减少可用的物品数量以反映正确的库存量
• 如果库存不足，可能需要进一步与供应商进行交互或者至少产生一条系统提示消息
• 不管受理订单是否成功，都需要产生流水记录，可以需要给对应的用户产生一条通知

        我们可以通过过程将复杂的操作封装起来，这样不仅有助于保证数据的⼀致性，⽽且将来如果业务发⽣了变动，只 需要调整和修改过程即可。对于调⽤过程的⽤户来说，过程并没有暴露数据表的细节，⽽且执⾏过程⽐⼀条条的执 ⾏⼀组 SQL 要快得多。

    下⾯的过程实现 hrs 数据库中员⼯⼯资的普调，具体的规则是： 10 部⻔的员⼯薪资上浮 300 ， 20 部⻔的员⼯薪 资上浮 800 ， 30 部⻔的员⼯薪资上浮 500。

delimiter $$

create procedure sp_upgrade_salary()

begin

        declare flag boolean default 1;

        -- 定义⼀个异常处理器

        declare continue handler for sqlexception set flag=0;

        -- 开启事务环境

        start transaction;

        update tb_emp set sal=sal+300 where dno=10;

        update tb_emp set sal=sal+800 where dno=20;

        update tb_emp set sal=sal+500 where dno=30;  

        

        -- 提交或回滚事务

        if flag then

                commit;

        else

                rollback;

        end if;

end $$

delimiter ;

        说明：上⾯的过程代码中使⽤了 start transaction 来开启事务环境，关于事务，在本课的最后有⼀个简单 的介绍。为了确定代码中是否发⽣异常，从⽽提交或回滚事务，上⾯的过程中定义了⼀个名为 flag 的变量和 ⼀个异常处理器，如果发⽣了异常， flag 将会被赋值为 0 ，后⾯的分⽀结构会根据 flag 的值来决定是执 ⾏ commit ，还是执⾏ rollback 。

使用过程

call sp_upgrade_salary();

删除过程

drop procedure if exists sp_upgrade_salary;

         在过程中，我们可以定义变量、条件，可以使⽤分⽀和循环语句，可以通过游标操作查询结果，还可以使⽤事件调 度器，这些内容我们暂时不在此处进⾏介绍。虽然我们说了很多过程的好处，但是在实际开发中，如果频繁的使⽤ 过程并将⼤量复杂的运算放到过程中，会给据库服务器造成巨⼤的压⼒，⽽数据库往往都是性能瓶颈所在，使⽤过 程⽆疑是雪上加霜的操作。所以，对于互联⽹产品开发，我们⼀般建议让数据库只做好存储，复杂的运算和处理交给应⽤服务器上的程序去完成，如果应⽤服务器变得不堪重负了，我们可以⽐较容易的部署多台应⽤服务器来分摊 这些压⼒。

        如果⼤家对上⾯讲到的视图、函数、过程包括我们没有讲到的触发器这些知识有兴趣，建议⼤家阅读 MySQL 的⼊ ⻔读物《MySQL必知必会》进⾏⼀般性了解即可。