Главная | Обратная связь | Поможем написать вашу работу!

Лекция 9: Создание динамических запросов при помощи хранимых процедур

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

Хранимая процедура - SQL запрос, который имеет параметры, то есть он выполняется как обычная процедура (мы задаем ее имя и передаем в хранимую процедуру значение параметров.) В зависимости от значения параметров хранимой процедуры мы получаем тот или иной результат запроса.

Замечание. В SQL сервере хранимые процедуры реализуют динамические запросы, выполняемые на стороне сервера.

Рассмотрим создание хранимых процедур при помощи команд языка SQL. Чтобы отобразить хранимые процедуры рабочей БД панели "Object Explorer" нужно выделить пункт "Stored Procedures". Чтобы создать новую процедуру при помощи команд языка SQL нужно щелкнуть ЛКМ по кнопке

на панели инструментов. В рабочей области окна сервера появится вкладка SQLQuery1.sql, где нужно набрать код новой процедуры, который имеет следующий синтаксис:

CREATE PROCEDURE <Имя процедуры>

[@<Параметр1> <Тип1>[=<Значение1>],

@<Параметр2> <Тип2>[=<Значение2>], …]

[WITH ENCRYPTION]

AS <Команды SQL>

Здесь:

· Имя процедуры- имя создаваемой хранимой процедуры.

· Параметр1, Параметр2, …- параметры передаваемые в процедуру.

· Значение1, Значение2, …- значения параметров по умолчанию.

· Тип1, Тип2, …- типы данных параметров.

· WITH ENCRYPTION- включает шифрование данных.

· Команды SQL- SQL запрос, который выполняется при запуске процедур.

Замечание: SQL запрос включает в себя параметры, если параметры сравниваются с какими то полями или выражениями, то они должны иметь точно такой же тип данных как эти поля или выражения.

Замечание: После создания процедура помещается в раздел Stored Procedures текущей БД на панели "Object Explorer". Если дважды щелкнуть по процедуре ЛКМ, то она откроется для редактирования на вкладке "SQLQuery".

Чтобы посмотреть информацию о хранимой процедуре необходимо выполнить команду:

EXEC SP_HELPTEXT <Имя процедуры>

Хранимые процедуры могут быть запущены следующей командой

EXEC <Имя процедуры> [<Параметр1>, <Параметр2>, …]

Здесь:

· <Имя процедуры>- имя выполняемой процедуры.

· Параметр1, Параметр2, …- значение параметров.

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

CREATE PROCEDURE СрБАЛЛ

@X Real

SELECT *

FROM Студенты

WHERE

(Оценка1+ Оценка2+ Оценка3)/3>@X

Команда вызова приведенной выше процедуры выглядит следующим образом:

EXEC СрБАЛЛ 4

Команда выводит всех студентов, у которых средний балл больше 4.

На этом мы заканчиваем рассмотрение хранимых процедур. Дополнительную информацию можно найти в лабораторной работе №5.

Лабораторная работа: Хранимые процедуры

Цель: научиться работать с хранимыми процедурами

Перейдем к созданию хранимых процедур. Для работы с хранимыми процедурами в обозревателе объектов необходимо выделить папку "Programmability/Stored Procedures" базы данных "Students" (рис. 10.1).

Рис. 10.1.

Создадим процедуру, вычисляющую среднее трех чисел. Для создания новой хранимой процедуры щелкните ПКМ по папке "Stored Procedures" (рис. 10.1) и в появившемся меню выберите пункт "New Stored Procedure". Появиться окно кода новой хранимой процедуры (рис. 10.2).

увеличить изображение
Рис. 10.2.

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

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

o SET ANSI_NULLS ON - включает использование значений NULL (Пусто) в кодировке ANSI,

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

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

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

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

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

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

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

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

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

Рис. 10.3.

Рассмотрим код данной процедуры более подробно (рис. 10.3):

1. CREATE PROCEDURE [Среднее трех величин]- определяет имя создаваемой процедуры как "Среднее трех величин";

2. @Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 - определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;

3. SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3 - вычисляет среднее и выводит результат с подписью "Среднее значение".

Остальные фрагменты кода рассмотрены выше (рис. 10.2).

