Write a procedure PROFESSORS_BY_DEPARTMENT (departmentId VARCHAR(3)). When execu

Question

Write a procedure PROFESSORS_BY_DEPARTMENT (departmentId VARCHAR(3)). When executed it displays the Ids, names and the department names for all the professors in a department.Test your procedure by executing these calls.

CALL PROFESSORS_BY_DEPARTMENT ("CSC");

CALL PROFESSORS_BY_DEPARTMENT ("MTH");

CALL PROFESSORS_BY_DEPARTMENT ("PSH");

Here are the tables:

Tables are bellow:

Student Table

Department Table

Professor Table

Course Table

Teaching_Assignment Table:

Transcript Table:

Banner Id 100 101 102 103 104 105 106 107 108 109 110 Name Leona Jerald Freddie Shannon Billie Casey Nicole Alex Mona Anthony Thomas Curtis Addres Status Freshman Freshman Freshman Sophomore Sophomore Sophomore Junior Junior Junior Senior Senior Senior

Explanation / Answer

create or replace PROCEDURE CALL PROFESSORS_BY_DEPARTMENT
(   
departmentId VARCHAR DEFAULT -1
)
AS

v_Id Professor.Id%TYPE;
v_name Professor.Name%TYPE;
v_dname Department.name%TYPE;
BEGIN
-- Just SELECT away, returning column values into the variables. If
-- the employee ID isn't found, Oracle will throw and you can pick
-- up the pieces in the EXCEPTION block below.
SELECT p.Id, p.Name, d.name
INTO v_Id, v_name, v_dname
FROM Professor P,Department d
WHERE p.DepartmentId = d.ID ;
-- Fallthrough to here means the query above found one (and only one)
-- row, and therefore it put values into the variables. Print out the
-- variables.
DBMS_OUTPUT.PUT_LINE('Professor ID: ' || v_Id );
DBMS_OUTPUT.PUT_LINE('NAME: ' || v_name);
DBMS_OUTPUT.PUT_LINE('DEPARMENT NAME: ' || d_name);
EXCEPTION
-- If the query didn't find a row you'll end up here. In this case
-- there's no need for any type of fancy exception handling; just
-- reporting that the employee wasn't found is enough.
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('data not found.');
END;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.