接SQL点滴之性能优化其实没有那么神秘(上)
其中,单位字段可以是0,1,2,分别代表主单位、单位1、单位2,很多计算需要统一单位,统一单位可以用游标实现:
DECLARE @料号 VARCHAR(30), @单位 INT, @参数 NUMERIC(18,4), DECLARE CUR CURSOR FOR SELECT 料号,单位 FROM 入库表 WHERE 单位 <>0 OPEN CUR FETCH NEXT FROM CUR INTO @料号,@单位 WHILE @@FETCH_STATUS<>-1 BEGIN IF @单位=1 BEGIN SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR END IF @单位=2 BEGIN SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR END FETCH NEXT FROM CUR INTO @料号,@单位 END CLOSE CUR DEALLOCATE CUR 可以改写成: UPDATE A SET 数量=CASE A.单位 WHEN 1 THEN A.数量*B. 单位1参数 WHEN 2 THEN A.数量*B. 单位2参数 ELSE A.数量 END, 损坏数量= CASE A.单位 WHEN 1 THEN A. 损坏数量*B. 单位1参数 WHEN 2 THEN A. 损坏数量*B. 单位2参数 ELSE A. 损坏数量 END, 单位=1 FROM入库表 A, 料件表 B WHERE A.单位<>1 AND A.料号=B.料号 |
C、变量参与的UPDATE语句的例子
SQL ERVER的语句比较灵活,变量参与的UPDATE语句可以实现一些游标一样的功能,比如:
SELECT A,B,C,CAST(NULL AS INT) AS 序号 INTO #T FROM 表 ORDER BY A ,NEWID() |
产生临时表后,已经按照A字段排序,但是在A相同的情况下是乱序的,这时如果需要更改序号字段为按照A字段分组的记录序号,就只有游标和变量参与的UPDATE语句可以实现了,这个变量参与的UPDATE语句如下:
DECLARE @A INT DECLARE @序号 INT UPDATE #T SET @序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END, @A=A, 序号=@序号 |
D、如果必须使用游标,注意选择游标的类型,如果只是循环取数据,那就应该用只进游标(选项FAST_FORWARD),一般只需要静态游标(选项STATIC)。
E、注意动态游标的不确定性,动态游标查询的记录集数据如果被修改,会自动刷新游标,这样使得动态游标有了不确定性,因为在多用户环境下,如果其他进程或者本身更改了纪录,就可能刷新游标的记录集。