求助：一个棘手的循环更新问题100分（在线等）

Question

商品台帐的推倒 select prodcode,bookno,ccount,ecount
from prodbook200807
where prodcode=100017
order by bookno结果如下
编号              流水号   发生数量  结存数量
100017        120866 1.0000 21.0000
100017        121733 2.0000 19.0000
100017        122317 24.0000 43.0000
100017        122445 3.0000 40.0000
100017        123640 2.0000 38.0000
100017        124248 3.0000 35.0000
100017        124748 2.0000 33.0000
100017        125601 2.0000 31.0000
100017        127375 17.0000 14.0000
100017        134574 2.0000 12.0000
100017        139973 1.0000 -15.0000规律要求是 前提 ：第一行数据是准确的 ，第一行是编号 ，第二行是流水号（唯一关键字）
第二行的结存=第一行结存 - 第二行发生的数量现在发先 其中有不符和规律的数据
如上面的最后一行要求用 一个 方法来更新 结存数量

Answer 1

update prodbook200807 b
set ecount=
(select r_ecount
from(
     select a.*,
lead(ecount,1,ecount+ccount) over (partition by prodcode order by bookno)-ccount  r_ecount
       from prodbook200807 a
        where prodcode=100017 ) aa
 where aa.ecount<>aa.r_ecount
      and aa.bookno=b.bookno)
where exists
(select 1
      from(
     select a.*,
        lead(ecount,1,ecount+ccount) over (partition by prodcode order by bookno)-ccount  r_ecount
       from prodbook200807 a
        where prodcode=100017 ) aa
 where aa.ecount<>aa.r_ecount
      and aa.bookno=b.bookno)

Answer 2

Try it ..
SQL> select * from prodbook200807;  PRODCODE     BOOKNO     CCOUNT     ECOUNT
---------- ---------- ---------- ----------
    100017     127375         17         14
    100017     134574          2         12
    100017     139973          1        -15
    100018     139999          4         36
    100018     140536          8         28
    100018     140779          6         -86 rows selectedSQL> 
SQL> update prodbook200807 p1
  2     set p1.ecount = (select prev - ccount as new_ecount
  3                        from (select lag(ecount) over(partition by prodcode order by prodcode, bookno) as prev,
  4                                     p.prodcode,
  5                                     p.bookno,
  6                                     p.ccount,
  7                                     p.ecount
  8                                from prodbook200807 p) p2
  9                       where p1.prodcode = p2.prodcode
 10                         and p1.bookno = p2.bookno
 11                         and prev is not null
 12                         and prev - ccount <> ecount)
 13   where exists (select 1
 14            from (select lag(ecount) over(partition by prodcode order by prodcode, bookno) as prev,
 15                         p.prodcode,
 16                         p.bookno,
 17                         p.ccount,
 18                         p.ecount
 19                    from prodbook200807 p) p2
 20           where p2.prodcode = p1.prodcode
 21             and p2.bookno = p1.bookno
 22             and prev is not null
 23             and prev - ccount <> ecount);2 rows updatedSQL> commit;Commit completeSQL> select * from prodbook200807;  PRODCODE     BOOKNO     CCOUNT     ECOUNT
---------- ---------- ---------- ----------
    100017     127375         17         14
    100017     134574          2         12
    100017     139973          1         11
    100018     139999          4         36
    100018     140536          8         28
    100018     140779          6         226 rows selected

Answer 3

select prodcode,bookno,ccount,ecount,ftype
from prodbook200807 
where prodcode=100017 
order by bookno 
100017        120866 1.0000 18.0000 E
100017        121733 2.0000 41.0000 E
100017        122317 24.0000 16.0000 F
100017        122445 3.0000 35.0000 E
100017        123640 2.0000 33.0000 E
100017        124248 3.0000 30.0000 E
100017        124748 2.0000 29.0000 E
100017        125601 2.0000 12.0000 E
100017        127375 17.0000 -5.0000 E
100017        134574 2.0000 -17.0000 E
100017        139973 1.0000 -15.0000 E最后一行为类型 E 为减少
               F 为增加

Answer 4

我的写错了，应该用lag的，LEAD是取后一条纪录

Answer 5

