Курсоры с ошибкой в oracle

An interesting question! Just wanted to add a few things.

To me, the real issue is in depending on IS_OPEN to determine if a cursor is valid or not. Oracle can throw INVALID_CURSOR for many reasons, and it is possible to have an «open» cursor that is not valid. Seems reasonable to assume that an open cursor must be valid (and we can therefore fetch from it or do other operations, like a simple close), but this isn’t necessarily the case.

For example, you cannot use cursor variables in remote procedure calls (via dblinks). This same example, even using Alex’s workaround, would fail if the open was called on 1 db instance and the fetch on another (if nested_test, any version, was defined on db_A and then called from db_B). The test for ISOPEN, however, would still return TRUE, but then trying to use the cursor (fetch) would fail.

INVALID_CURSOR can be raised for other reasons (like going beyond the max open cursors, or sometimes opening a cursor and waiting a while before trying to use it).

All that said, there is no «ISVALID» test that I know of. The best approach imo is to open, fetch and close cursors within the same program or subprogram. Creating a procedure whos responsibility is to just OPEN a cursor is a bit strange to me (but I’m sure there was some reason), and can cause hard to explain issues (like this one). If you must have another program open a cursor for you, then you might want to enclose the code that fetches and eventually closes the cursor in an anonymous block and catch the INVALID_CURSOR exception.

Just my ramblings

Одной из важнейших характеристик PL/SQL является тесная интеграция с базой данных Oracle в отношении как изменения данных в таблицах, так и выборки данных из таблиц. В этом блоге рассматриваются элементы PL/SQL, связанные с выборкой информации из базы данных и ее обработкой в программах PL/SQL.

При выполнении команды SQL из PL/SQL РСУБД Oracle назначает ей приватную рабочую область, а некоторые данные записывает в системную глобальную область (SGA, System Global Area). В приватной рабочей области содержится информация о команде SQL и набор данных, возвращаемых или обрабатываемых этой командой. PL/SQL предоставляет программистам несколько механизмов доступа к этой рабочей области и содержащейся в ней информации; все они так или иначе связаны с опреде­лением курсоров и выполнением операций с ними.

• Неявные курсоры. Команда SELECT.. .INTO считывает одну строку данных и при­сваивает ее в качестве значения локальной переменной программы. Это простейший (и зачастую наиболее эффективный) способ доступа к данным, но он часто ведет к написанию сходных и даже одинаковых SQL-команд SELECT во многих местах программы.
• Явные курсоры. Запрос можно явно объявить как курсор в разделе объявлений локального блока или пакета. После этого такой курсор можно будет открывать и выбирать из него данные в одной или нескольких программах, причем возмож­ности управления явным курсором шире, чем у неявного.
• Курсорные переменные. Курсорные переменные (в объявлении которых задается тип REF CURSOR) позволяют передавать из программы в программу указатель на результирующий набор строк запроса. Любая программа, для которой доступна такая переменная, может открыть курсор, извлечь из него необходимые данные и закрыть его.
•  Курсорные выражения. Ключевое слово CURSOR превращает команду SELECT в набор REF CURSOR, который может использоваться совместно с табличными функциями для повышения производительности приложения.
• Динамические SQL-запросы. Oracle позволяет динамически конструировать и вы­полнять запросы с использованием либо встроенного динамического SQL либо программ пакета DMBS_SQL. Этот встроенный пакет описывается в документации Oracle, а также в книге Oracle Built-in Packages (O’Reilly).

Основные принципы работы с курсорами

Курсор проще всего представить себе как указатель на таблицу в базе данных. Напри­мер, следующее объявление связывает всю таблицу employee с курсором employee_cur:

CURSOR employee_cur IS SELECT * FROM employee;

Объявленный курсор можно открыть:

OPEN employee_cur;

Далее из него можно выбирать строки:

FETCH employee_cur INTO employee_rec;

Завершив работу с курсором, его следует закрыть:

CLOSE employee_cur;

В этом случае каждая выбранная из курсора запись представляет строку таблицы employee. Однако с курсором можно связать любую допустимую команду SELECT. В сле­дующем примере в объявлении курсора объединяются три таблицы:

DECLARE
CURSOR joke_feedback_cur IS
   SELECT J.name, R.laugh_volume, C.name FROM Joke J, response R, comedian C WHERE J.joke_id = R.joke_id AND R.joker_id = C.joker_id;
BEGIN
END;

В данном случае курсор действует не как указатель на конкретную таблицу базы дан­ных — он указывает на виртуальную таблицу или неявное представление, определяемое командой SELECT. (Такая таблица называется виртуальной, потому что команда SELECT генерирует данные с табличной структурой, но эта таблица существует только вре­менно, пока программа работает с возвращенными командой данными.) Если тройное объединение возвращает таблицу из 20 строк и 3 столбцов, то курсор действует как указатель на эти 20 строк.

Терминология

В PL/SQL имеется множество возможностей выполнения команд SQL, и все они реа­лизованы в программах как курсоры того или иного типа. Прежде чем приступить к их освоению, необходимо познакомиться с методами выборки данных и используемой при этом терминологией.

• Статический SQL. Команда SQL называется статической, если она полностью определяется во время компиляции программы.
• Динамический SQL. Команда SQL называется динамической, если она строится и выполняется на стадии выполнения программы, так что в программном коде нет ее фиксированного объявления. Для динамического выполнения команд SQL мо­гут использоваться программы встроенного пакета DBMS_SQL (имеющегося во всех версиях Oracle) или встроенный динамический SQL.
• Результирующий набор строк. Набор строк с результирующими данными, удов­летворяющими критериям, определяемым командой SQL. Результирующий набор кэшируется в системной глобальной области с целью ускорения чтения и модифи­кации его данных.
• Неявный курсор. При каждом выполнении команды DML (INSERT, UPDATE, MERGE или delete) или команды select into, возвращающей строку из базы данных прямо в структуру данных программы, PL/SQL создает неявный курсор. Курсор этого типа называется неявным, поскольку Oracle автоматически выполняет многие связанные с ним операции, такие как открытие, выборка данных и даже закрытие.
• Явный курсор. Команда SELECT, явно определенная в программе как курсор. Все опе­рации с явным курсором (открытие, выборка данных, закрытие и т. д.) в программе должны выполняться явно. Как правило, явные курсоры используются для выборки из базы данных набора строк с использованием статического SQL.
•  Курсорная переменная. Объявленная программистом переменная, указывающая на объект курсора в базе данных. Ее значение (то есть указатель на курсор или резуль­тирующий набор строк) во время выполнения программы может меняться, как у всех остальных переменных. В разные моменты времени курсорная переменная мо­жет указывать на разные объекты курсора. Курсорную переменную можно передать в качестве параметра процедуре или функции. Такие переменные очень полезны для передачи результирующих наборов из программ PL/SQL в другие среды (например, Java или Visual Basic).
• Атрибут курсора. Атрибут курсора имеет форму %имя_атрибута и добавляется к имени курсора или курсорной переменной. Это что-то вроде внутренней пере­менной Oracle, возвращающей информацию о состоянии курсора — например о том, открыт ли курсор, или сколько строк из курсора вернул запрос. У явных и неявных курсоров и в динамическом SQL в атрибутах курсоров существуют некоторые раз­личия, которые рассматриваются в этой статье.
• SELECT FOR UPDATE. Разновидность обычной команды SELECT, устанавливающая блокировку на каждую возвращаемую запросом строку данных. Пользоваться ею следует только в тех случаях, когда нужно «зарезервировать» запрошенные данные, чтобы никто другой не мог изменить их, пока с ними работаете вы.
• Пакетная обработка. В Oracle8i и выше PL/SQL поддерживает запросы с секцией BULK COLLECT, позволяющей за один раз выбрать из базы данных более одной строки.

Типичные операции с запросами и курсорами

Независимо от типа курсора процесс выполнения команд SQL всегда состоит из одних и тех же действий. В одних случаях PL/SQL производит их автоматически, а в других, как, например, при использовании явного курсора, они явно организуются програм­мистом.

