陈建华的个人网站

  • MySQL数据库—游标
  • 一、游标简介
  • 1、游标简介
  • 2、游标的特性
  • 3、游标的优点
  • 4、游标的缺点
  • 5、游标的适用场景
  • 二、游标的操作
  • 1、游标的定义
  • 2、打开游标
  • 3、取游标中的数据
  • 4、关闭游标
  • 5、释放游标
  • 三、游标实例
  • 1、创建一张游标的测试表
  • 2、Loop循环
  • 3、While循环
  • 4、Repeat循环

MySQL数据库—游标

  • Jet Chen
  • 2018-09-10
  • 0

MySQL数据库—游标

一、游标简介

1、游标简介

游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
尽管游标能遍历结果中的所有行，但一次只指向一行。
游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

2、游标的特性

游标具有三个属性：
A、不敏感（Asensitive）：数据库可以选择不复制结果集
B、只读（Read only）
C、不滚动（Nonscrollable）：游标只能向一个方向前进，并且不可以跳过任何一行数据。

3、游标的优点

游标是针对行操作的，对从数据库中SELECT查询得到的结果集的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。游标是面向集合与面向行的设计思想之间的一种桥梁。

4、游标的缺点

游标的主要缺点是性能不高。
游标的开销与游标中进行的操作相关，如果在游标中进行复杂的操作，开销会非常高。如果采用面向集合的SQL语句，扫描成本为O(N)；但如果采用面向集合的SQL语句的扫描成本为O(N*N)，则使用游标有可能会带来性能上的提升。
游标的缺点是只能一行一行操作。在数据量大的情况下，速度过慢。数据库大部分是面对集合的，业务会比较复杂，而游标使用会有死锁，影响其他的业务操作，不可取。 当数据量大时，使用游标会造成内存不足现象。

5、游标的适用场景

MySQL数据库中，可以在存储过程、函数、触发器、事件中使用游标。

二、游标的操作

1、游标的定义

DECLARE cursor_name CURSOR FOR select_statement  

2、打开游标

OPEN cursor_name;

3、取游标中的数据

FETCH cursor_name INTO var_name [, var_name]...

4、关闭游标

CLOSE cursor_name;

5、释放游标

DEALLOCATE cursor_name;

三、游标实例

1、创建一张游标的测试表

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> cursor_table
(
<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> ,
<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">10</span>),
age <span class="hljs-built_in">INT</span>
)<span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">innoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8;
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> cursor_table <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-string">'孙悟空'</span>, <span class="hljs-number">500</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> cursor_table <span class="hljs-keyword">values</span>(<span class="hljs-number">2</span>, <span class="hljs-string">'猪八戒'</span>, <span class="hljs-number">200</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> cursor_table <span class="hljs-keyword">values</span>(<span class="hljs-number">3</span>, <span class="hljs-string">'沙悟净'</span>, <span class="hljs-number">100</span>);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> cursor_table <span class="hljs-keyword">values</span>(<span class="hljs-number">4</span>, <span class="hljs-string">'唐僧'</span>, <span class="hljs-number">20</span>);

使用三种方式使用游标创建一个存储过程，统计年龄大于30的记录的数量。

2、Loop循环

CREATE  PROCEDURE getTotal()
BEGIN  
    <span class="hljs-keyword">DECLARE</span> total INT; 
    <span class="hljs-comment">##创建接收游标数据的变量  </span>
    <span class="hljs-keyword">DECLARE</span> sid INT;  
    <span class="hljs-keyword">DECLARE</span> sname VARCHAR(<span class="hljs-number">10</span>);  
    <span class="hljs-comment">#创建总数变量  </span>
    <span class="hljs-keyword">DECLARE</span> sage INT;  
    <span class="hljs-comment">#创建结束标志变量  </span>
    <span class="hljs-keyword">DECLARE</span> done INT <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">false</span>;  
    <span class="hljs-comment">#创建游标  </span>
    <span class="hljs-keyword">DECLARE</span> cur CURSOR <span class="hljs-keyword">FOR</span> SELECT id,name,age from cursor_table where age><span class="hljs-number">30</span>;  
    <span class="hljs-comment">#指定游标循环结束时的返回值  </span>
    <span class="hljs-keyword">DECLARE</span> <span class="hljs-keyword">CONTINUE</span> HANDLER <span class="hljs-keyword">FOR</span> NOT FOUND SET done = <span class="hljs-keyword">true</span>;  
    <span class="hljs-comment">#设置初始值  </span>
    SET sage = <span class="hljs-number">0</span>;  
    SET total=<span class="hljs-number">0</span>;
    <span class="hljs-comment">#打开游标  </span>
    OPEN cur;  
    <span class="hljs-comment">#开始循环游标里的数据  </span>
    read_loop:loop  
    <span class="hljs-comment">#根据游标当前指向的一条数据  </span>
    FETCH cur INTO sid,sname,sage;  
    <span class="hljs-comment">#判断游标的循环是否结束  </span>
    <span class="hljs-keyword">IF</span> done THEN  
        LEAVE read_loop;    <span class="hljs-comment">#跳出游标循环  </span>
    END <span class="hljs-keyword">IF</span>;  
    <span class="hljs-comment">#获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，  </span>
    SET total = total + <span class="hljs-number">1</span>;  
    <span class="hljs-comment">#结束游标循环  </span>
    END LOOP;  
    <span class="hljs-comment">#关闭游标  </span>
    CLOSE cur;  

    <span class="hljs-comment">#输出结果  </span>
    SELECT total;  
