Лабораторный практикум. Лабораторная работа Обследование предметной области Построение модели ide создание контекстной диаграммы Создание диаграммы декомпозиции Создание диаграммы

Название	Лабораторная работа Обследование предметной области Построение модели ide создание контекстной диаграммы Создание диаграммы декомпозиции Создание диаграммы
Анкор	Лабораторный практикум
Дата	25.12.2022
Размер	5.73 Mb.
Формат файла
Имя файла	Лабораторный практикум.pdf
Тип	Практикум #863633
страница	7 из 12

1 2 3 4 5 6 7 8 9 ... 12

5.2 Получение данных из определенных столбцов таблицы «Titles»
1. На панели в окне введите следующий код Transact-
SQL:
USE Pubs
SELECT Title_id, Title, Price, Ytd_sales
FROM Titles
Здесь оператор извлечет лишь те строки, значение поля которых превышает $10.
2. Исполните оператор Transact-SQL.
Результирующий набор выводится на вкладке панели
(рисунок 3).
Рисунок 3 – Получение данных из определенных свойств таблицы «Titles», удовлетворяющих заданному условию

91
5.4 Задание порядка, в котором выводится результирующий набор
1. На панели в окне введите следующий код Transact-
SQL:
USE Pubs
SELECT Title_id, Title, Price, Ytd_sales
FROM Titles
WHERE Price > 10
ORDER BY Price DESC, Title
Результирующий набор, который вернет этот оператор , группируются строки с одними и теми же значениями поля <Туре>. Строки, не соответствующие условиям конструкции , исключаются до начала любых операций по группировке. При группировке выполняется усреднение значений столбца
, а полученное среднее значение вставляется в результирующий набор в виде столбца . Значения столбца
упорядочиваются по убыванию.
2. Исполните оператор Transact-SQL.
Результирующий набор выводится на вкладке панели
(рисунок 5).
Рисунок 5 – Использование группировки данных и агрегатных функций

93
5.6 Создание таблицы для размещения результирующего набора
1. На панели в окне введите следующий код Transact-
SQL:
USE Pubs
SELECT Type, AVG(Price) AS AvgPrice
INTO TypeAvgPrice
FROM Titles
WHERE Price > 10
GROUP BY Type
ORDER BY AvgPrice DESC
Оператор , который позволит просмотреть все данные из таблицы «Test1».
4. Исполните оператор для просмотра данных таблицы
«Test1». Обратите внимание, что значения поля сгенерированы автоматически, а в поле каждой строки содержится пустое значение.

