SQL_两种方式遍历操作主键不是自增的数据库

第一种是通过游标过每一行

--创建一个游标
DECLARE cursor_HECM_ROMM CURSOR SCROLL
FOR
    SELECT  ID
    FROM    TABLE
    WHERE   XX=XX
    --打开游标
OPEN cursor_HECM_ROMM;
--存储读取的值
DECLARE @ROOMID VARCHAR(50);
    --读取第一条记录
FETCH FIRST FROM cursor_HECM_ROMM INTO @ROOMID;
--循环读取游标记录
--PRINT '读取的数据如下：';
--全局变量
WHILE ( @@fetch_status = 0 )
    BEGIN
        PRINT 'ID：' + @ROOMID; 
        UPDATE  dbo.TABLE
        SET     XX = XX
        WHERE   XX = @ROOMID;
        --继续读取下一条记录
        FETCH NEXT FROM cursor_HECM_ROMM INTO @ROOMID;
    END;
--关闭游标
CLOSE cursor_HECM_ROMM;

--删除游标
DEALLOCATE cursor_HECM_ROMM;

 第二种是通过存储过程来遍历但是这里有BUG,有的会漏掉，还是推荐第一种。

USE ;
  --指定数据库
  
DECLARE @min_id INT;

DECLARE @UpOutTemp INT;
DECLARE @LowOutTemp INT;
DECLARE @NoPayInTemp INT;
DECLARE @NoPayOutTemp INT;
DECLARE @PayedInTemp INT;
DECLARE @PayedOutTemp INT;
--0 未缴费；1已缴费
DECLARE @PaymentState INT;
--设定温度
DECLARE @SetTemp INT;
DECLARE @UpRoomTemp INT;
DECLARE @LowRoomTemp INT;
DECLARE @RoomTemp INT;
DECLARE @SetBoltStatus INT;

   --声明整数变量@x
SET @min_id = ( SELECT  MIN(LXTID)
                FROM    dbo.t_LXT
              );
SET @LowOutTemp = 30;
SET @UpOutTemp = 46;

SET @LowRoomTemp = 18;
SET @UpRoomTemp = 23;

      --给变量@x赋初值为当前最小的Id值
WHILE @min_id > 0
    BEGIN
        SELECT  @SetTemp = ( SELECT setTemp
                             FROM   dbo.t_LXT
                             WHERE  LXTID = @min_id
                           );
        SELECT  @PaymentState = ( SELECT    paymentState
                                  FROM      dbo.t_LXT
                                  WHERE     LXTID = @min_id
                                );

        SELECT  @PayedOutTemp = ROUND(( ( @UpOutTemp - @LowOutTemp - 1 )
                                        * RAND() + @LowOutTemp ), 0);
        SELECT  @PayedInTemp = @PayedOutTemp + ROUND(( ( 11 - 7 ) * RAND() + 7 ),
                                                     0);

        SELECT  @NoPayInTemp = ROUND(( ( 23 - 18 - 1 ) * RAND() + 18 ), 0);
        SELECT  @NoPayOutTemp = ROUND(( ( 18 - 13 - 1 ) * RAND() + 13 ), 0);

        SELECT  @RoomTemp = ROUND(( ( @UpRoomTemp - @LowRoomTemp - 1 )
                                    * RAND() + @LowRoomTemp ), 0);
        SELECT  @SetTemp = ROUND(( ( @UpRoomTemp - @LowRoomTemp - 1 ) * RAND()
                                   + @LowRoomTemp ), 0);

        UPDATE  dbo.t_LXT
        --SET   productRoomID = ROUND(RAND() * 100, 0)
        SET     roomTemp = @RoomTemp ,
                setTemp = @SetTemp ,
                tempStatus = '1' ,
                outTemp = ( CASE @PaymentState
                              WHEN '1' THEN @PayedOutTemp
                              WHEN '0' THEN @NoPayOutTemp
                            END ) ,
                inTemp = ( CASE @PaymentState
                             WHEN '1' THEN @PayedInTemp
                             WHEN '0' THEN @NoPayInTemp
                           END ) ,
				--if 已缴费paymentState==1 供热setBoltStatus==0 开启 else setBoltStatus==1
                setBoltStatus = ( CASE @PaymentState
                                    WHEN '1' THEN '0'
                                    ELSE '1'
                                  END ) ,
				--if 设定setTemp>实际roomTemp 阀门开启boltStatus==0 else 阀门关闭boltStatus==1
                boltStatus = ( CASE WHEN @SetTemp < @RoomTemp THEN '1'
                                    WHEN @SetBoltStatus = '1' THEN '1'
                                    WHEN @PaymentState = '0' THEN '1'
                                    ELSE '0'
                               END )
        WHERE   LXTID = @min_id; --round()四舍五入把原值转化为指定小数位数

        SELECT  @min_id = ( SELECT  MIN(LXTID)
                            FROM    dbo.t_LXT
                            WHERE   LXTID > @min_id
                          );--rand（）取得是随机数  默认范围为（0·1） rand()*100范围是0~100

    END;

其他的需求都可以通过这两种进行改造。