Лабораторная работа: Знакомства с MS SQL Server

Упрощенный шаблон использования курсора имеет следующий синтаксис:

DECLARE <имя_курсора> CURSOR [FORWARD_ONLY | SCROLL]

FOR <SELECT_оператор> OPEN <имя_курсора>

FETCH <NEXT|PRIOR|FIRST|LAST|ABSOLUTE <число>| RELATIVE <число>>

FROM <имя_курсора> INTO <@переменная> CLOSE <имя_курсора> DEALLOCATE <имя_курсора>

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

После создания курсора, чтобы его использовать, надо открыть курсор с помощью ко- манды OPEN. А команда CLOSE закрывает открытый курсор, высвобождая текущий резуль- тирующий набор и снимая блокировки курсоров для строк.

Для получения определенной строки используется команда FETCH.

Ключевое слово NEXT возвращает следующую строку и перемещает указатель теку- щей строки на возвращенную строку. Если инструкция FETCH NEXT выполняет первую вы- борку в отношении курсора, она возвращает первую строку в результирующем наборе. NEXT является параметром по умолчанию выборки из курсора.

Ключевое слово PRIOR возвращает предыдущую строку и перемещает указатель теку- щей строки на возвращенную строку. Если инструкция FETCH PRIOR выполняет первую вы- борку из курсора, не возвращается никакая строка и положение курсора остается перед первой строкой.

Ключевое слово FIRST возвращает первую строку в курсоре и делает ее текущей. Ключевое слово LAST возвращает последнюю строку в курсоре, делая ее текущей. Ключевое слово ABSOLUTE с аргументом - положительным целым числом, возвращает строку с указанным номером от начала курсора, и делает ее текущей строкой. Если число отрицательное, возвращает строку с указанным номером от конца курсора, делая ее текущей строкой. Ключевое слово RELATIVE с аргументом - положительным целым числом возвращает строку с указанным номером после текущей строки и делает ее текущей строкой. Если число отрицательное, возвращает строку с указанным номером до текущей строки и делает ее теку- щей строкой. Если число равно 0, возвращает текущую строку.

Ключевое слово INTO позволяет поместить данные из столбцов выборки в локальные переменные. Каждая переменная из списка, слева направо, связывается с соответствующим столбцом в результирующем наборе курсора. Типы данных переменных должны соответство- вать типам данных соответствующего столбца результирующего набора. Количество перемен- ных и столбцов тоже должны совпадать. Для обработки результирующего набора построчно можно использовать инструкцию FETCH NEXT в цикле WHILE. Как условие в цикле используется функция @@FETCH_STATUS, которая возвращает состояние последней инструкции FETCH, вызван- ной в любом курсоре, открытом в рамках этого подключения.

Функция @@FETCH_STATUS возвращает одно из четырех значений:

0 Инструкция FETCH была выполнена успешно.

-1 Выполнение инструкции FETCH завершилось неудачно или строка оказалась вне пределов результирующего набора.

-2 Выбранная строка отсутствует.

-9 Курсор не выполняет операцию выборки.

Упрощенный синтаксис использования функции @@FETCH_STATUS имеет следую- щий вид:

FETCH NEXT FROM <имя_курсора> WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM <имя_курсора> END

Практическая часть

Таблица Ученики:

Пример 1: Создайте курсор, содержащий отсортированные по алфавиту фамилии уче- ников и названия их предметов, откройте его, выведите первую строку, закройте и освободите курсор:

DECLARE MyCursor CURSOR FOR

SELECT

Фамилия

,Предмет

FROM

Ученики

ORDER BY

Фамилия OPEN MyCursor FETCH MyCursor

CLOSE MyCursor DEALLOCATE MyCursor

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

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

ID

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

OPEN MyCursor

FETCH ABSOLUTE 5 FROM MyCursor FETCH PRIOR FROM MyCursor FETCH ABSOLUTE -4 FROM MyCursor FETCH RELATIVE -6 FROM MyCursor FETCH RELATIVE 4 FROM MyCursor FETCH NEXT FROM MyCursor

FETCH FIRST FROM MyCursor FETCH LAST FROM MyCursor

CLOSE MyCursor DEALLOCATE MyCursor

Пример 3: С помощью курсора, вычислите среднее арифметическое значение балла у учеников с наибольшим и наименьшим баллом:

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

Баллы

FROM

Ученики

ORDER BY

Баллы

DECLARE @S FLOAT = 0, @B FLOAT

OPEN MyCursor

FETCH FIRST FROM MyCursor INTO @B SET @S = @S + @B

FETCH LAST FROM MyCursor INTO @B SET @S = @S + @B

SET @S = @S / 2 PRINT @S

CLOSE MyCursor DEALLOCATE MyCursor

Пример 4: С помощью курсора, сгенерируйте строку вида «Ученики <список фамилий и названий школ, разделенных запятыми> участвовали в олимпиаде»:

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

Фамилия

,Школа

FROM

Ученики

DECLARE @S VARCHAR(2000), @F VARCHAR(50), @W VARCHAR(50)

OPEN MyCursor

