亲宝软件园·资讯

展开

MySQL游标的介绍与使用

知其黑、受其白 人气:0

定义

游标(Cursor)是处理数据的一种方法,为了查看或者处理结果集中的数据,游标提供了在结果集中一次一行遍历数据的能力。

游标也是一种面向过程的 sql 编程方法,所以一般在存储过程、函数、触发器、循环处理中使用。

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

游标的作用

游标相当于一个指针,这个指针指向 select 的第一行数据,可以通过移动指针来遍历后面的数据。

游标是对查询出来的结果集作为一个单元来有效的处理。

游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。

可以对结果集当前行做修改。

一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。

游标的使用

在mysql中,游标可以在存储过程、函数、触发器和事件中使用。

游标需要与相关 handler 一起使用,并在 handler 之前定义。

游标有以下三个属性:

声明游标:

创建一个游标,并指定这个游标需要遍历的select查询,声明游标时并不会去执行这个sql。

打开游标:

打开游标的时候,会执行游标对应的select语句。

遍历数据:

使用游标循环遍历select结果中每一行数据,然后进行处理。

业务操作:

对遍历到的每行数据进行操作的过程,可以放置任何需要执行的执行的语句(增删改查):这里视具体情况而定。

关闭游标:

游标使用完之后一定要释放(游标占用的内存还是有点大的)。

注:使用的临时字段需要在定义游标之前进行声明。

游标语法

游标的使用过程:声明游标、打开游标、遍历游标、关闭游标

声明游标:DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标:open 游标名称;

遍历游标:fetch 游标名称 into 变量列表;

取出当前行的结果,将结果放在对应的变量中,并将游标指针指向下一行的数据。

当调用 fetch 的时候,会获取当前行的数据,如果当前行无数据,会引发 mysql 内部的 NOT FOUND 错误。

关闭游标:close 游标名称; 游标使用完毕之后一定要关闭。

条件处理

DECLARE CONTINE HANDLER 表达式 1 SET 表达式 2:

这段代码的作用是定义一个 CONTINE HANDLER,这个的作用是当表达式 1 的条件出现时,将执行表达式 2 的语句。

用这个语句可以实现条件的变更实质是利用 mysql 的异常处理,也常常在游标上使用,来辅助判断游标数据是否遍历完了。

例如 DECLARE CONTINUE HANDLER FOR NOT FOUND … 的语句,这是为了对游标没有下一条记录可供访问的情况做出异常处理。

创建表-test1-test2-test3

DROP TABLE IF EXISTS test1; 
CREATE TABLE test1(a int,b int); 
INSERT INTO test1 VALUES (1,2),(3,4),(5,6); 
DROP TABLE IF EXISTS test2; 
CREATE TABLE test2(a int); 
INSERT INTO test2 VALUES (100),(200),(300); 
DROP TABLE IF EXISTS test3; 
CREATE TABLE test3(b int); 
INSERT INTO test3 VALUES (400),(500),(600);

写一个函数,计算 test1 表中 a、b 字段所有的和

/*删除函数*/ 
DROP FUNCTION IF EXISTS fun1; 
/*声明结束符为$*/ 
DELIMITER $ 
/*创建函数*/ 
CREATE FUNCTION fun1(v_max_a int) 
  RETURNS int 
  BEGIN 
    /*用于保存结果*/ 
    DECLARE v_total int DEFAULT 0; 
    /*创建一个变量,用来保存当前行中a的值*/
    DECLARE v_a int DEFAULT 0; 
    /*创建一个变量,用来保存当前行中b的值*/ 
    DECLARE v_b int DEFAULT 0; 
    /*创建游标结束标志变量*/ 
    DECLARE v_done int DEFAULT FALSE; 
    /*创建游标*/ 
    DECLARE cur_test1 CURSOR FOR SELECT a,b from test1 where a<=v_max_a; 
    /*设置游标结束时v_done的值为true,可以v_done来判断游标是否结束了*/ 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; 
    /*设置v_total初始值*/ 
    SET v_total = 0; 
    /*打开游标*/ 
    OPEN cur_test1; 
    /*使用Loop循环遍历游标*/ 
    a:LOOP 
      /*先获取当前行的数据,然后将当前行的数据放入v_a,v_b中,如果当前行无数据,v_done会被置 为true*/
      FETCH cur_test1 INTO v_a, v_b; 
      /*通过v_done来判断游标是否结束了,退出循环*/ 
      if v_done THEN 
      LEAVE a; 
      END IF; 
      /*对v_total值累加处理*/ 
      SET v_total = v_total + v_a + v_b;
    END LOOP; 
    /*关闭游标*/ 
    CLOSE cur_test1; 
    /*返回结果*/ 
    RETURN v_total; 
  END $ 
/*结束符置为;*/ 
DELIMITER ;

其中 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;  是异常处理的语法,意思是当遇到 NOT FOUND 错误时,将 v_done 设为 ture,continue 继续执行当前任务。

测试

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)
mysql> select fun1(1);
+---------+
| fun1(1) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)
mysql> select fun1(4);
+---------+
| fun1(4) |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)
mysql> select fun1(5);
+---------+
| fun1(5) |
+---------+
|      21 |
+---------+
1 row in set (0.00 sec)

游标过程解析

以上面的示例代码为例,看一下游标的详细执行过程。

游标中有个指针,当打开游标的时候,才会执行游标对应的 select 语句,这个指针会指向select 结果中第一行记录。

当调用 fetch 游标名称时,会获取当前行的数据,如果当前行无数据,会触发 NOT FOUND 异常,当触发 NOT FOUND 异常的时候,我们可以使用一个变量来标记一下,如下代码:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;

当游标无数据触发 NOT FOUND 异常的时候,将变量 v_down 的值置为 TURE ,循环中就可以通过 v_down 的值控制循环的退出。

如果当前行有数据,则将当前行数据存到对应的变量中,并将游标指针指向下一行数据,如下语句:

fetch 游标名称 into 变量列表;

加载全部内容

相关教程
猜你喜欢
用户评论