I'm trying to create a process that populates a student table. I want to be able to create a different combination of a student's first/last name and dob every time I run the query.
The code below appears to work fine as it generates 5 names. My first question is can this be modified to generate N NUMBER of rows sat for example 20. I tried using CONNECT by level <=20 but that gives me a syntax error.
Secondly, know the random_date function works
Select random_date(DATE '2001-01-01', DATE '2001-12-31') from dual
17-NOV-2001 08:31:16
But I can't seem to incorporate into my SQL.
Any help would be greatly appreciated. Thanks in advance for your expertise and time
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE OR REPLACE FUNCTION random_date(
p_from IN DATE,
p_to IN DATE
) RETURN DATE
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to - p_from + 1 );
END random_date;
/
CREATE TABLE students (
student_id number(*,0),
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
dob DATE,
constraint teacher_pk primary key (student_id));
WITH raw_names (first_name, last_name) AS
(
SELECT 'Faith', 'Andrews' FROM dual UNION ALL
SELECT 'Tom', 'Thornton' FROM dual UNION ALL
SELECT 'Anna', 'Smith' FROM dual UNION ALL
SELECT 'Lisa', 'Jones' FROM dual UNION ALL
SELECT 'Andy', 'Beirs' FROM dual
)
, numbered_names AS
(
SELECT first_name, last_name
, ROW_NUMBER () OVER (ORDER BY dbms_random.value (0, 1)) AS first_num
, ROW_NUMBER () OVER (ORDER BY dbms_random.value (0, 2)) AS last_num
FROM raw_names
)
SELECT fn.first_num AS student_id
, fn.first_name
, ln.last_name
FROM numbered_names fn
JOIN numbered_names ln ON ln.last_num = fn.first_num
ORDER BY student_id
;
Aucun commentaire:
Enregistrer un commentaire