update prodbook200807 b
set ecount=
(select r_ecount
from(
     select a.*,
deocde(ftype,'E',
lag(ecount,1,ecount+ccount) over (partition by prodcode order by bookno)-ccount,
lag(ecount,1,ecount-ccount) over (partition by prodcode order by bookno)-ccount)  r_ecount
       from prodbook200807 a
        where prodcode=100017 ) aa
 where aa.ecount<>aa.r_ecount
      and aa.bookno=b.bookno)
where exists
(select 1
      from(
     select a.*,
        deocde(ftype,'E',
lag(ecount,1,ecount+ccount) over (partition by prodcode order by bookno)-ccount,
lag(ecount,1,ecount-ccount) over (partition by prodcode order by bookno)-ccount) r_ecount
       from prodbook200807 a
        where prodcode=100017 ) aa
 where aa.ecount<>aa.r_ecount
      and aa.bookno=b.bookno)

Answer 6

还是有点错误
update prodbook200807 b
set ecount=
(select r_ecount
from(
     select a.*,
deocde(ftype,'E',
lag(ecount,1,ecount+ccount) over (partition by prodcode order by bookno)-ccount,
lag(ecount,1,ecount-ccount) over (partition by prodcode order by bookno)+ccount)  r_ecount
       from prodbook200807 a
        where prodcode=100017 ) aa
 where aa.ecount<>aa.r_ecount
      and aa.bookno=b.bookno)
where prodcode=100017 and exists
(select 1
      from(
     select a.*,
        deocde(ftype,'E',
lag(ecount,1,ecount+ccount) over (partition by prodcode order by bookno)-ccount,
lag(ecount,1,ecount-ccount) over (partition by prodcode order by bookno)+ccount) r_ecount
       from prodbook200807 a
        where prodcode=100017 ) aa
 where aa.ecount<>aa.r_ecount
      and aa.bookno=b.bookno)

Answer 7

解题思路，我和二楼的的思路都是取出上一条纪录的ecount,然后算出实际的ecount
最后取出两值不相等的纪录更新成实际的不过我刚刚想了下，还是有问题
就是如果第一条是对的，第二条减错了，而第三条在前面错误的基础上减的话，计算是对的，但值是错误的这样时update时，只会更新第二条纪录，第三条不会更新的
然后再重新运行边，此时才会更新第二条
我再想个办法

Answer 8

假设BOOKNO是主键,根据第一行的值，全部重新计算ecount（即r_count）,然后和ecount比较，不对的就更新，一次就能全部更新掉
不过这条语句仅限于prodcode是单一数值update prodbook200807 b
set ecount=
( select aa.r_count
     from(
      select aa.*,sum(t_count) over(order by bookno) r_count
     from(
     select a.*,
     decode(rownum,1,a.ecount,decode(ftype,'E',-ccount,count)) t_count
       from prodbook200807 a
        where prodcode=100017
        order by bookno ) aa) aaa
 where aaa.ecount<>aa.r_ecount
      and aaa.bookno=b.bookno)
where prodcode=100017 and exists
(select 1
     from(
      select aa.*,sum(t_count) over(order by bookno) r_count
     from(
     select a.*,
     decode(rownum,1,a.ecount,decode(ftype,'E',-ccount,count)) t_count
       from prodbook200807 a
        where prodcode=100017
        order by bookno ) aa) aaa
 where aaa.ecount<>aa.r_ecount
      and aaa.bookno=b.bookno)

Answer 9

可以先算出正确的，然后不等的数据再update就行了。
------------------------------------------------------
这样时update时，只会更新第二条纪录，第三条不会更新的 

Answer 10

试试这个:BEGIN
LOOP
    UPDATE PRODBOOK200807 P1
       SET P1.ECOUNT = (SELECT PREV - CCOUNT AS NEW_ECOUNT
                          FROM (SELECT LAG(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) AS PREV,
                                       P.PRODCODE,
                                       P.BOOKNO
                                  FROM PRODBOOK200807 P) P2
                         WHERE P1.PRODCODE = P2.PRODCODE
                           AND P1.BOOKNO = P2.BOOKNO
                           AND PREV IS NOT NULL
                           AND PREV - CCOUNT <> ECOUNT)
     WHERE EXISTS (SELECT NULL
              FROM (SELECT LAG(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) AS PREV,
                           P.PRODCODE,
                           P.BOOKNO
                      FROM PRODBOOK200807 P) P2
             WHERE P2.PRODCODE = P1.PRODCODE
               AND P2.BOOKNO = P1.BOOKNO
               AND PREV IS NOT NULL
               AND PREV - CCOUNT <> ECOUNT);
    EXIT WHEN SQL%ROWCOUNT = 0;
    dbms_output.put_line(SQL%ROWCOUNT);
  END LOOP; 
