MySQL数据库基础——7.存储过程和函数、游标

一 存储过程

简单来说，存储过程就是为我们以后的使用而保存的一条或多条MySQL语句的集合。为什么要使用存储过程？理由如下：

• 可以通过把处理封装在容易使用的单元中，简化复杂的操作。
• 由于不要求反复建立一系列的处理步骤，这保证了数据的完整性。
• 简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。
• 提高性能，使用存储过程比使用单独的SQL语句更快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

总而言之，使用存储过程有三个主要好处，简单、安全、高性能。当然，编写存储过程比基本SQL语句更复杂。

1.执行存储过程

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

call productpricing(@procelow,@pricehigh,@priceaverage);

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

2.创建存储过程

create procedure productpricing()
begin 
    select avg(prod_price) as priceaverage 
    from products;
end;

上面这个存储过程名为productpricing，用create procedure productpricing() 语句定义。如果存储过程接受参数，它们将在()中列举出来，这个例子中没有参数，但也要有()。begin和end语句用来限定存储过程体，过程体本身仅是一个简单的select语句。

用下面的语句就可以使用这个存储过程。此时的（）可以省略。

call productpricing();

3.删除存储过程

为了删除刚刚创建的存储过程，可以使用以下语句。这条语句删除刚刚创建的存储过程，只需给出存储过程名，不用加()。

drop procedure productpricing;

4.使用参数

变量（variable）：内存中一个特定的位置，用来临时存储数据。所有的MySQL变量都必须以@开始。

create procedure productpricing(
    out p1 decimal(8,2),
    out ph decimal(8,2),
    out pa decimal(8,2))
begin 
    select min(prod_price) into p1 
    from products;
    select max(prod_price) into ph
    from products;
    select avg(prod_price) into pa 
    from products; 
end;

上面这个存储过程接受三个参数，p1存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型。关键字out指出相应的参数用来从存储过程传出一个值（即返回给调用者）。

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

为了调用上面的存储过程，必须指定三个变量名，如下所示。在调用时，这条语句并不显式任何数据，它返回后可以显示（或在其他处理中使用）的变量。

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

为了显式检索出的产品平均价，可以利用如下语句。

select @priceaverage;

二 函数

函数和存储过程极为类似，过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别则在于函数总向调用者返回数据，而过程则不必返回数据。

使用过程与函数的原则：

1.如果需要多个返回值和或不返回值，使用存储过程；如果只需要返回一个值，使用函数。

2.函数不需要使用in/out模式，所有的参数都是传入（in）参数。

3.存储过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

4.可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但是存储过程不能被调用。

下面是一个函数的例子。

delimiter //
create function getTax(
in_sum float(10,2),
in_rate float(10,2)
)returns float(10,3) 
begin 
	declare total float(10,3) default 0;
	set total=in_sum*in_rate;
	return total;
end //
delimiter ;

三 游标

1.概念

我们知道，MySQL检索操作返回一组称为结果集的行，这组返回的行都是与SQL语句相匹配的行（零行或多行）。但是，使用简单的select语句没有办法得到第一行、下一行或前十行，也不存在每次一行地处理所有行的简单办法。有时需要在检索出来的行中前进或后退一行或多行，这时就需要使用游标了。

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

游标只能用于存储过程和函数。

2.创建游标

在使用游标之前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的select语句。

声明后必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来。对于填有数据的游标，根据需要取出（检索）各行。在结束游标使用时，必须关闭游标。
 

create procedure processorders() 
begin 
    declare ordernumbers cursor 
    for 
    select order_num from orders;
end;

3.打开和关闭游标

open ordernumbers;
close ordernumbers;

游标使用open cursor打开，在处理open语句时执行查询，存储检索出的数据以供浏览和滚动。在关闭游标时，close释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。（如果你不明确关闭游标，MySQL将会在到达end语句时自动关闭它）

第一个例子是从游标中检索单个行（第一行）。

delimiter //
create proceduce processorders() 
begin 
    declare o int; 
    declare ordernumbers cursor for 
    select order_num from orders; 
    open ordernumbers; 
    fetch ordernumbers into o;
    close ordernumbers; 
end //
delimiter ;

其中，fetch用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。

第二个例子是循环检索数据（从第一行到最后一行）。

delimiter //
create proceduce 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 //
delimiter ;

这个例子中的fetch在repeat内，因此它会反复执行直到done为真。在前面用default 0（代表假）定义done变量。continue handler是在条件出现时被执行的代码，当sqlstate ’02000‘出现时，set done=1。

---

下面是综合实例。

delimiter //
create proceduce processorders() 
begin 
    declare done boolean default 0;
    declare o int; 
    declare t decimal(8,2);

    declare ordernumbers cursor 
    for 
    select order_num from orders; 
    declare continue handler for sqlstate '02000' set done=1;
    creaute table if not exists ordertotals(order_num int,total decimal(8,2));
    open ordernumbers; 
    repeat
        fetch ordernumbers into o;
        call ordertotal(o,1,t);
        insert into ordertotals(order_num,total) values(o,t);
        until done 
    end repeat;
    close ordernumbers; 
end //
delimiter ;

在这个例子中，我们增加了另一个名为t的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(如果它不存在的话)，名为ordertotals。这个表将保存存储过程生成的结果。fetch像以前一样取每个order_num, 然后用call执行另一个存储过程来计算每个订单的带税的合计(结果存储到t)。最后，用insert保存每个订单的订单号和合计。此存储过程不返回数据，但它能够创建和填充另一个表，可以用一条简单的select语句查看该表:

select * from ordertotals;