98
5.10 Модификация данных с помощью оператора
1. Просмотрите данные таблицы «Test1».
Если на панели остались результаты предыдущего запроса, то можно воспользоваться ими. В противном случае используйте для просмотра содержимого таблицы оператор для просмотра данных таблицы
«Test1». Обратите внимание на удвоенное значение поля для каждой книги типа «business».
5.11 Удаление данных из таблицы с помощью оператора
1. Введите на панели в окне следующий код на SQL>:
DELETE Test1
WHERE Title = 'Test Title'
Этот оператор удаляет все строки из таблицы, в столбце которой указано значение «Test Title». 2. Исполните оператор Transact-SQL. На вкладке <Messages> панели <Results> выводится сообщение, где указано число строк, на которые повлияло исполнение оператора (рисунок 13). Рисунок 13 – Удаление данных из таблицы «Test1» 3. Воспользуйтесь оператором <SELECT> для просмотра данных таблицы «Test1». Обратите внимание, что строка <Test Title> удалена из таблицы. 4. Введите на панели <Editor> в окне <Query> следующий код на Transact- SQL: DELETE Test1 </div> <div> 100 Этот оператор удаляет все строки таблицы «Test1». 5. Исполните оператор Transact-SQL. На вкладке <Messages> панели <Results> выводится сообщение, где указано число строк, на которые повлияло исполнение оператора (рисунок 14). Рисунок 14 – Удаление всех строк таблицы «Test1» 6. Воспользуйтесь оператором <SELECT> для просмотра данных таблицы «Test1». Обратите внимание на отсутствие данных в таблице «Test1». 5.12 Удаление таблицы с помощью оператора <DROP TABLE> 1. Введите на панели <Editor> в окне <Query> следующий код на Transact- SQL: DROP TABLE Test1 Этот оператора удаляет таблицу «Test1» из базы данных 2. Исполните оператор Transact-SQL. На вкладке <Messages> панели <Results> выводится сообщение об успешном завершении команды (рисунок 15). </div> <div> 101 Рисунок 15 – Удаление таблицы «Test1» 3. С помощью окна <Object Browser> удостоверьтесь, что таблица «Test1» удалена из базы данных. 4. Закройте <SQL Query Analyzer>. 5.13 Контрольное задание 1. Средствами SQL Server 2000 создать четыре таблицы в базе данных . При создании таблиц выполнить следующие условия: − поля номер_поставщика, номер_детали, номер_изделия во всех таблицах имеет символьный тип (char) и длину 6; − поля рейтинг, вес и количество имеют целочисленный тип (int); − поля фамилия, город (поставщика, детали или изделия), название (детали или изделия) имеют символьный тип nchar и длину 20; − для всех полей допускаются значения NULL и значения-дубликаты, кроме поля номер_поставщика из таблицы «S», номер детали из таблицы «P», номер изделия из таблицы «J». Таблица 1 – Таблица поставщиков «S» Номер поставщика Фамилия Рейтинг Город S1 Смит 20 Лондон S2 Джонс 10 Париж S3 Блейк 30 Париж S4 Кларк 20 Лондон S5 Адамс 30 Афины </div> <div> 102 Таблица 2 – Таблица деталей «P» Номер детали Название Цвет Вес Город P1 Гайка Красный 12 Лондон P2 Болт Зеленый 17 Париж P3 Винт Голубой 17 Рим P4 Винт Красный 14 Лондон P5 Кулачок Голубой 12 Париж P6 Блюм Красный 19 Лондон Таблица 3 – Таблица изделий «J» Номер изделия Название Город J1 Жесткий диск Париж J2 Перфоратор Рим J3 Считыватель Афины J4 Принтер Афины J5 Флоппи-диск Лондон J6 Терминал Осло J7 Лента Лондон Таблица 4 – Таблица поставок «SPJ» Номер поставщика Номер детали Номер изделия Количество S1 P1 J1 200 S1 P1 J4 700 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J3 200 S2 P3 J4 500 S2 P3 J5 600 S2 P3 J6 400 S2 P3 J7 800 S2 P5 J2 100 S3 P3 J1 200 S3 P4 J2 500 S4 P6 J3 300 S4 P6 J7 300 S5 P2 J2 200 S5 P2 J4 100 S5 P5 J5 500 S5 P5 J7 100 S5 P6 J2 200 S5 P1 J4 100 S5 P3 J4 200 S5 P4 J4 800 S5 P5 J4 400 S5 P6 J4 500 </div> <div> 103 2. Убедиться в успешности выполненных действий. При необходимости исправить ошибки. 3. Используя Query Analizer, выполнить модификацию структуры таблиц «S» и «SPJ», добавив в «SPJ» поле с датой поставки. Убедиться в успешности выполненных действий. При необходимости исправить ошибки. 4. Проверить результат заполнения таблиц, написав и выполнив простейший запрос select * from имя_таблицы 5. Связать все эти таблицы и обеспечить целостность данных. 6. Согласно выданному преподавателем варианту выполнить задания, описанные ниже. 5.14 Варианты заданий на составление запросов по выборке информации из таблиц базы данных Вариант 1 1. Выдать общее количество деталей «P1», поставляемых поставщиком «S1». 2. Получить все пары названий городов, таких, что какой-либо поставщик из первого города поставляет детали для некоторого изделия, изготовляемого во втором городе. 3. Выдать номера изделий, использующих только детали, поставляемые поставщиком «S1». 4. Получить номера деталей, поставляемых каким-либо поставщиком из Лондона, для изделия, изготавливаемого также в Лондоне. Вариант 2 1. Выдать номера и фамилии поставщиков, поставляющих по крайней мере одну деталь, поставляемую по крайней мере одним поставщиком, который поставляет по крайней мере одну красную деталь. </div> <div> 104 2. Получить полный список деталей для всех изделий, изготавливаемых в Лондоне. 3. <a href="/poperechnie-dvijeniya-rejushej-kromki-umeneshayut/index.html" title="Поперечные движения режущей кромки уменьшают">Выдать номера деталей</a>, поставляемых каким-либо поставщиком из Лондона. 4. Получить номера деталей, поставляемых для всех изделий из Лондона. Вариант 3 1. Выдать номера изделий, для которых детали поставляются по крайней мере одним поставщиком не из того же самого города. 2. Получить список всех поставок, в которых количество деталей находится в диапазоне от 300 до 750 включительно. 3. Выдать номера изделий, использующих по крайней мере одну деталь, поставляемую поставщиком «S1». 4. Получить номера и названия деталей, поставляемых для какого-либо изделия в Лондоне. Вариант 4 1. Выдать номера и названия изделий, для которых город является первым в алфавитном списке таких городов. 2. Получить цвета деталей, поставляемых поставщиком «S1». 3. Выдать номера и фамилии поставщиков, поставляющих деталь «Р1» для какого-либо изделия в количестве, большем среднего объема поставок детали «Р1» для этого изделия. 4. Получить полный список деталей для всех изделий. Вариант 5 1. Выдать названия изделий, для которых поставляются детали поставщиком «S1». 2. Получить номера деталей, поставляемых для какоголибо изделия поставщиком, находящимся в том же городе, где изготавливается это изделие. </div> <div> 105 3. Выдать номера и названия изделий, для которых поставщик «S1» поставляет несколько деталей каждого из поставляемых им типов. 4. Получить номера изделий, для которых средний объем поставки деталей «P1» больше наибольшего объема поставки любой детали для изделия «J1». 5.15 Варианты заданий на составление запросов по модификации информации из таблиц базы данных Вариант 1 1. Увеличить на 10 рейтинг всех поставщиков, рейтинг которых в настоящее время меньше, чем рейтинг поставщика «S4». 2. Постройте таблицу, содержащую список номеров изделий, которые либо находятся в Лондоне, либо для них поставляются детали каким-нибудь поставщиком из Лондона. Вариант 2 1. Удалить все изделия, для которых нет поставок деталей. 2. Построить таблицу с номерами поставщиков и парами номеров деталей, таких, что некоторый поставщик поставляет обе указанные детали. Вариант 3 1. Увеличить размер поставки на 10 процентов для всех поставок тех поставщиков, которые поставляют какую-либо красную деталь. 2. Построить таблицу с комбинациями «цвет деталигород, где хранится деталь», исключая дубликаты пар (цветгород). Вариант 4 1. Построить таблицу, содержащую список номеров деталей, которые поставляются либо каким-нибудь поставщиком из Лондона, либо для какого- либо изделия в Лондон. </div> <div> 106 2. Вставить в таблицу «S» нового поставщика с номером «S10» с фамилией Уайт из города Нью-Йорк с неизвестным рейтингом. Вариант 5 1. Удалить все изделия из Рима и все соответствующие поставки. 2. Построить таблицу с упорядоченным списком всех городов, в которых размещаются по крайней мере один поставщик, деталь или изделие. Вариант 6 1. Изменить цвет красных деталей с весом менее 15 фунтов на желтый. 2. Построить таблицу с номерами изделий и городов, где они изготавливаются, такие, что второй буквой названия города является «О». Вариант 7 1. Увеличить на 10 рейтинг тех поставщиков, объем поставки которых выше среднего. 2. Построить таблицу с упорядоченным списком номеров и фамилиями поставщиков, поставляющих детали для изделия с номером «J1». Осуществить выборку и модификацию данных согласно выбранному вами варианту базы данных. 5.16 Контрольные вопросы 1. Каким образом выполнить простейшие операции вставки строк данных в таблицу средствами T-SQL? 2. Как осуществить простейшие операции модификации строк таблицы средствами T-SQL? 3. Как выполнить просмотр таблицы? 4. Как сохранить результаты запроса в таблице? </div> <div> 107 6 Лабораторная работа № 6. Управление и манипулирование данными Цель работы: выработка умений и навыков работы с операторами <INSERT>, <update>, <DELETE> и простейшими формами оператора <SELECT>. Используемое программное обеспечение: Microsoft SQL Server 2017. Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать «язык управления доступом». Он состоит из двух основных команд: − <GRANT> – предоставить привилегии пользователю или приложению на манипулирование объектами. − <REVOKE> – отменить привилегии пользователя или приложения (забрать права). Операторы манипулирования данными: − <SELECT> – отобрать строки из таблиц. − <INSERT> – добавить строки в таблицу. − <update> – изменить строки в таблице. − <DELETE> – удалить строки в таблице. 6.1 Управление данными. Предоставление права доступа к объекту базы данных с помощью <Query Analizer> 1. На панели <Editor> введите следующий оператор <GRANT>: GRANT UPDATE, SELECT, INSERT, DELETE ON Поставщик TO PUBLIC 2. Выделите оператор <GRANT> и выполните его. </div> <div> 108 На вкладке <Messages> панели <Results> выводится сообщение о том, что команда успешно завершена (рисунок 1). Рисунок 1 – Предоставление права доступа к таблице «Поставщик» 3. Введите следующий оператор <EXEC>: EXEC sp_helprotect Поставщик 4. Исполните оператор <EXEC>. На вкладке <Grids> панели <Results> отображаются данные о разрешениях пользователя для таблицы «Поставщик». Роли предоставлено право доступа <SELECT>, <update>, <INSERT>, <DELETE> для этой таблицы (рисунок 2). </div> <div> 109 Рисунок 2 – Отображение данных о разрешениях пользователя для таблицы «Поставщик» с помощью <Query Analizer> 6.2 Отзыв права доступа к объекту базы данных 1. Введите следующий оператор <REVOKE>: REVOKE UPDATE, SELECT, INSERT, DELETE ON Поставщик TO PUBLIC 2. Выделите оператор <REVOKE> и выполните его. На вкладке <Messages> панели <Results> выводится сообщение, свидетельствующее об успешном завершении команды (рисунок 3). </div> <div> 110 Рисунок 3 – Отзыв права доступа к таблице «Поставщик» 3. Введите следующий оператор <EXEC>: EXEC sp_helprotect Поставщик 4. Исполните оператор <EXEC>. На вкладке <Messages> панели <Results> выводится сообщение об отсутствии строк, совпадающих с критерием запроса. Поскольку у роли права доступа <SELECT> отозваны, теперь нет и предоставленных или отобранных прав доступа, о которых сообщает хранимая процедура (рисунок 4). Рисунок 4 – Сообщение об отсутствии прав доступа на выборку данных из таблицы «Поставщик» </div> <div> 111 6.3 Предоставление права доступа к объекту базы данных с помощью <Enterprise Manager> 1. Запустите <Enterprise Manager>. 2. В базе данных <Northwind> выберете таблицу «Поставщик» и щелкните по ней 2 раза. 3. В появившимся окне <Свойства таблицы> нажмите на кнопку 4. Для роли разрешите выборку и вставку, запретите изменение и удаление (рисунок 5). Рисунок 5 – Предоставление права доступа к таблице «Поставщик» с помощью <Enterprise Manager> </div> <div> 112 5. Выбрав роль и нажав на кнопку <columns>, можно выбрать для каких столбцов будет разрешена или запрещена выборка и изменение. 6. Разрешите роли изменение и удаление. 6.4 Манипулирование данными. Обновление данных 1. Запустите <Query Analizer>. 2. Введите следующий оператор <update> и выполните его: USE Northwind UPDATE Поставщик SET Тел_поставщика = 89246541252 WHERE Код_поставщика = 03 На вкладке <Messages> появляется сообщение о том, что исполнение оператора повлияло на одну строку (рисунок 6). Рисунок 6 – Обновление данных в таблице «Поставщик» 6.5 Выборка данных Исполните следующий оператор <SELECT>: SELECT * FROM Поставщик На панели <Grids> отображаются три строки из таблицы «Поставщик». Обратите внимание, что значение поля <Тел_поставщика> для Захарова М. В. теперь равно 89246541252 (рисунок 7). </div> <div> 113 Рисунок 7 – Извлечение всех данных из таблицы «Поставщик» 6.6 Удаление данных Наберите следующий оператор <DELETE> и исполните его: DELETE FROM Поставщик WHERE Код_поставщика = 02 На вкладке <Messages> выводится сообщение о том, что исполнение оператора повлиял на одну строку (рисунок 8). Рисунок 8 – Удаление из таблицы «Поставщик» записей, у которых <Код_поставщика> = 02 Исполните следующий оператор <SELECT>: </div> <div> 114 SELECT * FROM Поставщик Теперь на панели <Grids> осталось только две строки из таблицы «Поставщик». Обратите внимание на отсутствие в списке имени Иванов С. П. (рисунок 9). Рисунок 9 – Просмотр изменений в таблице «Поставщик» 6.7 Удаление таблицы Введите следующий оператор <DROP> и исполните его: DROP TABLE Поставщик На вкладке <Messages> появляется сообщение об ошибке. Обеспечение целостности данных, созданное в лабораторной работе № 4, не позволяет удалить эту таблицу. Таблицу «Поставщик» возможно удалить, если только перед этим удалить ее связь с таблицей «Товар». Закройте <SQL Query Analyzer> 6.8 Контрольное задание Назначьте права доступа к данным в базе данных согласно выбранному вами варианту. </div> <div> 115 6.9 Контрольные вопросы 1. Какие операторы управления Вы знаете? 2. Перечислите операторы манипулирования данными. 3. Опишите синтаксис операторов <INSERT>, <update>, <DELETE>. </div> <div> 116 7 Лабораторная работа № 7. Управление и манипулирование данными Цель работы: используя операторы T-SQL, научиться создавать хранимые процедуры и управлять ими. Используемое программное обеспечение: Microsoft SQL Server 2017. Хранимая процедура – компилируемый объект базы данных, хранящийся на сервере и представляющий собой набор SQL-инструкций. Хранимые процедуры могут обладать: 1) входными и выходными параметрами; 2) локальными переменными; 3) циклами и ветвлениями, то есть в них могут использоваться инструкции управления потоком; 4) в них могут производиться: − стандартные операции с базами данных (как DDL, так и DML); − числовые вычисления; − операции над символьными данными. 5) результаты которых могут присваиваться переменным и параметрам. С точки зрения приложений, работающих с базами данных, хранимые процедуры – это подпрограммы, которые выполнятся на сервере. По отношению к базе данных, хранимые процедуры – это объекты, которые создаются и хранятся в ней. Они могут быть вызваны из клиентских приложений. При этом одна процедура может быть использована в любом количестве клиентских приложений. 7.1 Создание и вызов процедуры. Создание процедуры CREATE PROC[EDURE] имя_процедуры [; номер] [@параметр тип_данных [VARYING] [= значение] [OUTPUT] [,…] </div> <div> 117 [WITH {RECOMPILE I ENCRYPTION|RECOMPILE|ENCRYPTION}] [FOR REPLICATION] AS команды SQL [RETURN [код_статуса]] Имя_процедуры подчиняется стандартным правилам выбора имен объектов: − в рамках одной базы данных для процедур с одинаковыми именем и владельцем номер должен быть уникальным; − имена локальных временных процедур должны начинаться с символа #; − имена глобальных временных процедур должны начинаться с символов ##. Номер используется для группировки процедур как единого целого (часто в сочетании с версией приложения). @параметр – представляет параметр, передаваемый и / или получаемый из процедуры. Процедура имеет до 1024 параметров. Параметры могут принимать значения <NULL>. Они не могут использоваться в процедуре вместо объектов баз данных, если только они не включаются в команду <EXEC>. Параметры относятся к встроенным либо пользовательским типам данных. Обратите внимание, правила, значения по умолчанию и свойства столбцов не относятся к параметрам, определяемым пользовательскими типами данных. Параметр может относиться к любому типу данных SQL Server, в том числе <text>, <image> или <cursor>. Для параметров типа <cursor> ключевые слова varying и output являются обязательными. VARYING означает, что параметр возвращает итоговый набор. Используется только для типа данных <CURSOR>. Значение определяет значение параметра по умолчанию. Может быть любой константой или <NULL>. </div> <div> 118 OUTPUT означает, что параметр является выходным, то есть может возвращаться стороне, вызвавшей процедуру. Параметры типа <CURSOR> обязаны быть выходными. ENCRYPTION означает, что таблица <syscomment> должна шифроваться (благодаря чему текст процедуры скрывается от постороннего взгляда). FOR REPLICATION означает, что процедура участвует в процессе публикации. Обратите внимание, параметры <FOR REPLICATION> и <RECOMPILE> являются взаимоисключающими. Команды SQL – произвольное количество команд SQL, составляющих тело процедуры. Максимальный объем команд не должен превышать 128 Кбайт. 7.2 Вызов процедуры [EXEC[UTE]] [@код_возврата = ] имя_процедуры [;номер] [@список_параметров] [WITH RECOMPILE] @код_возврата – переменная для сохранения кода возврата процедуры. Номер – числовой идентификатор процедуры. При отсутствии этого аргумента выполняется процедура с максимальным номером версии. @список параметров – список параметров процедуры. Параметры передаются как по имени (имя = значение), так и по позиции в списке, но если некоторый параметр передается по имени, все последующие параметры в списке тоже должны передаваться по имени. Чтобы пропустить параметр в середине списка, следует передавать остальные параметры по имени. WITH RECOMPILE – процедура заново компилируется при выполнении. Удаление процедуры: DROP PROC имя_процедуры [, имя_процедуры[, ...]] </div> <div> 119 7.3 Системные хранимые процедуры. Просмотр системных хранимых процедур в базе данных <Master> 1. Откройте <Query Analyzer> и подключитесь к локальному серверу. 2. Откройте окно <Object Browser>, если оно еще не открыто. В окне <Object Browser> выводится иерархическое дерево объектов базы данных. 3. Раскройте узел <Master>. Появляется список типов объектов. Обратите внимание на узлы <Stored Procedures> и <Extended Procedures>. 4. Раскройте узел <Stored Procedures>. Появляется список хранимых процедур и расширенных хранимых процедур базы данных <Master>. 5. Изучите имена процедур из списка. Обратите внимание, что владельцем всех процедур является <dbo>. 6. Раскройте системную хранимую процедуру dbo.sp_who. Появляются узлы и <Dependencies>. 7. Раскройте узел (рисунок 1). Рисунок 1 – Просмотр параметров системной хранимой процедуры «dbo.sp_who» Обратите внимание, что для этой процедуры определены два параметра: <@RETURN_VALUE> и <@loginame. @RETURN_VALUE> – это встроенный </div> <div> 120 параметр, который используется для хранения кодов возврата, он есть у всех хранимых процедур, <@loginame> – это входной параметр. 8. Зависимость <Dependencies>. Обратите внимание, что эта хранимая процедура зависит только от таблицы «dbo.sys-processes». Это системная таблица, которая хранится в базе данных <Master>. Системная хранимая процедура «sp_who» выполняет запрос к таблице «SysProcesses» и выводит часть ее данных в своем результирующем наборе. Эту таблицу можно найти, раскрыв узел <System Tables>. 9. Если вы уже просмотрели таблицы «dbo.sysprocesses», вернитесь к системной хранимой процедуре «sp_who» в окне <Object Browser>. 10. Прокрутите содержимое <Object Browser>, пока не увидите узел <Extended Procedures>. Обратите внимание на три хранимые процедуры с префиксом <хр_>, которые расположены непосредственно над узлом <Extended Procedures>. Они не являются расширенными. 11. Раскройте узел <Extended Procedures>. Прокручивая список объектов этого узла, обратите внимание на наличие в нем как системных, так и расширенных хранимых процедур. Большинство процедур из этого узла являются расширенными (рисунок 2). </div> <div> 121 Рисунок 2 – Просмотр хранимых процедур узла Extended Procedures </div> <div> 122 12. Оставьте <Query Analyzer> открытым. 7.4 Методы просмотра содержимого хранимой процедуры 1. Щелкните правой кнопкой мыши по объекту «dbo.sp_who». Появляется контекстное меню этого объекта. 2. Выберите <Scripting Options> (рисунок 3). Рисунок 3 – Просмотр содержимого хранимой процедуры (1-й способ) Выводится диалоговое окно <Options>. 3. Установите флажок <Include Descriptive Headers In The Script> и щелкните <ОК> (рисунок 4). </div> <div> 123 Рисунок 4 – Установление флажка <Include Descriptive Headers In The Script> Этот параметр выводит около заголовка текста хранимой процедуры ее имя и дату создания. 4. Щелкните правой кнопкой мыши по объекту «dbo.sp_who». Выводится контекстное меню для него. 5. Укажите и щелкните <Create> (рисунок 5). </div> <div> 124 Рисунок 5 – Просмотр содержимого хранимой процедуры (2-й способ) В окне <Query> появляется хранимая процедура «dbo.sp_who». Обратите внимание, что рядом с заголовком файла появляются ключевые слова <CREATE PROCEDURE> (рисунок 6). Рисунок 6 – Листинг хранимой процедуры <sp_who> 6. Щелкните на панели инструментов кнопку <New Query> или нажмите <CTRL+N>. На правой панели появляется окно нового запроса. 7. На панели <Editor> в окне <Query> введите следующий код Transact- SQL: sp_helptext [master.dbo.sp_who] </div> <div> 125 В этой команде для вывода содержимого системной хранимой процедуры «sp_who», которая содержится в базе данных <Master>, используется системная хранимая процедура «sp_helptext». Хотя указывать полное имя хранимой процедуры не обязательно, лучше это все-таки делать – в этом случае вы гарантированно откроете именно тот объект, который вам нужен. 8. Исполните команду. Содержимое системной хранимой процедуры «sp_who» выводится на вкладке <Grids> панели <Results> (рисунок 7). Рисунок 7 – Содержимое системной хранимой процедуры <sp_who> </div> <div> 126 7.5 Создание и исполнение хранимой процедуры в базе данных <Northwind> 1. Откройте <Query Analyzer> и подключитесь к локальному серверу. 2. Закройте окно <Object Browser>, если оно открыто. 3. Раскройте окно <Query>, чтобы оно заняло все рабочее пространство в <Query Analyzer>. 4. На панели <Editor> в окне <Query> введите следующий код: USE Northwind GO CREATE PROCEDURE dbo.CustOrderHistRep @CustomerID char(5) AS SELECT ContactName, ContactTitle FROM Customers WHERE CustomerID = @CustomerID SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers С WHERE С.CustomerID = @CustomerID AND С.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName GO При исполнении первого пакета база данных <Northwind> становится текущей. Далее создается процедура <CustOrderHistRep> и определяется единственный входной параметр – <@CustomerID>. Входной параметр может располагаться в одной строке с оператором <CREATE PROCEDURE>, но здесь для ясности он перенесен в отдельную строку. Аналогичный подход использован далее при разбиении по строкам кода операторов <SELECT>. Обратите внимание, что для параметра <@CustomerID> задан тип данных <char(5)>. Если выполнить запрос к таблице «[Northwind].[dbo].[Customers]», то видно, что длина </div> <div> 127 всех идентификаторов покупателей равна пяти символам. Строка, в которой находится единственное ключевое слово <as>, является разделительной линией между созданием процедуры в таблице «SysObjects» и текстом процедуры, сохраняемым в таблице «SysComments». 5. Просмотрите операторы <SELECT>, которые расположены ниже ключевого слова <as>. При исполнении запроса в ответ на ввод идентификатора покупателя первый оператор <SELECT> выводит имя контактного лица и заголовок контактной информации. Второй оператор <SELECT> выводит названия и общее количество (<SUM>) каждого товара, который приобрел покупатель. Результирующий набор возвращает данные, сгруппированные по названию товара. Можно заметить, что несколько соединений реализовано в конструкции <WHERE>, а не <FROM>. Во время модификации процедуры мы переместим выражения <JOIN> в конструкцию <FROM> (рисунок 8). Рисунок 8 – Создание хранимой процедуры <CustOrderHistRep> </div> <div> 128 7.6 Просмотр хранимых процедур в <Query Analyzer> 1. Откройте <Object Browser> в <Query Analyzer> и выберите базу данных <Northwind>. 2. Раскройте узел <Stored Procedures>. Появляются все хранимые процедуры, созданные в базе данных <Northwind>. 1. Раскройте хранимую процедуру «dbo.CustOrderHistRep». Обратите внимание, что эта хранимая процедура принадлежит <dbo> и что в «dbo.CustOrderHistRep» появляются два узла: и <Dependencies>. 2. Раскройте узел Обратите внимание на наличие у этой хранимой процедуры двух параметров: созданного вами <@CustomerID> и встроенного параметра для хранения кода возврата <@RETURN_VALUE>. 3. Раскройте узел <Dependencies>. Обратите внимание, что хранимая процедура зависит от четырех объектов (таблиц «Orders», «Products», «Order Details» и «Customers» из базы данных <Northwind>). От самой хранимой процедуры не зависит ни один объект (рисунок 9). </div> <div> 129 Рисунок 9 – Отображение содержимого узлов и <Dependencies> хранимой процедуры «CustOrderHistRep» 1. Переключитесь на панель <Editor> окна <Query>. 2. Введите в пустой строке в нижней части панели <Editor> и исполните следующую команду: sp_depends custorderhistrep Хранимая процедура «sp_depends» проверяет процедуру «CustOrderHistRep», чтобы определить ее зависимости. Обратите внимание на наличие в поле <Name> повторяющихся элементов. Однако все записи с повторяющимися элементами отличаются значениями других полей (рисунок 10). Рисунок 10 – Проверка процедуры «CustOrderHistRep» для определения ее зависимости </div> <div> 130 3. Не закрывайте <Query Analyzer>, он потребуется для выполнения следующего задания. 7.7 Исполнение хранимой процедуры 1. В нижней части панели <Editor> введите в пустой строке и исполните следующую команду: EXEC [Northwind]. [dbo]. [custorderhistrep] @CustomerID = 'thecr' Для запуска хранимой процедуры «CustOrderHistRep» использована сокращенная версия ключевого слова <EXECUTE> – <EXEC>. Обратите внимание на использование полного имени. Это не обязательно, но в данном случае такой прием позволяет запустить процедуру, не делая активной базу данных <Northwind>. Возвращаются два результирующих набора. Первый из них (имя контактного лица и заголовок контактной информа ции) выводится в верхней части панели <Results>. Второй результирующий набор (название и количество товара) отображается в нижней части панели <Results> (рисунок 11). Рисунок 11 – Вызов процедуры «CustOrderHistRep» с указанием значения параметра 2. В предыдущем примере для запуска хранимой процедуры было задано ключевое слово <EXEC>. Обязательно ли его использовать? </div> <div> 131 3. Найдите в <Object Browser> хранимую процедуру «dbo.CustOrderHistRep» и щелкните по ней правой кнопкой мыши. 4. Выберите из контекстного меню <Script Object to New Windows As> и щелкните <Execute>. <Query Analyzer> загружает в редактор <Query Window> новую страницу, появляется оператор <EXECUTE> для процедуры «dbo.CustOrderHistRep». Обратите внимание на объявление двух переменных: <@RC> и <@CustomerID>. Первая переменная используется для хранения любых кодов возврата, являющихся частью процедуры, а вторая хранит значение входного параметра <@CustomerID>. Обратите внимание, что в операторе <EXEC> переменная <@RC> соответствует хранимой процедуре. 5. На панели <Editor> щелкните мышью в конце оператора <EXEC>, чтобы после слова <@CustomerID> появился мигающий курсор. Допишите к концу строки следующее: = 'thecr'. Теперь оператор <EXEC> должен принять следующий вид: EXEC @RC = [Northwind].[dbo].[CustOrderHistRep] @CustomerID = 'thecr' 6. Исполните запрос. 7. Закройте новую страницу, созданную в окне <Query>, но не закрывайте <Query Analyzer> и исходную страницу, которая появилась на панели <Editor>. 8. Выводится сообщение <Query Analyzer> с запросом на сохранение внесенных изменений. 9. Щелкните <No>. На панели <Editor> появляется исходная страница. </div> <div> 132 7.8 Модификация и удаление хранимой процедуры. Модификация хранимой процедуры 1. На панели <Editor> в окне <Query> введите следующий код: USE Northwind GO CREATE PROCEDURE dbo.CustOrderHistRep @CustomerID char(5) AS SELECT ContactName, ContactTitle FROM Customers WHERE CustomerID = @CustomerID SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers С WHERE С.CustomerID = @CustomerID AND С.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName GO 2. Замените ключевое слово <CREATE> на <alter>. При изменении текста процедуры ключевое слово <alter> позволяет изменять хранимую процедуру без потери любых ее свойств (рисунок 12). Рисунок 12 – Изменение процедуры «CustOrderHistRep» </div> <div> 133 3. Необходимо отсортировать число заказов по убыванию. Для этого ниже оператора <GROUP BY ProductName> введите следующий оператор: ORDER BY Total DESC 4. Также решено переместить соединения таблиц из конструкции <WHERE> в конструкцию <FROM>. В окончательном виде программа выглядит примерно так: USE Northwind GO ALTER PROCEDURE dbo.CustOrderHistRep @CustomerID char(5) AS SELECT ContactName, ContactTitle FROM Customers WHERE CustomerID = @CustomerID SELECT ProductName, Total=SUM(Quantity) FROM Products P INNER JOIN [Order Details] OD ON P.ProductID = OD.ProductID JOIN Orders O ON OD.OrderID = O.OrderID JOIN Customers С ON O. CustomerID = С.CustomerID WHERE С.CustomerID = @CustomerID GROUP BY ProductName ORDER BY Total DESC GO 5. Исполните запрос (рисунок 13). </div> <div> 134 Рисунок 13 – Внесение изменений в процедуру «CustOrderHistRep» 6. <a href="/torjestvennaya-linejka-posvyashennaya-dnyu-uchitelya-zvuchit-k/index.html" title="Торжественная линейка, посвященная Дню учителя. Звучит красивая музыка, выходят ведущие. Ведущий 1">Чтобы убедиться</a>, что нужные изменения внесены, перейдите в нижнюю часть панели <Editor> нажмите <ENTER>, затем введите и исполните следующий оператор: sp_helptext custorderhistrep На вкладке <Grids> панели <Results> появляется текст хранимой процедуры (рисунок 14). </div> <div> 135 Рисунок 14 – Просмотр текста хранимой процедуры «CustOrderHistRep» 7. Оставьте <Query Analyzer> открытым. 7.9 Удаление хранимой процедуры 1. Перейдите в нижнюю часть панели <Editor> нажмите <ENTER>, затем введите и исполните следующий оператор: DROP PROCEDURE dbo.custorderhistrep Хранимая процедура удаляется из базы данных <Northwind>. 2. Проверьте с помощью <Object Browser> в <Query Analyzer> или <Enterprise Manager>, удалена ли хранимая процедура. 3. Закройте <Query Analyzer>. Выводится запрос на сохранение сценария. 4. Если вы хотите сохранить сценарий, нажмите <Yes>, в противном случае – <No>. </div> <div> 136 7.10 Контрольное задание Создайте не менее трех хранимых процедур в базе данных согласно выбранному вами варианту. 7.11 Контрольные вопросы 1. Что такое хранимая процедура? 2. Чем могут обладать хранимые процедуры? 3. Опишите методы просмотра содержимого хранимой процедуры. 4. Как создать хранимую процедуру? 5. Каким образом можно модифицировать хранимую процедуру? 6. Как осуществить просмотр хранимой процедуры? 7. Как удалить хранимую процедуру? </div> <div> 137 8 Лабораторная работа № 8. Триггеры Цель работы: используя операторы T-SQL, научиться создавать, просматривать, модифицировать и удалять триггеры. Используемое программное обеспечение: Microsoft SQL Server 2017. Триггеры – это специальный вид хранимой процедуры, которую SQL Server вызывает при выполнении операций модификации соответствующих таблиц. Триггер автоматически активизируется при выполнении операции, с которой он связан. Триггеры связываются с одной или несколькими операциями модификации над одной таблицей. 8.1 Создание триггера CREATE TRIGGER <имя_триггера> ON <имя_таблицы> FOR {[INSERT] [, UPDATE] [, DELETE]} [WITH ENCRIPTING] AS SQL-операторы (тело триггера) Ограничения: − Нельзя использовать в теле триггера операции создания объектов базы данных (новую базу, таблицу, индекс, хранимую процедуру, триггер, представление). − Нельзя использовать в триггере команду удаления объектов <DROP>. − Нельзя использовать в теле триггера команды изменения базовых объектов <alter>. − Нельзя изменять права доступа к объектам базы данных. − Триггер не может возвращать никаких значений. </div> <div> 138 8.2 Пример создания триггера Создадим триггер, который срабатывает при удалении экземпляра некоторой книги, например, в случае утери этой книги читателем. При этом триггер проверяет: если остался еще хоть один экземпляр данной книги в библиотеке, и если это был последний экземпляр книги в библиотеке – удаляет описание данной книги из предметного каталога. CREATE TRIGGER DEL_EXEMP ON dbo.examplar FOR DELETE AS DECLARE @Ntek int DECLARE @DEL_EX varchar(12) Begin Select @DEL_EX = ISBN From deleted Exec @Ntek=COUNT_EX @DEL_EX If @Ntek = 0 delete from BOOKS WHERE BOOKS.ISBN=@DEL_EX END 8.3 Создание простых триггеров для таблицы «Товар» из базы данных <Northwind> 1. Откройте <Query Analyzer> и подключитесь к локальному серверу. 2. На панели Editor в окне <Query> введите и исполните следующий код: USE Northwind GO CREATE TRIGGER dbo.insertindicator ON dbo.Товар AFTER INSERT AS PRINT 'The insert trigger fired.' </div> <div> 139 Оператор <CREATE TRIGGER> создает триггер «Insertindicator» и привязывает его к таблице «Товар» в базе данных <Northwind>. Этот триггер срабатывает при добавлении данных в таблицу «Товар» и выводит на панели <Grids> вкладки <Results> сообщение. 3. На панели <Editor> в окне <Query> введите и исполните следующий код: CREATE TRIGGER dbo.updateindicator ON dbo.Товар AFTER UPDATE AS PRINT 'The update trigger fired.' GO CREATE TRIGGER dbo.deleteindicator ON dbo.Товар AFTER DELETE AS IF @@ROWCOUNT <> 0 PRINT 'The delete trigger fired.' Оператор <CREATE TRIGGER> создает триггеры «UpdateIndicator» и «DeleteIndicator» и привязывает их к таблице «Товар» в базе данных <Northwind>. Если происходит событие <update> или <DELETE>, эти триггеры выводят на вкладке <Grids> панели <Results> сообщение. Обратите внимание, что триггер «DeleteIndicator» проверяет значение функции <@@ROWCOUNT>. Если удалена одна или несколько строк, выводится сообщение. 8.4 Проверка триггеров таблицы «Товар» 1. На панели <Editor> в окне <Query> введите и исполните следующий код: INSERT INTO Товар (Код_товара, Код_поставщика, Название_товара, Количество_товара, Цена_товара) </div> <div> 140 VALUES (3, 2, 'Фотоаппарат Canon', 5, 2000) В таблицу «Товар» добавляется запись, триггер «InsertIndicator» срабатывает и выводит сообщение на вкладке <Messages> панели <Results>. 2. На панели <Editor> в окне <Query> введите и исполните следующий код: UPDATE Товар SET Товар.Название_товара = 'Фотоаппарат_LG' WHERE Товар.Название_товара = 'Фотоаппарат Canon' В таблице «Товар» обновляется запись, триггер «UpdateIndicator» срабатывает и выводит сообщение на вкладке <Messages> панели <Results>. 3. На панели <Editor> в окне <Query> введите и исполните следующий код: DELETE Товар where Название_товара = 'Фотоаппарат_LG' Из таблицы «Товар» удаляется запись, триггер «DeleteIndicator» срабатывает и выводит сообщение на вкладке <Messages> панели <Results>. 8.4 Переименование, модификация и просмотр триггера 1. На панели <Editor> в окне <Query> введите и исполните следующую команду для запуска хранимой процедуры: sp_rename @objname=insertindicator, @newname=insupdcontrol Системная хранимая процедура «sp_rename» переименовывает триггер «InsertIndicator» в «InsUpdControl». 2. Исполните запрос. На вкладке <Messages> панели <Results> выводится предупреждение о том, что переименование объекта может повлечь за собой сбои в работе сценариев и хранимых процедур. Это сообщение также свидетельствует об успешном завершении операции переименования. 3. Введите на панели <Editor> в окне <Query> и исполните следующую команду для запуска хранимой процедуры: sp_helptrigger @tabname = Товар </div> <div> 141 Системная хранимая процедура «sp_helptrigger» выводит список триггеров, применяемых к таблице «Товар». 4. На панели <Editor> в окне <Query> введите и исполните следующий код: ALTER TRIGGER dbo.insupdcontrol ON dbo.Товар INSTEAD OF INSERT, UPDATE AS PRINT 'Inserts and updates are not allowed at this time.' В результате модификации переименованный триггер преобразуется в триггер «INSTEAD OF», который предотвращает все операции добавления и обновления в таблице «Товар». С помощью триггеров этого типа можно временно запретить внесение изменений в таблицу. Для включения и выключения триггеров используют оператор <alter table>. 5. Введите на панели <Editor> в окне <Query> и исполните следующий код: SET NOCOUNT ON INSERT INTO Товар (Код_товара, Код_поставщика, Название_товара, Количество_товара, Цена_товара) VALUES (4, 3, 'Фотоаппарат Samsung', 3, 5000) SET NOCOUNT OFF Триггер «INSTEAD OF» срабатывает и выводит сообщение, в котором говорится, что в данный момент обновление запрещено. Активирован параметр <NOCOUNT>, поэтому на вкладке <Messages> панели <Results> не выводится сообщение с числом строк, на которое повлияло исполнение запроса. 6. Чтобы убедиться, что в таблицу «Товар» не добавлено ни одной записи, исполните для этой таблицы оператор <SELECT>. 7. На панели <Editor> в окне <Query> введите и исполните следующую команду для запуска хранимой процедуры: sp_helptext @objname=insupdcontrol Хранимая процедура выводит содержимое триггера «InsUpdControl». </div> <div> 142 8.5 Отключение и удаление триггера 1. На панели <Editor> в окне <Query> введите и исполните следующий код: ALTER TABLE Товар DISABLE TRIGGER insupdcontrol Оператор <alter table> отключает триггер «InsUpdControl» в таблице «Товар». 2. На панели <Editor> в окне <Query> введите и исполните следующий код: INSERT INTO Товар (Код_товара, Код_поставщика, Название_товара, Количество_товара, Цена_товара) SELECT * FROM Товар where Название_товара = 'Фотоаппарат Samsung' Запись успешно добавляется в таблицу «Товар», добавленная запись выводится на вкладке <Grids> панели <Results>. 3. На панели <Editor> в окне <Query> введите и исполните следующий код: DROP TRIGGER insupdcontrol, updateindicator, deleteindicator DELETE Товар where Название_товара = 'Фотоаппарат Samsung' Оператор <DROP TRIGGER> удаляет все три триггера, привязанных к таблице «Товар». Оператор <DELETE> удаляет запись, ранее добавленную к таблице «Товар». 8.6 Контрольное задание Создать не менее трех триггеров в базе данных согласно выбранному вами варианту. 8.7 Контрольные вопросы 1. Что такое триггер? 2. Каково назначение триггеров? 3. В чем отличие триггера от хранимой процедуры? 4. Как создать триггер? </div> <div> 143 9 Лабораторная работа № 9. Представления Цель работы: используя язык T-SQL, научиться создавать, модифицировать и удалять представления. Используемое программное обеспечение: Microsoft SQL Server 2017. Представление – виртуальная (логическая) таблица, представляющая собой поименованный запрос, который будет подставлен как подзапрос при использовании представления. В отличие от обычных таблиц реляционной базы данных, представление не является самостоятельной частью набора данных, хранящегося в базе. Содержимое представления динамически вычисляется на основании данных, находящихся в реальных таблицах. Изменение данных в реальной таблице базы данных немедленно отражается в содержимом всех представлений, построенных на основании этой таблицы. 9.1 Представления и подзапросы Представления могут использовать подзапросы, включая соотнесенные подзапросы. Предположим, ваша компания предусматривает премию для тех продавцов, которые имеют заказчика с самой высокой суммой заказа для любой указанной даты. Вы можете проследить эту информацию с помощью представления: CREATE VIEW Elitesalesforce AS SELECT b.odate, a.snum, a.sname FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders c WHERE c.odate = b.odate) Если, с другой стороны, премия будет назначаться только продавцу, который имел самую высокую сумму заказа за последние десять лет, вам </div> <div> 144 необходимо будет проследить их в другом представлении, основанном на первом: CREATE VIEW Bonus AS SELECT DISTINCT snum, sname FROM Elitesalesforce a WHERE 10 <= (SELECT COUNT (*) FROM Elitesalesforce b WHERE a.snum = b.snum) Извлечение из этой таблицы продавца, который будет получать премию, выполняется простым запросом: SELECT * FROM Bonus Если команды модификации могут выполняться в представлении, представление как сообщалось, будет модифицируемым; в противном случае оно предназначено только для чтения при запросе. Не противореча этой терминологии, мы будем использовать выражение «модифицируемое представление» (updating a view), что означает возможность выполнения в представлении любой из трех команд модификации DML (<INSERT>, <update> и <DELETE>), которые могут изменять значения. 9.2 Модифицируемое представление Модифицируемое представление – это представление, в котором команда модификации может выполниться, чтобы изменить одну и только одну строку основной таблицы в каждый момент времени, не воздействуя на любые другие строки любой таблицы. Критерии определения модифицируемости представления: 1. Оно должно выводиться в одну и только в одну базовую таблицу. </div> <div> 145 2. Оно должно содержать первичный ключ этой таблицы (это технически не предписывается стандартом <ansi>, но было бы неплохо придерживаться этого). 3. Оно не должно иметь никаких полей, которые бы являлись агрегатными функциями. 4. Оно не должно содержать <DISTINCT> в своем определении. 5. Оно не должно использовать <GROUP BY> или </group></distinct></ansi></delete></update></insert></delete></drop></query></editor></results></grids></query></editor></alter></query></editor></query></editor></select></results></messages></nocount></query></editor></alter></query></editor></query></editor></results></messages></query></editor></results></messages></query></editor></results></messages></query></editor></results></messages></query></editor></results></grids></delete></update></northwind></create></query></editor></results></grids></northwind></create></query></query></northwind></alter></drop></no></yes></query></enterprise></query></object></northwind></enter></editor></query></results></grids></enter></editor></from></where></group></alter></alter></create></query></editor></editor></no></query></editor></query></query></exec></exec></editor></exec></execute></query></query></execute></script></object></exec></results></results></northwind></exec></execute></editor></query></name></editor></query></editor></dependencies></northwind></dependencies></dependencies></dbo></northwind></stored></northwind></query></object></query></custorderhistrep></from></join></from></where></sum></select></select></as></select></as></select></create></custorderhistrep></northwind></query></editor></query></query></object></query></northwind></results></grids></master></query></editor></new></create></query></create></include></include></options></scripting></query></extended></extended></extended></object></object></system></master></dependencies></dependencies></dbo></master></stored></extended></stored></master></object></object></query></master></recompile></for></syscomment></cursor></null></cursor></cursor></cursor></image></text></exec></null></delete></update></insert></sql></messages></drop></grids></select></messages></delete></grids></select></messages></update></query></columns></enterprise></northwind></enterprise></enterprise></select></results></messages></exec></exec></results></messages></revoke></revoke></query></delete></insert></update></select></results></grids></exec></exec></results></messages></grant></grant></editor></query></delete></update></insert></select></revoke></grant></select></delete></update></insert></sql></object></results></messages></query></editor></drop></select></results></messages></query></editor></test></select></results></messages>

1 2 3 4 5 6 7 8 9 ... 12