Правильный способ проверки наличия строки в таблице [pl/sql block]

Я писал несколько заданий вчера, и мне показалось, что я действительно не знаю PROPER и ACCEPTED проверки наличия строки в таблице, когда я использую pl/sql.

В качестве примеров можно использовать таблицу

PERSON(ID, Name);

Очевидно, что не может (если не существует секретного метода):

BEGIN 
  IF EXISTS SELECT id FROM person WHERE ID = 10; 
    -- do things when exists
  ELSE
    -- do things when doesn't exist
  END IF;
END;

Итак, мой стандартный способ решения этого вопроса:

DECLARE
  tmp NUMBER;
BEGIN 
  SELECT id INTO tmp FROM person WHERE id = 10; 
  --do things when record exists
EXCEPTION
  WHEN no_data_found THEN
  --do things when record doesn't exist
END;

Однако я не знаю, согласился ли он на это, или если у вас есть лучший способ проверки, я действительно буду apprieciate, если кто-то может поделиться со мной своей мудростью:)

Приветствия.

Ответы

Ответ 1

Я бы не вводил обычный код в блок исключений. Просто проверьте, существуют ли какие-либо строки, соответствующие вашему условию, и оттуда:

declare
  any_rows_found number;
begin
  select count(*)
  into   any_rows_found
  from   my_table
  where  rownum = 1 and
         ... other conditions ...

  if any_rows_found = 1 then
    ...
  else
    ...
  end if;

Ответ 2

IMO-код с автономным SELECT, используемый для проверки того, существует ли строка в таблице, не использует надлежащее преимущество базы данных. В вашем примере у вас есть твердое идентификационное значение, но не то, как приложения работают в "реальном мире" (по крайней мере, в моем мире - ваше может быть другим:-). В типичном приложении вы будете использовать курсор для поиска данных - так что скажем, у вас есть приложение, которое смотрит на данные счета и должно знать, существует ли клиент. Основной частью приложения может быть что-то вроде

FOR aRow IN (SELECT * FROM INVOICES WHERE DUE_DATE < TRUNC(SYSDATE)-60)
LOOP
  -- do something here
END LOOP;

а в -- do something here вы хотите найти, существует ли клиент, а если не печатать сообщение об ошибке.

Один из способов сделать это - установить какой-то одноэлементный SELECT, как в

-- Check to see if the customer exists in PERSON

BEGIN
  SELECT 'TRUE'
    INTO strCustomer_exists
    FROM PERSON
    WHERE PERSON_ID = aRow.CUSTOMER_ID;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    strCustomer_exists := 'FALSE';
END;

IF strCustomer_exists = 'FALSE' THEN
  DBMS_OUTPUT.PUT_LINE('Customer does not exist!');
END IF;

но IMO это относительно медленно и подвержено ошибкам. ИМО - лучший способ (tm) сделать это - включить его в основной курсор:

FOR aRow IN (SELECT i.*, p.ID AS PERSON_ID
               FROM INVOICES i
               LEFT OUTER JOIN PERSON p
                 ON (p.ID = i.CUSTOMER_PERSON_ID)
               WHERE DUE_DATA < TRUNC(SYSDATE)-60)
LOOP
  -- Check to see if the customer exists in PERSON

  IF aRow.PERSON_ID IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Customer does not exist!');
  END IF;
END LOOP;

Этот код рассчитывает, что PERSON.ID будет объявлен как ПЕРВИЧНЫЙ КЛЮЧ для ЧЕЛОВЕКА (или, по крайней мере, как NOT NULL); логика заключается в том, что если таблица PERSON является внешним соединением с запросом, а PERSON_ID появляется как NULL, это означает, что ни одна строка не найдена в PERSON для данного CUSTOMER_ID, потому что PERSON.ID должен иметь значение (то есть по крайней мере NOT NULL).

Поделитесь и наслаждайтесь.

Ответ 3

Много способов обмануть этого кота. Я поставил простую функцию в каждом пакете таблицы...

function exists( id_in in yourTable.id%type ) return boolean is
  res boolean := false;
begin
  for c1 in ( select 1 from yourTable where id = id_in and rownum = 1 ) loop
    res := true;
    exit; -- only care about one record, so exit.
  end loop;
  return( res );
end exists;

Делает ваши проверки действительно чистыми...

IF pkg.exists(someId) THEN
...
ELSE
...
END IF;

Ответ 4

select nvl(max(1), 0) from mytable;

Этот оператор возвращает 0, если строк нет, 1, если в этой таблице есть хотя бы одна строка. Это быстрее, чем выборка count (*). Оптимизатор "видит", что для ответа на вопрос нужно выбрать только одну строку.

Здесь (подробный) небольшой пример:

declare
  YES constant      signtype := 1;
  NO  constant      signtype := 0;
  v_table_has_rows  signtype;
begin

  select nvl(max(YES), NO)
    into v_table_has_rows
    from mytable -- where ...
  ;

  if v_table_has_rows = YES then
    DBMS_OUTPUT.PUT_LINE ('mytable has at least one row');
  end if;

end;

Ответ 5

Select 'YOU WILL SEE ME' as ANSWER from dual
where exists (select 1 from dual where 1 = 1);

Select 'YOU CAN NOT SEE ME' as ANSWER from dual
where exists (select 1 from dual where 1 = 0);

Select 'YOU WILL SEE ME, TOO' as ANSWER from dual
where not exists (select 1 from dual where 1 = 0);

Ответ 6

Если вы используете явный курсор, он должен быть следующим.

DECLARE
   CURSOR get_id IS 
    SELECT id 
      FROM person 
      WHERE id = 10;

  id_value_ person.id%ROWTYPE;
BEGIN 
   OPEN get_id;
   FETCH get_id INTO id_value_;

   IF (get_id%FOUND) THEN
     DBMS_OUTPUT.PUT_LINE('Record Found.');
   ELSE
     DBMS_OUTPUT.PUT_LINE('Record Not Found.');
   END IF;
   CLOSE get_id;

EXCEPTION
  WHEN no_data_found THEN
  --do things when record doesn't exist
END;