Операторские скобки

BEGIN

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

*/

END

Условная конструкция if

Синтаксис:

If условие

Набор операторов1

ELSE

Набор операторов2

Пример:

DECLARE @a INT

DECLARE @str CHAR(30)

SET @a = (SELECT COUNT(*) FROM teachers)

IF @a >100

BEGIN

SET @str = 'Количество преподавателей больше 100'

SELECT @str

END

ELSE

BEGIN

SET @str = 'Количество преподавателей = ' + str(@a)

SELECT @str

END

Цикл while

Синтаксис:

WHILE Условие

Набор операторов1

BREAK

Набор опреторов2

CONTINUE

Конструкции BREAK и CONTINUE являются необязательными. Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.

Пример:

DECLARE @a INT

SET @a = 1

WHILE @a <100

BEGIN

PRINT @a -- вывод на экран значения переменной

IF (@a>40) AND (@a<50)

BREAK --выход и выполнение 1-й команды за циклом

ELSE

SET @a = @a+rand()*10

CONTINUE

END

PRINT @a

Хранимые процедуры

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

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

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

Перейдём к созданию хранимых процедур. Для работы с хранимыми процедурами в обозревателе объектов необходимо выделить папку «Programmability/Stored Procedures» базы данных «db_students». Для создания новой хранимой процедуры щёлкним ПКМ по папке «Stored Procedures» и в появившемся меню выберем пункт «New Stored Procedure». Появиться окно кода новой хранимой процедуры:

Хранимая процедура имеет следующую структуру (Рис.5.2):

1. Область настройки параметров синтаксиса процедуры. Позволяет настраивать некоторые синтаксические правила, используемые при наборе кода процедуры. В нашем случае это:

• SET ANSI_NULLS ON – включает использование значений NULL

(Пусто) в кодировке ANSI,

• SET QUOTED_IDENTIFIER ON - включает возможность использования двойных кавычек для определения идентификаторов;

2. Область определения имени процедуры (Procedure_Name) и параметров передаваемых в процедуру (@Param1, @Param2). Определение параметров имеет следующий синтаксис:

@<Имя параметра> <Тип данных> = <Значение по умолчанию>

Параметры разделяются между собой запятыми;

3. Начало тела процедуры, обозначается служебным словом «BEGIN»;

4. Тело процедуры, содержит команды языка программирования запросов TSQL;

5. Конец тела процедуры, обозначается служебным словом «END».

Замечание: В коде зелёным цветом выделяются комментарии. Они не обрабатываются сервером и выполняют функцию пояснений к коду. Строки комментариев начинаются с подстроки «--». Далее в коде, мы не будем отображать комментарии, они будут свёрнуты. Слева от раздела с комментариями будет стоять знак «+», щёлкнув по которому можно развернуть комментарий. Наберём код процедуры вычисляющей произведение двух чисел:

Рассмотрим код процедуры более подробно:

1. CREATE PROCRDURE mult – определяет имясоздаваемой процедуры;

2. @Value1 Real = 3, @Value2 Real = 7 - определяют два параметра процедуры Value1, Value2. Данным

параметрам можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 3 и 7;

3. SELECT 'Произведение'=@Value1*@Value2 – вычисляет произведение и выводит результат с подписью «'Произведение'».

Для создания процедуры, выполним вышеописанный код, нажав кнопку (Выполнить) на панели инструментов. В нижней части окна с кодом появиться сообщение «Command(s) completed successfully». Закройте окно с кодом, щёлкнув мышью по кнопке закрытия, расположенной в верхнем правом углу окна с кодом процедуры.

Проверим работоспособность созданной хранимой процедуры. Для запуска хранимой процедуры необходимо создать новый пустой запрос, нажав на кнопку (Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберем команду EXEC mult и нажмите кнопку на панели инструментов:

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

Создадим хранимую процедуру для отбора преподавателей из таблицы студенты по их «ФИО».

Рассмотрим код процедуры:

1. CREATE PROCEDURE proc_teachers –определяет имя создаваемой процедуры как proc_teachers;

2. @FIO Varchar(50)=’’ - определяют единственный параметр процедуры FIO. Параметру можно присвоить текстовые сроки переменной длины, длинной до 50 символов (Тип данных Varchar(50)), значения по умолчанию равны пустой строке;

3. SELECT * FROM dbo.teachers WHERE fio=@FIO – отобразить все поля (*) из таблицы dbo.teachers, где значение поля fio равно значению параметра @FIO).

Проверим работоспособность созданной хранимой процедуры. Создадим новый пустой запрос. В появившемся окне с пустым запросом наберем команду:

EXEC proc_teachers 'Петров'

и нажмем кнопку на панели инструментов.

Замечание: нельзя создавать процедуры с одинаковым именем таблицы!

Попробуйте написать более сложные запросы SELECT в хранимых процедурах.