Mac安装使用Mysql教程(从零开始)
- 第一章 Mac安装MySQL
- 第二章 安装数据库管理软件DBeaver
- 第三章 DBeaver创建MySQL数据库
- 第四章 终端管理MySQL
- 第五章 MySQL基本操作之查询
- 第六章 MySQL基本操作之插入
- 第七章 MySQL基本操作之更新与删除
- 第八章 创建和操纵表
- 第九章 使用视图
- 第十章 使用存储过程
- 第十一章 管理事务处理
- 第十二章 游标
- 第十三章 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官方指南