Is it possible to Insert multiple rows with same user and differrent id

Question

I just started with Oracle-Sql and apex , I have stumbled into this code/answer that another user made (credits to the user) and tweaked it a bit. http://sqlfiddle.com/#!4/fa7342/11

• this code create 'x' rows/appointments defined by the multiple conditions

what I'm trying to do is to insert into a table 'x' rows/appointments with :

1. same user_id(not pk)
2. different appointment_id( pk - sequence and incremented by 'x' appointments')

If the input is :

interval_type VARCHAR2(20) := 'daily';
  start_date    DATE         := DATE '2021-07-15';
  number_of_appointments number := 3;
  appointent_id_number number := sequence.nextval;
  id_client number := 1012;

output should be :

APPOINTMENT_DATE	APPOINTMENT_ID	ID_CLIENT
2021-07-16T00:00:00Z	1	1012
2021-07-17T00:00:00Z	2	1012
2021-07-18T00:00:00Z	3	1012

query :

DECLARE
  interval_type VARCHAR2(20) := 'daily';
  start_date    DATE         := DATE '2021-07-15';
  number_of_appointments number := 3;
  appointent_id_number number := sequence.nextval
  id_client number := 1012;
BEGIN
  INSERT INTO table_name (appointment_date)
  SELECT CASE interval_type
         WHEN 'daily'
         THEN start_date + INTERVAL '1' DAY * LEVEL
         WHEN 'weekly'
         THEN start_date + INTERVAL '7' DAY * LEVEL
         WHEN 'monthly'
         THEN ADD_MONTHS( start_date, LEVEL )
         END
  FROM   DUAL
  CONNECT BY
         LEVEL <= number_of_appointments;
END;
/

Answer 1

It is easier to let the database generate your primary key so you don't have to worry about that in your code. In the code below I'm using an identity column as primary key - in that case a sequence doesn't need to be created manually. However if you're really on 11g (consider upgrading - that version is very very old) identity columns won't work. Code for that case is at the bottom.

create table appointments_table (
    id                             number generated by default on null as identity 
                                   constraint appointments_table_id_pk primary key,
    appointment_date               date,
    id_client                      number
)
;

DECLARE
  interval_type VARCHAR2(20) := 'daily';
  start_date    DATE         := DATE '2021-07-15';
  number_of_appointments number := 3;
  l_id_client number := 1012;
BEGIN
  INSERT INTO appointments_table (appointment_date, id_client)
  SELECT CASE interval_type
         WHEN 'daily'
         THEN start_date + INTERVAL '1' DAY * LEVEL
         WHEN 'weekly'
         THEN start_date + INTERVAL '7' DAY * LEVEL
         WHEN 'monthly'
         THEN ADD_MONTHS( start_date, LEVEL )
         END,
         l_id_client
  FROM   DUAL
  CONNECT BY
         LEVEL <= number_of_appointments;
END;
/

select * from appointments_table;

        ID APPOINTMENT  ID_CLIENT
---------- ----------- ----------
         1 16-JUL-2021       1012
         2 17-JUL-2021       1012
         3 18-JUL-2021       1012

If the version of oracle you are on does not support identity columns (available from 12.1) then this is how you can create your table:

create sequence appointments_table_seq;

create table appointments_table (
    id                             number default appointments_table_seq.NEXTVAL 
                                   constraint appointments_table_id_pk primary key,
    appointment_date_dt            date,
    id_client                      number
)
;