END;SELECT * FROM prodbook200807;
输出:
100017 120866 1 21
100017 121733 2 19
100017 122317 24 -5
100017 122445 3 -8
100017 123640 2 -10
100017 124248 3 -13
100017 124748 2 -15
100017 125601 2 -17
100017 127375 17 -34
100017 134574 2 -36
100017 139973 1 -37

Answer 11

我写了个函数
create or replace function updateProBook(procode varchar2) return varchar2 is
  cursor curProdBook(curProcode varchar2) is
    select prodcode, bookno, ccount, ecount, ftype
      from prodbook200807
     where prodcode = curProcode
     order by bookno;
  varProdcodeCurr prodbook200807.prodcode%type;
  varBookNoCurr   prodbook200807.bookno%type;
  intCcountCurr   prodbook200807.ccount%type;
  intEcountCurr   prodbook200807.ecount%type;
  varFtypeCurr    prodbook200807.ftype%type;
  varProdcodeNext prodbook200807.prodcode%type;
  varBookNoNext   prodbook200807.bookno%type;
  intCcountNext   prodbook200807.ccount%type;
  intEcountNext   prodbook200807.ecount%type;
  varFtypeNext    prodbook200807.ftype%type;
  intResult       number;
begin
  open curProdBook(procode);
  loop
    fetch curProdBook
      into varProdcodeCurr, varBookNoCurr, intCcountCurr, intEcountCurr, varFtypeCurr;
    exit when curProdBook%notfound;
    dbms_output.put_line(intEcountCurr);
    fetch curProdBook
      into varProdcodeNext, varBookNoNext, intCcountNext, intEcountNext, varFtypeNext;
    exit when curProdBook%notfound;
    dbms_output.put_line(intCcountNext);
    if varFtypeCurr = 'E' then
      intResult := intEcountCurr - intCcountNext;
    elsif varFtypeCurr = 'F' then
      intResult := intEcountCurr + intCcountNext;
    end if;
    update prodbook200807
       set ecount = intResult
     where prodcode = procode
       and bookno = varBookNoNext;
    commit;
  end loop;
  close curProdBook;
  return 'true';
exception
  when others then
    return 'false';
end;
函数没调用之前数据
1 100017 120866 1.0000 21.0000 E AAAHapAALAAAAA4AAA
2 100017 121733 2.0000 19.0000 E AAAHapAALAAAAA4AAB
3 100017 122317 24.0000 43.0000 F AAAHapAALAAAAA4AAC
4 100017 122445 3.0000 40.0000 F AAAHapAALAAAAA4AAD
5 100017 123640 2.0000 38.0000 E AAAHapAALAAAAA4AAE
6 100017 124248 3.0000 35.0000 E AAAHapAALAAAAA4AAF
7 100017 124748 2.0000 33.0000 F AAAHapAALAAAAA4AAG
8 100017 125601 2.0000 31.0000 F AAAHapAALAAAAA4AAH
9 100017 134574 2.0000 12.0000 E AAAHapAALAAAAA4AAJ
10 100017 139973 1.0000 -15.0000E       AAAHapAALAAAAA4AAK
调用函数并将100017传入之后的数据
1 100017 120866 1.0000 21.0000 E AAAHapAALAAAAA4AAA
2 100017 121733 2.0000 19.0000 E AAAHapAALAAAAA4AAB
3 100017 122317 24.0000 43.0000 F AAAHapAALAAAAA4AAC
4 100017 122445 3.0000 46.0000 F AAAHapAALAAAAA4AAD
5 100017 123640 2.0000 38.0000 E AAAHapAALAAAAA4AAE
6 100017 124248 3.0000 35.0000 E AAAHapAALAAAAA4AAF
7 100017 124748 2.0000 33.0000 F AAAHapAALAAAAA4AAG
8 100017 125601 2.0000 35.0000 F AAAHapAALAAAAA4AAH
9 100017 134574 2.0000 12.0000 E AAAHapAALAAAAA4AAJ
10 100017 139973 1.0000 11.0000 E AAAHapAALAAAAA4AAK
看看是不是你想要的结果

