Материал: BD_Laboratornyi_774_praktikum

Insert @rowset (КодРегиона, Страна, Область, Город)

SELECT КодРегиона, Страна, Область, Город

FROM Регион

WHERE Страна = @Страна

-- Заносим данные в столбец "ОбластнойЦентр" переменной @rowset

UPDATE @rowset

SET ОбластнойЦентр = 1

WHERE Область = ''

-- Заносим данные в результирующую переменную @CountryRegion типа таблица, т.к.

-- локальная переменная @rowset после выхода из функции автоматически уничтожается

INSERT @CountryRegions

SELECT КодРегиона, Страна, Город, ОбластнойЦентр

FROM @rowset

RETURN

END

GO

/* Проверка работы функции fn_ВыборРегионовСтраны */

SELECT * FROM fn_ВыборРегионовСтраны('Беларусь')

SELECT * FROM fn_ВыборРегионовСтраны('Россия')

GO



Для внесения изменений в существующие пользовательские функции используются те же команды, что и для их создания, с тем лишь отличием, что вместо зарезервированного слова CREATE используется слово ALTER (см. [1], стр. 1119).

Для удаления пользовательской функции используется команда, имеющая следующий синтаксис (см. [1], стр. 1120):

DROP FUNCTION { [ owner_name . ] function_name } [ ,...n ]

Задание к работе

1. Создайте хранимую процедуру pr_КолебанияСпросаТоваров, которая решает рассмотренную выше (см. раздел I) задачу определения наименования товара, по которому был наибольший или наименьший спрос за последние N дней. Эта процедура должна иметь два входных параметра (@Интервал, @ТипРезультата) и два выходных параметра (@Имя, @Итог). Если значение входного параметра @ТипРезультата равно 1, находится товар наибольшего спроса. Если же значение параметра равно 2 – находится товар наименьшего спроса.

2. Создайте хранимую процедуру pr_КлиентПоставщик_СтранаИнтервал, которая подсчитывает, сколько различных клиентов и различных поставщиков из указанной страны фигурирует в таблице Заказ, причем анализируются только те заказы, в которых значение поля Дата заказа попадает в указанный интервал дат. Эта процедура должна иметь три входных параметра (@Страна, @НачалоИнтервала, @КонецИнтервала) и два выходных параметра (@ЧислоКлиентов, @ЧислоПоставщиков). Если же значение параметра @Страна не будет указано (т.е. будет равно NULL), то подсчет клиентов и поставщиков должен вестись независимо от их национальной принадлежности.

3. Создайте хранимую процедуру pr_Товар_СтранаВалютаИнтервал, которая подсчитывает, сколько различных товаров в конкретной валюте было заказано клиентами из указанной страны, причем анализируются только те заказы, в которых значение поля Дата заказа попадает в заданный интервал дат. Эта процедура должна иметь четыре входных параметра (@Страна, @Валюта, @НачалоИнтервала, @КонецИнтервала) и один выходной параметр (@ЧислоТоваров). При этом расширьте возможности процедуры следующим образом:

- если значение параметра @Страна не будет указано (т.е. будет равно NULL), то подсчет товаров должен вестись независимо от национальной принадлежности клиента;

- если значение параметра @Валюта не будет указано (т.е. будет равно NULL), то подсчет товаров должен вестись применительно к национальной валюте (код валюты – BYR).

4. Создайте пользовательскую функцию fn_getЧислоДней_вМесяце типа Scalar, которая для конкретной даты возвращает число дней в месяце, который определяется этой датой (високосность года не учитывается). Эта функция должна иметь один входной параметр (@Дата).

5. Создайте пользовательскую функцию fn_getФИО_вФормате типа Scalar, которая на основе текстовой строки, содержащей фамилию, имя и отчество, формирует текстовую строку в одном из следующих форматов:

1) исходная строка переводится в верхний регистр;

2) исходная строка переводится в нижний регистр;

3) на верхнем регистре должны быть только первые буквы слов;

4) выводится только фамилия, а имя и отчество заменяются их первыми буквами с точкой.

Эта функция должна иметь два входных параметра (@ФИО, @Формат).

Усложненный вариант. Расширьте возможности функции таким образом, чтобы была допустима исходная строка (задаваемая параметром @ФИО), содержащая не один, а несколько пробелов между фамилией и именем или между именем и отчеством, а также допускающая наличие лидирующих пробелов перед фамилией.

6. Создайте пользовательскую функцию fn_getGroup_НаименованиеВалюта типа Inline Table-valued, которая возвращает таблицу со следующими столбцами:

Эта таблица должна отражать результат группировки данных по полям Наименование и ИмяВалюты. Для каждой такой группы подсчитывается итоговое количество заказанного товара и итоговая стоимость в валюте и в национальной валюте.

Пользовательская функция fn_getGroup_НаименованиеВалюта должна иметь два входных параметра (@НачалоИнтервала, @КонецИнтервала), поэтому при формировании результирующей таблицы необходимо учитывать только те строки из таблицы Заказ, в которых значение поля Дата заказа попадает в указанный параметрами интервал дат.

7. Создайте пользовательскую функцию fn_getTable_СтоимостьНВ типа Multi-statement Table-valued, которая возвращает таблицу со следующими столбцами:

Эта таблица строится в три этапа. Сначала создается таблица со столбцами, показанными выше, где столбец Номер является автоинкрементным первичным ключом, столбцы Цена в НВ (цена в национальной валюте) и Стоимость в НВ (стоимость в национальной валюте) являются вычисляемыми. Число строк этой таблицы будет равно числу строк в таблице Заказ.

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

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

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

