撰寫一個 PLSQL 塊來獲取分配給 EMP 表中員工的不同部門 ID。部門詳細資訊，如部門名稱和所有需要從部門表中檢索。此處不遵循 SQL 完整性約束，因此您可能會發現 DEPT 表中不可用的記錄。確保處理所有必需的例外以處理這些記錄。
架構詳細資訊是：
TABLE NAME COLUMN NAMES
------------ -------------------------------------------------
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
我已經撰寫了代碼，但是每次運行此代碼時，輸??出都與問題中的要求相似，但沒有通過測驗用例。請幫忙。
問題截圖
測驗用例錯誤資訊
我的代碼：
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;
uj5u.com熱心網友回復：
使用您的 SQL 測驗用例以顯示結果缺少行：
擺弄程式，以及更正的程式
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;
/
結果：
dbms_output:
Dept ID: 2 is not a valid ID
Dept ID: 1 is d1
請注意，此結果中缺少 dept_id = 3。
這就是為什么你需要的東西像的FULL JOIN同時檢測缺失的型別，都dept_id沒有包含在emp和dept_id中emp不存在中dept。
現在更正的程式：
我沒有洗掉不必要的SELECT INTO陳述。這些并不是真正需要的，因為游標包含您需要的一切。
-- 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;
/
正確結果：
dbms_output:
Dept ID: 1 is d1
Dept ID: 2 is not a valid ID
Dept ID: 3 is d3
通知dept_id = 3在結果中。
如果我們想要保證dept_id順序，我們可以在游標查詢中添加以下內容：
ORDER BY COALESCE(dept.dept_id, isExists)
帶描述的原始答案：
您的嘗試沒有檢測到dept.dept_id存在與emp行無關的情況。您的 SQL 只能找到emp表參考的 dept_id 。
游標只需要是：
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:
Fiddle
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 |
uj5u.com熱心網友回復：
問題是：
撰寫一個 PL/SQL 塊以獲取分配給 EMP 表中員工的不同部門 ID。
您只需DEPT_ID要從EMP表中獲取值并檢查它們是否有效（即它們存在于DEPT表中）；您不需要在DEPT表中列印表中不存在的任何 id，EMP因此使用 aFULL OUTER JOIN是錯誤的。
LEFT OUTER JOIN在游標中使用 a ：
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;
/
此外，請注意格式化輸出以使其與預期輸出匹配。
其中，對于樣本資料：
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;
輸出：
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<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/362695.html