Answer 12

增加标志位后的调整方法：调整前数据:
SELECT * FROM PRODBOOK200807;
100017  120866  1  21  E
100017  121733  2  19  E
100017  122317  24  43  E
100017  122445  3  40  E
100017  123640  2  38  F
100017  124248  3  35  E
100017  124748  2  33  F
100017  125601  2  31  E
100017  127375  17  14  E
100017  134574  2  12  E
100017  139973 1 -15 EBEGIN
  LOOP
    UPDATE PRODBOOK200807 P1
       SET P1.ECOUNT = (SELECT DECODE(FLAG,
                                      'E',
                                      PREV - CCOUNT,
                                      'F',
                                      PREV + CCOUNT) AS NEW_ECOUNT
                          FROM (SELECT LAG(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) AS PREV,
                                       P.PRODCODE,
                                       P.BOOKNO
                                  FROM PRODBOOK200807 P) P2
                         WHERE P1.PRODCODE = P2.PRODCODE
                           AND P1.BOOKNO = P2.BOOKNO
                           AND PREV IS NOT NULL
                           AND DECODE(FLAG,
                                      'E',
                                      PREV - CCOUNT,
                                      'F',
                                      PREV + CCOUNT) <> ECOUNT)
     WHERE EXISTS
     (SELECT NULL
              FROM (SELECT LAG(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) AS PREV,
                           P.PRODCODE,
                           P.BOOKNO
                      FROM PRODBOOK200807 P) P2
             WHERE P2.PRODCODE = P1.PRODCODE
               AND P2.BOOKNO = P1.BOOKNO
               AND PREV IS NOT NULL
               AND DECODE(FLAG, 'E', PREV - CCOUNT, 'F', PREV + CCOUNT) <>
                   ECOUNT);
    EXIT WHEN SQL%ROWCOUNT = 0;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
  END LOOP;
END;SELECT * FROM PRODBOOK200807;
调整后输出 :
100017 120866 1 21 E
100017 121733 2 19 E
100017 122317 24 -5 E
100017 122445 3 -8 E
100017 123640 2 -6 F
100017 124248 3 -9 E
100017 124748 2 -7 F
100017 125601 2 -9 E
100017 127375 17 -26 E
100017 134574 2 -28 E
100017 139973 1 -29 E

Answer 13

SELECT LAG(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) AS PREV,
                           P.PRODCODE,
                           P.BOOKNO,
                           p.ftype,
                           p.ccount,
                           p.ecount
                      FROM PRODBOOK200807 P
                      where prodcode=100017 100017        120866 E 1.0000 21.0000
21 100017        121733 E 2.0000 19.0000
19 100017        122317 F 24.0000 43.0000
43 100017        122445 E 3.0000 40.0000
40 100017        123640 E 2.0000 38.0000
38 100017        124248 E 3.0000 35.0000
35 100017        124748 E 2.0000 33.0000
33 100017        125601 E 2.0000 31.0000
31 100017        127375 E 17.0000 14.0000
14 100017        134574 E 2.0000 12.0000
12 100017        139973 E 1.0000 -15.0000
因为一开始描述的问题 简单化了，其实中间还有一个标志位的 E的为减少 F的为增加就是说 第二行 E的话是 21-2 =19  第三行F的是19+24=43 第四行E 是43-3 =40

Answer 14

bookno 是主关键字 但是没有规律性 的

Answer 15

BOOKNO
是PK的话，可以参照我最后的写法，此语句是只针对prodcode=100017 如果有多个的话，需要换种写法

Answer 16

我运行这段程序的时候出现 ,FROM 错误可是我找不到，from的存在啊

Answer 17

                                                       *
ERROR 位于第 19 行:
ORA-06550: 第 19 行, 第 56 列:
PLS-00103: 出现符号 "("在需要下列之一时：
,from

