转自:http://www.maomao365.com/?p=6567

用游标,和WHILE可以遍历您的查询中的每一条记录并将要求的字段传给变量进行相应的处理

DECLARE 
@A1 VARCHAR(10),
@A2 VARCHAR(10),
@A3 INT
金沙国际官网,DECLARE CURSOR YOUCURNAME FOR SELECT A1,A2,A3 FROM YOUTABLENAME
OPEN YOUCURNAME
fetch next from youcurname into @a1,@a2,@a3
while @@fetch_status<>-1
begin
update … set …=@a1,…=a2 …-a3 where

……您要执行的操作写在这里
fetch next from youcurname into @a1,@a2,@a3
end
close youcurname
deallocate youcurname

—————————————

在应用程序开发的时候,我们经常可能会遇到下面的应用,我们会通过查询数据表的记录集,循环每一条记录,通过每一条的记录集对另一张表进行数据进行操作,如插入与更新,我们现在假设有一个这样的业务:老师为所在班级的学生选课,选的课程如有哲学、马克思主义政治经济学、XXX思想概论、邓小平理论这些课,现在操作主要如下:
1) 先要查询这些还没有毕业的这些学生的名单,毕业过后的无法进行选课;
2) 在批量的选取学生的同时,还需要添加对应的某一门课程;
3) 点添加后选课结束。
数据量少可能看不出用Java程序直接多次进行数据库操作这种办法实现的弱点,因为它每次在操作数据库的时候,都存在着频繁的和数据库的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时停止循环,此时完成操作。
比较以上的几种循环方法的应用,就会知道,有时候可能对于同一种功能我们实现的方法不同,而最终应用程序性能的影响的差异就会很大,第二种、第三种就大大的减少的数据库交互I/O操作的频繁,会节省很多时间,方法三又避免用游标又可以节省不必要的开销。

 

 

使用SQL的Agent可以执行计划任务,把写好的SQL语句放在计划任务里,可以达到奇妙的效果,如定时备份数据,定时执行特定操作等等,当涉及循环操作很多条数据时,这里就要使用游标了,当然SQL中也有循环语句,如使用While。不过while的功能只能实现一般的操作,游标的功能更为强大些,可在一个指定的一个集合内循环操作数据,实现动态操作,那就更牛了,呵呵,以下资料供存档用。

WHILE
设置重复执行 SQL
语句或语句块的条件。只要指定的条件为真,就重复执行语句。可以使用 BREAK
和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。

语法
WHILE Boolean_expression
      { sql_statement | statement_block }
      [ BREAK ]
      { sql_statement | statement_block }
      [ CONTINUE ]

参数
Boolean_expression

返回 TRUE 或 FALSE 的表达式。如果布尔表达式中含有 SELECT
语句,必须用圆括号将 SELECT 语句括起来。

{sql_statement | statement_block}

Transact-SQL
语句或用语句块定义的语句分组。若要定义语句块,请使用控制流关键字 BEGIN
和 END。

BREAK

导致从最内层的 WHILE 循环中退出。将执行出现在 END
关键字后面的任何语句,END 关键字为循环结束标记。

CONTINUE

使 WHILE 循环重新开始执行,忽略 CONTINUE 关键字后的任何语句。

注释
如果嵌套了两个或多个 WHILE 循环,内层的 BREAK
将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句,然后下一个外层循环重新开始执行。

示例

declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end


while 条件
begin
执行操作
set @i=@i+1
end

A. 在嵌套的 IF…ELSE 和 WHILE 中使用 BREAK 和 CONTINUE
在下例中,如果平均价格少于 $30,WHILE
循环就将价格加倍,然后选择最高价。如果最高价少于或等于 $50,WHILE
循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过
$50,然后退出 WHILE 循环并打印一条消息。

USE pubs
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
     UPDATE titles
        SET price = price * 2
     SELECT MAX(price) FROM titles
     IF (SELECT MAX(price) FROM titles) > $50
        BREAK
     ELSE
        CONTINUE
END
PRINT ‘Too much for the market to bear’

B. 在带有游标的过程中使用 WHILE
以下的 WHILE 结构是名为 count_all_rows 过程中的一部分。下例中,该
WHILE 结构测试用于游标的函数
@@FETCH_STATUS 的返回值。因为 @@FETCH_STATUS 可能返回 –2、-1 或
0,所以,所有的情况都应进行测试。如果某一行在开始执行此存储过程以后从游标结果中删除,将跳过该行。成功提取
(0) 后将执行 BEGIN…END 循环内部的 SELECT 语句。

单变量循环
USE pubs
DECLARE tnames_cursor CURSOR
FOR
     SELECT TABLE_NAME 
     FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
