plsql - can anyone help me out how do we reset a sequence in oracle 11 g database at two particular times in one single day?

Question

Let me explain you the scenario of hospital management tool.

In every hospital, we have n no. of doctors, n no. of admins, n no. of security, etc departments respectively. Each and every hospital we have an out-patient consultations in the morning time approximately around 8:00 am to 10:00 am, from 10:00 am to evening 5:00 pm doctors will undertake operations and treatments for In-patients in "Intensive care unit" (ICU). So now after 5:00 pm again the doctors will have an out-patients consultation in the hospital from 18:00 pm to 20:00 pm.

Now, let me explain the same in technical terminology to you.

When the out-patients come ask a token number of so and so doctor. The admin will then select particular department in the UI and select particular doctor as per patient's problem. For this, i'm maintaining a each doctor's tables in the database which doctor name itself..

example :

1)Neurology Department

i) Dr. Sarath Kumar
ii) Dr. anil kumar

2)Cardiology Department

i) Dr. Madhu
ii) Dr. Anji Reddy

3)Orthopedics Department

i) Dr. Murali
ii) Dr. Sirisha

etc...

Creation of a doctor table :

create table sarath_Kumar(token_no not null primary key,
                          patient_name char(50) not null ,
                          patient_age number(3) not null ,
                          patient_phonenumber number(12) not null unique ,
                          patient_email varchar2(50) not null unique,
                          patient_gender char(1) not null,
                          patient_location varchar2(50) not null,
                          patient_dateofappointment date not null,
                          CONSTRAINT sk_token_no CHECK (token_no<=20); 

Note:

if we think generally admin doesn't know which token number is going on for each and every doctor. 

As we have the same table structure for each and every doctor by their name. But now the thing is the first column in each doctor table has to generate automatically from 1, to do this i created a sequence and a procedure to execute the sequence before an insertion happens by the admin.

let's take morning session of out-patients consultation from 8:00 am to 10:00 am. Each doctor will only have a 20 patients for consultation.

Sequence Creation :

create sequence appointment_sequence
start with 1
increment by 1
minvalue 1
maxvalue 20
cache 5
;

Procedure Creation :

create or replace trigger appointment_sequence before insert on sarath_kumar for each row
begin 
  :new.token_no := appointment_sequence.NEXTVAL;
end;
/

what i need from you is :

After reaching 20 patients for any doctor during consultation i.e., the token number reached it's maximum level between 8:00 am to 10:00 am. If any person asks for a appointment for that particular doctor. The admin shouldn't able to provide any kind of appointment for that doctor and insist the patient to come in evening time consultation which is from 18:00 pm to 20:00pm .

I need a procedure or function in which the doctor table should get truncated and the sequence should get reset back to minvalue at 10:00 am and in the evening after 20:00 pm respectively.

Answer 1

First of all, You should have the patient_appoint table instead of a separate table with the doctor's name and just pass the doctor's ID in that table.

create table patient_appoint(token_no not null primary key,
                             doctor_id number not null,
                             patient_name char(50) not null ,
                             patient_age number(3) not null ,
                             patient_phonenumber number(12) not null unique ,
                             patient_email varchar2(50) not null unique,
                             patient_gender char(1) not null,
                             patient_location varchar2(50) not null,
                             patient_dateofappointment date not null,
                             CONSTRAINT sk_token_no CHECK (token_no<=20); 

For resetting the sequence to 1, Use CYCLE property of sequence. Use below code to generate the sequence -

create sequence appointment_sequence
start with 1
increment by 1
minvalue 1
maxvalue 20
cycle
cache 5
;

For restricting to only 20 patients per day, you may use below trigger -

CREATE OR REPLACE TRIGGER TR_PATIENT_APPOINT
    AFTER INSERT ON PATIENT_APPOINT
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM PATIENT_APPOINT
    WHERE TRUNC(patient_dateofappointment) = TRUNC(SYSDATE);

    IF (v_count > 20) THEN
        raise_application_error(-20000, 'Maximum 20 appointments allowed per day.');
    END IF;
END TR_PATIENT_APPOINT;

Answer 2

As others have pointed out or at lest hinted at this will be a maintenance nightmare, with each doctor having their own table and their own sequence. Consider what happens when a patient cancels. You don't get that sequence value back, so that doctor can only see 19 patients. And that is an easy situation to handle. There is an easier way: don't use sequences.

If you break it down each patent is essentially given a 6min time slot (120min/20slots). With his you generate a skeleton schedule for each doctor that does not have patient information initially. Admins then fill in patient information when needed, and can actually view the available time for each doctor. The following shows how to generate such a schedule. (Note it assumes you have normalized you doctor table (1 table containing all doctors) and created a patient table (1 table containing all patients).

--- prior Setup 
create table doctors(doc_id integer, name varchar2(50), ..., constraint doc_pk primary key (doc_id));    
create table patients(pat_id integer, name varchar2(50), ..., constraint pat_pk primary key (pat_id));
--- Daily Out patient Schedule.
create table out_patient_schedule (
             ops_id       integer  
           , doc_id       integer not null
           , pat_id       integer
           , apt_schedule date
           , constraint  ops_pk primary key (ops_id)
           , constraint  ops2doc_fk foreign key (doc_id) references doctors(doc_id)
           , constraint  ops2pat_fk foreign key (pat_id) references patients(pat_id) 
           );

--- Generate skeleton schedule
create or replace procedure gen_outpatient_skeleton_schedule
as 
begin 
   insert into out_patient_schedule( doc_id, apt_schedule)
        with apt_times as
           ( select trunc(sysdate, 'day') +  8/24  + (120/20)*(level-1)/(60*24) apt_time from dual connect by level <= 20
             union all
             select trunc(sysdate, 'day') + 18/24  + (120/20)*(level-1)/(60*24)          from dual connect by level <= 20    
           )
        select doc_id, apt_time from doctors, apt_times;
end gen_outpatient_skeleton_schedule;

Now create an Oracle Job, or an entry for what ever job schedule you have, that executes the above procedure between midnight and 8:00.

There is a race condition you need to handle, but doing so would be much easier that trying it with sequences.

Good Luck either way.