认识MySQL的游标

一、MySQL游标的概念

1、游标介绍

MySQL的游标(cursor)是一个重要的概念,通过查找资料与自己的理解,主要得出以下几点关于自己的理解。

  • 有数据缓冲的思想:游标的设计是一种数据缓冲区的思想,用来存放SQL语句执行的结果
  • 先有数据基础:游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。
  • 类似于指针:游标类似于指向数据结构堆栈中的指针,用来pop出所指向的数据,并且只能每次取一个。

2、游标优缺点:

(1)游标的优点:

  • 因为游标是针对行操作的,所以对从数据库中select查询得到的每一行可以进行分开的独立的相同或不同的操作,是一种分离的思想。可以满足对某个结果行进行特殊的操作
  • 游标与基于游标位置的增删改查能力。
  • MySQL数据库中没有专门描述一行的表达形式,但这是需要的,所以,个人理解的话,我觉得游标是在关系数据库这种面向集合的系统中抽离出来,单独针对行进行表达(也可以理解成网上资料说的:游标是面向集合与面向行的设计思想之间的一种桥梁

(2)游标缺点

  • 游标的缺点是针对有点而言的,也就是只能一行一行操作,在数据量大的情况下,是不适用的,速度过慢。这里有个比喻就是:当你去ATM存钱是希望一次性存完呢,还是100一张一张的存,这里的100一张一张存就是游标针对行的操作。
  • 数据库大部分是面对集合的,业务会比较复杂,而游标使用会有死锁,影响其他的业务操作,不可取。
  • 当数据量大时,使用游标会造成内存不足现象。

3、游标的使用场景

针对游标的优缺点,我总结游标的使用场景,主要用在循环处理、存储过程、函数中使用,用来查询结果集,就比如:我们需要从表中循环判断并得到想要的结果集,这时候使用游标操作很方便速度也很快。

二、游标的使用

1、游标的使用步骤

游标的使用一般分为5个步骤,主要是:定义游标->打开游标->使用游标->关闭游标->释放游标
(1).定义游标
语法为:

DECLARE <游标名> CURSOR FOR select语句;

具体例子:

-- 这样游标就对select语句声明了一个游标
DECLARE mycursor CURSOR FOR select * from shops_info;

(2).打开游标
打开游标很简单就是下面的语句

open <游标名>

(3).使用游标
使用游标需要用关键字fetch来取出数据,然后取出的数据需要有存放的地方,我们需要用declare声明变量存放列的数据其语法格式为:

declare 变量1 数据类型(与列值的数据类型相同)
declare 变量2 数据类型(与列值的数据类型相同)
declare 变量3 数据类型(与列值的数据类型相同)
FETCH [NEXT | PRIOR | FIRST | LAST] FROM <游标名> [ INTO 变量名1,变量名2,变量名3[,…] ]

NEXT:取下一行的数据,游标一开始默认的第一行之前,故要让游标指向第一行,就必须第一次就执行FETCH NEXT操作
INTO:将一行中每个对应的列下的数据放到与列 的数据类型相同的变量中。

    -- 声明四个变量
    declare id varchar(20); 
    declare pname varchar(20); 
    declare pprice varchar(20);
    declare pdescription varchar(20);

    -- 1、定义一个游标mycursor
    declare mycursor cursor for 
  select *from shops_info;
    -- 2、打开游标
    open mycursor;
    -- 3、使用游标获取列数据放入变量中
    fetch  next from mycursor into id,pname,pprice,pdescription; 

(4).关闭游标:
很简单的使用语句:

close mycursor;

(5).释放游标

deallocate mycursor;

2、具体实例

1、未使用循环的游标实例

从表shops_info中使用游标对每一行进行fetch
这里写图片描述

代码如下:

drop procedure if exists cursor_test;
delimiter //
create procedure cursor_test()
begin
    -- 声明与列的类型相同的四个变量
    declare id varchar(20); 
    declare pname varchar(20); 
    declare pprice varchar(20);
    declare pdescription varchar(20);

-- 1、定义一个游标mycursor
    declare mycursor cursor for 
  select *from shops_info;
-- 2、打开游标
    open mycursor;
-- 3、使用游标获取列的值
    fetch  next from mycursor into id,pname,pprice,pdescription; 
-- 4、显示结果
    select id,pname,pprice,pdescription;
-- 5、关闭游标
    close mycursor;
end;
//
delimiter ;
call cursor_test();

结果如下:
未使用循环得到全部的行记录,值取到第一个fetch next指向的第一行记录。
这里写图片描述

2、使用循环的游标

(1)使用repeat循环读取行数据,操作游标

drop procedure if exists cursor_test;
delimiter //
create procedure cursor_test()
begin
    -- 声明与列的类型相同的四个变量
    declare id varchar(20); 
    declare pname varchar(20); 
    declare pprice varchar(20);
    declare pdescription varchar(20);
    declare temp varchar(20);
    declare i int default 0; -- 声明计数i初始值为0;

-- 1、定义一个游标mycursor
    declare mycursor cursor for 
  select *from shops_info;
    -- 获得总的记录数
    set @cur_count=(select count(*) from shops_info);

-- 2、打开游标
    open mycursor;
-- 3、使用游标获取列的值:取出第一行记录
-- 循环读数据
    repeat 
        fetch next from mycursor into id,pname,pprice,pdescription;
-- 4、显示结果
        select id,pname,pprice,pdescription;
        set i=i+1;
  until i=@cur_count
    end repeat;

-- 5、关闭游标
    close mycursor;
end;
//
delimiter ;
call cursor_test();

如下所示得到表shops_info的三行记录

这里写图片描述这里写图片描述这里写图片描述

(2)使用while操作游标循环读取行记录
要配合使用continue handler来识别游标一个越界标识,该语法为:

-- 当发生NOT FOUND事件时就会执行statement语句
declare continue handler for NOT FOUND statement
-- 或者SQLSTATE='0200'
 declare CONTINUE HANDLER FOR SQLSTATE '02000' statement

具体实例代码为:

drop procedure if exists cursor_test;
delimiter //
create procedure cursor_test()
begin
    -- 声明与列的类型相同的四个变量
    declare id varchar(20); 
    declare pname varchar(20); 
    declare pprice varchar(20);
    declare pdescription varchar(20);
    declare done varchar(20) default '';

-- 1、定义一个游标mycursor
    declare mycursor cursor for 
  select *from shops_info;
    -- 声明一个越界标识done:continuenot found之后,还会继续执行一次select语句
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = null;
-- 2、打开游标
    open mycursor;

-- 3、使用游标获取列的值:取出第一行记录
    fetch  next from  mycursor into id,pname,pprice,pdescription;

-- -- 循环读数据
    while ( done is not null) do 
        select id,pname,pprice,pdescription;
        fetch  next from mycursor into id,pname,pprice,pdescription;
-- 4、显示结果   
    end while;

-- 5、关闭游标
    close mycursor;
end;
//
delimiter ;
call cursor_test();


结果如下:
这里写图片描述

发布了63 篇原创文章 · 获赞 29 · 访问量 8万+
展开阅读全文

关于mysql5.6存储与游标的问题

11-07

如下是我的过程,不解的是当我把从游标里的任何一列当做判断结束游标条件时,无论何时它都为空,但它确确实实可以循环完,百思不得其解。 例如 WHILE ( TYPE IS NOT NULL ) 。 这里的type永远都为空。 再如果我将任意一列 , event_value 当做while 条件 event_value 同样为空。 在此跪求大鸟解答了。 DELIMITER $$ CREATE PROCEDURE test( IN event_value_name VARCHAR(20) , IN datestr VARCHAR(20) , IN start_date DATETIME , IN end_date DATETIME ) BEGIN DECLARE temp VARCHAR(10) DEFAULT ""; DECLARE temps VARCHAR(200) DEFAULT ""; DECLARE report_tim VARCHAR(100) DEFAULT ""; DECLARE TYPE VARCHAR(50); DECLARE event_value VARCHAR(50); DECLARE source VARCHAR(50); DECLARE event_name VARCHAR(50); DECLARE COT VARCHAR(50); DECLARE allrow CURSOR FOR SELECT tev.event_value,tev.source,tev.event_name,COUNT(*) AS COT,tev.type,DATE_FORMAT(tev.report_time,'%Y-%m-%d') report_tim FROM ts_event_value tev WHERE tev.name=event_value_name AND tev.report_time BETWEEN start_date AND end_date GROUP BY tev.event_value,tev.source,tev.event_name,tev.type,report_tim ORDER BY COT DESC LIMIT 15 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET TYPE = NULL; OPEN allrow; /*allrowloop:LOOP*/ FETCH allrow INTO event_value,source,event_name,COT,TYPE,report_tim; IF datestr = "all" THEN WHILE ( TYPE IS NOT NULL ) DO SELECT s.name INTO temp FROM statistical s WHERE s.name = event_value_name AND s.source = source AND s.event_name = event_name AND s.event_value = event_value AND s.zt = "all"; IF temp = "" OR temp IS NULL THEN INSERT INTO statistical VALUES (source,event_name,event_value,COT,event_value_name,report_tim,"all"); ELSE UPDATE statistical s SET s.cot = s.cot + COT WHERE s.name = event_value_name AND s.source = source AND s.event_name = event_name AND s.event_value = event_value AND s.zt = "all"; END IF; FETCH allrow INTO event_value,source,event_name,COT,TYPE,report_tim; END WHILE; END IF; COMMIT; SELECT event_value,source,event_name,COT,temp; /*SELECT temps;*/ /*END LOOP allrowloop;*/ CLOSE allrow; END; $$ DELIMITER ; 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览