–SET @tablename = ‘authors’
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
     IF (@@FETCH_STATUS <> -2)
     BEGIN    
        SELECT @tablename = RTRIM(@tablename) 
        EXEC (‘SELECT ”’ + @tablename + ”’ = count(*) FROM ‘ 
              + @tablename )
        PRINT ‘ ‘
    END
     FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

多变量循环

CREATE      PROCEDURE my_FeeCount  AS
declare  @到期时间      char(10)
declare  @片区          char(20)
declare  @缴费用户数 char(10)
declare  @sql  char(2000)

declare  cur_data  cursor  for 
 select   convert(varchar(10),到期时间,120)  as 到期时间
,片区,count(distinct main_id) as  缴费用户数 
 from V_aipu_fee where
提交时间>=convert(varchar(10),getdate()-90,120) and
提交时间<convert(varchar(10),getdate()+1-90,120)
 and 收费类型=’续费收费’
 Group   by   convert(varchar(10),到期时间,120),片区
 order   by   convert(varchar(10),到期时间,120) 
open  cur_data

fetch  next  from  cur_data  into  @到期时间,@片区,@缴费用户数  
while(@@fetch_status  =  0) 
begin
        set @sql=’update
‘+RTRIM(@片区)+’实收=’+RTRIM(@片区)+’实收+’+RTRIM(@缴费用户数)+’ where
收费日期=”’+RTRIM(@到期时间)+””

 print @sql

 fetch  next  from  cur_data  into  @到期时间,@片区,@缴费用户数 
end

close  cur_data

 摘要:

1.简单游标

sql循环,除了游标还有如下

摘要:

下文整理sqlserver优化中的常用知识点和注意事项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare m_cursor cursor scroll for
select  Address,PeopleId from  PeopleDetail
 
open m_cursor
declare   @Address varchar(50), @PeopleId int
 
fetch next from m_cursor into @Address,@PeopleId
while @@FETCH_STATUS=0
begin
    print @Address + convert(varchar(3), @PeopleId)
    fetch next from m_cursor into @Address,@PeopleId
end
 
close m_cursor
deallocate m_cursor

 

下文讲述sql脚本模拟for循环的写法,如下所示:


2.用于更新的游标 

     --   --创建临时表
                     --   DECLARE @tradeID INT;
            --if not object_id('Tempdb..#A') is null
            --     drop table #A

            --Create table #A([TradeID] nvarchar(100))
            --Insert #A SELECT TradeID FROM dbo.CRM_AntiFakeQRec GROUP BY TradeID
            --WHILE EXISTS(SELECT TradeID FROM #A)
            --BEGIN
            --SET ROWCOUNT 1
            --SELECT @tradeID=TradeID FROM #A
            --SET ROWCOUNT 0
            --DELETE FROM #A WHERE TradeID=@tradeID
            --PRINT @tradeID
 /*
for样例
for('初始值','条件','执行后自增')
通过对for语句的结构分析,我们可以采用以下
while 结构来实现for循环,
--------------------------
初始值
while(条件)
begin
  执行后自增
end
--------------------------
例:使用while输出10的遍历
*/
declare @i int=1  --初始值
while (@i<=10) --条件
begin
  print '@i的值:'
  print @i
  set @i=@i+1 ---执行后自增
end

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 定义游标
declare m_cursor cursor scroll for
select a.Id,b.Address from  People a left join PeopleDetail b on a.Id=b.PeopleId
for update
-- 打开游标
open m_cursor
declare   @Address varchar(50), @Id int
--填充数据
fetch next from m_cursor into @Id,@Address
--假如检索到了数据,才处理
while @@FETCH_STATUS=0
begin
    update People set  Address=@Address where current of m_cursor
    --填充下一条数据
    fetch next from m_cursor into @Id,@Address
end
-- 关闭游标
close m_cursor
--释放游标
deallocate m_cursor

  

 

1、表上未建立合理的索引

  

2、服务器内存过小,导致缓存过少,数据库频繁的交互数据

3、统计信息不准确,导致索引乱用

4、网络异常,数据交换慢

5、单次数据吞吐量大,导致显示慢

6、代码出现异常,导致死锁

7、select 查询返回过多的列或行

10、查询语句写法问题

11 、数据 日志需部署在不同的磁盘上

12、从业务上对表和库进行纵向或横向分割

13、索引碎片维护

14、少用游标,使用临时表加while代替循环

15、使用union all代替union

16、谨慎使用distinct ,它可能使查询变慢,可参阅执行计划

17、between速度高于in

18、合理使用临时表,避免多次获取数据

19、select into会锁系统表 sysobjects sysindexes 等,谨慎使用

20、判断行是否存在,使用exists效率会高于count

21、所有业务都按照一定的顺序依次访问表,可以避免出现死锁现象

22、连接表,需使用小表连大表,可加快脚本速度

23、使用连接替代子查询

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图