Dbms_random GENERATED different results-CodePudding

I'm putting together sample test data using SQL.

Is there a way in SQL I can get different results every time I rerun the same SQL statement.

I am familiar with using a seed but I think that only works with PLSQL, which I would like to avoid if possible.

Is there someway the SQL could be adjusted to emulate a seed and provide different results for every new run?


CREATE TABLE students (
   student_id  number(*,0),
  first_name VARCHAR(25) NOT NULL,
  last_name VARCHAR(25) NOT NULL,
  active VARCHAR2(1) DEFAULT 'Y',
constraint student_pk primary key (student_id));


 insert into students (student_id, first_name, last_name, active)
        select
          level,       
CASE round(dbms_random.value(1,25)) 
            WHEN 1 THEN 'Faith' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Lisa' 
            WHEN 5 THEN 'Andy' 
            WHEN 6 THEN 'Thomas' 
            WHEN 7 THEN 'Alan'
            WHEN 8 THEN 'Keith' 
            WHEN 9 THEN 'Cheryl' 
            WHEN 10 THEN 'Chester' 
            WHEN 11 THEN 'Steve'
            WHEN 12 THEN 'Mel' 
            WHEN 13 THEN 'Micheal' 
            WHEN 14 THEN 'Ron' 
            WHEN 15 THEN 'Donald'
            WHEN 16 THEN 'Carolyn' 
            WHEN 17 THEN 'Racheal' 
            WHEN 18 THEN 'Debbie' 
            WHEN 19 THEN 'Madison'
            WHEN  20 THEN 'Danny' 
            WHEN 21 THEN 'Claude' 
            WHEN 22 THEN 'Peter' 
            WHEN 23 THEN 'Edna' 
            WHEN 24 THEN 'Anita'
            WHEN 25 THEN 'Mindy' 
         END AS first_name,

    CASE  round(dbms_random.value(1,25)) 
            WHEN 1 THEN 'Andrews' 
            WHEN 2 THEN 'Thorton' 
            WHEN 3 THEN 'Smith'
            WHEN 4 THEN 'Jones' 
            WHEN 5 THEN 'Beirs' 
            WHEN 6 THEN 'Stevens' 
            WHEN 7 THEN 'Feldman'
            WHEN 8 THEN 'Stein' 
            WHEN 9 THEN 'Ross' 
            WHEN 10 THEN 'Mednick' 
            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 'Kern' 
            WHEN  21 THEN 'Saladino' 
            WHEN  22 THEN 'Rice'
            WHEN  23 THEN 'Sanford' 
            WHEN  24 THEN 'Krantz'
            WHEN  25 THEN 'Roth' 
         END AS last_name, 
        
          CASE MOD(LEVEL, 10)
           WHEN 0
          THEN 'N'
          ELSE 'Y'
         end
      from dual
      connect by level <= 25;

CodePudding user response：

The solution is provided here

Idea is to randomize the order rather then the actual values. Generate a series of distinct numbers and just radomize its order.

Using same approach it can be included to insert.

Example -

insert into students (student_id, first_name)
WITH cte AS ( SELECT  level lvl FROM dual CONNECT BY level <= 7 )
SELECT rownum, case t1.lvl
            WHEN 1 THEN 'Faith' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Lisa' 
            WHEN 5 THEN 'Andy' 
            WHEN 6 THEN 'Thomas' 
            WHEN 7 THEN 'Alan'
            end first_name
FROM 
(SELECT lvl FROM cte 
ORDER BY dbms_random.value() ) t1;

Refer fiddle here.

CodePudding user response：

You do not need to use CASE expressions. If you want a unique name each time then you need one table with a list of first names and one with a list of last names and then CROSS JOIN them, order the rows randomly and insert them:

insert into students (student_id, first_name, last_name, active)
WITH first_names (first_name) AS (
  SELECT COLUMN_VALUE
  FROM   TABLE(
           SYS.ODCIVARCHAR2LIST(
             'Faith', 'Tom', 'Anna', 'Lisa', 'Andy', 'Thomas' -- ...
           )
         )
),
last_names (last_name) AS (
  SELECT COLUMN_VALUE
  FROM   TABLE(
           SYS.ODCIVARCHAR2LIST(
             'Andrews', 'Thorton', 'Smith', 'Jones', 'Biers', 'Stevens', 'Feldman' -- ...
           )
         )
),
random (first_name, last_name) AS (
  SELECT first_name, last_name
  FROM   first_names CROSS JOIN last_names
  ORDER BY DBMS_RANDOM.VALUE()
)
SELECT ROWNUM,
       first_name,
       last_name,
       CASE MOD(ROWNUM, 10) WHEN 0 THEN 'N' ELSE 'Y' END
FROM   random
WHERE  ROWNUM <= 20 -- Get the first 20 rows
;

db<>fiddle here