8

Sql server存储过程中常见游标循环用法

 3 years ago
source link: https://www.cnblogs.com/tuyile006/p/5319117.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Sql server存储过程中常见游标循环用法

用游标，和WHILE可以遍历您的查询中的每一条记录并将要求的字段传给变量进行相应的处理

DECLARE 
@A1 VARCHAR(10),
@A2 VARCHAR(10),
@A3 INT
DECLARE YOUCURNAME CURSOR FOR SELECT A1,A2,A3 FROM YOUTABLENAME 
OPEN YOUCURNAME 
fetch next from youcurname into @a1,@a2,@a3 
while @@fetch_status<>-1
 begin 
--您要执行的操作写在这里 
fetch next from youcurname into @a1,@a2,@a3 
end 
close youcurname 
deallocate youcurname

再加上异常捕捉和事务，完整过程如下：

Create PROCEDURE  [dbo].[Usp_CreatePanicBuyingCode](
 @OrderNumber VARCHAR(50))
AS  
DECLARE 
@A1 VARCHAR(10),
@A2 VARCHAR(10),
@A3 INT
  begin try  
    begin tran 
   DECLARE youcurname CURSOR  FOR SELECT A1,A2,A3 FROM YOUTABLENAME
OPEN youcurname
fetch next from youcurname into @a1,@a2,@a3
while @@fetch_status<>-1
begin
--您要执行的操作写在这里
fetch next from youcurname into @a1,@a2,@a3
end
close youcurname
deallocate youcurname
COMMIT TRAN  
end try   
begin catch   
    ROLLBACK  
end catch

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK