Oracle 从笛卡尔插入N行

我有以下设置 (下面的测试用例)。如何修改查询以每个employee_id插入N个行数。

我想添加类似的内容，但无法为我的测试用例实现此功能

(通过 <= 子级从双连接选择级dbms_random.value(5,20) 子级)

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

-- create and populate an employee 
-- table with 50 rows
   
    CREATE TABLE employees AS
    SELECT level AS employee_id,
    
    CASE round(dbms_random.value(1,20)) 
            WHEN 1 THEN 'Albert' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Ty' 
            WHEN 5 THEN 'Andy' 
            WHEN 6 THEN 'Thomas' 
            WHEN 7 THEN 'Alan'
            WHEN 8 THEN 'Tara' 
            WHEN 9 THEN 'Cheryl' 
            WHEN 10 THEN 'Ed' 
            WHEN 11 THEN 'Steve'
            WHEN 12 THEN 'Mel' 
            WHEN 13 THEN 'Micheal' 
            WHEN 14 THEN 'Ron' 
            WHEN 15 THEN 'Donald'
            WHEN 16 THEN 'Donny' 
            WHEN 17 THEN 'Racheal' 
            WHEN 18 THEN 'Debbie' 
            WHEN 19 THEN 'Madison'
            WHEN  20 THEN 'Danny' 
         END AS first_name,

    CASE  round(dbms_random.value(1,20)) 
            WHEN 1 THEN 'Andrews' 
            WHEN 2 THEN 'Thorton' 
            WHEN 3 THEN 'Smith'
            WHEN 4 THEN 'Jones' 
            WHEN 5 THEN 'Ott' 
            WHEN 6 THEN 'Stevens' 
            WHEN 7 THEN 'Feldman'
            WHEN 8 THEN 'Stein' 
            WHEN 9 THEN 'Ross' 
            WHEN 10 THEN 'Eden' 
            WHEN 11 THEN 'Saltzman'
            WHEN 12 THEN 'Kramer'
            WHEN 13 THEN 'Monroe' 
            WHEN 14 THEN 'Hanks' 
            WHEN 15 THEN 'Dunn'
            WHEN 16 THEN 'Dunbar' 
            WHEN 17 THEN 'Rucker' 
            WHEN 18 THEN 'Silverberg' 
            WHEN 19 THEN 'Daniels'
            WHEN  20 THEN 'Kahn' 
         END AS last_name, 
        
    dbms_random.string('X',        dbms_random.value(5, 10))  AS card_num

    FROM   dual
    CONNECT BY level <= 50;


    ALTER TABLE employees
         ADD ( CONSTRAINT employee_id_pk
       PRIMARY KEY (employee_id));

    -- create and populate a location 
    -- table with 10 rows. Randomly
    -- make some types 'A' for access
    -- 'T' for time and attendance,
    -- 'G' for guard tour.

    CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'G' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 25;


     ALTER TABLE locations 
         ADD ( CONSTRAINT location_id_pk
       PRIMARY KEY (location_id));


    create table access_history(
       employee_id NUMBER(6), 
       card_num varchar2(10),
       location_id number(4),
       access_date date,
       processed NUMBER(1) default 0
    );

    INSERT into access_history
     (employee_id,
       card_num,
      location_id,
       access_date)
       with all_combos as
          ( select e.*, l.*
          from   employees e, locations l
         )

        select *
         from (
           select employee_id, card_num,
                   location_id, 
                 trunc(sysdate) +     dbms_random.value (0, 2) + dbms_random.value (0, .75)
            from   all_combos
            order by dbms_random.value
      );
 

下面是获取表中的每一行n-m行的一种方法:

-创建表/生成20行
-为每个员工调用dbms_random，为您提供该行的N行
-将第一个表的行号 <= N的两个连接起来

例如:

CREATE TABLE employees AS
SELECT level AS employee_id,
CASE round(dbms_random.value(1,20)) 
      WHEN 1 THEN 'Albert' 
      WHEN 2 THEN 'Tom' 
      WHEN 3 THEN 'Anna'
      WHEN 4 THEN 'Ty' 
      WHEN 5 THEN 'Andy' 
      WHEN 6 THEN 'Thomas' 
      WHEN 7 THEN 'Alan'
      WHEN 8 THEN 'Tara' 
      WHEN 9 THEN 'Cheryl' 
      WHEN 10 THEN 'Ed' 
      WHEN 11 THEN 'Steve'
      WHEN 12 THEN 'Mel' 
      WHEN 13 THEN 'Micheal' 
      WHEN 14 THEN 'Ron' 
      WHEN 15 THEN 'Donald'
      WHEN 16 THEN 'Donny' 
      WHEN 17 THEN 'Racheal' 
      WHEN 18 THEN 'Debbie' 
      WHEN 19 THEN 'Madison'
      WHEN  20 THEN 'Danny' 
   END AS first_name,
CASE  round(dbms_random.value(1,20)) 
      WHEN 1 THEN 'Andrews' 
      WHEN 2 THEN 'Thorton' 
      WHEN 3 THEN 'Smith'
      WHEN 4 THEN 'Jones' 
      WHEN 5 THEN 'Ott' 
      WHEN 6 THEN 'Stevens' 
      WHEN 7 THEN 'Feldman'
      WHEN 8 THEN 'Stein' 
      WHEN 9 THEN 'Ross' 
      WHEN 10 THEN 'Eden' 
      WHEN 11 THEN 'Saltzman'
      WHEN 12 THEN 'Kramer'
      WHEN 13 THEN 'Monroe' 
      WHEN 14 THEN 'Hanks' 
      WHEN 15 THEN 'Dunn'
      WHEN 16 THEN 'Dunbar' 
      WHEN 17 THEN 'Rucker' 
      WHEN 18 THEN 'Silverberg' 
      WHEN 19 THEN 'Daniels'
      WHEN  20 THEN 'Kahn' 
   END AS last_name, 
  dbms_random.string('X',        dbms_random.value(5, 10))  AS card_num
FROM   dual
CONNECT BY level <= 50;

with rws as (
  select level rn 
  from   dual
  connect by level <= 20
), emps as (
  select /*+ materialize */e.*, round ( dbms_random.value(5,20) ) n
  from   employees e
)
  select employee_id, count (*) rw_count
  from   rws
  join   emps e
  on     rn <= n
  group  by employee_id;

EMPLOYEE_ID    RW_COUNT   
             6           9 
            14           7 
            23          17 
            27          11 
            50          17 
             1          19 
             7          20 
            15          15 
            31           7 
...

实现提示是确保数据库只为每个员工调用dbms_random一次。