MySQL必知必会-视图、存储过程-oneNeko Blog

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询，不包含表中应该有的任何列或数据，它包含的是一个SQL查询视图的常见应用：

重用SQL语句
简化复杂的SQL操作
使用表的组成部分而不是整个表
保护数据。可以给用户表的特定部分的访问权限而不是整个表的访问权限
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

视图建立之后可以用与表基本相同的方式利用他们。

视图的规则和限制

与表一样，视图必须唯一命名
对于可以创建的视图数目没有限制
为了创建视图，必须具有足够的访问权限
视图可以嵌套
ORDER BY可以用于视图中，但如果从该视图检索数据的查询来构造一个视图，那么该视图的ORDER BY将被覆盖
视图可以和表一起使用

使用视图

视图用CREATE VIEW语句创建
使用SHOW CREATE VIEW viewname来查看创建视图的语句
用DROP删除视图，其语法为DROP VIEW viewname
更新视图时，可以先用DROP再用CREATE再用CREATE，也可以直接用CREATE OR REPLACE VIEW

$ 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;

之后可以复用视图方便使用

更新视图

视图时可更新的，更新一个视图将更新其基表。但并非所有视图都是可更新的，如果视图定义有以下操作，则不能进行视图的更新

分组
联结
子查询
并
聚集函数
DISTINCT
导出（计算）列

一般，应该将视图用于检索而不用于更新

存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理

通过把处理封装在容易使用的单元中，简化复杂的操作
由于不要求反复建立一系列处理步骤，这保证了数据的完整性，这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大
简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。通过存储过程限制对基础数据的访问减少了数据讹误的机会
提升性能。比使用单独的SQL语句要快
存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

换句话说，存储过程有三个好处，即简单、安全、高性能

使用存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接收存储过程的名字以及需要传递给它的任意参数，如：

# 执行 productpricing 的存储过程，它计算并返回产品的最低、最高和平均价格
$ CALL productpricing(@pricelow,
                      @pricehigh,
                      @priceaverage
                      );

存储过程可以显示结果，也可以不显示结果

创建存储过程

$ CREATE PROCEDURE productpricing()
    BEGIN
        SELECT Avg(prod_price) AS priceaverge
        FROM products;
    END;

如果存储过程接受参数，它们将在()中列举出来。BEGIN和END用来限定存储过程体，过程体本身仅是一个简单的SELECT语句

默认的MySQL语句分隔符为;，如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误解决办法是临时更改命令行实用程序的语句分隔符，最后恢复为原来的语句分隔符，如下所示

DELIMITER //

CREATE PROCEDURE productpricing()
    BEGIN
        SELECT Avg(prod_price) AS priceaverge
        FROM products;
    END //

DELIMITER ;

删除存储过程

DROP PROCEDURE productpricing;

仅当存在时删除：使用DROP PROCEDURE IF EXISTS

使用参数

一般存储过程并不显示结果，而是把结果返回给你指定的变量

CREATE PROCEDURE productpricing(
    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;

关键词OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）

MySQL支持IN(传入)、OUT(传出)、INOUT(传入和传出)，通过指定INTO关键字保存到相应的变量

注意：记录集不是允许的类型，因此，不能通过一个参数返回多个行和列

调用时需要指定3个变量名，如下所示

$ CALL productpricing(
        @pricelow,
        @pricehigh,
        @priceaverage
    );

所有MySQL变量都必须以@开始

为了显示检索出的产品平均价格，可如下进行：

$ SELECT @priceaverage;
$ SELECT @priceaverage,@pricelow,@priceaverage;

使用IN和OUT参数

CREATE PRODURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num=onumber
    INTO ototal
END;

# 调用
CALL ordertotal(20005,@total);

# 显示
SELECT @total

建立智能存储过程

迄今为止使用的所有存储过程基本上都是封装MySQL简单的SELECT语句。它们所能完成的工作你直接用这些被封装的语句就能完成，只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来

考虑这个场景，你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客，那么，你需要做下面几件事情

获得合计
把营业税有条件地添加到合计
返回合计（带或不带税）

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order order total, optionally adding tax'
BEGIN
    -- Declare variable for total
    DECLARE total DECIMAL(8,2);
    -- Declare tax percentage
    DECLARE taxrate INT DEFAULT 6;

    -- Get the order total
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total

    -- Is this taxable?
    IF taxable THEN
        -- Yes, so add taxrate to the total
        SELECT total+(total/100*taxrate) INTO total;
    END IF;

    -- And finally,save to out variable
    SELECT total INTO ototal;

END;

使用

CALL ordertotal(20005,0,@total);
SELECT @total

检查存储过程

# 显示用来创建一个存储过程的CREATE语句
SHOW CREATE PROCEDURE ordertotal;

# 列出所有存储结果
SHOW PROCEDURE STATUS;

# 限制过程状态结果
SHOW PROCEDURE STATUS LIKE 'ordertotal';

MySQL必知必会-视图、存储过程