Информационные системы

SQRT (Float) - вычисляет квадратный корень числа Float.

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

SELECT ABC(-10) результат 10

SELECT SQRT (16) результат 4

SELECT ROUND (125.85,0) результат 126

SELECT POWER (2,4) результат 16

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

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

'Строка1'+ 'Строка2' присоединяет Строку1 к Строке2 ;

ASCII(Char) - возвращает ASCII код с самого левого символа выражения Char ;

CHAR(Int) - выводит символ соответствующий ASCII коду в выражении Int ;

CHARINDEX(Образец, Выражение) - выводит позицию Образца выражения, то есть где находится Образец в Выражении ;

DIFFERENCE(Выражение1, Выражение2) - сравнивает два выражения, выводит числа от 0 до 4: 0 - выражения абсолютно различны; 4 - выражения абсолютно идентичны. Оба выражения типа данных Char ;

LEFT(Char, Int) - выводит из строки Char Int символов слева;

RIGHT(Char, Int) - выводит из строки Char Int символов справа;

LTRIM(Char) - удаляет из строки Char пробелы слева;

RTRIM(Char) - удаляет из строки Char пробелы справа;

WCHAR(Int) - выводит выражение Int в формате Unicode;

REPLACE(Строка1, Строка2, Строка3) - меняет в Строке1 все элементы Строка2 на элементы Строка3 ;

REPLICATE(Char, Int) - повторяет строку Char Int раз;

REVERSE(Сhar) - производит инверсию строки Char, то есть располагает символы в обратном порядке;

SPACE(Int) - выводит Int пробелов;

STR(Float) - переводит число Float в строку;

STUFF(Выражение1, Начало, Длина, Выражение2) - удаляет из Выражения1 начиная с позиции символа Начало количество символов равное параметру Длина, вместо них подставляет Выражение2 ;

SUBSTRING(Выражение, Начало, Длина) - из Выражения выводится строка заданной Длины начиная с позиции Начало ;

UNICODE(Char) - выводит код в формате Unicode первого символа в строке Char ;

LOWER(Char) - переводит строку Char в маленькие буквы;

UPPER(Char) - переводит строку Char в заглавные буквы.

Примеры применения строковых функций:

SELECT ASCII('G') результат 71.

SELECT LOWER('ABC') результат abc.

SELECT RIGHT('ABCDE',3) результат CDE

SELECT REVERSE('МИР') результат РИМ.

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

Функции дат

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

dd - число дат (от 1 до 31);

dy - день года (число от 1 до 366);

hh - значение часа (0-23)

ms - значение секунд (от 0 до 999)

mi - значение минут (0-59)

qq - значение (1-4)

mm - значение месяцев (1-12)

ss - значение секунд (0-59)

wk - значение номеров недель в году

dw - значение дней недели, неделя начинается с воскресенья (1-7).

yy - значение лет (1753 -999)

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

DATEADD(часть, число, date) - добавляет к дате date часть даты увеличенное на число;

DATEDIFF(часть, date1, date2) - выводит количество частей даты между date1 и date2 ;

DATENAME(часть, date) - выводит символьное значение частей даты к заданной дате (название дней недели);

DATEPART(часть, date) - выводит числовое значение части даты из заданной даты (номер месяца);

DAY(date) - выводит количество дней в заданной дате;

MONTH (date) - выводит количество месяцев в заданной дате;

YEAR(date) - выводит количество лет в заданной дате;

GETDATE() - выводит текущую дату установленную на компьютере;

Замечание: Даты выводятся в Американском формате: месяц/день/год.

Примеры функции работ с датами:

SELECT DATEADD(dd,5,11/20/07) результат Nov/25/2007.

SELECT DATEDIFF(dd,11/20/07, 11/25/07) результат 5 дней.

SELECT DATENAME(mm, 11/20/07) результат November.

SELECT DATEPART(mm, 11/20/07) результат 11.

Замечание: В выражениях оператора SELECT можно использовать операции сравнения. В результате будет либо истина TRUE, либо ложь FALSE. Можно использовать следующие операторы: =, <, >, >=, <=, <>, !<(не меньше), !>(не больше), !=(не равно). Приоритет операции задается круглыми скобками.

Системные функции

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

COL_LENGTH(таблица, поле) - выводит ширину поля;

DATALENGTH(выражение) - выводит длину выражения;

