Generating random names to populate a table-CodePudding

I have some SQL code, which generates random names and appears to be working well. See below for output.


WITH cte AS ( SELECT  level lvl FROM dual CONNECT BY level <= 5 )
        SELECT 
          case t1.lvl
               WHEN 1 THEN 'Faith'
                WHEN 2 THEN 'Tom'
                WHEN 3 THEN 'Anna'
                WHEN 4 THEN 'Lisa'
                WHEN 5 THEN 'Andy'
                end first_name,
         case t1.lvl
              WHEN 1 THEN 'Andrews'
               WHEN 2 THEN 'Thorton'
               WHEN 3 THEN 'Smith'
               WHEN 4 THEN 'Jones'
               WHEN 5 THEN 'Beirs'
               end last_name
   FROM (
   SELECT lvl
    FROM cte
    ORDER BY dbms_random.value(0,sign(lvl))
   ) t1


FIRST_NAME    LAST_NAME
Tom    Thorton
Anna    Smith
Andy    Beirs
Lisa    Jones
Faith    Andrews

FIRST_NAME    LAST_NAME
Lisa    Jones
Anna    Smith
Faith    Andrews
Andy    Beirs
Tom    Thorton

How can this be turned into a function or some sort of other mechanism where I can use the VALUES to populate some other table like customers.

I have PLSQL loop below with question Mark's that I want to substitute with random names.


CREATE TABLE CUSTOMERS (
 customer_id NUMBER,
 first_name VARCHAR2 (20),
 last_name VARCHAR2 (20));

begin
      for i in 1 .. 17 loop 
         INSERT into customers (customer_id,  first_name, last_name) 
      VALUES (i, '???, '???');
              end loop;
    end;
/

CodePudding user response：

Just use your CASE expressions:

CREATE SEQUENCE customers__customer_id__seq;

INSERT INTO customers (customer_id, first_name, last_name)
SELECT customers__customer_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
FROM   DUAL
CONNECT BY LEVEL <= 17;

If you do want to create functions then:

CREATE PACKAGE faker IS
  FUNCTION random_first_name(
    gender        IN VARCHAR2 DEFAULT NULL,
    percentage_mf IN NUMBER   DEFAULT 50
  ) RETURN VARCHAR2;

  FUNCTION random_last_name RETURN VARCHAR2;
END;
/

CREATE PACKAGE BODY faker IS
  first_names_male SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
    'Tom', 'Andy'
  );
  first_names_female SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
    'Alice', 'Anna', 'Beryl', 'Carol', 'Debra', 'Emily', 'Faith'
  );
  last_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
    'Andrews', 'Thorton', 'Smith', 'Jones', 'Biers'
  );
  
  FUNCTION random_first_name(
    gender        IN VARCHAR2 DEFAULT NULL,
    percentage_mf IN NUMBER   DEFAULT 50
  ) RETURN VARCHAR2
  IS
  BEGIN
    IF UPPER(gender) LIKE 'M%' THEN
      RETURN first_names_male(FLOOR(DBMS_RANDOM.VALUE(1, first_names_male.COUNT   1)));
    ELSIF UPPER(gender) LIKE 'F%' THEN
      RETURN first_names_female(FLOOR(DBMS_RANDOM.VALUE(1, first_names_female.COUNT   1)));
    ELSIF DBMS_RANDOM.VALUE(0, 100) < percentage_mf THEN
      RETURN random_first_name('M');
    ELSE
      RETURN random_first_name('F');
    END IF;
  END;

  FUNCTION random_last_name RETURN VARCHAR2
  IS
  BEGIN
    RETURN last_names(FLOOR(DBMS_RANDOM.VALUE(1, last_names.COUNT   1)));
  END;
END;
/

Then you can use:

INSERT INTO customers (customer_id, first_name, last_name)
SELECT customers__customer_id__seq.NEXTVAL,
       faker.random_first_name(),
       faker.random_last_name()
FROM   DUAL
CONNECT BY LEVEL <= 17;

fiddle