5.7 游标

一、游标简介

1、游标简介

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

游标分为动态游标(强类型和弱类型)和静态游标(显性和隐性)

静态游标：以游标打开时刻的当时状态显示结果集的游标。静态游标在游标打开时不反映对基础数据进行的更新、删除或插入。有时称它们为快照游标。

动态游标：可以在游标打开时反映对基础数据进行的修改的游标。用户所做的更新、删除和插入在动态游标中加以反映。

2、游标的特性

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

3、游标的优点

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

4、游标的缺点

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

5、游标的适用场景

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

二、游标的操作

1、游标的定义

  1. DECLARE cursor_name CURSOR FOR select_statement

2、打开游标

  1. OPEN cursor_name;

3、取游标中的数据

  1. FETCH cursor_name INTO var_name [, var_name]...

4、关闭游标

  1. CLOSE cursor_name;

5、释放游标

  1. DEALLOCATE cursor_name;

三、游标实例

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

  1. CREATE TABLE cursor_table
  2. (
  3. id INT ,
  4. name VARCHAR(10),
  5. age INT
  6. )ENGINE=innoDB DEFAULT CHARSET=utf8;
  7. insert into cursor_table values(1, '孙悟空', 500);
  8. insert into cursor_table values(2, '猪八戒', 200);
  9. insert into cursor_table values(3, '沙悟净', 100);
  10. insert into cursor_table values(4, '唐僧', 20);

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

2、Loop循环

  1. CREATE  PROCEDURE getTotal()
  2. BEGIN  
  3.     DECLARE total INT; 
  4.     ##创建接收游标数据的变量  
  5.     DECLARE sid INT;  
  6.     DECLARE sname VARCHAR(10);  
  7.     #创建总数变量  
  8.     DECLARE sage INT;  
  9.     #创建结束标志变量  
  10.     DECLARE done INT DEFAULT false;  
  11.     #创建游标  
  12.     DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;  
  13.     #指定游标循环结束时的返回值  
  14.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  
  15.     #设置初始值  
  16.     SET sage = 0;  
  17.     SET total=0;
  18.     #打开游标  
  19.     OPEN cur;  
  20.     #开始循环游标里的数据  
  21.     read_loop:loop  
  22.     #根据游标当前指向的一条数据  
  23.     FETCH cur INTO sid,sname,sage;  
  24.     #判断游标的循环是否结束  
  25.     IF done THEN  
  26.         LEAVE read_loop;    #跳出游标循环  
  27.     END IF;  
  28.     #获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，  
  29.     SET total = total + 1;  
  30.     #结束游标循环  
  31.     END LOOP;  
  32.     #关闭游标  
  33.     CLOSE cur;  
  35.     #输出结果  
  36.     SELECT total;  
  37. END
  38. #调用存储过程  
  39. call getTotal();

3、While循环

  1. CREATE  PROCEDURE getTotal()
  2. BEGIN  
  3.     DECLARE total INT; 
  4.     ##创建接收游标数据的变量  
  5.     DECLARE sid INT;  
  6.     DECLARE sname VARCHAR(10);  
  7.     #创建总数变量  
  8.     DECLARE sage INT;  
  9.     #创建结束标志变量  
  10.     DECLARE done INT DEFAULT false;  
  11.     #创建游标  
  12.     DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;  
  13.     #指定游标循环结束时的返回值  
  14.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;   
  15.     SET total = 0;  
  16.     OPEN cur;  
  17.     FETCH cur INTO sid, sname, sage;  
  18.     WHILE(NOT done) 
  19.     DO  
  20.         SET total = total + 1;  
  21.         FETCH cur INTO sid, sname, sage;  
  22.     END WHILE;  
  24.     CLOSE cur;  
  25.     SELECT total;  
  26. END

