sqlserver 脚本多条记录遍历

最新推荐文章于 2025-08-06 16:23:50 发布

转载最新推荐文章于 2025-08-06 16:23:50 发布 · 115 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/olmlo/p/3757802.html

文章标签：

#数据库

本文详细介绍了如何利用临时表和游标两种方法遍历数据库中的多条记录，并通过执行特定SQL操作来处理这些记录。通过实例展示了这两种方法的应用场景和区别，为数据库开发人员提供了实用的技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

临时表方式实现多条记录遍历

declare @oper_cart_item_id bigint;
 declare @oper_cart_id bigint;
 declare @the_last_changed_date datetime;
 declare @oper_cust_id int;
 
 
     select * , 0 as flag into #shoppingcart_temp from deleted;
    while exists (select top 1 1 from #shoppingcart_temp t where t.flag = 0 )
    begin
  
    select top 1 
    @oper_cart_item_id = cart_item_id,@oper_cart_id = cart_id,
    @oper_cust_id = cust_id,@the_last_changed_date = last_changed_date
    from  #shoppingcart_temp where flag = 0 ;

    if @oper_cart_item_id is not null and @oper_cart_item_id <> 0 and left(@oper_cart_id,1) = '8'
    begin
       exec proc_sqlser_insertqueue @oper_cart_id,@oper_cart_item_id, @the_last_changed_date, @oper_cust_id, '3';
    end
  
    update  #shoppingcart_temp  set  flag = 1 where cart_item_id = @oper_cart_item_id
  end

游标方式实现多条记录遍历

declare @oper_cart_item_id bigint;
 declare @oper_cart_id bigint;
 declare @the_last_changed_date datetime;
 declare @oper_cust_id int;
 
    declare shoppingcart_cursor cursor for select cart_item_id,cart_id,cust_id,last_changed_date from deleted;
 
    open shoppingcart_cursor
   
    fetch next from shoppingcart_cursor into @oper_cart_item_id,@oper_cart_id, @oper_cust_id,@the_last_changed_date
   
    while (@@fetch_status=0)
    begin
 
        if @oper_cart_item_id is not null and @oper_cart_item_id <> 0 and left(@oper_cart_id,1) = '8'
        begin
           exec proc_sqlser_insertqueue @oper_cart_id,@oper_cart_item_id, @the_last_changed_date, @oper_cust_id, '3';
        end
        fetch next from shoppingcart_cursor into @oper_cart_item_id,@oper_cart_id, @oper_cust_id,@the_last_changed_date
    end
    close shoppingcart_cursor
    DEALLOCATE shoppingcart_cursor