GETANSINULL(имя БД) - выводит допустимо или недопустимо использовать в БД значение NULL ;

IDENT_INCR(таблица) - выводит шаг увеличения поля счетчика в таблице;

IDENT_SEED(таблица) - выводит начальное значение счетчиков в таблице;

ISDATE(выражение) - выводит единицу, если выражение является датой и ноль, если не является;

ISNUMERIC(выражение) - выводит единицу, если выражение является числовым и ноль, если не числовым;

NULIFF(выражение1, выражение2) - выводит NULL если выражение1 равно выражению 2.

Агрегатные функции

Агрегатные функции - позволяют вычислять итоговые значения по полям таблицы.

AVG(поле) - выводит среднее значение поля;

COUNT(*) - выводит количество записей в таблице;

COUNT(поле) - выводит количество всех значений поля;

MAX(поле) - выводит максимальное значение поля;

MIN(поле) - выводит минимальное значение поля;

STDEV(поле) - выводит среднеквадратичное отклонение всех значений поля;

STDEVP(поле) - выводит среднеквадратичное отклонение различных значений поля;

SUM(поле) - суммирует все значения поля;

TOP n [Percent] - выводит n первых записей из таблицы, либо n% записей из таблицы;

VAR(поле) - выводит дисперсию всех значений поля;

VARP(поле) - выводит дисперсию всех различных значений поля.

Примеры использования агрегатных функций:

SELECT AVG(возраст) FROM Студенты - выводит средний возраст студента из таблицы "Студенты".

SELECT COUNT(ФИО) FROM Студенты - выводит количество различных ФИО из таблицы "Студенты".

SELECT Top 100 * FROM Студенты - выводит первые 100 студентов из таблицы "Студенты".

8. Лабораторная работа: Создание запросов и фильтров

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

Перейдем к созданию статических запросов. В обозревателе объектов "Microsoft SQL Server 2008" все запросы БД находятся в папке "Views"

Рис. 8.1.

Создадим запрос "Запрос Студенты+Специальности", связывающий таблицы "Студенты" и "Специальности" по полю связи "Код специальности". Для создания нового запроса необходимо в обозревателе объектов в БД "Students" щелкнуть ПКМ по папке "Views", затем в появившемся меню выбрать пункт "New View". Появится окно "Add Table" (Добавить таблицу), предназначенное для выбора таблиц и запросов, участвующих в новом запросе (рис. 8.2).



Рис. 8.2.

Добавим в новый запрос таблицы "Студенты" и "Специальности". Для этого в окне "Add Table" выделите таблицу "Студенты" и нажмите кнопку "Add" (Добавить). Аналогично добавьте таблицу "Специальности". После добавления таблиц участвующих в запросе закройте окно "Add Table" нажав кнопку "Close" (Закрыть). Появится окно конструктора запросов (рис. 8.3).

Рис. 8.3.

Замечание: Окно конструктора запросов состоит из следующих панелей:

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

Таблица отображаемых полей - показывает отображаемые поля (столбец "Column" ), позволяет задавать им псевдонимы (столбец "Alias" ), позволяет устанавливать тип сортировки записей по одному или нескольким полям (столбец "Sort Type" ), позволяет задавать порядок сортировки (столбец "Sort Order" ), позволяет задавать условия отбора записей в фильтрах (столбцы "Filter" и "Or…" ). Также эта таблица позволяет менять порядок отображения полей в запросе. Эта панель включается и выключается следующей кнопкой на панели инструментов

Код SQL - код создаваемого запроса на языке T-SQL. Эта панель включается и выключается следующей кнопкой на панели инструментов

Результат - показывает результат запроса после его выполнения. Эта панель включается и выключается следующей кнопкой на панели инструментов

Замечание: Если необходимо снова отобразить окно "Add Table" для добавления новых таблиц или запросов, то для этого на панели инструментов "Microsoft SQL Server 2008" нужно нажать кнопку

Замечание: Если необходимо удалить таблицу или запрос из схемы данных, то для этого нужно щелкнуть ПКМ и в появившемся меню выбрать пункт "Remove" (Удалить).

Теперь перейдем к связыванию таблиц "Студенты" и "Специальности" по полям связи "Код специальности". Чтобы создать связь необходимо в схеме данных перетащить мышью поле "Код специальности" таблицы "Специальности" на такое же поле таблицы "Студенты". Связь отобразится в виде ломаной линии соединяющей эти два поля связи (рис. 8.3).

