Курсоры. Необходимость их применения. Синтаксис описания (подробно). Общая схема работы с курсором. Команды и функции для работы с курсором. Примеры

1. Объявление курсора с помощью DECLARE или DECLARE и SET.

2. Открытие курсора (OPEN).

3. Извлечение данных из курсора (FETCH).

4. Закрытие курсора (CLOSE).

5. Удаление ссылки на курсор. (DEALLOCATE). Может применяться без CLOSE.

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL]область видимости - область действия

[FORWARD_ONLY | SCROLL]порядок извлечения данных

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]свойства связи с таблицей

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]обновление данных

[TYPE_WARNING]неявное преобразование типа курсора

FOR <команда SELECT>выборка

[FOR UPDATE [OF список_имен_столбцов]]список столбцов, которые могут обновлятся

OPEN служит для открытия курсора, а также для наполнения его данными (выполнения соответствующего запроса SELECT). OPEN {[GLOBAL] <имя курсора> | @cursor_variable}

FETCH служит для извлечения строки из курсора и имеет следующий синтаксис:

FETCH[ [NEXT | PRIOR | FIRST | LAST | определяет извлекаемую из курсора строку

ABSOLUTE {номер строки | @var} |

RELATIVE {номер строки | @var} ] FROM]

{[GLOBAL] <имя курсора> | @cursor_variable}

[INTO @var1, @var2, …] позволяющая выводить значения элементов извлекаемой строки не на экран, а в переменные

CLOSE Закрывает указанный открытый курсор, освобождая все блокировки данных сделанные курсором и выбранное курсором множествострок.CLOSE {[GLOBAL] <имя курсора> | @cursor_variable}

DEALLOCATEУдаляет ссылки на указанный курсор. Когда последняя ссылка на курсор удаляется, то это приводит к удалению всех структур данных, используемых курсором. Может применяться без команды CLOSE. DEALLOCATE {[GLOBAL] <имя курсора> | @cursor_variable}

1. @@CURSOR_ROWS – возвращает количество строк, содержащихся в последнем открытом в текущем соединении курсоре. Может вернуть такие значения:

· некоторое отрицательное число. Показывает, что этот курсор наполняется данными асинхронно. Может иметь место для больших KEYSET или STATIC курсоров.

· -1. Возвращается для динамического курсора (DYNAMIC). Т.к. этот курсор отображает все изменения в исходных данных, то количество содержащихся в нем строк неизвестно.

· 0. Курсор еще не открыт, уже закрыт или ни одна строка для него не выбрана.

· Некоторое положительное число. Количество строк, извлеченных в курсор.

2. CURSOR_STATUS({{‘local’ | ‘global’}, ‘имя курсора’ | ‘variable’, ‘@cursor_var’}) – возвращает состояние курсора. Имеет 2 строковых аргумента: 1-ый имеет значение ‘local’ или ‘global’ для курсоров, заданных именем (2-ой параметр) или ‘variable’ – для курсоров заданных переменной (2-ой параметр). Может возвращать следующие значения:

· 1. Показывает, что этот курсор открыт и имеет хотя бы 1 строку (для DYNAMIC курсоров 0 или более строк).

· 0. Курсор открыт, но не содержит строк. Это значение не может быть возвращено для DYNAMIC курсоров.

· -1. Курсор закрыт.

· -2. Может быть возвращено, если курсор задан переменной. Означает, что курсор не связан с переменной.

· -3. Такого курсора или переменной не существует или переменная существует, но не связана с курсором.

3. @@FETCH_STATUS – возвращает результат выполнения последней команды FETCH (целое число):

· 0. Команда FETCH выполнена успешно.

· -1. Команда FETCH не могла быть выполнена успешно или заданная строка выходит за границы имеющегося множества строк.

-2. Извлекаемая строка отсутствует.

13. Триггеры. Необходимость их применения. Синтаксис команды создания / изменения DMLтриггеров (подробно). Синтаксис команды удаления триггера (подробно). Особенности написания триггеров, реагирующих на несколько событий (функции COLUMNSUPDATEDи UPDATE). Использование таблиц inserted и deleted. Порядок срабатывания триггеров.