Answer 18

我这里执行没有任何问题，可能你copy代码的问题，再copy一次试试吧！
BEGIN
  LOOP
    UPDATE PRODBOOK200807 P1
       SET P1.ECOUNT = (SELECT DECODE(FLAG,
                                      'E',
                                      PREV - CCOUNT,
                                      'F',
                                      PREV + CCOUNT) AS NEW_ECOUNT
                          FROM (SELECT LAG(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) AS PREV,
                                       P.PRODCODE,
                                       P.BOOKNO
                                  FROM PRODBOOK200807 P) P2
                         WHERE P1.PRODCODE = P2.PRODCODE
                           AND P1.BOOKNO = P2.BOOKNO
                           AND PREV IS NOT NULL
                           AND DECODE(FLAG,
                                      'E',
                                      PREV - CCOUNT,
                                      'F',
                                      PREV + CCOUNT) <> ECOUNT)
     WHERE EXISTS
     (SELECT NULL
              FROM (SELECT LAG(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) AS PREV,
                           P.PRODCODE,
                           P.BOOKNO
                      FROM PRODBOOK200807 P) P2
             WHERE P2.PRODCODE = P1.PRODCODE
               AND P2.BOOKNO = P1.BOOKNO
               AND PREV IS NOT NULL
               AND DECODE(FLAG, 'E', PREV - CCOUNT, 'F', PREV + CCOUNT) <>
                   ECOUNT);
    EXIT WHEN SQL%ROWCOUNT = 0;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
  END LOOP;
END;

Answer 19

下面为不用指定prodcode的情况, 如果ktype是'G'的情况也是和'E'的情况一样的话(减少), 试试下面的update statement:
SQL> select * from prodbook200807;  PRODCODE     BOOKNO     CCOUNT     ECOUNT FTYPE
---------- ---------- ---------- ---------- -----
    100017     127375         17         14 E
    100017     134574          2         12 E
    100017     139973          4         10 F
    100017     139988          5         15 E
    100017     139990          7        -15 E
    100018     139999          4         36 E
    100018     140536          8         28 E
    100018     140779          6         30 F
    100018     140780          2         32 E
    100018     140786          1         33 E10 rows selectedSQL> select * from prodbook200807;  PRODCODE     BOOKNO     CCOUNT     ECOUNT FTYPE
---------- ---------- ---------- ---------- -----
    100017     127375         17         14 E
    100017     134574          2         12 E
    100017     139973          4         10 F
    100017     139988          5         15 E
    100017     139990          7        -15 E
    100018     139999          4         36 E
    100018     140536          8         28 E
    100018     140779          6         30 F
    100018     140780          2         31 E
    100018     140786          1         33 E10 rows selectedSQL> 
SQL> update prodbook200807 p1
  2     set p1.ecount = (select p2.correct_ecount
  3                        from (select pp.prodcode,
  4                                     pp.bookno,
  5                                     first_values +
  6                                     (sum(new_ccount)
  7                                      over(partition by prodcode order by
  8                                           prodcode,
  9                                           bookno)) as correct_ecount
 10                                from (select decode(p.FTYPE,
 11                                                    'E',
 12                                                    -ccount,
 13                                                    'G',
 14                                                    -ccount,
 15                                                    'F',
 16                                                    ccount,
 17                                                    0) as new_ccount,
 18                                             p.prodcode,
 19                                             p.bookno,
 20                                             lag(ecount) over(partition by prodcode order by prodcode, bookno) as prev,
 21                                             first_value(ecount) over(partition by prodcode order by prodcode, bookno) as first_values,
 22                                             p.ccount,
 23                                             p.ecount
 24                                        from prodbook200807 p) pp
 25                               where prev is not null) p2
 26                       where p2.prodcode = p1.prodcode
 27                         and p2.bookno = p1.bookno
 28                         and p2.correct_ecount <> p1.ecount)
 29   where exists
 30   (select 1
 31            from (select pp.prodcode,
 32                         pp.bookno,
 33                         first_values +
 34                         (sum(new_ccount)
 35                          over(partition by prodcode order by prodcode, bookno)) as correct_ecount
 36                    from (select decode(p.FTYPE,
 37                                        'E',
 38                                        -ccount,
 39                                        'G',
 40                                        -ccount,
 41                                        'F',
 42                                        ccount,
 43                                        0) as new_ccount,
 44                                 p.prodcode,
 45                                 p.bookno,
 46                                 lag(ecount) over(partition by prodcode order by prodcode, bookno) as prev,
 47                                 first_value(ecount) over(partition by prodcode order by prodcode, bookno) as first_values,
 48                                 p.ccount,
 49                                 p.ecount
 50                            from prodbook200807 p) pp
 51                   where prev is not null) p2
 52           where p2.prodcode = p1.prodcode
 53             and p2.bookno = p1.bookno
 54             and p2.correct_ecount <> p1.ecount);6 rows updatedSQL> commit;Commit completeSQL> select * from prodbook200807;  PRODCODE     BOOKNO     CCOUNT     ECOUNT FTYPE
