游标
创建一游标，逐行显示Student表中的记录，要求按’学号’+’------’+’姓名’+’-------’+’性别’+’-------’+’所在系’+’--------’格式输出。
[最优解释]
--声明游标
declare text_Cursor cursor forward_only for
select ID,Grade,Course From Student
open text_Cursor
declare @ID int
declare @Grade int
declare @Course int
--循环读取数据记录
fetch next from text_Cursor into @ID,@Grade,@Course
while @@FETCH_STATUS=0
begin
print 'ID='+convert(nvarchar(10),@ID)+space(3)+'Grade='+convert(nvarchar(10),@Grade)+space(3)+'Course='+convert(nvarchar(10),@Course)
fetch next from text_Cursor into @ID,@Grade,@Course
end
--关闭、释放游标
close text_Cursor
deallocate text_Cursor
--测试数据准备
if(object_id('t1') is not null)drop table t1
CREATE table t1(
id int identity(1,1) primary key,
value nvarchar(20)
)
go
--插入测试数据
insert into t1(value)
select '值1'union all
select '值2'union all
select '值3'union all
select '值4'
--查看结果集合
--select * from t1
if(OBJECT_ID('p_print')is not null) drop procedure p_print
go
create procedure p_print
as
begin
declare @value nvarchar(20)--注意这里的变量类型应该与游标中读取出来的字段类型相同
--创建游标
declare cur1 cursor for
select value from t1
--打开游标
open cur1
fetch next from cur1 into @value--这里的@value对应游标每条记录中的字段value的值
while(@@FETCH_STATUS = 0)
begin
print 'value:'+@value
fetch next from cur1 into @value
end
--关闭游标
close cur1
--释放游标
DEALLOCATE cur1
end
--调用(去注释调用)
--exec p_print
/* 执行结果
value:值1
value:值2
value:值3
value:值4
*/
select cast(学号 as int)+’------'+cast(姓名 as int)+'------'+cast(性别 as int) from student
declare cur cursor for
select rtrim(学号)+'------'+姓名+'-------'+性别'-------'+所在系+'--------' FROM 表名
declare @Out varchar(500)
open cur
fetch next from cur into @Out
while @@FETCH_STATUS=0
begin
print @Out
fetch next from cur into @Out
end
close cur
deallocate cur
select cast(学号 as int)+'------'+cast(姓名 as int)+'------'+cast(性别 as int) from student
while @@FETCH_STATUS=0
begin
select cast(Sno as int)+'------'+cast(Sname as int)+'------'+cast(Ssex as int) --声明游标
declare text0_Cursor cursor forward_only for
select * From Student
open text0_Cursor
declare @Sno char(9),@Sname char(10),@Ssex char(2),@Sage smallint,@Sdept char(20)
--循环读取数据记录
fetch next from text0_Cursor into @Sno,@Sname,@Ssex,@Sage,@Sdept
print 'Sno'+'------'+ 'Sname' +'------'+ 'Ssex '+'------'+ 'Sage' +'------'+ 'Sdept' +'------'
while @@FETCH_STATUS=0
begin
select cast(Sno as int)+'------'+cast(Sname as int)+'------'+cast(Ssex as int) --声明游标
declare text0_Cursor cursor forward_only for
select * From Student
open text0_Cursor
declare @Sno char(9),@Sname char(10),@Ssex char(2),@Sage smallint,@Sdept char(20)
--循环读取数据记录
fetch next from text0_Cursor into @Sno,@Sname,@Ssex,@Sage,@Sdept
print 'Sno'+'------'+ 'Sname' +'------'+ 'Ssex '+'------'+ 'Sage' +'------'+ 'Sdept' +'------'
while @@FETCH_STATUS=0
begin
select cast(Sno as int)+'------'+cast(Sname as int)+'------'+cast(Ssex as int)+cast(Sage as int)+'------'+cast(Sdept as int)+'------' from student
fetch next from text0_Cursor into @Sno,@Sname,@Ssex,@Sage,@Sdept
end
--关闭、释放游标
close text0_Cursor
deallocate text0_Cursor from student
fetch next from text0_Cursor into @Sno,@Sname,@Ssex,@Sage,@Sdept
end
--关闭、释放游标
close text0_Cursor
deallocate text0_Cursor from student
fetch next from text0_Cursor into @Sno,@Sname,@Ssex,@Sage,@Sdept
end
--关闭、释放游标
close text0_Cursor
deallocate text0_Cursorcast(Ssex as int) from student
fetch next from text0_Cursor into @Sno,@Sname,@Ssex,@Sage,@Sdept
end
--关闭、释放游标
close text0_Cursor
deallocate text0_Cursor
此代码老是Sno------Sname------Ssex ------Sage------Sdept------
而不显示结果，这是为什么啊
[其他解释]
