Generating Data in PL/SQL

When testing software that has a database, it may be a requirement to see how it performs when there is a significant amount of data in it. In circumstances such as this, the data doesn’t necessarily have to be meaningful, so an easy way to generate data is programmatically using PL/SQL.

The example below shows how to generate data for two tables that have a master-detail relationship. The two tables are ‘department’ and ’employee’, where a ‘department’ record can have many ’employee’ records. The attributes contained in these two tables, along with their datatype, are as follows.

The underlined attributes (‘dept_id’ and ’emp_id’) shown above are the primary keys in each table that uniquely identify each record. The ‘dept_id’ in italics included in the ’employee’ table is the foreign key used to join the two together.

The SQL to create these two tables, along with an index for the foreign key, is shown below.

-- Create the tables.
CREATE TABLE department
(dept_id NUMBER CONSTRAINT department_pk PRIMARY KEY,
name VARCHAR2(20),
country VARCHAR2(20),
location NUMBER);

CREATE TABLE employee
(emp_id NUMBER CONSTRAINT employee_pk PRIMARY KEY,
fname VARCHAR2(20),
sname VARCHAR2(20),
telno NUMBER,
dept_id NUMBER,
CONSTRAINT employee_fk FOREIGN KEY (dept_id) REFERENCES department(dept_id));

-- Create the index on the foreign.
CREATE INDEX employee_fk ON employee(dept_id);

In order to provide some differentiation between the records, a database sequence will be needed for each table, which can be created as follows. A database sequence is basically a sequence number, which increments by one every time it is used. There use will be explained further shortly.

-- Create the sequences.
CREATE SEQUENCE s_department
START WITH 10000;

CREATE SEQUENCE s_employee
START WITH 100000;

You will notice that the sequence number for ‘department’ is specified to start at 10000 and for ’employee’ at 100000. If these weren’t specified then the sequences will automatically start at 1.

The following PL/SQL script contains two ‘for’ loops, one nested inside the other. The outer loop inserts a ‘department’ record with each iteration, whilst the inner loop inserts ten ’employee’ records for each ‘department’ record. The outer loop is set to iterate 1000 times. The number of iterations for each loop can be set to any number as desired.

DECLARE

   -- Table row count variables.
   v_deptrows NUMBER(10);
   v_emprows NUMBER(10);

BEGIN

   FOR d IN 1..1000 LOOP

      -- Insert a record into the department table.
      INSERT INTO department (dept_id, name, country, location)
      VALUES (s_department.nextval,
              'Department '||s_department.nextval,
              'Country ' ||s_department.nextval,
              substr(s_department.nextval,5,1));
      COMMIT;

      FOR e IN 1..10 LOOP

         -- Insert a record into the employee table.
         INSERT INTO employee (emp_id, fname, sname, telno, dept_id)
         VALUES (s_employee.nextval,
                 'FName ' ||s_employee.nextval,
                 'SName ' ||s_employee.nextval,
                 substr(s_employee.nextval,6,1),
                 s_department.currval);
         COMMIT;

      END LOOP;

   END LOOP;

   -- Display how many employees and departments there are.
   SELECT count(*) INTO v_deptrows FROM department;
   SELECT count(*) INTO v_emprows FROM employee;
   DBMS_OUTPUT.PUT_LINE ('Departments: '||v_deptrows);
   DBMS_OUTPUT.PUT_LINE ('Employees: '||v_emprows);

END;

The variables declared at the beginning of the script are to hold the number of records in the two tables. These are populated at the end of the script using the ‘SELECT..INTO’ statements and then displayed using ‘DBMS_OUTPUT.PUT_LINE’.

The ‘dept_id’ and ’emp_id’ primary keys are populated with the next value in their corresponding sequences, ‘s_department.nextval’ for ‘dept_id’ and ‘s_employee_id.nextval’ for ’emp_id’. The ‘dept_id’ foreign key in the ’employee’ table is populated with the current value of the ‘department’ sequence, ‘s_departement.currval’. All the ‘varchar2’ attributes are populated with a descriptive piece of text, for example ‘Department’ for the department name, concatenated with the next value of the corresponding sequence. Both ‘location’ in the ‘department’ table and ‘telno’ in the ’employee’ table are populated with a substring of the current value of their corresponding sequences.

The advantage of using sequences in this script is that it can be run as many times as desired and the records will always be unique.