END
<span class="hljs-meta">#调用存储过程  </span>
<span class="hljs-function">call <span class="hljs-title">getTotal</span>()</span>;

3、While循环

CREATE  PROCEDURE getTotal()
BEGIN  
    <span class="hljs-keyword">DECLARE</span> total INT; 
    <span class="hljs-comment">##创建接收游标数据的变量  </span>
    <span class="hljs-keyword">DECLARE</span> sid INT;  
    <span class="hljs-keyword">DECLARE</span> sname VARCHAR(<span class="hljs-number">10</span>);  
    <span class="hljs-comment">#创建总数变量  </span>
    <span class="hljs-keyword">DECLARE</span> sage INT;  
    <span class="hljs-comment">#创建结束标志变量  </span>
    <span class="hljs-keyword">DECLARE</span> done INT <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">false</span>;  
    <span class="hljs-comment">#创建游标  </span>
    <span class="hljs-keyword">DECLARE</span> cur CURSOR <span class="hljs-keyword">FOR</span> SELECT id,name,age from cursor_table where age><span class="hljs-number">30</span>;  
    <span class="hljs-comment">#指定游标循环结束时的返回值  </span>
    <span class="hljs-keyword">DECLARE</span> <span class="hljs-keyword">CONTINUE</span> HANDLER <span class="hljs-keyword">FOR</span> NOT FOUND SET done = <span class="hljs-keyword">true</span>;   
    SET total = <span class="hljs-number">0</span>;  
    OPEN cur;  
    FETCH cur INTO sid, sname, sage;  
    <span class="hljs-keyword">WHILE</span>(NOT done) 
    <span class="hljs-keyword">DO</span>  
        SET total = total + <span class="hljs-number">1</span>;  
        FETCH cur INTO sid, sname, sage;  
    END <span class="hljs-keyword">WHILE</span>;  

    CLOSE cur;  
    SELECT total;  
END

4、Repeat循环

CREATE getTotal()
BEGIN  
    <span class="hljs-keyword">DECLARE</span> total INT; 
    <span class="hljs-comment">##创建接收游标数据的变量  </span>
    <span class="hljs-keyword">DECLARE</span> sid INT;  
    <span class="hljs-keyword">DECLARE</span> sname VARCHAR(<span class="hljs-number">10</span>);  
    <span class="hljs-comment">#创建总数变量  </span>
    <span class="hljs-keyword">DECLARE</span> sage INT;  
    <span class="hljs-comment">#创建结束标志变量  </span>
    <span class="hljs-keyword">DECLARE</span> done INT <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">false</span>;  
    <span class="hljs-comment">#创建游标  </span>
    <span class="hljs-keyword">DECLARE</span> cur CURSOR <span class="hljs-keyword">FOR</span> SELECT id,name,age from cursor_table where age > <span class="hljs-number">30</span>;  
    <span class="hljs-comment">#指定游标循环结束时的返回值  </span>
    <span class="hljs-keyword">DECLARE</span> <span class="hljs-keyword">CONTINUE</span> HANDLER <span class="hljs-keyword">FOR</span> NOT FOUND SET done = <span class="hljs-keyword">true</span>;   
    SET total = <span class="hljs-number">0</span>;  
    OPEN cur;  
    REPEAT  
    FETCH cur INTO sid, sname, sage;   
    <span class="hljs-keyword">IF</span> NOT done THEN  
        SET total = total + <span class="hljs-number">1</span>;  
    END <span class="hljs-keyword">IF</span>;  
    UNTIL done END REPEAT;  
    CLOSE cur;  
    SELECT total;  
END
© 2023 陈建华的个人网站
Theme by Wing
  • {{ item.name }}
  • {{ item.name }}