《SQL必知必会》笔记9-使用视图view、存储过程proced

作者: hufengreborn | 来源:发表于2017-07-23 16:34 被阅读0次

1 使用视图（VIEW）

1.1 视图

视图是虚拟的表，只包含使用时动态检索数据的查询。

视图的常见应用：

重用SQL语句。
简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和提示。视图可返回与底层表的表示和格式不同的数据。

创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。

创建和使用视图的一些常见规则和限制：

与表一样，视图必须唯一命名，不能给视图取与别的视图或表相同的名字。
对于可以创建的视图数目没有限制。
创建视图，必须有足够的访问权限。
视图可以嵌套，可以利用从其他视图中检索数据的查询来构造视图。
视图不能索引，也不能有关联的触发器或默认值。
有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。

1.2 创建视图CREATE VIEW

视图是虚拟的表，只包含使用时动态检索数据的查询。

视图用CREATE VIEW语句来创建。
使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
用DROP删除视图，其语法为DROP VIEW viewname;。
更新视图时，可先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

1.2.1 利用视图简化复杂的联结

常见的视图应用是隐藏复杂的SQL。

（1）先创建ProductCustomers视图。

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;

（2）检索订购了产品RGAN01的顾客。

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

1.2.2 用视图重新格式化检索出的数据

将供应商Vendors表中的vend_name和vend_country合并输出显示。

CREATE VIEW VendorLocations AS
SELECT concat(RTRIM(vend_name), '(', RTRIM(vend_country), ')')
AS vend_title
FROM Vendors;

SELECT * 
FROM VendorLocations;

1.2.3 用视图过滤不想要的数据

定义CustomerEmailList视图，过滤没有Email的顾客。

CREATE VIEW CustomerEmailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

SELECT * 
FROM CustomerEmailList;

1.2.4 使用视图与计算字段

检索某个订单的产品，计算每种产品的总价格。

CREATE VIEW OrderItemsExpanded AS
SELECT order_num, prod_id, quantity, item_price,
       quantity * item_price AS expanded_price
FROM OrderItems;

SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;

1.2.5 更新视图

迄今为止的所有视图都是和SELECT语句使用的。然而，视图的数据能否更新？答案视情况而定。

通常，视图是可更新的，即，可以对它们使用INSERT、UPDATE和DELETE。更新一个视图将更新其基表，如果对视图增加或者删除行，实际上是对其基表增加或删除行。

但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。

换句话说，如果视图定义中有以下操作，则不能进行视图的更新。

分组（使用GROUP BY和HAVING）。
联结。
子查询。
并。
聚集函数（MIN()、COUNT()等）。
DISTINCT。
导出（计算）列。

换句话说，本章的许多例子的视图都是不可更新的。这听上去好像是一个严重的限制，但实际上不是，因为视图主要用于数据检索。

一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）。

2 使用存储过程

2.1 存储过程

迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。

比如，考虑以下情况：

为了处理订单，需要核对以保证库存中有相应的物品。
如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以及反映正确的库存量。
库存中没有的物品需要订购，这需要与供应商进行某种交互。
关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。

执行上述的例子，需要针对许多表的多条SQL语句。此外，需要执行的具体语句及其次序也不是固定的，它们可能会（和将）根据哪些物品在库存中，哪些不在而变化。

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

2.2 为什么要使用存储过程

使用存储过程的主要理由：

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

使用存储过程的3个主要好处就是：简单、安全、高性能。

存在的缺陷：

一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

2.3 使用存储过程

2.3.1 执行存储过程

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

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

执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

2.3.2 创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
    SELECT AVG(prod_price) AS priceaverage
    FROM Products;
END;

由于默认的MySQL语句分隔符为;，如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会导致存储过程中的SQL出现句法错误，正如上图所示。

解决办法就是使用更改命令行实用程序的语法分隔符，如下所示：

DELIMITER //          # 临时改成//分隔符

CREATE PROCEDURE productpricing()
BEGIN
    SELECT AVG(prod_price) AS priceaverage
    FROM Products;
END//

DELIMITER ;     # 改成默认;分隔符，不然以后写的SQL语句都要以//作为结尾，才不会报错。

调用存储过程：

CALL productpricing();

CALL productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号，即使不传递参数也需要。

可以看作调用一个定义好的函数，一定要带()符号。

2.3.3 删除存储过程

删除刚创建的存储过程：

DROP PROCEDURE productpricing;

删除存储过程的时候，只需给出存储过程名即可，不需要带上()符号。

** 建议：如果指定的存储过程不存在的话，则DROP PROCEDURE将会产生错误，为防止出现错误，可使用DROP PROCEDURE IF EXISTS。**

DROP PROCEDURE IF EXISTS productpricing;

2.3.4 使用带参数的存储过程

productpricing只是一个简单的存储过程，它简单地显示SELECT语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

注意：创建下面的存储过程的时候，记得先删除以前创建的productpricing。

DELIMITER //

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 //

DELIMITER ;

此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。

存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

为调用此存储过程，必须指定3个变量名：

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

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

为了显示检索出的产品平均价格：

SELECT @priceaverage;

为获得3个值，可使用以下语句：

SELECT @pricehigh, @pricelow, @priceaverage;

创建另一个存储过程，这次使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计：

DELIMITER //

CREATE PROCEDURE ordertotal(
    IN   onumber  INT,
    OUT  ototal   DECIMAL(8,2)
)
BEGIN
    SELECT SUM(item_price * quantity)
    FROM OrderItems
    WHERE order_num = onumber
    INTO ototal;
END //

DELIMITER ;

调用该存储过程时，必须给ordertotal传递两个参数：第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。

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

CALL ordertotal(20009, @total);
SELECT @total;

2.3.5 建立智能存储过程

上述的所有存储过程基本上都是封装MySQL简单的SELECT语句，只有在存储过程内包含业务规则和智能处理时，它们的作用才能真正显现出来。

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

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

存储过程的完整工作如下（记得先删除以前创建的ordertotal存储过程）：

DELIMITER //

CREATE PROCEDURE ordertotal(
    IN    onumber   INT,
    IN    taxable   BOOLEAN,
    OUT   ototal    DECIMAL(8,2)
)
BEGIN
    # 声明局部变量
    DECLARE total    DECIMAL(8,2);
    DECLARE taxrate  INT   DEFAULT  6;

    SELECT SUM(item_price * quantity)
    FROM OrderItems
    WHERE order_num = onumber
    INTO total;

    IF taxable THEN
        SELECT total + (total/100 * taxrate) INTO total;
    END IF;

    # 结果保存到ototal中
    SELECT total INTO ototal;
END //

DELIMITER ;

DECLARE语句定义了两个局部变量，DECLARE要求指定变量名和数据类型，它支持可选的默认值。IF语句检查taxable是否为真，如果为真，则用另一个SELECT语句增加营业税到局部变量total。最后，将局部变量total保存到ototal。

调用该存储过程：

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

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

2.3.6 检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：

SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。

SHOW PROCEDURE STATUS;

可看出，SHOW PROCEDURE STATUS;显示了太多无关紧要的信息，为限制其输出，可以使用LIKE指定一个过滤模式。例如：

SHOW PROCEDURE STATUS LIKE 'ordertotal';

如果您发现文中有不清楚或者有问题的地方，请在下方评论区留言，我会根据您的评论，更新文中相关内容，谢谢！