Creating a procedure with PLSQL
- by user1857460
I am trying to write a PLSQL function that implements a constraint that an employee cannot be both a driver and a mechanic at the same time, in other words, the same E# from TRKEMPLOYEE cannot be in TRKDRIVER and TRKMECHANIC at the same time. The abovementioned tables are something like as follows:
TRKEMPLOYEE(E# NUMBER(12) NOT NULL
CONSTRAINT TRKEMPLOYEE_PKEY PRIMARY KEY(E#))
TRKDRIVER(E# NUMBER(12) NOT NULL
CONSTRAINT TRKDRIVER_PKEY PRIMARY KEY(E#),
CONSTRAINT TRKDRIVER_FKEY FOREIGN KEY(E#) REFERENCES TRKEMPLOYEE(E#))
TRKMECHANIC(E# NUMBER(12) NOT NULL
CONSTRAINT TRKMECHANIC_PKEY PRIMARY KEY(E#),
CONSTRAINT TRKMECHANIC_FKEY FOREIGN KEY(E#) REFERENCES TRKEMPLOYEE(E#))
I have attempted to write a function but keep getting a compile error in line 1 column 7. Can someone tell me why my code doesn't work? My code is as follows
CREATE OR REPLACE FUNCTION Verify()
IS DECLARE
E# TRKEMPLOYEE.E#%TYPE;
CURSOR C1 IS SELECT E# FROM TRKEMPLOYEE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO EMPNUM;
IF(EMPNUM IN(SELECT E# FROM TRKMECHANIC )AND EMPNUM IN(SELECT E# FROM TRKDRIVER))
SELECT E#, NAME FROM TRKEMPLOYEE WHERE E#=EMPNUM;
ELSE
dbms_output.put_line(“OK”);
ENDIF
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;
/
Any help would be appreciated. Thanks.
