Cursor

Mac安装使用Mysql教程(从零开始)

第十二章 游标

12.1 游标简介

       游标(cursor)可以在SQL检索结果集中前进或后退一行或多行。游标是存储在DBMS服务器上的数据库查询,它不是一条SELECT语句,而是该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
       不同的DBMS支持不同的游标选项和特性。常见的游标选项及特性如下所示:

  • 能够标记游标为只读,使数据能读取,但不能更新和删除。

  • 能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等)。

  • 能标记某些列为可编辑的,某些列为不可编辑的。

  • 规定范围,使游标对创建它的特定请求(如存储过程)或对所有请求可访问。

  • 指示 DBMS 对检索出的数据(而不是指出表中活动数据)进行复制,使数据在游标打开和访问期间不变化。

    注意
    Microsoft Access不支持游标,MySQL5以前的MySQL不支持游标,SQLite支持的游标称为步骤(step),它们的基本概念相同,但语法不同。
    Web 应用开发不适合使用 游标,要根据自己的需要重新开发相应的功能。

12.2 游标使用

       使用游标包含如下几个明确步骤:

  • MySQL游标只能用于存储过程(和函数)。
  • 在使用游标前,必须声明(定义)它。这个过程实际上没有检索数据, 它只是定义要使用的 SELECT 语句和游标选项。
  • 一旦声明,就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
  • 对于填有数据的游标,根据需要取出(检索)各行。
  • 在结束游标使用时,必须关闭游标,可能的话,释放游标(有赖于具体的 DBMS)。

       上述游标使用步骤的介绍可能比较抽象,通过以下三个实例会有更好的理解。

  • 实例一
           游标最基本用法为根据需要自行获取数据表的某几行,本实例中的游标用于存储无邮件地址的顾客的编号,名字,地址和联系方式。本实例中对游标的使用是获取其前三行的数据并存入变量中(这一操作利用SELECT语句是无法实现的)。
    1. 创建存储过程
      mysql> DELIMITER
      mysql> CREATE PROCEDURE build_email_list_1 ()
                > BEGIN
    2. 声明游标中需使用的变量
      游标声明必须在变量声明之后,否则会报错。
                > DECLARE c_id char(10) DEFAULT “”;            – cust_id
                > DECLARE c_name char(50) DEFAULT “”;      – cust_name
                > DECLARE c_address char(50) DEFAULT “”;   – cust_address
                > DECLARE c_contact char(50) DEFAULT “”;    – cust_contact
    3. 创建游标
      需说明游标对应的表及过滤条件
                > DEClARE CustCursor CURSOR FOR SELECT cust_id, cust_name, cust_address, cust_contact FROM Customers WHERE cust_email IS NULL;
    4. 打开游标
                > OPEN CustCursor;
    5. 访问游标数据
      使用FETCH访问游标数据,一次只能访问一行,要获取前三行数据需执行三次 FETCH
                > FETCH CustCursor INTO c_id, c_name, c_address, c_contact;
                > SELECT c_id, c_name, c_address, c_contact;
                > FETCH CustCursor INTO c_id, c_name, c_address, c_contact;
                > SELECT c_id, c_name, c_address, c_contact;
                > FETCH CustCursor INTO c_id, c_name, c_address, c_contact;
                > SELECT c_id, c_name, c_address, c_contact;
    6. 关闭游标
                > CLOSE CustCursor;
    7. 结束存储过程
                > END//
    8. 测试结果
      mysql> DELIMITER ;
      mysql> CALL build_email_list_1();
      ±-----------±-----------±---------------------±---------------+
      | c_id | c_name | c_address | c_contact |
      ±-----------±-----------±---------------------±---------------+
      | 1000000002 | Kids Place | 333 South Lake Drive | Michelle Green |
      ±-----------±-----------±---------------------±---------------+
      1 row in set (0.00 sec)
      ±-----------±---------±-------------±----------+
      | c_id | c_name | c_address | c_contact |
      ±-----------±---------±-------------±----------+
      | 1000000007 | LOL Land | 123 Toc Road | NULL |
      ±-----------±---------±-------------±----------+
      1 row in set (0.00 sec)
      ±-----------±--------±---------------±----------+
      | c_id | c_name | c_address | c_contact |
      ±-----------±--------±---------------±----------+
      | 1000000008 | CF Land | 321 Tic Avenue | NULL |
      ±-----------±--------±---------------±----------+
      1 row in set (0.00 sec)
      Query OK, 0 rows affected (0.00 sec)
  • 实例二
           实例一中的 F~ETCH 代码重复了三次,可以使用循环避免重复输入相同的代码。本实例中对游标的使用是获取游标中的所有数据,并将数据存入变量中。
    1. 创建存储过程
      mysql> DELIMITER
      mysql> CREATE PROCEDURE build_email_list_2 ()
                > BEGIN
    2. 声明游标中需使用的变量
      游标声明必须在变量声明之后,否则会报错。变量 c_finished 表示结束循环的变量。
                > DECLARE c_id char(10) DEFAULT “”;            – cust_id
                > DECLARE c_name char(50) DEFAULT “”;      – cust_name
                > DECLARE c_address char(50) DEFAULT “”;   – cust_address
                > DECLARE c_contact char(50) DEFAULT “”;    – cust_contact
                >DECLARE c_finished INTEGER DEFAULT 0;
    3. 创建游标
      需说明游标对应的表及过滤条件
                > DEClARE CustCursor CURSOR FOR SELECT cust_id, cust_name, cust_address, cust_contact FROM Customers WHERE cust_email IS NULL;
    4. 声明异常处理handler
      当获取不到下一语句时,设置c_finished为1,然后触发退出动作
               > DECLARE EXIT HANDLER FOR NOT FOUND SET c_finished = 1;
    5. 打开游标
                > OPEN CustCursor;
    6. 访问游标数据
      利用循环访问游标数据,当找不到数据时退出循环。
                >get_email: LOOP
                > FETCH CustCursor INTO c_id, c_name, c_address, c_contact;
                > SELECT c_id, c_name, c_address, c_contact;
                > IF c_finished = 1 THEN LEAVE get_email;
                > END IF;
                > END LOOP get_email;
    7. 关闭游标
                > CLOSE CustCursor;
    8. 结束存储过程
                > END//
    9. 测试结果
      mysql> DELIMITER ;
      mysql> CALL build_email_list_2();
      ±-----------±-----------±---------------------±---------------+
      | c_id | c_name | c_address| c_contact |
      ±-----------±-----------±---------------------±---------------+
      | 1000000002 | Kids Place | 333 South Lake Drive | Michelle Green |
      ±-----------±-----------±---------------------±---------------+
      1 row in set (0.00 sec)
      ±-----------±---------±-------------±----------+
      | c_id | c_name | c_address | c_contact |
      ±-----------±---------±-------------±----------+
      | 1000000007 | LOL Land | 123 Toc Road | NULL |
      ±-----------±---------±-------------±----------+
      1 row in set (0.00 sec)
      ±-----------±--------±---------------±----------+
      | c_id | c_name | c_address | c_contact |
      ±-----------±--------±---------------±----------+
      | 1000000008 | CF Land | 321 Tic Avenue | NULL |
      ±-----------±--------±---------------±----------+
      1 row in set (0.00 sec)
      ±-----------±-------±--------------±----------+
      | c_id | c_name | c_address | c_contact |
      ±-----------±-------±--------------±----------+
      | 1000000009 | PAPP | 200 Fake Lane | NULL |
      ±-----------±-------±--------------±----------+
      1 row in set (0.00 sec)
      ±-----------±-------±---------------±----------+
      | c_id | c_name | c_address | c_contact |
      ±-----------±-------±---------------±----------+
      | 1000000010 | PAPAPA | 250 Faker Road | NULL |
      ±-----------±-------±---------------±----------+
      1 row in set (0.00 sec)
      Query OK, 0 rows affected (0.00 sec)
  • 实例三
           实例二中的数据分五次进行显示,可以将数据插入表中并一次性显示出来。本实例中对游标的使用是获取游标中的所有数据,并将数据存入表格 Null_email 中
    1. 创建存储过程
      mysql> DELIMITER
      mysql> CREATE PROCEDURE build_email_list_3 ()
                > BEGIN
    2. 声明游标中需使用的变量
      游标声明必须在变量声明之后,否则会报错。变量 c_finished 表示结束循环的变量。
                > DECLARE c_id_v char(10) DEFAULT “”;            – cust_id
                > DECLARE c_name_v char(50) DEFAULT “”;      – cust_name
                > DECLARE c_address_v char(50) DEFAULT “”;   – cust_address
                > DECLARE c_contact_v char(50) DEFAULT “”;    – cust_contact
                >DECLARE c_finished INTEGER DEFAULT 0;
    3. 创建游标
      需说明游标对应的表及过滤条件
                > DEClARE CustCursor CURSOR FOR SELECT cust_id, cust_name, cust_address, cust_contact FROM Customers WHERE cust_email IS NULL;
    4. 声明异常处理handler
      当获取不到下一语句时,设置c_finished为1,然后触发退出动作。创建一个用于存储数据的表
                > DECLARE EXIT HANDLER FOR NOT FOUND SET c_finished = 1;
                > CREATE TABLE IF NOT EXISTS Null_email(c_id char(10), c_name char(50), c_address char(50), c_contact char(50))
    5. 打开游标
                > OPEN CustCursor;
    6. 访问游标数据
      利用循环访问游标数据,当找不到数据时退出循环。
                >get_email: LOOP
                > FETCH CustCursor INTO c_id_v, c_name_v, c_address_v, c_contact_v;
                > INSERT INTO Null_email(c_id, c_name, c_address, c_contact) VALUES (c_id_v, c_name_v, c_address_v, c_contact_v);
                > IF c_finished = 1 THEN LEAVE get_email;
                > END IF;
                > END LOOP get_email;
    7. 关闭游标
                > CLOSE CustCursor;
    8. 结束存储过程
                > END//
    9. 测试结果
      mysql> DELIMITER ;
      mysql> CALL build_email_list_2();
      mysql> select * from Null_email;
      ±-----------±-----------±---------------------±---------------+
      | c_id | c_name | c_address | c_contact |
      ±-----------±-----------±---------------------±---------------+
      | 1000000002 | Kids Place | 333 South Lake Drive | Michelle Green |
      | 1000000007 | LOL Land | 123 Toc Road | NULL |
      | 1000000008 | CF Land | 321 Tic Avenue | NULL |
      | 1000000009 | PAPP | 200 Fake Lane | NULL |
      | 1000000010 | PAPAPA | 250 Faker Road | NULL |
      ±-----------±-----------±---------------------±---------------+
      5 rows in set (0.00 sec)

    注意
            由MySQL官方手册可知,声明游标需在声明异常处理handler之前,声明变量和条件之后。而经过个人实践得知,声明游标需在创建表格之前。

12.3 参考

[1] Ben Forta.SQL必知必会-中文-第4版
[2] Cursors官方指南

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值