• Разбор. Первым шагом при обработке команды SQL должен быть ее разбор (син­таксический анализ), то есть проверка ее корректности и формирование плана выполнения (с применением оптимизации по синтаксису или по стоимости в за­висимости от того, какое значение параметра 0PTIMIZER_M0DE задал администратор базы данных).
• Привязка. Привязкой называется установление соответствия между значениями программы и параметрами команды SQL. Для статического SQL привязка произ­водится ядром PL/SQL. Привязка параметров в динамическом SQL выполняется явно с использованием переменных привязки.
• Открытие. При открытии курсора определяется результирующий набор строк команд SQL, для чего используются переменные привязки. Указатель активной или текущей строки указывает на первую строку результирующего набора. Иногда явное открытие курсора не требуется; ядро PL/SQL выполняет эту операцию авто­матически (так происходит в случае применения неявных курсоров и встроенного динамического SQL).
• Выполнение. На этой стадии команда выполняется ядром SQL.
• Выборка. Выборка очередной строки из результирующего набора строк курсора осуществляется командой FETCH. После каждой выборки PL/SQL перемещает ука­затель на одну строку вперед. Работая с явными курсорами, помните, что и после завершения перебора всех строк можно снова и снова выполнять команду FETCH, но PL/SQL ничего не будет делать (и не станет инициировать исключение) — для вы­явления этого условия следует использовать атрибуты курсора.
• Закрытие. Операция закрывает курсор и освобождает используемую им память. Закрытый курсор уже не содержит результирующий набор строк. Иногда явное за­крытие курсора не требуется, последовательность PL/SQL делает это автоматически (для неявных курсоров и встроенного динамического SQL).

На рис. 1 показано, как некоторые из этих операций используются для выборки информации из базы данных в программу PL/SQL.

Рис. 1. Упрощенная схема выборки данных с использованием курсора

Знакомство с атрибутами курсоров

В этом разделе перечисляются и вкратце описываются атрибуты курсоров.

PL/SQL поддерживает шесть атрибутов курсоров, перечисленных в табл. 1.

Таблица 1. Атрибуты курсоров

Чтобы обратиться к атрибуту курсора, укажите в виде его префикса имя курсора или курсорной переменной и символ %:

имя_курсора%имя_атрибута

В качестве имен неявных курсоров используется префикс SQL, например SQL%NOTFOUND. Ниже приведены краткие описания всех атрибутов курсоров.

Атрибут %FOUND

Атрибут %FOUND возвращает информацию о состоянии последней операции FETCH с курсо­ром. Если последний вызов FETCH для курсора вернул строку, то возвращается значение TRUE, а если строка не была получена, возвращается FALSE.

Если курсор еще не был открыт, база данных инициирует исключение INVALID_CURS0R. В следующем примере я перебираю все строки курсора caller_cur, присваиваю все звонки, введенные до сегодняшнего дня для конкретного позвонившего, после чего пере­хожу к следующей записи. При достижении последней записи атрибут %FOUND явного курсора возвращает FALSE, и выполнение простого цикла прерывается. Атрибут %FOUND неявного курсора также проверяется после команды UPDATE:

FOR caller_rec IN caller_cur LOOP
   UPDATE call
   SET caller_id = caller_rec.caller_id WHERE call_timestamp < SYSDATE;
   IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('Calls updated for ' || caller_rec.caller_id);
   END IF;
END LOOP;

Атрибут %NOTFOUND

Атрибут %NOTFOUND по смыслу противоположен %FOUND: он возвращает TRUE, если по­следняя операция FETCH с курсором не вернула строки (как правило, из-за того, что последняя строка уже была прочитана). Если курсор не может вернуть строку из-за ошибки, инициируется соответствующее исключение.

Если курсор еще не был открыт, база данных инициирует исключение INVALID_CURSOR. В каких случаях следует использовать %FOUND, а когда предпочтение отдается %NOTFOUND? Используйте ту формулировку, которая более естественно подходит для вашего кода. В предыдущем примере для выхода из цикла использовалась следующая команда:

EXIT WHEN NOT caller_cur%FOUND;

Альтернативная — и возможно, более понятная — формулировка могла бы использовать

%NOTFOUND:
EXIT WHEN caller_cur%NOTFOUND;

Атрибут %ROWCOUNT

