sql - 如何从预生成的表中插入外键？

Question

我有 3 张桌子：

create table customer
( 
    customer_id integer primary key, 
    customer_first_name varchar2(50) not null, 
    customer_surrname varchar2(50) not null, 
    phone_number varchar2(15) not null, 
    customer_details varchar2(200) default 'There is no special notes' 
); 

create table place
( 
    table_number integer primary key, 
    table_details varchar2(200) default 'There is no details' 
); 

create table booking
( 
    booking_id integer primary key, 
    date_of_booking date, 
    number_of_persons number(2) not null, 
    customer_id integer not null, 
    foreign key(customer_id) references customer(customer_id), 
    table_number integer not null, 
    foreign key(table_number) references place(table_number) 
);

我必须使用这种生成器生成客户表：

set SERVEROUTPUT on format wrapped; 
set define off; 
drop sequence customer_seq; 
drop sequence place_seq; 

--CUSTOMER TABLE INSERT ROW GENERATOR 
create sequence customer_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE; 
CREATE OR REPLACE TRIGGER customer_id_trigger 
BEFORE INSERT ON customer 
FOR EACH ROW 
BEGIN 
SELECT customer_seq.nextval INTO :new.customer_id FROM dual; 
END; 
/ 

DELETE FROM customer; 
DECLARE 
TYPE TABSTR IS TABLE OF VARCHAR2(250); 
first_name TABSTR; 
surrname TABSTR; 
qname number(5); 
phonenum number(15); 
details TABSTR; 
BEGIN 
first_name := TABSTR ('Jhon','Poul','Jesica','Arnold','Max','Teemo','Tim','Mikel','Michael', 
'Kristian','Adela','Mari','Anastasia','Robert','Jim','Juana','Adward', 
'Jana','Ola','Kristine','Natali','Corey','Chester','Naomi','Chin-Chou');
surrname := TABSTR ('Grey','Brown','Robins','Chiho','Lee','Das','Edwins','Porter','Potter', 
'Dali','Jordan','Jordison','Fox','Washington','Bal','Pitney','Komarowski', 
'Banks','Albra','Shwiger'); 
details := TABSTR ('Exellent Customer','Good Customer','Always drunked','Left big tips', 
'Bad Customer','Did not pay last bill','New Customer','VIP client');
qname := 100; — CHANGE THIS TO MANAGE HOW MANY ROWS YOU WANT TO BE ADDED 
FOR i IN 1..qname LOOP 
phonenum := dbms_random.value(111111111,999999999); 
INSERT INTO customer VALUES (NULL, first_name(dbms_random.value(1,25)), 
surrname(dbms_random.value(1,20)), phonenum, details(dbms_random.value(1,8))); 
END LOOP; 
DBMS_OUTPUT.put_line('Customers done!'); 
END; 
/ 

--TABLE INSERT 
DELETE FROM place; 
create sequence place_seq start with 1 increment by 1; 
insert into place values (place_seq.nextval, 'Near the window'); 
insert into place values (place_seq.nextval, default); 
insert into place values (place_seq.nextval, 'Near the door'); 
insert into place values (place_seq.nextval, 'Near the window'); 
insert into place values (place_seq.nextval, 'Near the window'); 
insert into place values (place_seq.nextval, default); 
insert into place values (place_seq.nextval, 'Near the door'); 
insert into place values (place_seq.nextval, 'Big table'); 
insert into place values (place_seq.nextval, default); 
insert into place values (place_seq.nextval, 'Big table');

所以问题是如何在“客户”表中具有数字之一的“预订”表中插入 client_id？因为每次我在“客户”表中重新生成数据时，数字都会发生变化，所以我应该以某种方式选择数组中的数字，然后从这个数组中随机选择其中一个。问题是我真的不知道如何从表到数组进行选择。有人可以帮忙吗？

标签： sqldatabaseoracle

Answer 1

对于 PL/SQL 版本，您可以使用BULK COLLECT标准sys.odcinumberlist数组。

create sequence booking_seq start with 1 increment by 1; 
declare
  customerIds   sys.odcinumberlist;
  placeIds      sys.odcinumberlist;
  number_of_generated_records number := 150; -- number of records to be generated
begin
  -- fill the array of customer_id values
  select customer_id
  bulk collect into customerIds
  from customer;

  -- fill the array of place numbers
  select table_number
  bulk collect into placeIds
  from place;

  for i in 1..number_of_generated_records loop
    insert into booking(booking_id,date_of_booking,number_of_persons,customer_id,table_number)
    values(
      booking_seq.nextval, -- booking_id
      trunc(sysdate) + round(dbms_random.value(1,365)), -- date_of_booking
      round(dbms_random.value(1,99)), -- number_of_persons
      customerIds(round(dbms_random.value(1,customerIds.count))), -- customer_id
      placeIds(round(dbms_random.value(1,placeIds.count))) -- table_number
    );
  end loop;

end;

但是，对于您的情况，我更喜欢纯 sql：

insert into booking(booking_id,date_of_booking,number_of_persons,customer_id,table_number)
with 
  customer_subq as (
    select customer_id, row_number() over (order by customer_id) rn from customer
  ),
  place_subq as (
    select table_number, row_number() over (order by table_number) rn from place
  ),
  params as (
    select 1500 number_of_generated_records,
           (select count(1) from customer) customer_count,
           (select count(1) from place) place_count
    from   dual
  ),
  random_numbers as (
    select round(dbms_random.value(1,1000)) random_number1,
           round(dbms_random.value(1,1000)) random_number2,
           round(dbms_random.value(1,1000)) random_number3,
           round(dbms_random.value(1,1000)) random_number4
    from   dual,params
    connect by level <= number_of_generated_records
  )
  select booking_seq.nextval booking_id,
         trunc(sysdate) + mod(random_number1,365) date_of_booking,
         mod(random_number1,100) number_of_persons,
         customer_id,
         table_number
  from   random_numbers, 
         params,
         customer_subq,
         place_subq
  where  mod(random_number1,customer_count) + 1 = customer_subq.rn
  and    mod(random_number2,place_count) + 1 = place_subq.rn