For each company entry in a table Company, I want to create a random number of rows between 50 and 250 in table Employee in PL/SQL.

CodePudding user response：

Here's one option, based on data in Scott's sample schema.

Departments (that's your company):

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Target table:

SQL> CREATE TABLE employee
  2  (
  3     deptno   NUMBER,
  4     empno    NUMBER PRIMARY KEY,
  5     ename    VARCHAR2 (10),
  6     salary   NUMBER
  7  );

Table created.

Sequence (for primary key values):

SQL> CREATE SEQUENCE seq_e;

Sequence created.

Here's the procedure: for each department, it creates L_ROWS number of rows (line #8) (I restricted it to a number between 1 and 5; your boundaries would be 50 and 250). It also creates random names (line #16) and salaries (line #17):

SQL> DECLARE
  2     l_rows  NUMBER;
  3  BEGIN
  4     DELETE FROM employee;
  5
  6     FOR cur_d IN (SELECT deptno FROM dept)
  7     LOOP
  8        l_rows := ROUND (DBMS_RANDOM.VALUE (1, 5));
  9
 10        INSERT INTO employee (deptno,
 11                              empno,
 12                              ename,
 13                              salary)
 14               SELECT cur_d.deptno,
 15                      seq_e.NEXTVAL,
 16                      DBMS_RANDOM.string ('x', 7),
 17                      ROUND (DBMS_RANDOM.VALUE (100, 900))
 18                 FROM DUAL
 19           CONNECT BY LEVEL <= l_rows;
 20     END LOOP;
 21  END;
 22  /

PL/SQL procedure successfully completed.

Result:

SQL>   SELECT *
  2      FROM employee
  3  ORDER BY deptno, empno;

    DEPTNO      EMPNO ENAME          SALARY
---------- ---------- ---------- ----------
        10          1 ZMO4RFN           830
        10          2 AEXL34I           589
        10          3 SI6X38Z           191
        10          4 59EWI42           397
        20          5 DBAMQDA           559
        20          6 79X78JV           491
        30          7 56ITU5V           178
        30          8 09KPAIS           297
        30          9 VQUVWDP           446
        40         10 AHJZNVJ           182
        40         11 0XWI3GC           553
        40         12 7GNTCG4           629
        40         13 23G871Z           480

13 rows selected.

SQL>

CodePudding user response：

Adapting my answer to this question:

INSERT INTO employees (id, first_name, last_name, department_id)
SELECT employees__id__seq.NEXTVAL,
       CASE FLOOR(DBMS_RANDOM.VALUE(1,6))
       WHEN 1 THEN 'Faith'
       WHEN 2 THEN 'Tom'
       WHEN 3 THEN 'Anna'
       WHEN 4 THEN 'Lisa'
       WHEN 5 THEN 'Andy'
       END,
       CASE FLOOR(DBMS_RANDOM.VALUE(1,6))
       WHEN 1 THEN 'Andrews'
       WHEN 2 THEN 'Thorton'
       WHEN 3 THEN 'Smith'
       WHEN 4 THEN 'Jones'
       WHEN 5 THEN 'Beirs'
       END,
       d.id
FROM   ( SELECT id,
                FLOOR(DBMS_RANDOM.VALUE(50,251)) AS num_employees
         FROM   departments
         ORDER BY ROWNUM -- Materialize the sub-query so the random values are individually
                         -- generated.
       ) d
       CROSS JOIN LATERAL (
         SELECT LEVEL
         FROM   DUAL
         CONNECT BY LEVEL <= d.num_employees
       );

fiddle