Лекция № 27

Тема: «Работа с хранимыми процедурами в SQL Server»

План

1. Введение

2. Основные элементы языка SQL

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

4. Использование функций SQL

5. Изменение и удаления хранимых процедур

6. Использование хранимых процедур в проектах Delphi

1. Введение

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

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

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

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

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

Все сказанное выше демонстрирует недостатки подхода к разработки систем, когда логика обработки данных реализуется на стороне клиента. Описанные проблемы могут быть решены за счет переноса алгоритмов обработки данных на сервер. В этом случае приложение просто сообщает сервер, какой именно набор команд необходимо выполнить. Дополнительно могут быть указаны параметры, которые в зависимости от реализации алгоритма будут влиять на ход выполнения процесса обработки данных. При этом приложение сможет получать только конечный результат выполнения. Все промежуточные результаты будут обработаны сервером. Это позволяет снизить сетевой трафик. Набор команд, который  хранится на сервере и выполняется как одно целое, в терминологии SQL Server называется хранимой процедурой (stored procedure).

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

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

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

- простота внесения изменений. Если нужно поменять логику работы процедуры, то достаточно внести изменения в код процедуры на сервере. При этом клиентские приложения в большинстве случаев перерабатывать не нужно;

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

Однако хранимые процедуры имеют и ряд недостатков:

- нужны хорошие знания языка SQL для создания процедур;

- высокие требования к мощности сервера при большом числе подключений.

Несмотря на недостатки, технология хранимых процедур считается одной из наиболее эффективных инструментов разработки клиент/серверным приложений.

2. Основные элементы языки SQL

Код хранимых процедур создается на основе команд языка SQL. Рассмотрим основные особенности данного языка.

Работа со переменными

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

declare @имя_переменной1 тип, @имя_переменной2 тип, …

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

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

С помощью одной команды DECLARE можно объявить несколько переменных, перечислив их через запятую:

declare @a nvarchar(20), @b deciamal(5,2), @c smalldatetime

Если нужно описать несколько переменных одного типа, их все равно описывают отдельно.

declare @d int, @e int

Для присвоения значения перемееной можно использовать разные способы. Простейший из них – применение команды SET. Однако с помощью этой команды можно присвоить значение только одной переменной.

set @а='Федоров'

set @b=1.5

set @c='12.05.1969'

Значение переменным также можно присваивать с помощью команды SELECT, что позволяет одновременно работать с несколькими переменными:

select @а='Федоров', @b=1.5, @c='12.05.1969'

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

declare @sredoklad decimal (5,2), @maxoklad decimal (5,2)

select @sredoklad=avg(oklad), @maxoklad=max(oklad) from sotrudniki

Работа с временными таблицами

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

 Доступ к глобальной временной таблице (Global Temporary Table) может быть полученн из любого соединения, независимо от того, в каком именно из них она была создана. Это обеспечивает удобный интерфейс для обмена данными между разными копиями хранимых процедур. Одна и та же хранимая процедура, запускаемая разными пользователями, может обращаться к глобальной временной таблице для получения информации. Кроме того, глобальные временные таблицы могут быть использованы для обмена данными между разными приложениями.

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

 Для того чтобы создать глобальную временную таблицу, достаточно в начале ее имени при создании указать символы "##". Когда сервер встречает эти символы, он обращается к базе данных TempDB для поиска указанной таблицы. Работа с временными таблицами ничем не отличается от работы с обычными таблицами. Глобальная временная таблица существует до тех пор, пока пользователь явным образом не уничтожит ее с помощью команды DROP TABLE или пока не будет закрытое соединение, в котором она создавалась. Кроме того, как глобальные, так и локальные временные таблицы также уничтожаются при остановке или перезапуске сервера.

Локальные временные таблицы (Local Temporary Table) видимы только из того соединения, в котором они были созданы. При закрытии соединения такая таблица автоматически уничтожается. При следующем открытии соединения она должна быть создана занова. Кроме того, если локальная временная таблица была создана в хранимой процедуре, то после выхода из этой процедуры такая таблица также автоматически уничтожается. В разных соединениях могут создаваться временные локальные таблицы с одинаковыми именами.

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

Для создания локальной временной таблицы необходимо перед именем таблицы при ее создании указать один символ "#". В дальнейшем для ссылки на созданную таблицу также необходимо в ее имени указывать символ "#".

Допускается создание временной таблицы с помощью конструкции into команды select.