Замечание: Если необходимо удалить связь, то для этого необходимо щелкнуть по ней ПКМ и в появившемся меню выбрать пункт "Remove".

Замечание: После связывания таблиц (а также при любых изменениях в запросе) в области кода T-SQL будет отображаться T-SQL код редактируемого запроса.

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

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

Замечание: Если необходимо отобразить все поля таблицы, то необходимо установить галочку слева от пункта "* (All Columns)" (Все поля), принадлежащего соответствующей таблице на схеме данных.

Определите отображаемые поля нашего запроса, как это показано на рис. 8.3 (Отображаются все поля кроме полей с кодами, то есть полей связи).

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

Либо щелкните ПКМ в любом месте окна конструктора запросов и в появившемся меню выберите пункт "Execute SQL" (Выполнить SQL). Результат выполнения запроса появиться в виде таблицы в области результата (рис. 8.3).

Замечание: Если после выполнения запроса результат не появился, а появилось сообщение об ошибке, то в этом случае проверьте, правильно ли создана связь. Ломаная линия связи должна соединять поля "Код специальности" в обеих таблицах. Если линия связи соединяет другие поля, то ее необходимо удалить и создать заново, как это описано выше.

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

расположенной в верхнем правом углу окна конструктора (над схемой данных). Появится окно с вопросом о сохранении запроса (рис. 8.4).

Рис. 8.4.

В данном окне необходимо нажать кнопку "Yes" (Да). Появится окно "Choose Name" (Выберите имя) (рис. 8.5).

Рис. 8.5.

В данном окне зададим имя нового запроса "Запрос Студенты+Специальности" и нажмем кнопку "Ok".



Запрос появится в папке "Views" БД "Students" в обозревателе объектов (рис. 8.6).

Рис. 8.6.

Проверим работоспособность созданного запроса вне конструктора запросов. Запустим вновь созданный запрос "Запрос Студенты+Специальности" без использования конструктора запросов. Для выполнения уже сохраненного запроса необходимо щелкнуть ПКМ по запросу и в появившемся меню выбрать пункт "Select top 1000 rows" (Отобразить первые 1000 записей). Выполните эту операцию для запроса "Запрос Студенты+Специальности". Результат представлен на рис. 8.6.

Перейдем к созданию запроса "Запрос Студенты+Оценки". В обозревателе объектов в БД "Students" щелкните ПКМ по папке "Views", затем в появившемся меню выберите пункт "New View". Появиться окно "Add Table" (рис. 8.2).

В запросе "Запрос Студенты+Оценки" мы связываем таблицы "Студенты" и "Оценки" по полям связи "Код студента". Следовательно, в окне "Add Table" в новый запрос добавляем таблицы "Студенты" и "Оценки". Более того, в данном запросе таблица "Оценки" связывается с таблицей "Предметы" не по одному полю, а по трем полям. То есть поля "Код предмета 1", "Код предмета 2" и "Код предмета 3" таблицы "Оценки" связаны с полем "Код предмета" таблицы "Предметы". По этому добавим в запрос три экземпляра таблицы "Предметы"(по одному экземпляру для каждого поля связи таблицы оценки). В итоге в запросе должны участвовать таблицы "Студенты", "Оценки" и три экземпляра таблицы "Предметы" (в запросе они будут называться "Предметы","Предметы_1" и "Предметы_2" ). После добавления таблиц закройте окно "Add Table", появится окно конструктора запросов.

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

Рис. 8.7.

Теперь поменяем порядок отображаемых полей в запросе, для этого в таблице отображаемых полей необходимо перетащить поля мышью вверх или вниз за заголовок строки таблицы (столбец перед столбцом "Column" ). Расположите отображаемые поля в таблице отображаемых полей как показано на рис. 8.8.

Рис. 8.8.

Задайте псевдонимы для каждого из полей, просто записав псевдонимы в столбце "Alias" таблицы отображаемых полей, как на рис. 8.8.

Проверьте работоспособность нового запроса, выполнив его. Обратите внимание на то, что реальные названия полей были заменены их псевдонимами. Закройте окно конструктора запросов. В появившемся окне "Choose Name" задайте имя нового запроса "Запрос Студенты+Оценки" (рис. 8.9).



Рис. 8.9.

