现在的位置: 首页 > 综合 > 正文

通过存储过程实现记录集的循环

2013年07月17日 ⁄ 综合 ⁄ 共 5959字 ⁄ 字号 评论关闭

(游标是单条读取,循环是遍历..) 

最近需要对数量比较大的数据进行处理;自然少不了记录集循环的操作,在参考了网上的资料以后总结如下。

当需要对很多的记录进行操作的时候,采用脚本语言(例如asp or asp.net)进行操作时。如果数据量少可能看不出用上面这种办法实现的弱点,因为它每次在操作数据库的时候,都存在着频繁的和数据库的I/O直接交互,这点性能的牺牲实属不应该,那我们就看下面的方法,通过存储过程的游标方法来实现:

建立存储过程(来自网络)

 

  1. Create PROCEDURE P_InsertSubject  
  2. @SubjectId int  
  3. AS  
  4.       
  5.  DECLARE rs CURSOR LOCAL SCROLL FOR  
  6.     select studentid from student where StudentGradu = 1  
  7.   
  8.  OPEN rs   
  9.  FETCH NEXT FROM rs INTO @tempStudentID  
  10.     WHILE @@FETCH_STATUS = 0  
  11.  BEGIN  
  12.  Insert SelSubject values (@SubjectId,@tempStudentID)   
  13.  FETCH NEXT FROM rs INTO @tempStudentID  
  14.  END  
  15.  CLOSE  rs   
  16. GO  

 

使用游标对记录集循环进行处理的时候一般操作如以下几个步骤:

  1、把记录集传给游标;

  2、打开游标

  3、开始循环

  4、从游标中取值

  5、检查那一行被返回

  6、处理

  7、关闭循环

  8、关闭游标

--------------------------------------

  上面这种方法在性能上面无疑已经是提高很多了,但我们也想到,在存储过程编写的时候,有时候我们尽量少的避免使用游标来进行操作,所以我们还可以对上面的存储过程进行改造,使用下面的方法来实现:

  1. Create PROCEDURE P_InsertSubject  
  2. @SubjectId int  
  3. AS  
  4.       
  5.  declare @i int,  
  6.    @studentid  
  7.   
  8.  DECLARE @tCanStudent TABLE  
  9.  (  
  10.   studentid  int  
  11.   ,FlagID   TINYINT    
  12.  )  
  13.   
  14. BEGIN  
  15.  insert @tCanStudent  select studentid,0 from student where StudentGradu = 1  
  16.  SET @i=1  
  17.  WHILE( @i>=1)  
  18.  BEGIN  
  19.     
  20.   SELECT @studentid=''  
  21.   
  22.   SELECT TOP 1 @studentid = studentid FROM @tCanStudent  WHERE flagID=0  
  23.   SET @i=@@ROWCOUNT  
  24.     
  25.   IF @i<=0 GOTO Return_Lab  
  26.      
  27.    Insert SelSubject values (@SubjectId,@studentid)   
  28.    IF @@error=0   
  29.       UPDATE @tCanStudent SET flagID=1 WHERE studentid = @studentid  
  30.  Return_Lab:  
  31.  END   
  32. End  
  33. 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……)

 

抱歉!评论已关闭.