Для создания процедуры, выполним вышеописанный код, нажав кнопку

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

расположенной в верхнем правом углу окна с кодом процедуры.

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

(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду EXEC [Среднее трех величин] 1, 7, 9 и нажмите кнопку

на панели инструментов (рис. 10.4).

увеличить изображение
Рис. 10.4.

В нижней части окна с кодом появится результат выполнения новой хранимой процедуры: Среднее значение 5,66667 (рис. 10.4).

Теперь создадим хранимую процедуру для отбора студентов из таблицы студенты по их "ФИО". Для этого создайте новую хранимую процедуру, как это описано выше, и наберите код новой процедуры как на рис. 10.5.

увеличить изображение
Рис. 10.5.

Рассмотрим код процедуры "Отображение студентов по ФИО" более подробно (рис. 10.5):

1. CREATE PROCEDURE [Отображение студентов по ФИО] - определяет имя создаваемой процедуры как "Отображение студентов по ФИО";

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

3. SELECT * FROM dbo.Студенты WHERE ФИО=@FIO- отобразить все поля (*) из таблицы студенты (dbo.Студенты), где значение поля ФИО равно значению параметра FIO (ФИО=@FIO).

Выполним вышеописанный код и закроем окно с кодом, как описано выше.

Проверим работоспособность созданной хранимой процедуры. Создайте новый пустой запрос. В появившемся окне с пустым запросом наберите команду EXEC [Отображение студентов по ФИО] 'Иванов А.И.' и нажмите кнопку

на панели инструментов (рис. 10.6).

увеличить изображение
Рис. 10.6.

В нижней части окна с кодом появиться результат выполнения хранимой процедуры "Отображение студентов по ФИО" (рис. 10.6).

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

увеличить изображение
Рис. 10.7.

Рассмотрим код процедуры "Отображение студентов по среднему баллу" более подробно (рис. 10.7):

1. CREATE PROCEDURE [Отображение студентов по среднему баллу] - определяет имя создаваемой процедуры как "Отображение студентов по среднему баллу";

2. @Grade Real=0 - определяют параметр процедуры Grade. Параметру можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;

3. SELECT * FROM [Запрос Студенты+Оценки] WHERE ([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3>@Grade - отобразить все поля (*) из запроса "Запрос Студенты+Оценки" (Запрос Студенты+Оценки), где средний балл больше чем значение параметра Grade (([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3>@Grade).

Выполним вышеописанный код и закроем окно с кодом, как описано выше. Проверим, как работает запрос, описанный выше. Для этого, создайте новый запрос и в нем наберите команду EXEC [Отображение студентов по среднему баллу] 3.5и выполните ее (Смотри выше) (рис. 10.8).

увеличить изображение
Рис. 10.8.

В нижней части окна с кодом появиться результат выполнения хранимой процедуры "Отображение студентов по среднему баллу" (рис. 10.8).

В заключение решим более сложную задачу - отображение студентов старше заданного возраста. При чем возраст будет автоматически вычисляться в зависимости от даты рождения.

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

увеличить изображение
Рис. 10.9.

Рассмотрим код создаваемой процедуры "Отображение студентов по возрасту" более подробно (рис. 10.9):

1. CREATE PROCEDURE [Отображение студентов по возрасту] - определяет имя создаваемой процедуры как "Отображение студентов по возрасту";

2. @Age int=0- определяют параметр процедуры Age. Параметру можно присвоить целые числа (Тип данных int), значения по умолчанию равны 0;

3. ФИО, [Запрос Студенты+Специальности].[Дата рождения], 'Возраст'=DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE()) - отображает из запроса "Запроса Студенты+Специальности" (FROM [Запрос Студенты+Специальности]) поля "ФИО" (ФИО) и "Дата рождения" ([Запрос Студенты+Специальности].[Дата рождения]), а также отображает возраст студента ('Возраст') в годах (yy), вычисленный исходя из его даты рождения и текущей даты (DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE())). Более того, выводятся студенты возраст которых больше определенного в параметре "Age" (DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE())>@Age).

Замечание: Встроенная функция DATEDIFF вычисляющая количество периодов между двумя датами, имеет следующий синтаксис: DATEDIFF(<период>,<начальная дата>, <конечная дата>)

Выполним код запроса "Отображение студентов по возрасту", а затем закроем окно с кодом, как описано выше. Проверим, как работает запрос. Для этого, создадим новый запрос и в нем наберем команду EXEC [Отображение студентов по возрасту] 26 и выполните ее. Должен появиться результат аналогичный результату, представленному на рис. 10.10.

увеличить изображение
Рис. 10.10.

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

Рис. 10.11.

Лекция 11: Пользовательские функции

Цели:

1. Изучить порядок создания пользовательских функций

2. Освоить применение пользовательских функций

Пользовательские функции очень похожи на хранимые процедуры. Так же в них можно передавать параметры и они выполняют некоторые действия, однако их главным отличием от хранимых процедур является то, что они выводят (возвращают) какой то результат. Более того, они вызываются только при помощи оператора SELECT, аналогично встроенным функциям. Все пользовательские функции делятся на 2 вида:

1. Скалярные функции - функции, которые возвращают число или текст, то есть одно или несколько значений;

2. Табличные функции - функции, которые выводят результат в виде таблицы.

Для создания новой пользовательской функции используется команда CREATE FUNCTION имеющая следующий синтаксис:

CREATE FUNCTION <Имя функции>

([@<Параметр1> <Тип1>[=<Значение1>],

@<Параметр2> <Тип2>[=<Значение2>],...])

RETURNS <Тип>/TABLE

RETURN([<Команды SQL>])

Здесь:

· Имя функции - имя создаваемой пользовательской функции.

· Параметр1, Параметр2,.. - параметры передаваемые в функцию.

· Значение1, Значение2, …- значения параметров по умолчанию.

· Тип1, Тип2,... - типы данных параметров.

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

После служебного слова RETURN ставится SQL команда самой функции.

Замечание: После служебного слова RETURN может быть несколько команд, которые располагаются между словами BEGIN и END. В этом случае служебное слово RETURN не ставится.

Замечание: Тип данных параметра должен совпадать с типом данных выражения, в котором он используется.

Замечание: Если используются несколько SQL команд и BEGIN и END, то перед END нужно ставить команду RETURN <результат функции>.

Пример (скалярная пользовательская функция): Функция для вычисления среднего 3 чисел:

CREATE FUNCTION Среднее

(@X1 Int,@X2 Int,@X3 Int)

RETURNS Real

BEGIN

DECLARE @Res Real

SET @Res =(@X1+@X2+@X3)/3

RETURN @Res

END

Замечание: Команда DECLARE создает переменную Res для хранения дробных чисел (тип данных Real).

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

CREATE FUNCTION Среднее

(@X1 Int,@X2 Int,@X3 Int)

RETURNS Real

RETURN (SELECT (@X1+@X2+@X3)/3)

Созданная функция, вычисляющая среднее 6, 3 и 3, запускается следующим образом:

SELECT Среднее (6, 3, 3)

Результат будет 4.

Пример (табличная пользовательская функция): Из таблицы Студенты выводятся поля ФИО, дата рождения и столбец возраст, который вычисляется как разница дат в годах, между датой рождения и текущей датой (параметр CurDate).

CREATE FUNCTION Возраст

(@CurDate Date)

RETURNS TABLE

RETURN (SELECT ФИО, [Дата рождения], Возраст = DATEDIFF (yy,[Дата рождения], @CurDate)

FROM Студенты)

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

SELECT * FROM Возраст ('12/17/2007')

В результате отобразятся студенты с их возрастом на 17 декабря 2007 года.

На этом мы заканчиваем рассмотрение пользовательских функций. Дополнительную информацию можно найти в лабораторной работе №6.

Лабораторная работа: Пользовательские функции

Цель: научиться работать с пользовательскими функциями

Теперь рассмотрим создание и применение пользовательских функций. В БД "Microsoft SQL Server 2008" все пользовательские функции находятся в папке "Functions" расположенной в папке "Programmability" в обозревателе объектов (рис. 12.1).

Рис. 12.1.

Начнем с создания скалярных пользовательских функций. Для создания новой скалярной пользовательской функции в обозревателе объектов, в БД "Students", в папке "Programmability", щелкните ПКМ по папке "Functions" и в появившемся меню выберите пункт "New/Scalar-valued Function". Появится окно новой скалярной пользовательской функции (рис. 12.2)

увеличить изображение
Рис. 12.2.

Синтаксис скалярной пользовательской функции похож на синтаксис хранимой процедуры (см. занятие 17). Однако имеется ряд существенных отличий (рис. 12.2):

1. Область определения имени функции (Inline_Function_Name);

2. Параметры, передаваемые в процедуру (@Param1). Определение параметров аналогично определению параметров в хранимой процедуре (см. занятие 5);

3. Тип данных значения возвращаемого процедурой;

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

DECLARE @<Имя переменной> <Тип данных>

5. Тело самой пользовательской функции, содержит команды языка программирования запросов T-SQL;

6. Команда RETURN возвращающая результат выполнения функции. Имеет следующий синтаксис:

RETURN @<Имя переменной с результатом>

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

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

увеличить изображение
Рис. 12.3.

Рассмотрим более подробно код данной скалярной пользовательской функции (рис. 12.3):

1. CREATE FUNCTION [Функция средних трех величин]- определяет имя создаваемой функции как "Функция средних трех величин";

2. @Value1 Real, @Value2, @Value3- определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить целые числа (Тип данных Int);

3. RETURNS Real- показывает, что функция возвращает дробные числа (Тип данных Real);

4. DECLARE @Result Real- объявляется переменная @Result для хранения результата работы функции, то есть дробного числа (Тип данных Real);

5. SELECT @Result=(@Value1+@Value2+@Value3)/3 - вычисляет среднее и помещает результат в переменную @Result;

6. RETURN @Result- возвращает значение переменной @Result.

Остальные фрагменты кода рассмотрены выше (рис. 12.2).

Для создания функции, выполним вышеописанный код, нажав кнопку

расположенной в верхнем правом углу окна с кодом функции.

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

(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду SELECT dbo.[Функция средних трех величин] (3, 5, 4)и нажмите кнопку

на панели инструментов (рис. 12.4).

увеличить изображение
Рис. 12.4.

В нижней части окна с кодом появится результат выполнения новой скалярной пользовательской функции: 4 (рис. 12.4).

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

Создайте новую скалярную пользовательскую функцию, так как об этом сказано выше. В окне новой пользовательской функции наберите следующий код (рис. 12.5):

увеличить изображение
Рис. 12.5.

Перейдем к рассмотрению вышеприведенного кода (рис. 12.5). Код состоит из следующих групп команд:

1. CREATE FUNCTION [Последний день месяца] - определяет имя создаваемой функции как "Последний день месяца";

2. @MyDate- определяют параметр процедуры MyDate. Параметру можно присвоить значения дат или времени (Тип данных DateTime);

3. RETURNS DateTime - показывает, что функция возвращает дату или время (Тип данных DateTime);

4. DECLARE @Year Int, DECLARE @Month Int, DECLARE @Day Int - объявляются переменные @Year, @Monthи @Day для хранения целочисленных значений года, месяца и дня введенной даты (Тип данных Int).

DECLARE @TmpDate VarChar(10) объявляет переменную "TmpDate" для хранения промежуточного значения даты в строке длинной до 10 символов (Тип данных VarChar(10)).

DECLARE @Result DateTime объявляет переменную "Result" для хранения результата - даты последнего дня месяца (Тип данных DateTime).

5. SET @Year=DatePart(yy, @MyDate), SET @Month=DatePart(mm, @MyDate), SET @Day=DatePart(dd, @MyDate) - определяются части введенной даты и помещаются в переменные @Year, @Monthи @Day. Для определения частей даты используется функция DatePart, имеющая следующий синтаксис: DatePart(<часть даты>, <дата>). Здесь "часть даты" - это закодированная специальными символами определяемая часть даты (yy - год, mm - месяц, dd - день), "дата" - это дата, части которой определяем.

IF @Month=12

BEGIN

SET @Month=1

SET @Year=@Year+1

END

ELSE

BEGIN

SET @Month=@Month+1

END

Выше приведенный фрагмент кода выполняет следующие действия: Если номер месяца равен 12 то установить номер месяца (@Month) равным 1 и увеличить год (@Year) на 1, иначе увеличить месяц на 1.

6. SET @TmpDate=Convert(Varchar, @Month)+'/01/'+Convert(Varchar, @Year), SET @Result=Convert(DateTime, @TmpDate) - переводит числовые значения даты в дату в строковом формате и записывает ее в переменную @TmpDate, затем переводит дату в строковом формате в тип данных даты и времени и помещает ее в переменную @Result. Для конвертации используется функция Convert, имеющая следующий синтаксис:

Convert(<тип данных>, <значение>), здесь "тип данных" это тип данных в который переводится "значение".

7. SET @Result=DateAdd(dd, -1, @Result) - из даты, хранимой в переменной @Result вычитается 1 день, для этого используется функция DateAdd, имеющая следующий синтаксис:

DateAdd(<часть даты>, <количество периодов>, <дата>) - здесь "часть даты"- это закодированная специальными символами определяемая часть даты (см. функцию DatePart), "количество периодов" - это количество частей даты прибавляемой к введенной дате (параметр "дата").

8. RETURN @Result- возвращает значение, хранимое в переменной @Result.

Для создания функции, выполним вышеописанный код, как и в случае с предыдущей функцией, нажав кнопку

После появления сообщения "Command(s) completed successfully." закройте окно с кодом.

Проверим работу функции "Последний день месяца" выполнив ее. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду SELECT dbo.[Последний день месяца] ('12/07/08')и нажмите кнопку

на панели инструментов (рис. 12.6).

увеличить изображение
Рис. 12.6.

Появится результат выполнения новой скалярной пользовательской функции: 2008-12-31 (рис. 12.6).

Теперь перейдем к созданию табличных пользовательских функций. Для создания табличной пользовательской функции в обозревателе объектов, в БД "Students", в папке "Programmability", щелкните ПКМ по папке "Functions" и в появившемся меню выберите пункт "New/Table-valued Function". Появится окно новой табличной пользовательской функции (рис. 12.7)

увеличить изображение
Рис. 12.7.

Рассмотрим структуру кода табличной пользовательской функции. Табличная пользовательская функция состоит из следующих разделов:

1. Область определения имени функции (Inline_Function_Name);

2. Параметры, передаваемые в процедуру (@Param1, @Param2);

3. RETURNS TABLE показывает что функция является табличной, то есть возвращает таблицу;

4. Тело самой пользовательской функции, состоит из команды SELECT языка программирования запросов T-SQL.

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

В заключение рассмотрим создание табличной пользовательской функции "Функция отбора по возрасту", вычисляющих текущий возраст студентов в зависимости от их даты рождения. В окне новой пользовательской функции (рис. 12.7) наберите следующий код (рис. 12.8):

увеличить изображение
Рис. 12.8.

Из кода представленного на рис. 12.8 видно, что данная табличная функция не имеет параметров и реализуется командой

SELECT ФИО, [Дата рождения], Возраст = DateDiff(yy, [Дата рождения], GetDate())

FROM Студенты

Из выше представленной команды видно, что из таблицы "Студенты" отображаются поля "ФИО" и "Дата рождения", а также вычислимое поле "Возраст". Поле "Возраст" вычисляется при помощи встроенной функции DateDiff вычисляющей различие между датами в определенных единицах измерения (частях даты) и имеющей следующий синтаксис:

DateDiff(<часть даты>, <начальная дата>, <конечная дата>).

Здесь "часть даты"- это закодированные специальными символами единицы измерения (часть даты) (yy - год, mm - месяц, dd - день), "начальная дата" - дата начала периода и "конечная дата" - дата конца периода. В нашем случае в качестве начальной даты берем дату рождения студента, а в качестве конечной даты берем текущую дату (функция GetDate()).

Для создания функции, выполним вышеописанный код, как и в случае с предыдущей функцией. После появления сообщения "Command(s) completed successfully." закройте окно с кодом.

Проверим работоспособность новой табличной пользовательской функции. Создайте новый пустой запрос, затем в окне с пустым запросом наберите команду SELECT * FROM dbo.[Функция отбора по возрасту]()и нажмите кнопку

на панели инструментов (рис. 12.9).

увеличить изображение
Рис. 12.9.

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

Замечание: Обратите внимание на тот факт, что мы работаем с табличной функцией как с обыкновенной таблицей.

На этом мы заканчиваем рассмотрение пользовательских функций и переходим к рассмотрению целостности данных, диаграмм и триггеров. По окончании выполнения главы 6 обозреватель объектов будет иметь следующий вид (рис. 12.10):

Рис. 12.10.

Лекция 13: Целостность данных. Диаграммы и триггеры

Цели:

1. Изучить порядок обеспечения целостности данных

2. Понятие порядок создания триггеров

Целостность данных

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

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

Для обеспечения целостности данных в SQL Server используют диаграммы и триггеры.

Диаграммы - это компоненты БД, которые блокируют удаление записей из первичных таблиц если существуют связанные с ними записи во вторичных таблицах. Следовательно, диаграммы предотвращают нарушение целостности данных. В SQL Server диаграммы создаются при помощи мастера диаграмм, его описание представлено в лабораторной работе.

Триггеры - это аналог процедур обработчиков событий в Visual Basic. То есть они выполняют команды SQL если происходят какие-либо действия с таблицей (Например: добавление, изменение или удаление записей). При помощи триггеров можно организовать автоматическое удаление записей из вторичной таблицы при удалении связанной с ними записи из первичной таблицы.

Рассмотрим создание триггеров при помощи языка SQL.

Создание триггеров

В SQL Server существуют два вида триггеров:

1. Триггеры выполняемые после события, произошедшего с таблицей (Полный аналог процедур событий в Visual Basic);

2. Триггеры выполняемые вместо события, происходящего с таблицей. В этом случае событие (добавление, изменение или удаление записей) не выполняется, а вместо него выполняются SQL команды заданные внутри триггера.

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

Замечание: Триггеры создаются для конкретной таблицы и выполняются автоматически, если с таблицей, для которой они были созданы, происходит событие (добавление, изменение или удаление записей).

Для создания триггера на вкладке нового запроса необходимо набрать команду CREATE TRIGGER, имеющую следующий синтаксис:

CREATE TRIGGER <Имя триггера>

ON <Имя таблицы>

FOR <INSERT|UPDATE|DELETE>

[WITH ENCRYPTION]

AS <Команды SQL>

Здесь:

· Имя триггера - это имя создаваемого триггера.

· Имя таблицы - имя таблицы, для которой создаётся триггер.

· Если используется параметр AFTER, то триггер выполняется после события, а если параметр INSTEAD OF, то выполняется вместо события.

· Параметры INSERT, UPDATE и DELETE определяют событие, при котором (или вместо которого) выполняется триггер.

· Параметр WITH ENCRYPTION - предназначен для включения шифрования данных при выполнении триггера.

· Команды SQL - это SQL команды, выполняемые при активизации триггера.

Рассмотрим примеры создания различных триггеров для таблицы "Студенты".

Пример: Создаёт триггер "Добавление", выводящий на экран сообщение "Запись добавлена" при добавлении новой записи в таблицу "Студенты"

CREATE TRIGGER Добавление

ON Студенты

FOR AFTER INSERT

AS PRINT 'Запись добавлена'

Пример: Создаёт триггер "Изменение", выводящий на экран с сообщение "Запись изменена" при изменении записи в таблице "Студенты"

CREATE TRIGGER Изменение

ON Студенты

FOR AFTER UPDATE

AS PRINT 'Запись изменена'

Пример: Создаёт триггер "Удаление", выводящий на экран с сообщение "Запись удалена" при удалении записи из таблицы "Студенты"

CREATE TRIGGER Удаление

ON Студенты

FOR AFTER DELETE

AS PRINT 'Запись удалена'

Пример: В данном примере вместо удаления студента из таблицы "Студенты" выполняется код между BEGIN и END. Он состоит из двух команд DELETE. Первая команда удаляет все записи из таблицы "Оценки", которые связаны с записями из таблицы "Студенты". То есть у которых Оценки.[Код студента] равен коду удаляемого студента. Затем из таблицы "Студенты" удаляется сам студент.

CREATE TRIGGER УдалениеСтудента

ON Студенты

INSTEAD OF DELETE

BEGIN

DELETE Оценки

FROM deleted

WHERE deleted.[Код студента]=Оценки.[Код студента]

DELETE Студенты

FROM deleted

WHERE deleted.[Код студента]=Студенты.[Код студента]

END

Замечание: Здесь удаляемая запись обозначается служебным словом deleted.

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

На этом мы заканчиваем рассмотрение диаграмм и триггеров. Дополнительную информацию можно найти в лабораторной работе №7.

Не верно

Лабораторная работа: Диаграммы и триггеры

Цель: научиться создавать диаграммы и триггеры

Перейдем теперь к созданию диаграмм. В БД "Microsoft SQL Server 2008" все диаграммы находятся в папке "Database Diagrams" обозревателя объектов (рис. 14.1).

Рис. 14.1.

Создадим диаграмму, обеспечивающую целостность данных нашей БД "Students". Для создания новой диаграммы в БД "Students" щелкните ПКМ по папке "Database Diagrams" и в появившемся меню выберем пункт "New Database Diagram". Сначала появится окно с вопросом о добавлении нового объекта "Диаграмма". В этом окне нужно нажать кнопку "Yes". Затем появится окно "Add Table" предназначенное для добавления таблиц в новую диаграмму (рис. 14.2).

Рис. 14.2.

В окне добавления таблиц выделите все таблицы нашей БД и нажмите кнопку "Add" (рис. 14.2). Закройте окно "Add Table" нажатием на кнопку "Close".

Появится окно диаграммы, где будут отображены отобранные таблицы. Теперь необходимо определить связи между таблицами. Перетащите поле "Код специальности" из таблицы "Специальности" на такое же поле в таблице "Студенты". Появится окно создания связи между таблицами "Tables and Columns" (рис. 14.3).

Рис. 14.3.

В окне создания связи нажмите кнопку "Ok". Появится окно настройки свойств связи "Foreign Key Relationship" (рис. 14.4).

увеличить изображение
Рис. 14.4.

Оставьте свойства связи без изменений и в окне свойств связи нажмите кнопку "Ok". В диаграмме между таблицами "Студенты" и "Специальности" появится связь в виде ломанной линии (рис. 14.5).

Аналогичным образом создайте связь таблицы "Студенты" с таблицей "Оценки", перетащив поле "Код студента" из таблицы "Студенты" на одноименное поле в таблице "Оценки". Затем, свяжите таблицы "Предметы" и "Оценки", перетащив поле "Код предмета" из таблицы "Предметы" на поля "Код предмета 1", "Код предмета 2" и "Код предмета 3" таблицы "Оценки". После выполнения вышеперечисленных действий диаграмма примет следующий вид (рис. 14.5).

увеличить изображение
Рис. 14.5.

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

расположенной в верхнем правом углу окна с диаграммой. Появится окно с вопросом о сохранении новой диаграммы, где необходимо нажать кнопку "Yes" (рис. 14.6).

Рис. 14.6.

Появится окно определения имени новой диаграммы "Choose Name". В окне определения имени, задайте имя диаграммы как "Диаграмма БД Студенты" и нажмите кнопку "Ok" (рис. 14.7).

Рис. 14.7.

Появится окно "Save" с запросом сохранения таблиц, входящих в диаграмму. В данном окне необходимо нажать кнопку "Yes" (рис. 14.8).

Рис. 14.8.

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

Рис. 14.9.

Для начала создадим триггер, выводящий сообщение "Запись добавлена" при добавлении записи в таблицу "Студенты". Создадим новый триггер, щелкнув ПКМ по папке "Triggers" в таблице "Студенты" и в появившемся меню выбрав пункт "New Trigger". Появится следующее окно с новым триггером (рис. 14.10):

увеличить изображение
Рис. 14.10.

Рассмотрим структуру триггеров:

1. Область определения имени функции (Trigger_Name);

2. Область, показывающая для какой таблицы создается триггер (Table_Name);

3. Область, показывающая когда выполнять триггер (INSERT - при создании записи в таблице, DELETE - при удалении и UPDATE - при изменении) и как его выполнять (AFTER - после выполнения операции, INSTEAD OF - вместо выполнения операции);

4. Тело триггера, содержит команды языка программирования запросов T-SQL.

В окне нового триггера наберите код как показано на рис. 14.11.

Рис. 14.11.

Из рис. 14.11 видно, что создаваемый триггер "Индикатор добавления" выполняется после добавления записи (AFTER INSERT) в таблицу "Студенты" (ON dbo.Студенты). После добавления записи триггер выведет на экран сообщение "Запись добавлена" (PRINT 'Запись добавлена'). Выполните набранный код, нажав кнопку

на панели инструментов. В нижней части окна с кодом появится сообщение "Command(s) completed successfully.".

Проверим, как работает новый триггер. Создайте новый пустой запрос и в нем наберите следующую команду для добавления новой записи в таблицу "Студенты" (рис. 14.12):

увеличить изображение
Рис. 14.12.

Выполните набранную команду, нажав кнопку

на панели инструментов. В таблицу будет добавлена новая запись, и триггер выведет сообщение "Запись добавлена" (рис. 14.12).

Теперь создадим триггер отображающий сообщение "Запись изменена". Создайте новый триггер, как в предыдущем случае. В окне нового триггера наберите следующий код (рис. 14.13):

увеличить изображение
Рис. 14.13.

Из рис. 14.13 видно, что новый триггер "Индикатор изменения" выполняется после изменения записи (AFTER UPDATE) в таблице "Студенты" (ON dbo.Студенты). После изменения записи триггер выведет на экран сообщение "Запись изменена" (PRINT 'Запись изменена'). Выполните набранный код. В нижней части окна с кодом появится сообщение "Command(s) completed successfully.".

Проверим работоспособность созданного триггера. Создайте новый запрос и в нем наберите команду, представленную на рис. 14.14.

увеличить изображение
Рис. 14.14.

Выполните набранную команду, нажав кнопку

на панели инструментов. В таблицу будет добавлена новая запись, и триггер выведет сообщение "Запись изменена" (рис. 14.14).

Для полноты картины создадим триггер, выводящий сообщение при удалении записи из таблицы "Студенты". Создайте новый триггер и в нем наберите код, показанный на рис. 14.15.

Рис. 14.15.

Создаваемый триггер "Индикатор удаления" выполняется после удаления записи (AFTER DELETE) из таблицы студенты (ON dbo.Студенты). После удаления записи триггер выводит сообщение "Запись удалена" (PRINT 'Запись удалена').

Выполните код, представленный рис. 14.15. В нижней части окна с кодом появится сообщение "Command(s) completed successfully.".

Проверим работу триггера "Индикатор удаления" удалив созданную ранее запись из таблицы "Студенты". Для этого создайте новый запрос и в нем наберите следующую команду (рис. 14.16):

увеличить изображение
Рис. 14.16.

Выполните вышеприведенную команду. После удаления записи триггер "Индикатор удаления" отобразит сообщение "Запись удалена" (рис. 14.16).

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

Создайте новый триггер и в нем наберите следующий код (рис. 14.17):

Рис. 14.17.

Создаваемый триггер "Удаление студента" выполняется вместо удаления записи (INSTEAD OF DELETE) из таблицы "Студенты" (ON dbo.Студенты).

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

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

DELETE dbo.Оценки FROM Deleted

WHERE Deleted.[Код студента] = Оценки.[Код студента]

Затем удаляется запись из таблицы "Студенты", которую удаляли до срабатывания триггера. Удаление выполняется следующими командами:

DELETE dbo.Студенты

FROM Deleted

WHERE Deleted.[Код студента] = Студенты.[Код студента]

Выполните код, представленный на рис. 14.17. В нижней части окна с кодом появиться сообщение "Command(s) completed successfully.".

Проверим, как работает триггер "Удаление студента". Для этого создайте новый запрос и в нем наберите следующий код (рис. 14.18):

увеличить изображение
Рис. 14.18.

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

Замечание: Хотелось бы заметить, что без использования триггера "Удаление студента" нам бы не удалось удалить запись из таблицы "Студенты". Команда удаления была бы заблокирована диаграммой "Диаграмма БД Студенты" во избежание нарушения целостности данных.

На этом мы завершаем работу с диаграммами и триггерами. После выполнения всех вышеописанных действий обозреватель объектов будет иметь следующий вид (рис. 14.19):

Рис. 14.19.

⇐ Предыдущая 1 23