这该死的游标，怎么这么难呢？

2024-03-02 14:15:16

SQL专栏

SQL数据库基础知识汇总

SQL数据库高级知识汇总

经常有小伙伴吐槽SQL中最难的可能就是游标了，游标确实不好理解，我们常见的数据操作都是返回一个结果集，而游标则是返回单行记录。

今天我们就来给小伙伴们讲解一下这“该死”的游标，该如何使用。

1 、什么是游标

关系数据库中的操作会对整个行集起作用。例如，由 SELECT 语句返回的行集包括满足该语句的 WHERE 子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展。

游标通过以下方式来扩展结果处理：

允许定位在结果集的特定行。
从结果集的当前位置检索一行或一部分行。
支持对结果集中当前位置的行进行数据修改。
为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。
提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句。
不难理解，游标与其他数据库操作的最大不同就是对象是单条记录而不是结果集，一般用于过程化程序里嵌入的SQL语句。在数据库服务程序里用到了自动隐含创建的游标。

2 、基本用法

2.1 声明游标

DECLARE 游标名 CURSOR

FOR SELECT语句

2.2 打开游标

OPEN 游标名

2.3 从游标获取数据

FETCH NEXT | PRIOR | FIRST | LAST |

ABSOLUTE{ n| @nvar} |

RELATIVE { n| @nvar}

FROM 游标名 [ INTO FETCH_LIST ]

这里要注意的是获取游标里的数据，除了常用的FETCH NEXT（获取下一行），SQL Server提供了6种定位选项：

NEXT
紧跟当前行返回结果行，并且当前行递增为返回行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。 NEXT 为默认的游标提取选项。
PRIOR
返回紧邻当前行前面的结果行，并且当前行递减为返回行。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。
FIRST
返回游标中的第一行并将其作为当前行。
LAST
返回游标中的最后一行并将其作为当前行。
ABSOLUTE { n| @nvar}
如果 n 或 @nvar 为正，则返回从游标起始处开始向后的第 n 行，并将返回行变成新的当前行**。如果 n 或 @nvar 为负，则返回从游标末尾处开始向前的第 n 行，并将返回行变成新的当前行**。如果 n 或 @nvar 为 0，则不返回行。 n 必须是整数常量，并且 @nvar 必须是 smallint、tinyint 或 int。
RELATIVE { n| @nvar}
如果 n 或 @nvar 为正，则返回从当前行开始向后的第 n 行，并将返回行变成新的当前行**。如果 n 或 @nvar 为负，则返回从当前行开始向前的第 n 行，并将返回行变成新的当前行**。如果 n 或 @nvar 为 0，则返回当前行。在对游标进行第一次提取时，如果在将 n 或 @nvar 设置为负数或 0 的情况下指定 FETCH RELATIVE，则不返回行。 n 必须是整数常量，并且 @nvar 必须是 smallint、tinyint 或 int。

从游标获取数据需要注意可能到达游标末尾，以下方法解决这个问题以避免用户在关闭游标时产生错误

--开始一个事务
BEGIN
--定义一个变量和一个游标
   DECLARE @custname VARCHAR(20)
   DECLARE namecursor CURSOR FOR 
--打开游标，并从游标中获取数据，然后插入变量中
     SELECT CUST_NAME FROM TBL_CUSTOMER OPEN namecursor
   FETCH NEXT FROM namecursor INTO @custname
--当FETCH 语句失败或此行不在结果集中时，执行下面的IF语句
   WHILE (@@FETCH_STATUS <> -1)
   BEGIN
--如果被提取的行不存在，执行下面的事务
       IF (@@FETCH_STATUS <> -2)
       BEGIN
       --操作游标变量
   END

   FETCH NEXT FROM namecursor INTO @custname
END
CLOSE namecursor
DEALLOCATE namecursor
END

其中上面的WHILE语句和IF语句中有个系统参数@@FETCH_STATUS，是MicroSoft SQL SERVER的一个全局变量，它有三个值：
0 FETCH 语句成功

-1 FETCH 语句失败或此行不在结果集中

-2 被提取的行不存在

2.4 关闭游标

CLOSE 游标名

关闭后不能对游标进行读取等操作，但可以使用OPEN语句再次打开

2.5 释放游标

DEALLOCATE 游标名

即删除游标，不可再使用

3、游标示例

虽然知道了游标的概念和基本用法，但对于什么时候用游标还很模糊，甚至误认为游标可以被子查询所代替。直到遇到了这个有意思的小例子：
表结构如下：

题目要求是：列出从事同一种工作但属于不同部门的雇员的不同组合

即如下结果：

在想尽了子查询、表连接、建临时表等等办法之后，我发现我遇到了一个不可逾越的障碍：无法排除两个名字组合的唯一性。即：我得到的结果可能是如下

最终我们用游标，代码如下：


SELECT A.Ename AS ANAME, B.Ename AS BNAME
INTO #t
FROM EMP A
JOIN EMP B
ON A.job = B.job AND A.deptNo <> B.deptNo and A.Ename<>b.Ename
ORDER BY ANAME

--DROP TABLE #t

DECLARE TEST_CURSOR CURSOR FOR
SELECT ANAME, BNAME FROM #t

OPEN TEST_CURSOR
 DECLARE @ANAME VARCHAR(20)
 DECLARE @BNAME VARCHAR(20)

 FETCH NEXT FROM TEST_CURSOR INTO @ANAME, @BNAME
 DELETE FROM #t WHERE ANAME=@BNAME AND BNAME=@ANAME
 WHILE @@FETCH_STATUS = 0
 BEGIN
   FETCH NEXT FROM TEST_CURSOR INTO @ANAME, @BNAME
   DELETE FROM #t WHERE ANAME=@BNAME AND BNAME=@ANAME
 END

CLOSE TEST_CURSOR
DEALLOCATE TEST_CURSOR

SELECT * FROM #t

以上就是游标的一些简单介绍，其实工作中不到万不得已，一般不会使用游标，因为对于数据量大的表使用游标，那执行效率绝对是个灾难。

SQL讲究的是简单才是王道，切勿为了追求什么骚操作把数据库给整垮了，切记！

码农公寓

相关文章