MySQL游标包括两部分_《MySQL必知必会》学习笔记十四(游标)------了解部分

MySQL必知必会知识预览

第一章——了解SQL

第二章——MySQL简介

第三章——使用MySQL

第四章——检索数据

第五章——排序检索数据

第六章——过滤数据

第七章——数据过滤

第八章——用通配符进行过滤

第九章——用正则表达式进行搜索

第十章——创建计算字段

第十一章——使用数据处理函数

第十二章——汇总数据

第十三章——分组数据

第十四章——使用子查询

第十五章——联结表

第十六章——创建高级联结

第十七章——组合查询

第十八章——全文本搜索

第十九章——插入数据

第二十章——更新和删除数据

第二十一章——创建和操纵表

第二十二章——使用视图

第二十三章——使用储存过程

第二十四章——使用游标

第二十五章——使用触发器

第二十六章——管理事务处理

第二十七章——全球化和本地化

第二十八章——安全管理

第二十九章——数据库维护

第三十章——改善性能

————————————– 华丽的分隔符 ————————————————–

游标

为什么使用游标，什么是游标，如何使用游标，

————————————– ————————————– ————————————–

游标的概念

首先考虑：MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行(零行或多行)。使用简单的SELECT语句，没有办法得到第一行、下一行或前十行等，也不存在每次一行地处理所有航的简单方法(相对于成批的处理他们)。

有时候，我们需要在检索出来的行中前进或者后退一行或者多行。这就是我们为什么使用游标。

于是：

游标是一个存储在MySQL服务器上的数据库查询呢，她不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要进行滚动或者浏览其中的数据。

游标主要是用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

注意：游标只能用于存储过程。(仅限MySQL)。

使用游标规则

1)在能够使用游标钱，必须声明(定义)它。这个过程实际上没有检索数据，他只是定义要使用的SELECT语句。

2)一旦声明后，必须打开游标以供使用，这个过程用前面定义的SELECT语句把数据实际检索出来。

3)对于填有数据的游标，根据需要取出(检索)各行。

4)在结束游标使用时，必须关闭游标。

在声明游标后，可根据需要频繁地打开和关闭游标。再打开游标之后，可以根据需要频繁地执行取操作。

使用游标

1、创建游标

游标用DECLARE语句创建。DECLARE命名游标，并定义相关的SELECT语句，根据需要带WHERE和其他子句。

CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;

END;

定义了名为 ordernumber 的游标，使用了可以检索所有订单的SELECT语句。

DECLARE 语句用来完成定义和命名游标。需要注意的是在存储过程处理完成后，游标就消失。【游标仅仅局限于存储过程】

2、打开和关闭游标

打开或者关闭游标使用OPEN CursorName和CLOSE CursorName

OPEN ordernumbers;

close ordernumbers;

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。

CLOSE释放游标使用的所有内部内存和资源，因此每个游标不再需要时都应该关闭。

在一个游标关闭后，没有再次重新打开，则不能使用它。但是使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。

隐含关闭：如果你不明确关闭游标，则MySQL将会在到达END语句时自动关闭它。

CREATE PROCEDURE processorders() BEGIN -- declare the cursor DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;

-- open the cursor

OPEN ordernumbers;

-- close the cursor

CLOSE ordernumbers;

END;

//存储过程声明、打开和关闭一个游标，对数据未做处理

3、使用游标数据

FETCH 语句

在打开游标之后，可以使用FETCH语句分别访问它的每一行。

FECTCH 指定检索什么数据(所需要的列)，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行(不重复读取同一行)

1、从游标中检索单个行(第一行)

CREATE PROCEDURE processorders() BEGIN -- declare local variables DECLARE o INT;

--delcare 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的局部变量中。对检索数据不做任何处理。

2、循环检索数据，从第一行到最后一行

CREATE PROCEDURE processorders() BEGIN -- Declare local variables DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

-- Declare the cursor

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

--Declare 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的变量中，REPEAT将FETCH包含在内，反复执行，直到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由于没有更多的行供循环而不能继续时，出现这个条件。

DECLARE 语句的次序 DECLARE 语句的发布在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或者句柄之前定义，而句柄必须在游标之后定义，不遵守这样的顺序，将会产生错误消息。

如果我们调用这个存储过程，它将定义几个变量和一个CONTINUE HANDLER，定义并打开一个游标，重复读取所有行，然后关闭游标。我们可以在循环内放入任意需要的处理(在FETCH语句之后，循环结束之前)

在这里我们使用了REPEAT语句进行循环，另外MySQL还支持循环语句【三种方式while、repeat、loop】，他可以重复执行代码。通常REPEAT语句的语法更适合用于对游标的循环。

3、对数据进行操作

CREATE PROCEDURE processorders() BEGIN -- Declare local variables DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLATE 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 ordertotals(order_num INT, total DECLMAL(8,2));

--Open the cursor

OPEN ordernumbers;

--Loop through all rows

REPEAT

--Get order number

FETCH ordernumbers INTO o;

-- Get order number FETCH ordernumbers INTO o;

-- get the total foe this order

CALL ordertotal(o,1,t);

--Inert order and total into ordertotals

INSERT INTO ordertotas(order_num,total)VALUES (o,t);

-- END of loop

UNTIL done END REPEAT；

-- close the cursor

CLOSE ordernumbers;

END;

-------------------------------------------

ordertotal //带税合计存储过程【学习笔记十三】

-- Name：ordertotal

-- Parameters： onumber = order number

-- taxable = 0 if not taxable,1 if taxable

-- ototal = order total variable

CREATE PROCEDURE ordertotal( IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2) )COMMENT 'Obtain order total, optionally adding tax' BEGIN -- declear variable for total DECLARE total DECIMAL(8,2);

-- declear tax percentage

DECLARE taxrate INT DEFAULT 6;

-- get the order total

SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = onumber INTO total;

-- IS this taxable?

IF taxable THEN

-- yes ,so add taxrate to the total

SELECT total+(total/100*taxrate)INTO total;

END IF;

-- finally ,save to out variable

SELECT total INTO ototal;

END;

我们在这里增加了一个名为t的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(不存在的情况下进行创建)，名为ordertotals。这个表将保存存储过程生成的结果。FETCH获取每个order_num ,然后用CALL执行另外一个存储过程来计算灭个订单的带税的合计存储到t中，最后我们利用INSERT保存每个订单的订单号和合计。

这个存储过程不返回数据，但是它创建和填充另外一个表，可以使用一条简单的【SELECT * from ordertotals】进行查询。

从前到后，我们得到了存储过程、游标、逐行处理以及存储过程调用其他存储过程的一个完整的工作例子。

————————————– ————————————– ————————————– 个人认为游标并不是重要部分是因为，我们在获取数据的时候总是会获取所有数据，然后在后台代码中根据需求进行分析，一般不会在数据库中进行前一个后一个等等类似的数据操作。即使在分页过程中会运用到前数据或者后数据，那样的话我们直接是利用LIMIT进行 操作，而用不到游标，而且游标必须是限制在存储过程中，所以，我认为了解即可。