Microsoft SQL Server 2000. Создание и использование хранимых процедур и пользовательских функций

Восстановление базы данных.

Убедитесь, что кроме вас никто не использует восстанавливаемую базу данных. Затем щелкните правой кнопкой мыши на имени базы данных, которую вы хотите восстановить и выберите команду Все задачи/Restore Database. Убедитесь, что в поле Restore as Database указана база данных, подлежащая восстановлению. Если ее имени нет в раскрывающемся списке, введите его вручную(таким образом можно восстановить базу под другим именем). В группе опций Restore установите переключатель Database. В области Parameters в поле Showbackups of database укажите имя восстанавливаемой базы данных, и Enterprise Manager покажет список ее резервных копий. По умолчанию в графе Restore установлены флажки резервных копий, необходимых для полного восстановления базы данных. Установите флажок только напротив полной резервной копии базы данных, которую вы делали в самом начале. Нажмите кнопку OK, после восстановления базы данных раскройте папку Tables восстановленной базы данных, выберите в контекстном меню пункт Обновить и убедитесь, что Table1 и Table2 восстановлены.

Вы можете восстановить базу данных и на момент времени, когда вы делали копию журнала транзакций, когда в базе данных оставалась только таблица Table2. Для этого при восстановлении поставьте флажки и напротив полной копии базы данных, и напротив копии журнала транзакций. Нажмите кнопку OK. Выберите пункт контекстного менюОбновить в папке Tables базы данных и убедитесь, что теперь в базе существует только одна таблица: Table2.

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

Хранимые процедуры очень полезны, поскольку они работают непосредственно на сервере. Применение подобных процедур очень хорошо укладывается в модель клиент/сервер. Так как система SQL Server управляет базами данных, она является лучшим местом для выполнения хранимых процедур, работающих с данными.

Хранимые процедуры могут возвращать значения. Внутри процедур можно выполнять сравнение вводимых пользователем значений с заранее установленной в системе информацией. Хранимые процедуры применяют в работе мощные аппаратные решения SQL Server. Они ориентированы на базы данных и тесно взаимодействуют с оптимизатором SQL Server. Это позволяет получить высокую производительность при обработке данных.

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

В самом общем случае хранимые процедуры обладают следующими преимуществами:

· Высокая производительность. Является результатом расположения хранимых процедур на сервере. Сервер — как правило, более мощная машина, поэтому время выполнения процедуры на сервере значительно меньше, чем на рабочей станции. Кроме того, информация из базы данных и хранимая процедура находятся в одной и той же системе, поэтому на передачу записей по сети время практически не затрачивается. Хранимые процедуры имеют непосредственный доступ к базам данных, что делает работу с информацией очень быстрой.

· Преимущество разработки системы в архитектуре клиент/сервер. Заключается в возможности раздельного создания программного обеспечения клиента и сервера. Это преимущество является ключевым при разработке, и благодаря ему можно значительно уменьшить время, необходимое для окончания проекта. Код, работающий на сервере, может разрабатываться отдельно от кода клиентской части. При этом компоненты серверной части могут совместно использоваться компонентами стороны клиента.

· Уровень безопасности. Х ранимые процедуры могут выступать в качестве инструмента улучшения безопасности. Можно создать хранимые процедуры, осуществляющие операции добавления, изменения, удаления и отображения списков, и, таким образом, получить контроль над каждым из аспектов доступа к информации.

· Усиление правил сервера, работающих с данными. Это одна из самых важных причин применения интеллектуального ядра баз данных. Хранимые процедуры позволяют применять правила и другую логику, помогающую контролировать вводимую в систему информацию.

Хранимые процедуры, работающие в динамических инструкциях SQL, дают ряд преимуществ:

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

o Хранимые процедуры могут быть выполнены либо на локальной машине, либо на удаленной системе SQL Server. Это дает возможность активизировать процессы на других машинах и работать не только с локальными базами данных, но и с информацией на нескольких серверах.

o Прикладные программы, написанные на таком языке высокого уровня, как С или Visual Basic, могут также вызывать хранимые процедуры, что обеспечивает оптимальное решение по распределению нагрузки между программным обеспечением клиентской части и SQL Server.

Для создания хранимой процедуры применяется инструкция create procedure. По умолчанию, выполнить созданную процедуру может только владелец базы данных. Он же может изменить разрешение доступа и позволить другим пользователям работать с этой процедурой. Имя хранимой процедуры может быть длиной до 128 символов, включая символы # и ##.

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

CREATE PROCEDURE] имя_процедуры [; число]

[{@параметр тип__данных] [VARYING]

[= значение__по_умолчанию] [OUTPUT]

]

[,... n]

