使用 DBMS_OUTPUT.put_line 顯示錯誤消息 (Display error message using DBMS_OUTPUT.put_line)

問題描述

使用 DBMS_OUTPUT.put_line 顯示錯誤消息 (Display error message using DBMS_OUTPUT.put_line)

我的要求是編寫一個在 COUNTRIES 表中添加值的過程。但是，首先它應該檢查對應的值是否存在於另一個表中，REGIONS 因為它是一個外鍵。僅當值存在時才允許插入 COUNTRIES 表。否則，不會。

我寫了一段代碼並且它工作正常：

create or replace procedure addi3 (c_cntry_id  in out countries.country_id%type,
                                   c_cntr_name in countries.country_name%type, 
                                   c_rgn_id    in countries.region_id%type)
is
    region_exists pls_integer;
begin
    begin
        select 1 into region_exists
        from regions r 
        where r.region_id = c_rgn_id;
    exception
      when no_data_found then
        region_exists := 0;
        DBMS_OUTPUT.PUT_LINE('Already present');    
    end;

    if region_exists = 1 then
      insert into countries(country_id, country_name,region_id)
      values (c_cntry_id, c_cntr_name,c_rgn_id);

      DBMS_OUTPUT.PUT_LINE('Inserted');
    end if;
end addi3;
/

它工作正常，除瞭如果我通過提供 region_id 不存在於區域表中，它正確地沒有插入國家表中。但是，如果 region_id 不存在並且即使我有 DBMS_OUTPUT，我想通過使用 DBMS_OUTPUT.put_line 引發錯誤來增強它。put_line，它沒有顯示適當的錯誤信息。有人可以指導嗎？

參考解法

方法 1:

An edit of your code per your request in comments:

create or replace procedure addi3 (c_cntry_id in out countries.country_id%type,
                                       c_cntr_name in countries.country_name%type, 
                                       c_rgn_id in countries.region_id%type)
is
    region_exists pls_integer;
begin
    begin
        select 1 into region_exists
        from regions r 
        where r.region_id = c_rgn_id;
    exception
        when no_data_found then
            region_exists := 0;
            DBMS_OUTPUT.PUT_LINE('Region not present '||sqlerrm);
            ‑‑ uncomment the RAISE if you want the exception to be
            ‑‑ propagated back to the calling code.
            ‑‑RAISE;

    end;
    ‑‑ if you uncommented the RAISE the IF here is redundant
    ‑‑ because you wouldn't have got here if the region didn't exist.
    if region_exists = 1 then
         insert into countries(country_id, country_name,region_id)
         values (c_cntry_id, c_cntr_name, c_rgn_id);
         DBMS_OUTPUT.PUT_LINE('Inserted');
     end if;
end addi3;
/

(by user3400060、Michael Broughton)

參考文件

1. Display error message using DBMS_OUTPUT.put_line (CC BY‑SA 2.5/3.0/4.0)