Search code examples
sqloracle-databasestored-proceduresdata-integrity

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

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;
/
Solution

  • 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.