一个带游标的存储过程例子

--创建存储过程 CopyBaseDict_T  
IF EXISTS(SELECT * FROM sys.objects WHERE type='p' AND name='CopyBaseDict_T')    
DROP PROC CopyBaseDict_T  
GO 
CREATE PROC [dbo].[CopyBaseDict_T]
@CLASSID int,
@CLASSNAME varchar(50),
@CLASSCODE varchar(50)
AS     
DECLARE @EXISTSCLASSNAME varchar(50);
--如果欲增加的检查类型名称已经存在则退出存储过程
    select @EXISTSCLASSNAME = CLASSNAME from DICT_CLASS where CLASSNAME = @CLASSNAME;
if (@EXISTSCLASSNAME = @CLASSNAME) return;


print '1';

DECLARE @MAXClassID int,@MAXOrganID int,@MAXOrganModeID int,@MAXDISEASEID int,@MAXDIAGTEMPLATEID int;
select @MAXClassID = max(CLASSID)+1 from DICT_CLASS;
select @MAXOrganID = max(ORGANID)+1 from DICT_ORGAN;
select @MAXOrganModeID = max(ORGANMODEID)+1 from DICT_ORGANMODE;
select @MAXDISEASEID = max(DISEASEID)+1 from DICT_DISEASE;
select @MAXDIAGTEMPLATEID = max(DIAGTEMPLATEID)+1 from DICT_DIAGTEMPLATE;


----这里插入DICT_CLASS表数据
insert into DICT_CLASS(CLASSID,CLASSNAME,MAXNO,CODE,APPLY,UNIFORMNO,PREFIXNO,DEPARTMENTNO,DEPARTTYPECODE)
     select @MAXClassID,@CLASSNAME,MAXNO,@CLASSCODE,APPLY,UNIFORMNO,PREFIXNO,DEPARTMENTNO,DEPARTTYPECODE 
   from DICT_CLASS 
  where CLASSID = @CLASSID;
print '插入DICT_CLASS表数据';
DECLARE @OrganID int,@NAME varchar(50),@CODE varchar(25),@FREQUENCY int     
--声明一个cursor_DICT_ORGAN游标     
DECLARE cursor_DICT_ORGAN CURSOR FOR
SELECT Organid,NAME,CODE,FREQUENCY
FROM DICT_ORGAN where CLASSID = @CLASSID
--打开游标
OPEN cursor_DICT_ORGAN     
--提取游标第一行     
FETCH NEXT FROM cursor_DICT_ORGAN INTO @OrganID,@NAME,@CODE,@FREQUENCY
--循环提取游标内容     
WHILE @@FETCH_STATUS=0     
BEGIN


----这里插入DICT_ORGAN表数据
insert into DICT_ORGAN(ORGANID,CLASSID,NAME,CODE,FREQUENCY)
    values(@MAXOrganID,@MAXClassID,@NAME,@CODE,@FREQUENCY);

print '插入DICT_ORGAN表数据';
--------------DICT_ORGANMODE 嵌套---------------------begin
DECLARE @ORGANMODEID int,@DIRECTION varchar(80),@DIRECTIONCODE varchar(100),@ROOMNO varchar(20),
       @FREQUENCY2 int,@EXPOSALTIMES float,@DEFAULTWIDTH varchar(20),@DEFAULTLEVEL varchar(20),
@WORKLISTCODE varchar(20),@ASSESCORE float
--声明一个cursor_DICT_ORGANMODE游标     
DECLARE cursor_DICT_ORGANMODE CURSOR FOR
SELECT DIRECTION,DIRECTIONCODE,ROOMNO,FREQUENCY,EXPOSALTIMES,DEFAULTWIDTH,DEFAULTLEVEL,WORKLISTCODE,ASSESCORE
FROM DICT_ORGANMODE where ORGANID = @OrganID
--打开游标
OPEN cursor_DICT_ORGANMODE     
--提取游标第一行     
FETCH NEXT FROM cursor_DICT_ORGANMODE INTO @DIRECTION,@DIRECTIONCODE,@ROOMNO,@FREQUENCY2,@EXPOSALTIMES,@DEFAULTWIDTH,@DEFAULTLEVEL,@WORKLISTCODE,@ASSESCORE     
--循环提取游标内容     
WHILE @@FETCH_STATUS=0     
BEGIN


