游标

--cursor游标:
--特点：能逐行处理记录但速度较慢
-
--定义
declare cursor_name cursor for select * from zhigong2
--
open cursor_name
--取过来放到变量
fetch into @
--close cursor
--分配空间收回来deallocate
create table ji16142
(first1 varchar(100))
bulk insert ji16142
from'F:\导入.txt'
with (fieldterminator=' ',rowterminator='\n')
select * from ji16142
--添加字段：
alter table ji16142
add jins varchar(20)
--
select * ss
--取前三个字符
update ji16142 set jnum=substring(first1,1,1)
update ji16142 set jins=substring(first1,4,10)
update ji16142 set jcls=substring(first1,13,7)
update ji16142 set jnam=substring(first1,19,4)
select * from ji16142
--
select * from ji16142
--
declare @bj varchar(20),@jn varchar(10),@fs varchar(10)
--declare cji cursor for 
declare set @i=1
declare cji cursor scroll for
select first,jcls,jnam from ji16142
open cji
--
set @i=rand()*163+1
set @i=31
fetch from cji into @bj,@jn,@fs
select @fs
l
while @@FETCH_STATUS=0
begin
if @i<=9
--
print @bj+' '+@jn
--print len(@fs)
--select len(@fs)


update ji16142 set jnam=SUBSTRING(@fs,36 ,3 )
where current of cji
--fetch from cji into @bj,@jn
if @i<=99
--fetch from cji into @bj,@jn
--fetch from cji into @bj,@jn
update ji16142 set jnam=SUBSTRING(@fs,36 ,3 )
where current of cji
if @i>99
--fetch from cji into @bj,@jn
--fetch from cji into @bj,@jn
update ji16142 set jnam=SUBSTRING(@fs,36 ,3 )
where current of cji


--print @bj+@jn
select len(@fs)
end
close cji
deallocate cji
select * from ji16142
update ji16142 set jnam=LEFT(jnam,3)
--first 
--last 
--absolute
--relative
--prior
--定义游标：