考试(三)

       创建一个表有三个字段：st_name，st_no，st_age ，插入100条记录（0~99），利用游标生成100张表：A00~A99

版本一：

--第一步
create table guanli(st_name varchar(20),st_no varchar(20),st_age varchar(20))
declare @i int,@j varchar(20),@k varchar(20)
set @i =0
while @i<100
begin
insert into guanli values(@j,@i,@k)
set @i=@i+1
end
select * from guanli
delete guanli
drop table guanli
------------------------------------------------------------------------------
--第二步
declare @nm varchar(20),@i int
declare ur cursor for select st_no from guanli
open ur
fetch next from ur into @nm
set @i=10
while @i<100
begin
update guanli set st_no='A'+ cast(@i as char) where current of ur 
--第三步改为 while @i<10 ,,update guanli set st_no='A0'+cast(@i as char)where current of ur 再执行一遍
fetch next from ur into @nm
set @i=@i+1
end
close ur
deallocate ur

------------------------------------------------------------
--第四步
declare ur cursor for select st_no from guanli   
open ur
declare @nb varchar(100),@sq varchar(200)
fetch from ur into @nb   --循环前先fetch一下
while @@FETCH_STATUS=0 --游标读取下一条数据是否成功。
begin 
set @sq='create table '+@nb+'(C_language varchar(10),E_nglish varchar(10),D_atabase varchar(10))'
exec (@sq)
fetch  from ur into @nb
end
close ur
deallocate ur

版本二：

use compterschool
--
create table stud1
(st_name varchar(10),
st_no varchar(5),
st_age varchar(5))

declare @i int
set @i=101
while @i<=199
begin
	insert into stud1 values('莎拉',cast(@i as char),'18')
	set @i=@i+1
end
select * from stud1
delete stud1
--利用游标创建表
declare @n1o varchar(5), @tb_name varchar(5),@exewords varchar(100)
set @tb_name='' set @exewords=''
declare cs cursor for select st_no from stud1
open cs
fetch from cs into @n1o
while @@FETCH_STATUS=0
begin
	set @tb_name='A'+@n1o
	set @exewords='create table '+@tb_name+'
	(C_language varchar(10),E_nglish varchar(10),D_atebase varchar(10))'
	exec(@exewords)
	fetch from cs into @n1o
end
close cs
deallocate cs

版本三：

create table student1
(st_name varchar(10),
st_no int,
st_age int)
declare @ii int,@an varchar(10),@age int 
set @ii=0
while @ii<100
begin 
	insert into student1 values(char(rand()*26+65),@ii,rand()*80)
	set @ii=@ii+1
	end
select * from student1
--
declare @no varchar(10)
declare cr cursor for
select st_no from student1
open cr
fetch next from cr into @no
while @@FETCH_STATUS=0
begin
	
	declare @nn varchar(500)
	set @nn='create table A'+ @no+'
	(
		C_language varchar(20),
		E_nglish varchar(20),
		D_atabase varchar(20),
	)'
	exec (@nn)
	fetch next from cr into @no
end
close cr
deallocate cr