---------- ---------- ---------- ---------- -----
    100017     127375         17         14 E
    100017     134574          2         12 E
    100017     139973          4         16 F
    100017     139988          5         11 E
    100017     139990          7          4 E
    100018     139999          4         36 E
    100018     140536          8         28 E
    100018     140779          6         34 F
    100018     140780          2         32 E
    100018     140786          1         31 E10 rows selected
Good luck!

Answer 20

    update prodbook200807 p1
           *
ERROR 位于第 1 行:
ORA-00600: 内部错误代码，自变量: [kcbgcur_9], [4194349], [1], [4294967250],
[2], [], [], []居然有这个错误 是不是数据库版本的问题啊

Answer 21

请问你的环境是什么版本的
我这里是8.17Oracle8i Enterprise Edition Release 8.1.7.0.0

Answer 22

方法层出不穷啊。！！！！！！！！！！！！
我再出个一次更新的sql，大家继续。UPDATE PRODBOOK200807 P1
   SET P1.ECOUNT = (SELECT CC + FF + XX
                      FROM (SELECT SUM(DECODE(FLAG, 'E', (-CCOUNT), 'F', CCOUNT)) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CC,
                                   FIRST_VALUE(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) FF,
                                   FIRST_VALUE(CCOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) XX,
                                   LAG(CCOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) YY,
                                   P.PRODCODE,
                                   P.BOOKNO
                              FROM PRODBOOK200807 P) P2
                     WHERE P1.PRODCODE = P2.PRODCODE
                       AND P1.BOOKNO = P2.BOOKNO
                       AND YY IS NOT NULL
                       AND CC + FF + XX <> ECOUNT)
 WHERE EXISTS (SELECT NULL
          FROM (SELECT SUM(DECODE(FLAG, 'E', -CCOUNT, 'F', CCOUNT)) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CC,
                       FIRST_VALUE(ECOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) FF,
                       FIRST_VALUE(CCOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) XX,
                       LAG(CCOUNT) OVER(PARTITION BY PRODCODE ORDER BY BOOKNO) YY,
                       P.PRODCODE,
                       P.BOOKNO
                  FROM PRODBOOK200807 P) P2
         WHERE P2.PRODCODE = P1.PRODCODE
           AND P2.BOOKNO = P1.BOOKNO
           AND yy IS NOT NULL
           AND CC + FF + XX <> ECOUNT);

Answer 23

我的Oracle版本是9.2.0.6.0  晕,冒似是版本的问题.http://blog.csdn.net/hrb_qiuyb/archive/2005/12/21/557949.aspx
我再想想有没有其他的办法.

Answer 24

前面写的那个函数根本就不对,理解错你的意思了
把函数改了一下只要传个prodcode进去就可以了
create or replace function updateProBook(procode varchar2) return varchar2 is
  intCount     number := 0;
  intCountLoop number := 0;
