mardi 21 juin 2022

Dbms_random GENERATED different results

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;




Aucun commentaire:

Enregistrer un commentaire