从一个小例子认识SQL游标

1    什么是游标：

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

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

• 允许定位在结果集的特定行。
• 从结果集的当前位置检索一行或一部分行。
• 支持对结果集中当前位置的行进行数据修改。
• 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。
• 提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句。 

——MSDN

不难理解，游标与其他数据库操作的最大不同就是对象是单条记录而不是结果集，一般用于过程化程序里嵌入的SQL语句。在数据库服务程序里用到了自动隐含创建的游标。

 

2    基本用法：

2.1 声明游标

DECLARE 游标名 CURSOR

FOR SELECT语句

2.2 打开游标

OPEN 游标名

2.3 从游标获取数据

FETCH NEXT FROM 游标名 [ INTO FETCH_LIST ]

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

BEGIN
    DECLARE @custname VARCHAR(20)
    DECLARE namecursor CURSOR FOR SELECT CUST_NAME FROM TBL_CUSTOMER OPEN namecursor
    FETCH NEXT FROM namecursor INTO @custname
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        IF (@@FETCH_STATUS <> -2)
        BEGIN
        --操作游标变量
    END
    FETCH NEXT FROM namecursor INTO @custname
END
CLOSE namecursor
DEALLOCATE namecursor
END

2.4 关闭游标

CLOSE 游标名

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

2.5 释放游标

DEALLOCATE 游标名

即删除游标，不可再使用

 

3    一个有意思的小例子：

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

表结构如下：

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

即如下结果：

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

ANAME	BNAME
Adams	James
James	Adams

 

 

 

最终我想到了刚学到的游标，代码如下

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

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

--12.2更新：

突然发现这题可以用极其简单的方法写出来，用游标简直Stupid~~

SELECT A.Ename AS ANAME, B.Ename AS BNAME 
FROM EMP A, EMP B
WHERE A.job = B.job AND A.deptNo <> B.deptNo AND A.Ename < B.Ename
ORDER BY ANAME

掩面遁走~~