Методические указания к лабораторной работе «Постреляционное расширение языка SQL» по дисциплине «Постреляционные базы данных»
доц. к.т.н. Виноградова М.В.
доц. к.т.н. Максаков А.А.
доц. к.т.н. Черников В.А.
ВВЕДЕНИЕ
Учебно-методическое издание «Постреляционное расширение языка SQL» представляет собой методические указания к лабораторным работам по дисциплине «Постреляционные базы данных». В теоретической части учебно-методического издания рассмотрены конструкции языка Transact-SQL, являющегося постреляционным расширением языка SQL для СУБД Microsoft SQL Server. Приведено краткое описание языковых конструкций для создания хранимых функций и процедур. Рассмотрены примеры рекурсивных, транспонирующих и ранжирующих запросов. В заключительной части методических указаний приведены контрольные вопросы, список рекомендуемой литературы, требования к отчету по лабораторной работе и пример задания.
Ознакомившись с методическими указаниями и разобрав приведенные в нем примеры, студент может получить у преподавателя свой вариант задания и приступить к его выполнению. Целью лабораторной работы является практическое изучение возможностей постреляционного расширения языка SQL для создания хранимых процедур и функций и выполнения рекурсивных и транспонирующих запросов на примере Transact-SQL в среде Microsoft SQL Server Management Studio.
Оглавление
Оглавление 2
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ 3
Среда Microsoft SQL Server Management Studio 3
Создание новой Базы данных 4
Программирование на стороне сервера с применением SQL\PSM 6
Базовые конструкции Transact-SQL для реализации SQL\PSM 6
Создание хранимых функций 9
Создание хранимой процедуры 11
Рекурсивные запросы 13
Функции ранжирования 16
Транспонирующие запросы 17
ПРИМЕР ВАРИАНТА ЗАДАНИЯ 19
СОДЕРЖАНИЕ ОТЧЕТА 20
КОНТРОЛЬНЫЕ ВОПРОСЫ 20
ЛИТЕРАТУРА 20
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
Базовым языком запросов, используемым при работе с базами данных, является SQL. В постреляционных базах данных его функциональные возможности расширены стандартом SQL/PSM, который задает команды для создания хранимых процедур и функций, и стандартом SQL:1999, который определяет рекурсивные запросы и пользовательские типы данных. Эти дополнения позволяют сделать расширенный SQL полнофункциональным языком программирования и повысить эффективность программирования на стороне сервера.
Хотя базовые конструкции языка SQL для большинства СУБД соответствуют стандарту, его постреляционное расширение зависит от диалекта конкретной СУБД. В данном учебно-методическом издании рассмотрен язык Transact SQL, используемый в СУБД Microsoft SQL Server.
Среда Microsoft SQL Server Management Studio
Для создания баз данных, просмотра и редактирования их таблиц, написания и выполнения запросов и серверных программ используется среда Microsoft SQL Server Management Studio, которая является компонентом пакета Microsoft SQL Server.
Для запуска среды выбрать: «Программы → Microsoft SQL Server → Microsoft SQL Server Management Studio». В окне установки соединения указать имя сервера базы данных (по умолчанию совпадает с названием компьютера) и тип авторизации (Windows).
Предварительно следует проверить, что сервер запущен. Для этого используется утилита «SQL Server Configuration Manager», в которой следует выбрать сервер «MSSQLSERVER» из списка установленных серверов и нажать кнопку «Start».
При успешном подключении к серверу баз данных будет открыто окно, в котором слева находится панель проводника «Object Explorer», содержащая список баз данных и их объектов, а справа будут открываться панели для ввода и редактирования запросов (см. рис. 1).
Рис. 1. Окно среды Microsoft SQL Server Management Studio.
Создание новой Базы данных
Для создания новой базы данных в окне проводника «Object Explorer» выбрать пункт «Databases» и в его контекстном меню выбрать «New Database». На открывшейся вкладке ввести название новой БД и нажать ОК.
Для добавления в БД таблицы в окне проводника выбрать нужную БД и в ее контекстном меню выбрать «New Table». В окне конструктора таблицы:
- ввести названия полей таблицы,
- выбрать для каждого поля его тип,
- снять флажки «Allow NULL» для обязательных полей.
Для поля - автоинкремента в окне его свойств «Column Properties» указать в поле «Identity Spetification» значение «yes» и параметры по умолчанию (1,1). Для создания ключа таблицы в списке ее полей выделить нужное поле (или несколько полей) и в контекстном меню выбрать «Set Primary Key». Выбранное поле будет помечено знаком ключа. После завершения конструирования таблицы нажать кнопку сохранения и в окне сохранения указать название создаваемой таблицы. Закрыть окно конструктора таблицы.
Для заполнения таблицы данными в окне проводника выбрать нужную таблицу и в ее контекстном меню выбрать «Edit Top 200 Rows». В том же меню можно перейти к просмотру записей при выборе пункта «Select Top 1000 Rows» или к редактированию структуры таблицы при выборе пункта «Design».
В окне редактирования записей ввести в таблицу нужные значения (рис.2). Поля с автоинкрементыми значения не заполняются. Для обновления значений автоинкрементных полей нажать кнопку выполнения запроса (восклицательный знак).
Рис.2. Окно редактирования записей таблицы.
Для выполнения запросов на языке SQL в окне проводника выбрать нужную БД и нажать кнопку «New Query» для открытия окна запросов. Написать в окне запросов текст запроса, например
select src,dest from roads
и нажать кнопку выполнения «Execute». Результат выполнения запроса будет выведен в нижней части окна запросов ( рис. 1).
Программирование на стороне сервера с применением SQL\PSM
Основой программирования на стороне сервера являются хранимые модули: процедуры и функции. Это - программы, которые хранятся как объекты базы данных и выполняются на сервере баз данных. Выполнение хранимых модулей на сервере позволяет повысить время их выполнения, сократить обмен данными между клиентским приложением и сервером, централизовать бизнес-логику приложения.
Хранимые модули могут содержать любые команды SQL, в том числе извлечения и изменения данных, изменения схемы базы данных или управления транзакциями, а также операторы SQL\PSM. Расширение SQL\PSM позволяет объявлять переменные, задавать условные выражения и циклы, определять и вызывать процедуры и функции. Вызывать можно как функции, созданные пользователями, так и встроенные или системные функции.
СУБД содержит набор встроенных функций для работы со строками, датой и временем, преобразованием форматов, обращения к системным объектам. Сигнатуры и описания встроенных функций и системных процедур можно посмотреть в документации по СУБД.
Базовые конструкции Transact-SQL для реализации SQL\PSM
Перед использованием переменной ее необходимо объявить с помощью ключевого слова «declare»:
declare @имя тип
Имя переменной должно начинаться со знака «@». В качестве типа переменной можно использовать любой тип, поддерживаемой СУБД, в том числе и табличный, например
declare @str nchar(10);
Если переменная имеет табличный тип, то необходимо использовать ключевое слово «table», после которого определить структуру таблицы, например
declare @person table (name nvarchar(100), age int);
Для обращения к табличной переменной используются команды SQL для добавления, изменения, удаления и просмотра записей, например
Insert into @person values('Иванов И.И.', 20);
Присвоение значений переменной выполняется с помощью оператора «set»:
set @переменная=значение;
Значением может быть константа, NULL или запрос на извлечение гарантированно одного значения, заключенный в скобки, например
set @dest = 'Москва';
set @src = (select name from Cities where id=1);
Для ограничения блока кода используются «BEGIN .. END»:
BEGIN
код
END
Кодом считаются любые команды SQL или SQL\PSM.
Условие задается с помощью конструкции «if .. else ..»
if условие
блок кода
else
блок кода
Условием может быть любое условное выражение, аналогичное используемому в части «WHERE», например
if not exists(select * from roads where src=@src and dest=@dest)
Для создания цикла используется оператор «while»:
while условие
блок кода
Команда «break» используется для прерывания цикла и перехода к оператору, следующему за циклом. Команда «continue» используется для перехода к началу цикла.
При возникновении ошибки во время выполнения программы, например, при нарушении ограничений целостности, происходит исключение. Для перехвата и обработки исключения используется конструкция «try .. catch»:
begin try
блок исходного кода
end try
begin catch
код при возникновении исключения
end catch;
Если при выполнении кода, расположенного в секции «begin try .. end try» возникнет исключение, то управление будет передано в блок кода, расположенный в секции «begin catch .. end catch». В противном случае управление будет передано на код, расположенный после «end catch».
Для получения информации о возникшем исключении используются встроенные функции, например, «error_number()» или «error_message()», которые возвращают код или текст возникшей ошибки.
Для прерывания программы и вызова исключения используется встроенная функция «raiserror()»:
raiserror(сообщение_или_код, уровень серьезности, состояние);
Указанные в качестве первого параметра функции сообщение или код возвращаются в вызвавшую программу как код или сообщение об ошибке. Уровень серьезности задает ошибку или предупреждение и определяет, произойдет ли откат выполняемой транзакции. Состояние указывает место возникновения ошибки.
Для возврата значения из хранимого модуля используется оператор «RETURN»:
RETURN значение;
Комментарии задаются оператором «--» в начале комментируемой строки или конструкцией «/* .. */» для создания многострочного комментария.
Команды SQL по работе с данными или схемой базы данных, а также операторы SQL/PSM могут чередоваться в произвольной последовательности. Внутри тех и других допускается использование именованных переменных.
Создание хранимых функций
Хранимые функции подразделяют на скалярные и табличные. Первые возвращают одно значение, вторые – множество значений или кортежей. Синтаксис определения функции:
CREATE FUNCTION схема.название(параметры) RETURNS тип
AS блок_кода
Для скалярной функции в качестве типа возвращаемого значения указывается скалярный тип данных, для табличной – ключевое слово «TABLE». Параметры функции разделяются запятыми и задаются в формате:
@имя тип
Для создания скалярной функции в окне проводника выбрать «Programmability -> Functions -> New -> Scalar-valued Function». Будет открыто окно с шаблоном для создания функции. В этом окне указать название, параметры и код функции.
Пример функция для вычисления количества дорог между городами:
CREATE FUNCTION dbo.countRoad
( @frm nchar(10), -- Входные параметры функции
@tto nchar(10) )
RETURNS int -- тип возвращаемого значения
AS
BEGIN
-- Объявляем переменную
DECLARE @ResultVar int;
-- Задаем значение переменной
set @ResultVar = (select count(*) from roads
where src=@frm and dest=@tto);
-- Возвращаем результат из функции
RETURN @ResultVar;
END
После ввода теста нажать кнопку выполнения «Execute» для сохранения и компиляции функции.
Поскольку скалярная функция возвращает одно значение, то она может вызываться из запроса как поле данных в частях «SELECT» или «WHERE». Ей можно передавать в качестве параметров поля таблиц или константы.
Для выполнения функции открыть окно запроса, написать и выполнить запрос, например:
select distinct src, dest, dbo.countRoad(src,dest) from roads
Для вызова скалярной функции вне запроса с константными параметрами используется конструкция вида:
select dbo.countRoad(‘Москва’,’Тверь’)
Для создания табличной функции в окне проводника выбрать «Programmability -> Functions -> New -> Inline Table-valued Function». Будет открыто окно с шаблоном для создания функции. В этом окне указать название, параметры и код функции.
Пример функции для отображения дорог указанного типа:
CREATE FUNCTION dbo.roadByType(@type nchar(10))
RETURNS TABLE
AS
RETURN
(
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. Множество дорог с одной пересадкой
При этом, в результат запроса не попадут дороги между городами без пересадки и с двумя пересадками. Если выполнить соединение таблицы с самой собой дважды, то получим дороги только с двумя пересадками, и так далее.
Для того, чтобы получить перечень всех возможных путей, включая как сами дороги, так и маршруты с пересадками, необходимо использовать рекурсивный запрос, который работает по следующему принципу:
За базу вычислений принимается таблица или несколько таблиц, записи из которой извлекаются по некоторому условию и заносятся в виртуальную таблицу, которая существует только во время выполнения данного запроса, например извлекаем записи о дорогах:
select src,dest from roads
Итерационно происходит пополнение временной таблицы. На каждой итерации содержимое временной таблицы, полученное на предыдущем шаге, соединяется с содержимым базовой или другой постоянной таблицы по условию соединения и полученный результат добавляется во временную таблицу. Запрос на соединение временной таблицы с постоянной записывается на SQL, например соединение дорог с полученными ранее путями
select A.src, B.dest from roads A join Paths B on A.dest = B.src
Обращение к виртуальной таблице выполняется через псевдоним, действующий в переделах данного запроса, в примере это «Paths».
Процесс вычислений останавливается, если на очередной итерации содержимое временной таблице не изменилось. Это называется достижением фиксированной точки.
После завершения итерационных вычислений к временной таблице формируется запрос, который выполняется один раз и получает результат всего выражения.
Итерационное пополнение временной таблицы называется «индукцией». Объединение базовой и индуктивной частей запроса выполняется командой «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) применительно к типу канала.
Продемонстрировать выполнение транспонирующего запрос, который формирует набор вида: (модем, оптоволокно, спутник, витая пара) и содержит значение максимальной скорости по соответствующему каналу.
СОДЕРЖАНИЕ ОТЧЕТА
Текст SQL запросов для создания таблиц, описания и вызова хранимых модулей, запросов на извлечение данных.
Скриншоты содержимого таблицы и результатов выполнения запросов.
КОНТРОЛЬНЫЕ ВОПРОСЫ
Каковы возможности программирования на стороне сервера баз данных с применением SQL\PSM?
Как создать и заполнять таблицы и выполнять SQL запросы в среде Microsoft SQL Server Management Studio?
Хранимые процедуры и функции. Назначение, синтаксис и способы вызова. Передача параметров. Параметры по умолчанию.
Как создать, изменять и вызывать хранимые процедуры и функции в среде Microsoft SQL Server Management Studio?
Различия между хранимыми процедурами и функциями. Виды хранимых функций.
Конструкции языка Transact-SQL: условие, цикл, перехват исключений, создание исключений, присваивание переменных.
Синтаксис и процесс выполнения рекурсивных запросов.
Синтаксис и назначение транспонирующих запросов.
Синтаксис и назначение ранжирующих функций.
ЛИТЕРАТУРА
Ревунков Г.И. Банки и базы данных: метод. указания по курсу «Банки данных». – М.: Изд-во МГТУ им. Н.Э. Баумана, 2011 г. – 68с.
Пирогов В. SQL Server 2005. Программирование клиент-серверных приложений. - Спб.: БХВ-Петербург, 2006 г. – 336с.
Уолтерс У. и др. SQL Server 2009: ускоренный курс для профессионалов: пер. с англ. – М.: ООО «И.Д. Вильямс», 2009. – 768с.
|
