Oracle SQL生成N条随机学生测试数据的实现问题咨询

你原有代码仅能生成5条记录的核心原因是逻辑被仅含5条固定数据的姓名基础表限制，直接在外层拼接CONNECT BY会和现有自连接排序逻辑冲突触发语法错误，两个问题的修正方式如下： #

1. 自定义N条记录生成逻辑 #

不要在原有自连接逻辑外层直接加层级查询，先单独用CONNECT BY做独立的行生成器，自定义需要的记录条数（比如20条就写level <=20），再逐行随机抽取姓名池里的名和姓，既不受基础姓名池行数限制，也能保证每次执行的姓名组合完全随机。

2. 随机日期函数整合方式 #

你写的random_date函数本身没有问题，直接在最终查询的字段列表中调用，传入出生日期的起止日期参数即可，不需要额外做表关联。 #

修正后完整可运行代码 #

-- 原有会话格式设置保持不变
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

-- 原有随机日期函数保持不变
CREATE OR REPLACE FUNCTION random_date(
      p_from IN DATE,
      p_to   IN DATE
    ) RETURN DATE
   IS
   BEGIN
      RETURN p_from + DBMS_RANDOM.VALUE() * (p_to - p_from + 1 );
END random_date;
/

-- 建表语句修正了原笔误的主键名（原主键名teacher_pk和学生表不匹配，不修改也可正常运行）
CREATE TABLE students (
   student_id  number(*,0),
  first_name VARCHAR(25) NOT NULL,
  last_name VARCHAR(25) NOT NULL,
   dob DATE,
  constraint student_pk primary key (student_id)
);

-- 插入自定义条数的随机学生数据，以下示例生成20条，调整row_generator中level <=后的数字即可修改生成条数
INSERT INTO students(student_id, first_name, last_name, dob)
WITH raw_names (first_name, last_name)  AS
(
  SELECT 'Faith', 'Andrews'  FROM dual UNION ALL
  SELECT 'Tom',   'Thornton' FROM dual UNION ALL
  SELECT 'Anna',  'Smith'    FROM dual UNION ALL
  SELECT 'Lisa',  'Jones'    FROM dual UNION ALL
  SELECT 'Andy',  'Beirs'    FROM dual
  -- 要更丰富的姓名组合，直接在这里追加UNION ALL添加新的名、姓即可
),
row_generator AS (
  SELECT level AS student_id
  FROM dual
  CONNECT BY level <= 20
)
SELECT 
  rg.student_id,
  -- 随机抽取名字
  (SELECT first_name FROM raw_names ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 1 ROW ONLY) AS first_name,
  -- 随机抽取姓氏
  (SELECT last_name FROM raw_names ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 1 ROW ONLY) AS last_name,
  -- 调用自定义函数生成2001年范围内的随机出生日期
  random_date(DATE '2001-01-01', DATE '2001-12-31') AS dob
FROM row_generator rg;

COMMIT;

兼容说明 #

如果你使用的是11g及更早不支持FETCH FIRST 1 ROW ONLY语法的Oracle版本，把查询里随机取名字、姓氏的两行替换成以下写法即可：

MAX(first_name) KEEP (DENSE_RANK FIRST ORDER BY DBMS_RANDOM.VALUE) AS first_name,
MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY DBMS_RANDOM.VALUE) AS last_name,

如果不需要插入表，仅需要查询返回随机学生结果集，删掉INSERT INTO students(...)和COMMIT行，直接执行WITH开头的SELECT语句即可。 #

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