Системные статистические переменные

НОВОСИБИРСК 2000

СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ХРАНИМЫХ ПРОЦЕДУР

Хранимые процедуры — это процессы, выполнение которых происходит непосредственно на сервере баз данных. Они представляют собой программы 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

Системные статистические переменные

Название Описание
@@CPUBUSY Время загрузки центрального процессора сервера
@@IDLE Время простоя процессора сервера
@@IO BUSY Время выполнения операций ввода/вывода
@@ PACKRECEIVED Число полученных пакетов данных
@@PACKSENT Число отправленных пакетов данных
@@PACKET ERRORS Число ошибок, возникших в процессе отправки и получения пакетов данных
@@TIMETICKS Время работы, системы определяется в единицах таймера, равенство секундам которых можно получить с помощью этой переменной
@@TOTAL ERRORS Число ошибок в операциях чтения и записи
@@TOTAL READ Число операций чтения с диска компьютера
@@TOTAL WRITE Число операций записи на диск компьютера

Табл. 2

Рандомно подобранные статьи с сайта:

Видео урок программирование на Си урок 7: Локальные и Глобальные переменные в языке Си


Похожие статьи:

admin