close

Вход

Забыли?

вход по аккаунту

?

210.Transact-SQL

код для вставкиСкачать
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Министерство образования и науки Российской Федерации
Ярославский государственный университет им. П. Г. Демидова
Кафедра компьютерной безопасности
и математических методов обработки информации
Transact-SQL
Методические указания
Рекомендовано
Научно-методическим советом университета
для студентов, обучающихся по направлению
Прикладная математика и информатика
Ярославль
ЯрГУ
2013
1
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
УДК 004.65(072)
ББК З973.233-018.2я73
Т 65
Рекомендовано
Редакционно-издательским советом университета
в качестве учебного издания. План 2013 года
Рецензент
кафедра компьютерной безопасности
и математических методов обработки информации
Составитель О. В. Власова
Transact-SQL : методические указания / сост. О. В. ВлаТ 65 сова ; Яросл. гос. ун-т им. П. Г. Демидова. – Ярославль : ЯрГУ,
2013. – 56 с.
В методических указаниях по учебному курсу «Базы данных
и экспертные системы» рассматривается язык Transact-SQL, используемый в одной из самых популярных систем управления реляционными базами данных – MS SQL Server. Указания ориентированы в первую очередь на практическое применение языка TransactSQL: рассмотрены средства управления транзакциями, приведены
сведения о хранимых процедурах, пользовательских и встроенных
функциях, рассмотрены примеры использования триггеров. Содержатся теоретические сведения, включая достаточно подробное описание синтаксиса операторов Transact-SQL, приведены указания
по их использованию. Методические указания содержат большое
количество примеров по использованию операторов Transact-SQL,
которые могут быть полезны на этапе освоения материала и выступать в качестве вопросов для самопроверки.
Предназначено для студентов, обучающихся по направлению 010400.62 Прикладная математика и информатика (дисциплина «Базы данных и экспертные системы», цикл Б3), очной формы обучения.
УДК 004.65(072)
ББК З973.233-018.2я73
© ЯрГУ, 2013
2
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Введение
SQL (Structured Query Language) – это универсальный компьютерный язык, применяемый для создания, модификации
и управления данными в реляционных базах данных (язык структурированных запросов).
Однако SQL в его исходном виде является информационнологическим языком, а не языком программирования, но тем
не менее SQL предусматривает возможность его процедурных
расширений, с учётом которых язык уже вполне может рассматриваться в качестве языка программирования.
В настоящее время широко распространенны следующие
процедурные расширения SQL:
Тип базы данных
Процедурные расширения SQL
Microsoft SQL
Transact-SQL
Microsoft Jet/Access
Jet SQL
MySQL
SQL/PSM (SQL/Persistent Stored Module)
Oracle
PL/SQL (Procedural Language/SQL)
IBM DB2
InterBase/Firebird
SQL PL (SQL Procedural Language)
PSQL (Procedural SQL)
Transact-SQL – это процедурное расширение языка SQL компаний Microsoft. SQL был расширен такими дополнительными
возможностями как:
• локальные и глобальные переменные,
• управляющие операторы,
• различные дополнительные функции для обработки
строк, дат, математики и т. п.
Язык Transact-SQL является ключом к использованию SQL
Server. Все приложения, взаимодействующие с экземпляром SQL
Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.
3
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
1. Транзакции
Язык запросов Transact-SQL взял свое название от слова
транзакция. Рассмотрим это понятие на примере. Допустим, что
у нас есть таблица, содержащая, как минимум, два поля – номер
счета в банке и сумма денег на этом счету.
R1([Номер счета], Баланс, …)
Нам необходимо перевести 100 единиц со счета «А123» на счет
«Б123». Для этого нужно выполнить запрос, уменьшающий сумму первого счета на 100 единиц.
UPDATE R1 SET Баланс = Баланс – 100 WHERE [Номер счета]= ‘A123’
А затем запрос, увеличивающий значение второго счета.
UPDATE R1 SET Баланс = Баланс + 100 WHERE [Номер счета]= ‘Б123’
Все вроде бы хорошо. Но что произойдет, если после уменьшения первого счета случится сбой и сервер не успеет пополнить
другой счет? Деньги уже сняты, но никуда не записаны, а значит,
они пропали. Если выполнять операции в обратном порядке: сначала пополнять счет, а потом снимать деньги, то если снятие не
успеет произойти, то банк может оказаться банкротом, ведь появляется лишняя сумма, снятия не происходит.
Сбои могут быть разные. Это и сбои в электроэнергии и работе компьютера, которые происходят редко, но, помимо этого,
бывают блокировки записей, арифметические переполнения,
да и просто ошибки в коде. Проблему решает транзакция. Перед выполнением операций обновления необходимо явно начать
транзакцию. После этого выполнить две операции UPDATE и по
их окончании завершить транзакцию. Если в момент выполнения
одного из запросов происходит сбой, то все изменения, происшедшие после начала транзакции, отменяются.
Концепция транзакций – неотъемлемая часть любой клиентсерверной базы данных.
Под транзакцией понимается неделимая с точки зрения воздействия на БД последовательность операторов манипулирования
данными (удаления, вставки, модификации), приводящая к одному из двух возможных результатов: либо последовательность выполняется, если все операторы правильные, либо вся транзакция
откатывается, если хотя бы один оператор не может быть успешно
4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
выполнен. Обработка транзакций гарантирует целостность информации в базе данных. Таким образом, транзакция переводит базу
данных из одного целостного состояния в другое.
Поддержание механизма транзакций – показатель уровня
развитости СУБД. Корректное поддержание транзакций одновременно является основой обеспечения целостности БД, а также составляет основу изолированности в многопользовательских
системах, где с одной БД параллельно могут работать несколько
пользователей или прикладных программ.
При выполнении транзакции СУБД должна придерживаться
определенных правил обработки набора команд, входящих в транзакцию (Atomicity, Consistency, Isolation, Durability (ACID) – неделимость, согласованность, изолированность, устойчивость).
ACID-свойства транзакций:
• Транзакция неделима в том смысле, что представляет собой единое целое. Все ее компоненты либо имеют место, либо
нет. Не бывает частичной транзакции. Если может быть выполнена лишь часть транзакции, она отклоняется.
• Транзакция является согласованной, потому что не на-
рушает бизнес-логику и отношения между элементами данных.
Это свойство очень важно при разработке клиент-серверных систем, поскольку в хранилище данных поступает большое количество транзакций от разных систем и объектов. Если хотя бы одна
из них нарушит целостность данных, то все остальные могут выдать неверные результаты.
• Транзакция всегда изолированна, поскольку ее результаты самодостаточны. Они не зависят от предыдущих или последующих транзакций – это свойство называется сериализуемостью
и означает, что транзакции в последовательности независимы.
• Транзакция устойчива. После своего завершения она со-
храняется в системе, которую ничто не может вернуть в исходное
(до начала транзакции) состояние, т. е. происходит фиксация
транзакции, означающая, что ее действие постоянно даже при
сбое системы. При этом подразумевается некая форма хранения
информации в постоянной памяти как часть транзакции.
5
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Указанные выше правила выполняет сервер. Программист
лишь выбирает нужный уровень изоляции, заботится о соблюдении логической целостности данных и бизнес-правил, решает,
какие команды должны выполняться как одна транзакция, а какие могут быть разбиты на несколько последовательно выполняемых транзакций.
Блокировки
Блокировкой называется временное ограничение на выполнение некоторых операций обработки данных. Блокировка может
быть наложена как на отдельную строку таблицы, так и на всю базу
данных. Управлением блокировками на сервере занимается менеджер блокировок, контролирующий их применение и разрешение
конфликтов. Транзакции и блокировки тесно связаны друг с другом.
Транзакции накладывают блокировки на данные, чтобы обеспечить
выполнение требований ACID. Без использования блокировок несколько транзакций могли бы изменять одни и те же данные.
Блокировка представляет собой метод управления параллельными процессами, при котором объект БД не может быть модифицирован без ведома транзакции, т. е. происходит блокирование доступа к объекту со стороны других транзакций, чем исключается непредсказуемое изменение объекта. Различают два вида блокировки:
• блокировка записи – транзакция блокирует строки в таблицах таким образом, что запрос другой транзакции к этим строкам будет отменен;
• блокировка чтения – транзакция блокирует строки так,
что запрос со стороны другой транзакции на блокировку записи
этих строк будет отвергнут, а на блокировку чтения – принят.
В СУБД используют протокол доступа к данным, позволяющий избежать проблемы параллелизма. Его суть заключается
в следующем:
• транзакция, результатом действия которой на строку данных в таблице является ее извлечение, обязана наложить блокировку чтения на эту строку;
• транзакция, предназначенная для модификации строки
данных, накладывает на нее блокировку записи;
6
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
• если запрашиваемая блокировка на строку отвергается
из-за уже имеющейся блокировки, то транзакция переводится
в режим ожидания до тех пор, пока блокировка не будет снята;
• блокировка записи сохраняется вплоть до конца выполнения транзакции.
Решение проблемы параллельной обработки БД заключается
в том, что строки таблиц блокируются, а последующие транзакции, модифицирующие эти строки, отвергаются и переводятся
в режим ожидания. В связи со свойством сохранения целостности БД транзакции являются подходящими единицами изолированности пользователей. Действительно, если каждый сеанс взаимодействия с базой данных реализуется транзакцией, то
пользователь начинает с того, что обращается к согласованному
состоянию базы данных – состоянию, в котором она могла бы находиться, даже если бы пользователь работал с ней в одиночку.
Если в системе управления базами данных не реализованы
механизмы блокирования, то при одновременном чтении и изменении одних и тех же данных несколькими пользователями могут возникнуть следующие проблемы одновременного доступа:
• проблема последнего изменения возникает, когда несколько пользователей изменяют одну и ту же строку, основываясь на ее начальном значении; тогда часть данных будет потеряна, т. к. каждая последующая транзакция перезапишет изменения,
сделанные предыдущей. Выход из этой ситуации заключается
в последовательном внесении изменений;
• проблема «грязного» чтения возможна в том случае,
если пользователь выполняет сложные операции обработки данных, требующие множественного изменения данных перед тем,
как они обретут логически верное состояние. Если во время изменения данных другой пользователь будет считывать их, то может
оказаться, что он получит логически неверную информацию. Для
исключения подобных проблем необходимо производить считывание данных после окончания всех изменений;
• проблема неповторяемого чтения является следствием
неоднократного считывания транзакцией одних и тех же данных.
Во время выполнения первой транзакции другая может внести
7
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
в данные изменения, поэтому при повторном чтении первая
транзакция получит уже иной набор данных, что приводит к нарушению их целостности или логической несогласованности;
• проблема чтения фантомов появляется после того, как
одна транзакция выбирает данные из таблицы, а другая вставляет
или удаляет строки до завершения первой. Выбранные из таблицы значения будут некорректны.
Для решения перечисленных проблем в специально разработанном стандарте определены четыре уровня блокирования. Уровень изоляции транзакции определяет, могут ли другие (конкурирующие) транзакции вносить изменения в данные, измененные текущей транзакцией, а также может ли текущая транзакция видеть
изменения, произведенные конкурирующими транзакциями, и наоборот. Каждый последующий уровень поддерживает требования
предыдущего и налагает дополнительные ограничения:
• уровень 0 – запрещение «загрязнения» данных. Этот
уровень требует, чтобы изменять данные могла только одна транзакция; если другой транзакции необходимо изменить те же данные, она должна ожидать завершения первой транзакции;
• уровень 1 – запрещение «грязного» чтения. Если транзакция начала изменение данных, то никакая другая транзакция
не сможет прочитать их до завершения первой;
• уровень 2 – запрещение неповторяемого чтения. Если
транзакция считывает данные, то никакая другая транзакция не
сможет их изменить. Таким образом, при повторном чтении они
будут находиться в первоначальном состоянии;
• уровень 3 – запрещение фантомов. Если транзакция
обращается к данным, то никакая другая транзакция не сможет
добавить новые или удалить имеющиеся строки, которые могут
быть считаны при выполнении транзакции. Реализация этого
уровня блокирования выполняется путем использования блокировок диапазона ключей. Подобная блокировка накладывается
не на конкретные строки таблицы, а на строки, удовлетворяющие
определенному логическому условию.
8
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Управление транзакциями в среде MS SQL Server
SQL Server поддерживает три вида определения транзакций:
• явное;
• автоматическое;
• подразумеваемое.
По умолчанию SQL Server работает в режиме автоматического начала транзакций, когда каждая команда рассматривается
как отдельная транзакция.
Когда пользователю понадобится создать транзакцию, включающую несколько команд, он должен явно указать транзакцию.
Сервер работает только в одном из двух режимов определения транзакций: автоматическом или подразумевающемся. Он не
может находиться в режиме исключительно явного определения
транзакций. Этот режим работает поверх двух других.
Для установки режима автоматического определения транзакций используется команда:
SET IMPLICIT_TRANSACTIONS OFF
При работе в режиме неявного (подразумевающегося) начала транзакций SQL Server автоматически начинает новую транзакцию, как только завершена предыдущая. Установка режима
подразумевающегося определения транзакций выполняется посредством другой команды:
SET IMPLICIT_TRANSACTIONS ON
Явные транзакции
Явные транзакции требуют, чтобы пользователь указал начало и конец транзакции, используя следующие команды:
Начало транзакции: в журнале транзакций фиксируются
первоначальные значения изменяемых данных и момент начала
транзакции;
BEGIN TRAN[SACTION]
[имя_транзакции | @имя_переменной_транзакции ]
[WITH MARK [‘описание’]]
9
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Опция WITH MARK указывает, что транзакция маркирована
в журнале транзакций.
Конец транзакции: если в теле транзакции не было ошибок,
то эта команда предписывает серверу зафиксировать все изменения, сделанные в транзакции, после чего в журнале транзакций помечается, что изменения зафиксированы и транзакция завершена;
COMMIT [TRAN[SACTION]
[имя_транзакции | @имя_переменной_транзакции]]
Создание внутри транзакции точки сохранения: СУБД
сохраняет состояние БД в текущей точке и присваивает сохраненному состоянию имя точки сохранения;
SAVE TRAN[SACTION]
[имя_точки_сохранеия | @имя_переменной_точки_сохранения]
Откат транзакции: когда сервер встречает эту команду,
происходит откат транзакции, восстанавливается первоначальное состояние системы и в журнале транзакций отмечается,
что транзакция была отменена. Приведенная ниже команда отменяет все изменения, сделанные в БД после оператора BEGIN
TRANSACTION, или отменяет изменения, сделанные в БД после
точки сохранения, возвращая транзакцию к месту, где был выполнен оператор SAVE TRANSACTION.
ROLLBACK [TRAN[SACTION]
[имя_транзакции | @имя_переменной_транзакции
| имя_точки_сохранения | @имя_переменной_точки_сохранения]]
Функция @@TRANCOUNT возвращает количество активных транзакций.
Пример использования явной транзакции:
BEGIN TRAN
UPDATE R1 SET Баланс = Баланс – 100 WHERE [Номер счета]= ‘A123’
UPDATE R1 SET Баланс = Баланс + 100 WHERE [Номер счета]= ‘Б123’
COMMIT
Пример использования точек сохранения:
BEGIN TRAN
SAVE TRANSACTION point1
-- в point1 – первоначальное состояние R1
UPDATE R1 SET Баланс = Баланс – 100 WHERE [Номер счета]= ‘A123’
10
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SAVE TRANSACTION point2 -- в point2 – со счёта А123 снято 100 единиц
UPDATE R1 SET Баланс = Баланс + 100 WHERE [Номер счета]= ‘Б123‘
SAVE TRANSACTION point3
-- в point3 – на счёт Б123 добавлено 100 единиц
UPDATE R1 SET Баланс = 0 WHERE [Номер счета] LIKE [АБ]+ ‘123’
ROLLBACK TRANSACTION point3
-- откат к точке point3
SELECT * FROM R1
ROLLBACK TRANSACTION point1
-- откат к точке point1 (исходное состояние R1)
SELECT * FROM R1
COMMIT
Вложенные транзакции
Вложенными называются транзакции, выполнение которых
инициируется из тела уже активной транзакции. Для создания
вложенной транзакции пользователю не нужны какие-либо дополнительные команды. Он просто начинает новую транзакцию,
не закрыв предыдущую. Завершение транзакции верхнего уровня
откладывается до завершения вложенных транзакций. Если транзакция самого нижнего (вложенного) уровня завершена неудачно
и отменена, то все транзакции верхнего уровня, включая транзакцию первого уровня, будут отменены. Кроме того, если несколько транзакций нижнего уровня были завершены успешно
(но не зафиксированы), однако на среднем уровне (не самая
верхняя транзакция) неудачно завершилась другая транзакция,
то в соответствии с требованиями ACID произойдет откат всех
транзакций всех уровней, включая успешно завершенные. Только когда все транзакции на всех уровнях завершены успешно,
происходит фиксация всех сделанных изменений в результате
успешного завершения транзакции верхнего уровня.
Каждая команда COMMIT TRANSACTION работает только
с последней начатой транзакцией. При завершении вложенной
транзакции команда COMMIT применяется к наиболее «глубокой» вложенной транзакции. Даже если в команде COMMIT
11
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
TRANSACTION указано имя транзакции более высокого уровня,
будет завершена транзакция, начатая последней.
Если команда ROLLBACK TRANSACTION используется
на любом уровне вложенности без указания имени транзакции,
то откатываются все вложенные транзакции, включая транзакцию самого высокого (верхнего) уровня. В команде ROLLBACK
TRANSACTION разрешается указывать только имя самой верхней
транзакции. Имена любых вложенных транзакций игнорируются,
и попытка их указания приведет к ошибке. Таким образом, при откате транзакции любого уровня вложенности всегда происходит
откат всех транзакций. Если же требуется откатить лишь часть
транзакций, можно использовать команду SAVE TRANSACTION,
с помощью которой создается точка сохранения.
Пример использования вложенных транзакций:
BEGIN TRANSACTION
UPDATE R1 SET Баланс = Баланс – 100 WHERE [Номер счета]= ‘A123’
BEGIN TRANSACTION
UPDATE R1 SET Баланс = Баланс + 100 WHERE [Номер счета]= ‘Б123’
IF @@ROWCOUNT = 0
ROLLBACK TRANSACTION
ELSE
BEGIN
COMMIT TRANSACTION
COMMIT TRANSACTION
END
Здесь в случае ошибки происходит возврат на начальное состояние таблицы, поскольку выполнение команды ROLLBACK
TRANSACTION без указания имени транзакции откатывает все
транзакции.
Уровни изоляции MS SQL Server
Уровень изоляции определяет степень независимости транзакций друг от друга. Наивысшим уровнем изоляции является
сериализуемость, обеспечивающая полную независимость транзакций друг от друга. Каждый последующий уровень соответствует требованиям всех предыдущих и обеспечивает дополнительную защиту транзакций.
12
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SQL Server поддерживает все четыре уровня изоляции, определенные стандартом ANSI. Уровень изоляции устанавливается
командой:
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED| READ COMMITTED | REPEATABLE READ
| SERIALIZABLE ]
READ������������������������������������������������
�����������������������������������������������
UNCOMMITED�������������������������������������
– незавершенное чтение, или допустимо черновое чтение. Низший уровень изоляции, соответствующий
уровню 0. Он гарантирует только физическую целостность данных: если несколько пользователей одновременно изменяют одну
и ту же строку, то в окончательном варианте строка будет иметь
значение, определенное пользователем, последним изменившим
запись. По сути, для транзакции не устанавливается никакой блокировки, которая гарантировала бы целостность данных.
READ COMMITTED – завершенное чтение, при котором отсутствует черновое, «грязное» чтение. Тем не менее в процессе
работы одной транзакции другая может быть успешно завершена
и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных. Это проблема
неповторяемого чтения.
REPEATABLE READ – повторяющееся чтение. Повторное
чтение строки возвратит первоначально считанные данные, несмотря на любые обновления, произведенные другими пользователями до завершения транзакции. Тем не менее на этом уровне
изоляции возможно возникновение фантомов.
SERIALIZABLE – сериализуемость. Чтение запрещено до завершения транзакции. Это максимальный уровень изоляции, который обеспечивает полную изоляцию транзакций друг от друга.
В каждый момент времени возможен только один уровень
изоляции.
Функция @@NESTLEVEL возвращает уровень вложенности транзакций.
Контрольные вопросы
1. Что такое транзакция?
2. Что такое блокировка?
13
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3. Какие виды определения транзакций поддерживает SQL
Server?
4. Какие команды используются для явного задания транзакций?
5. Какие уровни изоляции поддерживает SQL Server?
2. Операторы процедурной логики
Язык Transact-SQL отличается не только мощной поддержкой транзакций, но и поддержкой переменных, благодаря которым, становится возможным создание более сложных запросов
и даже целых программ. Переменные есть и в других расширениях языка SQL (в том числе и PL/SQL), поэтому это не индивидуальное преимущество именно MS SQL Server.
Переменные
Все используемые переменные должны быть поименованы.
В MS SQL Server необходимо придерживаться следующих правил именования переменных: имя начинается со специального
идентификатора (символа).
Типы идентификаторов:
• @ – идентификатор локальной переменной (пользовательской).
• @@ – идентификатор глобальной переменной (часто их называют встроенными функциями).
• # – идентификатор локальной таблицы или процедуры.
• ## – идентификатор глобальной таблицы или процедуры.
• [ ] – идентификатор группировки слов в переменную.
Для каждой переменной необходимо указать тип данных:
• Число���������������������������������������������������������
– для���������������������������������������������������
������������������������������������������������������
хранения������������������������������������������
��������������������������������������������������
числовых���������������������������������
�����������������������������������������
переменных����������������������
��������������������������������
(int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
• Дата – для хранения даты и времени (datetime,
smalldatetime,date,time).
• Строка – для хранения символьных данных (char, nchar,
varchar, nvarchar).
• Двоичные – для хранения бинарных данных (binary, varbinary, bit).
14
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Специальные – указатели (cursor), 16-байтовое шестнадцатиричное число, которое используется для GUID (uniqueidentifier),
штамп изменения строки (timestamp), версия строки (rowversion),
таблицы (table).
Переменные объявляются в теле пакета или процедуры при
помощи инструкции DECLARE, а значения им присваиваются
при помощи инструкций SET или SELECT. При помощи этой инструкции можно объявлять переменные курсоров для использования в других инструкциях. После декларации все переменные
инициализируются значением NULL, если иное значение не предоставляется как часть декларации.
Общий вид объявления переменной:
DECLARE @имя тип [ = значение ] [,..n]
Изменение значения переменной:
SET @имя = значение или SELECT @имя = значение
Пример использования переменных:
•
USE R1
DECLARE @balans money
SELECT @balans = Баланс FROM R1 WHERE [Номер счета]= ‘A123’
SET @balans = @balans-100
UPDATE R1 SET Баланс = @balans WHERE [Номер счета]= ‘A123’
Преобразование типов
Преобразование типов данных происходит в следующих случаях:
При перемещении, сравнении или объединении данных
одного объекта с данными другого объекта эти данные
могут преобразовываться из одного типа в другой.
• При передаче в переменную программы данных из результирующего столбца Transact-SQL, кодов возврата
или выходных параметров, эти данные должны преобразовываться из системного типа данных SQL Server в тип
данных переменной.
Преобразование типов данных бывает явным и неявным.
Неявное преобразование скрыто от пользователя. MS SQL
Server автоматически преобразует данные из одного типа в дру•
15
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
гой. Например, если тип данных smallint сравнивается с типом int,
то перед сравнением тип smallint неявно преобразуется в тип int.
Явное преобразование выполняется с помощью функций
CAST и CONVERT.
CAST ( выражение AS тип [ ( длина ) ] )
CONVERT ( тип [ ( длина ) ] , выражение [ , стиль ] )
или с версии MS SQL SERVER 2012
TRY_CAST ( выражение AS тип [ ( длина ) ] )
TRY_CONVERT ( тип [ ( длина ) ] , выражение [ , стиль ] )
где
выражение – любое допустимое выражение;
тип – целевой тип данных;
длина – указываемое дополнительно целое число, обозначающее
длину целевого типа данных. Значение по умолчанию равно 30;
стиль – целочисленное выражение, определяющее, как функция
CONVERT преобразует параметр выражение. Если стиль имеет
значение NULL, возвращается NULL. Диапазон определяется параметром тип.
Пример преобразования типов:
SELECT * FROM R1
WHERE CAST(SUBSTRING([номер счета], 2, LEN([номер счета])-1) AS INT)
LIKE ‘%123’
SELECT * FROM R1
WHERE CONVERT(INT , SUBSTRING([номер счета], 2, LEN([номер счета])-1) )
LIKE ‘%123’
SELECT CASE WHEN TRY_CAST(‘тест’ AS float) IS NULL
THEN ‘Ошибка преобразования’
ELSE ‘OK’
END AS Result
SELECT CASE WHEN TRY_CONVERT(float, ‘тест’) IS NULL
THEN ‘Ошибка преобразования’
ELSE ‘OK’
END AS Result
16
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Управляющие операторы
В Transact-SQL существуют специальные команды, которые
позволяют управлять потоком выполнения сценария, прерывая
его или направляя в нужную логику.
Оператор
BEGIN...END
IF...ELSE
RETURN
Описание
Определяет блок.
BEGIN
{оператор_SQL | блок _операторов}
END
Условный оператор.
IF логическое_выражение
{оператор_SQL | блок _операторов}
[ELSE [логическое_выражение]
{оператор_SQL | блок _операторов}
Безусловный выход.
RETURN ([integer_expression])
Возвращаемое значение – это код возврата, причем
часть значений(-14 . . 0) зарезервирована под специальные состояния
WHILE
Цикл с предусловием.
WHILE логическое_выражение
{оператор_SQL | блок _операторов}
BREAK
Выход из цикла WHILE
CONTINUE
Продолжение цикла WHILE
PRINT
CASE
Выдает заданное значение на экран.
PRINT переменная | строка
Длина строки с сообщением не должна превышать
255 символов
Простое выражение CASE для определения результата сравнивает выражение с набором простых выражений
CASE выражение
WHEN выражение1 THEN результат1
[[WHEN выражение 2 THEN результат2[..n]]
[ELSE результатN]
END
17
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
CASE
TRY...CATCH
RAISERROR
Поисковое выражение CASE для определения результата вычисляет набор логических выражений.
CASE
WHEN����������������������������������
логическое_выражение THEN��������
������������
резуль�
тирующее_ выражение [ ...n ]
[ ELSE else_ результирующее_ выражение]
END
Реализация обработчика ошибок. Перехватывает
все ошибки исполнения с кодом серьезности, большим 10, которые не закрывают подключение к базе
данных.
BEGIN TRY
{ оператор_SQL | блок _операторов }
END TRY
BEGIN CATCH
[ { оператор_SQL | блок _операторов } ]
END CATCH
Создает сообщение об ошибке и запускает обработку ошибок для сеанса.
RAISERROR���������������������������������
( { номер_сообщения | строка_со�
общения | @переменная } { ,степень_серьезности
,состояние }
[ ,аргументы_подстановки [ ,...n ] ] ) [ WITH
option [ ,...n ] ]
номер_сообщения – Определяемый пользователем
номер сообщения об ошибке, который хранится в
представлении каталога sys.messages с помощью
sp_addmessage
строка_сообщения – Определенное пользователем сообщение с форматом, аналогичным формату
функции printf из стандартной библиотеки языка С
степень_серьезности – Степень серьезности от 0
до 18 может указать любой пользователь. Степени
серьезности от 19 до 25 могут быть указаны только
членами предопределенной роли сервера sysadmin
и пользователями с разрешениями ALTER TRACE.
Для степеней серьезности от 19 до 25 требуется параметр WITH LOG.
Состояние – Целое число от 0 до 255. Отрицательные
значения или значения больше 255 вызывают ошибку.
18
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
WAITFOR
Блокирует выполнение пакета, хранимой процедуры или транзакции до наступления указанного времени или интервала времени
WAITFOR {
DELAY время_ожидания
TIME время_завершения
}
Пример использования управляющих конструкций для изменения информации в таблице
WHILE (SELECT AVG(Баланс) FROM R1) > 30000
BEGIN
UPDATE R1 SET Баланс = Баланс – 100
IF (SELECT MAX(Баланс) FROM R1) < 50000
BREAK
ELSE
CONTINUE
END
Пример использования условного оператора для проверки
существования объекта базы данных
IF NOT EXISTS (SELECT * FROM master.. sysdatabase
WHERE Name = ‘MY_BASE ‘)
BEGIN
PRINT ‘База данных MY_BAS не существует, создаем’
CREATE DATABASE MY_BASE
END
Курсоры
Запрос к реляционной базе данных обычно возвращает несколько рядов (записей) данных, но приложение за один раз обрабатывает лишь одну запись. К сожалению, в Transact-SQL нет
таких структур данных, как массивы, которые позволили бы обрабатывать каждую строку отдельно. Такую построчную обработку позволяют выполнить курсоры.
Курсор в SQL – это область в памяти базы данных, которая
предназначена для хранения последнего оператора SQL.
В соответствии со стандартом SQL при работе с курсорами
можно выделить следующие основные действия:
• создание или объявление курсора;
19
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
• открытие курсора, т. е. наполнение его данными, которые
сохраняются в многоуровневой памяти;
• выборка из курсора и изменение с его помощью строк
данных;
• закрытие курсора, после чего он становится недоступным
для пользовательских программ;
• освобождение курсора, т. е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает
ассоциированную с ним память.
Курсоры делятся на две категории: последовательные
и прокручиваемые. Последовательные позволяют выбирать данные только в одном направлении – от начала к концу. Прокручиваемые же курсоры предоставляют большую свободу действий
– допускается перемещение в обоих направлениях и переход
к произвольной строке результирующего набора курсора. Если
программа способна модифицировать данные, на которые указывает курсор, он называется прокручиваемым и модифицируемым. Говоря о курсорах, не следует забывать об изолированности транзакций. Когда один пользователь модифицирует запись,
другой читает ее при помощи собственного курсора, более того,
он может модифицировать ту же запись, что делает необходимым соблюдение целостности данных.
SQL Server поддерживает курсоры статические, динамиче�
ские, последовательные и управляемые набором ключей.
В схеме со статическим курсором информация читается из базы
данных один раз и хранится в виде моментального снимка (по состоянию на некоторый момент времени), поэтому изменения, внесенные в базу данных другим пользователем, не видны. На время
открытия курсора сервер устанавливает блокировку на все строки,
включенные в его полный результирующий набор. Статический
курсор не изменяется после создания и всегда отображает тот набор
данных, который существовал на момент его открытия. Если другие пользователи изменят в исходной таблице включенные в курсор
данные, это никак не повлияет на статический курсор.
В статический курсор внести изменения невозможно, поэтому он всегда открывается в режиме «только для чтения».
20
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Динамический курсор поддерживает данные в «живом» состоянии, но это требует затрат сетевых и программных ресурсов.
При использовании динамических курсоров не создается полная
копия исходных данных, а выполняется динамическая выборка
из исходных таблиц только при обращении пользователя к тем
или иным данным. На время выборки сервер блокирует строки, а все изменения, вносимые пользователем в полный результирующий набор курсора, будут видны в курсоре. Однако если
другой пользователь внес изменения уже после выборки данных
курсором, то они не отразятся в курсоре.
Курсор, управляемый набором ключей, находится посередине
между этими крайностями. Записи идентифицируются на момент
выборки, и тем самым отслеживаются изменения. Такой тип курсора
полезен при реализации прокрутки назад – тогда добавления и удаления рядов не видны, пока информация не обновится, а драйвер выбирает новую версию записи, если в нее были внесены изменения.
Создание курсора:
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 создается последовательный курсор; выборку данных можно осуществлять только в направлении от первой строки к последней.
21
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
При указании SCROLL создается прокручиваемый курсор, обращаться к данным можно в любом порядке и в любом направлении.
При указании STATIC создается статический курсор.
При указании KEYSET создается ключевой курсор.
При указании DYNAMIC создается динамический курсор.
Если для курсора READ_ONLY указать аргумент FAST_
FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC.
В курсоре, созданном с указанием аргумента OPTIMISTIC,
запрещается изменение и удаление строк, которые были изменены после открытия курсора.
При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора,
если он несовместим с запросом SELECT.
Открытие курсора
Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:
OPEN {{[GLOBAL] имя_курсора } |@имя_переменной_курсора}
После открытия курсора происходит выполнение связанного
с ним оператора SELECT, выходные данные которого сохраняются в многоуровневой памяти.
Выборка данных из курсора
Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:
FETCH [[NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {номер_строки | @переменная_номера_строки}
| RELATIVE {номер_строки | @переменная_номера_строки}]
FROM ]{{[GLOBAL ]имя_курсора }| @имя_переменной_курсора }
[INTO @имя_переменной [,...n]]
При указании FIRST будет возвращена самая первая строка
полного результирующего набора курсора, которая становится
текущей строкой.
При указании LAST возвращается самая последняя строка
курсора. Она же становится текущей строкой.
22
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
При указании NEXT возвращается строка, находящаяся
в полном результирующем наборе сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.
Ключевое слово PRIOR возвращает строку, находящуюся
перед текущей. Она и становится текущей.
Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому
номеру в полном результирующем наборе курсора. Номер строки можно задать с помощью константы или как имя переменной,
в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так
и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного –
от конца. Выбранная строка становится текущей. Если указано
нулевое значение, строка не возвращается.
Аргумент RELATIVE {кол_строки | @переменная_кол_строки}
возвращает строку, находящуюся через указанное количество строк
после текущей. Если указать отрицательное значение числа строк,
то будет возвращена строка, находящаяся за указанное количество
строк перед текущей. При указании нулевого значения возвратится
текущая строка. Возвращенная строка становится текущей.
В конструкции INTO @имя_переменной [,...n] задается список
переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных
должен соответствовать порядку столбцов в курсоре, а тип данных
переменной – типу данных в столбце курсора. Если конструкция
INTO не указана, то поведение команды FETCH будет напоминать
поведение команды SELECT – данные выводятся на экран.
Изменение и удаление данных
Для выполнения изменений с помощью курсора необходимо
выполнить команду UPDATE в следующем формате:
UPDATE имя_таблицы SET {имя_столбца={
DEFAULT | NULL | выражение}}[,...n]
WHERE CURRENT OF {{[GLOBAL] имя_курсора}
|@имя_переменной_курсора}
23
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
За одну операцию могут быть изменены несколько столбцов текущей строки курсора, но все они должны принадлежать одной таблице.
Для удаления данных посредством курсора используется команда DELETE в следующем формате:
DELETE имя_таблицы
WHERE CURRENT OF {{[GLOBAL] имя_курсора}
|@имя_переменной_курсора}
В результате будет удалена строка, установленная текущей
в курсоре.
Закрытие курсора
CLOSE {имя_курсора | @имя_переменной_курсора}
После закрытия курсор становится недоступным для пользователей программы. При закрытии снимаются все блокировки,
установленные в процессе его работы. Закрытие может применяться только к открытым курсорам. Закрытый, но неосвобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор.
Освобождение курсора
Закрытие курсора необязательно освобождает ассоциированную с ним память. В некоторых реализациях нужно явным образом освободить ее с помощью оператора DEALLOCATE. После
освобождения курсора освобождается и память, при этом становится возможным повторное использование имени курсора.
DEALLOCATE { имя_курсора | @имя_переменной_курсора }
Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS
Функция @@FETCH_STATUS возвращает:
0, если выборка завершилась успешно;
-1, если выборка завершилась неудачно вследствие попытки
выборки строки, находящейся за пределами курсора;
-2, если выборка завершилась неудачно вследствие попытки
обращения к удаленной или измененной строке.
Пример объявления и открытия курсора:
--объявление курсора
DECLARE My_Curcor CURSOR SCROLL FOR SELECT * FROM R1
-- использование переменной для объявления курсора
24
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
DECLARE @MyCursor CURSOR
SET @MyCursor=CURSOR LOCAL SCROLL FOR
SELECT * FROM Клиент
-- открытие курсора
OPEN My_Curcor
Пример использования курсора для вывода номеров счетов
с балансом выше среднего.
DECLARE @nomer varchar(50),
@balans money,
@avg_balans money,
@message varchar(250)
SELECT @avg_balans = avg(Баланс) FROM R1
PRINT ‘ Список счетов’
DECLARE klient_cursor CURSOR LOCAL FOR
SELECT * FROM R1 WHERE Баланс > @avg_balans ORDER BY
Баланс
OPEN klient_cursor
FETCH NEXT FROM klient_cursor INTO @nomer , @balans
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @message=’Счет’+@nomer +’Баланс ‘+CAST(@
balans as varchar)
PRINT @message
-- переход к следующему клиенту
FETCH NEXT FROM klient_cursor INTO @nomer , @balans
END
CLOSE klient_cursor
DEALLOCATE klient_cursor
3. Хранимые процедуры
Хранимые процедуры (Stored procedure) представляют собой набор команд, состоящий из одного или нескольких операторов SQL
или функций и сохраняемый в базе данных в откомпилированном
виде. Выполнение в базе данных хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества:
25
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
• необходимые операторы уже содержатся в базе данных;
• как правило, хранимые процедуры выполняются быстрее,
чем последовательность отдельных операторов, так как все они
прошли этап синтаксического анализа и находятся в исполняемом формате; перед выполнением хранимой процедуры SQL
Server генерирует для нее план исполнения, выполняет ее оптимизацию и компиляцию;
• хранимые процедуры поддерживают модульное программирование, так как позволяют разбивать большие задачи на самостоятельные, более мелкие и удобные в управлении части;
• хранимые процедуры могут вызывать другие хранимые
процедуры и функции и могут быть вызваны из прикладных программ других типов;
• хранимые процедуры проще использовать: они могут состоять из десятков и сотен команд, но для их запуска достаточно
указать всего лишь имя нужной хранимой процедуры. Это позволяет уменьшить размер запроса, посылаемого от клиента на сервер, а значит, и нагрузку на сеть.
Применение хранимых процедур упрощает сопровождение
программных комплексов и внесение изменений в них. Обычно
все ограничения целостности в виде правил и алгоритмов обработки данных реализуются на сервере баз данных и доступны конечному приложению в виде набора хранимых процедур, которые
и представляют интерфейс обработки данных. Для обеспечения
целостности данных, а также в целях безопасности, приложение
обычно не получает прямого доступа к данным – вся работа с ними
ведется путем вызова тех или иных хранимых процедур.
Подобный подход делает весьма простой модификацию алгоритмов обработки данных, тотчас же становящихся доступными для всех пользователей сети, и обеспечивает возможность
расширения системы без внесения изменений в само приложение: достаточно изменить хранимую процедуру на сервере баз
данных. Разработчику не нужно перекомпилировать приложение, создавать его копии, а также инструктировать пользователей
о необходимости работы с новой версией. Пользователи вообще
могут не подозревать о том, что в систему внесены изменения.
26
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Хранимые процедуры существуют независимо от таблиц
или каких-либо других объектов баз данных. Разработчик может
управлять правами доступа к хранимой процедуре, разрешая или
запрещая ее выполнение. Изменять код хранимой процедуры
разрешается только ее владельцу или члену фиксированной роли
базы данных. При необходимости можно передать права владения ею от одного пользователя к другому.
В SQL Server поддерживается несколько типов хранимых
процедур.
Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все
действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами,
которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры
имеют префикс sp_, хранятся в системной базе данных и могут быть
вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или
иные действия. Хранимые процедуры – полноценный объект
базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое
время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые
процедуры могут быть вызваны только из того соединения, в котором
созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные
временные хранимые процедуры доступны для любых соединений
сервера, на котором имеется такая же процедура. Для ее определения
достаточно дать ей имя, начинающееся с символов ##. Удаляются эти
процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.
27
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:
{CREATE | ALTER } PROCEDURE имя_процедуры [; номер]
[{@имя_параметра тип_данных } [VARYING ] [=default] [OUTPUT]
[READONLY] ] [,...n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION }]
[FOR REPLICATION] [ EXECUTE AS Clause ]
AS
тело_процедуры
Как видно из синтаксиса команды, не допускается указывать
имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую
процедуру в конкретной базе данных, необходимо выполнить
команду CREATE PROCEDURE в контексте этой базы данных.
При обращении из тела хранимой процедуры к объектам той
же базы данных можно использовать укороченные имена, т. е.
без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание
имени базы данных обязательно.
Номер – необязательный целочисленный аргумент, используемый для группирования одноименных процедур. Все сгруппированные процедуры можно удалить, выполнив одну инструкцию DROP PROCEDURE.
Для передачи входных и выходных данных в создаваемой
хранимой процедуре могут использоваться параметры, имена которых, как и имена локальных переменных, должны начинаться
с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры
не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры.
Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры, можно использовать
любые типы данных SQL, включая определенные пользователем.
Однако тип данных CURSOR может быть использован только
как выходной параметр хранимой процедуры, т. е. с указанием
ключевого слова OUTPUT.
28
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой
процедуры. Однако это вовсе не означает, что параметр не подходит
для передачи значений в хранимую процедуру. При указании ключевого слова OUTPUT значение соответствующего параметра при
вызове процедуры может быть задано только с помощью локальной
переменной. Не разрешается использование любых выражений или
констант, допустимое для обычных параметров.
Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что
выходным параметром будет результирующее множество.
Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию.
READONLY указывает, что параметр не может быть обновлен или изменен в тексте процедуры. Если тип параметра является возвращающим табличное значение типом, то должно быть
указано ключевое слово READONLY.
Так как сервер кэширует план исполнения запроса и компилированный код, при последующем вызове процедуры будут использоваться уже готовые значения. Однако в некоторых случаях все
же требуется выполнять перекомпиляцию кода процедуры. Указание ключевого слова RECOMPILE предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове.
Параметр FOR REPLICATION востребован при репликации
данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию.
Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры.
Ключевое слово EXECUTE AS определяет контекст безопасности, в котором должна быть выполнена процедура.
Ключевое слово AS размещается в начале собственно тела
хранимой процедуры, т. е. набора команд SQL, с помощью которых и будет реализовываться то или иное действие. В теле процедуры могут применяться практически все команды SQL, объ29
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
являться транзакции, устанавливаться блокировки и вызываться
другие хранимые процедуры. Выход из хранимой процедуры
можно осуществить посредством команды RETURN.
Удаление хранимой процедуры осуществляется командой:
DROP PROCEDURE {имя_процедуры} [,...n]
Для выполнения хранимой процедуры используется команда:
[[ EXEC [ UTE] [@return_status=] имя_процедуры [;номер]
[[@имя_параметра=]{значение | @имя_переменной}
[OUTPUT ]|[DEFAULT ]][,...n]
Если вызов хранимой процедуры не является единственной
командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры
из тела другой процедуры или триггера. Использование ключевого слова OUTPUT при вызове процедуры разрешается только для
параметров, которые были объявлены при создании процедуры
с ключевым словом OUTPUT.
Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение
по умолчанию. Естественно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.
Имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение
по умолчанию, просто пропустив его при перечислении нельзя.
Если же требуется опустить параметры, для которых определено значение по умолчанию, достаточно явного указания имен
параметров при вызове хранимой процедуры. Более того, таким
способом можно перечислять параметры и их значения в произвольном порядке.
Отметим, что при вызове процедуры указываются либо имена параметров со значениями, либо только значения без имени
параметра. Их комбинирование не допускается.
Пример процедуры без параметров, возвращающей набор
данных.
30
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Разработать процедуру для получения номеров счетов с балансом менее среднего.
CREATE PROC my_proc1
AS
SELECT * FROM R1
WHERE Баланс< (SELECT AVG(Баланс) FROM R1)
Для обращения к процедуре можно использовать команды:
EXEC my_proc1 или my_proc1
Пример процедуры с входным параметром, возвращающей
набор данных.
Разработать процедуру для получения номеров счетов с балансом более введенного.
CREATE PROC my_proc2 @par1 money =1000
AS
SELECT * FROM R1
WHERE Баланс>@par1
Для обращения к процедуре можно использовать команды:
EXEC my_proc2 7000
EXEC my_proc2 @par1=7000
my_proc2 -- будет использоваться значение по умолчанию
Пример процедуры с входным и выходным параметром.
Разработать процедуру для получения количества счетов
с балансом более введенного.
CREATE PROC my_proc3 @par1 money =1000, @count INT
OUTPUT
AS
SELECT @count =count([номер счета]) FROM R1
WHERE Баланс>@par1
Для обращения к процедуре можно использовать команды:
DECLARE @count_nomber int, @balans money
SET @balans = 7000
EXEC my_proc3 @balans , @count_nomber OUTPUT
SELECT @count_nomber AS ’количество счетов’
EXEC my_proc3 @count_nomber OUTPUT
31
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
-- будет использоваться значение по умолчанию для первого
параметра
Пример использования вложенных процедур.
Разработать процедуру для получения количества счетов
с балансом более среднего.
CREATE PROC my_proc4
AS
BEGIN
DECLARE @m money
SELECT @m=AVG(Баланс) FROM R1
RETURN @m
END
CREATE PROC my_proc5 @count_nomber INT OUTPUT
AS
BEGIN
DECLARE @p money
EXEC @p = my_proc4
EXEC my_proc3 @p , @count_nomber OUTPUT
END
Получение информации о хранимых процедурах
С помощью системной процедуры sp_help можно получить
отчет о сохраненной процедуре. Например, пользователь может получить информацию о сохраненной процедуре my_ proc1
из созданной базы данных с помощью следующей команды:
sp_help my_ proc1
Чтобы увидеть текст (тело) сохраненной процедуры, нужно
вызвать системную процедуру sp_helptext:
sp_helptext my_ proc1
Контрольные вопросы
1. Что такое хранимая процедура?
2. Где выполняются хранимые процедуры?
3. Как активизируются хранимые процедуры?
4. В чем преимущества использования хранимых процедур?
5. Какие типы хранимых процедур имеются в MS SQL Server?
32
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
4. Пользовательские функции
Одним из недостатков хранимых процедур является невозможность их использования непосредственно в выражениях, т. к.
они требуют промежуточного присвоения возвращенного значения переменной, которая затем и указывается в выражении.
Естественно, подобный метод применения программного кода
не слишком удобен. Многие разработчики уже давно хотели
иметь возможность вызова разработанных алгоритмов непосредственно в выражениях. Возможность создания пользовательских
функций была предоставлена в среде MS SQL Server 2000.
Функции пользователя представляют собой самостоятельные объекты базы данных, располагаются в определенной базе
данных и доступны только в ее контексте.
В SQL Server имеются следующие классы функций пользователя:
Scalar – функции возвращают обычное скалярное значение, каждая может включать множество команд, объединяемых
в один блок с помощью конструкции BEGIN...END. Такие функции могут возвращать любые типы данных, кроме text, ntext,
image, cursor и timestamp;
Inline – функции содержат всего одну команду SELECT
и возвращают пользователю набор данных в виде значения типа
данных TABLE;
Multi-statement – функции также возвращают пользователю
значение типа данных TABLE, содержащее набор данных, однако в теле функции находится множество команд SQL (INSERT,
UPDATE и т. д.). Именно с их помощью и формируется набор
данных, который должен быть возвращен после выполнения
функции.
Детерминированные функции каждый раз возвращают один
и тот же результат, если предоставлять им один и тот же набор
входных значений и использовать одно и то же состояние базы
данных. Недетерминированные функции могут возвращать каждый раз разные результаты, даже если предоставлять им один
и тот же набор входных значений и использовать одно и то же
состояние базы данных.
33
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
В функциях допустимы следующие инструкции.
Инструкции присваивания.
Инструкции управления потоком, за исключением
инструкций TRY...CATCH.
• Инструкции DECLARE, объявляющие локальные
переменные и локальные курсоры.
• Инструкции SELECT, которые содержат списки выбора
с выражениями, присваивающими значения локальным
переменным.
• Операции над локальными курсорами, которые
объявляются, открываются, закрываются и
освобождаются в теле функции. Допустимы только
те инструкции FETCH, которые предложением INTO
присваивают значения локальным переменным.
Инструкции FETCH, возвращающие данные клиенту,
недопустимы.
• Инструкции INSERT, UPDATE и DELETE, которые
изменяют локальные табличные переменные.
• Инструкции EXECUTE, вызывающие расширенные
хранимые процедуры.
•
•
Скалярные функции
Создание функции данного типа выполняется с помощью команды:
CREATE FUNCTION [ имя_схемы. ] имя _функции
( [ { @имя_параметра [ AS ][ имя_схемы. ] тип_данных
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS тип_данных
[ WITH {ENCRYPTION | SCHEMABINDING | EXECUTE AS …
} ,[…n] ]
[ AS ]
BEGIN
тело_функции
RETURN скалярное_выражение
END
34
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рассмотрим назначение параметров команды.
Функция может содержать один или несколько входных параметров либо не содержать ни одного. Каждый параметр должен иметь уникальное в пределах создаваемой функции имя
и начинаться с символа «@». После имени указывается тип данных параметра. Дополнительно можно указать значение, которое
будет автоматически присваиваться параметру (DEFAULT), если
пользователь явно не указал значение соответствующего параметра при вызове функции.
С помощью конструкции RETURNS указывается, какой тип
данных будет иметь возвращаемое функцией значение.
Дополнительные параметры, с которыми должна быть создана функция, могут быть указаны посредством ключевого слова
WITH. Благодаря ключевому слову ENCRYPTION код команды,
используемый для создания функции, будет зашифрован, и никто
не сможет просмотреть его. Эта возможность позволяет скрыть
логику работы функции. Кроме того, в теле функции может выполняться обращение к различным объектам базы данных, а потому изменение или удаление соответствующих объектов может
привести к нарушению работы функции. Чтобы избежать этого,
требуется запретить внесение изменений, указав при создании
этой функции ключевое слово SCHEMABINDING.
Между ключевыми словами BEGIN...END указывается набор команд, они и будут являться телом функции.
Когда в ходе выполнения кода функции встречается ключевое слово RETURN, выполнение функции завершается и как
результат ее вычисления возвращается значение, указанное непосредственно после слова RETURN. Отметим, что в теле функции разрешается использование множества команд RETURN,
которые могут возвращать различные значения. В качестве возвращаемого значения допускаются как обычные константы, так
и сложные выражения. Единственное условие – тип данных возвращаемого значения должен совпадать с типом данных, указанным после ключевого слова RETURNS.
Пример скалярной функции
35
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пусть имеется таблица, в которой фиксируются все проводки по счетам.
R2([Номер счета], Сумма, [Дата проведения])
Если Сумма больше 0, то деньги поступают на счет, если
меньше 0, то снимаются со счета.
Создать и применить функцию скалярного типа для вычисления суммарной прибыли за последние 30 календарных дней
на указанном счете.
CREATE FUNCTION sales ( @number VARCHAR(50) )
RETURNS MONEY
AS
BEGIN
DECLARE @prihod MONEY
IF NOT EXISTS (SELECT * FROM R2
WHERE [Дата проведения]=@number AND
ABS(DATEDIFF(DAY,GETDATE(),[Дата проведения]))<30)
SET @prihod=0
ELSE
SELECT @prihod=sum(Сумма)
FROM R2
WHERE [Дата проведения]=@number
AND ABS(DATEDIFF(DAY,GETDATE(),[�������������
Дата���������
проведе��������
ния]))<30
RETURN @prihod
END
GO
-- обращение к функции
PRINT ‘Прибыль =’+CAST(Test.dbo.sales(‘a123’) as varchar)
Inline функции
Создание функции этого типа выполняется с помощью команды:
CREATE FUNCTION [ имя_схемы. ] имя _функции
( [ { @имя_параметра [ AS ][ имя_схемы. ] тип_данных
[ = default ] [ READONLY ] } [ ,...n ] ])
36
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
RETURNS TABLE
[ WITH {ENCRYPTION | SCHEMABINDING | EXECUTE
AS … } ,[…n] ]
[ AS ]
RETURN [(] SELECT_оператор [)]
Основная часть параметров, используемых при создании табличных функций, аналогична параметрам скалярной функции. Тем
не менее создание табличных функций имеет свою специфику.
После ключевого слова RETURNS всегда должно указываться ключевое слово TABLE. Таким образом, функция данного
типа должна строго возвращать значение типа данных TABLE.
Структура возвращаемого значения типа TABLE не указывается
явно при описании собственно типа данных. Вместо этого сервер
будет автоматически использовать для возвращаемого значения
TABLE структуру, возвращаемую запросом SELECT, который
является единственной командой функции.
Возвращаемое функцией значение типа TABLE может быть
использовано непосредственно в запросе, т. е. в разделе FROM.
Пример inline функции
Создать и применить функцию табличного типа для определения номеров счетов с наименьшей прибылью за последние K дней.
Используется общетабличное представление.
CREATE FUNCTION sales1( @k int =30)
RETURNS TABLE
AS
RETURN
WITH
pr1([номер счета],summa)
AS (SELECT [номер счета], sum(сумма) as summa
FROM R2 WHERE
ABS(DATEDIFF(DAY,GETDATE(),[Дата проведения]))<@k
GROUP BY [номер счета] ),
pr2(min_sum)
AS (SELECT MIN(summa) as min_sum FROM pr1)
SELECT [номер счета] FROM pr1 ,pr2 where summa = pr2.min_sum
GO
37
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
-- обращение к функции
SELECT * FROM Test.dbo.sales1(25)
Multi-statement функции
Создание функций типа Multi-statement выполняется с помощью следующей команды:
CREATE FUNCTION [ имя_схемы. ] имя _функции
( [ { @имя_параметра [ AS ][ имя_схемы. ] тип_данных
[ = default ] [ READONLY ] } [ ,...n ] ])
RETURNS @имя_параметра TABLE определение_таблицы
[ WITH {ENCRYPTION | SCHEMABINDING | EXECUTE AS …
} ,[…n] ]
[ AS ]
BEGIN
тело_функции
RETURN
END
Использование большей части параметров рассматривалось
при описании предыдущих функций.
В отличие от табличных функций, при создании функций
Multi-statement необходимо явно задать структуру возвращаемого значения. Она указывается непосредственно после ключевого слова TABLE и, таким образом, является частью определения
возвращаемого типа данных. Синтаксис конструкции определение_таблицы полностью соответствует одноименным структурам, используемым при создании обычных таблиц с помощью
команды CREATE TABLE.
Набор возвращаемых данных должен формироваться с помощью команд INSERT, выполняемых в теле функции. Кроме того,
в теле функции допускается использование различных конструкций языка SQL, которые могут контролировать значения, размещаемые в выходном наборе строк. При работе с командой INSERT
требуется явно указать имя того объекта, куда необходимо вставить
строки. Поэтому в функциях типа Multi-statement, в отличие от табличных, необходимо присвоить какое-то имя объекту с типом данных TABLE – оно и указывается как возвращаемое значение.
38
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Завершение работы функции происходит в двух случаях:
если возникают ошибки выполнения и если появляется ключевое слово RETURN. В отличие от функций скалярного типа, при
использовании команды RETURN не нужно указывать возвращаемое значение. Сервер автоматически возвратит набор данных типа TABLE, имя и структура которого была указана после
ключевого слова RETURNS. В теле функции может быть указано
более одной команды RETURN.
Необходимо отметить, что работа функции завершается
только при наличии команды RETURN. Это утверждение верно
и в том случае, когда речь идет о достижении конца тела функции
– самой последней командой должна быть команда RETURN.
Пример multi-statement функции
Создать и применить функцию (типа multi-statement), которая для номеров счетов с наименьшей прибылью за последние
K дней выдает информацию о балансе счета.
CREATE FUNCTION sales2 (@k int =30)
RETURNS @my_table TABLE
([номер счета] VARCHAR(50),баланс MONEY)
AS
BEGIN
INSERT INTO @my_table
SELECT * FROM R1
WHERE [номер счета] IN (SELECT* FROM Test.dbo.sales3( @k ) )
RETURN
END
GO
-- обращение к функции
SELECT * FROM Test.dbo.sales2()
Удаление любой функции осуществляется командой:
DROP FUNCTION {[ владелец.] имя_функции } [,...n]
Встроенные функции
Встроенные функции, имеющиеся в распоряжении пользователей
при работе с SQL, можно условно разделить на следующие группы:
• математические функции;
39
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
•
•
•
•
•
•
строковые функции;
функции для работы с датой и временем;
функции конфигурирования;
функции системы безопасности;
функции управления метаданными;
статистические функции.
Математические функции
Краткий обзор математических функций представлен в таблице.
Функция
ACOS
ASIN
ATAN
Синтаксис
ABS ( numeric_
expression )
ACOS ( float_expression )
ASIN ( float_expression )
ATAN ( float_expression )
ATN2
ATN2 ( float_expression ,
float_expression )
ABS
CEILING
CEILING ( numeric_
expression )
COS
COT
COS ( float_expression )
COT ( float_expression )
DEGREES
FLOOR
DEGREES (numeric_
expression )
EXP ( float_expression )
FLOOR ( numeric_
expression )
LOG
LOG ( float_expression )
EXP
LOG10
PI
POWER
LOG10 ( float_expression
)
PI ( )
POWER ( float_
expression , y )
40
Возвращаемое значение
Вычисляет абсолютное значение числа
Вычисляет арккосинус
Вычисляет арксинус
Вычисляет арктангенс
Возвращает угол в радианах
между положительным направлением оси X и лучом, проведенным из начала координат
в точку (y, x), где x и y — значения двух указанных выражений с плавающей запятой.
Возвращает наименьшее целое число, которое больше
или равно данному числовому выражению.
Вычисляет косинус угла
Возвращает котангенс угла
Преобразует значение угла
из радиан в градусы
Возвращает экспоненту
Выполняет округление вниз
Вычисляет натуральный логарифм
Вычисляет десятичный логарифм
Возвращает значение «пи»
Возводит число в степень
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
RADIANS
RADIANS ( numeric_
expression )
Преобразует значение угла
из градуса в радианы
RAND
RAND ( [ seed ] )
Возвращает случайное число
ROUND
ROUND ( numeric_
expression , length [
,function ] )
Выполняет округление с заданной точностью
Возвращает положительное
(+1), нулевое (0) или отрицательное (-1) значение, обозначающее знак заданного
выражения.
SIGN
SIGN ( numeric_
expression )
SIN
SIN ( float_expression )
Вычисляет синус угла
SQUARE
SQUARE ( float_
expression )
Выполняет возведение числа
в квадрат
SQRT
SQRT ( float_expression )
Извлекает квадратный корень
TAN
TAN ( float_expression )
Возвращает тангенс угла
Пример применения функции POWER .
DECLARE @value int, @counter int;
SET @value = 2;
SET @counter = 1;
WHILE @counter < 5
BEGIN
SELECT POWER(@value, @counter)
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END;
Пример получения различных случайных числа, сформированных функцией RAND.
DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 5
BEGIN
SELECT RAND() Random_Number
41
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
GO
END;
SET @counter = @counter + 1
Cтроковые функции
Краткий обзор строковых функций представлен в таблице.
Функция
Синтаксис
ASCII
ASCII ( character_
expression )
CHAR
CHAR ( integer_
expression )
CHARINDEX
CHARINDEX (
expression1 ,expression2
[ , start_location ] )
DIFFERENCE
LEFT
LEN
LOWER
LTRIM
NCHAR
DIFFERENCE (
character_expression ,
character_expression )
LEFT ( character_
expression , integer_
expression )
LEN ( string_expression )
LOWER ( character_
expression )
LTRIM ( character_
expression )
NCHAR ( integer_
expression )
PATINDEX
PATINDEX
( ‘%pattern%’, expression )
REPLACE
REPLACE ( string_
expression1 , string_
expression2 , string_
expression3 )
REPLICATE
REPLICATE ( string_
expression ,integer_
expression )
42
Возвращаемое значение
Возвращает код ASCII
первого символа указанного символьного выражения.
По коду ASCII возвращает символ
Определяет порядковый
номер символа, с которого начинается вхождение подстроки в строку
Возвращает показатель
совпадения строк
Возвращает указанное
число символов с начала
строки
Возвращает длину строки
Переводит все символы
строки в нижний регистр
Удаляет пробелы в начале строки
Возвращает по коду
символ Unicode
Выполняет поиск подстроки в строке по указанному шаблону
Заменяет вхождения подстроки на указанное значение. Заменяет в строке1
все вхождения строки2 на
строку3.
Выполняет тиражирование строки определенное число раз
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
REVERSE ( character_
expression )
RIGHT ( character_
expression , integer_
expression )
RTRIM ( character_
expression )
SOUNDEX ( character_
expression )
SPACE ( integer_
expression )
STR
STR ( float_expression [ ,
length [ , decimal ] ] )
STUFF
STUFF ( character_
expression , start , length,
character_expression )
SUBSTRING
SUBSTRING
( expression ,start , length )
UNICODE
UNICODE ( ‘ncharacter_
expression’ )
UPPER
UPPER ( character_
expression )
Возвращает строку, символы которой записаны
в обратном порядке
Возвращает указанное
число символов с конца
строки
Удаляет пробелы в конце строки
Возвращает код звучания строки
Возвращает указанное
число пробелов
Выполняет конвертирование значения числового типа в символьный
формат
Удаляет указанное число символов, заменяя
новой подстрокой
Возвращает для строки
подстроку указанной длины с заданного символа
Возвращает Unicode-код
левого символа строки
Переводит все символы
строки в верхний регистр
SELECT Фирма, [Фамилия]+» «+Left([Имя],1)+».»+Left([Отчес
тво],1
FROM Клиент
Использование функции LEFT для получения инициалов
клиентов.
43
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Функции для работы с датой и временем
Краткий обзор основных функций для работы с датой и временем представлен в таблице.
Функция
DATEADD
DATEDIFF
Синтаксис
DATEADD
( datepart ,
number, date )
DATEDIFF
( datepart , startdate , enddate )
DATENAME
DATENAME
( datepart , date )
DATEPART
DATEPART
( datepart , date )
DAY
DAY( date )
GETDATE
GETDATE()
ISDATE
ISDATE ( date )
MONTH
MONTH ( date )
YEAR
YEAR ( date )
Возвращаемое значение
Добавляет к дате указанное значение дней, месяцев, часов и т. д.
Возвращает разницу между указанными частями двух дат
Выделяет из даты указанную
часть и возвращает ее в символьном формате
Выделяет из даты указанную
часть и возвращает ее в числовом
формате
Возвращает число из указанной
даты
Возвращает текущее системное
время
Проверяет правильность выражения на соответствие одному из
возможных форматов ввода даты
Возвращает значение месяца из
указанной даты
Возвращает значение года из указанной даты
Datepart
Допустимые сокращения
yy, yyyy
qq, q
mm, m
dy, y
dd, d
wk, ww
hh
mi, n
ss, s
ms
Year – год
Quarter – квартал
Month – месяц
Dayofyear – день года
Day – день
Week – неделя
Hour – час
Minute – минута
Second – секунда
Millisecond – миллисекунда
44
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример использование функций YEAR,
и DATEDIFF
Средства, накопленные за последние 30 дней.
MONTH
SELECT Year([Дата проведения])) AS Год, Month([Дата проведения]))
AS Месяц, Sum(Сумма) AS Итог
FROM R2
WHERE ABS( Datediff(d, ([Дата проведения],Getdate()) )<30
GROUP BY Year([Дата проведения])), Month([Дата проведения]))
Контрольные вопросы
1. Какие виды функций используются в MS SQL SERVER?
2. Какие имена используются для написания системных
функций?
3. Можно ли использовать функции в командах SQL?
4. Какие значения могут возвращать функции?
5. Триггеры DML
Триггеры DML – это хранимые процедуры особого типа, автоматически вступающие в силу, если происходит событие языка
обработки данных DML, которое затрагивает таблицу или представление, определенное в триггере. События DML включают инструкции INSERT, UPDATE или DELETE. Триггеры DML могут
использоваться для предписания бизнес-правил и правил целостности данных, выполнения запросов к другим таблицам и включения сложных инструкций Transact-SQL. Триггер и инструкция,
при выполнении которой он срабатывает, считаются одной транзакцией, которую можно откатить назад внутри триггера.
Триггеры DML аналогичны ограничениям в том, что могут
предписывать целостность сущностей или целостность домена.
Вообще говоря, целостность сущностей должна всегда задаваться на самом нижнем уровне с помощью индексов, являющихся
частью ограничений PRIMARY KEY и UNIQUE или создаваемых
независимо от ограничений. Целостность домена должна быть
предписана через ограничения CHECK, а ссылочная целостность
45
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
– через ограничения FOREIGN KEY. Триггеры DML наиболее
полезны в тех случаях, когда функции ограничений не удовлетворяют функциональным потребностям приложения.
В следующем списке приведено сравнение триггеров DML
с ограничениями и указано, в чем триггеры DML имеют преимущества.
- Триггеры DML позволяют каскадно проводить изменения
через связанные таблицы в базе данных; но эти изменения могут
осуществляться более эффективно с использованием каскадных
ограничений ссылочной целостности. Ограничения FOREIGN
KEY могут проверить значения столбца только на предмет точного совпадения со значениями другого столбца.
- Для предотвращения случайных или неверных операций
INSERT, UPDATE и DELETE и реализации других более сложных ограничений, чем те, которые определены при помощи
ограничения CHECK. В отличие от ограничений CHECK, DMLтриггеры могут ссылаться на столбцы других таблиц. Например,
триггер может использовать инструкцию SELECT для сравнения
вставленных или обновленных данных и выполнения других действий, например изменения данных или отображения пользовательского сообщения об ошибке.
- Чтобы оценить состояние таблицы до и после изменения
данных и предпринять действия на основе этого различия.
- Несколько DML-триггеров одинакового типа (INSERT,
UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.
- Ограничения могут сообщать об ошибках только с помощью соответствующих стандартных системных сообщений. Если
для пользовательского приложения требуются более сложные
методы управления ошибками и, соответственно, пользовательские сообщения, то необходимо использовать триггер.
- При использовании триггеров DML может произойти откат
изменений, нарушающих ссылочную целостность, что приводит
к запрету модификации данных. Подобные триггеры могут применяться при изменении внешнего ключа в случаях, когда новое
46
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
значение не соответствует первичному ключу. Обычно в указанных случаях используются ограничения FOREIGN KEY.
- Если в таблице существуют ограничения, то их проверка осуществляется между выполнением триггеров INSTEAD OF
и AFTER. В случае нарушения ограничений выполняется откат
действий триггеров INSTEAD OF, а триггер AFTER не срабатывает.
Основной формат команды создания триггера
CREATE TRIGGER [имя_триггера]
ON имя_таблицы
{ FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,][ DELETE]}
[WITH ENCRYPTION]
AS SQL_операторы
используя предложение IF UPDATE
CREATE TRIGGER [имя_триггера]
ON имя_таблицы
{ FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] }
[WITH ENCRYPTION]
AS
IF UPDATE (имя_столбца) [{AND | OR} UPDATE (имя_столбца)...]
| IF (COLUMNS_UPDATES(){оператор_бит_обработки} бит_маска_изменения)
{оператор_бит_сравнения } бит_маска [...n]}
sql_оператор [...n]}
SQL_операторы
ON имя_таблицы – объявляется таблица или представление, от
которых зависит триггер.
WITH ENCRYPTION имеет тот же смысл, что и для хранимых
процедур.
{ FOR | AFTER | INSTEAD OF} – указывает, когда должен запускаться триггер. Ключевые слова FOR и AFTER являются синонимами.
47
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
IF UPDATE (имя_столбца) [{AND | OR} UPDATE (имя_столбца)...]
– позволяет выбрать конкретный столбец, запускающий триггер.
COLUMNS_UPDATES – возвращает битовый шаблон varbinary,
который показывает, какие столбцы таблицы или представления
добавлялись или изменялись.
В большинстве СУБД действуют следующие ограничения:
- Нельзя использовать в теле триггера операции создания объектов базы данных (новой базы данных, новой таблицы, новой
хранимой процедуры, нового триггера, новых представлений).
- Нельзя использовать в триггере команду удаления объектов DROP для всех типов базовых объектов базы данных.
- Нельзя использовать в теле триггера команды изменения
базовых объектов ALTER TABLE, ALTER DATABASE.
- Нельзя изменять права доступа к объектам базы данных,
т. е. выполнять команду GRAND или REVOKE.
- Нельзя создать триггер для представления (VIEW) .
- В отличие от хранимых процедур триггер не может возвращать никаких значений, он запускается автоматически сервером
и не может связаться самостоятельно ни с одним клиентом.
- Внутри триггера не допускается выполнение восстановления резервной копии БД или журнала транзакций.
Выполнение этих команд не разрешено, так как они не могут быть
отменены в случае отката транзакции, в которой выполняется триггер.
Триггер FOR (AFTER)
Триггеры ��������������������������������������������
FOR�����������������������������������������
(����������������������������������������
AFTER�����������������������������������
) выполняются после выполнения действий инструкции INSERT, UPDATE или DELETE. Триггеры
FOR(AFTER) никогда не выполняются, если происходит нарушение
ограничения, поэтому эти триггеры нельзя использовать для какойлибо обработки, которая могла бы предотвратить нарушение ограничения. Если в результате работы триггера была обнаружена ошибка,
то необходимо выполнить откат транзакции с помощью команды
ROLLBACK TRANSACTION для отмены действия инструкции, если
ошибок нет, то никаких дополнительных действий не нужно.
48
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Триггер INSTEAD OF
Триггеры INSTEAD OF переопределяют стандартные действия инструкции, вызывающей триггер. Поэтому они могут
использоваться для проверки на наличие ошибок или проверки
значений на одном или нескольких столбцах и выполнения дополнительных действий перед вставкой, обновлением или удалением одной или нескольких строк. Например, если обновляемое
значение в столбце почасовой оплаты в таблице учетной ведомости начинает превышать определенное значение, то с помощью
этого триггера можно либо задать вывод сообщения об ошибке
и откатить транзакцию, либо сделать вставку новой записи в след
аудита до вставки записи в таблицу учетной ведомости. Главное
преимущество триггеров INSTEAD OF в том, что они позволяют поддерживать обновления для таких представлений, которые
обновлять невозможно. Например, в представлении, основанном
на нескольких базовых таблицах, должен использоваться триггер INSTEAD OF для поддержки операций вставки, обновления
и удаления, которые ссылаются на данные больше чем в одной
таблице. Другое преимущество триггера INSTEAD OF состоит в том, что он обеспечивает логику кода, при которой можно
отвергать одни части пакета и принимать другие. Если триггер
INSTEAD OF отработал успешно, необходимо продублировать
инструкцию, вызвавшую триггер, так как данные не попали в таблицу, если возникла ошибка, никаких действий не требуется.
Функциональность триггеров AFTER и INSTEAD OF сравнивается в следующей таблице.
Функция
Сущности, к которым применяется
триггер
Количество триггеров на таблицу или
представление
Триггер AFTER
Таблицы
Триггер INSTEAD OF
Таблицы и представления
Несколько триггеров на
одно запускающее триггеры действие (UPDATE,
DELETE или INSERT).
sp_settriggerorder позволяет задать порядок выполнения триггеров.
Один триггер на
одно запускающее
триггеры действие
(UPDATE, DELETE
или INSERT)
49
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Каскадные
ссылки
Нет ограничений
Выполнение
После следующих операций.
• Обработка
ограничений.
• Декларативные
ссылочные действия.
• Создание
таблиц inserted и
deleted.
• Действие, запускающее триггер.
Порядок
выполнения
Можно задать выполнение в первую и в последнюю очередь.
Нужно выполнить откат транзакции
Дополнительные действия не нужны
При возникновении ошибки
При успешном завершении работы
Ссылки на столбцы varchar(max),
nvarchar(max) и
varbinary(max) в
таблицах inserted
и deleted
Ссылки на столбцы text, ntext и
image в таблицах
inserted и deleted
Триггеры INSTEAD
OF UPDATE и
DELETE нельзя
определять для таблиц, на которые
распространяются
каскадные ограничения ссылочной
целостности
Перед следующей
операцией.
• Обработка
ограничений.
Вместо следующей
операции.
• Действие,
запускающее
триггер.
После следующих
операций.
• Создание
таблиц inserted
и deleted
Неприменимо
Разрешены
Дополнительные
действия не нужны
Нужно выполнить
действие запустившее триггер
(данные из таблиц
inserted и deleted)
Разрешены
Не допускается
Разрешены
50
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Триггер может быть создан только в текущей базе данных,
но допускается обращение внутри триггера к другим базам данных, в том числе и расположенным на удаленном сервере.
Имя триггера должно быть уникальным в пределах базы данных. Дополнительно можно указать имя владельца.
По умолчанию в SQL Server все триггеры являются AFTERтриггерами.
Программирование триггера
При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы: inserted
и deleted. В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. Структура таблиц
inserted и deleted идентична структуре таблиц, для которой определяется триггер. Для каждого триггера создается свой комплект
таблиц inserted и deleted, поэтому никакой другой триггер не
сможет получить к ним доступ. В зависимости от типа операции,
вызвавшей выполнение триггера, содержимое таблиц inserted
и deleted может быть разным:
команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу; в таблице
deleted не будет ни одной строки;
команда DELETE – в таблице deleted будут содержаться все
строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее
удаление; в таблице inserted не окажется ни одной строки;
команда UPDATE – при ее выполнении в таблице deleted
находятся старые значения строк, которые будут изменены при
успешном завершении триггера. Новые значения строк содержатся в таблице inserted.
Для получения информации о количестве строк, которое будет изменено при успешном завершении триггера, можно использовать функцию @@ROWCOUNT; она возвращает количество
строк, обработанных последней командой. Следует подчеркнуть,
что триггер запускается не при попытке изменить конкретную
строку, а в момент выполнения команды изменения. Одна такая
51
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
команда воздействует на множество строк, поэтому триггер должен обрабатывать все эти строки либо одним запросом, либо построчно, используя курсор.
Триггер выполняется как неявно определенная транзакция,
поэтому внутри триггера допускается применение команд управления транзакциями.
Для удаления триггера используется команда
DROP TRIGGER {имя_триггера} [,...n]
Примеры использования триггеров.
R1([Номер счета], Баланс)
R2([Номер счета], Сумма, [Дата проведения])
Использование триггера для реализации ограничений на значение. В добавляемой в таблицу R2 записи снимаемая Сумма
должна быть не больше, чем Баланс из таблицы R1.
Команда вставки записи в таблицу R2 может быть, например, такой:
INSERT INTO R2 VALUES (‘A123’ , -299 ,’01/08/2013’)
Или
INSERT INTO R2 SELECT [Номер счета], -1000,’01/08/2013’
FROM R1 WHERE [Номер счета] LIKE ‘%123’
Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если
в R1 величина Баланса оказалась меньше снимаемой Суммы с
введенным Номером счета (в примере [Номер счета]= ‘A123’ или
[Номер счета] LIKE������������������������������������������
����������������������������������������������
‘%123’). Во вставляемой записи сумма указывается со знаком «+», если счет пополняется, и со знаком «-»,
если с него снимаются деньги. Представленный триггер настроен
на обработку сразу всех добавляемых записей, и если хотя бы
одна недопустима, происходит откат всех.
CREATE TRIGGER Provodka
ON R2 FOR INSERT
AS
IF @@ROWCOUNT > 0
52
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
BEGIN
IF EXISTS (SELECT * FROM inserted JOIN R1
ON inserted.[Номер счета] = R1.[Номер счета]
WHERE R1.Баланс+inserted.Сумма < 0)
BEGIN
-- откат действий команды
ROLLBACK TRANSACTION
RIASERROR(‘Отмена проводки’, 16, 1)
END
END
Аналогичные действия, но с использованием триггера INSTEAD OF.
CREATE TRIGGER Provodka1
ON R2 INSTEAD OF INSERT
AS
IF @@ROWCOUNT > 0
BEGIN
IF NOT EXISTS (SELECT * FROM inserted JOIN R1
ON inserted.[Номер счета] = R1.[Номер счета]
WHERE R1.Баланс+inserted.Сумма < 0)
BEGIN -- подтверждение действия команды
INSERT INTO R2 SELECT * FROM inserted
END
END
Создать триггер для обработки операции удаления записи
из таблицы R2, например, такой команды:
DELETE FROM R2 WHERE [Дата проведения] = GETDATE()
Для Счетов, номера которых указаны при удалении записи,
необходимо откорректировать их Баланс в таблице R1. Триггер
обрабатывает все удаляемые записи с использованием курсора.
CREATE TRIGGER del_provodka
ON R2 FOR DELETE
AS
DECLARE @nomer varchar(59),@summa money
-- создание курсора для обработки отменяемых действий
DECLARE provodka_cursor CURSOR FOR
SELECT [Номер счета], SUM(Сумма) AS S
FROM deleted GROUP BY [Номер счета]
OPEN provodka _cursor
FETCH NEXT FROM provodka _cursor INTO @nomer , @summa
WHILE @@FETCH_STATUS=0
53
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
BEGIN
UPDATE R1 SET Баланс = Баланс + @summa
WHERE [Номер счета] = @nomer
FETCH NEXT FROM provodka _cursor INTO @nomer, @summa
END
CLOSE provodka _cursor
DEALLOCATE provodka _cursor
Контрольные вопросы
1. Что такое триггер?
2. Какие виды триггеров бывают?
3. Какие основные правила программирования триггеров
FOR?
4. Какие основные правила программирования триггеров
INSTEAD OF?
5. Какую информацию содержат таблицы inserted и deleted?
Литература
1. Бондарь, А. Microsoft SQL Server 2012 / А. Бондарь. – СПб.:
Изд-во: БХВ-Петербург, 2013. – 608 с.
2. Вьейра, Р. Программирование баз данных Microsoft SQL
Server 2008. Базовый курс / Р. Вьейра. – СПб.: Изд-во: Диалектика, Вильямс, 2010 г. – 816 с.
3. Ицик, Бен-Ган. Microsoft����������������������������
�������������������������������������
SQL������������������������
���������������������������
Server�����������������
�����������������������
2012. Высокопроизводительный код T-SQL. Оконные функции/ Бен-Ган Ицик. –
СПб.: Русская Редакция; БХВ-Петербург, 2013. – 256 с.
4. Ицик, Бен-Ган. Microsoft SQL Server 2008. Основы T-SQL
/ Бен-Ган Ицик. – СПб.: БХВ-Петербург, 2009. – 430 с.
5. URL:http://msdn.microsoft.com/ru-ru/library/bb510741.aspx.
Справочник по Transact-SQL (компонент Database Engine).
54
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Оглавление
Введение..............................................................................................3
1. Транзакции......................................................................................4
Блокировки ........................................................................................6
Управление транзакциями в среде MS SQL Server........................9
Явные транзакции..............................................................................9
Вложенные транзакции....................................................................11
Уровни изоляции MS SQL Server...................................................12
2. Операторы процедурной логики.................................................14
Переменные......................................................................................14
Преобразование типов.....................................................................15
Управляющие операторы................................................................17
Курсоры.............................................................................................19
3. Хранимые процедуры...................................................................25
Контрольные вопросы.....................................................................32
4. Пользовательские функции.........................................................33
Скалярные функции.........................................................................34
Inline функции..................................................................................36
Multi-statement функции..................................................................38
Встроенные функции.......................................................................39
Математические функции...............................................................40
Cтроковые функции.........................................................................42
Функции для работы с датой и временем......................................44
5. Триггеры DML..............................................................................45
Триггер FOR (AFTER).....................................................................48
Триггер INSTEAD OF.....................................................................49
Программирование триггера..........................................................51
Литература.....................................................................................54
55
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Учебное издание
Составитель
Власова Ольга Владимировна
Transact-SQL
Методические указания
Редактор, корректор М. В. Никулина
Верстка Е. Б. Половковой
Подписано в печать 22.07.2013. Формат 60×841/16.
Усл. печ. л. 3,25. Уч.-изд. л. 2,0.
Тираж 50 экз. Заказ
.
Оригинал-макет подготовлен
в редакционно-издательском отделе ЯрГУ.
Ярославский государственный университет
им. П. Г. Демидова.
150000, Ярославль, ул. Советская, 14.
56
Документ
Категория
Без категории
Просмотров
32
Размер файла
438 Кб
Теги
sql, transact, 210
1/--страниц
Пожаловаться на содержимое документа