Курсоры в прикладных программах часто используются для последовательного просмотра данных. Если курсор не связан с операцией группировки, то фактически каждая строка курсора соответствует строго только одной строке исходной таблицы, и в этом случае курсор удобно использовать для оперативной корректировки данных. В стандарте определены операции модификации данных, связанные с курсором. Операция удаления строки, связанной с текущим указателем курсора, имеет следующий синтаксис:
DELETE FROM <имя_таблицы> WHERE CURRENT OF <имя курсора>
Если указанный в операторе курсор открыт и установлен на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка курсора удаляется, а он позиционируется перед следующей строкой. Таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.
Если нам необходимо прочитать следующую строку курсора, то надо снова выполнить оператор FETCH NEXT.
Аналогично курсор может быть использован для модификации данных. Синтаксис операции позиционной модификации следующий:
Одним оператором позиционного обновления могут быть заменены несколько значений столбцов строки таблицы, соответствующей текущей позиции курсора. После выполнения операции модификации позиция курсора не изменяется.
Для того чтобы можно было применять позиционные операторы удаления (DELETE) и модификации (UPDATE), курсор должен удовлетворять определенным требованиям. Согласно стандарту SQL1, это следующие требования:
Запрос, связанный с курсором, должен считывать данные из одной исходной таблицы, то есть в предложении FROM запроса SELECT, связанного с определением курсора (DECLARE CURSOR), должна быть задана только одна таблица.
В запросе не может присутствовать параметр упорядочения ORDER BY. Для того чтобы сохранялось взаимно однозначное соответствие строк курсора и исходной таблицы, курсор не должен идентифицировать упорядоченный набор данных.
В запросе не должно присутствовать ключевое слово DISTINCT.
Запрос не должен содержать операций группировки, то есть в нем не должно присутствовать предложение GROUP BY или HAVING.
Пользователь, который хочет применить операции позиционного удаления или обновления, должен иметь соответствующие права на выполнение данных операций над базовой таблицей. (О правах и привилегиях пользователя мы поговорим в главе 13.)
Использование курсора для операций обновления значительно усложняет работу с подобным курсором со стороны СУБД, поэтому операции, связанные с позиционной модификацией, выполняются гораздо медленнее, чем операции с курсорами, которые используются только для чтения. Именно поэтому рекомендуется обязательно указывать в операторе определения курсора предложение READ ONLY, если вы не собираетесь использовать данный курсор для операций модификации. По умолчанию, если нет дополнительных указаний, СУБД создает курсор с возможностью модификации.
Курсоры — удобное средство для формирования бизнес-логики приложений, но следует помнить, что если вы открываете курсор с возможностью модификации, то СУБД блокирует все строки базовой таблицы, вошедшие в ваш курсор, и тем самым блокируется работа других пользователей с данной таблицей.
Чтобы свести к минимуму количество требуемых блокировок, при работе интерактивных программ следует придерживаться следующих правил:
Необходимо делать транзакции как можно короче.
Необходимо выполнять оператор завершения COMMIT после каждого запроса и как можно скорее после изменений, сделанных программой.
Необходимо избегать программ, в которых осуществляется интенсивное взаимодействие с пользователем или осуществляется просмотр очень большого количества строк данных.
Если возможно, то лучше не применять прокручиваемые курсоры (SCROLL), потому что они требуют блокирования всех строк выборки, связанных с открытым курсором.
Использование простого последовательного курсора позволит системе разблокировать текущую строку, как только будет выполнена операция FETCH, что минимизирует блокировки других пользователей, работающих параллельно с вами и использующих те же таблицы.
Если возможно, определяйте курсор как READ ONLY.
Однако когда мы рассматривали модели «клиент—сервер», применяемые в БД, то определили, что в развитых моделях серверов баз данных большая часть бизнес-логики клиентского приложения выполняется именно на сервере, а не на клиенте. Для этого используются специальные объекты, которые называются хранимыми процедурами и хранятся в БД, как таблицы и другие базовые объекты.
В связи с этим фактом курсоры, которые могут быть использованы в приложениях, обычно делятся на курсоры сервера и курсоры клиента. Курсор сервера создается и выполняется на сервере, данные, связанные с ним, не пересылаются на компьютер клиента. Курсоры сервера определяются обычно в хранимых процедурах или триггерах.
Курсоры клиента — это те курсоры, которые определяются в прикладных программах, выполняемых на клиенте. Набор строк, связанный с данным курсором, пересылается на клиент и там обрабатывается. Если с курсором связан большой набор данных, то операция пересылки набора строк, связанных с курсором, может занять значительное время и значительные ресурсы сети и клиентского компьютера.
Конечно, курсоры сервера более экономичны и выполняются быстрее. Поэтому последней рекомендацией, связанной с использованием курсоров, будет рекомендация трансформировать логику работы вашего приложения, чтобы как можно чаще вместо курсоров клиента использовать курсоры сервера.
Хранимые процедуры
С точки зрения приложений, работающих с БД, хранимые процедуры (Stored Procedure) — это подпрограммы, которые выполняются на сервере. По отношению к БД — это объекты, которые создаются и хранятся в БД. Они могут быть вызваны из клиентских приложений. При этом одна процедура может быть использована в любом количестве клиентских приложений, что позволяет существенно сэкономить трудозатраты на создание прикладного программного обеспечения и эффективно применять стратегию повторного использования кода. Так же как и любые процедуры в стандартных языках программирования, хранимые процедуры могут иметь входные и выходные параметры или не иметь их вовсе.
Хранимые процедуры могут быть активизированы не только пользовательскими приложениями, но и триггерами.
Хранимые процедуры пишутся на специальном встроенном языке программирования, они могут включать любые операторы SQL, а также включают некоторый набор операторов, управляющих ходом выполнения программ, которые во многом схожи с подобными операторами процедурно ориентированных языков программирования. В коммерческих СУБД для написания текстов хранимых процедур используются собственные языки программирования, так, в СУБД Oracle для этого используется язык PL /SQL, а в MS SQL Server и Systemll фирмы Sybase используется язык Transact SQL. В последних версиях Oracle объявлено использование языка Java для написания хранимых процедур.
Хранимые процедуры являются объектами БД. Каждая хранимая процедура компилируется при первом выполнении, в процессе компиляции строится оптимальный план выполнения процедуры. Описание процедуры совместно с планом ее выполнения хранится в системных таблицах БД.
Для создания хранимой процедуры применяется оператор SQL CREATE PROCEDURE.
По умолчанию выполнить хранимую процедуру может только ее владелец, которым является владелец БД, и создатель хранимой процедуры. Однако владелец хранимой процедуры может делегировать права на ее запуск другим пользователям.
Имя хранимой процедуры является идентификатором в языке программирования, на котором она пишется, и должно удовлетворять всем требованиям, которые предъявляются к идентификаторам в данном языке.
В MS SQL Server хранимая процедура создается оператором:
CREATE PROCEDURE] <имя_процедуры> [;<версия>]
[{@параметр1 тип_данных}
[VARYING] [= <значение_по_умолчанию>] [OUTPUT]]
[..параметрN..]
[ WITH
{ RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION}] [FOR REPLICATION]
AS
Тело процедуры
Здесь необязательное ключевое слово VARYING определяет заданное значение по умолчанию для определенного ранее параметра.
Ключевое слово RECOMPILE определяет режим компиляции создаваемой хранимой процедуры. Если задано ключевое слово RECOMPILE, то процедура будет перекомпилироваться каждый раз, когда она будет вызываться на исполнение. Это может резко замедлить исполнение процедуры. Но, с другой стороны, если данные, обрабатываемые данной хранимой процедурой, настолько динамичны, что предыдущий план исполнения, составленный при ее первом вызове, может быть абсолютно неэффективен при последующих вызовах, то стоит применять данный параметр при создании этой процедуры.
Ключевое слово ENCRYPTION определяет режим, при котором исходный текст хранимой процедуры не сохраняется в БД. Такой режим применяется для того, чтобы сохранить авторское право на интеллектуальную продукцию, которой и являются хранимые процедуры. Часто такой режим применяется, когда вы ставите готовую базу заказчику и не хотите, чтобы исходные тексты разработанных вами хранимых процедур были бы доступны администратору БД, работающему у заказчика. Однако надо помнить, что если вы захотите отредактировать текст хранимой процедуры сами, то вы его не сможете извлечь из БД тоже, его надо будет хранить отдельно в некотором текстовом файле. И это не самое плохое, но вот в случае восстановления БД после серьезной аварии для перекомпиляции потребуются первоначальные исходные тексты всех хранимых процедур. Поэтому защита вещь хорошая, но она усложняет сопровождение и модификацию хранимых процедур.
Однако кроме имени хранимой процедуры все остальные параметры являются необязательными. Процедуры могут быть процедурами или процедурами-функциями. И эти понятия здесь трактуются традиционно, как в языках программирования высокого уровня. Хранимая процедура-функция возвращает значение, которое присваивается переменной, определяющей имя процедуры. Процедура в явном виде не возвращает значение, но в ней может быть использовано ключевое слово OUTPUT, которое определяет, что данный параметр является выходным.
Рассмотрим несколько примеров простейших хранимых процедур.
/* процедура проверки наличия экземпляров данной книги
параметры:
@ISBN шифр книги
процедура возвращает параметр, равный количеству экземпляров
Если возвращается ноль, то это значит, что нет свободных экземпляров данной
книги в библиотеке.
*/
CREATE PROCEDURE COUNT_EX (@ISBN varchar(12)) AS
/* определим внутреннюю переменную */
DECLARE @TEK_COUNT int
/* выполним соответствующий оператор
SELECT
Будем считать только экземпляры, которые в настоящий момент находятся
не на руках у читателей, а в библиотеке */
select @TEK_COUNT = select count(*)
FROM EXEMPLAR WHERE ISBN = @ISBN
AND READERJD Is NULL AND EXIST = True
/* 0 - ноль означает, что нет ни одного свободного экземпляра данной книги в библиотеке */
RETURN @TEK_COUNT
Хранимая процедура может быть вызвана несколькими способами. Простейший способ — это использование оператора:
При этом все входные и выходные параметры должны быть заданы обязательно и в том порядке, в котором они определены в процедуре.
Например, если мне надо найти число экземпляров книги «Oracle8. Энциклопедия пользователя», которая имеет ISBN 966-7393-08-09, то текст вызова ранее созданной хранимой процедуры может быть следующим:
/* Определили две переменные
@Ntek - количество экземпляров данной книги в наличие в библиотеке
@ISBN - международный шифр книги */
declare @Ntek int
DECLARE @ISBN VARCHAR(14)
/* Присвоим значение переменной @ISBN */
Select @ISBN = '966-7393-08-09'
/* Присвоим переменной @Ntek результаты выполнения хранимой процедуры
COUNT_EX */
ЕХЕС @Ntek = COUNT_EX @ISBN
Если у вас определено несколько версий хранимой процедуры, то при вызове вы можете указать номер конкретной версии для исполнения. Tак, например, в версии 2 процедуры COUNT_EX последний оператор исполнения этой процедуры имеет вид:
ЕХЕС @Ntek = COUNT_EX:2 @ISBN
Однако если в процедуре определены значения входных параметров по умолчанию, то при запуске процедуры могут быть указаны значения не всех параметров. В этом случае оператор вызова процедуры может быть записан в следующем виде:
Например, создадим процедуру, которая считает количество книг, изданных конкретным издательством в конкретном году. При создании процедуры зададим для года издания по умолчанию значение текущего года.
CREATE PROCEDURE COUNT_BOOKS (@YEARIZD Int = Year(GetDate()),
@PUBLICH varchar(20))
/* процедура подсчета количества книг конкретного издательства, изданных в конкретом году
параметры:
@YEARIZD Int год издания
(PPUBLICH название издательства */ AS
DECLARE @TEK_Count int Select
@TEK count = Select COUNT(ISBN)
From BOOKS
Where YFARIZD = @YEARIZD AND PUBLICH =@PUBLICH
/* одновременно с исполнением оператора Select мы присваиваем результаты его работы определенной ранее переменной @TEK_Count */
/* при формировании результата работы нашей процедуры мы должны учесть, что в нашей библиотеке, возможно, нет ни одной книги некоторого издательства для заданного года. Результат выполнения запроса SELECT в этом случае будет иметь неопределенное значение, но анализировать все-таки лучше числовые значения. Поэтому в качестве возвращаемого значения мы используем результаты работы специальной встроенной функции Transact SQL COALESCE (nl.n2.....nm), которая возвращает первое конкретное, то есть не равное NULL, значение из списка значений nl.n2....,nm. */
Return COALESCE (@TEK_Count,0)
Теперь вызовем эту процедуру, для этого подготовим переменную, куда можно поместить результаты выполнения процедуры.
declare @N int
Exec @N = COUNTJOOKS @PUBLICH = 'Питер'
В переменной @N мы получим количество книг в нашей библиотеке, изданных издательством «Питер» в текущем году. Мы можем обратиться к этой процедуре и задав все параметры:
Тогда получим количество книг, изданных издательством «BHW» в 1999 году и присутствующих в нашей библиотеке.
Если мы задаем параметры по именам, то нам необязательно задавать их в том порядке, в котором они описаны при создании процедуры.
Каждая хранимая процедура является объектом БД. Она имеет уникальное имя и уникальный внутренний номер в системном каталоге. При изменении текста хранимой процедуры мы должны сначала уничтожить данную процедуру как объект, хранимый в БД, и только после этого записать на ее место новую. Следует отметить, что при удалении хранимой процедуры удаляются одновременно все ее версии, нельзя удалить только одну версию хранимой процедуры.
Для того чтобы автоматизировать процесс уничтожения старой процедуры и замены ее на новую, в начале текста хранимой процедуры можно выполнить проверку наличия объекта типа «хранимая процедура» с данным именем в системном каталоге и при наличии описания данного объекта удалить его из системного каталога. В этом случае текст хранимой процедуры предваряется специальным оператором проверки.
Хранимые процедуры допускают наличие нескольких выходных параметров. Для этого каждый выходной параметр должен после задания своего типа данных иметь дополнительное ключевое слово OUTPUT. Рассмотрим пример хранимой процедуры с несколькими выходными параметрами.
Теперь обратимся к оценке эффективности применения хранимых процедур.
Если рассмотреть этапы выполнения одинакового текста части приложения, содержащего SQL-операторы, самостоятельно на клиенте и в качестве хранимой процедуры, то можно отметить, что на клиенте выполняются все 5 этапов выполнения SQL-операторов, а хранимая процедура может храниться в БД в уже скомпилированном виде, и ее исполнение займет гораздо меньше времени (см. рис. 12.2).
Кроме того, хранимые процедуры, как уже упоминалось, могут быть использованы несколькими приложениями, а встроенные операторы SQL должны быть включены в каждое приложение повторно.
Рис. 12.2. Процесс выполнения операторов SQL на клиенте и процесс выполнения хранимой процедуры
Хранимые процедуры также играют ключевую роль в повышении быстродействия при работе в сети с архитектурой «клиент—сервер».
Триггеры
Фактически триггер — это специальный вид хранимой процедуры, которую SQLServer вызывает при выполнении операций модификации соответствующих таблиц. Триггер автоматически активизируется при выполнении операции, с которой он связан. Триггеры связываются с одной или несколькими операциями модификации над одной таблицей.
В разных коммерческих СУБД рассматриваются разные триггеры. Так, в MS SQL Server триггеры определены только как постфильтры, то есть такие триггеры, которые выполняются после свершения события.
В СУБД Oracle определены два типа триггеров: триггеры, которые могут быть запущены перед реализацией операции модификации, они называются BEFORE-триггерами, и триггеры, которые активизируются после выполнения соответствующей модификации, аналогично триггерам MS SQL Server, — они называются AFTER-тригерами.
Триггеры могут быть эффективно использованы для поддержки семантической целостности БД, однако приоритет их ниже, чем приоритет правил-ограничений (constraints), задаваемых на уровне описания таблиц и на уровне связей между таблицами. При написании триггеров всегда надо помнить об этом, при нарушении правил целостности по связям (DRI declarative Referential Integrity) триггер просто может никогда не сработать.
В стандарте SQL1 ни хранимые процедуры, ни триггеры были не определены. Но в добавлении к стандарту SQL2, выпущенному в 1996 году, те и другие объекты были стандартизированы и определены.
Для создания триггеров используется специальная команда:
CREATE TRIGGER <имя_триггера> ON <имя_таблицы>
FOR {[INSERT][. UPDATE] [, DELETE] } [WITH ENCRIPTING]
AS
SQL-операторы (Тело триггера)
Имя триггера является идентификатором во встроенном языке программирования СУБД и должно удовлетворять соответствующим требованиям.
В параметре FOR задается одна или несколько операций модификации, которые запускают данный триггер.
Параметр WITH ENCRIPTING имеет тот же смысл, что и для хранимых процедур, он скрывает исходный текст тела триггера.
Существует несколько правил, которые ограничивают набор операторов, которые могут быть использованы в теле триггера.
Так, в большинстве СУБД действуют следующие ограничения:
Нельзя использовать в теле триггера операции создания объектов БД (новой БД, новой таблицы, нового индекса, новой хранимой процедуры, нового триггера, новых индексов, новых представлений).
Нельзя использовать в триггере команду удаления объектов DROP для всех типов базовых объектов БД.
Нельзя использовать в теле триггера команды изменения базовых объектов ALTER TABLE, ALTER DATABASE.
Нельзя изменять права доступа к объектам БД, то есть выполнять команду GRAND или REVOKE.
Нельзя создать триггер для представления (VIEW) .
В отличие от хранимых процедур, триггер не может возвращать никаких значений, он запускается автоматически сервером и не может связаться самостоятельно ни с одним клиентом.
