SQL笔记09

游标

--声明游标
declare my_cursor cursor for select mname from major
declare @mname varchar(30)
--打开游标
open my_cursor
--取数据（循环）
fetch next from my_cursor into @mname
while @@FETCH_STATUS=0
begin
	select @mname as 'mname'
	fetch next from my_cursor into @mname
end
--关闭游标 或者 deallocate
deallocate my_cursor

select * from sc
--声明游标
declare my_cursor cursor for select cno,sno,grade from sc
declare @cno varchar(13)
declare @sno varchar(13)
declare @grade decimal
--打开游标
open my_cursor
--取数据（循环）
fetch next from my_cursor into @cno,@sno,@grade
while @@FETCH_STATUS=0
begin
	if @grade>=80
		update sc set sc_rank='A' where cno=@cno and sno=@sno
	else if @grade>=70
		update sc set sc_rank='B' where cno=@cno and sno=@sno
	else if @grade>=60
		update sc set sc_rank='C' where cno=@cno and sno=@sno
	fetch next from my_cursor into @cno,@sno,@grade
end
--关闭游标 或者 deallocate
deallocate my_cursor
select * from sc

select * from stu
select mname,sname from stu,major where stu.mno=major.mno
select sname,mname from stu left outer join major on stu.mno=major.mno

declare my_cursor cursor for select sname,mname from stu left outer join major on stu.mno=major.mno
declare @sname varchar(30)
declare @mname varchar(30)
open my_cursor
fetch next from my_cursor into @sname,@mname
while @@FETCH_STATUS=0
begin
	select @sname as 'sname',@mname as 'mname'
	fetch next from my_cursor into @sname,@mname
end
deallocate my_cursor