Пример. Пусть на предприятии планируется повысить оклады на 20%. Нужно оценить, сколько людей будет получать более 1500 грн. При этом сами данные в исходной таблице sotrudniki менять не нужно.

Для решения этой задачи сначала отберем данные во временную таблицу, а потом в этой таблице обновим оклады и найдем количество сотрудников.

--копируем все записи в локальную временную таблицу #bufer

select * into #bufer from sotrudniki

-- во временной таблице обновляем оклады

update #bufer set oklad=oklad+oklad*0.2

--отбираем количество сотрудников

select count(*) as kol from #bufer where oklad>1500

Управляющие конструкции и команды

begin...end

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

Допускается создание вложенных конструкций begin...end. SQL Server практически не ограничивает глубину вложенности конструкций. Однако на практике вложенность обычно не превышает 5-7 уровней.

if...else

С помощью этой конструкции в SQL реализуется ветвление алгоритмов. Типичным примером такого ветвления является выполнения набора команд в зависимости от соблюдения некоторой условия.

Синтаксис конструкции IF...ELSE следующий:

if условие

    действие

[else

    действие]

Если нужно выполнить блок команд, то необходимо воспользоваться конструкцией begin...end, как это было описано раньше.

Если нужно проверить несколько условий, то конструкция записывается так:

if условие

    действие

[else if условие

    действие

[else действие]]

while...

С помощью этой команды SQL организует цикл. Сразу отметим, что в SQL Server есть всего один тип цикла – с предусловием, который и реализуется с помощью данной команды.

Синтаксис команды while:

while условие

действие

[break]

 С помощью параметра "условие" указывается логическое условие. Цикл будет выполняться до тех пор, пока условие возвращает значение true. Предполагается, что в условии могут использоваться функции или переменные, значения которых меняются в ходе выполнения цикла. Выход из такого цикла можно осуществляться принудительно с помощью команды break, которая должна выполниться в теле цикла.

Если внутри цикла while нужно выполнить несколько команд, то потребуется использовать конструкцию begin...end.

case...when...then

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

Функция имеет вид:

case when условие1 then действие

     when условие2 then действие

     . . .

     when условиеN then действие

     [else действие]

end

use

Все команды выполняются в контексте некоторой базы данных. Такая база данных называется текущей. При обращении к объектам текущей базы данных не нужно указание ее имени. Например, если текущей является база данных pubs, то обращение к таблице authors этой базы данных можно осуществить таким способом:

select * from authors

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

use имя_БД

go

Клиент отправляет серверу команды на выполнение так называемыми пакетами (batch). После выполнения всех команд пакета (или в ходе выполнения пакета) сервер возвращает клиенту результат. После этого клиент может отправить следующий пакет. В качестве пакета понимается набор команд, которые пользователь запускает на выполнение. Однако для выполнения некоторых команд требуется определенное время (например команды create, с помощью которых выполняется создания объектов базы данных, команды bаскup и restore, предназначенные, соответственно, для выполнения создания и восстановления резервных копий баз данных, а также некоторые другие команды). Пока данные команды не закончили свою работу, выполнять следующие команды нельзя. По умолчанию сервер не ожидает завершение выполнения таких команд, что приводит к ошибкам работы кода.

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

Например, набор команд:

use pubs

create table primer . . .

select * from primer

выдаст ошибку, потому что команда select не будет ожидать окончания создания таблицы на диске.

Для решения проблемы данный код нужно записать так:

use pubs

create table primer . . .

go

select * from primer

Все команды, связанные с изменением данных (create, alter, delete, insert, update и т.п.), должны всегда делиться на пакеты.

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

Все хранимые процедуры SQL Server находятся в папке "программирование – Хранимые процедуры" внутри узла БД.

Для создания новой хранимой процедуры вызовите контекстное меню папки "Хранимые процедуры" и выберите команду "Создать хранимую процедуру".

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

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

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

create procedure имя_ процедуры

[параметр1 тип [output],

 параметр2 тип [output],

 . . .

 параметрN тип [output]]

as

begin

    код процедуры

end

Рассмотрим назначение и использование параметров команды:

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

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

as. После этого ключевого слова начинается тело процедуры, состоящее из набора команд SQL.

begin … end. Задает начало и конец тела хранимой процедуры. Если процедура состоит из, однйть команды, то указывать эту конструкцию не обязательно.

Для вызова процедуры используют команду вида:

exec имя_ процедуры список_параметров

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

Процедура без параметров

Пример. Создать процедуру, которая увеличивает оклады сотрудников на 15%.

create procedure oklad

as

    update sotrudniki set oklad=oklad+oklad*0.15

Вызовем процедуру для увеличения оклада.

exec oklad

Процедура с входным параметром

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

create procedure oklad

    --опишем параметр для задания нужного процента

    @procent int

as

    update sotrudniki

    set oklad=oklad*oklad*@procent/100

Вызовем процедуру для увеличения оклада на 10%

exec oklad 10

Вызовем процедуру для увеличения оклада на 7%

exec oklad 7

Процедура на выборку данных

Пример. Написать процедуру для вывода списка сотрудников заданного пола.

create procedure spisok

    --опишем параметр для задания пола

    @pol varchar(1)

as

    select * from sotrudniki where pol=@pol order by fam

Вызовем процедуру для вывода списка женщин.

exec spisok 'ж'

Пример. Написать процедуру, которая выдает на экран количество, средний и максимальный оклад сотрудников

create procedure schet_oklad

as

    select count(*) as kol, avg(oklad) as sred, max(oklad) as maximum from sotrudniki

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

exec schet_oklad

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

create procedure schet_oklad_pol

    --опишем параметр для задания пола

    @pol varchar(1)

as

    select count(*) as kol, avg(oklad) as sred, max(oklad) as maximum from sotrudniki where pol=@pol

Вызовем процедуру для вывода расчетных значений для мужчин

exec schet_oklad_pol 'м'

Процедура с выходными параметрами

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

create procedure schet_oklad2

--опишем выходные параметры

@kol int output,

@sred decimal(7,2) output,

@maximum decimal(7,2) output

as

    select  @kol=count(*),

@sred=avg(oklad),

@maximum=max(oklad) from sotrudniki

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

--опишем переменные для передачи как параметров в процедуру

@declare @k int, @s decimal(7,2), @m decimal(7,2)

--вызываем процедуру с предачей в нее переменных для записи ответов

exec schet_oklad2 @k output, @s output, @m output

--выводим переменные на экран

select @k, @s, @m

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

create procedure schet_oklad_pol2

    --опишем параметр для задания пола

    @pol varchar(1)

--опишем выходные параметры

@kol int output,

@sred decimal(7,2) output,

@maximum decimal(7,2) output

as

    select  @kol=count(*),

@sred=avg(oklad),

@maximum=max(oklad) from sotrudniki

where pol=@pol

Вызовем процедуру для вывода расчетных значений для мужчин

--опишем переменные для передачи как параметров в процедуру

@declare @k int, @s decimal(7,2), @m decimal(7,2)

--вызываем процедуру с предачей в нее переменных для записи ответов

exec schet_oklad_pol2 'м', @k output, @s output, @m output

--выводим переменные на экран

select @k, @s, @m

Рассмотренные команды создания процедур вводят как обычный запрос в окне утилиты "Среда SQL Server Management Studio". После выполнения данной команды созданная процедура появится как элемент БД в папке "Программирование – Хранимые процедуры"

4. Использование функций SQL

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

Функции для работы с датой и временем

Нередко возникает необходимость выделить из даты день, месяца или год, добавить или отнять несколько дней и т.д.

Пример. В таблице сотрудники вывести ФИО всех, кто родился в октябре (10 месяц)

select fam, imya, otch from sotrudniki

where month(datar)=10

В таблице сотрудники вывести ФИО всех, кто родился в 1970 году

select fam, imya, otch from sotrudniki

where year(datar)=1970

В таблице сотрудники вывести ФИО все, кто родился в 70-х годах

select fam, imya, otch from sotrudniki

where year(datar) between 1970 and 1979

В таблице сотрудники вывести ФИО всех, кто родился сегодня

select fam, imya, otch from sotrudniki

where dayr(datar)=day(getdate()) and

month(datar)=month(getdate())

Математические функции

 Функции этой группы предназначены для выполнения разных математических и алгебраических операций.

Строковые функции

 Функции этой группы предназначены для работы с символьными строками.

Пример. В таблице Sotrudniki все фамилии записать с прописной буквы, удалив начальные пробелы

--запишем все фамилии маленькими буквами и удалим конечные и начальные пробелы

update sotrudniki set fam=ltrim(rtrim(lower(fam)))

--заменим первый символ в каждой фамилии на заглавный

