sqlserver游标概念与实例全面解说

游标

    Mysql检索操作返回一组称为结果集的行。

    有时，需要在检索出来的行中前进或者后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在mysql服务器上的数据库查询，它不是一条SELECT语句，而是被SELECT语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

    不像多数的DBMS，MySQL游标仅在存储程序和函数内被支持。语法如同在嵌入的SQL中。游标当前是不敏感的，只读的及不滚动的。不敏感意为服务器可以活不可以复制它的结果表。

    游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作；游标有下面这些属性：

• 游标是只读的，也就是不能更新它；

• 游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；

• 避免在已经打开游标的表上更新数据。

    游标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

使用游标的步骤：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

CREATE PROCEDURE curdemo()          -- 创建存储过程

BEGIN

  DECLARE done INT DEFAULT 0;       -- 声明变量

  DECLARE a CHAR(16);

  DECLARE b,c INT;

   

  -- 声明游标

  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;

  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

   

  -- 打开游标

  OPEN cur1;

  OPEN cur2;

  

  REPEAT

    FETCH cur1 INTO a, b;

    FETCH cur2 INTO c;

    IF NOT done THEN

       IF b < c THEN

          INSERT INTO test.t3 VALUES (a,b);

       ELSE

          INSERT INTO test.t3 VALUES (a,c);

       END IF;

    END IF;

  UNTIL done END REPEAT;

   

  -- 关闭游标

  CLOSE cur1;

  CLOSE cur2;

END

声明游标

1

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个游标。也可以在子程序中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。也就是定义一个SELECT语句。

SELECT语句不能有INTO子句。

游标OPEN语句

1

OPEN cursor_name

这个语句打开先前声明的游标。

游标FETCH语句

1

FETCH cursor_name INTO var_name [, var_name] ...

这个语句用指定的打开游标读取下一行（如果有下一行的话），并且前进游标指针。

游标CLOSE语句

1

CLOSE cursor_name

这个语句关闭先前打开的游标。

如果未被明确地关闭，游标在它被声明的复合语句的末尾被关闭，也就是在到达END语句时自动关闭它。

    在使用游标时需要注意的是，使用定义一个针对NOT FOUND的条件处理函数(condition handler)来避免出现“no data to fetch”这样的错误，条件处理函数就是当某种条件产生时所执行的代码，这里但我们游标指到记录的末尾时，便达到NOT FOUND这样条件，这个时候我们希望继续进行后面的操作，所以我们会在下面的代码中看到一个CONTINUE。

1

2

DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_products = 1; 

