SQL Server 中的动态游标

SQL 动态游标与静态游标恰恰相反。您可以使用此 SQL Server 动态游标执行 INSERT、DELETE 和 UPDATE 操作。与静态游标不同，动态游标中进行的所有更改都会反映在原始数据中。

下面我们来看如何在 SQL Server 中创建动态游标，并通过一个示例说明如何在动态游标中执行更新和删除操作。在本演示动态游标的过程中，我们将使用包含 14 条记录的 Employee 表。

SQL Server 中的动态游标用于 UPDATE

在此示例中，我们将向您展示如何声明和打开动态游标，以及如何在动态游标内执行更新操作。

为此，我们使用 DECLARE CURSOR 语句。在动态游标中，我们将使用 WHILE LOOP 来循环遍历 Server 游标元素并执行更新。

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);

-- SQL Dynamic Cursor Declaration
DECLARE dynamic_employee_cursor CURSOR 
DYNAMIC FOR 
	SELECT [ID]
	      ,[Name]
	      ,[Education]
	      ,[Occupation]
	      ,[YearlyIncome]
	      ,[Sales]
	FROM EmployeeTable
        ORDER BY Occupation

OPEN dynamic_employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
      FETCH NEXT FROM dynamic_employee_cursor
            INTO @EmpID, @EmpName, @EmpEducation,
	         @EmpOccupation, @EmpYearlyIncome, @EmpSales
      WHILE @@FETCH_STATUS = 0
      BEGIN
	IF @EmpOccupation = N'Management'
 	    UPDATE [EmployeeTable] 
		SET [YearlyIncome] = 999999,
		    [Sales] = 15000
	    WHERE CURRENT OF dynamic_employee_cursor                
		
        FETCH NEXT FROM dynamic_employee_cursor 
             INTO @EmpID, @EmpName, @EmpEducation,
	          @EmpOccupation, @EmpYearlyIncome, @EmpSales
      END
END
CLOSE dynamic_employee_cursor
DEALLOCATE dynamic_employee_cursor
SET NOCOUNT OFF 
GO

分析

首先，我们使用  SET NOCOUNT ON 来停止 SQL 查询中“受影响的行数”消息的显示。然后，我们声明了几个变量来存储来自游标的数据。接着，我们声明并打开了名为 dynamic_employee_cursor 的 SQL Server 动态游标，用于处理 Employee 表中的所有记录。

接下来，我们在 IF 语句中使用 @@CURSOR_ROWS 来检查游标中是否有记录。

IF @@CURSOR_ROWS > 0

下面的语句会将 dynamic_employee_cursor 中的下一条记录提取到已声明的变量中。

FETCH NEXT FROM dynamic_employee_cursor
           INTO @EmpID, @EmpName, @EmpEducation,
	        @EmpOccupation, @EmpYearlyIncome, @EmpSales

然后，我们使用 WHILE LOOP 来循环遍历游标元素，并在循环内使用 FETCH_STATUS 来检查 FETCH 语句的状态。

在循环内部，我们使用了另一个 IF 语句来检查 Occupation 是否等于 management。

IF @EmpOccupation = N'Management'

如果条件为 TRUE，游标将使用 UPDATE 语句来更新年收入和销售额。

UPDATE [EmployeeTable] 
SET [YearlyIncome] = 999999,
    [Sales] = 15000
WHERE CURRENT OF dynamic_employee_cursor

提示：如果您的表（这里是 Employee 表）有主键，那么您才可以使用 WHERE CURRENT OF 语句。否则，它将引发错误。

然后，我们使用 FETCH NEXT 来获取游标中的下一条记录。

FETCH NEXT FROM dynamic_employee_cursor 
           INTO @EmpID, @EmpName, @EmpEducation,
                @EmpOccupation, @EmpYearlyIncome, @EmpSales

这里，我们使用 CLOSE 和 DEALLOCATE 语句来关闭和取消分配游标。

CLOSE dynamic_employee_cursor
DEALLOCATE dynamic_employee_cursor
Dynamic Cursor in SQL Server 2

请使用以下 SQL 查询来检查游标是否已更新 Employee 表中的记录。

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

从下面的屏幕截图可以看到，游标已更新了收入和销售额。

Dynamic Cursor 3

SQL Server 中的动态游标用于 DELETE

在此示例中，我们将向您展示如何在动态游标中执行删除操作。

-- SQL Server Dynamic Cursor Example
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 dynamic_employee_cursor CURSOR 
DYNAMIC FOR 
	SELECT [ID]
	      ,[Name]
	      ,[Education]
	      ,[Occupation]
	      ,[YearlyIncome]
	      ,[Sales]
	FROM EmployeeTable
        ORDER BY Occupation, Education

OPEN dynamic_employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
      FETCH NEXT FROM dynamic_employee_cursor
            INTO @EmpID, @EmpName, @EmpEducation,
	         @EmpOccupation, @EmpYearlyIncome, @EmpSales
      WHILE @@FETCH_STATUS = 0
      BEGIN
	IF @EmpOccupation = N'Management' OR @EmpEducation = N'Partial High School'
 	    DELETE FROM [EmployeeTable] 
	    WHERE CURRENT OF dynamic_employee_cursor                
		
        FETCH NEXT FROM dynamic_employee_cursor 
             INTO @EmpID, @EmpName, @EmpEducation,
	          @EmpOccupation, @EmpYearlyIncome, @EmpSales
      END
END
CLOSE dynamic_employee_cursor
DEALLOCATE dynamic_employee_cursor
SET NOCOUNT OFF 
GO

分析

在这个动态游标示例中，我们只更改了几行代码，这些代码是：

在循环内部，我们使用了另一个 IF 语句来检查 Occupation 是否等于 Management 或 Education = Partial High School。

IF @EmpOccupation = N'Management' OR @EmpEducation = N'Partial High School'

如果条件为 TRUE，动态游标将使用 DELETE 语句来删除该记录。

DELETE FROM [EmployeeTable] 
WHERE CURRENT OF dynamic_employee_cursor
Dynamic Cursor in SQL Server 4

我们来看 Employee 表。

请参阅 SQL Server 中的静态游标、INSERT 语句、DELETE 语句和 UPDATE 语句操作。

评论已关闭。