begin
  select count(*)
    into intCount
    from prodbook200807
   where prodcode = procode;
  dbms_output.put_line(intCount);
  loop
    update prodbook200807 t3
       set t3.ecount = (select t2.result
                          from (select t1.prodcode,
                                       t1.bookno,
                                       t1.ccount,
                                       t1.ecount,
                                       t1.ftype,
                                       t1.result
                                  from (select t.*,
                                               t.ecount result,
                                               row_number() over(partition by t.prodcode order by t.bookno) rn
                                        
                                          from prodbook200807 t
                                         where t.prodcode = procode
                                         order by t.bookno) t1
                                 where t1.rn = 1
                                union all
                                select t1.prodcode,
                                       t1.bookno,
                                       t1.ccount,
                                       t1.ecount,
                                       t1.ftype,
                                       t1.result
                                  from (select t.*,
                                               decode(t.ftype,
                                                      'E',
                                                      lag(t.ecount, 1, t.ecount)
                                                      over(partition by
                                                           t.prodcode order by
                                                           t.bookno) - ccount,
                                                      'F',
                                                      lag(t.ecount, 1, t.ecount)
                                                      over(partition by
                                                           t.prodcode order by
                                                           t.bookno) + ccount) result,
                                               row_number() over(partition by t.prodcode order by t.bookno) rn
                                          from prodbook200807 t
                                         where t.prodcode = procode
                                         order by t.bookno) t1
                                 where t1.rn > 1) t2
                         where t3.bookno = t2.bookno);
    commit;
    intCountLoop := intCountLoop + 1;
    dbms_output.put_line(intCountLoop);
    exit when intCountLoop = intCount;
  end loop;
  return 'true';
exception
  when others then
    return 'false';
end;

Answer 25

不用试下了，写存储过程去UPDATE吧
上面用到很多分析函数，8.17不支持的,像ROWNUMBER,LAG,LEAD,SUM OVER
之类

Answer 26

重新找了一台数据库服务器
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
测试 mantisXF 的程序 
成功了 谢谢 mantisXF  oracledbalgtu hebo2005 dbcxbj 

Answer 27

重新找了一台数据库服务器
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
测试 mantisXF 的程序 
成功了 谢谢 mantisXF  oracledbalgtu hebo2005 dbcxbj 

Answer 28

本人最后采用了mantisXF 
21楼所讲述的方法

Answer 29

最后用的版本
    update prodbook200807 p1
       set p1.ecount = (select p2.correct_ecount
                          from (select pp.prodcode,
                                       pp.bookno,
                                       first_values +
                                       (sum(new_ccount)
                                        over(partition by prodcode order by
                                             prodcode,
                                             bookno)) as correct_ecount
                                 from (select decode(p.FTYPE,
                                                     'E',
                                                     -ccount,
                                                     'G',
                                                     ccount,
                                                     'H',
                                                     -ccount,
                                                     'F',
                                                     ccount,
                                                     'I',
                                                     -ccount,
                                                     'O',
                                                     -ccount,
                                                     'P',
                                                     ccount,
                                                     0) as new_ccount,
                                              p.prodcode,
                                              p.bookno,
                                              lag(ecount) over(partition by prodcode order by prodcode, bookno) as prev,
                                              first_value(ecount) over(partition by prodcode order by prodcode, bookno) as first_values,
                                              p.ccount,
                                              p.ecount
                                         from prodbook200807 p) pp
                                where prev is not null) p2
                        where p2.prodcode = p1.prodcode
                          and p2.bookno = p1.bookno
                          and p2.correct_ecount <> p1.ecount)
    where exists
    (select 1
             from (select pp.prodcode,
                          pp.bookno,
                          first_values +
                          (sum(new_ccount)
                           over(partition by prodcode order by prodcode, bookno)) as correct_ecount
                     from (select decode(p.FTYPE,
                                         'E',
                                                     -ccount,
                                                     'G',
                                                     ccount,
                                                     'H',
                                                     -ccount,
                                                     'F',
                                                     ccount,
                                                     'I',
                                                     -ccount,
                                                     'O',
                                                     -ccount,
                                                     'P',
                                                     ccount,
                                         0) as new_ccount,
                                  p.prodcode,
                                  p.bookno,
                                  lag(ecount) over(partition by prodcode order by prodcode, bookno) as prev,
                                  first_value(ecount) over(partition by prodcode order by prodcode, bookno) as first_values,
                                  p.ccount,
                                  p.ecount
                             from prodbook200807 p) pp
                    where prev is not null) p2
            where p2.prodcode = p1.prodcode
              and p2.bookno = p1.bookno
              and p2.correct_ecount <> p1.ecount);-----------------------------------------按照业务要求加了一些其他的标志