Использование команд SQL в хранимом коде

Команды INSERT, DELETE и UPDATE используются в программе на PL/SQL в качестве отдельных операторов наряду с другими операторами языка. В данных командах разрешено использовать переменные программы везде, где по правилам SQL используются константы, что делает данные команды более гибкими, чем при их использовании в интерактивном режиме. Для обработки исключительных ситуаций, которые могут возникнуть в случае, когда какая-либо из этих команд нарушает целостность данных, существует большое количество стандартных предопределенных имен. Например, приведенная в предыдущем примере ситуация DUPLICATE_KEYS возникает при нарушении ограничения уникальности (и в первичном ключе в том числе).

Проблема возникает при встраивании в процедурный язык оператора SELECT. Результатом оператора SELECT является множество строк, а процедурный язык ориентирован в основном на обработку последовательностей. Для преодоления этого противоречия в стандарт SQL введен механизм курсора, который реализован и в PL/SQL Oracle. Курсор представляет собой результат выборки из базы данных, который предназначен для дальнейшей построчной обработки.

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

SELECT список_выражений INTO список_переменных ...

остальная часть оператора SELECT

Количество переменных в списке и их типы должны в точности соответствовать списку выражений оператора SELECT.

SELECT mark INTO m FROM marks WHERE cod_st=c_st AND cod_sub=c_s

Значения переменных c_st и c_s задаются заранее. Если существуют студент и предмет с такими значениями кодов, запрос вернет ровно одно значение оценки и разместит его в переменной с именем m.

При выполнении команды SELECT ... INTO … в различных случаях ее применения могут возникнуть две разные исключительные ситуации:

· TOO_MANY_ROWS возникает в том случае, если запрос SELECT вместо одной строки возвращает несколько строк (в этом случае возвращаемые данные невозможно разместить в заданном списке переменных)

· NO_DATA_FOUND возникает в том случае, если запрос SELECT вообще не возвращает данных. Тогда переменные в списке не могут получить никаких значений.

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

Явный курсор является более универсальным средством обработки произвольной выборки из базы данных. Он должен быть явно объявлен в разделе DECLARE. В объявлении курсора определяется его имя и запрос, на котором он основан.

DECLARE CURSOR имя_курсора IS SELECT ...далее идет запрос на выборку

Например:

Следует отметить, что приведенное выше объявление курсора, принятое в Oracle, не совсем соответствует стандарту. Согласно стандарту объявление курсора выглядит так:

имя_курсора CURSOR FOR SELECT ….

Все остальные операции с курсором соответствуют стандарту.

Объявление курсора не является выполнимым оператором. Выборка, заданная в объявлении курсора, выполняется только при его открытии.

Например:

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

Каждое следующее выполнение FETCH выбирает значение столбцов из следующей строки выборки в переменные заданного списка. Оператор FETCH, как правило, применяется в цикле. Например:

END LOOP;

или

FETCH cur INTO fio;

FETCH cur INTO fio;

END LOOP;

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