update sotrudniki

set fam=replace(fam,left(fam,1),upper(left(fam,1)))

Пример. В таблице sotrudniki вывести на экран ФИО всех, у кого длина фамилии равна 6 символам.

select fam, imya, otch from sotrudniki where len(fam)=6

5. Изменение и удаления хранимых процедур

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

Для внесения изменения в дереве объектов БД раскройте папку "Программирование – Хранимые процедуры". В контекстном меню нужной процедуры выберите команду "Изменить". Внесите изменения в код процедуры и щелкните на кнопке "Выполнить".

Для удаления процедуры в ее контекстном меню выберите команду "Удалить".

6. Использование хранимых процедур в проектах Delphi

 Для работы с хранимыми процедурами существует специальный компонент ADOStoreProc (ADO). Данный компонент имеет основные свойства:

- Connection – ссылка на компонент ADOConnection для подключения к БД;

- ProcedureName – имя хранимой процедуры;

- Parameters – список параметров хранимой процедуры. Если процедура имеет входные параметры, то для них обязательно надо задать тип данных. Для этого раскройте свойство Parameters, выберите нужный параметр и в свойстве Value - Type задайте нужный тип. Если этого не сделать, то программа выдает сообщение о том, что указанный параметр не найден.

Процедура на выборку данных без параметров

Если сохраненная процедура выполняет выборку данных и не имеет входных параметров, то компонент ADOStoredProc связывают с нужными компонентами на форме с помощью задания свойств:

ADOStoredProc => DataSource => визуальный компонент

И используют код:

ADOStoredProc1.Active:=true;

После этого кода компонент ADOStoredProc1 будет содержать набор данных, с которым можно работать также как с обычной таблицей: использовать методы редактирования и навигации (Append, Delete, Post, Cancel и т.п.)

Пример. Пусть на сервере имеется процедура "proc1", которая отбирает список всех сотрудников с сортировкой по полю fam:

create procedure proc1

as

    select * from sotrudniki where pol=@pol order by fam

Необходимо вызвать данную процедуру и отобразить данные в сетке DBGrid.

Нанесем на форму компонент ADOStoredProc (ADO). В свойстве Connection укажем ссылку на компонент ADOConnection1, в свойстве ProcedureName укажем "proc1".

Нанесем на форму компонент DataSource (Data Access) и в свойстве DataSet укажем ADOStoredProc1.

Нанесем на форму компонент DBGrid (Data Controls) и в свойстве DataSource укажем DataSource1.

В событии OnCreate формы введем код:

ADOStoreProc1.Active:=true;

Процедура на выборку данных с параметрами

Если процедура имеет входной параметр, то перед ее вызовом нужно задавать тип параметра с помощью свойства параметра Value - Type и его значение, для чего  компонент ADOStoredProc имеет специальное свойство:

ADOStoreProc1.Parameters.ParamByName('имя_параметра').Value

Пример. Изменим код процедуры "proc1" так, чтобы она отбирала список сотрудников заданного пола.

create procedure proc1

    --входной параметр для задания пола

    @pol nvarchar(1)

as

    select * from sotrudniki where pol=@pol order by fam

    where pol=@pol

Пусть на форме имеется выпадающий список Combobox1, в котором можно выбрать букву нужного пола. По умолчанию в списке отображается первое значение. Для этого для списка задано свойство Itemindex=0 (по умолчанию отображается первая строка).

Для указания типа входного параметра @pol выделите компонент ADOStoreProc1, раскройте его свойство Parameters, выделите параметр @pol и задайте свойство Value - Type = OleStr.

В событии OnCreate формы напишем код:

//выключаем процедуру для задания значения параметров

ADOStoreProc1.Active:=false;

//задаем значение входного параметра

ADOStoredProc1.ADOStoreProc1.Parameters.ParamByName('@pol').Value:=ComboBox1.Text;

//активируем процедуру для отбора данных

ADOStoreProc1.Active:=true;

Если пользователь выбрал в списке другой пол, то данные должны обновиться. Для этого в событии OnCloseUp компонента ComboBox1 выберите событие form1.OnCreate

Процедура на изменение данных без параметров

Если процедура изменяет данные (содержит запрос Update, Delete, Insert), то такой запрос выполняется командой:

ADOStoredProc1.ExecProc;

Пример. Пусть процедура "proc2" в таблице сотрудников увеличивает оклад на 20%.

