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

た入场券 2022-11-25 11:39 212阅读 0赞

# 一、视图 #

## 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';

查询结果

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>cust_name</th> 
   <th>cust_contact</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Yosemite Place</td> 
   <td>Y Sam</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Coyote Inc.</td> 
   <td>Y Lee</td> 
  </tr> 
 </tbody> 
</table>

-- 创建视图
    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';

查询结果

从示例中可以看出，所谓视图就是封装了一堆的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();

结果展示

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>priceAvg</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>16.133571</td> 
  </tr> 
 </tbody> 
</table>

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

/* 创建存储过程: 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;

结果展示

<table> 
 <thead> 
  <tr> 
   <th>@pricelow</th> 
   <th>@pricehigh</th> 
   <th>@priceavg</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2.50</td> 
   <td>55.00</td> 
  </tr> 
 </tbody> 
</table>

### 示例三：传入和传出 ###

/*创建存储过程*/
    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;

结果展示

<table> 
 <thead> 
  <tr> 
   <th>@ototall</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>149.87</td> 
  </tr> 
 </tbody> 
</table>

### 示例四：综合 ###

/* 创建存储过程 判断逻辑：针对有些顾客要收营业税，有些则不 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;

结果展示

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>@total</th> 
   <th>@total1</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>149.87</td> 
   <td>158.86</td> 
  </tr> 
 </tbody> 
</table>