SET @S = 'Ученики'

FETCH NEXT FROM MyCursor INTO @F, @W WHILE @@FETCH_STATUS = 0

BEGIN

SET @S = @S + ', ' + @F + ' из школы "' + @W + '"' FETCH NEXT FROM MyCursor INTO @F, @W

END

SET @S = @S + ' участвовали на олимпиаде.' PRINT @S

CLOSE MyCursor DEALLOCATE MyCursor

Пример 5: Создайте курсор, содержащий список учеников, с его помощью выведите учеников с четной позицией:

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

ID

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

OPEN MyCursor

FETCH ABSOLUTE 2 FROM MyCursor WHILE @@FETCH_STATUS = 0 BEGIN FETCH RELATIVE 2 FROM MyCursor

END

CLOSE MyCursor DEALLOCATE MyCursor

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

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

DECLARE @F VARCHAR(50) DECLARE @P VARCHAR(50) DECLARE @S VARCHAR(50) DECLARE @B FLOAT DECLARE @OB FLOAT = 0

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @F, @P, @S, @B WHILE @@FETCH_STATUS = 0

BEGIN

SELECT

@F AS Фамилия

,@P AS Предмет

,@S AS Школа

,@B AS Баллы

,ABS(@B - @OB) AS Разница SET @OB = @B

FETCH NEXT FROM MyCursor INTO @F, @P, @S, @B

END

CLOSE MyCursor DEALLOCATE MyCursor

Задание

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

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

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

С помощью курсора, вычислите сумму баллов у учеников с наибольшим и наименьшим баллом.

С помощью курсора, сгенерируйте строку вида «Ученики <список фамилий и названий предметов, разделенных запятыми> участвовали в олимпиаде».

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

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

Лабораторная работа 13

Оконные функции

Цель работы

Изучить оконные функции.

Изучить аналитические функции.

Изучить ранжирующие функции.

Изучить функции смещения.

Теоретическая часть

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

Для определения секций используется инструкция OVER. Упрощенный синтаксис имеет следующий вид:

<функция> <столбец для вычислений> OVER ([PARTITION BY <столбец для группи- ровки>] [ORDER BY <столбец для сортировки>])

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

Предложение ORDER BY определяет сортировку строк в каждой секции результирую- щего набора.

Оконные функции разделяются на агрегирующие, ранжирующие и функции смещения. Агрегирующие функции MAX(), MIN(), SUM, AVG(), COUNT() можно применить к секциям результирующего набора запроса. Синтаксис функций имеет следующий вид:

MAX|MIN|SUM|AVG|COUNT(<столбец>) OVER([PARTITION BY <столбцы>] [ORDER BY <столбцы>])

В агрегирующих функциях предложение PARTITION BY можно не указывать, тогда функция обрабатывает все строки результирующего набора запроса как одну группу. Предло- жение ORDER BY определяет последовательность, в которой строкам назначаются уникаль- ные номера. Его также можно не указать.

Ранжирующие функции возвращают ранжирующее значение для каждой строки в сек- ции и являются недетерминированными.

В ранжирующих функциях предложение PARTITION BY можно не указывать, тогда функция обрабатывает все строки результирующего набора запроса как одну группу. Предло- жение ORDER BY определяет последовательность, в которой строкам назначаются уникаль- ные номера. Оно должно указываться обязательно.

Transact-SQL содержит следующие ранжирующие функции:

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

ROW_NUMBER( ) OVER([PARTITION BY <столбцы>] ORDER BY <столбцы>)

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

RANK( ) OVER([PARTITION BY <столбцы>] ORDER BY <столбцы>)

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

DENSE_RANK( ) OVER([PARTITION BY <столбцы>] ORDER BY <столбцы>)

NTILE - распределяет строки упорядоченной секции в заданное количество групп. Группы нумеруются, начиная с единицы. Для каждой строки функция NTILE возвращает но- мер группы, которой принадлежит строка. Синтаксис функции имеет следующий вид:

NTILE(<количество групп>) OVER([PARTITION BY <столбцы>] ORDER BY

<столбцы>)

Функции смещения возвращают значение из другой строки секции результирующего набора запроса. Предложение ORDER BY должно указываться обязательно.

Transact-SQL содержит следующие функции смещения:

Функции LAG() и LEAD() - обращаются к данным из предыдущей или последующей строки того же результирующего набора. Синтаксис функций имеет следующий вид:

LAG|LEAD(<столбец> [,<смещение>] [,<значение по умолчанию>]) OVER([PARTI- TION BY <столбцы>] ORDER BY <столбцы>)

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

«Значение по умолчанию» должно иметь такой же тип данных, как первый параметр, используется, когда «смещение» находится за пределами секции. Если не задано, то возвра- щается NULL.

Функции FIRST_VALUE() и LAST_VALUE() - возвращают первое или последнее зна- чение из упорядоченного набора значений. Синтаксис функций имеет следующий вид:

FIRST_VALUE|LAST_VALUE(<столбец>) OVER([PARTITION BY <столбцы>] OR-

DER BY <столбцы>)

Практическая часть

Таблица Ученики: