mysql之游标

mysql之游标

原文转载于:http://www.cnblogs.com/duhuo/p/4651107.html

游标

        在操作mysql的时候我们知道MySQL检索操作返回一组称为结果集的行。这组返回的行都是与 SQL语句相匹配的行(零行或多行)。使用简单的 SELECT句,例如,没有办法得到第一行、下一行或前 10行,也不存在每次一行地处理所有行的简单方法(相对于成批地处理它们)。有时,需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标( cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条 SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改。
注意:只能用于存储过程。不像多数 DBMS,MySQL 游标只能用于存储过程(和函数)。
 
使用游标
    使用游标涉及几个明确的步骤。
        1、在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的 SELECT语句。
        2、一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
        3、对于填有数据的游标,根据需要取出(检索)各行。
        4、在结束游标使用时,必须关闭游标。
    在声明游标后,可根据需要频繁地打开和关闭游标。在游标打开后,可根据需要频繁地执行取操作。
 
创建游标
    游标用 DECLARE语句创建 DECLARE命名游标,并定义相应的 SELECT语句,根据需要带WHERE和其他子句。例如,下面的语句定义了名为 ordernumbers的游标,使用了可以检索所有订单的 SELECT语句。
复制代码
mysql> delimiter //
mysql> create procedure duhuo()
    -> begin
    -> declare ordnums cursor
    -> for
    -> select order_num from orders;
    -> end
    -> //
复制代码
    这个存储过程并没有做很多事情, DECLARE语句用来定义和命名游标,这里为 ordnums存储过程处理完成后,游标就消失(因为它局限于存储过程)。在我们定义了游标之后。可以打开它。
 
打开与关闭游标
    游标使用OPEN CURSOR语句来打开。如下:
OPEN ordnums;
    在处理 OPEN语句时执行查询,存储检索出的数据以供浏览和滚动。
 
    游标处理完成后,应当使用如下语句关闭游标:
close ordnums;
    CLOSE释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭。
    在一个游标关闭后,如果没有重新打开,则不能使用它。但是,使用声明过的游标不需要再次声明,用 OPEN语句打开它就可以了。此外mysql具有隐含关闭功能。如果你不明确关闭游标, MySQL将会在到达END句时自动关闭它。
 
    下面是前面例子的修改版本:
复制代码
mysql> delimiter //
mysql> create procedure duhuo1()
    -> begin
    -> declare dudu cursor
    -> for
    -> select order_num from orders;
    -> open dudu;
    -> close dudu;
    -> end
    -> //
复制代码
    这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没做。
 
使用游标数据
    在一个游标被打开后,可以使用 FETCH语句分别访问它的每一行。FETCH指定检索什么数据(所需的列),检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行(不重复读取同一行)。
 
    第一个例子从游标中检索单个行(第一行):
复制代码
mysql> delimiter //
mysql>  create procedure demo0()
    -> begin
    -> declare tx int;
    -> declare du1 cursor
    -> for
    -> select order_num from orders;
    -> open du1;
    -> fetch du1 into tx;
    -> close du1;
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)
复制代码
    其中 FETCH用来检索当前行的order_num列(将自动从第一行开始)到一个名为 tx的局部声明的变量中。对检索出的数据不做任何处理。
 
    在下一个例子中,循环检索数据,从第一行到最后一行:
复制代码
mysql> create procedure demo0()
    -> begin
    -> delcare done boolean default 0;
    -> declare du int;
    -> declare ordernu cursor
    -> for
    -> select order_num from orders;
    -> declare continue handler for sqlstate '02000' set done = 1;
    -> open ordernu;
    -> repeat
    -> fetch ordernu into du;
    -> until done end repeat;
    -> close ordernu;
    -> end//
