一. 使用存储过程
1. 存储过程
大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。例如：
为了处理订单，需要核对以保证库存中有相应物品，若库存有物品，该物品需要预定并且减少可用物品数量以反映正确的库存量；若库存中没有物品来订购，需要和供应商来进行交互。最后关于哪些物品入库或是退订，需要通知相应客户。
这个例子虽不完整，但体现出我想表达的意思。执行这个处理需要针对多表的多条SQL语句，并且执行的具体语句及其次序也不是固定的。它们可能会根据哪些物品是否在库存中而变化。这样的代码如何进行编写？我们就可以通过创建存储过程来解决。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。
2. 使用存储过程的原因
（一）通过把处理封装在容易使用的单元中，简化复杂的操作。
（二）由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误，需要执行的步骤越多，出错的可能性就越大。防止错误保证数据的一致性。
（三）简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误的机会。
（四）提高性能，因为使用存储过程比使用单独的SQL语句要快。
（五）存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
同时我们也必须知道它的一些缺陷。
（一）一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
（二）你可能没有创建存储过程的安全访问权限。
简单、安全、高性能。尽管有一些缺陷，存储过程还是非常有用的，并且应该尽可能地使用。
3. 使用存储过程
（1） 执行存储过程
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字已经需要传递给它的任意参数。
Call productpricing (@pricelow,
@pricehigh,
@priceaverage);
该语句执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。存储过程可以显示结果，也可以不显示结果。
（2） 创建存储过程
//例如创建一个返回产品平均价格的存储过程
Create procedure productpricing()
Begin
Select avg(prod_price) as priceaverage
From products;
End;
//在使用MySQL命令窗口创建时，有两个;号，会产生句法错误。
//解决办法是临时更改命令行实用程序的语句分隔符
//在第一句前添加 DELIMITER //，告诉命令行实用程序使用//作为新的语句结束分隔符。
此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来, 此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。
在MySQL处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。
//执行存储过程
call productpricing();
（3） 删除存储过程
存储过程在创建之后，被保存在服务器上以供使用直至被删除。删除命令从服务器中删除存储过程：Drop procedure productpricing; 请注意没有使用后面的()，只给出存储过程名。
（4） 使用参数
//创建一个存储过程
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 //
此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。OUT指出相应的参数用来从存储过程传出一个值。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，是一系列SELECT语句，用来检索值，然后保存到相应的变量。
为调用此修改过的存储过程，必须指定3个变量名，所有MySQL变量都必须以@开始。由于此存储过程要求3个参数，因此必须正好传递3个参数。它们是存储过程将保存结果的3个变量的名字。
//调用存储过程
Call productpricing (@pricelow,
@pricehigh,
@priceaverage);
//检索显示产品平均价格
select @priceaverage;
//检索显示三个值
Select @pricehigh, @pricelow, @priceaverage;
再来看一个带有IN和OUT参数的例子。
//ordertotal接受订单号并返回该订单的合计
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;
因为订单号被传入存储过程,onumber定义为IN。要从存储过程返回合计，ototal定义为out。SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计。
//调用存储过程，第一个为传入参数订单号，第二个为包含计算出来的合计的变量名。
call ordertotal(20005,@total);
//显示
select @total;
//为了得到另一个订单的合计显示，需要再次调用存储过程，然后重新显示变量.
call ordertotal(20009,@total);
select @total;
检索结果
（5） 建立智能存储过程
例子:需要获得和上述一样的订单合计，但是对合计增加营业税，不过只是针对某些顾客，那么应该如何设计？
（一）获取合计。 （二）把营业税有条件地加入到合计。（三）返回合计。
Create procedure ordertotal(
In onumber int,
In taxable Boolean,
Out ototal decimal(8,2)
)comment ‘obtain order total, adding tax’
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;
Select total into ototal;
End;
此存储过程有很大的改动。添加了另外一个参数taxable，它是一个布尔值(指定为1表示真，指定为0表示假)。在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（默认taxrate为6%）。Select语句已经改变因此将结果存到total（局部变量）而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将total（增加或许不增加营业税）保存到ototal。Comment值不是必需的。
//测试
//不加税
Call ordertotal(20005,0,@total);
select @total;
//加税
Call ordertotal(20005,1,@total);
select @total;
上述例子结果
（6） 检查存储过程
为显示用来创建一个存储过程的CREATE语句。
show create procedure ordertotal;
为了获得包括何时、由谁创建等详细信息的存储过程列表。
show procedure status;
//可以使用like指定一个过滤模式
show procedure status like 'ordertotal';
检查存储过程
二. 使用游标
1. 游标
游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。MySQL游标只能用于存储过程（和函数）。
2. 使用游标
使用游标的几个明确步骤：
（一）在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
（二）一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
（三）对于填有数据的游标，根据需要取出（检索）各行。
（四）在结束游标使用时，必须关闭游标。
在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。
（1） 创建游标
游标用declare语句创建，并定义相应的select语句，根据需要带where和其他子句。例如
Create procedure processorders()
Begin
Declare ordernumbers cursor
For
Select order_num from orders;
End;
Declare语句用来定义和命名游标，这里为ordernumbers，在定义游标之后，可以打开它。
（2） 打开和关闭游标
游标用open ordernumbers打开，在处理open语句时执行查询，存储检索出的数据以供浏览和滚动。游标处理完成后，应当使用如下语句关闭游标：close ordernumbers. CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。
Create procedure processorders()
Begin
Declare o int;
--定义游标
Declare ordernumbers cursor
For
Select order_num from orders;
--打开游标
Open ordernumbers;
--关闭游标
Close ordernumbers;
End;
（3） 使用游标数据
在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行。
//该例子从游标中检索单个行。
Create procedure processorders()
Begin
Declare o int;
Declare ordernumbers cursor
For
Select order_num from orders;
Open ordernumbers;
Fetch ordernumbers into o;
Close ordernumbers;
End;
//其中FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。
//对检索出的数据不做任何处理。
//该例子循环检索数据，从第一行到最后一行。
Create procedure processorders()
Begin
Declare done boolean default 0;
Declare o int;
Declare ordernumbers cursor
For
Select order_num from orders;
Declare continue handler for sqlstate '02000' set done=1;
Open ordernumbers;
repeat
Fetch ordernumbers into o;
until done end repeat;
Close ordernumbers;
End;
这个例子使用FETCH检索当前order_num到声明的名为o的变量中。这个例子中的FETCH是在repeat内，因此它反复执行直到done为真。为使它起作用，用一个DEFAULT 0定义变量done。
Declare continue handler for sqlstate '02000' set done=1;
这条语句定义了一个CONTINUE HANDLER,它是在条件出现是被执行的代码。它指出当SQLSTATE‘02000’出现时，设置done为1。SQLSTATE'02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。即跳出循环。如果调用这个存储过程，它将定义几个变量和一个CONTINUE HANDLER，定义并打开一个游标，重复读取所有行，然后关闭游标。
总结一下：本节介绍了什么是存储过程以及为什么要使用存储过程。并且展示存储过程的执行和创建的语法以及使用存储过程的一些方法。又介绍了什么是游标以及为什么要使用游标，举了几个基本游标使用的例子。
