游标
MySQL 检索操作返回一组称为结果集的行,这组返回的行都是与 SQL 语句相匹配的行(零行或者多行)
结果集(result set):
SQL 查询所检索出的结果
使用简单的 SELECT
语句,没有办法得到第一行、下一行或者前 10 行,也不存在每次一行地处理所有行的简单方法(相对于成批地处理它们)
有时,需要在检索出来的行中前进或者后退一行或多行,这就是 游标的作用 所在
游标(cursor):
是一个存储在 DBMS 服务器上的数据库查询,它不是一条SELECT
语句,而是被该语句检索出来的结果集
在存储了游标后,应用程序可以根据需要滚动或浏览其中的数据,主要用于交互式应用
在 MySQL 中,游标只能用于存储过程(和函数)
不同的 DBMS 支持不同的游标选项和特性,一些常见的选项和特性如下:
1、能够标识游标为只读,使数据能够被读取,但是不能更新和删除
2、能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等)
3、能标识某些列为可编辑的,某些列为不可编辑的
4、规定范围,使游标对创建它的特定请求(如存储过程)或对所有请求可访问
5、指示 DBMS 对检索出的数据(而不是指出表中活动数据)进行复制,使数据在游标打开和访问期间不变化
使用游标
使用游标涉及以下几个明确的步骤:
-
在使用游标前,必须声明(定义)它;这个过程实际上没有检索数据,它只是定义要使用的
SELECT
语句和游标选项 -
一旦声明,就必须打开游标以供使用,这个过程用前面定义的
SELECT
语句把数据实际检索出来 -
对于填有数据的游标,根据需要取出(检索)各行
-
在结束游标使用时,必须关闭游标,可能的话,释放游标
声明游标后,可以根据需要频繁地打开和关闭游标;在游标打开时,可以根据需要频繁地执行取操作
创建游标
使用 DECLARE
语句创建游标
DECLARE
命名游标,并定义相应的 SELECT
语句,根据需要带 WHERE
和其他子句
下面的例子,创建一个名为 ordernumbers 的游标,使用了可以检索所有订单的 SELECT
语句
mysql> CREATE PROCEDURE processorders()
-> BEGIN
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
-> END; //
这个存储过程并没有做很多事情,DECLARE
语句用来定义和命名游标;存储过程处理完成后,游标就消失(因为它局限于存储过程)
打开和关闭游标
定义游标后,就可以打开它了
使用 OPEN
语句打开游标
OPEN ordernumbers;
在处理 OPEN
语句时,执行查询,存储检索出的数据以供浏览和滚动
游标在使用完毕时,需要关闭,使用 CLOSE
CLOSE ordernumbers;
CLOSE
释放游标使用的所有内部内存和资源
一旦关闭游标,如果不再打开,将不能使用;
第二次使用时,不需要声明,只需用 OPEN
打开它即可
隐含关闭
如果不明确关闭游标,MySQL 会在到达END
语句时自动关闭
如下存储过程,声明、打开和关闭一个游标,但是对检索的数据什么都没有做
mysql> CREATE PROCEDURE processorders()
-> BEGIN
-> -- Decalre the cursor
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
-> -- Open the cursor
-> OPEN ordernumbers;
-> -- Close the cursor
-> CLOSE ordernumbers;
-> END; //
使用游标数据
在一个游标被打开后,可以使用 FETCH
语句分别访问它的每一行,FETCH
指定检索什么数据(所需的列),检索出来的数据存储在什么地方
它还向前移动游标中的内部行指针,使下一条 FETCH
语句检索下一行(不重复读取同一行)
从游标中检索单个行(第一行):
mysql> CREATE PROCEDURE processorders()
-> BEGIN
-> -- Declare local variables
-> DECLARE o INT;
-> -- Declare the cursor
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
-> -- Open the cursor
-> OPEN ordernumbers;
-> -- Get order number
-> FETCH ordernumbers INTO o;
-> -- Close the cursor
-> CLOSE ordernumbers;
-> END;
-> //
其中 FETCH
用来检索当前行的 order_num
列(将自动从第一行开始)到一个名为 o
的局部声明的变量中。对检索出的数据不做、任何处理
循环检索数据,从第一行到最后一行:
mysql> CREATE PROCEDURE processorders()
-> BEGIN
-> -- Delcare local variables
-> DECLARE done BOOLEAN DEFAULT 0;
-> DECLARE o INT;
-> -- Declare the cursor
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
-> -- Delcare continue handler
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-> -- Open the cursor
-> OPEN ordernumbers;
-> -- Loop through all rows
-> REPEAT
-> -- Get order number
-> FETCH ordernumbers INTO o;
-> -- End of loop
-> UNTIL done END REPEAT;
-> -- Close the cursor
-> CLOSE ordernumbers;
-> END; //
这个例子使用 FETCH
检索当前 order_num 到声明的名为 o
的变量中
但与前一个例子不一样的是,这个例子中的 FETC
H 是在 REPEAT
内,因此它反复执行直到 done
为真(由 UNTIL done END REPEAT; ·
规定)
为使它起作用,用一个 DEFAULT 0
(假,不结束)定义变量 done
那么, done
怎样才能在结束时被设置为真呢?答案
是用以下语句:DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
这条语句定义了一个 CONTINUE HANDLER
,它是在条件出现时被执行的代码。 这里, 它指出当 SQLSTATE '02000' 出现时, SET done=1
SQLSTATE '02000'
是一个未找到条件, 当 REPEAT
由于没有更多的行供循环而不能继续时,出现这个条件
如果调用这个存储过程,它将定义几个变量和一个 CONTINUE HANDLER
,定义并打开一个游标,重复读取所有行,然后关闭游标
如果一切正常,你可以在循环内放入任意需要的处理(在 FETCH
语句之后,循环结束之前)
DECLARE
语句的次序 :
DECLARE
语句的发布存在特定的次序
用
DECLARE
语句定义的局部变量必须在定义任意游标或句柄之前定义,而句柄必须在游标之后定义
不遵守此顺序将产生错误消息
mysql> CREATE PROCEDURE processorders()
-> BEGIN
-> -- Declare local variables
-> DECLARE done BOOLEAN DEFAULT 0;
-> DECLARE o INT;
-> DECLARE t DECIMAL(8,2);
-> -- Declare the cursor
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
-> -- Declare continue handler
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-> -- Create a table to store the results
-> CREATE TABLE IF NOT EXISTS ordertotals2
-> (order_num INT, total DECIMAL(8,2));
-> -- Open the cursor
-> OPEN ordernumbers;
-> -- Loop through all rows
-> REPEAT
-> -- Get order number
-> FETCH ordernumbers INTO o;
-> -- Get the total for this order
-> CALL ordertotal(o, l, t);
-> -- Insert order and total into ordertotals
-> INSERT INTO ordertotals(order_num, total)
-> VALUES(o,t);
-> -- End of loop
-> UNTIL done END REPEAT;
-> -- Close the cursor
-> CLOSE ordernumbers;
-> END; //
在这个例子中,我们增加了另一个名为 t
的变量(存储每个订单的合计)。 此存储过程还在运行中创建了一个新表(如果它不存在的话), 名为 ordertotals
这个表将保存存储过程生成的结果。 FETCH
像以前一样取每个 order_num,然后用 CALL
执行另一个存储过程来计算每个订单的带税的合计(结果存储到 t)
最后,用 INSERT
保存每个订单的订单号和合计
此存储过程不返回数据,但它能够创建和填充另一个表,可以用一条简单的 SELECT
语句查看该表
SELECT * FROM ordertotals2; //