MySql入门学习：存储过程、游标及触发器

溪午闻璐

已于 2022-02-06 16:38:08 修改

阅读量822

点赞数

于 2022-02-06 16:30:57 首次发布

本文链接：https://blog.csdn.net/sssxlxwbwz/article/details/122796369

版权

数据库专栏收录该内容

21 篇文章

订阅专栏

一、存储过程

大多数SQL语句都是针对一个或多个表的单条语句。但是，并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。例如，如下情形：

为了处理订单，需要核对以保证库存中有相应的物品。
如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。
库存中没有的物品需要订购，这需要与供应商进行某种交互。
关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。

执行这个处理需要针对许多表的多条MySQL语句。此外，需要执行的具体语句及其次序也不是固定的，它们可能会根据哪些物品在库存中哪些不在而变化。我们可以通过单独编写每条语句，并根据结果有条件地执行另外的语句。在每次需要这个处理时都必须做这些工作。针对此，其实我们可以创建存储过程。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

1.1 使用存储过程的目的

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

使用存储过程有3个主要的好处，即简单、安全、高性能。

1.2 使用存储过程

使用存储过程需要知道如何执行（运行）它们。存储过程的执行远比其定义更经常遇到。

1.2.1 创建存储过程

示例如下，是一个返回产品平均价格的存储过程：

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

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

1.2.2 执行存储过程

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

示例如下：

CALL productpricing()

CALL productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号。

1.2.3 删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令：

DROP PROCEDURE productpricing;

1.2.4 使用参数

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

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语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

存储过程的参数允许的数据类型与表中使用的数据类型相同。

为调用此修改过的存储过程，必须指定3个变量名，如下所示，所有MySQL变量都必须以@开始：

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

在调用时，这条语句并不显示任何数据。它返回以后可以显示（或在其他处理中使用）的变量，如下所示：

SELECT @pricehight,@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;

number定义为IN，因为订单号要被传入存储过程。total定义为OUT，因为要从存储过程返回合计。SELECT语句使用这两个参数，WHERE子句使用number选择正确的行，INTO使用total存储计算出来的合计。

调用该存储过程：

CALL ordertotal(20005,@total);

显示total结果：

SELECT @total

1.2.5 复杂存储过程

以上使用的所有存储过程基本上都是封装MySQL简单的SELECT语句。虽然它们全都是有效的存储过程例子，但它们所能完成的工作你直接用这些被封装的语句就能完成。只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来。

考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，你需要做下面几件事情：

获得合计（与以前一样）；
把营业税有条件地添加到合计；
返回合计（带或不带税）。

整个存储过程如下：

-- name:ordertotal
-- parameters:onumber = order number
--            taxable = 0 if not taxable,1 if taxable
--            ototal = order total variable
create PROCEDURE ordertotal(
       IN onumber INT,
       IN taxable Boolean,
       OUT ototal DECIMAL(8,2)
) COMMENT "obtain order total, optionally add tax"
BEGIN
    -- DECLARE variable for total
    DECLARE total DECIMAL(8,2);
    -- DECLARE tax percentage
    DECLARE taxrate INT DEFAULT 6;
     -- Get the order total
    SELECT SUM(item_price*quantity)
    from orderitems
    WHERE order_num = onumber
    INTO total;
    -- is this taxable?
    IF taxable THEN
       -- yes, so add taxrate to the total
       SELECT total + (total/100*taxrate) INTO total;
    END IF;
    -- And finally, save to out variable
    select total into ototal;
END

此存储过程有很大的变动。首先，增加了注释（前面放置--）。在存储过程复杂性增加时，这样做特别重要。添加了另外一个参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。SELECT语句已经改变，其结果存储到total（局部变量）中。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到ototal。

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

调用上述存储过程：

CALL ordertotal(20005,0,@total);
SELECT @total;

CALL ordertotal(20005,1,@total);
SELECT @total;

BOOLEAN值指定为1表示真，指定为0表示假（实际上，非零值都考虑为真，只有0被视为假）。通过给中间的参数指定0或1，可以有条件地将营业税加到订单合计上。

二、游标

MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行（零行或多行）。有时，需要在检索出来的行中前进或后退一行或多行。这就需要使用到游标。

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

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

不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

2.1 游标使用步骤

使用游标涉及几个明确的步骤：

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

2.2 创建游标

游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

示例：下面的语句定义了名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句：

CREATE PROCEDURE processorders()
BEGIN
	-- 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

2.3 使用游标

游标用OPEN CURSOR语句来打开，CLOSE CURSOR语句来关闭。

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

示例：游标中检索单个行（第一行）

CREATE PROCEDURE processorders()
BEGIN
	-- 声明变量
	DECLARE o INT;
	-- 声明游标
  DECLARE ordernumbers CURSOR
	FOR 
	SELECT order_num FROM orders;

	-- 打开游标
  OPEN ordernumbers;

	-- GET order number
	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
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	-- 打开游标
  OPEN ordernumbers;

	-- LOOP
	REPEAT
		-- get order number
		FETCH ordernumbers INTO o;
	UNTIL done END REPEAT;
	
	-- 关闭游标
	CLOSE ordernumbers;
	
END

与前一个例子一样，这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是，这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定）。为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。那么，done怎样才能在结束时被设置为真呢？答案是用以下语句：

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000'出现时，SET done=1。SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

三、触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例如：

每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；
每当订购一个产品时，都从库存数量中减去订购的数量；
无论何时删除一行，都在某个存档表中保留一个副本。

所有这些例子的共同之处是它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

DELETE；
INSERT；
UPDATE。

其他MySQL语句不支持触发器。

3.1 创建触发器

在创建触发器时，需要给出4条信息：

唯一的触发器名；
触发器关联的表；
触发器应该响应的活动（DELETE、INSERT或UPDATE）；
触发器何时执行（处理之前或之后）。

触发器用CREATE TRIGGER语句创建。示例如下：

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @Test;

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。

仅支持表 只有表才支持触发器，视图不支持（临时表也不支持）。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

3.2 删除触发器

为了删除一个触发器，可使用DROP TRIGGER语句，如下所示：

DROP TRIGGER newproduct;

3.3 使用触发器

3.3.1 INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）
对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

示例：

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE
INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。