/* when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/

流程控制语句

IF, CASE, LOOP, WHILE, ITERATE, 及 LEAVE 结构被完全实现。

有过编程经验的同学，都会很熟悉这些东西，与编程语言的控制语句非常相似。

IF语句

1

2

3

4

IF search_condition THEN statement_list

    [ELSEIF search_condition THEN statement_list] ...

    [ELSE statement_list]

END IF

    IF实现了一个基本的条件结构。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

CASE语句

1

2

3

4

5

6

7

8

9

10

11

CASE case_value

    WHEN when_value THEN statement_list

    [WHEN when_value THEN statement_list] ...

    [ELSE statement_list]

END CASE

Or:

CASE

    WHEN search_condition THEN statement_list

    [WHEN search_condition THEN statement_list] ...

    [ELSE statement_list]

END CASE

    存储过程的CASE语句实现一个复杂的条件结构。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。与SHELL中的case语句作用类似。

   

LOOP语句

1

2

3

[begin_label:] LOOP

    statement_list

END LOOP [end_label]

    LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环结构。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。

    LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。

LEAVE语句（类比： break）

1

LEAVE label

    这个语句被用来退出任何被标注的流程控制结构。它和BEGIN ... END或循环一起被使用。

ITERATE语句(类比： continue)

1

ITERATE label

    ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。”

例如：

1

2

3

4

5

6

7

8

9

CREATE PROCEDURE doiterate(p1 INT)

BEGIN

  label1: LOOP

    SET p1 = p1 + 1;

    IF p1 < 10 THEN ITERATE label1; END IF;

    LEAVE label1;

  END LOOP label1;

  SET @x = p1;

END

REPEAT语句

1

2

3

4

[begin_label:] REPEAT

    statement_list

UNTIL search_condition

END REPEAT [end_label]

REPEAT语句内的语句或语句群被重复，直至search_condition 为真。

REPEAT 语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。

例如：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

mysql> delimiter //

  

mysql> CREATE PROCEDURE dorepeat(p1 INT)

    -> BEGIN

    ->   SET @x = 0;

    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;

    -> END

    -> //

Query OK, 0 rows affected (0.00 sec)

  

mysql> CALL dorepeat(1000)//

Query OK, 0 rows affected (0.00 sec)

  

mysql> SELECT @x//

+------+

| @x   |

+------+

| 1001 |

+------+

1 row in set (0.00 sec)

WHILE语句

1

2

3

[begin_label:] WHILE search_condition DO

    statement_list

END WHILE [end_label]

WHILE语句内的语句或语句群被重复，直至search_condition 为真。

WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。

例如：

1

2

3

4

5

6

7

8

9

CREATE PROCEDURE dowhile()

BEGIN

  DECLARE v1 INT DEFAULT 5;

  

  WHILE v1 > 0 DO

    ...

    SET v1 = v1 - 1;

  END WHILE;

END

官方文档

http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html

 我们先不讲游标的什么概念，步骤及语法，先来看一个例子：

            

       表一 OriginSalary                                                    表二 AddSalary

 

现在有2张表，一张是OriginSalary表--工资表，有三个字段0_ID 员工号（NVARCHAR）、O_Name员工姓名（NVARCHAR）、O_Salary工资（FLOAT）。
另一张表AddSalary表—加薪表。有2个字段，O_ID员工号、A_Salary增加工资。两张表的O_ID是一一对应的，现在求将加薪的工资+原来的工资=现在的工资，也就是O_Salary=O_Salary+A_Salary,修改表OriginSalary的工资字段。
对于一些不熟悉游标的程序员来说，这个并不是什么很难的问题，这个问题用程序来实现可能也很简单。我先说说，用ASP.NET程序解决这个问题的思路：
1.       先获得表OriginSalary的记录数，写个循环。
2.       写SQL语句“select * from dbo.OriginSalary as A left join dbo.AddSalary as B on A.O_ID=B.O_ID”获得视图。
3.       使用Dataset获得O_Salary=O_Salary+A_Salary。
4.       写UPDATE语句“update OriginSalary set O_Salary=”相加的值” where O_ID=”获得值”
5.       循环3次，完成此功能。
还有一种方法就是写存储过程，在这里我就不列出来了。
我想大家在学习游标之前好好想想这个问题，及一些批量处理的例子。可能有的人会说：“难道数据库不能一行一行的处理数据吗？将表AddSalary的数据逐行的取出，然后表 OriginSalary数据逐行的修改？”答案当然是肯定。这就是游标概念。接下来的一章我们会好好的讲讲什么是游标？我会用游标来解决刚才留给大家的问题。
 
1.1游标的概念
 游标(Cursor)它使用户可逐行访问由SQL Server返回的结果集。使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。游标机制允许用户在SQL server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。
1.2 游标的优点
从游标定义可以得到游标的如下优点，这些优点使游标在实际应用中发挥了重要作用：
  1）允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。
  2）提供对基于游标位置的表中的行进行删除和更新的能力。
  3）游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。
1.3 游标的使用
 讲了这个多游标的优点，现在我们就亲自来揭开游标的神秘的面纱。
 使用游标的顺序： 声名游标、打开游标、读取数据、关闭游标、删除游标。
1.3.1声明游标
最简单游标声明：DECLARE <游标名>CURSOR FOR<SELECT语句>；
其中select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询
例子：[已表2 AddSalary为例子]
Declare mycursor cursor for select * from AddSalary
这样我就对表AddSalary申明了一个游标mycursor
 
【高级备注】
DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT语句>
这里我说一下游标中级应用中的[INSENSITIVE]和[SCROLL]
INSENSITIVE
表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。
另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。
a.在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；
b.使用OUTER JOIN；
c.所选取的任意表没有索引；
d.将实数值当作选取的列。 
SCROLL
表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再
重开游标。 
 
1.3.2 打开游标
非常简单，我们就打开刚才我们声明的游标mycursor
OPEN mycursor
 
1.3.3读取数据
FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名  | @游标变量名 } [ INTO @变量名 [，…] ] 
参数说明： 
NEXT   取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。 
INTO @变量名[,…]  把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。 
 
现在我们就取出mycursor游标的数据吧！
 
当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。就本例而言，可以使用下列操作读取第1行数据： 
Eg: Fetch next from mycursor 或则 Fetch first from mycursor 
这样我就取出了游标里的数据，但是光光这样可不够，我们还需要将取出的数据赋给变量
//声明2个变量
declare @O_ID NVARCHAR(20)
declare @A_Salary float
//将取出的值传入刚才声明的2个变量
Fetch next from mycursor into @ O_ID,@ A_Salary
 
1.3.4关闭游标
CLOSE mycursor    
          
1.3.5删除游标 
DEALLOCATE mycursor          
 
1.3.6 实例训练
如上我介绍完了游标使用的5个步骤，那现在我们就来上上手，练习用游标取出表2 AddSalary的数据。
为了运行我们自己创建的游标，我们将游标写在存储过程里，方便我们看到游标的整个使用过程。
在sqlserver2000中新建一个存储过程：
CREATE PROCEDURE PK_Test
AS
//声明2个变量
declare @O_ID nvarchar(20)   
declare @A_Salary float
 
//声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
declare mycursor cursor for select O_ID,A_Salary from AddSalary
 
//打开游标
open mycursor
 
//从游标里取出数据赋值到我们刚才声明的2个变量中
fetch next from mycursor into @O_ID,@A_Salary
 
//判断游标的状态
//0 fetch语句成功     
//-1 fetch语句失败或此行不在结果集中     
//-2被提取的行不存在
while (@@fetch_status=0) 
begin 
 
//显示出我们每次用游标取出的值 
   print '游标成功取出一条数据' 
   print @O_ID
   print @A_Salary
 
//用游标去取下一条记录
   fetch next from mycursor into @O_ID,@A_Salary
end 
//关闭游标
close mycursor 
//撤销游标
deallocate mycursor
GO
 
通过上面的注释，我想大家都明白了整个游标的创建过程了吧。但是我们现在还是一个抽象的了解，我们学任何知识，都要用于实践，这样才能使抽象的东西变的具体。
那我们就运行这个存储过程，看看游标到底是怎么取值的：
我们打开SQLSERVER2000的查询分析器，制定好数据库后，我们执行存储过程
Exec PK_Test

让我看看效果吧（如图）

 

通过实例我们可以看到游标逐行逐行都把值都取出来了。那么我请大家先不看下面的答案，在引言部分我刚才留个大家的问题试一下能不能解决？
现在我们写一个存储过程解决刚才我留下来的问题吧
CREATE PROCEDURE PK_SalaryAdd
AS
declare @O_ID nvarchar(20),@A_Salary float
declare mycursor cursor for select O_ID,A_Salary from AddSalary
open mycursor
fetch next from mycursor into @O_ID,@A_Salary
while(@@fetch_status = 0)
begin
Update OriginSalary set O_Salary=O_Salary+@A_Salary where O_ID=@O_ID
fetch next from mycursor into @O_ID,@A_Salary
end
close mycursor
deallocate mycursor
GO
 
按照老方法，我们用查询分析器来执行我们的存储过程，看看结果是怎么样的？
Exec PK_SalaryAdd
让我看看效果吧（如图）

  

执行存储过程，看到我们影响了3行数据

   
用sql语句，看看表OriginSalary现在的结果：                                                       

 

 

1.4 结束语
 很高兴大家能把这个教程看完，其实这只是游标的最最基础的一个应用，显示生活的逻辑的关系中，可能有更复杂的游标。但是我们只有学会走路，才能跑步嘛，o(∩_∩)o…。