一个存储过程是一个可编程的函数,它在MySQL中创建并保存。它可以由SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看作是对编程中面向对象方法的模拟。它们允许控制数据的访问方式。
首先,让我们来了解一个简单的示例。
13.5.1 基本示例
程序清单13-1显示了一个存储过程的声明。
程序清单13-1 basic_stored_procedure.sql——声明一个存储过程
#Basic stored procedure example
delimiter//
create procedure total_orders(out total float)
BEGIN
select sum(amount)into total from orders;
END
//
delimiter;
下面,让我们逐行分析以上代码。
第一行语句:
delimiter//
将语句末尾的分隔符从当前值(这个分隔符通常是分号,除非以前改变了分隔符)改为双斜杠字符。这样做的目的是可以在存储过程中使用分号分隔符,这样MySQL就会将分号当作是存储过程的代码,不会执行这些代码。
接下来的语句:
create procedure total_orders(out total float)
创建了实际的存储过程。该存储过程的名称是total_orders。它只有一个total参数,该参数是需要计算的值。OUT表示该参数将被传出或返回。
参数也可以声明为IN,表示该值必须传入到存储过程,或者INOUT,表示该值必须传入但是可以被存储过程修改。
float表示参数的类型。在这个例子中,将返回orders表中的所有订单的总数。orders列的类型为float,因此该返回类型也必须是float。可接受的数据类型映射到可供使用的列类型。
如果希望使用多个参数,可以提供一个由逗号间隔的参数列表,就像在PHP中的一样。过程体必须封闭在BEGIN和END语句中。它们都是对PHP中的括号({})的模拟,因为它们可以标识一个语句块。
在过程体中,只需运行一个SELECT语句。与常规SELECT语句的唯一差别在于使用into total子句将查询结果载入到total参数。
在声明了过程后,可以将分隔符重新设置为分号,如下语句所示:
delimiter;
在过程声明之后,可以使用call关键字调用该过程,如下所示:
call total_orders(@t);
这个语句将调用total_orders过程并且传入一个用来保存结果的变量。要查看该结果,需要查看改变量,如下语句所示:
[email protected];
其结果应该类似于如下所示:
+-----------------+
|@t|
+-----------------+
|289.92001152039|
+-----------------+
与创建过程的方法类似,可以创建一个函数。函数接收输入参数并且返回一个唯一值。创建函数的基本语法几乎相同。程序清单13-2显示了一个简单的函数。
程序清单13-2 basic_function.sql——声明一个存储函数
#Basic syntax to create a function
delimiter//
create function add_tax(price float)returns float
return price*1.1;
//
delimiter;
可以看到,该示例使用了function关键字,而不是procedure关键字。此外,二者还存在一些其他差异。
参数不必通过IN或OUT来指定,因为所有参数都是IN,或输入参数。在参数列表之后是returns float子句,它指定了返回值的类型。需要再次提到的是,该值可以是任何有效的MySQL类型。
使用return语句,可以返回一个值,就像在PHP中所介绍的。
请注意,这个示例并没有使用BEGIN和END语句。可以使用它们,但是它们并不是必需的。就像在PHP中,如果一个语句块只包含了一个语句,就不需要标注该语句块的开始和结束。
调用函数与调用过程存在一些差异。可以以调用内置函数的相同方式调用一个存储函数。例如,
select add_tax(100);
该语句应该返回如下所示的输出:
+-------------+
|add_tax(100)|
+-------------+
|110|
+-------------+
在定义了过程和函数之后,可以使用如下所示的语句来查看定义这些过程和函数的代码:
show create procedure total_orders;
或者
show create function addtax;
也可以使用如下所示的语句来删除它们:
drop procedure total_orders;
或者
drop function add_tax;
存储过程提供了使用控制结构、变量、DECLARE句柄(就像异常)的功能,以及游标这个重要的概念。在接下来的内容中,我们将简单介绍这些概念。