SQL Server 中的 KEYSET 光标

SQL Server 中的 KEYSET 光标只能从第一行移到最后一行，以及从最后一行移到第一行。当 SQL Server KEYSET 光标打开时，行的顺序和成员固定。用于唯一标识行的键集将存储在 tempdb 数据库下的一个表中。

请记住，对具有唯一键的表使用 SQL Keyset 光标。否则，此光标将表现得像任何其他 静态光标。一旦您在 Employee 表上打开 SQL KEYSET 光标，该表上的任何 INSERT 操作都不会反映在光标中。但是 DELETE 和 UPDATE 操作都会反映。

对于此 SQL Server keyset 光标演示，我们将使用下面显示的 Employee 表，该表包含 14 条记录。

SQL Server 中的 KEYSET 光标 示例 1

让我们看看如何在 SQL Server 中声明和打开 keyset 光标，以及如何从光标中获取行。在此示例中，我们将使用 WHILE LOOP 来循环遍历 SQL Server 光标元素并将其作为输出打印。

请记住，如果您将 SQL keyset 光标应用于至少没有唯一列的任何表，它将表现为 静态光标。

SET NOCOUNT ON
-- Declaring the Variables 
DECLARE @EmpID INT,
        @EmpName VARCHAR(50),
        @EmpEducation VARCHAR(50),
	@EmpOccupation VARCHAR(50),
	@EmpYearlyIncome DECIMAL (10, 2), 
	@EmpSales DECIMAL (10, 2);

DECLARE keyset_employee_cursor CURSOR 
KEYSET FOR 
	SELECT [ID]
	      ,[Name]
	      ,[Education]
	      ,[Occupation]
	      ,[YearlyIncome]
	      ,[Sales]
	FROM EmployeeTable

OPEN keyset_employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
	FETCH NEXT FROM keyset_employee_cursor 
              INTO @EmpID, @EmpName, @EmpEducation,
		       @EmpOccupation, @EmpYearlyIncome, @EmpSales
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT 'ID = '+ CONVERT(VARCHAR(10), @EmpID)+', Full Name = '+ @EmpName
			+', Education = '+ @EmpEducation +', Occupation = '+ @EmpOccupation 
			+ ', Yearly Income = ' + CONVERT(VARCHAR(10),@EmpYearlyIncome)
			+ ', Sales Amount = ' + CONVERT(VARCHAR(10),@EmpSales)
		
                FETCH NEXT FROM keyset_employee_cursor
                      INTO @EmpID, @EmpName, @EmpEducation,
				   @EmpOccupation, @EmpYearlyIncome, @EmpSales
	END
END
CLOSE keyset_employee_cursor
DEALLOCATE keyset_employee_cursor
SET NOCOUNT OFF

如果您观察光标声明，我们在声明光标时使用了 KEYSET 光标。我们已经在 SQL Server 中的静态光标 示例中解释了其余步骤。

我将添加 WAITFOR DELAY ’00:00:05′ 来延迟查询的执行，并打印 @@FETCH_STATUS 来打印 FETCH 状态（-2 到 0）。

SQL Server 中的 KEYSET 光标 示例 2

当光标打开时执行 INSERT 操作会发生什么？

SET NOCOUNT ON
-- Declaring the Variables 
DECLARE @EmpID INT,
        @EmpName VARCHAR(50),
        @EmpEducation VARCHAR(50),
	@EmpOccupation VARCHAR(50),
	@EmpYearlyIncome DECIMAL (10, 2), 
	@EmpSales DECIMAL (10, 2);

DECLARE keyset_employee_cursor CURSOR 
KEYSET FOR 
	SELECT TOP 5 [ID]
	      ,[Name]
	      ,[Education]
	      ,[Occupation]
	      ,[YearlyIncome]
	      ,[Sales]
	FROM EmployeeTable

OPEN keyset_employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
	FETCH NEXT FROM keyset_employee_cursor 
              INTO @EmpID, @EmpName, @EmpEducation,
		       @EmpOccupation, @EmpYearlyIncome, @EmpSales
	WHILE @@FETCH_STATUS = 0
	BEGIN
                WAITFOR DELAY '00:00:05'
		PRINT 'ID = '+ CONVERT(VARCHAR(10), @EmpID)+', Full Name = '+ @EmpName
			+', Education = '+ @EmpEducation +', Occupation = '+ @EmpOccupation 
			+ ', Yearly Income = ' + CONVERT(VARCHAR(10),@EmpYearlyIncome)
			+ ', Sales Amount = ' + CONVERT(VARCHAR(10),@EmpSales)
		
                FETCH NEXT FROM keyset_employee_cursor
                      INTO @EmpID, @EmpName, @EmpEducation,
				   @EmpOccupation, @EmpYearlyIncome, @EmpSales
                PRINT @@FETCH_STATUS
	END
END
CLOSE keyset_employee_cursor
DEALLOCATE keyset_employee_cursor
SET NOCOUNT OFF

当光标打开时，让我使用以下查询插入几条记录。

INSERT INTO [EmployeeTable] (
		[Name]
	       ,[Education]
	       ,[Occupation]
	       ,[YearlyIncome]
	       ,[Sales]
	     )
VALUES ('Imran Khan', 'Bachelors', 'Skilled Professional', 69000, 100)
      ,('Doe Lara', 'Bachelors', 'Management', 85000, 60)
      ,('Ramesh Kumar', 'High School', 'Professional', 45000, 630)
      ,('John Ruiz', 'Partial High School', 'Clerical', 40000, 220)

如果您观察下面的屏幕截图，尽管我们已将四条新记录插入到我们的 Employee 表中，但 keyset 光标仍返回前 5 条记录。因为一旦光标打开，它就会固定键值，而另一个用户执行的插入操作不会在光标内反映出来。

请使用以下 SQL 查询来检查新记录是否已插入到 Employee 表中。

SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]

SQL Server 中的 KEYSET 光标 示例 3

当光标打开时执行 UPDATE 操作会发生什么？为此，我们使用之前使用的光标定义。在这里，我们选择所有列而不是前 5 列。

当光标打开时，让我使用以下查询更新职业为管理人员的员工的年收入和销售额。

UPDATE [EmployeeTable] 
    SET [YearlyIncome] = 999999,
        [Sales] = 15000
WHERE [Occupation] = N'Management'

您可以看到，尽管 keyset 光标已打开，但它仍显示我们 Employee 表中的更新值。因为即使光标已打开，它也可以读取更新后的值（由另一个用户更新的值）。

让我检查 Employee 表，看看更新是否已在服务器级别发生。

SQL Server 中的 KEYSET 光标 示例 4

当 KEYSET 光标打开时执行 DELETE 操作。为此，我将在光标打开时删除职业为专业人士或教育程度为部分高中毕业的员工。

DELETE FROM [EmployeeTable] 
WHERE [Occupation] = N'professional' OR
      [Education] = N'Partial High School'

尽管 Sql Server keyset 光标已打开，但在到达第 3 条记录时终止了，因为第 4 条记录的 Occupation = Professional，所以 fetch 状态返回 -2。尝试增加时间延迟以获得正确的结果。

让我检查 Employee 表，看看删除是否已在原始表中执行。