Oracle select into данные не найдены
У меня есть sql-код pl, который последовательно выполняет три запроса, чтобы определить уровень соответствия и выполнить некоторую логику
Проблема в том, что когда первый запрос не дал результатов (полностью верный сценарий), я получаю ORA-01403 Данные не найдены.
Я понимаю, что мне нужно включить [Исключение при NO_DATA_FOUND], но как добавить его и перейти к следующему запросу?
4 Answers 4
Just surround your SELECT INTO with begin-end;
This is like try block of PL/Sql
With this technique you can log the reason your statement failed.
If the goal of the block is to catch NO_DATA_FOUND , then WHEN OTHERS is at best pointless and at worst dangerous.
This is just to show technique. In any case, what is the danger? That some other error will be handled? What if you write it to log and then re-throw? Of course, you need to design not only your Sql Block itself but also error handling as well.
The danger is that someone reading that example will literally do nothing in that section: WHEN OTHERS THEN NULL; , which would cause the code to ignore all errors other than NO_DATA_FOUND .
We shouldn't consider people being idiots. Most programmers know how to make a program out of example. We show the technique, they take it from there. General Patton said, "tell people what to do, not how. And you will be surprised with their ingenuity".
I'm not considering anyone an idiot. However, if someone is inexperienced enough to be searching for the solution to a NO_DATA_FOUND error, it's a pretty good bet that they don't know the best practices of handling errors in PL/SQL. In this case, your instruction "do something here or nothing" can be read literally, which could lead to bad code. It seemed worth a comment.
For a SELECT . INTO . statement, the PL/SQL engine assume there will be one, and only one row returned by your query. If there is no row, or more than one, an exception is raised.
FWIW, you can handle such cases without resorting on exception handling by using aggregate functions. That way, there will always be only one row in the result set.
Assuming A can't be NULL in your rows:
If the NULL value is a possible case, just add an extra COUNT(*) column:
Definitely one of the ways to go. Only not fit when you want to know and log reason the SELECT INTO failed.
@T.S. I agree in the general case. But, to quote OP: " when first query has no results (completely valid scenario) . " As far as I understand, there is no need for logging the NO_DATA_FOUND or TOO_MANY_ROWS exceptions in that particular use case.
I see performance issue with this approach, 6 db fetch instead of three, The match queries are very expensive.
Oracle will not allow you to open an implicit cursor (i.e. a select statement in the body of a code block) that returns no rows. You have two options here (3 really, counting @Sylvain's answer, but that is an unusual approach): use an explicit cursor or handle the error.
Explicit Cursor
An explicit cursor is one found in the DECLARE section it must be opened and fetched manually (or in a FOR loop). This has the added advantage that, if you parameterize the query properly, you can write it once and use it multiple times.
Handle the error
If you choose to handle the error, you'll need to create a BEGIN. END block around the code that is going to throw the error. When disregarding an error, it's crucial that you ensure that you are only disregarding the specific error you want avoid, when generated from the specific statement you expect it from. If you simply add the EXCEPTION section to your existing BEGIN. END block, for instance, you couldn't know which statement generated it, or even if it was really the error you expected.
While I'd discourage it, you can catch any other errors in the same exception blocks. However, by definition, those errors would be unexpected, so it would be a poor practice to discard them (you'll never know they even happened!). Generally speaking, if you use a WHEN OTHERS clause in your exception handling, that clause should always conclude with RAISE; , so that the error gets passed up to the next level and is not lost.
когда у меня есть оператор sql, например select * from table1 , он отлично работает, но как только я помещаю его в функцию, я получаю:
есть несколько вещей, которые вы могли бы посмотреть. Основываясь на вашем вопросе, похоже, что владелец функции отличается от владельца таблицы.
1) предоставляет через роль: для создания хранимых процедур и функций на объектах другого пользователя требуется прямой доступ к объектам (вместо доступа через роль).
по умолчанию хранимые процедуры и методы SQL выполняются с помощью привилегии их владельца, а не их текущий пользователь.
Если вы создали таблицу в схеме A и функцию в схеме B, вы должны взглянуть на концепции прав вызывающего/Определителя Oracle, чтобы понять, что может вызвать проблему.
существует большая вероятность того, что привилегии для выбора из таблицы 1 были предоставлены роли, и роль была предоставлена вам. Права, предоставленные роли, недоступны для PL / SQL, написанного пользователем, даже если пользователю была предоставлена роль.
вы видите это много для пользователей, которым была предоставлена роль dba для объектов, принадлежащих sys. Пользователь с ролью dba сможет, скажем, SELECT * from V$SESSION , но не сможет написать функцию, которая включает SELECT * FROM V$SESSION .
исправление заключается в предоставлении явных разрешений на рассматриваемый объект непосредственно пользователю, например, в случае выше, пользователь SYS должен GRANT SELECT ON V_$SESSION TO MyUser;
убедитесь, что функция находится в той же схеме БД, как таблицы.
либо у вас нет разрешения на эту схему / таблицу, либо таблица существует. В основном эта проблема возникает при использовании других таблиц схемы в хранимых процедурах. Например. Если вы используете хранимую процедуру из user/schema ABC и в том же PL/SQL есть таблицы, которые из user / schema XYZ. В этом случае ABC должна иметь GRANT т. е. привилегии таблиц XYZ
предоставить все на ABC;
очень простое решение-добавить имя базы данных с именем таблицы, например, если ваше имя БД DBMS и таблицы info тогда это будет DBMS.info для любого запроса.
если инструкция SELECT INTO не возвращает по крайней мере одну строку, Ora-01403 выбрасывается.
для каждой другой СУБД я знаю, что это нормально для выбора. Только Oracle обрабатывает SELECT таким образом.
почему?
на мой взгляд, вам не нужно это исключение. Это слишком накладно. Иногда это удобно, но вам нужно написать целый блок BEGIN, EXCEPTION, WHEN, END.
есть ли существенные причины я не видишь?
блок исключений не нужны, вы можете использовать его или нет, в зависимости от контекста.
здесь вы активно игнорируете исключение (процедура вернется успешно), но большую часть времени, если вы делаете выбор, вы хотите, чтобы не если он не возвращает строк, считать:
В общем, единственными исключениями, которые вы должны поймать, являются ожидаемые исключения (т. е. это не должно быть стандартом для ловли всех ORA-01403 или всех исключений в этом отношении).
но нам все равно нужно ответить на вопрос "почему возникает исключение в случае, когда SELECT не имеет данных для извлечения".
Я считаю, что это делается, потому что это обычная ситуация, которая в противном случае может быть упущена. Написание кода, как будто он всегда ожидает найти данные, - это обычная вещь, и если бы мы должны были поставить проверки ошибок, такие как
вероятно, IMHO, что проверка на SQLCODE = 100 будет часто пропускаться. Имеющий исключение, поднятое баранами, прямо в нос, что А) важное условие (данные не найдены) произошло, и Б) на это не было сделано никакого учета. IMO, имеющий PL / SQL engine, вызывает исключение, лучше, чем программа весело продолжает свой путь в предположении, что данные были получены, когда на самом деле это не так, что может привести ко всем видам других, чем веселых проблем.
поделиться и наслаждаться.
вы можете попробовать использовать MIN для предложения исключения использования.
тогда фиктивная переменная будет NULL
потому что вы делаете выбор, в который требуется ровно одна строка (больше строк также будет ошибкой).
Если у вас может быть одна строка или нет, вы можете использовать курсор.
Это не работа базы данных, чтобы решить для вас, что отсутствующая строка не является ошибкой, и просто установите значение null.
потому что неясно, что должен делать движок PL/SQL - должен ли он выйти из блока? Должен ли он нажимать с NULL в переменной? Что делать, если в следующем блоке вы попытаетесь вставить это в столбец NOT NULL, как он должен сообщить о местоположении ошибки? Сделав это исключением, вы должны быть откровенны.
вы также можете использовать SQL макс или мин функции. Если строка не возвращается, то эти функции вернут NULL.
например: Выберите макс(колонка 1) В переменные из таблицы Где Column1 = 'Value';
на макс функция вернет максимальное значение или, если строка не будет возвращена, она вернет NULL.
У меня есть sql-код pl, который последовательно выполняет три запроса, чтобы определить уровень соответствия и выполнить некоторую логику
. Проблема в том, что когда первый запрос не имеет результатов (полностью допустимый сценарий), я получаю ORA-01403 Данные не найдены.
Я понимаю, что мне нужно включить [Исключение при NO_DATA_FOUND], но как добавить его и перейти к следующему запросу?
Просто комментарий, запросы на сопоставление дороги - когда первый запрос возвращает совпадение - мне не нужно получать запросы 2 и 3 .
Просто окружить SELECT INTO с begin-end;
Это похоже на try блок PL/Sql
С помощью этой техники вы можете записать причину, по которой ваш оператор не удался.
Если цель блока - поймать NO_DATA_FOUND , то WHEN OTHERS это в лучшем случае бессмысленно, а в худшем - опасно.
Это просто для демонстрации техники. В любом случае, в чем опасность? Что будет обрабатываться какая-то другая ошибка? Что если записать в лог, а потом заново закинуть? Конечно, вам нужно спроектировать не только сам блок Sql, но и обработку ошибок.
Опасность заключается в том, что кто-то, читающий этот пример, буквально ничего не сделает в этом разделе : WHEN OTHERS THEN NULL; , что приведет к тому, что код будет игнорировать все ошибки, кроме NO_DATA_FOUND .
Мы не должны считать людей идиотами. Большинство программистов знают, как составить программу из примера. Показываем технику, оттуда берут. Генерал Паттон сказал: «Говорите людям, что делать, а не как. И вы будете удивлены их изобретательностью».
Я никого не считаю идиотом. Однако, если кто-то недостаточно опытен, чтобы искать решение NO_DATA_FOUND ошибки, можно сделать ставку, что он не знает лучших практик обработки ошибок в PL / SQL. В этом случае ваша инструкция «сделай что-нибудь здесь или ничего» может быть прочитана буквально, что может привести к плохому коду. Казалось, стоит прокомментировать.
Для SELECT . INTO . оператора механизм PL / SQL предполагает, что ваш запрос будет возвращать одну и только одну строку. Если строки нет или больше одной, возникает исключение.
FWIW, вы можете обрабатывать такие случаи, не прибегая к обработке исключений, с помощью агрегатных функций. Таким образом, в наборе результатов всегда будет только одна строка.
Предполагая, что A не может быть NULL в ваших строках:
Если NULL значение является возможным, просто добавьте дополнительный COUNT(*) столбец:
Определенно один из способов пойти. Только не подходит, когда вы хотите узнать и записать причину SELECT INTO неудачи.
@TS Согласен в общем случае. Но, цитируя OP: «когда первый запрос не дает результатов (полностью допустимый сценарий) . » Насколько я понимаю, нет необходимости регистрировать исключения NO_DATA_FOUND или TOO_MANY_ROWS в этом конкретном случае использования.
Я вижу проблему с производительностью при таком подходе, выборка 6 дБ вместо трех, запросы на сопоставление очень дороги .
Oracle не позволит вам открыть неявный курсор (т. Е. select Оператор в теле блока кода), который не возвращает строк. У вас есть два варианта (на самом деле 3, считая ответ @Sylvain, но это необычный подход): использовать явный курсор или обработать ошибку.
Явный курсор
Явный курсор - это курсор, найденный в DECLARE разделе, который необходимо открыть и получить вручную (или в FOR цикле). Это дает дополнительное преимущество: если вы правильно параметризуете запрос, вы можете написать его один раз и использовать несколько раз.
Обработайте ошибку
Если вы решите обработать ошибку, вам нужно будет создать BEGIN. END блок вокруг кода, который будет вызывать ошибку. При игнорировании ошибки очень важно убедиться, что вы игнорируете только конкретную ошибку, которую хотите избежать, когда она сгенерирована из определенного оператора, от которого вы ее ожидаете. Например, если вы просто добавите EXCEPTION раздел в существующий BEGIN. END блок, вы не сможете узнать, какой оператор его сгенерировал, или даже если это была действительно ожидаемая вами ошибка.
Хотя я бы не одобрил это, вы можете поймать любые другие ошибки в тех же блоках исключений. Однако по определению эти ошибки были бы неожиданными, поэтому было бы плохой практикой отбрасывать их (вы никогда не узнаете, что они вообще произошли!). Вообще говоря, если вы используете WHEN OTHERS предложение при обработке исключений, это предложение всегда должно заканчиваться с RAISE; , чтобы ошибка передавалась на следующий уровень и не терялась.
4 ответа
Просто окружите свой SELECT INTO begin-end;
Это похоже на блок try из PL/Sql
С помощью этой техники вы можете записать причину, по которой ваш оператор не удался.
Oracle не позволит вам открыть неявный курсор (т.е. оператор select в теле блока кода), который не возвращает строк. У вас есть два варианта (на самом деле 3, считая ответ @Sylvain, но это необычный подход): использовать явный курсор или обработать ошибку.
Явный курсор
Явный курсор - это курсор, который находится в разделе DECLARE , он должен быть открыт и извлечен вручную (или в цикле FOR ). Это дает дополнительное преимущество: если вы правильно параметризуете запрос, вы можете написать его один раз и использовать несколько раз.
Обработайте ошибку
Если вы решите обработать ошибку, вам нужно будет создать блок BEGIN. END вокруг кода, который будет вызывать ошибку. При игнорировании ошибки очень важно убедиться, что вы игнорируете только конкретную ошибку, которую хотите избежать, когда она сгенерирована из определенного оператора, от которого вы ее ожидаете. Если вы просто добавите раздел EXCEPTION к существующему блоку BEGIN. END , например, вы не сможете узнать, какой оператор его сгенерировал, или даже если это была действительно ожидаемая вами ошибка.
Хотя я бы не одобрил это, вы можете поймать любые другие ошибки в тех же блоках исключений. Однако по определению эти ошибки были бы неожиданными, поэтому было бы плохой практикой отбрасывать их (вы никогда не узнаете, что они вообще произошли!). Вообще говоря, если вы используете предложение WHEN OTHERS при обработке исключений, это предложение всегда должно заканчиваться RAISE; , чтобы ошибка передавалась на следующий уровень и не терялась.
Для оператора SELECT . INTO . механизм PL / SQL предполагает, что будет одна и только одна строка, возвращенная вашим запросом. Если строки нет или больше одной, возникает исключение.
FWIW, вы можете обрабатывать такие случаи, не прибегая к обработке исключений, с помощью агрегатных функций. Таким образом, в наборе результатов всегда будет только одна строка.
Предполагая, что A не может быть NULL в ваших строках:
Если значение NULL возможно, просто добавьте дополнительный столбец COUNT(*) :
I have a pl sql code that execute three queries sequentially to determine a match level and do some logic
The issue is - when first query has no results (completely valid scenario) I get ORA-01403 No data found.
I understand that I need to incorporate [ Exception clause when NO_DATA_FOUND ]- but how to add it and continue to the next query?
Just a comment, match queries are expensive - when first query return a match - i don't need to fetch query 2 and three.
Читайте также: