游标与更新 [英] CURSOR vs. UPDATE

问题描述

公司使用SQL Server数据库存储有关其客户及其业务交易的信息。为您所在的城市引入了新的区号。前缀小于500的电话号码的区域代码111保持不变。前缀大于等于500的电话号码的区域代码将分配为222。客户表的电话列中的所有电话号码都存储为char（12）字符串，格式如下， 999-999-9999。我必须使用最少的管理工作来尽快对客户表

A company uses a SQL Server database to store information about its customers and its business transactions. A new area code has been introduced for your city. The area code 111 remains the same for telephone numbers with prefixes that are less than 500. The numbers with prefixes that are 500 and greater will be assigned an area code of 222. All telephone numbers in the Phone column in the Customers table are stored as char(12) strings of the following format, ‘999-999-9999’. i must make the appropriate changes to the Customers table

进行适当的更改。我应该使用哪一个？

as quickly as possible using the least administrative effort. Which one should I use ?

a。

UPDATE Customers SET Phone = ‘222-‘ + SUBSTRING(Phone,5,8) 
FROM Customers WHERE SUBSTRING(Phone,1,3) = ‘111’ 
AND SUBSTRING(Phone,5,3) >= 500 

b。

DECLARE PhoneCursor CURSOR FOR 
   SELECT Phone FROM Customers 
   WHERE SUBSTRING(Phone,1,3) = 111 
     AND SUBSTRING(Phone,5,3) >= 500 

OPEN PhoneCursor 
FETCH NEXT FROM PhoneCursor 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    UPDATE Customers 
    SET Phone = ‘222’ + SUBSTRING(Phone,5,8) 
    WHERE CURRENT OF PhoneCursor 

    FETCH NEXT FROM PhoneCursor 
END 

CLOSE PhoneCursor 
DEALLOCATE PhoneCursor 

推荐答案

重大更新将使数据库中的事务保持很长时间……可能将其锁定并造成各种破坏。

The big update will hold a transaction against the database for, potentially, a long time... locking things up and causing all kinds of havoc.

为此，我建议使用游标将负载分散一段时间。

For this, I would recommend a cursor to spread that load out over a period of time.

我还做了一个块状更新。 ..类似这样的东西：

I've also done a 'chunked' update... something like this:

DECLARE @Done bit = 0
WHILE @Done = 0
BEGIN
    UPDATE TOP(10000)
        Customers SET Phone = ‘222-‘ + SUBSTRING(Phone,5,8) 
    FROM Customers WHERE SUBSTRING(Phone,1,3) = ‘111’ 
    AND SUBSTRING(Phone,5,3) >= 500 

    IF @@ROWCOUNT = 0
    BEGIN
        SET @Done = 1
    END
END

这篇关于游标与更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！