Атрибут %ROWCOUNT возвращает количество записей, прочитанных из курсора к моменту запроса атрибута. При исходном открытии курсора атрибут %ROWCOUNT равен 0. При обра­щении к атрибуту %ROWCOUNT курсора, который еще не был открыт, инициируется исклю­чение INVALID_CURSOR. После выборки каждой записи %ROWCOUNT увеличивается на единицу. Используйте атрибут %ROWCOUNT для проверки того, что программа прочитала (или обновила в случае DML) нужное количество строк, или для прерывания выполнения после заданного числа итераций. Пример:

BEGIN
UPDATE employees SET last_name = 'FEUERSTEIN';
DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);
END;

Атрибут %ISOPEN

Атрибут %ISOPEN возвращает TRUE, если курсор открыт; в противном случае возвраща­ется FALSE.

Приведем типичный пример использования этого атрибута, который гарантирует, что курсор не остается открытым, если в программе произойдет что-то непредвиденное:

DECLARE
CURSOR happiness_cur IS SELECT simple_delights FROM ...;
BEGIN
   OPEN happiness_cur;
   IF happiness_cur%ISOPEN THEN ...
      EXCEPTION
      WHEN OTHERS THEN
      IF happiness_cur%ISOPEN THEN close happiness_cur;
   END IF;
END;

Атрибут %BULK_ROWCOUNT

Атрибут %BULK_ROWCOUNT, предназначенный для использования с командой FORALL, воз­вращает количество строк, обработанных при каждом выполнении DML. Этот атрибут обладает семантикой ассоциативного массива.

Атрибут %BULK_EXCEPTIONS

Атрибут %BULK_EXCEPTIONS, также предназначенный для использования с командой FORALL, возвращает информацию об исключении, которое может быть инициировано при каждом выполнении DML. Этот атрибут обладает семантикой ассо­циативного массива записей.

На атрибуты курсоров можно ссылаться в коде PL/SQL, как показано в приве­денных примерах, но вы не сможете напрямую обращаться к ним в команде SQL. Например, при попытке использовать атрибут %ROWCOUNT в секции WHERE команды SELECT:

SELECT caller_id, company_id FROM caller 
WHERE company_id = company_cur%ROWCOUNT;

компилятор выдает ошибку PLS-00229: Attribute expression within SQL expression.

Ссылки на переменные PL/SQL в курсорах

Поскольку курсор должен быть связан с командой SELECT, в нем всегда присутствует хотя бы одна ссылка на таблицу базы данных; по ней (и по содержимому условия WHERE) Oracle определяет, какие строки будут возвращены в составе активного набора. Однако это не означает, что команда SELECT может возвращать информацию только из базы данных.

Список выражений, задаваемых между ключевыми словами SELECT и FROM, называется списком выборки. Во встроенном SQL список выборки может содержать столбцы и вы­ражения (вызовы SQL-функций для этих столбцов, константы и т. д.). В PL/SQL список выборки обычно содержит переменные PL/SQL и сложные выражения.

 На локальные данные программы PL/SQL (переменные и константы), а также на перемен­ные привязки хост-среды можно ссылаться в предложениях WHERE, GROUP BY и HAVING команды SELECT курсора. Ссылки на переменные PL/SQL можно (и должно) уточнять именем ее области видимости (именем процедуры, именем пакета и т. д.), особенно в командах SQL.

Выбор между явным и неявным курсорами

Все последние годы знатоки Oracle (включая и авторов данной книги) убежденно до­казывали, что для однострочной выборки данных никогда не следует использовать неявные курсоры. Это мотивировалось тем, что неявные курсоры, соответствуя стан­дарту ISO, всегда выполняют две выборки, из-за чего они уступают по эффективности явным курсорам.

Так утверждалось и в первых двух изданиях этой книги, но пришло время нарушить эту традицию (вместе со многими другими). Начиная с Oracle8, в результате целенаправ­ленных оптимизаций неявные курсоры выполняются даже эффективнее эквивалентных явных курсоров.

Означает ли это, что теперь всегда лучше пользоваться неявными курсорами? Вовсе нет. В пользу применения явных курсоров существуют убедительные доводы.

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

Поэтому вместо формулировки «явный или неявный?» лучше спросить: «инкапсули­рованный или открытый?» И ответ будет таким: всегда инкапсулируйте однострочные запросы, скрывая их за интерфейсом функции (желательно пакетной) и возвращая данные через RETURN.

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