Oracle SQL随机功能异常：插入数据时Position字段值重复

问题描述 #

我创建了Position lookup表作为数据字典，在向SEASON_STATISTICS表插入75条测试数据时，期望随机获取Position表中的职位值，但所有行的Position字段均为"First Base"。需要排查并修正代码中标记***的行，实现职位值的随机插入。

问题原因 #

原代码中标记***的子查询(SELECT Position FROM Position WHERE PositionId = (SELECT ROUND(dbms_random.value(2, 9)) FROM Dual))存在优化问题：在CONNECT BY生成多行数据的场景下，内层的随机值子查询只会执行一次，生成一个固定的PositionId，导致所有行都使用同一个职位值。

修正方案 #

将标记的行替换为每行都会重新执行随机选择逻辑*的写法，确保每一行插入时都能随机获取一个Position表中的职位。

修正后的完整INSERT代码 #

INSERT INTO SEASON_STATISTICS (Season_StatisticsId, EMPLOYEEID, GAMES, YEAR, ATBATS, HITS, HR, RBI, AVG, POSITION, TEAMID, LEAGUE) 
SELECT SEASON_STATS_SEQ.nextval, 
    ROUND(dbms_random.value(1, 9)), -- EmployeeId
    ROUND(dbms_random.value(1, 56)), -- Games
    '2022', 
    ROUND(dbms_random.value(50, 175)), -- At Bats
    ROUND(dbms_random.value(15, 45)),   -- Hits 
    ROUND(dbms_random.value(1, 17)),    -- HR
    ROUND(dbms_random.value(1, 64)),    -- RBI
    ROUND(dbms_random.value(100, 350)) / 1000,                             -- Avg
    (SELECT Position FROM Position ORDER BY dbms_random.value FETCH FIRST 1 ROW ONLY), -- 修正后的随机职位获取逻辑
    ROUND(dbms_random.value(1, 10)), 
    'NL'
FROM DUAL
CONNECT BY LEVEL <= 75;

兼容旧版本Oracle的写法（12c之前） #

如果你的Oracle版本不支持FETCH FIRST语法，可以改用以下方式：

(SELECT Position FROM (SELECT Position FROM Position ORDER BY dbms_random.value) WHERE ROWNUM = 1)

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