Триггер – это хранимая процедура, которая начинает свою работу в случае выполнения действия, на которое триггер настроен. Триггеры применяются для решения задач поддержания целостности (корректности) данных, когда по каким-либо причинам невозможно (неудобно) использовать ограничения FOREIGN KEY (или ограничений на значения столбцов), и безопасности, когда, например, недопустимы какие-либо изменения в данных. Триггеры бывают нескольких типов: DML, DDL и LOGON. DML триггеры могут срабатывать при выполнении (после выполнения или вместо выполнения) команд INSERT, UPDATE и DELETE для таблиц или представлений. Этот тип триггеров присутствовал и в MS SQL Server 2000. DDL триггеры могут срабатывать при выполнении после выполнения команд CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS и некоторых системных процедур. LOGON триггеры срабатывают после установки соединения с MS SQL Server. DDL и LOGON триггеры можно использовать только в MS SQL Server 2005

CREATE TRIGGER [пространство_имен.]название_триггера

ON {название_таблицы | название_представления}

[WITH

{ ENCRYPTION |

EXEC[UTE] AS {CALLER | SELF | OWNER | ‘имя_пользователя’} }[,]]

{ FOR [AFTER] | INSTEAD OF}

{[INSERT] [,] [UPDATE] [,] [DELETE]}

[WITH APPEND]

[NOT FOR REPLICATION]

{ Перечень_команд_SQL |

EXTERNAL NAME имя_сборки.имя_класса.название_метода [;]}

При написании тела DML триггеров можно использовать специальные функции COLUMNS_UPDATED() и UPDATE(имя_столбца), а также две специальные таблицы inserted и deleted. Их применение позволяет определить, какое действие вызвало активацию триггера, если триггер может быть активирован различными командами. В случае же использования INSTEAD OF триггера данные в таблице или представлении не изменяются, так как триггер вызван вместо активировавшего его действия. Использование же таблиц inserted и deleted позволяет определить, как данные должны были бы измениться.

Функция COLUMNS_UPDATED() возвращает значение типа varbinary, представляющее собой набор байт, каждый из которых представляет информацию об изменении значения группы из 8-ми столбцов. При этом 1-ый байт представляет эту информацию для 1-ой группы из 8-ми столбцов и т.д. В каждом байте младший бит (нулевой) представляет информацию об изменении 1-ого столбца группы, 1-ый бит – 2-ого столбца и т.д. Если какой-либо столбец изменился, то соответствующий ему бит будет равен 1, иначе 0. Функция COLUMNS_UPDATED() выдает одни и те же значения независимо от того, изменилось ли в результате выполнения команды UPDATE хотя бы одно значение в обновляемых столбцах или нет. При активации триггера командой INSERT функция COLUMNS_UPDATED() возвращает значение, соответствующее изменению ВСЕХ столбцов таблицы или представления.

Функция UPDATE(имя_столбца) ведет себя аналогично функции COLUMNS_UPDATED(), однако имеет следующие отличия: применима только к одному столбцу и возвращает значение TRUE, если столбец изменился, и FALSE – в противном случае. Эту функцию можно последовательно применять в уловном операторе IF для проверки изменения нескольких столбцов.

Таблицы inserted и deleted являются временными хранящимися в памяти таблицами, создаваемыми при запуске DML триггера автоматически. Их структуру и данные в них нельзя изменять, создавать индексы для этих таблиц также недопустимо, их использование возможно ТОЛЬКО в теле DML триггеров. Таблица deleted содержит строки, которые были удалены или строки, которые были обновлены командой UPDATE, причем в последнем случае для обновляемых столбцов содержатся их первоначальные значения. Таблица inserted содержит добавляемые в таблицу строки (команда INSERT) или обновляемые строки, причем в последнем случае в обновляемых столбцах этой таблицы содержатся новые значения. Следует отметить, что для INSTEAD OF триггеров получить измененные/добавленные значения можно получить ТОЛЬКО из таблицы inserted, а набор удаляемых строк – ТОЛЬКО из таблицы deleted. Если в результате выполнения какой-либо из команд, которая должна была привести к срабатыванию триггера, ни одна строка (значение в строке) в таблице (представлении) не была изменена, то триггер все равно будет вызван. При этом количество строк в таблицах inserted и deleted будет равно 0.

@order –определяет последовательность срабатывания триггера, имеет тип данных varchar(10) и может принимать значения: ‘First’ – триггер срабатывает первым, ‘Last’ – триггер срабатывает последним, ‘None’ – порядок срабатывания триггера не определен. Следует отметить, что первый и последний триггеры должны ОБЯЗАТЕЛЬНО отличаться и можно описать только один первый и один последний триггеры для какого-либо действия.

@stmttype –описывает тип действия, для которого устанавливает порядок выполнения триггеров, имеет тип данных varchar(50) и может иметь следующие значения: ‘INSERT’, ‘UPDATE’, ‘DELETE’, ‘LOGON’ или название любого из типов событий, допустимых при создании DDL триггера.