9. Фрагменты кода, относящиеся к выполнению задания, сохраните в файле D:\Work\X7230ХХХ\script3.sql.

10. Удалите базу данных Склад_ХХХ. Для этого в ее контекстном меню выберите команду Delete и затем в появившемся окне Delete Object установите флажок Close Existing Connections.

Лабораторная работа №7. Курсоры и триггеры в языке Transact-sql

Цель работы

1. Ознакомиться с использованием курсоров в языке Transact-SQL.

2. Освоить способы создания триггеров в языке Transact-SQL.

Задачи

1. Объявление и открытие курсора с целью манипулирования строками набора данных.

2. Закрытие и освобождение курсора.

3. Реализация бизнес-правил с помощью триггеров.

4. Получение информации об изменениях в данных с помощью триггерных таблиц inserted и deleted.

5. Использование команд управления транзакциями.

Методические указания

При помощи пользовательского меню Windows запустите утилиту SQL Server Management Studio, после чего на панели Object Explorer в древовидной структуре раскройте папку Databases.

С помощью команды меню File►Open►File загрузите сценарий из файла D:\Work\X7230ХХХ\script.sql в окно Query.

Выполните сценарий, нажав на панели инструментов кнопку Execute (или клавишу F5). В результате будет создана база данных Склад_ХХХ.

Обновите данные на панели Object Explorer. Для этого используйте команду Refresh в контекстном меню папку Databases или соответствующую кнопку в верхней части панели. В результате база данных Склад_ХХХ станет видимой на панели Object Explorer.

Закройте окно Query, содержащее сценарий script.sql. Затем на панели инструментов нажмите кнопку New Query, и откройте новое пустое окно Query, предназначенное для формирования нового сценария script4.sql. Готовые к исполнению команды (пакеты) языка Transact-SQL, из которых будет формироваться сценарий, выделены ниже при помощи стрелок  и .

Сделайте активной созданную базу данных Склад_ХХХ:



USE Склад_ХХХ

GO



Раздел I. Использование курсоров в языке Transact-sql

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

Существуют статические и динамические курсоры. Статический курсор представляет собой моментальный снимок выбираемых строк на момент его открытия. В дальнейшем содержимое такого курсора не меняется и хранится в системной базе данных tempdb.

Динамические курсоры каждый раз обновляются при обращении к его строкам (т.е. обращение к другой строке формирует набор данных заново путем выполнения команды SELECT). При работе с динамическими курсорами пользователи могут вносить изменения в строки набора данных с помощью команд UPDATE, INSERT, DELETE. Однако каждая такая команда за один раз может работать только с одной строкой.

Кроме того, курсоры бывают последовательные (Forward-only) и прокручиваемые (Scrollable). Последовательные курсоры обеспечивают только последовательное считывание строк в прямом направлении, т.е. начиная с первой строки и заканчивая последней.

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

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

Весь процесс использования курсора включает в себя пять этапов.

1. Объявление курсора. Подразумевает указание его имени и запроса SELECT, который будет использоваться для формирования набора данных, связанного с курсором. Также указывается тип курсора. Команда имеет две разновидности, различающиеся синтаксисом (см. [1], стр. 1224).

Синтаксис стандарта SQL-92:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

Синтаксис Transact-SQL (с более широкими возможностями) :

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

2. Открытие курсора. В процессе открытия курсора выполняется ассоциированный с курсором запрос SELECT, в результате чего создается связанный с курсором набор данных. Команда имеет следующий синтаксис (см. [1], стр. 1228):

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

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

Команда считывания строки данных из курсора имеет следующий синтаксис (см. [1], стр. 1229):

FETCH         [ [ NEXT | PRIOR | FIRST | LAST                 | ABSOLUTE { n | @nvar }                 | RELATIVE { n | @nvar }            ]      FROM         ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]

Примечание. Непосредственно после слова FETCH опцией указывается строка, которую нужно выбирать, поэтому сначала эта строка становится текущей, и только после этого производится выборка данных. При этом возможен выход за пределы диапазона строк набора данных. Эту ситуацию можно контролировать с помощью встроенной функции @@FETCH_STATUS, которая возвращает значение 0, если последняя операция выборки данных из курсора была выполнена успешно. Если возвращается отрицательное значение, то это означает, что была предпринята попытка выборки строки, находящейся за пределами набора данных.

Можно также использовать функцию @@CURSOR_ROWS. Она возвращает количество строк набора данных, но только для статического курсора. Для динамического курсора всегда возвращается -1.

Обновление данных посредством курсора выполняется с помощью команды UPDATE, имеющей следующий синтаксис (см.[1], стр. 1232):

UPDATE table_name

SET { column_name = { expression | DEFAULT | NULL }} [ ,...n ]     WHERE CURRENT OF cursor_name

Изменения, внесенные этой командой, будут касаться только текущей строки курсора. За одну операцию обновления данных допускается изменение значений полей, относящихся к одной таблице. Если курсор строится на основе нескольких таблиц и необходимо изменить все значения строки курсора, то для этого придется выполнить несколько команд UPDATE.

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

Удаление данных посредством курсора выполняется с помощью команды DELETE, имеющей следующий синтаксис (см.[1], стр. 1234):

DELETE  [FROM] table_name

    WHERE CURRENT OF cursor_name

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