4、Repeat循环

  1. CREATE getTotal()
  2. BEGIN  
  3.     DECLARE total INT; 
  4.     ##创建接收游标数据的变量  
  5.     DECLARE sid INT;  
  6.     DECLARE sname VARCHAR(10);  
  7.     #创建总数变量  
  8.     DECLARE sage INT;  
  9.     #创建结束标志变量  
  10.     DECLARE done INT DEFAULT false;  
  11.     #创建游标  
  12.     DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age > 30;  
  13.     #指定游标循环结束时的返回值  
  14.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;   
  15.     SET total = 0;  
  16.     OPEN cur;  
  17.     REPEAT  
  18.     FETCH cur INTO sid, sname, sage;   
  19.     IF NOT done THEN  
  20.         SET total = total + 1;  
  21.     END IF;  
  22.     UNTIL done END REPEAT;  
  23.     CLOSE cur;  
  24.     SELECT total;  
  25. END
  1. BEGIN
  3.   DECLARE v_t_rt VARCHAR(50);
  4.   DECLARE v_sitec INT DEFAULT 0;
  6.   BEGIN
  7.         DECLARE no_more_devices INTEGER DEFAULT 0;
  8.         DECLARE cur_device CURSOR FOR
  9.         SELECT CONCAT('T_BAS_DATART',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL rownum DAY),'%Y%m%d'))
  10.           FROM
  11.           (
  12.           SELECT  @rownum:=@rownum+1 rownum
  13.           FROM (SELECT @rownum:=-1 ,table_name FROM information_schema.tables LIMIT 7) t
  14.           )tmp;
  15.         DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_devices=1;
  17.         OPEN cur_device;
  18.         REPEAT
  19.           FETCH cur_device INTO v_t_rt;
  21.             SET @STMT:=CONCAT("CREATE TABLE IF NOT EXISTS ",v_t_rt,
  22.                                                             "(`TSTAMP` int(11) NOT NULL COMMENT '上号时间',
  23.                                                             `DID` INT(11) NOT NULL,
  24.                                                             `DEVICEID` varchar(20) NOT NULL,
  25.                                                             `IMSI` bigint(15) NOT NULL,
  26.                                                             `IMEI` bigint(15) DEFAULT NULL COMMENT 'IMEI',
  27.                                                             `RAT` TINYINT(1) DEFAULT NULL COMMENT '制式',
  28.                                                             `HOMEAREA` VARCHAR(20) DEFAULT NULL COMMENT '号码归属地',
  29.                                                             `USERNUM` int(7) DEFAULT NULL COMMENT '用户号码',
  30.                                                             `SAVETIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '保存时间',
  31.                                                             PRIMARY KEY (`DID`,`TSTAMP`,`IMSI`),
  32.                                                             KEY `IMSI_TSTAMP_INDEX` (`IMSI`,`TSTAMP`) USING BTREE
  33.                                                             )ENGINE=INNODB DEFAULT CHARSET=utf8
  34.                                                             PARTITION BY HASH(DID)
  35.                                                             PARTITIONS 100;");
  37.              PREPARE STMT FROM @STMT;
  38.              EXECUTE STMT;
  40.         UNTIL no_more_devices  END REPEAT;
  41.         CLOSE cur_device;
  42.   END;
  43. END

四、存储过程游标解析

  1. 游标是SQL的一个内存工作区，由系统或用户以变量的形式定义，从表中检索出结果集，从中每次指向一条记录进行交互的机制。 由于游标指示结果集中的当前位置 ，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。

  2. 游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。有以下几点： （1）指定结果集中特定行的位置。 （2）基于当前的结果集位置检索一行或连续的几行。 （3）在结果集的当前位置修改行中的数据。 （4）对其他用户所做的数据更改定义不同的敏感性级别。 （5）可以以编程的方式访问数据库。

  3. Oracle游标的类型 （1）静态游标：

    结果集已经确定（静态定义）的游标。分为隐式和显式游标。 A、隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息。当直接在代码中执行一条SQL语句时，只要该代码没有显式声明一个游标，PL/SQL就会产生一个隐式游标。如：插入操作：INSERT、更新操作：UPDATE、删除操作：DELETE、单行查询操作：SELECT…INTO… 。当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。 B、显式游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。 （2）REF游标（动态游标）：

    动态关联结果集的临时对象。即在运行的时候动态决定执行查询。实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。 在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。

  4. 静态游标和REF 游标的区别 （1）静态游标是静态定义，REF 游标是动态关联； （2）使用REF 游标需REF 游标变量。 （3）REF 游标能做为参数进行传递，而静态游标是不可能的。

  5. REF 游标变量 REF游标变量是一种 引用 REF游标类型 的变量，指向动态关联的结果集。 特点： （1）可与不同查询关联，可从不同结果集中取数； （2）可作为过程或函数的参数传递。实现对游标的共享； （3）可以使用静态游标所具有的全部功能； （4）可以将一个游标变量内容（包括它的结果集）赋给另一个游标变量。

  6. Oracle游标的属性 游标的状态是通过属性来表示。 %Found ：Fetch语句（获取记录）执行情况 True or False。 %NotFound : 最后一条记录是否提取出 True or False。 %ISOpen ： 游标是否打开True or False。 %RowCount ：游标当前提取的行数 。

  7. 使用显示游标与遍历循环游标 A、使用显示游标 （1）声明游标：划分存储区域，注意此时并没有执行Select 语句。 （DELARE） CURSOR 游标名(参数列表) [返回值类型] IS Select 语句；

    参数只定义数据类型，没有大小(所有Oracle中的形参只定义数据类型，不指定大小)。游标只能接受传递的值，而不能返回值。 参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。如：

    （DELARE） CURSOR 游标名(参数名 数据类型 DEFAULT 默认值，。。。) [返回值类型] IS Select 语句。

    SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

    （2）打开游标：执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头， 而不是第一条记录。

    1. Open 游标名( 参数 列表)；

    （3）获取记录：移动游标取一条记录

    1. Fetch  游标名 InTo  临时记录或属性类型变量；

    （4）关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。

    1. Close  游标名；

    ​ 显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

    B、遍历循环游标 （1）For 循环游标 循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。

    1.  For  变量名  In  游标名  
    2.  Loop
    3.   数据处理语句；
    4.  End Loop；

    （2）Loop循环游标

    1. Loop 
    2.  Fatch  游标名 InTo  临时记录或属性类型变量；
    3.  Exit  When  游标名%NotFound；

    数据处理语句；

    1. End   Loop；

    （3）Loop循环游标

    1. FETCH 游标名 INTO 临时记录或属性类型变量;
    2. WHILE 游标名%FOUND LOOP
    3.   数据处理语句；
    4.   FETCH 游标名 临时记录或属性类型变量;
    5. END LOOP;

  8. 更新和删除显示游标中的记录 A、UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT…FOR UPDATE操作。 在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。 B、使用更新或删除： （1）声明更新或删除显示游标： Cursor 游标名 IS SELECT 语句 For Update [ Of 更新列列名]； Cursor 游标名 IS SELECT 语句 For Delete [ Of 更新列列名]； （2）使用显示游标来更新或删除当前记录： Update 表名 SET 更新语句 Where Current Of 游标名； Delete From 表名 Where Current Of 游标名；

  9. 使用 REF游标

    A、声明REF 游标类型，确定REF 游标类型 （1）强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。 语法：Type REF游标名 IS Ref Cursor Return 结果集返回记录类型； （2）弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。 语法：Type REF游标名 IS Ref Cursor；

    B、声明Ref 游标类型变量 语法：变量名 已声明Ref 游标类型；

    C、打开REF游标，关联结果集 语法：Open Ref 游标类型变量 For 查询语句返回结果集；

    D、获取记录，操作记录 语法：Fatch REF游标名 InTo 临时记录类型变量或属性类型变量列表；

    E、关闭游标，完全释放资源； 语法：Close REF游标名；