数据库游标复习(MSSQL用内置的pubs做纯SQL的报表)

今天复习了下游标 流程控制语句 SQL查询等基础语法 做了一个纯SQL报表 附图：

 代码如下：用了一个游标的嵌套呵呵

declare @jobid smallint ,@jobdesc varchar(50)
declare @name varchar(50),@joblvl smallint ,@hdate smalldatetime

declare cur_job cursor for
  select job_id,job_desc from jobs order by job_id asc

open cur_job
fetch next from cur_job into @jobid,@jobdesc

while @@fetch_status=0
begin 
 --显示当前的工作编号和工作描述
 print cast(@jobid as varchar(4))+';'+@jobdesc
 declare cur_emp cursor for
   select fname +' '+lname,job_lvl,hire_date 
    from employee
   where job_id=@jobid
open cur_emp
 fetch next from cur_emp into @name ,@joblvl,@hdate
 while @@fetch_status=0
   begin 
	print '雇员:'+@name +',级别：'+cast(@joblvl as varchar(4))+',参加工作日期：'+datename(year,@hdate)+'年'+
        datename(mm,@hdate)+'月'+datename(day,@hdate)+'日'
        fetch next from cur_emp into @name ,@joblvl,@hdate
   end
  print'================================================================================='
  close cur_emp
  deallocate cur_emp
  fetch next from cur_job into @jobid,@jobdesc
end
close cur_job
deallocate cur_job
go