Проверьте работоспособность нового запроса вне конструктора. Для этого запустите запрос. Результат выполнения запроса "Запрос Студенты+Оценки" должен выглядеть как на рис. 8.10.

Рис. 8.10.

На этом мы заканчиваем рассмотрение обычных запросов и переходим к созданию фильтров.

На основе запроса "Запрос Студенты+Специальности" создадим фильтры, отображающие студентов отдельных специальностей. Создайте новый запрос. Так как он будет основан на запросе "Запрос Студенты+Специальности", то в окне "Add Table" перейдите на вкладку "Views" и добавьте в новый запрос "Запрос Студенты+Специальности" (рис. 8.11). Затем закройте окно "Add Table".

Рис. 8.11.

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

Рис. 8.12.

Замечание: Для отображения всех полей запроса, в данном случае, мы не можем использовать пункт "* (All Columns)" (Все поля). Так как в этом случае мы не можем устанавливать критерий отбора записей в фильтре, а также невозможно установить сортировку записей.

Теперь установим критерий отбора записей в фильтре. Пусть наш фильтр отображает только студентов имеющих специальность "ММ". Для определения условия отбора записей в таблице отображаемых полей в строке, соответствующей полю, на которое накладывается условие, в столбце "Filter", необходимо задать условие. В нашем случае условие накладывается на поле "Наименование специальности". Следовательно, в строке "Наименование специальности", в столбце "Filter" нужно задать следующее условие отбора "='ММ'" (рис. 8.12).

В заключение настроим сортировку записей в фильтре. Пусть при выполнении фильтра сначала происходит сортировка записей по возрастанию по полю "Очная форма обучения", а затем по убыванию по полю "Курс". Для установки сортировки записей по возрастанию, в таблице определяемых полей, в строке для поля "Очная форма обучения", в столбце "Sort Type" (Тип сортировки), задайте "Ascending" (По возрастанию), а в строке для поля "Курс" - задайте "Descending" (По убыванию). Для определения порядка сортировки для поля "Очная форма обучения" в столбце "Sort Order" (Порядок сортировки) поставьте 1, а для поля "Курс" поставьте 2 (рис. 8.12). То есть, при выполнении запроса записи сначала сортируются по полю "Очная форма обучения", а затем по полю "Курс".

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

И

обозначают сортировку по возрастанию и убыванию, а значок

показывает наличие условия отбора.

После установки сортировки записей в фильтре проверим его работоспособность, выполнив его. Результат выполнения фильтра должен выглядеть как на рис. 8.12. Закройте окно конструктора запросов. В качестве имени нового фильтра в окне "Choose Name" задайте "Фильтр ММ" (рис. 8.13) и нажмите кнопку "Ok".

Рис. 8.13.



Фильтр "Фильтр ММ" появится в обозревателе объектов. Выполните созданный фильтр вне окна конструктора запросов. Результат должен быть таким же как на рис. 8.14.

Рис. 8.14.

Самостоятельно создайте фильтры для отображения других специальностей. Данные фильтры создаются аналогично фильтру "Фильтр ММ" (смотри выше). Единственным отличием является условие отбора, накладываемое на поле "Наименование специальности", оно должно быть не "='ММ'", а "='ПИ'", "='СТ'","='МО'" или "='БУ'". При сохранении фильтров задаем их имена соответственно их условиям отбора, то есть "Фильтр ПИ", "Фильтр СТ", "Фильтр МО" или "Фильтр БУ". Проверьте созданные фильтры на работоспособность.

Теперь на основе запроса "Запрос Студенты+Специальности" создадим фильтры, отображающие студентов имеющих отдельных родителей. Для начала создадим фильтр для студентов, из родителей только "Отец". Создайте новый запрос и добавьте в него запрос "Запрос Студенты+Специальности" (рис. 8.11). После закрытия окна "Add Table" сделайте отображаемыми все поля запроса (рис. 8.15).



Рис. 8.15.

В таблице отображаемых полей в строке для поля "Родители", в столбце "Filter", задайте условие отбора равное "='Отец'". Проверьте работу фильтра, выполнив его. В результате выполнения фильтра окно конструктора запросов должно выглядеть как на рис. 8.15.

Закройте окно конструктора запросов. В окне "Choose Name" задайте имя нового фильтра как "Фильтр Отец" (рис. 8.16).

