CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
CM25
CM25

Reputation: 103

PL/SQL code not inserting records into table

BACKGROUND: i'm using oracle SQLDeveloper, I have the tables:

TUTPRAC: CLASSID (PK), UNITCODE, STAFFNO, CLASSDAY, CLASSTIME, CLASSTYPE, ROOMNUM

UNITSTREAM: STREAMID (PK), UNITCODE, STAFFNO, DAY, TIME, LOCATION

PROBLEM: My current code no matter what i try to enter using the procedure does not enter a record even if the record is completely unique in terms of day, time and room.

What i am trying to do with the procedure is to check if there is any class already occupying the day/time/room of the record i'm trying to enter using the procedure.

For a record to be added successfully = The unit (e.g COMP111 (UNITCODE)) cannot have any classes in the same day (regardless of time or venue). It also cannot be assigned the same room as a another unit to prevent 2 classes being booked into the same classroom (e.g. SCIE112). And finally the unit cannot have a class at the same as that units lecture (lecture details are stored in UNITSTREAM table).

TUTPRACS contains the records for both tutorials and practicals while UNITSTREAM containts the records for streams (Lectures).

I'm quite new to PL/SQL so any help would be greatly appreciated. My code so far:

CREATE OR REPLACE PROCEDURE OPEN_CLASS(
           p_class IN TUTPRAC.CLASSID%TYPE,
           p_unitc IN TUTPRAC.UNITCODE%TYPE,
           p_classd IN TUTPRAC.CLASS_DAY%TYPE,
           p_classt IN TUTPRAC.CLASS_TIME%TYPE,
           p_classtp IN TUTPRAC.CLASS_TYPE%TYPE,
           p_roomnm IN TUTPRAC.ROOMNUM%TYPE)
    IS
   x NUMBER:=0;
   y NUMBER:=0;  
BEGIN

    -- checks
    SELECT nvl((SELECT 1 FROM TUTPRAC WHERE CLASS_DAY = p_classd and CLASS_TIME = p_classt and CLASS_TYPE = p_classtp and ROOMNUM = p_roomnm) , 0) INTO x FROM dual;
    SELECT nvl((SELECT 1 FROM UNITSTREAM WHERE UNITCODE = p_unitc and DAY = p_classd and TIME = p_classt and LOCATION = p_roomnm) , 0) INTO y FROM dual;

    -- insert
    IF (x = 1 and y = 1) THEN
        INSERT INTO TUTPRAC ("CLASSID", "UNITCODE", "CLASS_DAY", "CLASS_TIME", "CLASS_TYPE", "ROOMNUM") 
      VALUES (p_class, p_unitc, p_classd, p_classt, p_classtp, p_roomnm);
    END IF;
END OPEN_CLASS;

Upvotes: 0

Views: 1643

Answers (2)

tale852150
tale852150

Reputation: 1638

Try this for starters. You may want to handle the exception differently and/or return something to the program which calls this procedure to let it know of 'success' or 'failure'. But we can work on that later if you like.

    CREATE OR REPLACE PROCEDURE OPEN_CLASS(
               p_class IN TUTPRAC.CLASSID%TYPE,
               p_unitc IN TUTPRAC.UNITCODE%TYPE,
               p_classd IN TUTPRAC.CLASS_DAY%TYPE,
               p_classt IN TUTPRAC.CLASS_TIME%TYPE,
               p_classtp IN TUTPRAC.CLASS_TYPE%TYPE,
               p_roomnm IN TUTPRAC.ROOMNUM%TYPE)
        IS
       x NUMBER := 0;
       y NUMBER := 0;  
       exc_clash EXCEPTION;
       PRAGMA EXCEPTION_INIT(exc_clash, -22000);

    BEGIN

        -- checks for conflicts
        SELECT 1 
        INTO x
        FROM TUTPRAC 
        WHERE CLASS_DAY = p_classd 
        and CLASS_TIME = p_classt 
        and CLASS_TYPE = p_classtp 
        and ROOMNUM = p_roomnm;

        SELECT 1 
        INTO y
        FROM UNITSTREAM 
        WHERE UNITCODE = p_unitc 
        and DAY = p_classd 
        and TIME = p_classt 
        and LOCATION = p_roomnm;

        -- exception if conflict else insert class.
        IF (x = 1 OR y = 1) THEN
          RAISE exc_clash;
        ELSE
          INSERT INTO TUTPRAC ("CLASSID", "UNITCODE", "CLASS_DAY", "CLASS_TIME",  "CLASS_TYPE", "ROOMNUM") 
          VALUES (p_class, p_unitc, p_classd, p_classt, p_classtp, p_roomnm);
          COMMIT;
        END IF;

        EXCEPTION
          WHEN exc_clash THEN
             DBMS_OUTPUT.PUT_LINE('There is a class clash');

    END OPEN_CLASS;

Upvotes: 1

CM25
CM25

Reputation: 103

I used and + or in the checks to first check for class + day clash and then time/day/room clash. Seems to work now. 

BEGIN
        -- checks
        SELECT nvl((SELECT 1 FROM TUTPRAC WHERE UNITCODE = p_unitc and CLASS_DAY = p_classd or CLASS_DAY = p_classd and CLASS_TIME = p_classt and ROOMNUM = p_roomnm) , 0) INTO x FROM dual;
        SELECT nvl((SELECT 1 FROM UNITSTREAM WHERE UNITCODE = p_unitc and DAY = p_classd or DAY = p_classd and TIME = p_classt and LOCATION = p_roomnm) , 0) INTO y FROM dual;

        -- insert
        IF (x = 0 and y = 0) THEN
          INSERT INTO TUTPRAC (CLASSID, UNITCODE, CLASS_DAY, CLASS_TIME, CLASS_TYPE, ROOMNUM) 
          VALUES (p_class, p_unitc, p_classd, p_classt, p_classtp, p_roomnm);
        END IF;

    END OPEN_CLASS;

Upvotes: 0

Related Questions