Быстрая проверка существования записи в Oracle

← →
Ломброзо © (2007-03-25 14:19) [0]

Как известно, В MSSQL оператор EXISTS может использоваться как скалярная функция, которая возвращает булевый результат, и её можно использовать для быстрой проверки существования записи по условию, как то:

IF EXISTS(SELECT * FROM CUSTOMERS WHERE NAME = @NAME) THEN ....

В Oracle такая фича не работает и я выкручиваюсь через подсчёт числа записей:

DECLARE iCNT NUMBER; BEGIN SELECT COUNT(ID) INTO iCNT FROM CUSTOMERS WHERE NAME = :NAME; IF iCNT = 0 THEN ...

Есть ли в оракле какой-нибудь заменитель сей громоздкой конструкции?

← →
Desdechado © (2007-03-25 15:36) [1]

Не знаю, как в 10-ке, а в 9-ке я не нашел.
Обычно использую одну из 3 конструкций (какая ближе к контексту операции):
CREATE OR REPLACE FUNCTION fn_TESTSELECT RETURN INTEGER AS nRes INTEGER := 10; BEGIN /* такая конструкция вызовет исключение NO_DATA_FOUND, что прервет выполнение процедуры */ SELECT class_id INTO nRes FROM Classes WHERE class_id = -1; /* 1-й способ обойти исключение */ -- Позволяет также обойти случай с выборкой нескольких строк в одну переменную, -- что тоже вызывает исключение. -- Вернет первое попавшееся значение по условию или не изменит состояние -- переменной nRes при отсутствии данных в таблице. -- Наиболее близкое к поведению InterBase. FOR x IN( SELECT class_id INTO nRes FROM Classes WHERE class_id = -1 ) LOOP nRes := x.class_id; EXIT; -- прерывание цикла END LOOP; /* 2-й способ обойти исключение */ -- Позволяет проверить разные виды исключений, не только с отсутствием данных. -- При исключении вернет то, что указано в ветке WHEN. -- Гибко, но громоздко. BEGIN SELECT class_id INTO nRes FROM Classes WHERE class_id = -1; EXCEPTION WHEN NO_DATA_FOUND THEN nRes := -1; END; /* 3-й способ обойти исключение */ -- Подходит только для случая выборки одного числового поля, -- чреват логическими ошибками при наличии нескольких разных значений по условию. -- Вернет NULL при отсутствии данных. SELECT Max( class_id ) INTO nRes FROM Classes WHERE class_id = -1; -- проверим, что же нашлось RETURN nRes; END;

Count хуже тем, что все-таки индекс (если он есть) прорабатывается дольше. Если нет, то вообще дрова.

← →
Petr V. Abramov © (2007-03-25 15:47) [2]

SELECT COUNT(ID) INTO iCNT FROM CUSTOMERS WHERE NAME = :NAME;
IF iCNT = 0 THEN ...

