Практическая работа 1. Проектирование базы данных

• Название: Практическая работа 1. Проектирование базы данных
• Дата: 17.12.2022
• Размер: 2.91 Mb.
• Имя файла: bazy_dannykh._dlia_prakt_.pdf
• Тип: Практическая работа; #849388
• страница: 13 из 19

CREATE VIEW [Имя базы данных.] [имя владельца.]
Имя представления
[(Имя колонки [,... n])]
[WITH{ENCRYPITION\SHEMABINDING\

151
VIEW_METADATA}
AS Команда SELECT
[WITH CHECK OPTION]
Если в команде не заданы имена колонок представления , то они определяются по именам выбираемых колонок в команде SELECT. Параметр ENCRYPTION скрывает код создания этого представления, а параметр SHEMABINDING обеспечивает контроль структуры исходных объектов, к которым обращается оператор SELECT. Опция
WITH CHEC OPTION не позволяет изменять строки таким образом, чтобы они исчезли при отборе командой SELECT.
8.3. Задания для выполнения практической работы №8
Задание 1. Создать представление auth, ссылающегося на таблицу authors базы данных Pubs и содержащего идентификационный номер автора au_lname и телефон phone, при этом отобразить только авторов из Калифорнии ‘СА’ или авторов, не подписавших контракт с издательством, выполнив следующую команду:
CREATE VIEW auth
WITH SHEMABINDING
AS SELECT au_id, au_lname, au_fname, phone
FROM dbo. Authors
WHERE state = ‘CA’ OR contract = 0
WITH CHECK OPTION.
Задание 2. Создать представление report, которое ссылается на представление auth и таблицы titleauthor и titles и в котором выводятся имя автора au_fname, фамилия автора au_lname и сокращенные названия написанных им книг, выполнив команду:
CREATE VIEW report
AS SELECT [Фамилия] = CAST (au_lname aschar(10)),
[Имя] = CAST(au_fname aschar(10)),
[Название книги] =
CAST (title as char(30)) +
CASE WHEN LEN (title) >30 THEN ‘...’ END
FROM auth a, titleauthor ta, titles t
WHERE ta.au_id = a.au_id AND t.title_id = ta .title_id.
Задание 3. Создать представление auth, рассмотренное в первом задании, с помощью графических средств утилиты Enterprise Manager.
Задание 4. Создать представление report, рассмотренное во втором задании, с помощью мастера Create View Wizard.
Задание 5. Сопоставить запросы, полученные автоматически в заданиях 3и 4, с запросами соответственно в первом и втором заданиях. Модифицировать запросы с помощью команды ALTER VIEW и получить справочную информацию об этих представлениях с помощью процедур sp_help, sp_helptexst и sp_depends.

152
ПРАКТИЧЕСКАЯ РАБОТА №9. ОСНОВЫ ПРОГРАММИРОВАНИЯ С
ПОМОЩЬЮ ВСТРОЕННОГО ЯЗЫКА TRANSACT-SQL В MICROSOFT
SQL SERVER
9.1.
Цель работы
Изучить используемый в реляционных СУБД встроенный язык программирования Transact-SQL для написания программ в MS SQL Server. Изучить правила построения идентификаторов, правила объявления переменных и их типов.
Изучить принципы работы с циклами и ветвлениями. Изучить работу с переменными типа Table. Изучить синтаксис и семантику функций и хранимых процедур Transact–
SQL: способов их идентификации, методов задания и спецификации параметров и возвращаемых значений и вызовов функций и хранимых процедур.
9.2.
Исходные данные
Исходными данными является индивидуальное задание и результат предыдущих практических работ.
9.3.
Используемые программы
Программа Microsoft SQL Server Managеmant Studio.
9.4.
Задание
Практическую работу следует выполнять в следующем порядке:
1.
Знакомство с правилами обозначения синтаксиса команд в справочной системе MS SQL Server (утилита Books Online).
2.
Изучение правил написания программ на Transact SQL.
3.
Изучение правил построения идентификаторов, правил объявления переменных и их типов.
4.
Изучение работы с циклами и ветвлениями.
5.
Изучение работы с переменными типа Table.
6.
Изучение правил написания хранимых процедур и функций.
7.
Проработка всех примеров, анализ результатов их выполнения.
8.
Выполнение индивидуальных заданий по вариантам.
Для освоения программирования используем пример базы данных University,
которая была создана в предыдущих практических работах. При выполнении примеров и заданий обращайте внимание на соответствие названий БД, таблиц и других объектов проекта.
9.5.
Теоретические сведения

153
Идентификаторы - это имена объектов, на которые можно ссылаться в программе, написанной на языке Transact SQL. Первый символ может состоять из букв английского алфавита или "@", "#". Остальные дополнительно из цифр и «$».
Имя идентификатора не должно совпадать с зарезервированным словом.
Для ограничителей идентификаторов при установленном параметре SET
QUOTED_IDENTIFIER ON можно использовать как квадратные скобки, так и одинарные кавычки, а строковые значения только в одинарных кавычках (режим по умолчанию).
Если использовать установленный параметр в режиме
SET
QUOTED_IDENTIFIER OFF, то в качестве ограничителей идентификаторов можно использовать только квадратные скобки, а строковые значения указываются в одинарных или двойных кавычках.
Переменные используются для сохранения промежуточных данных в хранимых процедурах и функциях. Все переменные считаются локальными. Имя переменной должно начинаться с @.
Объявление переменных
Синтаксис в обозначениях MS SQL Server:
DECLARE @имя_переменной1 тип_переменной, @имя_переменнойN
тип_переменной
Если тип переменной предполагает указание размера, то используется следующий синтаксис для объявления переменных:
DECLARE @ имя_переменной1 тип_переменной (размер), .,
@ имя_переменнойN тип_переменной(размер)

154
Пример 1: Введите в редактор SQL запросов следующее объявление переменных
DECLARE @a INT, @b numeric(10,2)
DECLARE @str CHAR(20)
Присвоение значений переменным и вывод значений на экран
Присвоение с помощью SET - обычное присвоение, синтаксис:
SET @имя_переменной = значение
Допишите в редакторе SQL объявление переменных с вводом их значений и выводом результата
DECLARE @ a INT, @ b numeric(10,2)
SET @ a = 20
SET @ b = (@a+@a)/15
SELECT @ b --вывод на экран результата
Запустить эту конструкцию на выполнение. У вас появиться следующий результат на экране. Сохраните запрос в папку Лаб12.
Присвоение с помощью SELECT - помещение результата запроса в переменную. Если в результате выполнения запроса не будет возвращено ни одной строки, то значение переменной не меняется, т.е. остается старым.
Рассмотрим пример, в котором переменной присвоим результат выполнения агрегированной функции над таблицами базы данных
Пример 2: Вывести количество строк в таблице Студенты и присвоить это значение переменной
DECLARE @a INT
SELECT @a = COUNT(*) FROM Student
SELECT @a
Пример 3:
DECLARE @str CHAR(30)
SELECT @str = Sutname FROM Student
SELECT @str
В данном примере в переменную поместится последнее значение из результата запроса.
Сочетание ключевых слов SET и SELECT
Измените код запроса из примера 2 на следующий:

155
DECLARE @a INT
SET @a = (SELECT COUNT(*) FROM Authors)
SELECT @a
Создание временной таблицы через переменную типа TABLE
Объявляется через DECLARE с указанием в скобках столбцов таблицы, их типов, размеров, значений по умолчанию, а также индексов типа PRIMARY KEY или UNIQUE.
Пример 4: Создание временной таблицы с двумя полями, в которую будет добавлены 2 строки с данными
DECLARE @mytable TABLE (id INT, myname CHAR(20) DEFAULT
'Иванов Иван')
INSERT INTO @mytable(id) VALUES (1)
INSERT INTO @mytable(id, myname) VALUES (2,'Игорь Троцкий')
SELECT * FROM @mytable
Выполните и сохраните запрос
Пример 5: Создание временной таблицы с двумя полями, в которую будет добавлены строки, как результат выполнения запроса выборки данных из таблицы
факультет
DECLARE @mytable TABLE(id INT, myname CHAR(255) DEFAULT
'Введите название')
INSERT @mytable SELECT kod_faculteta, name_faculteta FROM facultet
SELECT * FROM @mytable
Выполните и сохраните запрос.
Самостоятельно создать на языке P-SQL запросы, с помощью которых
Запрос 1. Подсчитать среднюю зарплату преподавателей (с помощью запроса
SELECT) и умножить ее на значение 123,34, которое необходимо сохранить в отдельной переменной, вывести значение переменной на экран.
Запрос 2. Подсчитать суммарное значение всех стипендий у студентов, результат поместить в переменную, вывести значение переменной на экран.
Запрос 3. Подсчитать количество кафедр, результат поместить в переменную, вывести значение переменной на экран.
Запрос 4. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое, строка. Добавить в нее две записи с данными и вывести результат на экран.
Операторские скобки
BEGIN
/* в них нельзя помещать команды, изменяющие структуры объектов БД.
Операторские скобки должны содержать хотя бы один оператор. Требуются для конструкций поливариантных ветвлений, условных и циклических конструкций
*/
END
Условная конструкция IF
Синтаксис:
IF условие
Набор операторов1 ELSE
Набор операторов2

156
Пример 6: Использование операторских скобок и условных конструкций для
поиска ответа – количество кафедр больше 10, да или нет
DECLARE @a INT
DECLARE @str CHAR(30)
SET @a = (SELECT COUNT(*) FROM kafedra)
IF @a >10 BEGIN
SET @str = 'Количество кафедр больше 10'
SELECT @str
END ELSE
BEGIN
SET @str = 'Количество кафедр = ' + str(@a)
SELECT @str
END
Выполните и сохраните запрос.
Самостоятельно создать на языке P-SQL запросы, с помощью которых
Запрос 5. Подсчитать количество факультетов. Если их в таблице от 2 до 4, то ничего не сообщать, в противном случае вывести сообщение вида "В таблице ... факультетов" (вместо многоточия поставить точное количество факультетов).
Запрос 6. Подсчитать средний год рождения студентов. Если полученный год в диапозоне от 1980 до 1999, то ничего не сообщать, в противном случае вывести сообщение вида "Средний год рождения = ." (вместо многоточия поставить точный средний год).
Цикл WHILE
Синтаксис:
WHILE Условие
Набор
операторов1
BREAK
Набор
опреторов2
CONTINUE
Конструкции BREAK и CONTINUE являются необязательными.
Цикл можно принудительно остановить, если в его теле выполнить команду
BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.
Пример 7: Объявление переменной а, проверка в цикле, чтобы значение а не превосходило 100. Переменная а увеличивает свое значение в случайном порядке. Также организуется проверка на условие.
DECLARE @a INT
SET @a = 1
WHILE @a <100
BEGIN
PRINT @a -- вывод на экран значения переменной
IF (@a>40) AND (@a<50)
BREAK --выход и выполнение 1-й команды за циклом
ELSE

157
SET @a = @a+rand()*10
CONTINUE
END
PRINT @a
Выполните и сохраните запрос.
Самостоятельно создать на языке P-SQL запросы, с помощью которых
Запрос 7. Определить количество записей в таблице кафедра. Пока записей меньше 10, делать в цикле добавление записи в временную таблицу с автоматическим наращиванием значения ключевого поля, а вместо названия кафедры ставить значение 'Имя не известено'.
Функции и хранимые процедуры
Функции и хранимые процедуры используются в SQL Server для реализации на языке Transact-SQL сложных часто используемых алгоритмов обработки данных или различных административных действий создания учетных записей, получения информации об объектах базы данных, управления свойствами сервера и баз данных, управления подсистемой репликации и автоматизации и т.д.
Они хранятся в виде исходного текста и являются программными модулями, существующими независимо от таблиц или каких либо других объектов баз данных.
Исключением являются расширенные хранимые процедуры, которые хранятся в двоичном формате в виде динамически подключаемых библиотек типа
*.dll и создаются с помощью других языков программирования с использованием интерфейса SQL Server Open Data Services API. Такие процедуры подключаются, отключаются и выгружаются соответственно командами
sp_addextendedproc, sp_dropextendedproc и DBCC dlname (FREE), где dllno me_имя dll_библиотеки.
Хранение функций и хранимых процедур в виде исходных модулей языка Transact
– SQL на сервере и в соответствующих базах данных позволяет уменьшить размер
запроса, посылаемого по сети от клиента на сервер, а следовательно и нагрузку на
сеть, что повышает общую производительность системы. Это также позволяет
упростить сопровождение программных комплексов и внесение изменений в исходный текст модулей, причем большинство изменений не отразится на работоспособности клиентских приложений.
Значительная часть функций и хранимых процедур поставляются в составе
SQL Server. Они называются системными, или встроенными (built - in).
Кроме того, пользователю предоставляется возможность разрабатывать и включать в свою базу данных собственные, или пользовательские (user-defined)
функции и хранимые процедуры, реализующие специальные алгоритмы обработки данных.
Таким образом, пользовательские функции и хранимые процедуры становятся объектами той базы данных, в которой они создавались. Поэтому при их создании, если необходимо, требуемую базу данных следует сделать текущей с помощью команды
USE имя базы данных. Системные же функции хранятся на экземпляре сервера, а системные хранимые процедуры – в базе MASTER этого же экземпляра сервера.

158
В SQL Server можно создавать и так называемые временные хранимые
процедуры в базе данных tempdb экземпляра сервера, которые существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они бывают
локальными и глобальными.
Функции и хранимые процедуры могут быть вызваны клиентскими
программами, другими функциями или хранимыми процедурами, а также
триггерами.
В любом случае необходимо указать имя функции или хранимой процедуры и список аргументов, которые сопоставляются параметрам соответствующей функции или хранимой процедуры при этом типы аргументов и параметров должны совпадать или допускать автоматические преобразования типов. Если для некоторого параметра задано значение по умолчанию и это значение подходит для данного вызова, то соответствующий аргумент может быть опущен.
Поскольку функция возвращает значение, она используется в качестве операнда некоторого выражения в виде вызова функций, состоящего из имени этой функции и списка аргументов, заключенного в круглые скобки, при этом в качестве аргументов могут быть любые выражения языка Transact – SQL, дающие в результате значения требуемых типов.
Аргументы в вызове функции отделяются запятыми.
Если список аргументов пуст, то круглые скобки после имени функции, как правило, задаются.
Исключения составляют некоторые системные функции, для которых круглые скобки не задаются, когда нет аргументов.
Хранимые процедуры могут вызываться только командой EXECUTE, или сокращенно EXEC. За этой командой должны быть указаны имя процедуры и через пробел список аргументов, если вызывается процедура с параметрами.
Аргументы разделяются запятой. Если для параметра задано значение по умолчанию, то аргумент либо совсем не задается (в конце списка), либо используется слово
DEFAULT(в середине списка).
Процедура может возвращать результаты только через параметры с ключевым словом OUTPUT, при этом и аргумент должен быть задан с таким же ключевым словом.
Создание, изменение и удаление функций и хранимых процедур производится соответственно командами: o CREATE FUNCTION,
o CREATE PROCEDURE ,
o ALTER FUNCTION,
o ALTER PROCEDURE,
o DROP FUNCTION,
o DROP PROCEDURE.
При создании функции указывается тип возвращаемого значения и в теле функции обязательно задается команда RETURN, за которой следует выражения для вычисления возвращаемого значения.
В теле процедуры использование команды RETURN (конечно, без последующего выражения) вовсе не обязательно. Когда этой команды нет, выход из процедуры будет происходить после исполнения последней команды процедуры.

159
Тело, как функции, так и хранимой процедуры начинается ключевым словом AS.
Поскольку каждая из них храниться как отдельный объект, то для указания конца тела не требуется записывать какое-либо специальное ключевое слово или знак.
За командами создания функции или хранимой процедуры перечисляются имена параметров, начинающиеся с символа @, и их типы, а также важно значение по умолчанию.
Для функции этот список заключается в круглые скобки, после которых записывается ключевое слово RETURNS (возвращает) и тип возвращаемого значения.
Для хранимой процедуры круглые скобки не используются, и задавать тип возвращаемого значение не требуется.
Для тела функции часто используют ключевое слово begin после ключевого слова as и ключевое слово end в конце тела.
Дополнительные опции функции или хранимой процедуры задаются ключевым словом with до начала тела.
В SQL Server 2003 можно создавать