oracle database: create a PL/SQL procedure that checks the duplicated data

Question

I want to create a PL/produces that checks the start date and end date in the database. here is my data

HAS_CONTRACT        DATE_FROM     DATE_TO       DURATION
---------------------------------------- --------- --------- -----------------------------
Simon Palm           20-AUG-01      19-AUG-02         0
Simon Palm          20-SEP-02       19-AUG-03         0
Flut            10-JUN-99       09-SEP-02         0
John Cliff          10-MAR-03       10-APR-04         0

If the user trying to input a person that has the same date_from and date_to, then it should not be permitted, therefore a exception error. Here is how far i did, but i am sure thats not how a procedure should be. Any help?

CREATE PROCEDURE insertTuple(
x contractinfo.date_from%Type,
y contractinfo.date_to%Type)
AS
BEGIN
  IF x!=y THEN 
     INSERT INTO contractInfo VALUES(x,y);
END;
/

This is a constraint: `Alter table XXXX add constraint XXX_AK_from_to UNIQUE (date_from, date_to)` — dani herrera, Dec 20 '12 at 11:42
Better way to do this is to add some constraints to your table so that the duplicate data doesn't get in there in the first place! alter table mytable add constraint myconstraint unique (has_contract, date_from, date_to) — Lord Peter, Dec 20 '12 at 11:43

score 2 · Accepted Answer · answered Dec 20 '12 at 11:50

The proper way to enforce this would be with a database constraint.

alter table contractors add constraint contractor_date_uk unique
    (has_contract, start_date, end_date);

This is the standard way, it is scalable, works in a multi-user environment and cannot be circumvented by cheeky developers.

Of course it is important to ensure that the time element is removed from the dates. This can be done in a trigger, or enforced through an index (which can be used by the constraint:

create unique index contractor_date_uidx 
   on   contractors(has_contract, trunc(start_date), trunc(end_date));

Note that I am including the contractor person in the check - your question isn't clear on that point. If you really only want one contractor in force at a time then simply remove HAS_CONTRACT from the uniqueness specification.

The other thing is, this doesn't allow for overlaps. Your posted data has overlapping date ranges, so I presume that is okay. If you want to avoid overlapping date ranges, please edit your question to say so; but be warned, the logic gets gnarly.

oracle database: create a PL/SQL procedure that checks the duplicated data

1 Answers1