UPDATE and DELETE Statements Positioned UPDATE and DELETE statements are used in conjunction with browse cursors and include WHERE CURRENT OF clauses instead of search condition clauses. The WHERE CURRENT OF clause specifies the location of the corresponding cursor. Before a cursor can be used by a positioned UPDATE or DELETE statement, the SELECT statement in the cursor declaration must contain the FOR BROWSE option. (The Microsoft® SQL Server™ 2000 FOR BROWSE option is similar to the FOR UPDATE option in other SQL databases, but you must use SQL Server syntax.) To use the FOR BROWSE option, the table must have both a unique index and a timestamp column. When performing a positioned UPDATE or DELETE statement, a method called optimistic concurrency control helps prevent conflicts with other users. Optimistic concurrency control allows users to share data with less interference than they would experience with locking, which is the alternative concurrency control method. Although optimistic concurrency control minimizes the likelihood of conflicts with other users, write your application so that it can handle updates to tables that are rejected due to locking conflicts or other problems. Use the SQLCODE field in the SQL communications area (SQLCA) data structure to detect conflicts with other users. (A SQLCODE value of -532 means the positioned UPDATE or DELETE statement failed because of a conflict with another user.) For more information about the SQLCODE field, see Using the SQLCA Data Structure. For more information about browse-mode processing, see Advanced Programming. A positioned update can be performed twice on the same row. To do this, use the FETCH statement to obtain the row, begin a transaction by using BEGIN TRANSACTION, and update a nonkey column to itself. This locks the row and prevents other users from reading or updating it until a COMMIT TRANSACTION statement is issued.这是sql server关于FOR UPDATE语法的说明(我用的是中文版,这一段竟然是英文的,晕倒)
--简单的说,就是--测试数据 create table #t(a int) insert #t select 1 union all select 2 union all select 3--游标处理 declare @a int declare tb cursor --read_only --如果用这个,不要下面那个,定义成只读游标,则更新失败 local for select a from #t for update --定义为可更新游标open tb fetch tb into @a while @@fetch_status=0 begin if @a=2 delete #t where current of tb else update #t set a=a+@a where current of tb fetch tb into @a end close tb deallocate tb--显示结果 select * from #t go--删除测试 drop table #t/*--测试结果a ----------- 2 6(所影响的行数为 2 行) --*/
升到什么程序呢?现在总分多少了? ------------------------------------ 《CSDN论坛新助手 CSDN's forum Explorer》 1、更快速的浏览 2、更方便地保存 3、更快捷的接收短信 下载地址:http://www.seeyou.com.cn/CoolSlob/CSDNExplorer.exe
支持一下我刚升级的软件吧:) ------------------------------------ 《CSDN论坛新助手 CSDN's forum Explorer》 1、更快速的浏览 2、更方便地保存 3、更快捷的接收短信 下载地址:http://www.seeyou.com.cn/CoolSlob/CSDNExplorer.exe
仅用在UPDATE类型游标声明当中 FOR UPDATE 首先通知SQL采用optimistic concurrency control 技术 其次执行时在用户UPDATE 的同时锁定行,防止其它用户读和写入
DECLARE @a1 varchar(20) ,@a2 varchar(10)DECLARE Tcur CURSOR FOR SELECT top 10 pluno,pluname FROM basplumain FOR UPDATE of plunoDECLARE @count smallint SELECT @count = 1OPEN Tcur FETCH NEXT FROM Tcur into @a1,@a2WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT 'updating record of ' + @a1 + ' ' + @a2 UPDATE basplumain SET pluname = @a1 + '-' + CAST(@count AS varchar(4)) WHERE pluname = @a2 END FETCH NEXT FROM Tcur INTO @a1,@a2 SELECT @count = @count + 1 ENDCLOSE Tcur DEALLOCATE Tcur GO
Positioned UPDATE and DELETE statements are used in conjunction with browse cursors and include WHERE CURRENT OF clauses instead of search condition clauses. The WHERE CURRENT OF clause specifies the location of the corresponding cursor. Before a cursor can be used by a positioned UPDATE or DELETE statement, the SELECT statement in the cursor declaration must contain the FOR BROWSE option. (The Microsoft® SQL Server™ 2000 FOR BROWSE option is similar to the FOR UPDATE option in other SQL databases, but you must use SQL Server syntax.) To use the FOR BROWSE option, the table must have both a unique index and a timestamp column. When performing a positioned UPDATE or DELETE statement, a method called optimistic concurrency control helps prevent conflicts with other users. Optimistic concurrency control allows users to share data with less interference than they would experience with locking, which is the alternative concurrency control method. Although optimistic concurrency control minimizes the likelihood of conflicts with other users, write your application so that it can handle updates to tables that are rejected due to locking conflicts or other problems. Use the SQLCODE field in the SQL communications area (SQLCA) data structure to detect conflicts with other users. (A SQLCODE value of -532 means the positioned UPDATE or DELETE statement failed because of a conflict with another user.) For more information about the SQLCODE field, see Using the SQLCA Data Structure. For more information about browse-mode processing, see Advanced Programming. A positioned update can be performed twice on the same row. To do this, use the FETCH statement to obtain the row, begin a transaction by using BEGIN TRANSACTION, and update a nonkey column to itself. This locks the row and prevents other users from reading or updating it until a COMMIT TRANSACTION statement is issued.这是sql server关于FOR UPDATE语法的说明(我用的是中文版,这一段竟然是英文的,晕倒)
------------------------------------ 《CSDN论坛新助手 CSDN's forum Explorer》
1、更快速的浏览
2、更方便地保存
3、更快捷的接收短信
下载地址:http://www.seeyou.com.cn/CoolSlob/CSDNExplorer.exe
create table #t(a int)
insert #t select 1
union all select 2
union all select 3--游标处理
declare @a int
declare tb cursor --read_only --如果用这个,不要下面那个,定义成只读游标,则更新失败
local for
select a from #t for update --定义为可更新游标open tb
fetch tb into @a
while @@fetch_status=0
begin
if @a=2
delete #t where current of tb
else
update #t set a=a+@a where current of tb
fetch tb into @a
end
close tb
deallocate tb--显示结果
select * from #t
go--删除测试
drop table #t/*--测试结果a
-----------
2
6(所影响的行数为 2 行)
--*/
------------------------------------ 《CSDN论坛新助手 CSDN's forum Explorer》
1、更快速的浏览
2、更方便地保存
3、更快捷的接收短信
下载地址:http://www.seeyou.com.cn/CoolSlob/CSDNExplorer.exe
------------------------------------ 《CSDN论坛新助手 CSDN's forum Explorer》
1、更快速的浏览
2、更方便地保存
3、更快捷的接收短信
下载地址:http://www.seeyou.com.cn/CoolSlob/CSDNExplorer.exe
------------------------------------ 《CSDN论坛新助手 CSDN's forum Explorer》
1、更快速的浏览
2、更方便地保存
3、更快捷的接收短信
下载地址:http://www.seeyou.com.cn/CoolSlob/CSDNExplorer.exe
FOR UPDATE
首先通知SQL采用optimistic concurrency control 技术
其次执行时在用户UPDATE 的同时锁定行,防止其它用户读和写入
FOR SELECT top 10 pluno,pluname FROM basplumain
FOR UPDATE of plunoDECLARE @count smallint
SELECT @count = 1OPEN Tcur
FETCH NEXT FROM Tcur into @a1,@a2WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN PRINT 'updating record of ' + @a1 + ' ' + @a2
UPDATE basplumain
SET pluname = @a1 + '-' + CAST(@count AS varchar(4))
WHERE pluname = @a2
END
FETCH NEXT FROM Tcur INTO @a1,@a2
SELECT @count = @count + 1
ENDCLOSE Tcur
DEALLOCATE Tcur
GO
对结果集加锁,在sqlserver游标中也有吗?