Write a PLSQL block to fetch distinct department ids which is assigned to employees in EMP table. Department details such as dept name and all needs to be retrieved from DEPT Table. SQL integrity constraints are not followed here, and so you may find records which are not available in DEPT Tables. Make sure to handle all the required exceptions to take care of these records.
Schema details are:
TABLE NAME COLOUMN NAME
DEPT DEPT_HEAD, DEPT_ID, DEPT_NAME
EMP DEPT_ID, EMP_ACTIVEFROM, EMP_DOB, EMP_FNAME, EMP_ID, EMP_LNAME,
EMP_SAL, EMP_STATUS, EMP_TERMINATIONDATE, LOC_ID, MGR_ID
I have written the code, but every time I run this, the output is coming similar to what is asked in the question but it's not passing the Test case. Please help.
My code:
SET SERVEROUTPUT ON
DECLARE
l_dept_name dept.dept_name%type;
BEGIN
for cur_r in(select distinct e.dept_id from EMP e)
loop
begin
select d.dept_name into l_dept_name from DEPT d where d.dept_id = cur_r.dept_id;
dbms_output.put_line('Dept ID: '|| cur_r.dept_id||' is '||l_dept_name);
exception
when NO_DATA_FOUND then
dbms_output.put_line('Dept ID: '|| cur_r.dept_id ||' is not a valid ID');
end;
end loop;
END;
/
EXIT;
CodePudding user response：
Test case with your SQL to show the result is missing rows:
Fiddle with the procedure, and the corrected procedure
CREATE TABLE dept (dept_id int, dept_name varchar2(10));
CREATE TABLE emp (dept_id int);
BEGIN
INSERT INTO dept VALUES (1, 'd1');
INSERT INTO dept VALUES (3, 'd3');
INSERT INTO emp VALUES (1);
INSERT INTO emp VALUES (2);
END;
/
SELECT dept.dept_id, dept.dept_name, emp.dept_id AS isExists
FROM dept FULL JOIN emp ON dept.dept_id = emp.dept_id
;
-- The original procedure, with missing results:
DECLARE
l_dept_name dept.dept_name%type;
BEGIN
for cur_r in(select distinct e.dept_id from EMP e)
loop
begin
select d.dept_name into l_dept_name from DEPT d where d.dept_id = cur_r.dept_id;
dbms_output.put_line('Dept ID: '|| cur_r.dept_id||' is '||l_dept_name);
exception
when NO_DATA_FOUND then
dbms_output.put_line('Dept ID: '|| cur_r.dept_id ||' is not a valid ID');
end;
end loop;
END;
/
The results:
dbms_output:
Dept ID: 2 is not a valid ID
Dept ID: 1 is d1
Notice dept_id = 3 is missing in this result.
That's why you need something like a FULL JOIN to detect both missing types, both dept_id not contained in emp and dept_id in emp that doesn't exist in dept.
Now the corrected procedure:
I didn't remove the unnecessary SELECT INTO statements. Those aren't really needed, since the cursor contains everything you need.
-- The new procedure, with all expected results:
DECLARE
l_dept_name dept.dept_name%type;
BEGIN
for cur_r in(
SELECT DISTINCT dept.dept_id, dept.dept_name, emp.dept_id AS isExists
FROM dept FULL JOIN emp ON dept.dept_id = emp.dept_id
)
loop
begin
select d.dept_name into l_dept_name from DEPT d where d.dept_id = cur_r.dept_id;
dbms_output.put_line('Dept ID: '|| cur_r.dept_id||' is '||l_dept_name);
exception
when NO_DATA_FOUND then
dbms_output.put_line('Dept ID: '|| cur_r.isexists ||' is not a valid ID');
end;
end loop;
END;
/
The correct result:
dbms_output:
Dept ID: 1 is d1
Dept ID: 2 is not a valid ID
Dept ID: 3 is d3
Notice dept_id = 3 is in the result.
If we want the order guaranteed in dept_id order, we can add the following to the cursor query:
ORDER BY COALESCE(dept.dept_id, isExists)
Original answer with description:
Your attempt doesn't detect the cases where a dept.dept_id exists which is not associated with an emp row. Your SQL only finds dept_id's which are referenced by the emp table.
The cursor only needs to be:
SELECT DISTINCT dept.*
, emp.dept_id AS isExists
FROM dept FULL JOIN emp ON dept.dept_id = emp.dept_id
;
This provides the distinct list of all dept detail, as requested, plus a column indicating if an employee record was found for the corresponding department.
When an emp row refers to an invalid dept_id, dept.dept_id will be null, and the isExists column in the result contains that missing dept_id.
FULL JOIN detects both emp.dept_id which is not in dept table and dept.dept_id which is not found in the emp table.
Test case:
WITH emp (dept_id) AS (SELECT 1 FROM dual UNION SELECT 2 FROM dual)
, dept (dept_id) AS (SELECT 1 FROM dual UNION SELECT 3 FROM dual)
SELECT dept.dept_id, emp.dept_id AS isExists
FROM dept FULL JOIN emp ON dept.dept_id = emp.dept_id
;
Result:
| DEPT_ID | ISEXISTS |
|---|---|
| 1 | 1 |
| null | 2 |
| 3 | null |
CodePudding user response：
The question is:
Write a PL/SQL block to fetch the distinct department ids which is assigned to employees in the EMP table.
You only need to fetch the DEPT_ID values from the EMP table and check if they are valid (i.e. they exist in the DEPT table); you do not need to print any ids in the DEPT table that do not exist in the EMP table so using a FULL OUTER JOIN is wrong.
Use a LEFT OUTER JOIN in the cursor:
SET SERVEROUTPUT ON;
BEGIN
FOR r IN (SELECT DISTINCT
e.dept_id,
d.dept_name
FROM EMP e
LEFT OUTER JOIN DEPT d
ON e.dept_id = d.dept_id)
LOOP
IF r.dept_name IS NULL THEN
DBMS_OUTPUT.PUT_LINE( 'Dept_ID :'||r.dept_id|| ' is not a valid ID');
ELSE
DBMS_OUTPUT.PUT_LINE( 'Dept_ID :'||r.dept_id|| ' is '||r.dept_name);
END IF;
END LOOP;
END;
/
Also, take care that you format the output so that it matches the expected output.
Which, for the sample data:
CREATE TABLE dept (dept_id, dept_name) AS
SELECT 1, 'Dept_name1' FROM DUAL UNION ALL
SELECT 2, 'Dept_name2' FROM DUAL UNION ALL
SELECT 4, 'Dept_name4' FROM DUAL;
CREATE TABLE emp (emp_id, dept_id) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 5, 5 FROM DUAL UNION ALL
SELECT 6, 5 FROM DUAL;
Outputs:
Dept_ID :1 is Dept_name1 Dept_ID :4 is Dept_name4 Dept_ID :3 is not a valid ID Dept_ID :5 is not a valid ID
db<>fiddle here