язык PL/SQL: программирование неявных курсоров

Неявные урсоры PL/SQL - принципы программирования При каждом выполнении команды DML (INSERT, UPDATE, MERGE или delete) или команды SELECT INTO, возвращающей строку из базы данных в структуру данных программы, PL/ SQL автоматически создает для нее курсор. Курсор этого типа называется неявным, поскольку Oracle автоматически выполняет многие связанные с ним операции, такие как выделение курсора, его открытие, выборку строк и т. д.

Оглавление статьи[Показать]

Неявный курсор — это команда SELECT, обладающая следующими характеристиками: О Команда SELECT определяется в исполняемом разделе блока, а не в разделе объявлений, как явные курсоры.

В команде содержится предложение INTO (или BULK COLLECT INTO), которое относится к языку PL/SQL (а не SQL) и представляет собой механизм передачи данных из базы в локальные структуры данных PL/SQL.
Команду SELECT не нужно открывать, выбирать из нее данные и закрывать; все эти операции осуществляются автоматически.

Общая структура неявного запроса выглядит так:

SELECT список_столбцов
   [BULK COLLECT] INTO PL/SQL список_переменных 
   ...продолжение команды SELECT...

Если в программе используется неявный курсор, Oracle автоматически открывает его, выбирает строки и закрывает; над этими операциями программист не властен. Однако он может получить информацию о последней выполненной команде SQL, анализируя значения атрибутов неявного курсора SQL.

В следующих разделах, говоря о неявных курсорах, мы будем иметь в виду команду SELECT INTO, которая извлекает (или пытается извлечь) одну строку данных. Eе разновидность SELECT BULK COLLECT INTO позволяет с помощью одного неявного запроса получить несколько строк данных.

Примеры неявных курсоров

Неявные курсоры часто используются для поиска данных на основе значений первичного ключа. В следующем примере выполняется поиск названия книги по ее коду ISBN:

DECLARE
   l_title books.title%TYPE;
BEGIN
   SELECT title INTO 
      l_title FROM books
    WHERE isbn = '0-596-00121-5';

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

DECLARE
   l_book books%ROWTYPE;
BEGIN
   SELECT *
    INTO 
      l_book 
    FROM books
    WHERE isbn = '0-596-00121-5';

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

SELECT SUM (salary)
   INTO department_total 
   FROM employees 
  WHERE department_id = 10;

Благодаря тесной интеграции PL/SQL с базой данных Oracle с помощью запросов из базы данных можно извлекать и сложные типы данных, такие как объекты и коллекции. Все эти примеры демонстрируют применение неявных запросов для выборки данных одной строки. Если вы хотите получить более одной строки, используйте либо явный курсор, либо конструкцию bulk collect into.

Как упоминалось ранее, я рекомендую всегда «скрывать» однострочные запросы за функциональным интерфейсом. Эта концепция подробно рассматривается в разделе «Выбор между явным и неявным курсорами» вот этого блога.

Обработка ошибок при использовании неявных курсоров

Команда SELECT, выполняемая как неявный курсор, представляет собой «черный ящик». Она передается в базу данных и возвращает одну строку информации. Что происходит с курсором, как он открывается, получает данные и закрывается, вы не знаете. Также приходится смириться с тем, что в двух стандартных ситуациях при выполнении команды SELECT автоматически инициируются исключения:

По запросу не найдено ни одной строки. В этом случае Oracle инициирует исключение NO_DATA_FOUND.
Команда SELECT вернула несколько строк. В этом случае Oracle инициирует исключение TOO_MANY_ROWS.

В каждом из этих случаев (как и при возникновении любого другого исключения, инициированного при выполнении команды SQL) выполнение текущего блока прерывается, и управление передается в раздел исключений. Этим процессом вы не управляете; у вас даже нет возможности сообщить Oracle, что данный запрос может не вернуть ни одной строки, и это не является ошибкой. Таким образом, если вы используете неявный курсор, в раздел исключений обязательно нужно включить код перехвата и обработки этих двух исключений (а возможно, и других исключений, как того требует логика программы). В следующем блоке кода производится поиск названия книги по ее коду ISBN с обработкой возможных исключений:

DECLARE
   l_isbn books.isbn%TYPE := '0-596-00121-5'; 
   l_title books.title%TYPE;
BEGIN
   SELECT title 
      INTO l_title 
      FROM books
     WHERE isbn = l_isbn;
EXCEPTION
   WHEN NO_DATA_FOUND 
   THEN
      DBMS_OUTPUT.PUT_LINE ('Неизвестная книга: ' || l_isbn);
   WHEN TOO_MANY_ROWS 
   THEN
      /* Пакет определяется в errpkg.pkg */
      errpkg.record_and_stop ('Нарушение целостности данных для: ' || l_isbn);
      RAISE;
