Представления. Изменение значений с помощью представлений.

 

Представление – виртуальная таблица, содержащая некоторое определение на основе данных из других таблиц.

Создания и изменения представлений:

{ CREATE| ALTER} VIEW имя_просмотра

[(имя_столбца [,...n])]

[WITH ENCRYPTION]

AS SELECT_оператор

[WITH CHECK OPTION]

Параметр WITH ENCRYPTION предписывает серверу шифровать SQL-код запроса.

Параметр WITH CHECK OPTION предписывает серверу исполнять проверку изменений, производимых через представление, на соответствие критериям, определенным в операторе SELECT. Это означает, что не допускается выполнение изменений, которые приведут к исчезновению строки из представления.

Изменение значений в таблицах с помощью представлений осуществляется след. образом:

INSERT INTO <имя просмотра> VALUES (<значения>)

Представление удаляется командой:

DROP VIEW имя_просмотра [,...n]

 

 

Программные объекты: переменные, временные таблицы.

 

Временные объекты предназначены для хранения промежуточных значений. Существует два типа временных объектов – переменные и таблицы.

Переменная имеет определенный тип данных и используется для хранения, одной величины.

Объявление: DECLARE @локальная_переменная тип_данных

Тип данных может быть любым системным типом данных, исключая text, ntext или image.

Присвоение значений: SET @переменная=значение

Использование табличной переменной:

создание:DECLARE @локальная_переменная TABLE ({определение_таблицы})

присвоение значений: INSERT INTO @табл_пер SELECT поля_таблицы FROM имя_таблицы

Для временного хранения больших объемов информации используются временные таблицы.

Создание и использование временной таблицы:

CREATE TABLE #имя_вр_табл (определение_таблицы)

INSERT INTO #имя_вр_табл (поля_таблицы) VALUES (значения)

SELECT * FROM #имя_вр_табл

DROP TABLE #имя_вр_табл

 

 

Выражения. Управление ходом выполнения.

 

Команды управления:

BEGIN... END — операторные скобки;

IF... ELSE — ветвление алгоритма;

WHILE... CONTINUE — цикл;

USE — оператор переключения базы данных;

WAITFOR — оператор приостановки;

GО — оператор конца пакета команд.

GОТО — оператор перехода;

BEGIN... END предназначена для объединения двух и более команд в единый блок.

IF... ELSE реализуется ветвление алгоритмов.

IF условие

{ запрос }

[ ELSE

{ запрос } ]

WHILE...CONTINUE организуется цикл.

WHILE условие

{ запрос }

[ BREAK ]

{ запрос }

[ CONTINUE ]

Курсоры. Типы курсоров.

 

Курсор – особый временный объект SQL. Предназначен для использования в программах и ХП. Указывает на определённую строчку в результирующем наборе запроса.

Классификация:

1)по месту хранения и принципа работы:
-- курсоры T-SQL

-- курсоры API;

-- курсоры клиента.

2) по способу обращения к данным:

-- последовательные курсоры;

-- прокручиваемые курсоры.

В SS реализовано 4 типа курсора:

1) статические курсоры.

При их создании сервер сохраняет полученные данные в системной БД tempdb. Курсор делает моментальный снимок данных. В статических курсорах никак не отображаются изменения в исходных таблицах, открываются всегда только в режиме readonly.

2) ключевые курсоры.

Копируют в базу tempdb те столбцы, которые уникально идентифицируют каждую строку.

3) последовательные курсоры

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

4) динамический курсор

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

Работа с курсорами.

 

Весь процесс использования курсора можно разбить на пять этапов:

Объявление курсора (declare cursor). Объявление курсора под­разумевает указание его имени и запроса SELECT, который будет использо­ваться для формирования результирующего набора. При объявлении курсора также указывает его тип.

 

DECLARE имя_курсора CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC | FAST_FORWARD][READ_ONLY | SCROLL_LOCKS | OPTIMISTIC][TYPE_WARNING]FOR SELECT_оператор[FOR UPDATE [OF имя_столбца[,...n]]]

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

Если указано ключевое слово GLOBAL, создается глобальный курсор; он существует до закрытия текущего соединения.

При указании FORWARD_ONLY создается последовательный курсор; выборку данных можно осуществлять только в направлении от первой строки к последней.

При указании SCROLL создается прокручиваемый курсор; обращаться к данным можно в любом порядке и в любом направлении.

При указании STATIC создается статический курсор.

При указании KEYSET создается ключевой курсор.

При указании DYNAMIC создается динамический курсор.

Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC.

В курсоре, созданном с указанием аргумента OPTIMISTIC, запрещается изменение и удаление строк, которые были изменены после открытия курсора.

При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT.

 

Открытие курсора (open cursor). В процессе открытия курсора выполняется ассоциированный с кур­сором запрос SELECT.

OPEN {{[GLOBAL]имя_курсора } |@имя_переменной_курсора}

После открытия курсора происходит выполнение связанного с ним оператора SELECT, выходные данные которого сохраняются в многоуровневой памяти.

 

Выборка данных из курсора и использование курсора для изменения исходных данных (fetch). После того, как курсор открыт, пользователь может приступать к выборке данных из этого курсора. В зависимости от типа созданного курсора также с помощью курсора возможно изменение исходных данных.

FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {номер_строки| @переменная_номера_строки} | RELATIVE {номер_строки | @переменная_номера_строки}] FROM ]{{[GLOBAL ]имя_курсора }| @имя_переменной_курсора } [INTO @имя_переменной [,...n]]

При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора, которая становится текущей строкой.

При указании LAST возвращается самая последняя строка курсора. Она же становится текущей строкой.

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

Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.

Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора. Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.

Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.

Чтобы открыть глобальный курсор, перед его именем требуется указать ключевое слово GLOBAL. Имя курсора также может быть указано с помощью переменной.

В конструкции INTO @имя_переменной [,...n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре, а тип данных переменной – типу данных в столбце курсора. Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды SELECT – данные выводятся на экран.

 

Закрытие курсора (close cursor). Закрытие курсора подразумевает освобождение выделенного для него пространства в системной базе данных Tempdb. После закрытия кур­сора пользователь не сможет обратиться ни к одной строке.

Освобождение курсора (release cursor). Закрытый курсор может быть удален. Удаление курсора подразумевает удаление из оперативной памяти описания курсора как объекта. Если курсор закрыт, но не удален, он может быть по­вторно открыт для использования. При этом в него будет помещен новый набор строк. Можно удалить только закрытый курсор.