----这里插入DICT_ORGANMODE表数据
insert into DICT_ORGANMODE(ORGANMODEID,ORGANID,DIRECTION,DIRECTIONCODE,ROOMNO,FREQUENCY,EXPOSALTIMES,DEFAULTWIDTH,DEFAULTLEVEL,WORKLISTCODE,ASSESCORE)
        values(@MAXOrganModeID,@MAXOrganID, @DIRECTION,@DIRECTIONCODE,@ROOMNO,@FREQUENCY2,@EXPOSALTIMES,@DEFAULTWIDTH,@DEFAULTLEVEL,@WORKLISTCODE,@ASSESCORE);
print '插入DICT_ORGANMODE表数据';
set @MAXOrganModeID = @MAXOrganModeID+1;
FETCH NEXT FROM cursor_DICT_ORGANMODE 
INTO @DIRECTION,@DIRECTIONCODE,@ROOMNO,@FREQUENCY2,@EXPOSALTIMES,@DEFAULTWIDTH,@DEFAULTLEVEL,@WORKLISTCODE,@ASSESCORE   
END     
--关闭游标     
CLOSE cursor_DICT_ORGANMODE     
--释放游标资源     
DEALLOCATE cursor_DICT_ORGANMODE 
--------------DICT_ORGANMODE 嵌套---------------------end


--------------DICT_DISEASE 嵌套---------------------begin
DECLARE @DISEASEID int, @DISEASENAME varchar(100),@DISEASECODE varchar(100),@FREQUENCY3 varchar(6),@ISMASCULINE varchar(2),@DISEASEORDER int
--声明一个cursor_DICT_DISEASE 游标     
DECLARE cursor_DICT_DISEASE CURSOR FOR
SELECT DISEASEID, NAME,CODE,FREQUENCY,ISMASCULINE,DISEASEORDER
FROM DICT_DISEASE where ORGANID = @OrganID
--打开游标
OPEN cursor_DICT_DISEASE     
--提取游标第一行     
FETCH NEXT FROM cursor_DICT_DISEASE INTO @DISEASEID, @DISEASENAME ,@DISEASECODE,@FREQUENCY3,@ISMASCULINE,@DISEASEORDER     
--循环提取游标内容     
WHILE @@FETCH_STATUS=0     
BEGIN
   ----这里插入DICT_DISEASE表数据  
insert into DICT_DISEASE(DISEASEID,ORGANID,ORGANMODEID,NAME,CODE,FREQUENCY,ISMASCULINE,DISEASEORDER)
     values(@MAXDISEASEID,@MAXOrganID,null,@DISEASENAME ,@DISEASECODE,@FREQUENCY3,@ISMASCULINE,@DISEASEORDER);
print '插入DICT_DISEASE表数据';
--------------DICT_DIAGTEMPLATE 嵌套---------------------begin
DECLARE @FREQUENCY4 numeric(10,0),@DIAGTEMPLATENAME varchar(50),@TITLEONE varchar(20),
       @CONTENTONE varchar(max),@TITLETWO varchar(20),@CONTENTTWO varchar(max),@TITLETHREE varchar(20),
@CONTENTTHREE varchar(max),@TITLEFOUR varchar(20),@CONTENTFOUR varchar(max),@TITLEFIVE varchar(20),
@CONTENTFIVE varchar(max),@TITLESIX varchar(20),@CONTENTSIX varchar(max),@TITLESEVEN varchar(20),
@CONTENTSEVEN varchar(max),@TITLEALL varchar(20),@LAYOUTOFBODY varchar(20),
@DISIMGTIMES varchar(20),@SCANFORMULA varchar(20),@TURNTOBIGS varchar(20)
--声明一个cursor_DICT_DIAGTEMPLATE 游标     
DECLARE cursor_DICT_DIAGTEMPLATE CURSOR FOR
SELECT FREQUENCY,DIAGTEMPLATENAME,TITLEONE,CONTENTONE,TITLETWO,CONTENTTWO,TITLETHREE,CONTENTTHREE,TITLEFOUR,CONTENTFOUR,TITLEFIVE,CONTENTFIVE,TITLESIX,CONTENTSIX,TITLESEVEN,CONTENTSEVEN,TITLEALL,LAYOUTOFBODY,DISIMGTIMES,SCANFORMULA,TURNTOBIGS
FROM DICT_DIAGTEMPLATE where DISEASEID = @DISEASEID
--打开游标
OPEN cursor_DICT_DIAGTEMPLATE     
--提取游标第一行     
FETCH NEXT FROM cursor_DICT_DIAGTEMPLATE INTO @FREQUENCY4,@DIAGTEMPLATENAME,@TITLEONE,@CONTENTONE,@TITLETWO,@CONTENTTWO,@TITLETHREE,@CONTENTTHREE,@TITLEFOUR,@CONTENTFOUR,@TITLEFIVE,@CONTENTFIVE,@TITLESIX,@CONTENTSIX,@TITLESEVEN,@CONTENTSEVEN,@TITLEALL,@LAYOUTOFBODY,@DISIMGTIMES,@SCANFORMULA,@TURNTOBIGS     
--循环提取游标内容     
WHILE @@FETCH_STATUS=0     
BEGIN


