сервер. Учебнометодическое пособие по выполнению пратических работ тольятти 2018 2 содержание
 Скачать 7.66 Mb.
|
|
Запрос 6 Вывести на экран сведения о наибольшей по размеру партии товара во всех договорах с указанием поставщика, а также номера и даты договора. Текст запроса приведен на рис. 3.7. Рисунок 3.7 Запрос можно сохранить в файле с именем SQLQuery06.sql Запрос 7 Вывести на экран список поставщиков (наименование и код), с которыми не было заключено ни одного договора. Текст запроса приведен на рис. 3.8. Рисунок 3.8 20 Запрос можно сохранить в файле с именем SQLQuery07.sql Запрос 8 Вывести на экран список наименований поставленных товаров с указанием средней цены поставки за единицу (вне зависимости от поставщика). Текст запроса приведен на рис. 3.9. Рисунок 3.9 Запрос можно сохранить в файле с именем SQLQuery08.sql Запрос 9 Вывести на экран список товаров (наименование, количество и цена, поставщик), для которых цена за единицу больше средней. Текст запроса приведен на рис. 3.10. Рисунок 3.10 Запрос можно сохранить в файле с именем SQLQuery09.sql Запрос 10 Вывести на экран сведения о пяти самых дорогих товарах (наименование, цена за единицу, поставщик). Текст запроса приведен на рис. 3.11 Рисунок 3.11 Запрос можно сохранить в файле с именем SQLQuery10.sql Запрос 11 Сформировать список поставщиков с указанием кода, адреса и данных поставщика. При формировании данных поставщика для поставщиков – 21 физических лиц вывести фамилию и инициалы, для поставщиков – юридических лиц – наименование. Текст запроса приведен на рис. 3.12. Рисунок 3.12 Запрос можно сохранить в файле с именем SQLQuery11.sql. Запрос 12 Сформировать список договоров (с указанием номера, даты поставки и данных о поставщике), общее количество поставленных товаров и общую сумму по каждому договору. Для поставщиков – физических лиц вывести фамилию и инициалы, для поставщиков – юридических лиц – ОГРН. В результат запроса должны быть включены только те договоры, на основании которых товары действительно поставлялись (т.е. в результат запроса не должны попасть так называемые «пустые» договоры) Текст запроса приведен на рис. 3.13. Рисунок 3.13 Запрос можно сохранить в файле с именем SQLQuery12.sql. Запрос 13 Сформировать список товаров (с указанием номера договора и даты поставки), поставленных поставщиками 1 (ЧП Петров П.П.) и 2 (ООО 22 «Интерфрут»). Примечание. Данный запрос иллюстрирует особенности использования операции объединения (UNION). Нетрудно заметить, что данный запрос может быть легко реализован без использования операции объединения. Текст запроса приведен на рис. 3.14. Рисунок 3.14 Запрос можно сохранить в файле с именем SQLQuery13.sql. Запрос 14 Сформировать номенклатуру товаров (т.е. список наименований товаров), которые поставлялись только поставщиком 1 (ЧП Петров П.П.), или только поставщиком 2 (ООО «Интерфрут»), или и поставщиком 1, и поставщиком 2. Текст запроса приведен на рис. 3.15. Рисунок 3.15 Запрос можно сохранить в файле с именем SQLQuery14.sql Запрос 15 Сформировать номенклатуру товаров (т.е. список наименований товаров), которые поставлялись и поставщиком 1 (ЧП Петров П.П.), и поставщиком 2 (ООО «Интерфрут»). Примечание. Данный запрос иллюстрирует особенности использования операции пересечения (INTERSECT). Текст запроса приведен на рис. 3.16. 23 Рисунок 3.16 Запрос можно сохранить в файле с именем SQLQuery15.sql Запрос 16 Сформировать номенклатуру товаров (т.е. список наименований товаров), которые поставлялись поставщиком 1 (ЧП Петров П.П.), но поставлялись поставщиком 2 (ООО «Интерфрут»). Примечание. Данный запрос иллюстрирует особенности использования операции разности (EXCEPT). Текст запроса приведен на рис. 3.17. Рисунок 3.17 Запрос можно сохранить в файле с именем SQLQuery16.sql Запрос 17 Сформировать список товаров, который должен отражать частоту поставок товаров. В список включить только товары, которые поставлялись более одного раза. Список должен быть отсортирован в порядке убывания частоты поставок. Текст запроса приведен на рис. 3.18. Рисунок 3.18 Запрос можно сохранить в файле с именем SQLQuery17.sql. 24 Запрос 18 Сформировать данные о количественной динамике поставок товаров в течение 2018 года. Данные должны быть агрегированы помесячно и представлены в виде таблицы, строками которой являются названия товаров, а столбцами – номера месяцев 2018 года. На пересечении строки и столбца должно отображаться количество данного товара, поставленного в данном месяце. Примечание. Данный запрос иллюстрирует особенности создания и использования перекрестного запроса средствами языка Transact-SQL. Текст запроса приведен на рис. 3.19. Запрос можно сохранить в файле с именем SQLQuery18_1.sql. Рисунок 3.19 Приведенный результат запроса может быть неудобным для восприятия (например, из-за наличия значений NULL). Этот недостаток может быть устранен, например, путем замены значений NULL на 0. Текст измененного запроса приведен на рис. 3.20. Запрос можно сохранить в файле с именем SQLQuery18_2.sql. Рисунок 3.20 Запрос 19 Сформировать список поставленных товаров. Для каждого товара в этом списке должны быть указаны следующие данные: номер договора, название товара, количество единиц, цена за единицу, дата поставки, название месяца и 25 номер года. Текст запроса приведен на рис. 3.21. Рисунок 3.21 Запрос можно сохранить в файле с именем SQLQuery19_1.sql Как видно из результата запроса, формально требование включения в результат запроса наименования месяца выполнено путем использования встроенной функции DATENAME(). Однако такие названия месяцев не всегда удобны для восприятия. Может возникнуть требование их замены на русскоязычные. Эту проблему можно решить путем разработки пользовательской функции, конвертирующей названия месяцев. Этот подход несколько более трудоемкий. Другим вариантом решения проблемы может быть использование в запросе функции CASE языка Transact-SQL. Текст такого запроса приведен на рис. 3.22. Запрос можно сохранить в файле с именем SQLQuery19_2.sql Рисунок 3.22 Составить отчет по проделанной работе. 26 Практическая работа №4. Создание и использование программных объектов БД СУБД MS SQL Server Как и все СУБД, поддерживающие технологию "клиент-сервер", Microsoft SQL Server наряду с данными хранит в базах данных программные объекты. Такими объектами являются: хранимые процедуры, хранимые функции и триггеры. Программные объекты могут использоваться большим количеством приложений. Такие объекты позволяют повысить эффективность функционирования приложений, взаимодействующих с базами данных, обеспечить высокую степень защиты последних и унифицировать способы обращения к данным из приложений. Хранимые процедуры - это подпрограммы на языке SQL, хранящиеся в базах данных и представляющие собой один из видов их общих ресурсов. Тело любой хранимой процедуры представляет последовательность SQL-операторов, например таких, как выборка данных, их модификация, удаление данных, операторы цикла, условные операторы и ряд других. Процедуры вызываются и могут использовать как входные параметры (передающие значения в процедуру), так и выходные параметры (возвращающие результаты процедуры вызывающему программному объекту). Процедуры могут вызываться из процедур, функций и других типов программных объектов. Хранимые процедуры создаются оператором CREATE PROCEDURE. Модификация тела хранимой процедуры осуществляется оператором ALTER PROCEDURE. Эти операторы могут использовать: пользователи, которым разрешено создавать объекты БД; администратор БД. Тело хранимой процедуры является составным оператором, т.е. совокупностью операторов, заключенных между служебными словами BEGIN и END. Наряду с SQL-операторами в составном операторе могут быть определены локальные переменные, курсоры, временные таблицы данных и исключительные ситуации. Они доступны только в пределах составного оператора и не видимы за его пределами. Время их существования ограничено периодом исполнения составного оператора. Локальные определения широко используются при разработке программных объектов. При выполнении процедуры, формирующей результирующее множество, создается временная таблица - курсор (CURSOR). В курсор записывается результирующее множество. В дальнейшем пользователь может обрабатывать данные курсора по-своему усмотрению. Одним из способов возврата результатов работы хранимых процедур является формирование результирующего множества. Данное множество формируется при выполнении оператора SELECT. Оно записывается во временную таблицу - курсор. Применение курсора в процедурах осуществляется путем последовательного выполнения следующих шагов: 27 при помощи оператора DECLARE объявляется курсор для отдельного оператора SELECT или для отдельной процедуры; оператором OPEN производится открытие курсора; используя оператор FETCH, осуществляется установление указателя на требуемую запись курсора. При этом значения полей текущей записи присваиваются переменным, указываемым в операторе FETCH; в процессе перемещения указателя текущей записи курсора при выходе указателя за пределы курсора выдается предупреждение row not found; после того как курсор становится ненужным, он закрывается оператором CLOSE_._По_умолчанию_курсор_закрывается_автоматически_в_конце_транзакции_(операторы_COMMIT'>CLOSE. По умолчанию курсор закрывается автоматически в конце транзакции (операторы COMMIT или ROLLBACK). Если при объявлении курсора указана фраза WITH HOLD (с сохранением), то курсор закрывается только явным образом оператором CLOSE. Хранимые функции являются разновидностью хранимых процедур. Они включены в состав программных объектов баз данных с целью наибольшего соответствия языкам программирования, например С или Pascal. Как и в этих языках программирования, каждая хранимая функция рассматривается в качестве выражения, формирующего одно единственное значение. Хранимые функции применяются для расширения функциональных возможностей операторов SELECT и ряда других SQL-операторов. Хранимые функции создаются оператором CREATE FUNCTION. Их модификация производится при помощи оператора ALTER FUNCTION. Оператор CREATE FUNCTION несколько отличается от оператора CREATE PROCEDURE. Эти отличия состоят в следующем: в функциях допустимы только входные формальные параметры; после описания формальных параметров функции должен следовать оператор RETURNS. Данный оператор указывает тип возвращаемого функцией значения; в теле функции обязательно должен присутствовать оператор RETURN, который специфицирует возвращаемое функцией значение; функция не может формировать результирующее множество. Вызов хранимой функции должен осуществляться там, где требуется выражение, формирующее значение. В связи с этим, функции могут непосредственно использоваться в выражениях. Например, они могут применяться в арифметических выражениях, вместо фактических параметров процедур или функций, а также прямо в составе SQL-операторов. Все эти качества позволяют в значительной степени расширить функциональные возможности языка SQL, как средства разработки приложений. Триггеры - это один из видов программных объектов СУБД, поддерживаемых Microsoft SQL Server . Каждый триггер связан с одной из таблиц данных, входящих состав БД. С каждой такой таблицей может быть связано несколько триггеров. Основное назначение триггеров состоит в автоматическом использовании их в качестве реакции на некоторые события, 28 происходящие с таблицами, с которыми связанны триггеры. Это свойство триггеров позволяет использовать их для реализации сложных форм ограничений целостности данных. Кроме того, рассматриваемое свойство превращает сервер из пассивного наблюдателя за происходящими изменениями данных в систему, оперативно реагирующую на такие изменения. Правила, в соответствие с которыми осуществляются активные действия сервера, определяются триггерами. Триггеры, создаются оператором CREATE TRIGGER. Модификация триггеров производится при помощи оператора ALTER TRIGGER, а удаление – оператором DROP TRIGGER. В СУБД Microsoft SQL Server триггеры могут быть определены для одного из приведенных ниже событий или сразу на несколько из них: добавление новой записи в таблицу. Данное событие возникает при выполнении оператора INSERT. Оно приводит к активизации триггера при добавлении новой записи в связанную таблицу; удаление записей. Это событие наступает в результате воздействия оператора DELETE. Данный оператор производит удаление записей из таблиц, с которыми связаны триггеры, что является причиной активизации триггера; модификация записей. Событие этого типа инициируется оператором UPDATE. Оно возникает при изменении значений любого из полей записей таблицы, с которой связан активизируемый триггер; изменение значений заданного списка полей таблицы. Это событие, как и предыдущее, возникает при выполнении оператора UPDATE (UPDATE OF colums-list), но при модификации только заданных полей таблицы данных (colums-list). Таким образом, триггеры автоматически запускаются при изменении содержимого таблицы данных, с которой они связаны. Для каждого триггера должно быть определено время его выполнения - либо перед операторами INSERT, DELETE, UPDATE (предваряющий триггер), либо после них (завершающий триггер). Типичный пример использования предваряющих триггеров - проверка вводимых данных. Завершающие триггеры полезны в тех случаях, когда при модификации записей необходимо сравнивать исходные значения полей с их новыми значениями. Операторы INSERT, DELETE, UPDATE, которые содержат служебное слово WHERE или подзапросы, как правило, воздействуют (добавляют, удаляют, модифицируют) на несколько записей таблицы. Когда при этом должен выполняться триггер? Каждый раз при изменение очередной записи или один раз после модификации всех записей? Для ответа на этот вопрос в Microsoft SQL Server реализованы триггеры двух уровней. Триггер первого уровня Statement-level trigger выполняется однократно после полного завершения одного из вышеуказанных операторов (операторный триггер) Триггер второго уровня Row-level trigger предназначен для многократного выполнения после каждого изменения одной из записей (строк) таблицы (строчный триггер). 29 Если при выполнении триггера возникает ошибка, тогда все действия осуществленные оператором, активизировавшим триггер, и действия, уже произведенные самим триггером, отменяются. В результате этого БД возвращается в состояние, предшествующее началу выполнения вышеуказанного оператора. Общий порядок применения триггеров состоит в последовательном выполнении следующих шагов при вызове на выполнение операторов INSERT, DELETE или UPDATE: 1. Активизируются предваряющие триггеры, если они есть. 2. Выполняются действия по проверке ограничений ссылочной целостности, установленной при определении схемы БД и возможно нарушенной действиями предваряющих триггеров. 3. Выполняется непосредственно операция, активизирующая триггер. 4. Активизируются завершающие триггеры, в случае если таковые имеются. При работе триггеров часто возникает необходимость обращаться как к исходным значениям полей таблицы, так и к их обновленным значениям. Для этой цели используются фраза REFERENCING с параметрами NEW AS или OLD AS. С ее помощью вводятся псевдонимы для обращения к исходным и обновленным данным. В конечном же итоге смысл таких псевдонимов зависит от типа триггера и от событий, при возникновении которых активизируются триггеры. Назначение псевдонимов, вводимых фразой REFERENCING, приведено в табл. 4.1. Таблица 4.1 – Назначение псевдонимов, вводимых фразой REFERENCING для обращения к исходным и обновленным записям при определении триггеров Тип триггера Параметр для REFERENCING Операторы, активизирующие триггер INSERT DELETE UPDATE Строчный Предваряющий NEW AS Для обращения к полям вводимой записи -- Для обращения к новым значениям модифицируемых полей записи OLD AS -- Для обращения к полям удаляемой записи Для обращения к исходным значениям модифицируемых полей записи Завершающий NEW AS Для обращения к полям введенной записи -- Для обращения к новым значениям модифицируемых полей записи 30 OLD AS -- Для обращения к полям удаленной записи Для обращения к исходным значениям модифицируемых полей записи Для обращения к Для обращения к временной временной NEW AS таблице, -- таблице,содержащей содержащей модифицированные введенные записи Операторный Завершающий записи Для обращения к Для обращения к временной временной таблице, OLD AS -- таблице, содержащей исходные содержащей значения обновленных удаленные записей записи Создание и использование хранимых процедур Для получения доступа к перечню хранимых процедур нужно в списке объектов БД открыть пункт Programmability и в нем открыть пункт Stored Procedures. В результате появится список пользовательских хранимых процедур (если такие процедуры были созданы ранее). Пункт System Stored Procedures открывать не нужно. Создание хранимой процедуры, реализующей выборку данных из таблиц Договоры, ЮридическиеЛица, ФизическиеЛица Такая хранимая процедура может обеспечить вывод связанных данных, находящихся в нескольких таблицах. Для создания хранимой процедуры следует выполнить следующую последовательность действий 1. Щелкнуть правой кнопкой мыши по пункту Stored Procedures и в появившемся меню выбрать пункт New Stored Procedure…. В результате будет создан запрос, содержащий шаблон хранимой процедуры (рис. 4.1). 31 Рисунок 4.1 2. Для изменения шаблона используем следующий код (рис. 4.2). Рисунок 4.2 3. Для создания этой хранимой процедуры нужно нажать кнопку Execute на панели инструментов. В том случае, если запрос выполнен успешно, в окне Messages появится сообщение Command(s) completed successfully. В этом случае окно запроса с текстом хранимой процедуры можно закрыть, причем запрос сохранять в виде файла не нужно. Хранимая процедура должна появиться в 32 списке хранимых процедур. Если процедура отсутсвует в списке, нужно щелкнуть правой кнопкой мыши по пункту Stored Procedures и в появившемся меню выбрать пункт Refresh. 4. Для проверки работы хранимой процедуры нужно создать новый запрос и ввести оператор вызова хранимой процедуры (рис. 4.3). В результате выполнения хранимой процедуры на экран будет выведена таблица, содержащая результат запроса, реализованного в хранимой процедуре. Этот запрос можно закрыть, не сохраняя. Другим способом выполнения хранимой процедуры является ее выбор в списке хранимых процедур правой кнопкой мыши и выбор в появившемся меню пункта Execute Stored Procedure…. Рисунок 4.3 5. При необходимости внесения изменений в текст процедуры, ее можно открыть в режиме редактирования, для чего нужно щелкнуть правой кнопкой мыши по имени процедуры в списке процедур и в появившемся меню выбрать пункт Modify. В результате на экран будет выведен запрос, содержащий тест хранимой процедуры. Если в тест процедуры вносились изменения и эти изменения нужно сохранить, то запрос, содержащий тест хранимой процедуры, нужно выполнить. После успешного выполнения запрос в виде файла сохранять не нужно. Для проверки изменения хранимой процедуры ее нужно вновь открыть в режиме редактирования. |