Part 2: Writing a PL/SQL program to create the procedure. Here’s the code. SET S
ID: 3912797 • Letter: P
Question
Part 2: Writing a PL/SQL program to create the procedure.
Here’s the code.
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE DEPT_EMPLOYEE_DETAILS
AS
CURSOR C_DETAILS
AS
select d.department_id Department_id,
d.department_name department_name,
d.manager_id manager_id,
e.employee_id employee_id,
e.first_name || ' ' || e.last_name full_name
from departments d, employees e
where e.department_id = e.department_id;
R_DETAILS C_DETAILS%ROWTYPE;
BEGIN
OPEN C_DETAILS;
FETCH C_DETAILS;
DBMS_OUTPUT.PUT_LINE(‘----------------------‘);
LOOP
DBMS_OUTPUT.PUT_LINE(‘Department Id: ‘ || r_details.department_id);
DBMS_OUTPUT.PUT_LINE(‘Department Name: ‘ || r_details.department_name);
DBMS_OUTPUT.PUT_LINE(‘Department Manager: ‘ || r_details.manager_id);
DBMS_OUTPUT.PUT_LINE(‘Employee Id: ‘ || r_details.Employee_id);
.
.
.
Complete the remaining display of employee’s data based on the cursor variables. i.e. full_name
EXIT WHEN R_DETAILS%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘----------------------‘);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No data found’);
END;
Enter the above PL/SQL block into the Query Builder.
Compile and run the program by clicking on the green triangle. If you receive errors, correct them until the program runs correctly.
Create an anonymous block to call the procedure using the following template. Complete the remaining part as directed in the comment inside the BEGIN section of the anonymous block.
SET SERVEROUTPUT ON
BEGIN
-- Call the procedure DEPT_EMPLOYEE_DETAILS here
EXCEPTION
END;
/
Part 2 Questions
Write the above anonymous block and share the screenshot of the output. (75%)
Is an exception needed here? (25%)
Explanation / Answer
Please find the entire PROCEDURE written below, which can be exceuted in Query Builder:-
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE DEPT_EMPLOYEE_DETAILS
AS
CURSOR C_DETAILS
AS
select d.department_id Department_id,
d.department_name department_name,
d.manager_id manager_id,
e.employee_id employee_id,
e.first_name || ' ' || e.last_name full_name
from departments d, employees e
where e.department_id = e.department_id;
R_DETAILS C_DETAILS%ROWTYPE;
BEGIN
OPEN C_DETAILS;
FETCH C_DETAILS;
DBMS_OUTPUT.PUT_LINE(‘----------------------‘);
LOOP
DBMS_OUTPUT.PUT_LINE(‘Department Id: ‘ || r_details.department_id);
DBMS_OUTPUT.PUT_LINE(‘Department Name: ‘ || r_details.department_name);
DBMS_OUTPUT.PUT_LINE(‘Department Manager: ‘ || r_details.manager_id);
DBMS_OUTPUT.PUT_LINE(‘Employee Id: ‘ || r_details.Employee_id);
DBMS_OUTPUT.PUT_LINE(‘Employee Full Name: ‘ || r_details.full_name);
EXIT WHEN R_DETAILS%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘----------------------‘);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No data found’);
END;
Q2) Create an anonymous block to call the procedure using the following template. Complete the remaining part as directed in the comment inside the BEGIN section of the anonymous block.
SET SERVEROUTPUT ON
BEGIN
EXECUTE DEPT_EMPLOYEE_DETAILS;
EXCEPTION
WHEN NOT_LOGGED_ON THEN
DBMS_OUTPUT.PUT_LINE ('Please Connect to the Dabase first, before calling any Stored Procedure/Function');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Some other Exceptions arised during execution’);
END;
/
Is an exception needed here? - YES, because if there are any other types of exceptions thta may arise inside the Stored Procedure DEPT_EMPLOYEE_DETAILS, it will not be able to handle it as it has only NO_DATA_FOUND Exception Handler. So there can be chances of unexpexcted exceptions that may arise inside the Procedure while execution, so we need to catch them as well.
Please let me know in case of any clarifications required. Thanks!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.