@namespace –задает уровень DDL триггера: DATABASE или SERVER. Для LOGON триггеров можно указать только SERVER, для DML триггеров – либо не указывать последний параметр, либо указать значение NULL.

Следует отметить, что при изменении триггера, имеющего атрибут First или Last командой ALTER TRIGGER, этот атрибут автоматически сбрасывается и требует своей повторной установки с помощью процедуры sp_settriggerorder.

Триггеры. Необходимость их применения. Примеры DMLтриггеров реагирующих на одно и несколько событий. Способы определения события, активировавшего триггер. Ограничения на команды, содержащиеся в теле DMLтриггера.

Создание простейшего AFTER триггера, который срабатывает при выполнении команды DELETE и в случае удаления одной из строк таблицы, содержащих в столбце c1 значение 1, удаляет все такие строки в таблице. Кроме этого возникает проблема, связанная с проверкой того, существует ли такой триггер в данном пространстве имен.

Перед созданием триггера необходимо убедиться, что такого триггера в используемом пространстве имен. Для этого необходимо сделать соответствующий запрос к системной таблице sysobjects, в котором необходимо выбрать строку, содержащую информацию об объекте с именем trig1, тип которого TR (триггер). Кроме этого поле uid (номер схемы) должно соответствовать номеру нужного пространства имен. Этот номер можно получить путем выборки из системного представления sys.schemas поля schema_id у строки, которая содержит в поле name соответствующее название пространства имен.

if exists(select * from sysobjects where name = 'trig1' and

uid = (select schema_id from sys.schemas where name = 'dbo') and type = 'TR')

Drop trigger trig1 go

Правила, по которым можно определить какое действие активировало триггер, если триггер может быть активирован несколькими командами:

1. Если функция COLUMNS_UPDATED() вернула значение 0, то триггер был активирован командой удаления(DELETE), соответственно количество строк в таблице deleted будет соответствовать количеству удаляемых строк.

2. Если функция COLUMNS_UPDATED() вернула значение не 0, то триггер был активирован либо командой INSERT, либо командой UPDATE.

2.1. Если в таблице inserted - ненулевое количество строк, а таблица deleted – не содержит строк, то триггер был активирован командой INSERT.

2.2. Если таблицы inserted и deleted содержат одинаковое ненулевое количество строкили таблицы inserted и deleted содержат нулевое количество строк и функция COLUMNS_UPDATED() вернула некоторое значение , удовлетворяющее неравенству , где - количество столбцов в таблице или в представлении, то триггер был активирован командой UPDATE.

2.3.Если таблицы inserted и deleted содержат нулевое количество строк и функция COLUMNS_UPDATED() вернула некоторое значение , такое что , то определить какая из команд INSERT или UPDATE активировала триггер не представляется возможным, т.к. команда UPDATEможет изменять значения во всех столбцах, а команда INSERT может не вставить ни одной строки.

15. Триггеры. Необходимость их применения. Синтаксис команды созданий / изменения DDLтриггеров (подробно). Синтаксис команды удаления триггера (подробно). Особенности написания триггеров, реагирующих на несколько событий (функция EVENTDATA). Примеры триггеров.

DML триггер:

DROP TRIGGER пространство_имен.имя_триггера1[, пространство_имен.имя_триггера2…] [;]

DDL триггер:

DROP TRIGGER имя_триггера1[, имя_триггера2…]

ON {DATABASE | ALL SERVER}

[;]

LOGON триггер:

DROP TRIGGER имя_триггера1[, имя_триггера2…]

ON ALL SERVER

причем ключевое слово DATABASE показывает, что DDL триггер(ы) имеют уровень БД, а ключевое слово ALL SERVER показывает, что DDL триггер(ы) имеют уровень сервера.

Следует отметить, что:

1. Уничтожение таблицы уничтожает и все связанные с ней DML триггеры.

2. Когда триггер уничтожается, то информацию о нем из представлений sys.objects, sys.triggers, sys.sql_modules также удаляется.

3. Несколько DDL триггеров можно удалить одной командой DROP TRIGGER в том случае если все они имеют или уровень БД, или уровень сервера.

Для того чтобы переименовать триггер, необходимо выполнить соответствующие команды DROP TRIGGER и CREATE TRIGGER

16. Триггеры. Необходимость их применения. Синтаксис команды создания / изменения LOGONтриггеров (подробно). Синтаксис команды удаления триггера (подробно). Пример триггера.

Создание:

LOGON триггер:

CREATE TRIGGER название_триггера

ON ALL SERVER

[WITH

{ ENCRYPTION |

EXEC[UTE] AS {CALLER | SELF ‘login_name’}

}[,]

]

{ FOR [AFTER]} LOGON

{

Перечень_команд_SQL |

EXTERNAL NAME имя_сборки.имя_класса.название_метода [;]

}

Удаление:

DML триггер:

DROP TRIGGER пространство_имен.имя_триггера1[, пространство_имен.имя_триггера2…] [;]

DDL триггер:

DROP TRIGGER имя_триггера1[, имя_триггера2…]

ON {DATABASE | ALL SERVER}

[;]

LOGON триггер:

DROP TRIGGER имя_триггера1[, имя_триггера2…]

ON ALL SERVER

Следует отметить, что:

4. Уничтожение таблицы уничтожает и все связанные с ней DML триггеры.

5. Когда триггер уничтожается, то информацию о нем из представлений sys.objects, sys.triggers, sys.sql_modules также удаляется.

6. Несколько DDL триггеров можно удалить одной командой DROP TRIGGER в том случае если все они имеют или уровень БД, или уровень сервера.

Для того чтобы переименовать триггер, необходимо выполнить соответствующие команды DROP TRIGGER и CREATE TRIGGER

17. Триггеры. Необходимость их применения. Разрешение / запрещение срабатывания различных типов триггеров (команды ENABLE/ DISABLEи команда ALTERTABLE, применяемая для разрешения / запрещения срабатывания триггеров). Особенности использования этих команд. Рекурсивное срабатывание триггеров и способы борьбы с этим явлением. Примеры.

Рассмотрим команды разрешения | запрещения срабатывания триггеров.

{ENABLE | DISABLE} TRIGGER

{[пространство_имен.]имя_триггера1[,[пространство_имен.]имя_триггера1…] | ALL}

ON {имя_объекта | DATABASE | ALL SERVER} [;]

причем:

1. Пространство_имен можно указать только для DML триггеров.

2. Ключевое слово ALL показывает, что все триггеры указанного типа (тип указывается после ключевого слова ON) будут разрешены или запрещены для выполнения.

3. После ключевого слова ON можно указать либо имя_таблицы|имя_представления для DML триггеров, либо ключевое слово DATABASE для DDL триггеров уровня БД, либо ключевое слово ALL SERVER для DDL триггеров уровня сервера или для LOGON триггеров.

4. Команда DISABLE TRIGGER, не может использоваться в теле триггера, состояние которого она должна изменить, если в нем происходит выполнение действия, приводящего к повторной активации триггера в явном виде (без использования команды EXECUTE или процедуры sp_executesql). Следует также учитывать, что команды {ENABLE | DISABLE} TRIGGER, должны быть первыми в пакете команд (находиться сразу после begin или go).

Для разрешения | запрещения выполнения DML триггеров применительно к таблицам также можно использовать команду ALTER TABLE, которая в этом случае имеет следующий вид:

ALTER TABLE [ имя_БД.[пространство_имен]. | пространство_имен.] имя_таблицы

{ENABLE | DISABLE } TRIGGER