скользкая довольно конструкция. к моменту выполнения if`а count уже может стать каким угодно, т.е., вообще говоря, оно эквивалентно if random.

for update нужно по-хорошему

← →
Ломброзо © (2007-03-25 17:04) [3]

Desdechado © (25.03.07 15:36) [1]
В 99% COUNT считается по индексированным полям, а посему, раз более простого способа проверить наличие записи не существует, продолжу пользоваться COUNT :)

Petr V. Abramov © (25.03.07 15:47) [2]

Это не клиентский код, серверная ХП. Проверка предусловий.
В хранимой COUNT может сбойнуть? насколько я знаю, ХП выполняется в транзакции. Объясни, пожалуйста, на пальцах :) что где нужно написать, чтобы между двумя операторами никакой паразит не смог прошмыгнуть

← →
Petr V. Abramov © (2007-03-25 17:19) [4]

> В хранимой COUNT может сбойнуть?
ну уж слишком круто было бы :)
> насколько я знаю, ХП выполняется в транзакции.
ну и что?

вот смотри: :)

SELECT COUNT(ID) INTO iCNT FROM CUSTOMERS WHERE NAME = :NAME;
cnt чему-то равно, например, нулю.
IF iCNT = 0 THEN ...
if-то начинает выполняться не "прям сразу", и между селектом и и ифом вполне может кто-то закоммититься, и в результате в контектсте твоей же транзакции, если выполнить селект, он вернет уже, например, единицу. Но ты принимаешь решение на основе переменной cnt. В результате, если например, в этом IF iCNT = 0 THEN ... ты захочешь сказать insert, получишь дубль, хотя и честно проверял.
И не говори, что это "маловероятно". Законы Мэрфи имеют приоритет над законами больших чисел. Данный пример, конечно, упрощенный, но чем "дальше" от селекта ты принимаешь решение на основе его результата, тем быстрее словишь глюк, который еще и никто не воспроизведет специально.

← →
Ломброзо © (2007-03-25 17:25) [5]

а, понятно. Select for update лочит те строки, в которых я считаю count, и до завершения моей транзакции вторая, которая хочет выполнить commit, или будет висеть, или кинет исключение?

← →
Petr V. Abramov © (2007-03-25 17:45) [6]

> Ломброзо © (25.03.07 17:25) [5]
именно
только select count ... for update не пройдет :)

select id bulk collect into coll
а потом coll.count

вот в данном твоем случае не проще ли индекс уникальный сгородить и ловить исключение?

← →
Ломброзо © (2007-03-25 17:56) [7]

Petr V. Abramov © (25.03.07 17:45) [6]

аспасиба. Bulk collect тоже штука малознакомая, пойду изучать.
Индексы не пойдут, я хотел универсальное решение. В общем-то с тем же и остался, разве что с поправкой на конкурирующие транзакции. Буду иметь в виду.

← →
Petr V. Abramov © (2007-03-25 18:28) [8]

> Индексы не пойдут, я хотел универсальное решение.
универсальное решение чего? ну обходятся ж люди без вот этих count-тов, и не кашляют. Может, не там ищешь?

← →
Ломброзо © (2007-03-25 19:00) [9]

Не, я не спорю с тем, что можно обойтись уникальным констрейнтом, но из соображений юзабилити гораздо красивше не вываливать на клиента мессежд бокс со страшным текстом о том, что Unique constraint violated, а самому ручками проверить входные данные и в случае чего - на литературном русском языке порадовать его внятной инструкцией о причине ошибки и способах её разрешения - безо всякого намёка на имена таблиц, полей и констрейнтов.

Если есть какой-то другой способ трансформации низкоуровневых ошибок СУБД во внятные русскоязычные сообщения - готов выслушать :)

(тем паче, что констрейнт - это, по всем канонам проектирования, последний бастион обороны (первый - клиент, второй-сервер приложений, третий - бизнес-логика в хранимых процедурах) для обеспечения непротиворечивости и ссылочной целостности)

← →
Petr V. Abramov © (2007-03-25 19:12) [10]

> констрейнт - это, по всем канонам проектирования, последний бастион обороны
это да

> из соображений юзабилити гораздо красивше не вываливать на клиента мессежд бокс со страшным текстом
так и не вываливай на клиента. обработай в хранимке, и если уж совсем поделать нечего, подыми свой с внятным текстом.

> Если есть какой-то другой способ трансформации низкоуровневых ошибок
> СУБД во внятные русскоязычные сообщения - готов выслушать :)
when others then if sqlcode = 1 then -- тут в автономной транзакции запиши куда-нить оригинальный sqlerrm raise_application_error(-2001, "сам дурак и никаких имен таблиц" ); else raise; end if;

еще обрати внимание на pragma exception_init

← →
Evgeny V © (2007-03-26 07:48) [11]

В Оракле например -
SELECT 1 FROM dual where exists(select sernum from myshema.avl_sensor where dk > SYSDATE-1)

Есть запись - получим 1, нет -получим NULL

В свое время узнал это от Шевченко Игоря и Внука:-)

← →
roottim © (2007-03-26 09:20) [12]

у тебя нет into, речь ведь о pl/sql
а посему влетит exception no_data_found который обработать надо...
посему тот же результат без гемороя можно получить обернув его
select nvl((SELECT 1 FROM dual where exists(select 1 from myshema.avl_sensor where dk > SYSDATE-1), 0) f_exists into v_result from dual

← →
Evgeny V © (2007-03-26 09:38) [13]

> roottim © (26.03.07 09:20) [12]

Добавить INTO и перевести в PL/SQL не проблема думаю
Ну если надо строго 1 или 0, то можно и такие варианты

Первый с COUNT, хоть и COUNT, но считать не более одной записи...

SELECT COUNT(*) from (SELECT 1 FROM dual where exists(select sernum from myshema.avl_sensor where dk > SYSDATE-1))

Второй вариант

select nvl(
(select 1 from adm.avl_sensor
where dk > SYSDATE-1
and rownum<=1),0) from dual

← →
evvcom © (2007-03-26 12:06) [14]

А что после
> IF iCNT = 0 THEN ...
? От этого зависит красота решения. А то "вставят, не вставят" :) Каков вообще смысл проверки?

← →
ANB © (2007-03-26 13:05) [15]

Практически все проверки, выполняемые до DML могут оказаться ложными к моменту собственно изменения данных. Что, однако, не снижает их полезности, т.к. минимум 90% случаев они обрабатывают.
Вывод - если операция не критична по скорости и не лень эти проверки писать - то есть смысл таки их написать. Но при этом не забывать об ограничениях.
Все имхо.

← →
Petr V. Abramov © (2007-03-26 13:51) [16]

> минимум 90% случаев они обрабатывают.
тогда можно не писать никаких проверок, а проверять резльтат random.

← →
ANB © (2007-03-26 17:11) [17]

> Petr V. Abramov © (26.03.07 13:51) [16]

А в 10% сработает констрайнт. При чем тут random ?

← →
Val © (2007-03-26 18:09) [18]

Честно говоря, я бы попытался сделать вставку и обработать исключение, а не выборку, а затем обработку условия и вставку, которая может не произойти по вышеописанным причинам.
p.s. топик возник, думается, как тут говорили, из-за "незначительной" разницы между блокировочником и версионником ;)

← →
ANB © (2007-03-26 18:20) [19]

> "незначительной" разницы между блокировочником и версионником

Эээ. А кто тут версионник ? Про IB/FB разговора вроде не было . . .

← →
Val © (2007-03-26 18:23) [20]

я, наверное.

← →
Petr V. Abramov © (2007-03-26 23:15) [21]

> ANB © (26.03.07 17:11) [17]
> А в 10% сработает констрайнт. При чем тут random
если есть констрайнт, то получим исключение по факту инсерта. хоть и проверяли, что "нету".
будут и более гадкие ситуациии, при delete в другой транзакции, без exception

> А в 10% сработает констрайнт. При чем тут random
а нахрена писанина, если все ошибочные ситуации этими "10%" и описываются?

имелось в виду, когда констрайнта нет и он моделируется кодом, что стронгли не рекомендуется в документации.

Блин, ну я уже объяснял, зачем. Мне, например, прежде чем в транзакции перенести данные, полученные от некоего девайса, в медкарту, нужно порядка сотни параметров в десятке процедур проверить, причем таким образом, чтобы человек со средним медицинским образованием в обморок не падал при виде непонятных английских слов. То есть буквально на каждый неверный чих нужно выдавать внятное сообщение на русском языке с объяснением причин ошибки и инструкцией во избежание. Как это технически реализовать - плеваться ли из хранимок кодами ошибок в сервер приложений, который их транслирует в текстовые, или прямо из ХП ексепшн кидать - непринципиально.

Вот такой код (с)

when others then if sqlcode = 1 then -- тут в автономной транзакции запиши куда-нить оригинальный sqlerrm raise_application_error(-2001, "сам дурак и никаких имен таблиц" ); else raise; end if;

- мне ничего не говорит о том, по какой причине и в каком месте случилась ошибка, если отключить Debug info и не смотреть стек.

Разумеется, в проверке тех предусловий, которые проверяются в том числе и констрейнтами, ручками, есть некоторое неудобство, но тут предметная область такая. Не боинг, но всё же.

> Не боинг, но всё же.
так бы и ветку озаглавил, хрен вас медиков, поймешь, спросишь в реанимации анекдот, так говорят: "у нас все про покойников" :)))) увы, из жизни....

> Ломброзо ©
че те не устаривает в стандартном подходе Oracle? щас объясним, че ты в нем не понимаешь или workaround придумаем.

Да ладно, select for update я уже освоил на твёрдую тройку и запытал в конкурирующей среде :) Вполне устраивает.

> [23] Ломброзо © (27.03.07 00:18)
> мне ничего не говорит о том, по какой причине и в каком
> месте случилась ошибка

а кто тебе мешает написать:
<procedure>
begin ... begin ... exception ... end; ... begin ... exception ... end; ... begin ... exception ... end; ... exception ... end; Будет тебе и место и смысл

> evvcom © (27.03.07 10:36) [26]

И так обернуть каждый оператор ?

Предварительные проверки, имхо, имеют смысл именно из-за простоты их реализации. Но база все равно должна быть закрыта от мусора ограничениями.

А вот обработка исключений в оракле имеет некоторые проблемы, т.к. до 10-ки при перехвате их портился стек ошибок. И разобраться, где именно произошли проблемы, довольно тяжело.

> [27] ANB © (27.03.07 12:01)
> И так обернуть каждый оператор ?

А это уж, как душе угодно. :)

> А вот обработка исключений в оракле имеет некоторые проблемы,
> т.к. до 10-ки при перехвате их портился стек ошибок. И
> разобраться, где именно произошли проблемы, довольно тяжело.

Вот, видимо, из-за этого и родилась у меня такая идея. А 10-ку на этот счет не тестил пока.

> А 10-ку на этот счет не тестил пока.

Я тестил. Тока теперь там еще одну функцию добавили для выгребания стека. Разобраться, что сломалось, помудохавшись, можно. Но неочевидно все все равно.

> [29] ANB © (27.03.07 14:32)
> Но неочевидно все все равно.

АбЫдна...

Еще как вариант, по тексту навставлять Step := Step + 1; и в exception запись в лог в том числе и Step. Извращений много разных. :)

Быстрая проверка существования записи в Oracle Найти похожие ветки