Begin набор операторов end

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

If логическое_условие

набор операторов

ELSE

альтернативный набор операторов

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

WHILE логическое_условие

набор операторов

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

Оператор паузы – waitfor, позволяет организовать задержку исполнения сценария. Внутри данного оператора используются ключевые слова delay – для указания отрезка времени или time – для указания точного времени. Например:

WAITFOR delay ’31:00’ – остановит выполнение сценария на 31 минуту

WAITFOR time ’12:00’ – остановит выполнение команд до 12 часов.

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

Вопросы к допуску

1. Что называется SQL-сценарием?

2. Для решения каких задач применяют сценарии?

3. Как хранятся сценарии в MS SQL Server 2000?

4. Как вызвать и запустить на исполнение сценарий в MS SQL Server 2000?

5. Что называется пакетом? Для чего применяют пакеты?

6. Как разделить сценарий на пакеты?

7. Какие ограничения накладываются на пакеты в MS SQL Server 2000?

8. Какими недостатками обладают сценарии?

Контрольные вопросы

1. Опишите процесс создания сценария в MS SQL Server;

2. Как объявлять и инициализировать переменные в сценариях?

3. Как создавать временные таблицы?

4. Опишите организацию процесса вывода данных из переменных и из временных таблиц;

5. Какие группы операторов можно использовать в выражениях T-SQL?

6. Опишите операторы логического набора операторов T-SQL;

7. Что такое составной оператор? Какие команды применяются для создания составного оператора?

8. Как организовать процесс ветвления средствами T-SQL?

9. Опишите оператор выбора, используемый в T-SQL?

10. Как организуют циклический процесс в T-SQL?

11. Какие команды применяют для досрочного завершения цикла? Приведите пример;

12. С помощью какой команды можно приостановить выполнение сценария?

Лабораторная работа №6: «Разработка хранимых процедур»

Задание:

• Создать несколько хранимых процедур, используя Query Analyzer;

• Продемонстрировать работу с хранимыми процедурами в Query Analyzer – запуск, передача параметров, просмотр кода.

Теоретический материал:

Хранимые процедуры являются усовершенствованным вариантом sql-сценариев. Сценарии позволяют решать разнообразные прикладные задачи, но они обладают рядом следующих недостатков:

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

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

• Хранение сценариев в отдельном файле понижает безопасность базы данных. В MS SQL Server 2000 можно выдавать права и разрешения только на объекты, хранящиеся на сервере. Т.о. сценарием может воспользоваться любой человек. Злоумышленник может изменить сценарий и настроить его на деструктивные действия. Средства безопасности MS SQL Server 2000 в данном случае окажутся неэффективными.

Рассмотренные недостатки сильно понижают пользу от использования сценариев и заставляют задуматься об их усовершенствовании. Хранимые процедуры являются результатом усовершенствования sql-сценариев и лишены описанных недостатков.

Хранимую процедуру можно определить как именованный sql-сценарий, хранящийся на сервере. В хранимые процедуры можно передавать параметры, кроме того, хранимая процедура способна возвращать параметры. Это позволяет разрабатывать гибкие сценарии, состоящие из последовательно выполняемых хранимых процедур. Средства безопасности MS SQL Server 2000 позволяют выдать пользователю или приложению право на исполнение хранимой процедуры.

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

В MS SQL Server 2000 хранимые процедуры разделяются на 5 видов:

• Системные хранимые процедуры – используются для поддержки функциональности MS SQL Server 2000 и хранящихся на нем баз данных. Позволяют организовать выборку данных из системных таблиц, решать различные задачи администрирования сервера, управлять различными его службами. Существует несколько сотен различных системных хранимых процедур. Все они хранятся в системной базе данных Master и имеют в своем названии префикс sp_;

• Локальные хранимые процедуры – хранятся в пользовательской базе данных и используются для решения различных задач в ней. В данной лабораторной работе мы будем создавать именно эту разновидность хранимых процедур;

• Временные хранимые процедуры – хранятся в системной базе данных TempDB и существуют только на период сеанса пользователя;

• Удаленные хранимые процедуры – данный вид хранимых процедур функционирует на удаленной копии MS SQL Server. В настоящее время подобные процедуры не рекомендуется использовать. Они оставлены лишь для совместимости с ранними версиями MS SQL Server.

• Расширенные хранимые процедуры – по своей структуре, данная разновидность резко отличается от всех остальных хранимых процедур. Расширенные хранимые процедуры представляют собой динамически подключаемые библиотеки (dll). Хранимые процедуры этого вида имеют префикс xp_ и предназначены для взаимодействия с различными внешними программами (например для отправки сообщений по электронной почте – xp_sendmail). Создавать расширенные хранимые процедуры можно с помощью любого языка программирования, используя SQL Server Open Data Services API.

В данной лабораторной работе мы будем рассматривать локальные хранимые процедуры. Для создания таких процедур используется команда create proc, имеющая следующий синтаксис:

CREATE PROC имя_процедуры

[список параметров]

[WITH RECOMPILE | ENCRYPTION]

AS

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

GO

Рассмотрим команду более подробно. После ключевых слов create proc необходимо указать имя процедуры. Имена процедур подчиняются стандартным правилам именования объектов в MS SQL Server 2000. Далее можно указать список параметров передаваемых в процедуры и возвращаемых процедурой. Параметры перечисляются через запятую и имеют следующий синтаксис:

имя тип [= значение_по_умолчанию] [OUTPUT]

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

Опции with recompile и with encryption являются необязательными и используются соответственно, если нужно запретить кэширование плана выполнения процедуры (не рекомендуется т.к. это снизит производительность) и если нужно шифровать код хранимой процедуры.

Тело процедуры представляет собой набор команд языка T-SQL. Для завершения команды create proc используется оператор go (т.к. данная команда может быть единственной в пакете).

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

Create proc building_info

@type varchar (9)

as

select bldg_id, address, status

from builling

where bldg_type = @type

go

В данном примере хранимая процедура принимает параметр @type и выводит все сведения о зданиях указанного типа. Для выполнения процедуры можно выполнить команду:

Exec building_info ‘Магазин’

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

Рассмотрим еще один пример:

Create proc worker_info

@id char(5) = null output

@name varchar(30) = null output

@skill varchar (10) output

@rate smallmoney output

AS

If @id is not null

Select @name = wrkr_name, @skill = skill_type, @rate = h_rate

From worker

Where wrkr_id = @id

Else

Select @id = wrkr_id, @skill = skill_type, @rate = h_rate

From worker

Where wrkr_name = @name

Go

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

Declare @id char(5), @name varchar(30),

@skill varchar (10), @rate smallmoney

Set @name = ‘Сидоров И.П.’

Exec worker_info @name, @id output, @skill output,

@rate output

Select [Код работника] =@id ,[Специальность]= @skill, [Оплата]= @rate

Результатом выполнения приведенного кода будет вывод сведений о работнике по имени «Сидоров И.П.».