[WITH

{

RECOMPILE |

ENCRYPTION |

RECOMPILE, ENCRYPTION

}

[FOR REPLICATION]

AS

и нструкции_SQL

Рассмотрим некоторые параметры этой команды:

· имя_процедуры — имя процедуры должно удовлетворять правилам для идентификаторов, его длина не может превышать 128 символов; для локальных временных процедур перед именем используется знак #, а для глобальных временных процедур — знаки ##

· число — необязательное целое число, используемое для группировки нескольких процедур под одним именем

· @ параметр тип_данных — список имен параметров процедуры с соответствующим типом данных для каждого; таких параметров может быть до 1024; в качестве значения параметра разрешается передавать null; могут использоваться все типы данных — никаких ограничений нет; в качестве выходного параметра (используется ключевое слово output или varying) можно использовать тип данных cursor

· varying — ключевое слово, определяющее, что в качестве выходного параметра используется результирующий набор — только для типа cursor

· значение_по_умолчанию — используется в случае, когда при вызове процедуры параметр пропущен; должно быть константой и может включать символы.маски (%, _, [] и ^) и значение null

· with recompile — ключевые слова, показывающие, что SQL Server не будет записывать план процедуры в кэш, а будет создавать его каждый раз при выполнении

· with encryption — ключевые слова, показывающие, что SQL Server будет зашифровывать процедуру перед записью в системную таблицу syscomments

· for replication — ключевые слова, показывающие, что эта процедура создается только для репликации; несовместимы с ключевыми словами WITH RECOMPILE

· as — начало определения текста процедуры

· инструкции_SQL — любой набор допустимых инструкций SQL, ограниченный только максимальным размером хранимой процедуры — 128 Кб; недопустимыми являются следующие операторы:

CREATE DEFAULT CREATE TRIGGER

CREATE PROCEDURE

CREATE RULE CREATE VIEW

После создания процедуры достаточно ввести ее имя в командную строку или окно SQL Server Query Analyzer и выполнить как обычную инструкцию. Если вызов хранимой процедуры следует за выполнением других инструкций, для ее активизации необходимо применять команду EXEC[UTE] имя_процедуры (при вызове хранимой процедуры первой в последовательности команд можно опустить ключевое слово ехес в инструкции ехес имя_процедуры. Ее можно просто вызвать по имени, и она будет выполнена автоматически).

Поскольку хранимая процедура является полноценным компонентом базы данных, то, создать новую процедуру можно только для текущей базы данных. При работе в SQL Server Query Analyzer установление текущей базы данных выполняется с помощью оператора use, за которым следует имя базы данных, где должна быть создана хранимая процедура. Внутрь хранимой процедуры может быть помещена любая инструкция Transact-SQL, за исключением инструкции create.

После создания в системе хранимой процедуры SQL Server компилирует ее и проверяет выполняемые внутри нее подпрограммы. При возникновении каких-либо проблем процедура отвергается. Перед повторной трансляцией ошибки должны быть устранены. В SQL Server 7.0 используется отложенное распознавание имен (delayed name resolution), поэтому если хранимая процедура содержит обращение к другой, еще нереализованной процедуре выводится предупреждение, но вызов несуществующей процедуры все же сохранится.

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

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

Основными принципами применения параметров в хранимых процедурах являются:

· Для процедуры может быть определен один или несколько параметров

· Параметры используются в качестве именованных мест хранения данных точно так же, как переменные в языках программирования, таких как С или Visual Basic

· Имя параметра обязательно предваряется символом @

· Имена параметров являются локальными в той процедуре, где они определены

· Параметры служат для передачи информации в процедуру при ее выполнении. Они помещаются в командной строке после имени процедуры.

· В случае, если процедура имеет несколько параметров, они разделяются запятыми.

· Для определения типа информации, передаваемой в качестве параметра, применяют системные типы данных

В SQL Server имеется огромное количество системных хранимых процедур. Все они начинаются с префикса sp_ и находятся в базе данных master. Созданные процедуры хранятся в соответствующей базе данных. При вызове любой хранимой процедуры, имя которой начинается с префикса sp_, SQL Server всегда производит поиск процедуры в следующем порядке:

1. В базе данных master.

2. В базе данных, указанной при вызове.

3. В текущей базе данных.

Чем чревато присвоение своей хранимой процедуре имени, начинающегося с префикса sp_? Во-первых, увеличением времени ее поиска — как минимум, надо "пройтись" по базе данных master. Во-вторых, если случайно имя процедуры совпадет с именем системной, то результатов выполнения такой процедуры вы никогда не увидите, поскольку она будет замаскирована одноименной процедурой из базы данных master. Это следует иметь в виду при присвоении имен хранимым процедурам.

Просмотр определения процедуры выполняется с помощью системной процедуры sp_helptext, а системная процедура sp_help позволяет вывести контрольную информацию о ней. Системные процедуры sp_helptext и sp_help используются и для просмотра таких объектов баз данных, как таблицы, правила и установки по умолчанию.

В SQL Server Query Analyzer хранимая процедура удаляется с помощью инструкции DROP PROCEDURE. С помощью одной такой инструкции может быть удалено несколько процедур. Имена удаляемых процедур указываются через запятую после ключевого слова drop procedure. Синтаксис:

DROP PROCEDURE имя_процедуры_1 [,..., имя_процедуры_п]

Несколько версий процедуры не могут быть удалены селективно. Все версии с одинаковым именем удаляются одновременно. При этом в инструкции drop procedure имя процедуры указывается без номера версии.

В SQL Server 7.0 появилась возможность изменять хранимые процедуры и с помощью Transact-SQL без их предварительного удаления. Для этого служит команда alter procedure, ее синтаксис:

ALTER PROC[EDURE] имя_процедуры [; число ]

[{@параметр тип_данных } [= значение_ло_умолчанию] [OUTPUT]

]

[,... л]

[ WITH

{

RECOMPILE |

ENCRYPTION |

RECOMPILE, ENCRYPTION

[ FOR REPLICATION]

AS

инструкции_ SQL

Структура и параметры инструкции alter procedure идентичны структуре и параметрам инструкции create procedure.

Дата добавления: 2015-05-09