CODEWITHSUNDEEP
sqloracle-databaseplsql
Ms.J
Ms.J

Reputation: 161

write a procedure to add course plsql

i am trying to write a procedure that register a course by student id, course number and section id .. and i have to check course availability before registering it.

below is my procedure, no errors but nothing registered..

SET SERVEROUTPUT  ON SIZE 4000
CREATE  OR REPLACE  PROCEDURE ADD_COURSE ( Sid IN VARCHAR2, courseNo IN VARCHAR2,secNum IN NUMBER)
IS
stat VARCHAR2(20);
CSecID NUMBER(6);
TiD NUMBER(6);
BEGIN

SELECT c_sec_id, term_id into CSecID, TiD FROM COURSE_SECTION
where course_no=courseNo and sec_num=secNum;

SELECT status into stat FROM TERM where term_id=TiD;

IF stat = 'OPEN' THEN
    INSERT INTO enrollment(s_id, c_sec_id, grade) VALUES(Sid, CSecID, NULL);
END IF;

END;
/


BEGIN
    ADD_COURSE('122487', 'ITCS490', 1);
END;
/

Tables and insert

CREATE TABLE TERM
(term_id NUMBER(6),
term_desc VARCHAR2(20),
status VARCHAR2(20),
start_date DATE,
CONSTRAINT term_term_id_pk PRIMARY KEY (term_id),
CONSTRAINT term_status_cc CHECK ((status = 'OPEN') OR (status = 'CLOSED')));


--- inserting records into TERM
INSERT INTO term (term_id, term_desc, status) VALUES
(1, 'Spring 2014', 'CLOSED');

INSERT INTO term (term_id, term_desc, status) VALUES
(2, 'Summer 2014', 'CLOSED');

INSERT INTO term (term_id, term_desc, status) VALUES
(3, 'Summer 2015', 'OPEN');



CREATE TABLE COURSE_SECTION
(c_sec_id NUMBER(6),
course_no VARCHAR2(7) CONSTRAINT course_section_courseid_nn NOT NULL,
term_id NUMBER(6) CONSTRAINT course_section_termid_nn NOT NULL,
sec_num NUMBER(2) CONSTRAINT course_section_secnum_nn NOT NULL,
f_id NUMBER(6),
c_sec_day VARCHAR2(10),
c_sec_time DATE,
c_sec_duration INTERVAL DAY TO SECOND,
loc_id NUMBER(6),
max_enrl NUMBER(4) CONSTRAINT course_section_maxenrl_nn NOT NULL,
CONSTRAINT course_section_csec_id_pk PRIMARY KEY (c_sec_id),
CONSTRAINT course_section_cid_fk FOREIGN KEY (course_no) REFERENCES course(course_no),  
CONSTRAINT course_section_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id),
CONSTRAINT course_section_termid_fk FOREIGN KEY (term_id) REFERENCES term(term_id),
CONSTRAINT course_section_fid_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));


--- inserting records into COURSE_SECTION
INSERT INTO course_section VALUES
(1, 'ITCS101', 3, 1, 1, 'UTH', TO_DATE('10:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 2, 30 );
INSERT INTO course_section VALUES
(2, 'ITCS101', 3, 2, 1, 'MW', TO_DATE('09:30 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:01:15.00'), 7, 35);
INSERT INTO course_section VALUES
(3, 'ITCS102', 3, 1, 2, 'UTH', TO_DATE('8:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 4, 30);
INSERT INTO course_section VALUES
(4, 'ITCS215', 3, 1, 2, 'UTH', TO_DATE('8:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 1, 30);
INSERT INTO course_section VALUES
(5, 'ITCS490', 1, 1, 3, 'UTH', TO_DATE('10:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 5,30 );
INSERT INTO course_section VALUES
(6, 'ITCS385', 1, 1, 1, 'UTH', TO_DATE('11:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 6,30 );

Upvotes: 2

Views: 208

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

Your data has this:

INSERT INTO course_section VALUES
(5, 'ITCS490', 1, 1, 3, 'UTH', TO_DATE('10:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 5,30 );

You are calling the procedure with courseNo => 'ITCS490' and secNum => 1.

That means the first query inside the procedure gets:

SELECT c_sec_id, term_id FROM COURSE_SECTION where course_no='ITCS490' and sec_num=1;

  C_SEC_ID    TERM_ID
---------- ----------
         5          1

And term 1 is closed:

SELECT status FROM TERM where term_id=1;

STATUS             
--------------------
CLOSED

So your insert is correctly not called.

You can verify the value from your procedure by adding some debugging code to your procedure:

dbms_output.put_line('CSecID=' || CSecID || ', TiD=' || TiD || ', stat=' || stat);

Then when you call it you see:

PL/SQL procedure successfully completed.

CSecID=5, TiD=1, stat=CLOSED

Your procedure does insert with different input values;

BEGIN
    ADD_COURSE('122487', 'ITCS102', 1);
END;
/

PL/SQL procedure successfully completed.

CSecID=3, TiD=3, stat=OPEN

select * from enrollment;

      S_ID   C_SEC_ID      GRADE
---------- ---------- ----------
    122487          3

Upvotes: 1

Related Questions