有关游标的使用
因为项目改动,需要把一段C#代码变成存储过程,然后代码里面有一个foreach,sql里面可没foreach啊,就用游标咯。
贴代码
DECLARE TrackingNumber_Cursor CURSOR --定义游标 --查出需要的集合放到游标中 FOR (SELECT por1.OrderNumber,por1.CheckingID,p.WmsState FROM FMS_Package p join FMS_PackageOrderRelation por1 on p.PackageID = por1.PackageID WHERE EXISTS (SELECT OrderNumber FROM FMS_PackageOrderRelation por2 WHERE PackageID =@IntoPackageID and por1.OrderNumber = por2.OrderNumber)) OPEN TrackingNumber_Cursor; --打开游标 FETCH NEXT FROM TrackingNumber_Cursor INTO @COID,@CheckingID,@WmsState; --读取第一行数据 WHILE @@FETCH_STATUS = 0 BEGIN SET @BINT=CHARINDEX('F',@CheckingID); IF(@WmsState!=2 AND @BINT=1) BEGIN SET @ShipState='parting'; END Update tdShipInfor SET [SendTime]=@IntoTransDate,[SINo]=@IntoTrackingNumber WHERE coid=@COID; Update tdCOMain set COState=@WmsState where coid=@COID; FETCH NEXT FROM TrackingNumber_Cursor ; --读取下一行数据 END CLOSE TrackingNumber_Cursor; --关闭游标 DEALLOCATE TrackingNumber_Cursor; --释放游标
呐,就是这个东东了。写完之后执行,发现巨慢,要用40秒才能执行完成。哎呀,这还了得啊,不被骂死才怪了,所以找原因咯。
找找找,终于找到了,给游标加上只读标识。
DECLARE TrackingNumber_Cursor CURSOR --定义游标 READ_ONLY--设置游标只读 --查出需要的集合放到游标中 FOR (SELECT por1.OrderNumber,por1.CheckingID,p.WmsState FROM FMS_Package p join FMS_PackageOrderRelation por1 on p.PackageID = por1.PackageID WHERE EXISTS (SELECT OrderNumber FROM FMS_PackageOrderRelation por2 WHERE PackageID =@IntoPackageID and por1.OrderNumber = por2.OrderNumber)) OPEN TrackingNumber_Cursor; --打开游标 FETCH NEXT FROM TrackingNumber_Cursor INTO @COID,@CheckingID,@WmsState; --读取第一行数据 WHILE @@FETCH_STATUS = 0 BEGIN SET @BINT=CHARINDEX('F',@CheckingID); IF(@WmsState!=2 AND @BINT=1) BEGIN SET @ShipState='parting'; END Update tdShipInfor SET [SendTime]=@IntoTransDate,[SINo]=@IntoTrackingNumber WHERE coid=@COID; Update tdCOMain set COState=@WmsState where coid=@COID; FETCH NEXT FROM TrackingNumber_Cursor ; --读取下一行数据 END CLOSE TrackingNumber_Cursor; --关闭游标 DEALLOCATE TrackingNumber_Cursor; --释放游标
就设置了一个只读,然后执行只需0.5秒。
至于为什么,我也不知道,我猜应该是第一次的时候,更新操作在游标里面执行,导致的吧。
顺便分享一个链接。http://www.cnblogs.com/jiajiayuan/archive/2011/07/14/2106341.html