Файл: Лекция 14 Хранимый код. Триггеры 14 Процедурные расширения языка sql.doc

Лекция 14 Хранимый код. Триггеры

14.1. Процедурные расширения языка SQL

Как мы показали в предыдущих лекциях, язык SQL является очень мощным языком манипулирования данными, однако для решения сложных задач обработки данных ему не хватает управляющих конструкций, имеющихся в универсальных языках программирования. В связи с этим многие СУБД имеют процедурные расширения этого языка, которые представляют собой полноценный язык программирования, поддерживающий возможность использования в нем операторов SQL. На таком языке можно писать процедуры и функции, постоянно хранящиеся в базе данных и исполняемые в среде СУБД.

К сожалению, на настоящий момент ситуация такова, что каждая СУБД поддерживает свой собственный язык процедурного расширения SQL, что усложняет задачи переносимости программного обеспечения. Поэтому те примеры, которые будут приведены в этой лекции, используют процедурный язык PL/SQL, поддерживаемый Oracle, и работают только в среде этой СУБД. Из других процедурных расширений наиболее близок к PL/SQL язык СУБД PostgreSQL, который называется PLG/SQL. Используемое в Microsoft SQL Server процедурное расширение Transact-SQL по синтаксическим конструкциям отличается от PL/SQL, но по семантике является близким. Во всяком случае, понимание логики разработки хранимого кода на PL/SQL поможет легко освоить и любое другое процедурное расширение.

Для дальнейшего изложения вначале необходимо привести минимальные сведения по конструкциям языка PL/SQL. По синтаксису он наиболее близок языку программирования ADA, конструкции его очень логичны. Основной программной единицей является блок – совокупность операторов, заключенная в операторные скобки BEGIN ... END. При выполнении процедурных действий в блоке, как правило, необходимы переменные для хранения промежуточных значений. Объявление переменных предшествует блоку и образует секцию объявления, которая начинается ключевым словом DECLARE. Для переменных поддерживаются те же типы, что и для столбцов таблиц Oracle. Имеется возможность указать тип переменной, явно ссылаясь на определенный столбец или таблицу. Например:

DECLARE

x INTEGER;

fio students.name_st%type;

s subjects%type;

BEGIN

. . .

END

В приведенном примере для переменной x тип указан явно, переменная fio имеет такой же тип, как столбец name_st таблицы students, переменная s имеет тип запись (RECORD), структура которой идентична строке таблицы subjects (содержит два поля cod_sub и name_sub).

---

Операторы языка PL/SQL

1. Оператор присваивания

переменная:= выражение;

2. Условный оператор

IF условие THEN

оператор1; оператор2;

...

[ELSIF условие THEN

оператор3; оператор4;

...]

[ELSE

оператор5; оператор6;

...]

END IF;

3. Операторы цикла

Бесконечный цикл, условие выхода задается в теле цикла

LOOP

оператор1; оператор2;

...

EXIT WHEN условие выхода из цикла;

END LOOP;

Цикл с предусловием

WHILE условие LOOP

оператор1; оператор2;

...

END LOOP;

Цикл с параметром

FOR параметр IN (множество значений) LOOP

оператор1; оператор2; ...

END LOOP;

Множество значений параметра обычно задается в виде диапазона

(начальное_значение .. конечное_значение)

5. Оператор безусловного перехода:

GOTO метка;

...

метка: оператор;
6. Оператор возврата из процедуры/функции

RETURN;

RETURN выражение; (только функции)

7. Комментарии

Однострочный комментарий обозначается так:

-- далее следует текст комментария до конца строки

Многострочный комментарий обозначается аналогично языку С:

/* текст комментария может располагаться где угодно и занимать сколько угодно строк */

8. Средства для обработки исключительных ситуаций

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

Для стандартных исключительных ситуаций существует большое число предустановленных имен. Пользовательская исключительная ситуация вводится объявлением переменной типа EXCEPTION. Генерация исключений из программы выполняется в PL/SQL оператором RAISE.

---

Обработчик исключений в PL/SQL, общий для всех исключений, составляет отдельную часть блока PL/SQL, начинающуюся со слова EXCEPTION и содержащую набор операторов WHEN, распознающих типы исключений и задающих действия, выполняемые по каждому типу (возможны любые действия, которые можно запрограммировать средствами PL/SQL).

Например:

DECLARE

err EXCEPTION;

BEGIN

INSERT INTO … VALUES … ;

SELECT ... INTO …;

IF ... THEN

RAISE err;

...

EXCEPTION

WHEN DUPLICATE_KEYS THEN

...

WHEN TOO_MANY_ROWS THEN

...

WHEN err THEN

...

WHEN OTHERS THEN

...

END;

В приведенном примере имеется два предопределенных имени для стандартных исключительных ситуаций, возникающих в процессе выполнения команд INSERT и SELECT …INTO и предопределенное имя OTHERS, предназначенное для обозначения любой другой исключительной ситуации, которую процедура отдельно не обрабатывает. Переменная err предназначена для возбуждения пользовательской исключительной ситуации при помощи оператора RAISE.

Как видим из приведенного примера, блок PL/SQL может содержать команды языка SQL, которые органично сочетаются с операторами языка высокого уровня. Эта тема заслуживает отдельного обсуждения, поскольку механизм встраивания команд в язык высокого регламентируется стандартом SQL и практически одинаков во всех СУБД.

14.2. Использование команд 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 ...далее идет запрос на выборку

Например:

DECLARE CURSOR cur IS

SELECT name_st FROM students WHERE name_st LIKE ‘A%’

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

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

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

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

Например:

OPEN CURSOR cur

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

---

FETCH cur INTO fio

Переменная fio должна быть предварительно объявлена, например, так:

fio students.name_st%type;

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

LOOP

FETCH cur INTO fio;

...

EXIT WHEN NOT cur%FOUND;

END LOOP;

или

FETCH cur INTO fio;

WHILE cur%FOUND LOOP

FETCH cur INTO fio;

…

END LOOP;

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

CLOSE cur

Цикл по курсору

Некоторые СУБД, в том числе Oracle, поддерживают цикл с параметром по курсору;

FOR параметр IN имя_курсора LOOP

...

END LOOP;

Использование такого цикла не требует операций открытия и закрытия курсора – они выполняются неявно. Параметр цикла не требуется объявлять в секции DECLARE, его тип определяется автоматически как RECORD, а имена полей записи соответствуют именам в объявлении курсора.

Например:

FOR cur_rec IN cur LOOP

... cur_rec.name_st…

END LOOP;

Из этих объяснений понятно, что использование цикла по курсору – очень простой и удобный способ обработки курсора.

Приведенных сведений уже достаточно, чтобы перейти к практическому применению языка PL/SQL. Его основное назначение – разработка хранимых процедур и функций, а также триггеров базы данных.

14.3 Хранимые процедуры и функции

Хранимые процедуры и функции являются стандартными объектами базы данных. Их понимание в SQL не отличается от общепринятого: хранимой подпрограммой (процедурой или функцией) называется именованная, отдельно описанная, повторно используемая программная единица, выполняющая, как правило, определенную прикладную функцию.

Преимущества и недостатки хранимого кода

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

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

---