函式的子查詢替代方案？

我正在嘗試獲取一個程序，該程序將獲取 2 個 varchars（1 個用于名字，1 個用于姓氏）作為引數，并將回傳（如果存在）具有該名稱的經理的 id；

我的員工表看起來像這樣：

EMPLOYEE_ID NUMBER,
FIRST_NAME VARCHAR(50),
LAST_NAME VARCHAR(50),
MANAGER_ID NUMBER

我的功能就在這里：

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type) RETURN NUMBER IS numar NUMBER;
    BEGIN
        SELECT e.employee_id INTO numar from
        employees e
        where e.last_name = v_ln and e.first_name = v_fn;
        IF numar IN (SELECT manager_id from employees)  /*here is the problem */
            THEN DBMS_OUTPUT.PUT_LINE('That is not a manager');
            ELSE return numar;
        END IF;
        return numar;
        end;
        /

當我運行它時，我收到此錯誤PLS-00405: subquery not allowed in this context 此子查詢是否有任何替代方法可以驗證員工的 ID 是否實際上是經理的 ID？

uj5u.com熱心網友回復：

您可以執行第二個查詢，例如匹配行的計數，并測驗該結果：

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type)
RETURN NUMBER IS
    numar NUMBER;
    manager_count NUMBER;
BEGIN
    SELECT e.employee_id INTO numar from
    employees e
    where e.last_name = v_ln and e.first_name = v_fn;

    SELECT count(*) INTO manager_count from
    employees e
    where manager_id = numar;

    IF manager_count = 0
        THEN DBMS_OUTPUT.PUT_LINE('That is not a manager');
        ELSE return numar;
    END IF;
    return numar;
END;
/

或者在單個查詢中，您可以使用外連接并計算匹配的行數，或者 - 我認為更清楚一點，并且可能更有效（不是真的在這里，而是在更復雜的情況下）測驗exists：

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type)
RETURN NUMBER IS
    numar NUMBER;
    is_manager VARCHAR2(3);
BEGIN
    SELECT e.employee_id,
        case when exists (
            select null from employees m where m.manager_id = e.employee_id
        ) then 'yes' else 'no' end
    INTO numar, is_manager from
    employees e
    where e.last_name = v_ln and e.first_name = v_fn;

    IF is_manager != 'yes'
        THEN DBMS_OUTPUT.PUT_LINE('That is not a manager');
        ELSE return numar;
    END IF;
    return numar;
END;
/

db<>小提琴演示

您還應該決定如何處理傳遞給不匹配任何行的引數。目前查詢將在 PL/SQL 背景關系中拋出“ORA-01403: no data found”，或者在 SQL 背景關系中只回傳 null，這可能沒問題。

假設呼叫您的函式的人能夠看到您發送的任何內容通常是不安全的dbms_output（例如，默認情況下 db<>fiddle 不會在 SQL 呼叫中顯示它）；并且您的else分支有點毫無意義，因為您以numar任何方式回傳值。

盡管如此，所有這些都超出了您實際問題的范圍。

uj5u.com熱心網友回復：

以 Alex Poole 的回答為基礎，另一種可能如下。

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type)
RETURN NUMBER IS
    numar NUMBER;
    manager_id employees.manager_id%type;
BEGIN
    with 
      "S" as
      ( select e.employee_id
             , m.manager_id
             , 1 srt
          from employees e
               left join employees m
                      on e.employee_id = m.employee_id
         where e.last_name      = v_ln
               and e.first_name = v_fn
               and rownum = 1
        union all
        select null
             , null
             , 2
          from dual
      )
      select employee_id
           , manager_id
        into numar
           , manager_id
        from "S"
       where rownum = 1
       order by srt
    ;
    
    if numar is null then
      dbms_output.put_line('That is not an employee');
    elsif manager_id is null then 
      DBMS_OUTPUT.PUT_LINE('That is not a manager');
    end if;
    
    return numar;
END;
/

編輯 或者，也許更優雅的解決方案是這個重構版本。

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type)
RETURN NUMBER IS
    numar NUMBER;
    manager_id employees.manager_id%type;
BEGIN
    select e.employee_id
         , m.manager_id
      into numar
         , manager_id         
      from dual 
           left join employees e
                  on e.last_name      = v_ln
                     and e.first_name = v_fn
           left join employees m
                  on e.employee_id = m.employee_id
     where rownum = 1
    ;
    
    if numar is null then
      dbms_output.put_line('That is not an employee');
    elsif manager_id is null then 
      DBMS_OUTPUT.PUT_LINE('That is not a manager');
    end if;
    
    return numar;
END;
/

db<>小提琴演示

標籤：甲骨文 oracle-sqldeveloper

上一篇：如何計算OTBI報告中員工型別的百分比？

下一篇：在選擇中定義