复制代码
    与前一个例子一样,这个例子使用 FETCH检索当前order_num到声明的名为 o的变量中。但与前一个例子不一样的是,这个例子中的 FETCH是在REPEAT 内,因此它反复执行直到 done为真(由UNTILdone 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由于没有更多的行供循环而不能继续时,出现这个条件。
    关于MySQL 5使用的 MySQL错误代码列表,请参阅 http://dev.mysql.com/doc/mysql/en/error-handling.html 
    注意:DECLARE语句的次序。DECLARE语句的发布存在特定的次序。DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义,而句柄必须在游标之后定义。不遵守此顺序将产生错误消息。
    如果调用这个存储过程,它将定义几个变量和一个 CONTINUEHANDLER,定义并打开一个游标,重复读取所有行,然后关闭游标。如果一切正常,你可以在循环内放入任意需要的处理(在 FETCH语句之后,循环结束之前)。
 
    为了把这些内容组织起来,下面给出我们的游标存储过程样例的更进一步修改的版本,这次对取出的数据进行某种实际的处理:
复制代码
mysql> delimiter //
mysql> create procedure liwei()
    -> begin
    -> declare done boolean default 0;
    -> declare li int;
    -> declare wei decimal(8,2);
    -> declare numb cursor
    -> for
    -> select order_num from orders;
    -> declare continue handler for sqlstate '02000' set done=1;
    -> create table if not exists ordertotals
    -> (order_num int,total decimal(8,2));
    -> open numb;
    -> repeat
    -> fetch ordernumbers into li;
    -> call ordertotals(tx,1,t);
    -> insert into ordertotals(order_num, total)
    -> values(li,wei);
    -> until done end repeat;
    -> close numd;
    -> end
    -> //
复制代码
    在这个例子中,我们增加了另一个名为 t的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(如果它不存在的话),名为 ordertotals。这个表将保存存储过程生成的结果。 FETCH像以前一样取每个 order_num,然后用CALL 执行另一个存储过程(我们在前一章中创建)来计算每个订单的带税的合计(结果存储到 t)。最后,INSERT保存每个订单的订单号和合计。
 
    调用存储过程:
call liwei();
 
    此存储过程不返回数据,但它能够创建和填充另一个表,可以用一条简单的 SELECT语句查看该表:
select *
from ordertotals;
    输出:
复制代码
+----------------+--------------+
| order_num | total |
+----------------+--------------+
|   20005   |   158.56    |
|   20006   |  25.78  |
|   20007   |  1068.00   | 
+----------------+--------------+
复制代码
    这样,我们就得到了存储过程、游标、逐行处理以及存储过程调用其他存储过程的一个完整的工作样例。
<think>好的,我现在需要帮助用户解决关于MySQL中动态游标的使用方法的问题。用户明确提到想要了解动态游标的使用方法或者示例,所以我得先回忆一下MySQL游标的相关知识。 首先,动态游标MySQL中通常是指在存储过程中动态生成SQL语句并执行的游标。根据引用内容,动态游标允许在运行时动态生成和执行SQL语句,这比静态游标更灵活。不过,MySQL本身并没有显式的动态游标类型,但可以通过预处理语句来模拟动态游标的功能。我需要确认这一点是否正确,可能需要查阅相关资料或引用中的信息。 根据用户提供的引用[1],动态游标是灵活的查询工具,能动态生成和执行SQL。引用[2]提到MySQL有静态和动态游标两种类型,但实际使用中,动态游标可能需要使用预处理语句来实现。引用[3]中的示例代码使用了变量@o,这可能涉及到动态SQL的执行。 接下来,我需要整理使用动态游标的步骤。通常步骤可能包括:声明变量、准备动态SQL语句、执行语句、定义游标、打开游标、循环获取数据、关闭游标和释放资源。我需要确保每一步都正确,并且符合MySQL的语法规则。 例如,用户可能需要一个具体的示例。我需要构造一个存储过程的例子,比如根据不同的部门统计员工信息。动态SQL部分可能根据传入的部门名称来过滤结果,然后使用游标遍历结果集。这里需要注意预处理语句的写法,以及游标的正确使用,避免常见的错误,比如未声明变量或作用域问题。 另外,需要提到动态游标的优缺点,如引用[2]所述,优点包括灵活处理结果集、流式处理减少内存消耗,缺点则是语法复杂、性能开销和资源管理问题。这些信息对用户全面了解动态游标的应用场景和注意事项很重要。 还需要考虑用户可能的后续问题,比如如何处理动态游标中的异常,或者如何传递参数到动态SQL中。在示例中加入错误处理部分(如DECLARE CONTINUE HANDLER)会更好,这样用户在实际使用时可以避免程序崩溃。 最后,确保回答结构清晰,步骤明确,示例代码正确,并且符合用户的中文要求。同时生成的相关问题要围绕动态游标的性能、应用场景、参数传递和错误处理,这些都是用户可能进一步感兴趣的方面。</think>### MySQL动态游标使用方法及示例 在MySQL中,动态游标需要结合**预处理语句**实现,其核心是通过`PREPARE`和`EXECUTE`动态生成SQL语句,再通过游标遍历结果集。以下是具体步骤和示例: --- #### 一、动态游标使用步骤 1. **声明变量**:定义存储过程参数和局部变量 2. **准备动态SQL**:使用`CONCAT`拼接动态查询语句 3. **执行预处理**:通过`PREPARE`和`EXECUTE`运行动态SQL 4. **定义游标**:基于动态结果集声明游标 5. **遍历数据**:使用`FETCH`逐行处理数据 6. **释放资源**:关闭游标并释放预处理语句 --- #### 二、完整示例代码 ```sql DELIMITER $$ CREATE PROCEDURE dynamic_cursor_demo(IN dept_name VARCHAR(50)) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE emp_id INT; DECLARE emp_name VARCHAR(50); -- 1. 动态SQL拼接 SET @dynamic_sql = CONCAT( 'SELECT id, name FROM employees WHERE department = "', dept_name, '"' ); -- 2. 预处理语句 PREPARE stmt FROM @dynamic_sql; EXECUTE stmt; -- 3. 声明游标 DECLARE cur CURSOR FOR SELECT id, name FROM employees; -- 实际使用动态结果集 -- 4. 错误处理 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO emp_id, emp_name; IF done THEN LEAVE read_loop; END IF; -- 业务逻辑处理(示例:输出员工信息) SELECT CONCAT('ID:', emp_id, ' Name:', emp_name) AS employee_info; END LOOP; CLOSE cur; DEALLOCATE PREPARE stmt; -- 释放预处理资源 END$$ DELIMITER ; ``` --- #### 三、关键点说明 1. **动态SQL构建** 通过`CONCAT()`函数拼接包含变量的查询条件,实现动态过滤条件[^1] 2. **预处理优势** 使用`PREPARE`语句可防止SQL注入,同时支持动态参数传递[^2] 3. **游标作用域** 动态生成的查询结果集需通过临时表或子查询转换为可用游标数据源[^3] --- #### 四、动态游标优缺点 | 优点 | 缺点 | |------|------| | 灵活处理复杂查询逻辑 | 语法复杂度高 | | 支持运行时参数传递 | 性能低于静态游标 | | 减少内存占用(流式处理) | 需要手动资源管理 | ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值