Oracle批量插入随机数时每行列值均相同的问题排查

这个问题的核心在于Oracle中INSERT ALL的执行逻辑，以及dbms_random.value()函数的计算时机。

问题原因 #

当你使用INSERT ALL时，Oracle会先执行后面的SELECT * FROM DUAL语句，把查询结果中的所有表达式（包括你的dbms_random.value()调用）先计算一遍，然后再把这个计算好的结果集复用给所有的INTO子句。哪怕你在VALUES里写了20次round(dbms_random.value(1,5))，这些函数调用其实只会在解析SELECT部分时被执行一次，最终所有列都会拿到同一个随机数。

你套了FOR循环也没用，因为每次循环里的INSERT ALL还是会触发这个逻辑：SELECT * FROM DUAL生成一行数据，所有随机函数只计算一次，然后填充到所有列里。

解决办法 #

最简单的修复方式是去掉INSERT ALL，直接使用普通的INSERT语句。普通INSERT在执行时，会逐个计算VALUES子句里的每个表达式，这样每个dbms_random.value()都会被独立调用，生成不同的随机数。

修正后的代码如下：

BEGIN
  FOR i IN 163 .. 400 LOOP
    INSERT INTO results (student_id,OPN1,OPN2,OPN3,OPN4,AGG1,AGG2,AGG3,AGG4,NEU1,NEU2,NEU3,NEU4,EXT1,EXT2,EXT3,EXT4,CSN1,CSN2,CSN3,CSN4)
    VALUES (
      i,
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5)),
      round(dbms_random.value(1,5))
    );
  END LOOP;
  COMMIT;
END;
/

另外，如果你想要更高效的批量插入（避免循环里每次INSERT的开销），也可以用CONNECT BY生成多行数据，结合dbms_random.value()一次性插入，比如：

INSERT INTO results (student_id,OPN1,OPN2,OPN3,OPN4,AGG1,AGG2,AGG3,AGG4,NEU1,NEU2,NEU3,NEU4,EXT1,EXT2,EXT3,EXT4,CSN1,CSN2,CSN3,CSN4)
SELECT
  162 + level,
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5))
FROM dual
CONNECT BY level <= 400 - 163 + 1;
COMMIT;

这种方式不需要PL/SQL循环，直接用SQL一次性生成所有需要的行，效率更高，而且每个随机函数在每行里都会被独立计算，不会出现值重复的问题。

内容的提问来源于stack exchange，提问作者Phaki