END;

При работе с неявным курсором программист может сделать некоторые рискованные предположения об извлекаемых данных. Приведем пару примеров:

В таблице не может быть более одной книги с указанным кодом ISBN — ведь это гарантируется заданным для таблицы ограничением.
В таблице обязательно имеется строка с данными о содержащейся в ней книге, поэтому не стоит беспокоиться об исключении NO_DATA_FOUND.

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

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

Как правило, при использовании неявных курсоров желательно всегда включать в программу обработчики исключений NO_DATA_FOUND и TOO_MANY_ROWS. В обобщенной формулировке можно сказать, что в программе должны присутствовать обработчики всех исключений, которые в ней могут произойти. А вот действия, выполняемые этими обработчиками, могут быть самыми разными. Возьмем все тот же код, извлекающий название книги по заданному коду ISBN. Ниже он реализован в виде функции с двумя обработчиками ошибок. Обработчик NO_DATA_FOUND возвращает значение, а обработчик TOO_MANY_ROWS записывает ошибку в журнал и повторно инициирует исключение, прерывая работу функции. (Скачать errpkg.pkg можно здесь).

FUNCTION book_title (isbn_in IN books.isbn%TYPE )
   RETURN books.title%TYPE 
IS
   return_value book.title%TYPE;
BEGIN
   SELECT title
      INTO return_value 
      FROM books
      WHERE isbn = isbn_in;
      RETURN return_value;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
   WHEN TOO_MANY_ROWS 
   THEN
      errpkg.record_and_stop ('Нарушение целостности данных для: '
      || isbn_in);
   RAISE;
END;

Почему эти два обработчика ведут себя по-разному? Дело в том, что функция должна вернуть название книги, которое никогда не может быть представлено значением NULL. Для проверки используется код ISBN («существует ли книга с данным кодом ISBN?»), поэтому если книга по ISBN не найдена, функция не должна инициировать исключение. В этом нет ничего плохого. Например, логика программы может быть такой: «если книги с заданным ISBN не существует, используем его для новой книги», а возможная реализация может выглядеть так:

IF book_title ('0-596-00121-7') IS NULL 
THEN ...

Иначе говоря, то, что запрос не вернул ни одной строки, не всегда свидетельствует об ошибке.

С другой стороны, если запрос сгенерировал исключение TOO_MANY_ROWS, мы сталкиваемся с настоящей проблемой: в базе не может быть двух книг с одинаковыми кодами ISBN. Поэтому в данном случае информация об ошибке записывается в журнал, а программа прекращает свою работу.

Атрибуты неявных курсоров

Для получения информации о последнем запросе, выполненном с помощью неявного курсора, Oracle предоставляет программисту четыре атрибута, перечисленных в табл. 1. Поскольку неявный курсор не имеет имени, оно заменяется ключевым словом SQL.

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

Все атрибуты неявных курсоров возвращают NULL, если в сеансе еще не выполнялся ни один неявный курсор. В противном случае значения атрибутов всегда относятся к последней выполненной команде SQL независимо от того, в каком блоке или программе она выполнялась. За дополнительной информацией об этом поведении обращайтесь к разделу «Атрибуты курсора для операций DML» этого блога . Для тестирования этих значений также можно воспользоваться сценарием queryjmphcit_attributes.sql на сайте книги. Чтобы этот момент стал более понятным, рассмотрим следующие две программы:

PROCEDURE remove_from_circulation
   (isbn_in in books.isbn%TYPE)
IS
BEGIN
   DELETE FROM book WHERE isbn = isbn_in;
END;
PROCEDURE show_book_count 
IS
   l_count INTEGER;
BEGIN
   SELECT COUNT (*)
   INTO l_count 
   FROM books;
   -- Такой книги нет!
   remove_from_circulation ('0-000-00000-0');

Независимо от количества строк данных в таблице books результат всегда будет равен 0. Поскольку после команды SELECT INTO вызывается процедура remove_from_circulation, атрибут SQL%ROWCOUNT возвращает информацию о результате немыслимой команды DELETE, а вовсе не количество строк, возвращаемых запросом.

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

PROCEDURE show_book_count 
IS
   l_count INTEGER; 
   l_numfound PLS_INTEGER;
BEGIN
   SELECT COUNT (*)
   INTO l_count
   FROM books;

   -- Копирование значения атрибута
   l_numfound := SQL%ROWCOUNT;

   -- Такой книги нет!
   remove_from_circulation ('0-000-00000-0');

   -- Теперь можно вернуться к предыдущему значению атрибута.
   DBMS_OUTPUT.put_line (l_numfound);
END;