大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

数据库学习笔记——19 使用存储过程

1 存储过程

存储过程简单来说,就是为以后的使用而保存的一条或多条SQL语句的集合。可将其视为批文件,虽然它们的作用不仅限于批处理。

2 为什么要使用存储过程

既然我们知道了什么是存储过程,那么为什么要使用它们呢?有许多理由,下面列出一些主要的理由。

  • 通过把处理封装在容易使用的单元中,简化复杂的操作。
  • 由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程,则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。
  • 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减
    少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会。
  • 提高性能。因为使用存储过程比使用单独的SQL语句要快。
  • 存在一些只能用在单个请求中的SQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码。

换句话说,使用存储过程有3个主要的好处,即简单、安全、高性能。显然,它们都很重要。不过,在将SQL代码转换为存储过程前,也必须知道它的一些缺陷。

  • 一般来说,存储过程的编写比基本SQL语句复杂,编写存储过程需要更高的技能,更丰富的经验。
  • 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限,允许用户使用存储过程,但不允许他们创建存储过程。

3 使用存储过程

3.1 执行存储过程

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

call.jpg

CALL productpricing(); 执行存储过程并显示返回的结果。因为存储过程实际上是一种函数,所以存储过程名后需要有 () 符号(即使不传递参数也需要)。

3.2 创建存储过程

正如所述,编写存储过程并不是微不足道的事情。为让你了解这个过程,请看一个例子——一个返回产品平均价格的存储过程。以下是其代码:

CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END;

此存储过程名为productpricing ,用 CREATE PROCEDURE productpricing() 语句定义。如果存储过程接受参数,它们将在 () 中列举出来。此存储过程没有参数,但后跟的 () 仍然需要。 BEGIN 和 END 语句用来限定存储过程体,过程体本身仅是一个简单的 SELECT 语句。

3.3 删除存储过程

为删除刚创建的存储过程,可使用以下语句:
DROP PROCEDURE productpricing;
这条语句删除刚创建的存储过程。请注意没有使用后面的 () ,只给出存储过程名。

3.4 使用参数

productpricing 只是一个简单的存储过程,它简单地显示 SELECT 语句的结果。一般,存储过程并不显示结果,而是把结果返回给你指定的变量。

变量(variable)内存中一个特定的位置,用来临时存储数据。

以下是 productpricing 的修改版本(如果不先删除此存储过程,则不能再次创建它):

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;

关键字 OUT 指出相应的参数用来从存储过程传出一个值(返回给调用者)。
MySQL支持 IN(传递给存储过程)、 OUT (从存储过程传出,如这里所用)和 INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于 BEGINEND 语句内,如前所见,它们是一系列SELECT 语句,用来检索值,然后保存到相应的变量(通过指定 INTO 关键字)。


为调用此修改过的存储过程,必须指定3个变量名,如下所示:

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

这条 CALL 语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。

变量名 所有MySQL变量都必须以 @ 开始。

在调用时,这条语句并不显示任何数据。它返回以后可以显示(或在其他处理中使用)的变量。
为了显示检索出的产品平均价格,可如下进行:
SELECT @priceaverage;


为了获得3个值,可使用以下语句:
SELECT @pricelow ,@pricehigh ,@priceaverage;


下面是另外一个例子,这次使用 IN 和 OUT 参数。 ordertotal 接受订单号并返回该订单的合计:

CREATE PROCEDURE ordertotal (
    IN onumber INT,
    OUT total DECIMAL (8, 2)
)
BEGIN
    SELECT
        SUM(item_price * quantity)
    FROM
        orderitems
    WHERE
        order_num = onumber 
    INTO total;
END;

onumber 定义为 IN ,因为订单号被传入存储过程。 ototal 定义为 OUT ,因为要从存储过程返回合计。 SELECT 语句使用这两个参数, WHERE 子句使用 onumber 选择正确的行, INTO 使用 ototal 存储计算出来的合计。
为调用这个新存储过程,可使用以下语句:
CALL ordertotal(20005,@total);
为了显示此合计,可如下进行:
SELECT @total;


@total 已由 ordertotal 的 CALL 语句填写, SELECT 显示它包含的值。
为了得到另一个订单的合计显示,需要再次调用存储过程,然后重新显示变量:
CALL ordertotal(20009,@total);
SELECT @total;

3.5 建立智能存储过程

CREATE PROCEDURE ordertotalmye(
    IN onumber INT, -- 订单号
    IN taxable TINYINT(1),-- 布尔类型
    OUT ototal DECIMAL (8, 2)
) COMMENT 'Obtain order total,optionally adding tax'
BEGIN
#定义两个变量
DECLARE total DECIMAL (8, 2);
DECLARE taxrate INT DEFAULT 6; -- 税率,默认为6%

SELECT SUM(item_price * quantity)
FROM orderitems
WHERE order_num = onumber 
INTO total;

IF taxable THEN
    SELECT total + (total / 100 * taxrate) INTO ototal;
ELSE
SELECT total INTO ototal;
END IF;

END;

添加了另外一个参数 taxable ,它是一个布尔值(如果要增加税则为真,否则为假)。在存储过程体中,用 DECLARE 语句定义了两个局部变量。 DECLARE 要求指定变量名和数据类型,它也支持可选的默认值(这个例子中的 taxrate 的默认被设置为 6% )。 SELECT 语句已经改变,因此其结果存储到 total (局部变量)而不是 ototal 。 IF 语句检查 taxable 是否为真,如果为真,则用另一 SELECT 语句增加营业税后保存到 ototal。

COMMENT 关键字
本例子中的存储过程在 CREATE PROCEDURE 语句中包含了一个 COMMENT 值。它不是必需的,但如果给出,将在 SHOW PROCEDURE STATUS 的结果中显示。

SHOW PROCEDURE STATUS

status.png

这显然是一个更高级,功能更强的存储过程。为试验它,请用以下两条语句:

CALL ordertotalmye(20005,0,@one);
SELECT @one;
one.png
CALL ordertotalmye(20005,1,@two);
SELECT @two;
two.png

3.6 检查存储过程

为显示用来创建一个存储过程的 CREATE 语句,使用 SHOW CREATE PROCEDURE 语句:

SHOW CREATE PROCEDURE ordertotalmye;
show.png

为了获得包括何时、由谁创建等详细信息的存储过程列表,使用 SHOW PROCEDURE STATUS 。

限制过程状态结果 SHOW PROCEDURE STATUS 列出所有存储过程。为限制其输出,可使用 LIKE 指定一个过滤模式,例如:SHOW PROCEDURE STATUS LIKE '%mye';