sql游标

因为要根据很复杂的规则处理用户数据，所以这里用到数据库的游标。平时不怎么用这个，写在这里纯粹为自己备个忘。

 

 

--将学籍号重复的放入临时表 tmp_zdsoft_unitive_code(除高中学段外)
drop table tmp_zdsoft_unitive_code;
select s.id ,sch.school_code,sch.school_name,s.student_name,s.unitive_code,s.identity_card,
c.section,c.acadyear,c.class_name ,s.now_state
INTO tmp_zdsoft_unitive_code
from student_info as s ,basic_schoolinfo as sch
,basic_class as c where s.unitive_code in(
select unitive_code from student_info where len(unitive_code)>0 and isdeleted='0' group by  unitive_code having count(1)>1
) and c.section<>3 and s.isdeleted='0' and s.school_id=sch.id and s.class_id=c.id order by unitive_code,student_name

 

--建立临时表
drop table tmp_zdsoft_stuid;
create table tmp_zdsoft_stuid(id varchar(32) not null);
--利用三层游标，检索学籍号相同，姓名身份证都不同的学生，存放到临时表tmp_stuid
declare @unitive_code  varchar(30),
  @student_name varchar(60),
  @identity_card  varchar(18),
  @stuid  varchar(36)
--检索学籍号重复的学生---
declare tmp_cursor cursor for 
  select unitive_code,student_name,identity_card,id from tmp_zdsoft_unitive_code
  
OPEN tmp_cursor
FETCH next from tmp_cursor into @unitive_code,@student_name,@identity_card,@stuid
while @@fetch_status = 0
begin
 begin
 
--------------内层循环（检验姓名在同学籍号是否唯一）----------------------------
declare @count_name  int
    
declare tmp_cursor2 cursor for 
  select count(1) from tmp_zdsoft_unitive_code
  where unitive_code = @unitive_code and student_name=@student_name and (len(identity_card)=0 or isnull(identity_card,'')='')

OPEN tmp_cursor2
FETCH next from tmp_cursor2 into @count_name
while @@fetch_status = 0
begin
 begin
  if @count_name =1
  begin
   insert into tmp_zdsoft_stuid values(@stuid )
  end
  else
   break
 end
 FETCH next from tmp_cursor2 into @count_name 
end
CLOSE tmp_cursor2
DEALLOCATE tmp_cursor2
------------------------------------------
  
 end
 FETCH next from tmp_cursor into @unitive_code,@student_name,@identity_card,@stuid
end
CLOSE tmp_cursor
DEALLOCATE tmp_cursor