Рис. 8.16.

Выполните фильтр "Фильтр Отец" вне конструктора запросов. Результат должен быть аналогичен рис. 8.17.

Рис. 8.17.

Создайте фильтры для отображения студентов с другими вариантами родителей. Данные фильтры создаются аналогично фильтру "Фильтр Отец" (смотри выше). Единственным отличием является условие отбора, накладываемое на поле "Родители", оно должно быть не "='Отец'", а "='Мать'", "='Отец, Мать'" или "='Нет'". При сохранении фильтров задаем их имена соответственно их условиям отбора, то есть "Фильтр Мать", "Фильтр Отец и Мать" или "Фильтр Нет родителей". Проверьте созданные фильтры на работоспособность.

Наконец создадим фильтры для отображения студентов очной и заочной формы обучения. Начнем с очной формы обучения. Создайте новый запрос и добавьте в него запрос "Запрос Студенты+Специальности". Как и ранее сделайте все поля запроса отображаемыми (рис. 8.18).

Рис. 8.18.

В таблице отображаемых полей в столбце "Filter", в строке для поля "Очная форма обучения" установите условие отбора равное "=1"

Замечание: Поле "Очная форма обучения" является логическим полем, оно может принимать значения либо "True" (Истина), либо "False" (Ложь). В качестве синонимов этих значений в "Microsoft SQL Server 2008" можно использовать 1 и 0 соответственно.

Установите сортировку по возрастанию, по полю курс, задав в строке для этого поля, в столбце "Sort Type", значение "Ascending".

Проверьте работу фильтра, выполнив его. После выполнения фильтра окно конструктора запросов должно выглядеть точно также как на рис. 8.18.



Закройте окно конструктора запросов. Сохраните фильтр под именем "Фильтр очная форма обучения" (рис. 8.19).

Рис. 8.19.

После появления фильтра "Фильтр очная форма обучения" в обозревателе объектов выполните фильтр вне окна конструктора запросов. Результат выполнения фильтра "Фильтр очная форма обучения" представлен на рис. 8.20.

Рис. 8.20.

Самостоятельно создайте фильтр для отображения студентов заочной формы обучения. Данный фильтр создается точно также как и фильтр "Фильтр очная форма обучения". Единственным отличием является условие отбора, накладываемое на поле "Очная форма обучения", оно должно быть не "=1", а "=0". При сохранении фильтра задайте его имя как "Фильтр заочная форма обучения". Проверьте созданный фильтр на работоспособность.

В итоге, после создания всех запросов и фильтров окно обозревателя объектов должно выглядеть следующим образом (рис. 8.21):



Рис. 8.21.

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

AS

SELECT *

FROM Студенты

WHERE

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

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

EXEC СрБАЛЛ 4

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

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

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

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



Рис. 10.1.

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

Рис. 10.2.

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 10.3.

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

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

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

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):

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

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

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

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

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

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

Рис. 10.6.

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

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



Рис. 10.7.

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

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

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

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

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



Рис. 10.8.

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

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

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

Рис. 10.9.

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

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

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

ФИО, [Запрос Студенты+Специальности].[Дата рождения], 'Возраст'=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. Лекция: Пользовательские функции

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

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

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

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

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

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

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

RETURNS <Тип>/TABLE

AS

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

AS

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

AS

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

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

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

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

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

CREATE FUNCTION Возраст

(@CurDate Date)

RETURNS TABLE

AS

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

FROM Студенты)

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

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

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



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

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

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

Рис. 12.1.

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

Рис. 12.2.



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

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

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

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

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

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

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

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

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

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

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

Рис. 12.3.



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

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

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

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

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

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

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

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

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

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

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



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

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

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

Рис. 12.4.

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

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



Рис. 12.5.

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

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

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

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

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).

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.

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

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

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

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

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.

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

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

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

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

Тело самой пользовательской функции, состоит из команды 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. Лекция: Целостность данных. Диаграммы и триггеры

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

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

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

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

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

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

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

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

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

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

Триггеры выполняемые вместо события, происходящего с таблицей. В этом случае событие (добавление, изменение или удаление записей) не выполняется, а вместо него выполняются 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

AS

BEGIN

DELETE Оценки

FROM deleted

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

DELETE Студенты

FROM deleted

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

END

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

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

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

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

Перейдем теперь к созданию диаграмм. В БД "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).

...