Обработка результатов запросов. Подпрограммы в Transact-SQL (Лабораторная работа № 4)

Лабораторная работа №4

Обработка результатов запросов. Подпрограммы в Transact-SQL

1. Цель работы

1.  Научиться создавать хранимые SQL-процедуры и пользовательские функции в БД.

2.  Научиться обрабатывать результаты запросов в SQL-подпрограммах с использованием курсоров.

2. Основные сведения из теории

2.1. Подпрограммы в Transact-SQL

Основными клиентами баз данных являются прикладные программы, входящие в состав АИС. В них реализуются сложные процедуры и функции обработки данных, требующие использования целых серий идущих друг за другом операций вставки, удаления, обновления, выборки. Нередко при этом используются такие алгоритмические структуры, как циклы и ветвления.

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

·  изменение логики обработки данных ведет к необходимости перекодирования всех клиентских приложений, использующих эту логику, их последующей перекомпиляции и повторного распространения среди пользователей;

·  постоянная пересылка команд и результатов между сервером и клиентом генерирует большой сетевой трафик;

·  логика обработки данных «оторвана» от самих данных, что не соответствует современной парадигме разработки программных систем – объектно-ориентированному и компонентно-ориентированному подходу.

Перечисленные проблемы легко решаются на современном этапе, благодаря технологии «активный сервер». Смысл ее заключается в том, что функции АИС, выполняющие обработку данных в БД, реализуются не в клиентских приложениях, а на стороне сервера СУБД в виде подпрограмм БД. Коды этих подпрограмм располагаются вместе с данными, внутри БД, и являются, таким образом, объектами БД. Преимущества такого подхода заключаются в следующем:

·  программа-клиент передает на сервер только один запрос – на вызов хранимой подпрограммы – и получает, соответственно, один результат; отпадает необходимость передачи промежуточных запросов и промежуточных результатов; снижается сетевой трафик;

·  всем клиентским программам дается централизованный доступ к подпрограммам обработки данных; модификация логики обработки данных не приводит к необходимости перекомпиляции приложений;

·  БД становится полноценным отражением предметной области, т. к. в ней не только хранятся сведения об объектах, но и моделируются различные способы взаимодействия между объектами.

Подпрограммы БД могут быть реализованы на любом языке высокого уровня. Базовой возможностью СУБД является создание SQL-подпрограмм, а внедрение в БД подпрограмм на других языках высокого уровня – это дополнительная возможность, сопряженная с дополнительными трудностями.

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

2.1.1. Создание хранимой процедуры

Создание хранимой процедуры выполняется при помощи оператора createprocedure:

createproc[edure] имя_процедуры

           [{@параметр тип_параметра [varying] [= значение] [output]}

           [,…n]]

           as тело_процедуры

Имя процедуры начинается с латинской буквы, может содержать цифры и знак подчеркивания. Дополнительно имени процедуры может предшествовать имя схемы, отделенное точкой. Допускается создавать процедуры только в текущей БД.

Хранимая процедура может принимать параметры – локальные переменные, которым передаются значения извне процедуры, во время ее вызова. Имени параметра предшествует символ @. Параметр имеет тип, для него можно задать значение по умолчанию, для чего используется необязательная секция [= значение]. Каждый параметр, по определению, является входным, но дополнительно может быть и выходным, т. е. передавать значение вовне процедуры. Объявление выходного параметра завершается указанием ключевого слова output.

Ключевое слово varying используется при объявлении курсорной переменной, как выходного параметра. Курсоры и курсорные переменные рассматриваются в п. 2.2. Тело хранимой процедуры – это последовательность вызовов SQL-операторов, причем последние не только следуют друг за другом, но также образуют циклы, ветвления.

2.1.2. Вызов хранимой процедуры

Чтобы вызвать хранимую процедуру на выполнение, нужно запустить команду execute с указанием имени процедуры и всех ее аргументов:

exec[ute] имя_процедуры [{{значение | @переменная} [output][defalt]}[,…n]]

Значения должны перечисляться согласно очередности задания параметров в вызове createprocedure. Ключевое слово output в вызове может использоваться только с аргументами, которые соответствуют выходным параметрам. Аналогично слово default указывает на то, что данный параметр при вызове принимает заранее установленное значение по умолчанию.