我已經為PLSQL例外處理程式撰寫了代碼，但它仍然沒有通過HackerRank中的測驗用例

撰寫一個 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:

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<>在這里擺弄

標籤：sql 甲骨文 例外 plsql plsql开发者

上一篇：Python子類化例外

下一篇：如何使用javascript/jquery為播放/暫停按鈕制作回圈