[学会MySql系列] 第八篇：视图和存储过程

11. 视图是什么

视图就相当于一个虚拟的表，一张索引的表，其中的内容只是真实表中内容的引用。
视图的灵活使用能够极大的简化工作，比如可以表联结作为中间过程建立视图，以后的所有检索都在视图中进行，再比如需要检索结果结构化处理的，我先将所有数据按照结构化生成视图，作为中间过程，以后所有操作都在视图中进行，这样就减少了语句。

11.1 创建、删除、更新视图

创建视图
创建视图用CREATE VIEW子句

#CREATE VIEW viewname AS
# ...
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;

上述语句创建了一个联结三个表的视图，其中customers是客户表，orders是联系人表，orderitems是订购商品表。

那么对于订购某商品的客户相关信息查询，就直接从视图里面搜索就可以了，极大简化SQL语句。

删除视图
删除采用DROP VIEW viewname;

更新视图
更新视图可以删除再重建，也可以通过CREATE OR REPLACE VIEW来实现，这里的更新视图主要指的是视图结构。

11.2 视图格式化检索的结果

还记得对于某一个表搜索出来人名和国籍想要展示成name(nation)这种格式吗？如果我经常会用到，那岂不是每一次使用都需要先处理成这种形式，会很麻烦。

使用视图能够简化，只要建立如下视图，以后再视图里面进行搜索或者其他操作都能够满足name(nation)这种格式。

CREATE VIEW vendorlocation AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
	AS vend_title
FROM vendors
ORDER BY vend_name;

12. 存储过程

12.1 创建、删除、调用过程

储存过程的概念类似于程序语言种的脚本或者函数之类，可以通过调用来实现其内部功能。
（也类似于将一定的语句封装成函数、子程序，以供调用。）

储存过程的创建如下

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

上例是创建了一个简单的过程，主要就是计算商品均价，其中

1. 定义过程语句：CREATE PROCEDURE productpricing
2. 定义过程体：BEGIN 到 END之间是过程体，BEGIN是过程体开始，END是过程体结束
3. 重置语句终止符：由于在过程体中需要用到；号，所以需要先将语句的终止符切换成其他（delimiter $$），待定义完成后再切换回来（delimiter ;）

这组成了最简单的过程。

调用格式如下

CALL productpricing();

结果

+--------------+
| priceaverage |
+--------------+
|     166.6667 |
+--------------+

删除储存过程语句格式如下

DROP PROCEDURE productpricing;

• 注意调用格式和删除格式的区别。

12.2 存储过程详解

12.2.1 存储结构体

上例中是简单的过程，完整的过程结构如下所示

CREATE [DEFINER={user | current_user}]
		PROCEDURE procedurename
				([parameter:IN|OUT|INOUT param_name datatype])
						[characteristic:COMMENT 'string'...]
				[begin_label:]BEGIN
					statement;
				END[end_label];

其中[]内的参数都是可选参数，包括DEFINER关键词，过程参数等。

1. definer：主要设定过程体的访问账户
2. parameter：设定过程的传入返回参数
3. characteristic：特性，比如comment:备注信息等
4. begine_label/end_label:过程体的begin-end块中的标签，在嵌套块中能够增强代码层次

12.2.2 变量

局部变量定义，语句如下

DECLARE variable_name datatype [default value]

其中datatype为数据类型，如int，float，date等。

变量赋值，语句如下

SET 变量名 = 表达式值
#用户变量名要以@为开头

使用INTO关键词也能达到赋值的目的

SELECT expression INTO variable

12.2.3 控制语句

12.2.3.1 条件语句

if-then-else-end if

if condition then
statement;
else
statement;
end if;

case

case
	when condition1 then
	statement;
	when condition2 then
	statement;
	else
end case

12.2.3.2 循环语句

while

while condition then
statement;
end while;

repeat end repeat

repeat
	statement;
until condition
end repeat;

loop end loop

[loop_label:]loop
statement;
if condition then
leave loop_label;
end if;
end loop;

label
可以在关键词前添加label，使用leave就可以跳出循环。

12.2 范例：带参数的过程及使用

储存过程就和函数一样，能够传递参数（传入参数和返回参数），例如

CREATE PROCEDURE productpricing
( OUT p1 DECIMAL(8,2), OUT ph DECIMAL(8,2))
BEGIN
	SELECT Min(prod_price) INTO p1
	FROM products;
	SELECT Max(prod_price) INTO ph
	FROM products;
END;

其中（）内就是传递的参数，传入用IN关键词，返回用OUT关键词，INOUT就是即是传入也是返回参数。
INTO关键词相当于赋值。

在进行调用的时候格式如下

CALL productpricing(@pricelow, @pricehigh)
#执行productpricing过程
SELECT @pricelow, @pricehigh;
#显示返回参数的值需要用上述语句

MySQL必知必会
MySQL菜鸟教程