13.视图和存储过程

2023-10-12 19:37:46

视图

视图简介

什么是视图

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

示例

# 此查询⽤来检索订购了某种产品的顾客。
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 = 'RGAN01';

为了使任何使用这个数据的人都不需要了解表的结构、表的联结。

将查询包装成一个名为ProductCustomers的虚拟表，使用视图

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

这就是视图的作⽤。ProductCustomers是⼀个视图，作为视图，它不包含任何列或数据，包含的是⼀个查询（与上⾯⽤以正确联结表的相同查询）。

视图的优点

为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将他们集中在一千七，从而方便用户进行数据查询和处理。
屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表的结构，并且数据库表的更改也不影响用户对数据库的使用。
简化用户权限的管理。只需授予用户使用试图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。
便于数据共享。各用户不必都定义和存储自己所需的数据，而可共享数据库的数据，这样，同样的数据只需存储一次。
可以重新组织数据以便输出到其他应用程序中。

性能问题

因为试图不包含数据，所以每次使用时，都必须处理查询执行时所需要的所有检索。如果你⽤多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使⽤了⼤量视图的应⽤前，应该进⾏测试。

注意事项：

只有在当前数据库中才能创建视图。视图的命名必须遵循标识符命名规则，不能与表同名。
不能把规则、默认值或触发器与视图相关联。

视图的操作

创建视图

CREATE VIEW virw_name AS 
	SELECT * FROM employees WHERE employee_id = '001';

删除视图

DROP VIEW view_name;

存储过程

存储过程简介

什么是存储过程

存储过程就是为以后使用而保存的一条或多条SQL语句。

存储过程的优点

通过把处理封装在一个易用的单元中，可以简化复杂操作。
由于不要求反复建⽴⼀系列处理步骤，因⽽保证了数据的⼀致性。开发者们都使用同一存储过程，保证了数据的一致性。
如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使⽤它的⼈员甚⾄不需要知道这些变化。这⼀点的延伸就是安全性。
因为存储过程通常以编译过的形式存储，在操作中，只需从告高速缓存器中调用已编译好的二进制代码执行，提高了系统性能。
存在⼀些只能⽤在单个请求中的SQL元素和特性，存储过程可以使⽤它们来编写功能更强更灵活的代码。

使⽤存储过程有三个主要的好处，即简单、安全、⾼性能。

缺点：

在不同DBMS之间，存储过程语法有所不同。可移植性差。
编写存储过程比基本SQL语句复杂，需要更高的技能，更丰富的经验。

存储过程的操作

执行存储过程

EXECUTE 存储过程的名字

创建存储过程

DELIMITER $$                                          # 声明语句结束符为$$ 
CREATE PROCEDURE procedure_name (IN params int)       # 声明存储过程
BEGIN 												  # 存储过程的开始符号
SELECT * FROM employees
END $$												  # 存储过程的结束符号

delimiter;　　#将语句的结束符号恢复为分号

示例

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter;　　#将语句的结束符号恢复为分号

解析：默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

调用存储过程：

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       7 |      1 |       57 |   3 |    0 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
5 rows in set (0.00 sec)
 
 
mysql> call delete_matches(57);
Query OK, 1 row affected (0.03 sec)
 
 
mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
4 rows in set (0.00 sec)

解析：在存储过程中设置了需要传参的变量p_playerno，调用存储过程的时候，通过传参将57赋值给p_playerno，然后进行存储过程里的SQL操作

码农公寓

视图