Методические указания к выполнению ЛР2 - Постреляционное расширение языка SQL (1033944), страница 2

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 2)

SELECT src,dest from roads where type=@type

)

После ввода теста нажать кнопку выполнения «Execute» для сохранения и компиляции функции.

Функция типа «inline» отличается тем, что содержит только один запрос, который подставляется в место обращения к функции. Также можно создать табличную функцию типа «Multi-statement» из нескольких команд и запросов, которая вызывается и выполняется как программа. В этом случае синтаксис объявления функции следующий:

CREATE FUNCTION схема.название(параметры)

RETURNS @переменная TABLE (описание таблицы)

AS BEGIN

Код функции

RETURN

END

Переменная, указанная как возвращаемый тип, содержит набор записей, которые возвращаются в качестве результата функции. Код функции должен обеспечить заполнение этой переменной, например, командой «insert».

Поскольку табличная функция возвращает набор записей, то обращение к ней происходит как к таблице. Табличная функция указывается в части «FROM» запроса.

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

select distinct * from dbo.roadByType('жд')

Создание хранимой процедуры

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

Для создания хранимой процедуры в окне прводника выбрать «Programmability -> Stored Procedure» и в контекстном меню выбрать «New Stored Procedure». Будет открыто окно с шаблоном для создания процедуры. В этом окне указать название, параметры и код процедуры.

Пример процедуры добавления записи в таблицу дороги:

CREATE PROCEDURE dbo.addRoad

@src nchar(10), @dest nchar(10), @type nchar(10) = 'жд'

AS

BEGIN

SET NOCOUNT ON;

-- Если NULL на входе, то выдает предупреждение

if( @src is NULL OR @dest is NULL)

raiserror('Недопустимы пустые значения',10,1);

-- Если дорога не существует, то добавить ее с перехватом возможного исключения

else if not exists(select * from roads

where src=@src and dest=@dest and [type]=@type )

begin try

insert into roads(src,dest,[len],[type],cost)

values (@src,@dest,1,@type,10)

end try

begin catch

-- Вывод своего сообщения при исключении

raiserror('Ошибка добавления данных',16,1);

end catch;

END

При указании параметров как процедур, так и функций можно задать значения по умолчанию, например:

@type nchar(10) = 'жд'

Вызов процедуры выполняется командой «Exec» из окна запросов или другой процедуры:

Exec процедура параметры

Для вызова процедуры и проверки ее работы открыть окно запроса, написать и выполнить запрос, например:

exec dbo.addRoad 'сочи','геленджик'

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

Если указать пустые значения параметров, например

exec dbo.addRoad 'Москва',NULL

то будет вызвано исключение и выведено предупреждение «Недопустимы пустые значения». Запись в таблицу не добавится.

При нарушении ограничений целостности, например

exec dbo.addRoad 'сочи','туапсе',NULL

будет вызвано исключение и выведено сообщение «Ошибка добавления данных». Запись в таблицу не добавится.

Для изменения кода функции или процедуры выбрать в окне проводника в контекстном меню нужного модуля «Modify». Будет открыто окно редактирования кода хранимого модуля для внесения в него изменений.

Рекурсивные запросы

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

Рис.3. Пример таблицы дорог

Если в запросе использовать соединение таблицы с самой собой при условии, что конец первого отрезка пути соединяется с началом второго отрезка, например

select A.src, B.dest from roads A join roads B on A.dest = B.src

то получаем множество дорог между двумя городами с одной пересадкой (см. рис. 4).

Рис.4. Множество дорог с одной пересадкой

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

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

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

select src,dest from roads

1. Итерационно происходит пополнение временной таблицы. На каждой итерации содержимое временной таблицы, полученное на предыдущем шаге, соединяется с содержимым базовой или другой постоянной таблицы по условию соединения и полученный результат добавляется во временную таблицу. Запрос на соединение временной таблицы с постоянной записывается на SQL, например соединение дорог с полученными ранее путями

select A.src, B.dest from roads A join Paths B on A.dest = B.src

Обращение к виртуальной таблице выполняется через псевдоним, действующий в переделах данного запроса, в примере это «Paths».

1. Процесс вычислений останавливается, если на очередной итерации содержимое временной таблице не изменилось. Это называется достижением фиксированной точки.

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

Итерационное пополнение временной таблицы называется «индукцией». Объединение базовой и индуктивной частей запроса выполняется командой «UNION»:

Базовая часть запроса

union ALL

Индуктивная часть запроса

Временная таблица, построенная в процессе итерационных вычислений, называется «общим табличным выражением». Синтаксис запроса с общим табличным выражением:

with Псевдоном (поля) as

( Вычисляемая часть запроса )

запрос к вычисляемому выражению

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

Например, запрос на получения всех путей выглядит как

with Paths(fromA,toB) as

(

select src,dest from roads

union ALL

select src,toB from roads join Paths on dest = fromA

)

select fromA,toB from Paths

В результате будет получена таблица, содержащая все пути (см. рис. 5).

Рис.5. Результат выполнения рекурсивного запроса

Процесс вычисления рекурсии должен быть конечен. Это обеспечивается его монотонностью. Свойство монотонности процесса вычислений подразумевает, что на каждой итерации происходит пополнение временной таблицы новыми кортежами без удаления или изменения находящихся в ней. По этой причине в вычисляемой части рекурсивного запроса недопустимо использовать операторы SQL, которые могут привести к сокращению временной таблицы, например, «distinct», «except», «exists», «group by». Однако, эти операторы можно использовать при формировании конечного запроса к вычисляемому выражению.

