华企号 后端开发 数据库使用游标检索

数据库使用游标检索

游标

之前的select语句检索出来的数据,没有办法得到第一行或者下一行

有时,需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。

游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。

在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。

MySQL游标只能用于存储过程(和函数)

使用游标

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

在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句。

一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。

对于填有数据的游标,根据需要取出(检索)各行。

在结束游标使用时,必须关闭游标。

创建游标

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;

这个存储过程并没有做很多事情,DECLARE语句用来定义和命名游标,这里为ordernumbers。存储过程处理完成后,游标就消失(因为它局限于存储过程)。

打开和关闭游标

--打开游标
OPEN ordernumbers;
--处理完成后,应当使用下句关闭游标
CLOSE ordernumbers;

CLOSE释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭
MySQL会在达到END语句时自动关闭它

使用游标数据

例:从游标中检索单个行

CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
    DECLARE o INT;

    -- Declare the cursor
    DECLARE orderumbers CURSOR
    FRO
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

	-- Get order number
    FETCH ordernumbers INTO o;

	-- Close the cursor
    CLOSE ordernumbers;

END;

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

例:循环检索数据,从第一行到最后一行

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    --Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

	--Declare continue handler
    DECLARE CONTINUE HANDLER FRO SQLSTATE '02000' SET done=1;

	--Open the cursor
    OPEN ordernumbers;

	--Loop through all rows
    REPEAT
    	--Get order numbers
        FETCH ordernumbers INTO o;
    --End of loop
    UNTIL done END REPEAT;
    --CLose the cursor
    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

这条语句定义(DECLARE)了一个CONTINUE HANDLER,它是在条件出现时被执行的代码。

这里,它指出当SQLSTATE ‘02000’出现时,SET done=1。

SQLSTATE ‘02000’是一个未找到条件,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件。

注意:
DECLARE语句的次序,定义局部变量时必须在游标或者句柄前定义,定义句柄必须在游标之后定义

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    --Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR SELECT order_num FROM orders;
    --Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    --Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
    	(order_num INT, total DECIMAL(8,2));
    --Open the cursor
    OPEN ordernumbers;
    --Loop through all rows
    REPEAT
    	--Get order number
        FETCH ordernumbers INTO o;
        --Get the total for this order
        CALL ordertoal(o, 1, t);
        --Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total)
        VALUES(o,t);
    --End of loop
    UNTIL done END REPEAT;
    --Close the cursor
    CLOSE ordernumbers;
END;

在这个例子中,增加了另一个名为t的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(如果它不存在的话),名为ordertotals。这个表将保存存储过程生成的结果。FETCH像以前一样取每个order_num,然后用CALL执行另一个存储过程来计算每个订单的带税的合计(结果存储到t)。最后,用INSERT保存每个订单的订单号和合计。
次存储过程不返回数据

第二十五章 使用触发器

本章介绍什么是触发器,为什么要使用以及如何使用触发器,还有创建和使用触发器的语法

触发器

想要某条语句或者些语句在事件发生时执行就需要使用触发器

需求:往表中新增一条员工数据,都需要检查其电话号格式是否正确,每当订购一个产品时,都从库存数量中减去订购的数量

其实也就是在某个表发生更改时自动处理

触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句

  • DELETE
  • INSERT
  • UPDATE
    其他MySQL语句不支持触发器

创建触发器

创建触发器,需要给出以下4个数据

  • 唯一的触发器名
  • 触发器关联的表
  • 触发器应该响应的活动
  • 触发器合何时执行

使用CREATE TRIGGER语句创建。例:

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

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

注意:

只有表支持触发器,视图不支持

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

删除触发器

DROP TRIGGER newproduct;

触发器不能更新或者覆盖,想要修改,只能删除并重建

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的每次插入使用这个触发器将总是返回新的订单号。

INSERT INTO orders(order_date,cust_id)
VALUES(Now(),10001);

将返回一个order_num,orders表包含三列,date、id必须给出,num自动生成并返回

DELETE触发器

触发器在DELETE语句执行之前或之后执行。需要知道以下两点:

  • 在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行;
  • OLD中的值全都是只读的,不能更新。
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值(要被删除的订单)保存到一个名为archive_orders的存档表中(为实际使用这个例子,你需要用与orders相同的列创建一个名为archive_orders的表)。

说白了就是在删除前备份数据

UPDATE触发器

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

  • 在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新更新的值;
  • 在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);
  • OLD中的值全都是只读的,不能更新。
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(New.vend_state);

该sql保证州名缩写总是大写(不管UPDATE语句中给出的是大写还是小写)

关于触发器的进一步介绍

  • 与其他DBMS相比,MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
  • 创建触发器可能需要特殊的安全访问权限,但是,触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行,则相关的触发器也能执行。
  • 应该用触发器来保证数据的一致性(大小写、格式等)。在触发器中执行这种类型的处理的优点是它总是进行这种处理,而且是透明地进行,与客户机应用无关。
  • 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器,把更改(如果需要,甚至还有之前和之后的状态)记录到另一个表非常容易。
  • 遗憾的是,MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

作者: 华企网通李铁牛程序员

我是程序员李铁牛,热爱互联网软件开发和设计,专注于大数据、数据分析、数据库、php、java、python、scala、k8s、docker等知识总结。15889726201 我的座右铭:"业精于勤荒于嬉,行成于思毁于随"
上一篇
下一篇

发表回复

联系我们

联系我们

028-84868647

在线咨询: QQ交谈

邮箱: tech@68v8.com

工作时间:周一至周五,9:00-17:30,节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部