SQL-游标

游标的分类

• 静态游标：静态游标的结果集，在游标打开的时候建立在TempDB中，不论你在操作游标的时候，如何操作数据库，游标中的数据集都不会变。例如你在游标打开的时候，对游标查询的数据表数据进行增删改，操作之后，静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致，则重新关闭打开游标即可。

• 动态游标：这个则与静态游标相对，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新，他们将立即可见。在游标外部所做的更新直到提交时才可见。

• 只进游标：只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。

• 键集驱动游标：打开键集驱动游标时，该有表中的各个成员身份和顺序是固定的。打开游标时，结果集这些行数据被一组唯一标识符标识，被标识的列做删改时，用户滚动游标是可见的，如果没被标识的列增该，则不可见，比如insert一条数据，是不可见的，若可见，须关闭重新打开游标。

静态游标在滚动时检测不到表数据变化，但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化，但消耗的资源却较多。键集驱动游标则处于他们中间，所以根据需求建立适合自己的游标，避免资源浪费。

 

---

语法

声明一个游标

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

参数说明：

• cursor_name：游标名称。

• Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。

• Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。 [ Local | Global ] ：默认为local。

• Forward_Only：指定游标智能从第一行滚到最后一行。Fetch Next 是唯一支持的提取选项。如果在指定 Forward_Only 是不指定 Static、KeySet、Dynamic 关键字，默认为 Dynamic 游标。如果 Forward_Only 和 Scroll 没有指定，Static、KeySet、Dynamic 游标默认为 Scroll，Fast_Forward 默认为 Forward_Only。

• Static：静态游标

• KeySet：键集游标

• Dynamic：动态游标，不支持 Absolute 提取选项

• Fast_Forward：指定启用了性能优化的 Forward_Only、Read_Only 游标（ 如果指定了 Scroll 或 For_Update，就不能指定他 ）。

• Read_Only：不能通过游标对数据进行删改。

• Scroll_Locks：将行读入游标时，锁定这些行，确保删除或更新一定会成功（ 如果指定了 Fast_Forward 或 Static ，就不能指定他 ）。

• Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver 不锁定行，它改用 timestamp 列值的比较结果来确定行读入游标后是否发生了修改，如果表不行 timestamp 列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定了 Fast_Forward ，则不能指定他。

• Type_Warning：指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。

• For Update [ of column_name , .... ]：定义游标中可更新的列。

 提取数据

--提取游标语法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]

参数说明：

• Frist ：结果集的第一行

• Prior ：当前位置的上一行

• Next ：当前位置的下一行

• Last ：最后一行

• Absoute n ：从游标的第一行开始数，第 n 行。

• Relative n ：从当前位置数，第 n 行。

• Into @variable_name [ ,... ] ：将提取到的数据存放到变量 variable_name 中。

@@Fetch_Status

        通过检测全局变量@@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。

        这个状态值可以帮你判断提取数据的成功与否。

举例

# 1 ：基本表如下：

 

 

   使用游标更新或删除某一数据：

--声明游标（ 此为只进游标，仅支持使用 FETCH NEXT ）
DECLARE test_1 CURSOR FOR 
SELECT id,name FROM student;
OPEN test_1;   --打开游标
DECLARE @id int,@name varchar(20);
--提取数据赋值给变量
FETCH NEXT FROM test_1 INTO @id,@name; 
WHILE @@FETCH_STATUS <> -1    --提取成功，进行下一条数据的提取操作
BEGIN
    IF @id = 2
        UPDATE student SET sex = 0 WHERE CURRENT OF test_1;   --游标修改当前数据语法
    IF @id = 10
        DELETE student WHERE CURRENT OF test_1;   --游标删除当前数据语法
    FETCH NEXT FROM test_1 INTO @id,@name;     --移动游标
END
CLOSE test_1;  --关闭游标
DEALLOCATE test_1;  --释放游标

用SQL 语句可以轻松完成：

UPDATE student SET sex = 0 WHERE id = 2;
DELETE student WHERE id = 10;

# 2 ：基本表如下：

使用游标更新或删除某一数据：

--声明游标（ 此为动态游标 ）
DECLARE test_2 CURSOR SCROLL DYNAMIC
FOR SELECT Orderid,Userid FROM 
OrderNum WHERE Ordernum = 'ZEORD003402';
OPEN test_2;
DECLARE @Orderid varchar(6) ,@Userid varchar(11);
FETCH FIRST FROM test_2 INTO @Orderid,@Userid;
WHILE @@FETCH_STATUS <> -1
BEGIN
    IF @Orderid = '39789'
        UPDATE OrderNum SET Userid = '3' WHERE CURRENT OF test_2;
    IF @Orderid = '154074'
        DELETE OrderNum WHERE CURRENT OF test_2;
    FETCH NEXT FROM test_2 INTO @Orderid,@Userid;
    
END
CLOSE test_2;
DEALLOCATE test_2;

用SQL 语句可以轻松完成：

UPDATE OrderNum SET Userid  = '3' WHERE Orderid = '39789';
DELETE OrderNum WHERE Orderid = '154074';