Представления и табличные объекты
Цель работы
Изучить создание и удаление представлений.
Изучить табличные переменные.
Изучить временные таблицы.
Изучить производные таблицы.
Теоретическая часть
Представление - это виртуальная таблица, содержимое которой определяется запро- сом. Как и таблица, представление состоит из ряда именованных столбцов и строк данных.
Представления, как таблицы, могут иметь до 1024 столбцов.
Запрос для создания представления может обращаться не более чем к 256 таблицам.
Можно создавать представления на основе других представлений, при этом уровень вложенности не может быть больше 32-х.
Представление можно использовать как обычную таблицу. Упрощенный синтаксис создания преставления имеет следующий вид:
CREATE VIEW <название> <список столбцов> AS <запрос SELECT>
Запрос SELECT, используемый в определении представления, не может включать пред- ложение ORDER BY, если только в списке выбора инструкции SELECT нет также предложе- ния TOP.
Для удаления представления используется команда DROP VIEW, его синтаксис: DROP VIEW <название>
В Transact-SQL есть специальный тип данных для хранения результирующего набора для обработки в будущем. Его используют в основном для временного хранения набора строк, возвращаемых как результирующий набор функций с табличным значением. Функции и пере- менные могут быть объявлены как табличные переменные. Табличные переменные могут ис- пользоваться в функциях, хранимых процедурах и пакетах. Для объявления табличных пере- менных используется следующий синтаксис:
DECLARE <@название переменной> TABLE (<объявление столбцов>)
Табличная переменная ведет себя как локальная переменная, она имеет точно опреде- ленную область применения.
Табличная переменная может быть применена в любом месте, где используется таблица или табличное выражение в инструкциях SELECT, INSERT, UPDATE и DELETE. Но таблич- ную переменную нельзя использовать в инструкции SELECT … INTO …
Табличные переменные автоматически очищаются в конце функции, хранимой проце- дуры или пакета, в котором они были определены.
Операция присвоения между табличными переменными не поддерживается.
В MS SQL Server для хранения промежуточных данных можно использовать времен- ные таблицы. Они по поведению не отличаются от базовых таблиц. Создание, удаление и об- ращение к ним аналогично к базовым.
Первый символ в названии временной таблицы должен быть знак решетки #. Для ло- кальных временных таблиц используется один знак #. Локальные временные таблицы до- ступны в течение текущей сессии и удаляются, когда пользователь отсоединяется от сервера. Для глобальных временных таблиц используются два знака ##. Глобальные временные таблицы доступны всем открытым сессиям базы данных и удаляются, когда все пользователи, ссылающиеся на таблицы, отсоединяются от сервера.
Временные таблицы хранятся в системной базе данных TEMPDB.
Для принудительного удаления временных таблиц используется команда DROP TABLE.
В MS SQL Server можно создать временно именованный результирующий набор, назы- ваемый обобщенным табличным выражением. Он формируется при выполнении простого за- проса.
За обобщенным табличным выражением должны следовать одиночные инструкции SELECT, INSERT, UPDATE или DELETE, ссылающиеся на некоторые или на все столбцы.
Обобщенные табличные выражения хранятся в оперативной памяти и существуют только во время первого выполнения запроса, который представляет эту таблицу.
Практическая часть
Пример 1: Создайте представление, содержащее список стран, население которых меньше 1 млн. чел., а площадь больше 100 тыс. кв. км, и используйте его:
CREATE VIEW Пример1 AS
SELECT
Название
,Столица
,Площадь
,Население
,Континент
FROM
Страны
WHERE
Население < 1000000 AND
Площадь > 100000
SELECT
Название
,Столица
,Площадь
,Население
,Континент
FROM
Пример1
Пример 2: Создайте представление, содержащее список континентов, суммарную пло- щадь и суммарное население стран, которые находятся на каждом континенте и используйте его:
CREATE VIEW Пример2 (
Континент
,Площадь
,Население
) AS
SELECT
Континент
,SUM(Площадь)
,SUM(Население)
FROM
Страны
GROUP BY
Континент
SELECT
Континент
,Площадь
,Население
FROM
Пример2
Пример 3: Создайте представление, содержащее фамилии преподавателей, должность, каждого преподавателя, звание, степень, место работы, зарплату и используйте его:
CREATE VIEW Пример3 (
Фамилия
,Должность
,Звание
,Степень
,Кафедра
,Зарплата
) AS
SELECT
Фамилия
,Должность
,Звание
,Степень
,Название
,Зарплата
FROM
Сотрудник С
INNER JOIN Преподаватель П ON С.Таб_номер = П.Таб_номер INNER JOIN Кафедра К ON С.Шифр = К.Шифр
SELECT
Фамилия
,Должность
,Звание
,Степень
,Кафедра
,Зарплата
FROM
Пример3
Пример 4: Создайте табличную переменную, содержащую три столбца («Номер не- дели», «Дата начала», «Дата конца»). Заполните ее для текущего года и используйте:
DECLARE @Пример4 TABLE (
[Номер недели] INT, [Дата начала] DATE, [Дата конца] DATE
)
DECLARE @T AS DATE, @N INT = 1
SET @T = CAST(YEAR(GETDATE()) AS CHAR(4)) + '0101' WHILE DATEPART(WEEKDAY, @T) > 1
SET @T = DATEADD(DAY, -1, @T) PRINT DATEPART(WEEK, @T)
WHILE YEAR(@T) < YEAR(DATEADD(YEAR, 1, GETDATE())) BEGIN
INSERT
@Пример4 VALUES
(@N, @T, DATEADD(DAY, 6, @T))
END
SET @T = DATEADD(DAY, 7, @T) SET @N = @N + 1
SELECT
[Номер недели]
,[Дата начала]
,[Дата конца]
FROM
@Пример4
Пример 5: Создайте табличную переменную, содержащую список стран, площадь ко- торых в 1000 раз меньше, чем средняя площадь стран в мире и используйте:
DECLARE @Пример5 TABLE (
Название VARCHAR(50), Столица VARCHAR(50), Площадь FLOAT, Население BIGINT, Континент VARCHAR(50)
)
INSERT INTO
@Пример5 SELECT
Название
,Столица
,Площадь
,Население
,Континент
FROM
Страны
WHERE
Площадь * 1000 < (
SELECT
AVG(Площадь)
SELECT
Название
,Столица
,Площадь
,Население
,Континент
FROM
)
Страны
FROM
@Пример5
Пример 6: Создайте локальную временную таблицу, имеющую три столбца («Название месяца», «Количество экзаменов», «Количество студентов»), заполните и используйте ее:
SELECT
DATENAME(MONTH, Дата) AS [Название месяца]
, COUNT(DISTINCT Код) AS [Количество экзаменов]
, COUNT(DISTINCT Рег_номер) AS [Количество студентов]
INTO FROM
#Пример6 Экзамен
GROUP BY
DATENAME(MONTH, Дата)
SELECT * FROM #Пример6
Пример 7: Создайте глобальную временную таблицу, содержащую название стран и плотность их населения, заполните и используйте ее:
CREATE TABLE ##Пример7 (
Название VARCHAR(50), Плотность FLOAT
)
INSERT INTO
##Пример7
(Название, Плотность)
SELECT
Название, ROUND(Население / Площадь, 0) AS Плотность
FROM
Страны
SELECT * FROM ##Пример7 DROP TABLE #Пример6
Пример 8: С помощью обобщенных табличных выражений, напишите запрос для вы- вода списка сотрудников, чьи зарплаты меньше, чем средняя зарплата по кафедре, их зарплаты и название кафедры:
WITH СЗК AS (
SELECT
К.Название AS Кафедра
,К.Шифр
,AVG(Зарплата) AS [Средняя зарплата по кафедре]
FROM
Сотрудник С
INNER JOIN Кафедра К ON С.Шифр = К.Шифр
GROUP BY
К.Название, К.Шифр
) SELECT
С.Фамилия
, С.Зарплата
, З.Кафедра
, З.[Средняя зарплата по кафедре]
FROM
Сотрудник С
INNER JOIN СЗК З ON С.Шифр = З.Шифр
WHERE
С.Зарплата < З.[Средняя зарплата по кафедре]
Задание
Создайте представление, содержащее список африканских стран, население которых больше 10 млн. чел., а площадь больше 500 тыс. кв. км, и используйте его.
Создайте представление, содержащее список континентов, среднюю площадь стран, которые находятся на нем, среднюю плотность населения, и используйте его.
Создайте представление, содержащее фамилии преподавателей, их должность, зва- ние, степень, место работы, количество их экзаменов, и используйте его.
Создайте табличную переменную, содержащую три столбца («Номер месяца»,
«Название месяца», «Количество дней»), заполните ее для текущего года, и используйте ее.
Создайте табличную переменную, содержащую список стран, площадь которых в 100 раз меньше, чем средняя площадь стран на континенте, где они находятся, и используйте ее.
Создайте локальную временную таблицу, имеющую три столбца («Номер недели»,
«Количество экзаменов», «Количество студентов»), заполните и используйте ее.
Создайте глобальную временную таблицу, содержащую название континентов, наибольшую и наименьшую площадь стран на них, заполните и используйте ее.
С помощью обобщенных табличных выражений напишите запрос для вывода списка сотрудников, чьи зарплаты меньше, чем средняя зарплата по факультету, их зарплаты и назва- ние факультета.
Напишите команды для удаления всех созданных вами представлений.
Лабораторная работа 12
Курсоры
Цель работы
Изучить использование курсоров.
Изучить прокрутку курсоров.
Изучить использование циклов в курсорах.
Изучить использование переменных в курсорах.
Теоретическая часть
Инструкции Transact-SQL выполняют операции над множествами, но интерактивным приложениям иногда требуется обрабатывать результаты построчно. Для выполнения команд над отдельной строкой предусмотрены курсоры. Открытие курсора в результирующем наборе делает возможной его построчную обработку. Можно присвоить курсор переменной или па- раметру с типом данных CURSOR.
Курсоры позволяют усовершенствовать обработку результатов: позиционируясь на отдельные строки результирующего набора;
получая одну или несколько строк от текущей позиции в результирующем наборе; поддерживая изменение данных в строках в текущей позиции результирующего набора;
поддерживая разные уровни видимости изменений, сделанных другими пользова-
телями для данных, представленных в результирующем наборе;
предоставляя инструкциям Transact-SQL в скриптах, хранимых процедурах и триг- герах доступ к данным результирующего набора.
Обычно курсоры используются для выбора из базы данных некоторого подмножества хранимой в ней информации. В каждый момент времени прикладной программой может быть проверена одна строка курсора. Курсоры часто применяются в операторах SQL, встроенных в написанные на языках процедурного типа прикладные программы. Некоторые из них неявно создаются сервером базы данных, в то время как другие определяются программистами.
В соответствии со стандартом SQL при работе с курсорами можно выделить следую- щие основные действия:
создание или объявление курсора;
открытие курсора, т.е. наполнение его данными, которые сохраняются в много- уровневой памяти;
выборка из курсора и изменение с его помощью строк данных;
программ;
закрытие курсора, после чего он становится недоступным для пользовательских
освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие
необязательно освобождает ассоциированную с ним память.
После освобождения курсора ассоциированная с ним память также освобождается. При этом становится возможным повторное использование его имени.
В некоторых случаях применение курсора неизбежно. Однако по возможности этого следует избегать и работать со стандартными командами обработки данных: SELECT, UPDATE, INSERT, DELETE. Помимо того, что курсоры не позволяют проводить операции изменения над всем объемом данных, скорость выполнения операций обработки данных по- средством курсора заметно ниже, чем у стандартных средств Transact-SQL.
SQL Server поддерживает четыре типа курсоров:
Однонаправленный курсор - указывается как FORWARD_ONLY и не поддерживает прокрутку. Он также называется курсором FIREHOSE и поддерживает только получение строк последовательно, от начала до конца курсора. Строки нельзя получить из базы данных, пока они не будут выбраны. Результаты всех инструкций INSERT, UPDATE и DELETE, вли- яющих на строки результирующего набора (выполненных текущим пользователем или зафик- сированных другими пользователями), отображаются как строки, выбранные из курсора.
Статический курсор - полный результирующий набор статического курсора создается в базе данных TEMPDB при открытии курсора. Статический курсор всегда отображает резуль- тирующий набор точно в том виде, в котором он был при открытии курсора. Статическими курсорами обнаруживаются лишь некоторые изменения или не обнаруживаются вовсе, но при этом в процессе прокрутки такие курсоры потребляют сравнительно мало ресурсов. Статиче- ские курсоры всегда доступны только для чтения.
KEYSET курсор - членство и порядок строк в курсоре, управляемом набором ключей, являются фиксированными при открытии курсора. Такие курсоры управляются с помощью набора уникальных идентификаторов - ключей. Ключи создаются из набора столбцов, кото- рый уникально идентифицирует строки результирующего набора.
Динамический курсор - это противоположность статических курсоров. Динамические курсоры отражают все изменения строк в результирующем наборе при прокрутке курсора. Значения типа данных, порядок и членство строк в результирующем наборе могут меняться для каждой выборки. Все инструкции UPDATE, INSERT и DELETE, выполняемые пользова- телями, видимы посредством курсора.