{ALL | имя_триггера1[, имя_триггера2…]

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

Команда ALTER TABLE имя_таблицы DISABLE TRIGGER {имя_триггера | ALL} и ALTER TABLE имя_таблицы ENABLE TRIGGER {имя_триггера | ALL} не могут одновременно использоваться в теле триггера, состояние которого они изменяют. Кроме этого команда ALTER TABLE имя_таблицы DISABLE TRIGGER {имя_триггера | ALL} не может быть применена в таких случаях, когда в теле DML триггера, отключение срабатывания которого она должна обеспечить, происходит выполнение действия, приводящего к повторной активации триггера в явном виде (без использования команды EXECUTE или процедуры sp_executesql).

18. Представления. Их назначение. Команда создания / изменения представления, ее синтаксис (подробное описание). Команда удаления представления. Особенности при модификации данных при работе с представлениями. Примеры.

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

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

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

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

Синтаксис

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

[ WITH <view_attribute> [ ,...n ] ]

AS select_statement

[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::=

{ [ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }

Обновляемые представления

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

· Любые изменения, в том числе инструкции UPDATE, INSERT и DELETE, должны ссылаться на столбцы только одной базовой таблицы.

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

· при помощи агрегатной функции: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR и VARP;

· на основе вычисления. Столбец нельзя вычислить по выражению, включающему другие столбцы. Столбцы, сформированные при помощи операторов UNION, UNION ALL, CROSSJOIN, EXCEPT и INTERSECT, считаются вычисляемыми и также не являются обновляемыми.

· Предложения GROUP BY, HAVING и DISTINCT не влияют на изменяемые столбцы.

· Предложение TOP не используется нигде в инструкции select_statement представления вместе с предложением WITH CHECK OPTION.

Вышеназванные ограничения относятся ко всем подзапросам представления в предложении FROM, равно как и к самому представлению. Как правило, компонент Database Engine должен иметь возможность однозначно проследить изменения от определения представления до одной базовой таблицы. Дополнительные сведения см. в разделе Изменение данных через представление.

Если вышеуказанные ограничения не позволяют изменить данные через представление напрямую, рассмотрите следующие варианты.

· Триггеры INSTEAD OF

Чтобы сделать представление обновляемым, для него можно создать триггеры INSTEAD OF. Триггер INSTEAD OF выполняется вместо инструкции модификации данных, для которой он определен. Этот триггер позволяет пользователю указать набор действий, которые должны быть выполнены для обработки инструкции модификации данных.

· Секционированные представления

Секционированное представление является в то же время и обновляемым, но при этом действуют некоторые ограничения. При необходимости компонент Database Engine проводит различие между локальными и распределенными секционированными представлениями.

19. Хранимые процедуры. Их назначение. Создание / изменение хранимых процедур (подробное описание синтаксиса команд). Ограничения на команды, содержащиеся в теле хранимой процедуры. Команды удаления хранимой процедуры. Синтаксис команды запуска хранимой процедуры (подробно). Примеры.

Выполняет инструкцию Transact-SQL или пакет инструкций, которые могут выполняться много раз или создаваться динамически. Инструкция Transact-SQL или пакет инструкций могут содержать параметры.

sp_executesql [ @statement = ] statement[ { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } { , [ @param1 = ] 'value1' [ ,...n ] }]

[ @statement = ] statement - Строка в Юникоде, содержащая инструкцию или пакет Transact-SQL.

[ @params = ] N'@parameter_namedata_type [ ,... n ] ' - Строка, содержащая определения всех параметров, внедренных в stmt. Строка должна представлять собой константу в Юникоде либо переменную в этом же формате. Каждое определение параметра состоит из имени параметра и типа данных. n — заполнитель, означающий определения дополнительных параметров. Каждый параметр, указанный в аргументе statement, должен быть определен в аргументе @params. Если инструкция или пакет инструкций языка Transact-SQL в аргументе stmt не содержат параметров, @params может отсутствовать. Этот аргумент по умолчанию принимает значение NULL.

[ @param1 = ] 'value1' - Значение для первого параметра, определенного в строке параметров. Это значение может быть константой или переменной в Юникоде. Каждому параметру, указанному в stmt, должно соответствовать значение. Если инструкция или пакет инструкций Transact-SQL в stmt не содержат параметров, список значений может отсутствовать.

[ OUT | OUTPUT ] - Показывает, что параметр процедуры является выходным. Параметры типов text, ntext и image могут быть выходными, если процедура не является процедурой CLR. Выходным параметром с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CLR.

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

В следующем примере создается и выполняется простая инструкция SELECT, содержащая внедренный параметр с именем @level.

EXECUTE sp_executesql N'SELECT * FROM AdventureWorks2008R2.HumanResources.Employee WHERE BusinessEntityID = @level', N'@level tinyint', @level = 109;

Относительно пакетов инструкций, области имен и контекста базы данных процедура sp_executesql ведет себя аналогично инструкции EXECUTE. Инструкция или пакет инструкций Transact-SQL в параметре stmt процедуры sp_executesql не компилируются до выполнения инструкции sp_executesql. Содержимое stmt затем компилируется и выполняется в качестве отдельного плана выполнения, не зависящего от плана выполнения пакета, вызвавшего процедуру sp_executesql. Пакет, содержащийся в процедуре sp_executesql, не может ссылаться на переменные, объявленные в пакете, вызвавшем sp_executesql. Локальные курсоры или переменные в пакете sp_executesql недоступны пакету, вызвавшему sp_executesql. Изменения в контексте базы данных длятся только до завершения выполнения инструкции sp_executesql.

Процедура sp_executesql может использоваться вместо хранимых процедур для многократного выполнения инструкций Transact-SQL, где единственные различия между инструкциями — значения параметров. Так как инструкция Transact-SQL сама остается неизменной и меняются только значения параметров, оптимизатор запросов SQL Server, вероятнее всего, повторно использует план выполнения, сформированный перед первым выполнением.