create procedure proc2

as

    update sotrudniki set oklad=oklad*1.2

Для выполнения такой процедуры нанесем на форму компонент ADOStoredProc (ADO). В свойстве Connection укажем ссылку на компонент ADOConnection1, в свойстве ProcedureName укажем "proc2". В обработчике нужного события (для кнопки, пункта меню и т.п.) введем код:

ADOStoredProc2.ExecSql;

После данной команды данные в сетке DBGrid1, которая связана с хранимой процедурой ADOStoredProc1, автоматически не обновятся. Поэтому после выполнения процедуры на изменение данных нужно ввести команду обновления данных в сетке:

ADOStoredProc1.Requery();

Процедура на изменение данных с параметрами

Если процедура имеет входные параметры, то перед ее выполнением нужно задать этим параметрам типы и нужные значения.

Для задания типа входным параметрам @pol, @god выделите компонент ADOStoreProc1, раскройте его свойство Parameters, выделите параметр @pol и задайте свойство Value - Type = OleStr, выделите параметр @god и задайте свойство Value - Type = Date.

Пример. Пусть процедура "proc3" удаляет сотрудников заданного пола и родившихся к заданному году.

create procedure proc2

    --входной параметр для задания пола

    @pol nvarchar(1)

    --входной параметр для задания года рождения

    @gor int

as

    delete from sotrudniki

    where pol=@pol and year(datar)<@god

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

Пусть пол на форме выбирается из выпадающего списка Combobox1, а год рождения вводится в текстовое поле Edit1.

В обработчике нужного события (для кнопки, пункта меню и т.п.) введем код:

//меняем у компонента имя процедуры

ADOStoredProc2.ProcedureName:='proc3';

//задаем параметр "пол"

ADOStoredProc2.Parameters.ParamByName(@pol).Value:=ComboBox1.Text;

//задаем параметр "год рождения"

ADOStoreProc2.Parameters.ParamByName('@god').Value:=Edit1.Text;

//выполняем процедуру

ADOStoredProc2.ExecProc;

//обновляем данные в сетке (связана с ADOStoredProc1)

ADOStoredProc1.Requery();

Процедура на выборку данных с выходными параметрами

Если процедура имеет выходные параметры, то перед ее выполнением нужно задать этим параметрам типы и нужные значения.

Для задания типа входным параметрам выделите компонент ADOStoreProc, раскройте его свойство Parameters, выделите параметр задайте свойство Value - Type = тип.

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

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

Пусть пол на форме задается с помощью выпадающего списка ComboBox1.

В обработчике нужного события (для кнопки, пункта меню и т.п.) введем код:

//задаем параметр "пол"

ADOStoredProc2.Parameters.ParamByName(@pol).Value:=ComboBox1.Text;

//выполняем процедуру

ADOStoredProc2.ExecProc;

//на форму в текстовых надписях выводим расчетные выходные параметры

Label1.Caption:=vartostr(ADOStoredProc2.Parameters.ParamByName('@kol').Value);

Label2.Caption:=vartostr(ADOStoredProc2.Parameters.ParamByName('@sred').Value);

Label3.Caption:=vartostr(ADOStoredProc2.Parameters.ParamByName('@maximum').Value);

Вопросы для самоконтроля

1. Какие операции над переменными можно выполнить в коде SQL Server? Как в коде SQL Server присвоить переменной значение?

2. Как записать комментарий в коде SQL Server? Как записать оператор if, case, while в коде SQL Server? Как записать операторные скобки? Для чего они нужны?

3. Что такое хранимая процедура? В чем ее преимущества и недостатки? Как создать хранимую процедуру SQL Server без параметров и вызвать ее на выполнение. Пример.

4. Как создать хранимую процедуру SQL Server с входными параметрами и вызвать ее на выполнение. Пример.

5. Опишите процесс создания хранимой процедуры SQL Server для расчета результатов. Как вызвать такую процедуру? Пример.

6. Опишите процесс создания хранимой процедуры SQL Server на обновление или удаление данных Пример.

7. Какой компонент Delphi предназначен для работы с хранимыми процедурами? Приведите все его свойства.

8. Как с помощью хранимой процедуры отобразить данные в сетке DBGrid?

9. Как в проекте Delphi выполнить хранимую процедуру на выборку данных, на обновление данных, на удаление данных?

10. Как в Delphi задать типы и значение входных параметров для хранимой процедуры?