none
怎样循环遍历一个select查询结果集,,结果集只有单列,该列的类型是varchar, RRS feed

  • 问题

  • 怎样循环遍历一个select查询结果集,结果集只有一个列,该列的类型是varchar。每次循环做点什么,例如每次循环用print输出当前遍历的值。这里所说到的结果集是在带参数的存储过程的内部对结果集进行遍历结果集。有其他的好方法的话,不想用游标





    2017年12月22日 4:16

答案

  • 游标就行了

    declare cur cursor as select ....
    open cur
    declare @value nvarchar(max);
    while 1 = 1
    begin;
    	fetch cur into @value;
    	if not @@fetch_status = 0 break;
    	print @value;
    end;
    close cur;
    deallocate cur

    2017年12月22日 9:02

全部回复

  • Hi 便携式家园,

    您好!这是循环遍历的其中一个例子,具体请看示例代码:

    --Drop table Test
    
    CREATE TABLE Test (
    v_No varchar(10),
    DesignCode varchar(100)
    )
    
    INSERT INTO Test
    VALUES ('A10','ertYrtEq65Less120days'),
    ('A21','YrtDefUdrEq70Less180days'),
    ('A23','YrtDefUdrEq70')
    
    select 
    ROW_NUMBER() over (order by v_No) as rn,
    v_No,
    DesignCode
    into #tempTest
    from Test
    
    --query
    declare @v_num int
    declare @v_max int
    declare @output varchar(max)
    
    select @v_max=max(rn) from #tempTest
    set @v_num=1
    
    while (@v_num<=@v_max)
    begin
    select @output=v_No+','+DesignCode  from #tempTest where rn=@v_num
    print @output
    set @v_num=@v_num+1
    end

    另外,使用游标做循环遍历是比较常见的,但一般不建议这么做。

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2017年12月22日 5:47
  • 游标就行了

    declare cur cursor as select ....
    open cur
    declare @value nvarchar(max);
    while 1 = 1
    begin;
    	fetch cur into @value;
    	if not @@fetch_status = 0 break;
    	print @value;
    end;
    close cur;
    deallocate cur

    2017年12月22日 9:02
  • Hi 便携式家园,

    您好!这是循环遍历的其中一个例子,具体请看示例代码:

    --Drop table Test
    
    CREATE TABLE Test (
    v_No varchar(10),
    DesignCode varchar(100)
    )
    
    INSERT INTO Test
    VALUES ('A10','ertYrtEq65Less120days'),
    ('A21','YrtDefUdrEq70Less180days'),
    ('A23','YrtDefUdrEq70')
    
    select 
    ROW_NUMBER() over (order by v_No) as rn,
    v_No,
    DesignCode
    into #tempTest
    from Test
    
    --query
    declare @v_num int
    declare @v_max int
    declare @output varchar(max)
    
    select @v_max=max(rn) from #tempTest
    set @v_num=1
    
    while (@v_num<=@v_max)
    begin
    select @output=v_No+','+DesignCode  from #tempTest where rn=@v_num
    print @output
    set @v_num=@v_num+1
    end

    另外,使用游标做循环遍历是比较常见的,但一般不建议这么做。

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    你这个我以我的方式测试了一下,对表或视图还行,如果是带参数的存储过程的内部遍历的结果集。好像不太行


    2017年12月22日 10:39
  • 你这个我以我的方式测试了一下,对表或视图还行,如果是带参数的存储过程的内部遍历的结果集。好像不太行

    是的,如果是您说的那种情况,可能会用到游标,但是,如果逻辑上的限制到必须要用的地步,那也是没办法的事。其实存储过程不封装的话,还是可以避免使用游标的,是否可以避免使用游标,还得根据实际的逻辑情况。

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2017年12月22日 12:40
  • USE tempdb;
    go
    IF OBJECT_ID('Test') IS NOT NULL Drop table Test
    CREATE TABLE Test (
    v_No varchar(10),
    DesignCode varchar(100)
    )
    INSERT INTO Test
    SELECT TOP 10000
    	A.name, B.name
    FROM sys.all_columns A, sys.all_columns B
    ;
    GO
    
    -- =============================================
    -- 游标循环
    -- =============================================
    DECLARE @dt datetime; SET @dt = GETDATE();
    DECLARE CUR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    select v_No+','+DesignCode from Test
    ;
    declare @output varchar(max)
    OPEN CUR;
    while 1=1
    begin
    	FETCH CUR INTO @output;
    	IF NOT @@FETCH_STATUS = 0 BREAK;
    	print @output
    end
    CLOSE CUR; DEALLOCATE CUR;
    
    SELECT 游标循环 = DATEDIFF(ms, @dt, GETDATE());
    GO
    
    -- =============================================
    -- 你自己写的循环
    -- =============================================
    IF OBJECT_ID('tempdb..#tempTest') IS NOT NULL DROP TABLE #tempTest;
    DECLARE @dt datetime; SET @dt = GETDATE();
    
    select 
    ROW_NUMBER() over (order by v_No) as rn,
    v_No,
    DesignCode
    into #tempTest
    from Test
    
    --query
    declare @v_num int
    declare @v_max int
    declare @output varchar(max)
    
    select @v_max=max(rn) from #tempTest
    set @v_num=1
    
    while (@v_num<=@v_max)
    begin
    select @output=v_No+','+DesignCode  from #tempTest where rn=@v_num
    print @output
    set @v_num=@v_num+1
    end
    
    SELECT 自己做的循环 = DATEDIFF(ms, @dt, GETDATE());
    GO
    
    那么嫌弃游标,你测试一下游标和你自己写的循环,那个快呢
    2017年12月25日 1:22
  • >>那么嫌弃游标,你测试一下游标和你自己写的循环,那个快呢

    从单个场景考虑,游标会快一些,但是,别忘了,资源耗用太多,执行的语句不仅仅就这游标语句一个,除非你的服务器能分配的内存足够大,尽管用游标好了,言尽于此,用游标还是用循环,自己根据情况决断吧。我没有排斥游标的意思,能不用,尽量不用。

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2017年12月25日 2:01
  • 根据情况自然是必须的,不考虑情况那机器都自动实现了

    通常能够不遍历的不考虑遍历,所以仅针对一定要遍历的

    只是在这个问题上想起了遇到过的那些为了避免游标而使用不恰当方法的替代方法来遍历的情况啰嗦下

    个人看法仅供参考

    2017年12月25日 2:51