(游标是单条读取,循环是遍历..)
最近需要对数量比较大的数据进行处理;自然少不了记录集循环的操作,在参考了网上的资料以后总结如下。
当需要对很多的记录进行操作的时候,采用脚本语言(例如asp or asp.net)进行操作时。如果数据量少可能看不出用上面这种办法实现的弱点,因为它每次在操作数据库的时候,都存在着频繁的和数据库的I/O直接交互,这点性能的牺牲实属不应该,那我们就看下面的方法,通过存储过程的游标方法来实现:
建立存储过程(来自网络)
- Create PROCEDURE P_InsertSubject
- @SubjectId int
- AS
- DECLARE rs CURSOR LOCAL SCROLL FOR
- select studentid from student where StudentGradu = 1
- OPEN rs
- FETCH NEXT FROM rs INTO @tempStudentID
- WHILE @@FETCH_STATUS = 0
- BEGIN
- Insert SelSubject values (@SubjectId,@tempStudentID)
- FETCH NEXT FROM rs INTO @tempStudentID
- END
- CLOSE rs
- GO
使用游标对记录集循环进行处理的时候一般操作如以下几个步骤:
1、把记录集传给游标;
2、打开游标
3、开始循环
4、从游标中取值
5、检查那一行被返回
6、处理
7、关闭循环
8、关闭游标
--------------------------------------
上面这种方法在性能上面无疑已经是提高很多了,但我们也想到,在存储过程编写的时候,有时候我们尽量少的避免使用游标来进行操作,所以我们还可以对上面的存储过程进行改造,使用下面的方法来实现:
- Create PROCEDURE P_InsertSubject
- @SubjectId int
- AS
- declare @i int,
- @studentid
- DECLARE @tCanStudent TABLE
- (
- studentid int
- ,FlagID TINYINT
- )
- BEGIN
- insert @tCanStudent select studentid,0 from student where StudentGradu = 1
- SET @i=1
- WHILE( @i>=1)
- BEGIN
- SELECT @studentid=''
- SELECT TOP 1 @studentid = studentid FROM @tCanStudent WHERE flagID=0
- SET @i=@@ROWCOUNT
- IF @i<=0 GOTO Return_Lab
- Insert SelSubject values (@SubjectId,@studentid)
- IF @@error=0
- UPDATE @tCanStudent SET flagID=1 WHERE studentid = @studentid
- Return_Lab:
- END
- End
- GO
我们现在再来分析以上这个存储过程,它实现的方法是先把满足条件的记录集数据存放到一个表变量中,并且在这个表变量中增加一个FLAGID进行数据初始值为0的存放,然后去循环这个记录集,每循环一次,就把对应的FLAGID的值改成1,然后再根据循环来查找满足条件等于0的情况,可以看到,每循环一次,处理的记录集就会少一次,然后循环的往选好课程表里面插入,直到记录集的条数为0时停止循环,此时完成操作。
-------------------------------------------------
示例:对比一下吧,先执行创建表格与初始化数据的脚本,然后再依次执行游标操作和普通sql的操作的执行效率。
- SQL code
-
-- 创建表格与初始化数据 create table testTB( tid int not null identity, tname varchar(36) ) declare @flag int set @flag = 10000 while @flag > 0 begin insert into testTB(tname) values(newid()) set @flag = @flag -1 end --========= 游标循环操作 =========-- declare @fl_field nvarchar(36) declare @id int -- 定义游标 declare Temp_Table cursor for select tid, tname from testTB -- 查询结果 -- 打开游标 取第一行记录 赋给@fl_field open Temp_Table fetch next from Temp_Table into @id, @fl_field -- 循环开始 while @@fetch_status = 0 begin set @fl_field = newid() update testTB set tname=@fl_field where tid=@id -- 取下一条记录 fetch next from Temp_Table into @id, @fl_field end -- 循环结束 -- 关闭游标 删除游标引用 close Temp_Table deallocate Temp_Table --========= 游标循环操作 =========-- --========= 临时表的操作 =========-- -- 其实这里也不用放到临时表中,一句话就搞定了,可能这个例子过于简单,不过你可以测试上面游标操作的效率 update testTB set tname=newid() --========= 临时表的操作 =========--(临时表其实也有两种,一种是完全在内存里的,用declare @mytable table (id int, value varchar(10)) 的方式建立,另一种建在tempdb数据库里,用create table #mytable (id int, value varchar)来定义。)----------------------------------------------------------------------------
----------------------------------------------------------------------------
可以使用临时表加一个自增列
- SQL code:
-
CREATE TABLE #ForUserName( [ForID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL ) INSERT INTO #ForUserName([UserName]) SELECT ID from Users DECLARE @MaxForID int DECLARE @id INT SELECT @MaxForID=Max([ForID]) FROM #ForUserName WHILE(@ID<=@MaxForID) BEGIN UPDATE #ForUserName ...WHERE @id=ForID END----------------------------------------------------------------------------CTE:(也可以,顶级菜单使用:A000、A001、A002……,次级使用B000、B001……,三级使用C000、C001……)