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

Текст из файла

Методические указания к лабораторной работе «Постреляционное расширение языка SQL » по дисциплине «Постреляционные базы данных»

доц. к.т.н. Виноградова М.В.

доц. к.т.н. Максаков А.А.

доц. к.т.н. Черников В.А.

ВВЕДЕНИЕ

Учебно-методическое издание «Постреляционное расширение языка SQL» представляет собой методические указания к лабораторным работам по дисциплине «Постреляционные базы данных». В теоретической части учебно-методического издания рассмотрены конструкции языка Transact-SQL, являющегося постреляционным расширением языка SQL для СУБД Microsoft SQL Server. Приведено краткое описание языковых конструкций для создания хранимых функций и процедур. Рассмотрены примеры рекурсивных, транспонирующих и ранжирующих запросов. В заключительной части методических указаний приведены контрольные вопросы, список рекомендуемой литературы, требования к отчету по лабораторной работе и пример задания.

Ознакомившись с методическими указаниями и разобрав приведенные в нем примеры, студент может получить у преподавателя свой вариант задания и приступить к его выполнению. Целью лабораторной работы является практическое изучение возможностей постреляционного расширения языка SQL для создания хранимых процедур и функций и выполнения рекурсивных и транспонирующих запросов на примере Transact-SQL в среде Microsoft SQL Server Management Studio.

Оглавление

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ 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

1. Перед использованием переменной ее необходимо объявить с помощью ключевого слова «declare»:

declare @имя тип

Имя переменной должно начинаться со знака «@». В качестве типа переменной можно использовать любой тип, поддерживаемой СУБД, в том числе и табличный, например

declare @str nchar(10);

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

declare @person table (name nvarchar(100), age int);

Для обращения к табличной переменной используются команды SQL для добавления, изменения, удаления и просмотра записей, например

Insert into @person values('Иванов И.И.', 20);

1. Присвоение значений переменной выполняется с помощью оператора «set»:

set @переменная=значение;

Значением может быть константа, NULL или запрос на извлечение гарантированно одного значения, заключенный в скобки, например

set @dest = 'Москва';

set @src = (select name from Cities where id=1);

1. Для ограничения блока кода используются «BEGIN .. END»:

BEGIN

код

END

Кодом считаются любые команды SQL или SQL\PSM.

1. Условие задается с помощью конструкции «if .. else ..»

if условие

блок кода

else

блок кода

Условием может быть любое условное выражение, аналогичное используемому в части «WHERE», например

if not exists(select * from roads where src=@src and dest=@dest)

1. Для создания цикла используется оператор «while»:

while условие

блок кода

Команда «break» используется для прерывания цикла и перехода к оператору, следующему за циклом. Команда «continue» используется для перехода к началу цикла.

1. При возникновении ошибки во время выполнения программы, например, при нарушении ограничений целостности, происходит исключение. Для перехвата и обработки исключения используется конструкция «try .. catch»:

begin try

блок исходного кода

end try

begin catch

код при возникновении исключения

end catch;

Если при выполнении кода, расположенного в секции «begin try .. end try» возникнет исключение, то управление будет передано в блок кода, расположенный в секции «begin catch .. end catch». В противном случае управление будет передано на код, расположенный после «end catch».

Для получения информации о возникшем исключении используются встроенные функции, например, «error_number()» или «error_message()», которые возвращают код или текст возникшей ошибки.

1. Для прерывания программы и вызова исключения используется встроенная функция «raiserror()»:

raiserror(сообщение_или_код, уровень серьезности, состояние);

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

1. Для возврата значения из хранимого модуля используется оператор «RETURN»:

RETURN значение;

1. Комментарии задаются оператором «--» в начале комментируемой строки или конструкцией «/* .. */» для создания многострочного комментария.

Команды 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

(

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

Тип файла документ

Документы такого типа открываются такими программами, как Microsoft Office Word на компьютерах Windows, Apple Pages на компьютерах Mac, Open Office - бесплатная альтернатива на различных платформах, в том числе Linux. Наиболее простым и современным решением будут Google документы, так как открываются онлайн без скачивания прямо в браузере на любой платформе. Существуют российские качественные аналоги, например от Яндекса.

Будьте внимательны на мобильных устройствах, так как там используются упрощённый функционал даже в официальном приложении от Microsoft, поэтому для просмотра скачивайте PDF-версию. А если нужно редактировать файл, то используйте оригинальный файл.

Файлы такого типа обычно разбиты на страницы, а текст может быть форматированным (жирный, курсив, выбор шрифта, таблицы и т.п.), а также в него можно добавлять изображения. Формат идеально подходит для рефератов, докладов и РПЗ курсовых проектов, которые необходимо распечатать. Кстати перед печатью также сохраняйте файл в PDF, так как принтер может начудить со шрифтами.

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