Функции ранжирования

Функции ранжирования позволяют получить в качестве результата запроса номера извлекаемых строк или номера строк в некоторых группах. Основные функции ранжирования:

«ROW_NUMBER() over (order by поле)» – возвращает номер данной строки в результате запроса при сортировке по указанному полю.

«RANK() over (order by поле)» – возвращает для данной строки номер ее группы в результате запроса при группировке по указанному полю. Номер группы совпадает с абсолютным номером первой строки из группы.

«DENSE_RANK() over (order by поле)» – возвращает для данной строки номер ее группы в результате запроса при группировке по указанному полю. Нумерация групп сплошная.

«NTILE(количество) over (order by поле)» – возвращает для данной строки номер ее группы при разбиении результата запроса на указанное количество групп одинакового размера.

Пример использования ранжирующих функций:

select ROW_NUMBER() over (order by src),

rank() over (order by src),

dense_rank() over (order by src),

ntile(3) over (order by src),

src

from roads

В результате получаем (см. рис. 6)

Рис.6. Результат выполнения функций ранжирования

Транспонирующие запросы

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

Синтаксис транспонирующего запроса:

SELECT итоговые_поля

FROM (запрос к исходной таблице) псевдоним1

PIVOT

( функция_агрегирования FOR поле

IN ([значение1], .. , [значениеN])) псевдоним2

Сначала выполняется запрос к исходной таблице, который извлекает набор записей. К полученным записям применяется оператор «PIVOT», который выполняет их группировку по указанному полю для записей, содержащих перечисленные значения этого поля, и применяет к полученным группам функцию агрегирования. Из результата выполнения оператора «PIVOT» извлекаются итоговые поля запроса. Псевдонимы необходимо указывать для хранения промежуточных результатов вычислений.

Пример для получения количества дорог из указанных городов:

select * from

(select src, [LEN] from roads) a

pivot ( count([len]) for src in

([москва],[СПб],[лондон],[париж])) b

В результате получаем (см. рис. 7)

Рис. 7. Результат транспонирующего запроса

ПРИМЕР ВАРИАНТА ЗАДАНИЯ

В среде SQL Server Management Studio создать БД lab2. В БД создать таблицу «Topology»(Топология), которая содержит свойства:

Ip1 – IP адрес 1 — строковое (not null),

Ip2 - IP адрес 2 — строковое (not null),

channel - тип канала — строковое (not null),

vel – скорость передачи,

id - идентификатор (PK) – целое, автоинкремент.

Открыть таблицу на редактирование и заполнить тестовыми данными.

Создать скалярную функцию maxVel(канал), которая возвращает максимальную скорость по указанному типу канала. Вызвать функцию из окна запроса.

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

Создать хранимую процедуру AddTopology (IP1, IP2, канал, скорость), которая проверяет наличие указанного канала. Если канал не существует, то добавляет его. Иначе изменяет тип канала и скорость для существующего. Для канала и скорости предусмотреть значения по умолчанию. Если скорость за пределами разумного, то вернуть через raiserror() предупреждение. Использовать «begin try ... end try» для перехвата исключений при выполнении команд «insert\update». При возникновении исключения вернуть через raiserror() сообщение об ошибке. Вызвать процедуру из окна запроса. Проверить перехват и создание исключений.

Продемонстрировать выполнение рекурсивного запроса, например, перечень IP-адресов, не достижимых из узла «192.168.0.1».

Продемонстрировать выполнение функций row_number(), Rank(), dense_rank(), ntile(4) применительно к типу канала.

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

СОДЕРЖАНИЕ ОТЧЕТА

1. Текст SQL запросов для создания таблиц, описания и вызова хранимых модулей, запросов на извлечение данных.

2. Скриншоты содержимого таблицы и результатов выполнения запросов.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Каковы возможности программирования на стороне сервера баз данных с применением SQL\PSM?

2. Как создать и заполнять таблицы и выполнять SQL запросы в среде Microsoft SQL Server Management Studio?

3. Хранимые процедуры и функции. Назначение, синтаксис и способы вызова. Передача параметров. Параметры по умолчанию.

4. Как создать, изменять и вызывать хранимые процедуры и функции в среде Microsoft SQL Server Management Studio?

5. Различия между хранимыми процедурами и функциями. Виды хранимых функций.

6. Конструкции языка Transact-SQL: условие, цикл, перехват исключений, создание исключений, присваивание переменных.

7. Синтаксис и процесс выполнения рекурсивных запросов.

8. Синтаксис и назначение транспонирующих запросов.

9. Синтаксис и назначение ранжирующих функций.

ЛИТЕРАТУРА

1. Ревунков Г.И. Банки и базы данных: метод. указания по курсу «Банки данных». – М.: Изд-во МГТУ им. Н.Э. Баумана, 2011 г. – 68с.

2. Пирогов В. SQL Server 2005. Программирование клиент-серверных приложений. - Спб.: БХВ-Петербург, 2006 г. – 336с.

3. Уолтерс У. и др. SQL Server 2009: ускоренный курс для профессионалов: пер. с англ. – М.: ООО «И.Д. Вильямс», 2009. – 768с.

Характеристики

Список файлов лабораторной работы