【MySQL】第十二篇：MySQL视图和存储过程

一、视图

1.1、什么是视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select 语句保存在数据字典中的。
通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。
基表：用来创建视图的表叫做基表(base table)

1.2、为什么要使用视图

因为视图的诸多优点，如下：

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
  总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

1.3、示例

-- 使用sql查询
select cust_name,cust_contact from customers,orders,orderitems
where customers.cust_id=orders.cust_id
and orderitems.order_num=orders.order_num
and prod_id='TNT2';

查询结果

	cust_name	cust_contact
1	Yosemite Place	Y Sam
2	Coyote Inc.	Y Lee

-- 创建视图
create view productcustomers AS
    select cust_name,cust_contact,prod_id
    from customers,orders,orderitems
    where customers.cust_id=orders.cust_id 
    and orderitems.order_num=orders.order_num;

-- 查询结果
select cust_name,cust_contact from productcustomers where prod_id='TNT2';

查询结果

	cust_name	cust_contact
1	Yosemite Place	Y Sam
2	Coyote Inc.	Y Lee

从示例中可以看出，所谓视图就是封装了一堆的sql查询语句

作用

• 重用sql
• 简化复杂的sql操作，封装后可以方便的使用视图，而不必知道它的基本查询细节。
• 保护数据，可以只授予表的特定部分的访问权限而不是整个表的访问权限。

规定

• 名字唯一（不能与表名和其他视图名重名）
• 视图不能创建索引，也不能有关联的触发器
• 视图和表可以混着用

二、存储过程

2.1、什么是存储过程

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；可以将存储过程理解成sql中的函数
ps：存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

2.2、特点

• 有输入输出参数、可以声明变量。有if/else, case,while等控制语句、通过编写存储过程，可以实现复杂的逻辑功能；
• 函数的普遍特性：模块化，封装，代码复用；
• 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

简单来说，存储过程就是为了方便以后使用而事先保存的sql语句集合。

2.3、示例

示例一：基本使用

/*
	创建存储过程
    1. 如果需要参数，可以在（）中给出，即使没有参数，也要写（）。
    2. 用 begin 和 end 来限制执行体，end要分号结尾。
*/
create procedure productprice()
begin
    select AVG(prod_price) as priceAvg from products;
end;

/*调用存储过程*/
call productprice();

结果展示

	priceAvg
1	16.133571

示例二：使用参数（传出）

/*
    创建存储过程:
    1. 此存储过程接收三个参数：pl、ph、pa
    2. out:指出相应的参数是用来传出去的（返回给调用者）
    3. in:调用者传递给存储过程的
    4. inout：同时具备上述两者作用
*/
create procedure productprice2(
    out pl DECIMAL(8,2),
    out ph DECIMAL(8,2),
    out pa decimal(8,2)
)
begin
    select min(prod_price) into pl from products;
    select max(prod_price) into ph from products;
    select avg(prod_price) into pa from products;
end;

/*
    调用存储过程
    1. @用来声明变量
    2. @pricelow,@pricehigh,@priceavg在此处用来接收返回值
    3. 该调用并不显示任何数据，只返回变量。
*/
call productprice2(@pricelow,@pricehigh,@priceavg);

/*查看变量*/
select @pricelow,@pricehigh,@priceavg;

结果展示

@pricelow	@pricehigh	@priceavg
1	2.50	55.00

示例三：传入和传出

/*创建存储过程*/
create procedure ordertotal(
    in onnumber int,
    out ototal decimal(9,2)
)
begin
    select sum(item_price*quantity) from orderitems where order_num=onnumber into ototal;
end;

/*调用存储过程*/
call ordertotal(20009,@ototall);

/*查看变量*/
select @ototall;

结果展示

@ototall
149.87

示例四：综合

/*
    创建存储过程
    判断逻辑：针对有些顾客要收营业税，有些则不
    taxable为1则征收
    taxable为0则不征收
*/
create procedure ordertotal(
    in onnumber int,
    in taxable boolean,
    out ototal decimal(8,2)
)
begin
    -- declare variable for total 声明一个总量（局部变量）
    declare total decimal(8,2);

    # declare tax percentage 交税的百分比
    declare taxpercent int default 6;

    -- 销售额=销售总量×单价
    select sum(item_price*quantity) from orderitems
    where order_num=onnumber into total;

    -- 判断要不要交税；注意if格式
    if taxable then
        select total+(total/100*taxpercent) into total;
    end if;
    select total into ototal;
end;

/*不交税的调用过程*/
call ordertotal(20005,0,@total);

/*交税的调用过程*/
call ordertotal(20005,1,@total1);

/*展示结果*/
select @total,@total1;

结果展示

	@total	@total1
1	149.87	158.86