Sql存储过程游标循环的用法及sql如何使用cursor写

DECLARE 
@A1 VARCHAR(10),
@A2 VARCHAR(10),
@A3 INT
DECLARE CURSOR YOUCURNAME FOR SELECT A1,A2,A3 FROM YOUTABLENAME
OPEN YOUCURNAME
fetch next from youcurname into @a1,@a2,@a3
while @@fetch_status<>-1
begin
update … set …-a3 where …

fetch next from youcurname into @a1,@a2,@a3
end
close youcurname
deallocate youcurname

Create PROCEDURE P_InsertSubject
@SubjectId int
AS
DECLARE rs CURSOR LOCAL SCROLL FOR
select studentid from student where StudentGradu = 1
OPEN rs
FETCH NEXT FROM rs INTO @tempStudentID
WHILE @@FETCH_STATUS = 0
BEGIN
Insert SelSubject values (@SubjectId,@tempStudentID)
FETCH NEXT FROM rs INTO @tempStudentID
END
CLOSE rs
GO

Create PROCEDURE P_InsertSubject
@SubjectId int
AS
declare @i int,
@studentid
DECLARE @tCanStudent TABLE
(
studentid int
,FlagID TINYINT
)
BEGIN
insert @tCanStudent select studentid,0 from student where StudentGradu = 1
SET @i=1
WHILE( @i>=1)
BEGIN
SELECT @studentid=''
SELECT TOP 1 @studentid = studentid FROM @tCanStudent WHERE flagID=0
SET @i=@@ROWCOUNT
IF @i<=0 GOTO Return_Lab
Insert SelSubject values (@SubjectId,@studentid)
IF @@error=0
UPDATE @tCanStudent SET flagID=1 WHERE studentid = @studentid
Return_Lab:
END
End
GO

WHILE Boolean_expression
  { sql_statement | statement_block }
  [ BREAK ]
  { sql_statement | statement_block }
  [ CONTINUE ]

declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end

while 条件
begin
执行操作
set @i=@i+1
end

USE pubs
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
  UPDATE titles
  SET price = price * 2
  SELECT MAX(price) FROM titles
  IF (SELECT MAX(price) FROM titles) > $50
  BREAK
  ELSE
  CONTINUE
END
PRINT 'Too much for the market to bear'

USE pubs
DECLARE tnames_cursor CURSOR
FOR
  SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF (@@FETCH_STATUS <> -2)
  BEGIN 
  SELECT @tablename = RTRIM(@tablename) 
  EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM ' 
    + @tablename )
  PRINT ' '
 END
  FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

CREATE  PROCEDURE my_FeeCount AS
declare @到期时间  char(10)
declare @片区   char(20)
declare @缴费用户数 char(10)
declare @sql char(2000)
declare cur_data cursor for 
 select convert(varchar(10),到期时间,120) as 到期时间 ,片区,count(distinct main_id) as 缴费用户数 
 from V_aipu_fee where 提交时间>=convert(varchar(10),getdate()-90,120) and 提交时间<convert(varchar(10),getdate()+1-90,120)
 and 收费类型='续费收费'
 Group by convert(varchar(10),到期时间,120),片区
 order by convert(varchar(10),到期时间,120) 
open cur_data
fetch next from cur_data into @到期时间,@片区,@缴费用户数 
while(@@fetch_status = 0) 
begin
  set @sql='update '+RTRIM(@片区)+'实收='+RTRIM(@片区)+'实收+'+RTRIM(@缴费用户数)+' where 收费日期='''+RTRIM(@到期时间)+''''
 print @sql
 fetch next from cur_data into @到期时间,@片区,@缴费用户数 
end
close cur_data

rights
　　-------------------
　　rightid int
　　right varchar(20)
　　
　　＝＝＝＝＝＝＝＝＝＝
　　
　　roles
　　-------------------
　　roleid int
　　roletype int
　　role varchar(20)
　　rightid int

create procedure TempFill
　　
　　declare mycursor cursor for
　　select rightid from rights--这两个是一句，定义游标，然后从rights中取一个rightid给游标
　　
　　open mycursor
　　
　　declare @rightid int--定义一个变量
　　fetch mycursor into @rightid--把mycursor当前的值给@rightid
　　
　　while @@fetch_status=
　　begin
　　insert into rolestable (roletype,role,rightid)values(,'admin',@rightid)
　　fetch mycursor into @rightid
　　end
　　
　　close mycursor
　　deallocate mycursor