Mac安装使用Mysql教程(从零开始)
第十二章 游标
12.1 游标简介
游标(cursor)可以在SQL检索结果集中前进或后退一行或多行。游标是存储在DBMS服务器上的数据库查询,它不是一条SELECT语句,而是该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
不同的DBMS支持不同的游标选项和特性。常见的游标选项及特性如下所示:
-
能够标记游标为只读,使数据能读取,但不能更新和删除。
-
能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等)。
-
能标记某些列为可编辑的,某些列为不可编辑的。
-
规定范围,使游标对创建它的特定请求(如存储过程)或对所有请求可访问。
-
指示 DBMS 对检索出的数据(而不是指出表中活动数据)进行复制,使数据在游标打开和访问期间不变化。
注意
Microsoft Access不支持游标,MySQL5以前的MySQL不支持游标,SQLite支持的游标称为步骤(step),它们的基本概念相同,但语法不同。
Web 应用开发不适合使用 游标,要根据自己的需要重新开发相应的功能。
12.2 游标使用
使用游标包含如下几个明确步骤:
- MySQL游标只能用于存储过程(和函数)。
- 在使用游标前,必须声明(定义)它。这个过程实际上没有检索数据, 它只是定义要使用的 SELECT 语句和游标选项。
- 一旦声明,就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
- 对于填有数据的游标,根据需要取出(检索)各行。
- 在结束游标使用时,必须关闭游标,可能的话,释放游标(有赖于具体的 DBMS)。
上述游标使用步骤的介绍可能比较抽象,通过以下三个实例会有更好的理解。
-
实例一
游标最基本用法为根据需要自行获取数据表的某几行,本实例中的游标用于存储无邮件地址的顾客的编号,名字,地址和联系方式。本实例中对游标的使用是获取其前三行的数据并存入变量中(这一操作利用SELECT语句是无法实现的)。
- 创建存储过程
mysql> DELIMITER
mysql> CREATE PROCEDURE build_email_list_1 ()
> BEGIN
- 声明游标中需使用的变量
游标声明必须在变量声明之后,否则会报错。
> 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 CustCursor CURSOR FOR SELECT cust_id, cust_name, cust_address, cust_contact FROM Customers WHERE cust_email IS NULL;
- 打开游标
> OPEN CustCursor;
- 访问游标数据
使用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;
- 关闭游标
> CLOSE CustCursor;
- 结束存储过程
> END//
- 测试结果
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 代码重复了三次,可以使用循环避免重复输入相同的代码。本实例中对游标的使用是获取游标中的所有数据,并将数据存入变量中。
- 创建存储过程
mysql> DELIMITER
mysql> CREATE PROCEDURE build_email_list_2 ()
> BEGIN
- 声明游标中需使用的变量
游标声明必须在变量声明之后,否则会报错。变量 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;
- 创建游标
需说明游标对应的表及过滤条件
> DEClARE CustCursor CURSOR FOR SELECT cust_id, cust_name, cust_address, cust_contact FROM Customers WHERE cust_email IS NULL;
- 声明异常处理handler
当获取不到下一语句时,设置c_finished为1,然后触发退出动作
> DECLARE EXIT HANDLER FOR NOT FOUND SET c_finished = 1;
- 打开游标
> OPEN CustCursor;
- 访问游标数据
利用循环访问游标数据,当找不到数据时退出循环。
>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;
- 关闭游标
> CLOSE CustCursor;
- 结束存储过程
> END//
- 测试结果
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 中
- 创建存储过程
mysql> DELIMITER
mysql> CREATE PROCEDURE build_email_list_3 ()
> BEGIN
- 声明游标中需使用的变量
游标声明必须在变量声明之后,否则会报错。变量 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;
- 创建游标
需说明游标对应的表及过滤条件
> DEClARE CustCursor CURSOR FOR SELECT cust_id, cust_name, cust_address, cust_contact FROM Customers WHERE cust_email IS NULL;
- 声明异常处理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))
- 打开游标
> OPEN CustCursor;
- 访问游标数据
利用循环访问游标数据,当找不到数据时退出循环。
>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;
- 关闭游标
> CLOSE CustCursor;
- 结束存储过程
> END//
- 测试结果
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官方指南