13.5.3　游标和控制结构

现在，让我们来分析一个更复杂的例子。在这个例子中，我们将编写一个存储过程，该存储过程将计算出最大金额的订单，并且返回该订单的orderid（很明显，通过一个简单的查询，就可以计算出该数目，但是这个简单的示例只是说明了如何使用游标和控制结构）。该存储过程的代码如程序清单13-4所示。

程序清单13-4　control_structures_cursors.sql——使用游标和循环来处理一个结果集

Procedure to find the orderid with the largest amount

could be done with max,but just to illustrate stored procedure principles

delimiter//

create procedure largest_order(out largest_id int)

begin

declare this_id int;

declare this_amount float;

declare l_amount float default 0.0;

declare l_id int;

declare done int default 0;

declare continue handler for sqlstate'02000'set done=1;

declare c1 cursor for select orderid,amount from orders;

open c1;

repeat

fetch c1 into this_id,this_amount;

if not done then

if this_amount＞l_amount then

set l_amount=this_amount;

set l_id=this_id;

end if;

end if;

until done end repeat;

close c1;

set largest_id=l_id;

end

//

delimiter;

以上代码使用了控制结构（条件语句和循环语句）、游标和声明句柄。下面，我们逐行分析以上代码。

在该存储过程的开始处，声明了一些在该存储过程中使用的局部变量。this_id和this_amount变量保存了当前行的orderid和amount值。l_amount和l_id变量用来存储最大的订单金额和与之对应的ID。由于需要将每一个值与当前最大值进行比较，可以将当前最大值初始化为0。

下一个变量被声明为done，初始化为0（false）。这个变量是循环标记。当遍历了所有需要查看的行，可以将该变量设置为1（true）。

以下代码行：

declare continue handler for sqlstate'02000'set done=1;

是一个声明句柄。它类似于存储过程中的一个异常。在continue句柄和exit句柄中，也可以使用它。就像以上代码所显示的，continue句柄执行了指定的动作，并且继续存储过程的执行。exit句柄将从最近的begin…end代码块中退出。

声明句柄的下一个部分指定了句柄被调用的时间。在这个例子中，该句柄将在sqlstate'02000'语句被执行时调用。你可能会奇怪，这是什么意思，因为该语句非常神秘。这意味着，该句柄将在无法再找到记录行后被调用。我们将逐行处理一个结果集，而且当遍历了所有需要处理的记录行时，这个句柄将被调用。也可以指定等价的FOR NOT FOUND语句。其他选项还包括SQLWARNING和SQLEXCEPTION。

接下来就是游标。一个游标类似于一个数组；它将从一个查询获得结果集（例如，mysqli_query()所返回的），并且允许一次只处理一行（例如，我们可能会使用mysqli_fetch_row()函数）。分析以下游标：

declare c1 cursor for select orderid,amount from orders;

这个游标名称为c1。这只是它将要保存内容的定义。该查询还不会被执行。

接下来一行代码：

open c1;

真正运行这个查询。要获得每一个数据行，必须运行一个fetch语句。可以在一个repeat循环中完成此操作。在这个例子中，循环语句如下所示：

repeat

…

until done end repeat;

请注意，只有在循环语句块的末尾才会检查循环条件。存储过程还支持while循环，如下形式所示：

while condition do

…

end while;

此外，还支持loop循环语句，如下形式所示：

loop

…

end loop

这些循环没有内置的循环条件，但是可以通过leave语句退出循环。

请注意，存储过程不支持for循环。

继续这个例子，以下代码将获得一个数据行：

fetch c1 into this_id,this_amount;

以上代码行将从游标查询中获得一个数据行。该查询所获得两个属性保存在两个指定的局部变量中。

我们可以检查一个数据行是否被获得，然后再将当前循环量与最大的存储值进行比较，通过两个IF语句的方式，如下所示：

if not done then

if this_amount＞l_amount then

set l_amount=this_amount;

set l_id=this_id;

end if;

end if;

请注意，变量值将通过set语句进行设置。

除了if…then语句外，存储过程还支持if…then…else语句结构，如下形式所示：

if condition then

…

[elseif condition then]

…

[else]

…

end if

此外，也可以使用case语句，如下形式所示：

case value

when value then statement

[when value then statement…]

[else statement]

end case

回到这个例子，在循环语句末尾，将执行一些清除操作：

close c1;

set largest_id=l_id;

close语句将关闭这个游标。

最后，将所计算出的最大值赋值给OUT参数。不能将该参数作为临时变量，只能用来保存最终值（这种用法类似于其他一些编程语言，例如Ada）。

如果按照以上方式创建了这个存储过程，可以像调用其他存储过程一样调用这个存储过程：

call largest_order(@l);

select@l;

将获得类似于如下所示的输出：

+———+

|@l|

+———+

|3|

+———+

你可以自己检查计算结果是否正确。