请问,如何实现一个UPDATE的SQL文里每一万条COMMIT一次

Question

Oracle的机制决定了这是不可能的，一句DML语句（insert,update,delete)只能commit一次。

Answer 1

可以用Where条件过滤一下。比如你的ID是从1到100万，就可以写多个Update的语句然后Commit。

Answer 2

因为我是写在存储过程里的,那可不可以用LOOP,实现呢?
但还是要保证只有一条UPDATE的SQL文.

Answer 3

UPDATE 
SET XXX=333
WHERE ROWNUM 《10000  每次只更新一万条

Answer 4

楼上的,这种方式,如果只是单独一个SQL文,不放入LOOP中,那么只能更新前一万条.
如果放入LOOP中,那么是死循环.改成如下的形式倒是可以,只不过我现在的UPDATE语句要UPDATE的列的值不固定,类似SET XXX=DECODE(YYY,3,ZZZ,4,AAA,5,BBB,XXX)这种形式,所以用下面的这种方式,在WHERE条件里追加相反情况的条件,可能能些出来,但得想得特别清楚.所以求一个更好的方式.    LOOP
        UPDATE TABLE
        SET XXX=333
        WHERE XXX<>333 AND ROWNUM<=10000  
        IF SQL%ROWCOUNT < 10000 THEN
            EXIT;
        END IF;
        COMMIT;
    END LOOP;
    COMMIT;

Answer 5

1条语句是不可能的了，如果分批次提交肯定还是需要用游标的，用bulk collect ... limit 10000 可以使循环每10000条循环一次declare 
  -- 数组变量，保存查询条件 
  TYPE t_id IS TABLE OF test.id%TYPE;
  v_t_id t_id;  CURSOR c IS
    SELECT id FROM test where xxx <> 333;
BEGIN
  OPEN c;
  LOOP
      -- 批量更新，一次更新10000条数据
      fetch c bulk collect into v_t_id LIMIT 10000;
      -- 这里用forall效率更高
      FORALL i IN 1 .. v_t_id.COUNT
       UPDATE test SET xxx = 333 WHERE id = v_t_id(i);
      
      -- 提交
      COMMIT;
      -- 循环退出
      exit when c%NOTFOUND;
      
  END LOOP;
  
  COMMIT;
end;

Answer 6

上面这位大哥写的循环内commit，游标都释放了，只能commit前10000条，如果循环内commit，只能用序号循环

Answer 7

楼上这位兄弟没明白我的意思吧，这样吧，我把测试数据放上来就清楚了：这里我插入1999条记录，然后把记录中xxx为非33的全部更新为33，分20次提交，1次提交100条，比较下更新前后xxx为33的记录个数就知道对不对了SQL> CREATE TABLE test (ID VARCHAR2(20),xxx NUMBER);Table createdSQL> INSERT INTO test SELECT lpad(ROWNUM,4,'0'),mod(ROWNUM,34) FROM dual CONNECT BY LEVEL < 2000;1999 rows insertedSQL> commit;Commit completeSQL> set serverout on;
SQL> select count(*) from test where id = 33;  COUNT(*)
----------
         1SQL> select count(*) from test where xxx = 33;  COUNT(*)
----------
        58SQL> select count(*) from test where xxx <> 33;  COUNT(*)
----------
      1941SQL> 
SQL> declare
  2    -- 数组变量，保存查询条件
  3    TYPE t_id IS TABLE OF test.id%TYPE;
  4    v_t_id t_id;
  5  
  6    CURSOR c IS
  7      SELECT id FROM test where xxx <> 33;
  8    -- 循环次数
  9    cnt NUMBER := 0;
 10  BEGIN
 11    OPEN c;
 12    LOOP
 13        cnt := cnt + 1;
 14        -- 批量更新，一次更新10000条数据
 15        fetch c bulk collect into v_t_id LIMIT 100;
 16        -- 这里用forall效率更高
 17        FORALL i IN 1 .. v_t_id.COUNT
 18         UPDATE test SET xxx = 33 WHERE id = v_t_id(i);
 19  
 20        -- 提交
 21        COMMIT;
 22        -- 循环退出
 23        exit when c%NOTFOUND;
 24  
 25    END LOOP;
 26  
 27    dbms_output.put_line('循环次数：' || cnt);
 28  
 29    CLOSE c;
 30    COMMIT;
 31  end;
 32  /
循环次数：20PL/SQL procedure successfully completedSQL> select count(*) from test where xxx = 33;  COUNT(*)
----------
      1999SQL> select count(*) from test where xxx <> 33;  COUNT(*)
----------
         0SQL>