----这里插入DICT_DIAGTEMPLATE表数据 
insert into  DICT_DIAGTEMPLATE(DIAGTEMPLATEID,DISEASEID, FREQUENCY,DIAGTEMPLATENAME,TITLEONE,
                               CONTENTONE,TITLETWO,CONTENTTWO,TITLETHREE,
CONTENTTHREE,TITLEFOUR,CONTENTFOUR,
TITLEFIVE,CONTENTFIVE,TITLESIX,CONTENTSIX,
TITLESEVEN,CONTENTSEVEN,TITLEALL,
LAYOUTOFBODY,DISIMGTIMES,SCANFORMULA,TURNTOBIGS)
values(@MAXDIAGTEMPLATEID,@MAXDISEASEID,@FREQUENCY4,@DIAGTEMPLATENAME,@TITLEONE,
      @CONTENTONE,@TITLETWO,@CONTENTTWO,@TITLETHREE,
  @CONTENTTHREE,@TITLEFOUR,@CONTENTFOUR,
  @TITLEFIVE,@CONTENTFIVE,@TITLESIX,@CONTENTSIX,
  @TITLESEVEN,@CONTENTSEVEN,@TITLEALL,
  @LAYOUTOFBODY,@DISIMGTIMES,@SCANFORMULA,@TURNTOBIGS);
print '插入DICT_DIAGTEMPLATE表数据';
set @MAXDIAGTEMPLATEID = @MAXDIAGTEMPLATEID+1; 
FETCH NEXT FROM cursor_DICT_DIAGTEMPLATE 
INTO @FREQUENCY4,@DIAGTEMPLATENAME,@TITLEONE,@CONTENTONE,@TITLETWO,@CONTENTTWO,@TITLETHREE,@CONTENTTHREE,@TITLEFOUR,@CONTENTFOUR,@TITLEFIVE,@CONTENTFIVE,@TITLESIX,@CONTENTSIX,@TITLESEVEN,@CONTENTSEVEN,@TITLEALL,@LAYOUTOFBODY,@DISIMGTIMES,@SCANFORMULA,@TURNTOBIGS     
END     
--关闭游标     
CLOSE cursor_DICT_DIAGTEMPLATE     
--释放游标资源     
DEALLOCATE cursor_DICT_DIAGTEMPLATE 
--------------DICT_DIAGTEMPLATE 嵌套---------------------end
set @MAXDISEASEID = @MAXDISEASEID+1;
FETCH NEXT FROM cursor_DICT_DISEASE 
INTO @DISEASEID, @DISEASENAME ,@DISEASECODE,@FREQUENCY3,@ISMASCULINE,@DISEASEORDER     
END     
--关闭游标     
CLOSE cursor_DICT_DISEASE     
--释放游标资源     
DEALLOCATE cursor_DICT_DISEASE 
   --------------DICT_DISEASE 嵌套---------------------end
   
set @MAXOrganID = @MAXOrganID+1;


FETCH NEXT FROM cursor_DICT_ORGAN 
INTO @OrganID,@NAME,@CODE,@FREQUENCY     
END     
--关闭游标     
CLOSE cursor_DICT_ORGAN     
--释放游标资源     
DEALLOCATE cursor_DICT_ORGAN 


GO
--存储过程 CopyBaseDict_T 结束