Хранимая процедура Oracle для вставки недвойственных значений и учета ограничений внешнего ключа

Question

Мое требование состоит в том, чтобы написать процедуру для добавления значений в COUNTRIES Таблица. Но сначала он должен проверить, существуют ли соответствующие значения в другой таблице, REGIONS как это внешний ключ. Только если значения существуют, вставка в COUNTRIES стол разрешен Второе требование заключается в том, что у меня не должно быть повторяющихся значений для country_id в COUNTRIES стол как country_id является PK, Я написал этот код, но это исключение:

PLS-00103 Encountered the symbol "WHEN" when expecting one of the following:
begin case
PLS-00103:Encountered the symbol "WHEN" when expecting one of the following: ERROR

Мой код:

create or replace procedure addi5 (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);
    end;

   BEGIN
     INSERT INTO countries(COUNTRY_ID, COUNTRY_NAME,REGION_ID)
     values (c_cntry_id, c_cntr_name,c_rgn_id);

   EXCEPTION
     WHEN dup_val_on_index THEN 
       c_cntry_id := null;
       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');

   ELSE
     WHEN dup_val_on_index
     THEN 
     c_cntry_id := null;

     DBMS_OUTPUT.PUT_LINE('Already present');
   END IF;

end addi5;
/

Может кто-нибудь подсказать мне, что я делаю не так? Или как написать или проверить эти два условия?

Answer 1

Ваша практика кодирования сама по себе не верна. Сначала вы должны попытаться написать свою логику в sql, если это возможно, для удобства чтения, а также для уменьшения переключения контекста между sql и pl / sql engine. Вот пример кода, вам, возможно, придется добавить обработку исключений.

create or replace procedure addi5 (c_cntry_id in countries.country_id%type,
                                   c_cntr_name in countries.country_name%type, 
                                   c_rgn_id in countries.region_id%type)
is
begin
  insert into countries select c_cntry_id, c_cntr_name, c_rgn_id from dual
  where c_rgn_id in (select region_id from regions)
  and c_cntry_id not in (select country_id from countries);
  commit;
end;
/