极其离谱的现象看哪位超级高手能揭开谜底。

mole 2009-02-03 08:44:42

各位,想请问下面下面两个问题：

问题一：
两种存储过程的写发在不同的Oracle数据库中(结构完全相同,数据量不同,一个是测试库,一个是真实运行库)执行过程中为何有差异:
写法一在测试库上是正确的，在真实库上在执行时update语句影响了5倍的正常记录，我们检查过数据，数据是正确的。
写法二在测试库和真实库上都是正确的。
我们估计可能是Oracle的执行上有问题。

写法一：
v_RESULTCOUNT NUMBER;
BEGIN
SELECT COUNT(DISTINCT PRODUCTID)
INTO v_RESULTCOUNT
FROM TSALESORDERITEMS
WHERE SALESORDERID = v_SALESORDERID
AND VALID = 'T';
UPDATE TPRODUCTSTOCK A
SET BALANCE = BALANCE - (SELECT SUM(LOCATEDQTY)
FROM TSALESORDERITEMS B
WHERE A.PRODUCTID = B.PRODUCTID
AND B.SALESORDERID = v_SALESORDERID
AND B.VALID = 'T'
GROUP BY B.PRODUCTID),
RESERVEDQTY = RESERVEDQTY -
(SELECT SUM(LOCATEDQTY)
FROM TSALESORDERITEMS C
WHERE A.PRODUCTID = C.PRODUCTID
AND C.SALESORDERID = v_SALESORDERID
AND C.VALID = 'T'
GROUP BY C.PRODUCTID),
LOCATEDQTY = LOCATEDQTY -
(SELECT SUM(LOCATEDQTY)
FROM TSALESORDERITEMS D
WHERE A.PRODUCTID = D.PRODUCTID
AND D.SALESORDERID = v_SALESORDERID
AND D.VALID = 'T'
GROUP BY D.PRODUCTID),
LASTMODIFYTIME = to_char(sysdate, 'YYYYMMDDHH24MISS'),
Importtime = '**************'
WHERE WAREHOUSEID = v_WAREHOUSEID
AND A.VALID = 'T'
AND EXISTS (SELECT 1
FROM TSALESORDERITEMS E
WHERE A.PRODUCTID = E.PRODUCTID
AND E.SALESORDERID = v_SALESORDERID
AND E.VALID = 'T'
GROUP BY E.PRODUCTID
HAVING A.BALANCE >= SUM(E.LOCATEDQTY));

IF SQL%ROWCOUNT <> v_RESULTCOUNT THEN
v_ERRORCODE := -20018;
END IF;
END;
写法二:
v_RESULTCOUNT NUMBER;
v_AFFEATED NUMBER:=0;
BEGIN
SELECT COUNT(DISTINCT PRODUCTID)
INTO v_RESULTCOUNT
FROM TSALESORDERITEMS
WHERE SALESORDERID = v_SALESORDERID
AND VALID = 'T';
FOR I IN (SELECT PRODUCTID,SUM(LOCATEDQTY) LOCATEDQTY
FROM TSALESORDERITEMS
WHERE SALESORDERID=v_SALESORDERID
AND VALID='T'
GROUP BY PRODUCTID
) LOOP
UPDATE TPRODUCTSTOCK A
SET BALANCE = BALANCE - I.LOCATEDQTY,
RESERVEDQTY = RESERVEDQTY -I.LOCATEDQTY,
LOCATEDQTY = LOCATEDQTY -I.LOCATEDQTY,
LASTMODIFYTIME = to_char(sysdate, 'YYYYMMDDHH24MISS')
WHERE WAREHOUSEID = v_WAREHOUSEID
AND A.VALID = 'T'
AND A.PRODUCTID=I.PRODUCTID
AND A.BALANCE>=I.LOCATEDQTY;

v_AFFEATED:=v_AFFEATED+SQL%ROWCOUNT;

END LOOP;

IF v_AFFEATED <> v_RESULTCOUNT THEN
v_ERRORCODE := -20018;
END IF;
END;

问题二
就上面第一个存储过程，我们发现对Update语句增加一个对以前不使用的字段赋个值也能使脚本执行正确，但是过了一天就不行了，这个不知是什么原因。

...全文