Создание и использование хранимых процедур

Хранимые процедуры - это процессы, выполнение которых происходит непосредственно на сервере баз данных. Они представляют собой программы SQL, скомпилированные при первом выполнении и затем сохраненные для дальнейшего применения. Хранимые процедуры обладают определенными преимуществами перед программами, выполняе­мыми немедленно и не предполагающими последующего применения:

1. Процедуры, которые были скомпилированы ранее, выполняются более быстро.

2. Они могут получать и возвращать параметры - переменные, исполь­зуемые для передачи данных хранимым процедурам и от них - что позволяет создавать модули-утилиты или иным способом разбивать на модули большие и сложные программы.

 

Параметр. Переменная, используемая для передачи данных хранимой процедуре и получения данных от нее.

Рассмотрим пример. Предположим, что нам часто приходится считать число работников в таблице worker. После того, как мы определим локальную переменную @wo_count (число работников), мы можем написать запрос:

select @wo_count = count (*) from worker

Или вместо этого мы можем создать процедуру:

create procedure count_workers @wo_count int output

as

select @wo_count = count (*) from worker

Первая строка этого выражения

create procedure count_workers @wo_count int output

присваивает процедуре имя, «count_workers», и сразу после этого опре­деляет все параметры, задавая их типы данных и указывая, чем они явля­ются - входными или выходными параметрами. В нашем примере @wo_count - выходной параметр типа int (целое число). Теперь посмотрим, как эта процедура будет работать.

Когда процедура вызывается, то выполняется содержащийся в ней запрос

select @wo_count = count (*) from worker,

результат помещается в выходную переменную @wo_count и возвраща­ется вызывающей программе. Выходные параметры задаются словом «output» или «out» после типа данных. Входными параметрами будут все параметры, не помеченные как выходные. Ключевое слово «as» сигнализи­рует об окончании определений параметров и начале определения проце­дуры. Все, что стоит после «as», составляет выполняющуюся часть процедуры.

Для создания хранимой процедуры можно воспользоваться средствами утилиты SQL Server Enterprise Manager.

Все хранимые процедуры в базе данных находятся в специ­ально отведенном списке Stored Procedures (рис. 1). Следует обратить внимание на перечень системных процедур, используе­мых при работе SQL-сервера, список которых также находится в этой группе.

 

Рис.1. Окно Enterprise Manager для выбора списка хранимых процедур

В окне для работы с хранимыми процедурами в колонке Type возле имени процедур находится ключевое слово System, которое показывает принадлежность данной процедуры к группе системных процедур. С другой сторо­ны, все процедуры, создаваемые пользователем, помечаются ключевым словом User в колонке Type.

Для создания новой процедуры выберите команду New Stored Procedures меню Action, после чего на экране отобразится диало­говое окно, в котором будет расположена область для ввода тек­ста процедуры (рис. 2).

Для решения выше рассмотренной задачи создадим новую хранимую про­цедуру, после чего системой будет предложена заготовка SQL-команда, используемая для создания процедуры:

 

CREATE PROCEDURE [PROCEDURE NAME] AS.

Здесь вместо текста [PROCEDURE NAME] необходимо ввести имя создаваемой процедуры, после чего набрать текст ее команд.

 

 

Рис. 2. Диалоговое окно редактора хранимых процедур

 

Следующим этапом будет проверка работоспособности соз­данной процедуры. Для этого запустите утилиту SQL Server Query Analyzer, после чего осуществите подключение к требуемому серверу баз данных. Выберите базу данных Premier1 в выпа­дающем списке DB.

Далее необходимо набрать и выполнить ряд команд (рис. 3).

 

Рис. 3. Диалоговое окно Query Analyzer c командами запуска хранимой процедуры Count_workers и результатом ее выполнения

В процедуре сначала определяется локальная переменная @worker_count. Затем вызывается ранее определенная хранимая процедура count_workers. Результат выполнения будет помещен в локальную перемен­ную @worker_count. С помощью оператора Print значение этой переменной выводится на экран.

Разумеется, преимущество применения хранимых процедур существенно больше, когда сама процедура обширнее и сложнее.

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

Мы создадим процедуру calc_wage_fcns (рис. 4).

Рис. 4. Диалоговое окно редактора хранимых процедур с текстом процедуры Calc_wage_fcns

У этой хранимой процедуры есть выходной параметр @avg_hrly_rate (средняя почасовая ставка) и входной - @skill_type. Вызывающая про­грамма должна задавать локальную переменную типа real для фиксации выходного параметра, и значение типа специальности. Более того, в ней эти параметры должны быть указаны в том же порядке, в котором они перечислены при определении процедуры. Приведем пример вызова процедуры Сalc_wage_fcns (рис. 5).

Рис. 5. Диалоговое окно Query Analyzer c командами запуска хранимой процедуры Calc_wage_fcns и результатом ее выполнения

 

Обратите внимание, что команду «execute» можно сократить до «ехес». Хотя значение входного параметра (в нашем случае «Штукатур») является символьной величиной, его не нужно заключать в кавычки, за исключением тех случаев, когда оно дополнено пробелами, содержит знаки препинания или начинается с цифры. Процедура calc_wage_fcns подставит значение «Штукатур» в переменную @skill_type, и будет подсчитана средняя почасовая ставка штукатура. Когда значе­ние будет возвращено вызывающей программе, оно будет помещено в пере­менную @avg_wage.

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

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

Измененная процедура представлена на рис. 6.

 

Рис. 6. Диалоговое окно редактора хранимых процедур с текстом измененной процедуры Calc_wage_fcns

 

Если вы сравните эту версию с предыдущей версией, то увидите, что значение по умолчанию определяется сразу после задания типа данных параметра:

@skill_type char (10) = null.

Помещая «null» после определения параметра, мы говорим, что если никакое значение параметру не передано, то параметр считается имеющим пустое значение. Выполняемая часть процедуры изменена, так, чтобы учиты­вать такую возможность - тип специальности не передан вызывающей программой.

Вызов и результат выполнения процедуры представлен на рис. 7.

Рис. 7. Диалоговое окно Query Analyzer c командами запуска измененного варианта хранимой процедуры Calc_wage_fcns и результатом ее выполнения

Применение команды RETURN. Когда последняя команда процедуры выполнена, процедура завершается и возвращает управление вызывающей процедуре. Как быть, если логика процедуры такова, что мы хотим выйти из нее раньше? Команда RETURN обрывает выполнение хранимой процедуры и немедленно возвращает управление вызывающей программе. Предположим, что мы хотим придать одной хранимой процедуре несколько разных функций. Например, мы хотим позволить пользователю запрашивать максимальную, минимальную или среднюю почасовую ставку из таблицы worker. Процедура, которая это делает, представлена на рис. 8.

Рис. 8. Диалоговое окно редактора хранимых процедур с текстом процедуры Calc_wage_fcns1

В этом примере вызывающей программе требуется одна из трех функ­ций. Если выбрана функция «max», то мы вычисляем максимальное значение и немед­ленно возвращаемся в исходную программу, так как не хотим вычислять остальные две функции. Результат выполнения хранимой процедуры представлен на рис. 9.

Из этого примера нетрудно понять, как команда RETURN может применяться в хранимых процедурах.

 

Рис. 9. Диалоговое окно Query Analyzer c командами запуска хранимой процедуры Calc_wage_fcns1 и результатом ее выполнения

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

Табл. 1