I have the query below and I get it to run, but it is missing something how to answer the problem. My joins appear to be incorrect, I am using ID_number and S_ID. so I am completely lost there.
Hi,
I have the query below and I get it to run, but it is missing something how to answer the problem. My joins appear to be incorrect, I am using ID_number and S_ID. so I am completely lost there.
CREATE OR REPLACE PROCEDURE Moreno_03_insCourseNumsProc(INOUT i_ID VARCHAR(5))
LANGUAGE PLPGSQL
AS
$$
DECLARE
name VARCHAR(35);
tot_courses INTEGER; -- Created this variable to hold the total course taught by the instructor
l_exists_count INTEGER := 0; -- This is to identify the existence of the records the table instructor_course_nums
BEGIN
SELECT name INTO name FROM instructor WHERE instructor.id = i_id;
SELECT COUNT(*) INTO tot_courses FROM teaches WHERE teaches.id = i_id;
SELECT COUNT(*) INTO l_exists_count FROM instructor_course_nums WHERE ID_Number = i_ID;
IF l_exists_count > 0
THEN
UPDATE instructor_course_nums SET tot_courses = total_course_nums WHERE ID_Number = i_ID;
ELSE
INSERT INTO instructor_course_nums VALUES(i_ID, name, tot_courses);
END IF;
COMMIT;
END;
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
