Stored procedure to add a row to top of list

Question

Oracle 12c

I currently have the following three tables: Physician, Patient, and Wait_List. The Wait_List table is a bridge table to hold patients who are waiting on individual doctors.

I would like to track priority for each of these patients in a way other than the date they entered the wait list. For instance, if a given doctor is waiting on 3 patients each of these patients are assigned a value of 1-3 in their Priority field.

The issue I am having is figuring out how I would begin to write the procedure to enter a fourth patient who is to have a priority higher than 4, for instance a priority of 1. I understand that the procedure would require each of the 3 current patients' priorities to be pushed down in value.

Any guidance to this question would be greatly appreciated.

Thank you,

This part does not make sense to me, please clarify/correct: *enter a fourth patient who is to have a priority higher than 4, for instance a priority of 1* — Hawk, Feb 26 '16 at 01:54
the wait_list table will have the following three columns: physicianID, patientID, Priority. Priority will be an incremented number that is unique for each instance of (PhysicianID, PatientID). In the scenario above, we have a physician who is waited on by 3 patients. Each of these patients have been assigned a number based on priority (1, 2, or 3). A fourth patient comes in and it was decided for this patient to be put ahead of the previous 3 patients. Thus, this fourth patient will have a priority of 1 and the previous three patients will have priorities of 2,3, and 4 respectively. — Ian, Feb 26 '16 at 02:00

score 1 · Accepted Answer · answered Feb 26 '16 at 02:02

This procedure will insert values into the wait_list table and it will set the priority number to one if there are already three patients waiting for that particular physician. Just edit the column and table names

  create procedure insertinto_Wait_List(i_patient_name IN Patient.patient_namecolumn%TYPE, i_physician Physician.Physician_column%TYPE)
  IS
  v_max_priority NUMBER;
  BEGIN
  select max(prioritynumber_column) into v_max_priority from Wait_List where Physician_column = i_physician;
  if v_max_priority > 2 THEN
  update Wait_List set prioritynumber_column = prioritynumber_column + 1 where Physician_column = i_physician;
  insert into Wait_List (prioritynumber_column, patient_namecolumn, Physician_column) values (1,i_patient_name, i_physician);
  else
  insert into Wait_List (prioritynumber_column, patient_namecolumn, Physician_column) values (v_max_priority + 1,i_patient_name, i_physician);
  end if;
  end;

This got me where I needed to go! Thanks again. – Ian Feb 26 '16 at 02:37 — Ian, Feb 26 '16 at 02:37

score 1 · Answer 2 · edited May 23 '17 at 12:23

There could be better answers using advanced PL/SQL. But using unique constraint is what comes to my mind. When you create the table wait_list, add the following unique constraint to create statement:

CONSTRAINT priority_unique UNIQUE (prioritynumber_column, Physician_column)

Then the procedure, can use the exception as follows

CREATE PROCEDURE proc_insert_wait_list(i_priority IN wait_list.prioritynumber_column%TYPE, i_patient_name IN Patient.patient_namecolumn%TYPE, i_physician Physician.Physician_column%TYPE)
IS
BEGIN
INSERT INTO Wait_List (prioritynumber_column, patient_namecolumn, Physician_column) values (i_priority,i_patient_name, i_physician);
COMMIT;
EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
      FOR i IN (SELECT prioritynumber_column, patient_namecolumn, Physician_column
          FROM Wait_List 
          WHERE Physician_column = i_physician 
          AND prioritynumber_column >= i_priority)
      LOOP
      UPDATE Wait_List 
      SET prioritynumber_column = prioritynumber_column + 1;
      END LOOP;
      INSERT INTO Wait_List (prioritynumber_column, patient_namecolumn, Physician_column) values (P_priority,i_patient_name, i_physician);
END proc_insert_wait_list;

The idea, you can set the priority you want. But if it collides with existing one, RDBMS will throw the exception DUP_VAL_ON_INDEX. Which you handle by pushing down all the existing records and then insert the priority.

Stored procedure to add a row to top of list

2 Answers2