 |
Удаление хранимой процедуры
|
|
|
|
Лабораторная работа № 3
По дисциплине «Базы и банки данных»
T- SQL. Создание хранимых процедур.
Цель работы – научиться создавать хранимые процедуры в СУБД MS SQL Server. Реализовать хранимые процедуры для изменения данных.
Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде.
Типы хранимых процедур
Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные.
Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера.
Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.
|
|
|
Создание хранимой процедуры предполагает решение следующих задач: планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь; определение параметров хранимой процедуры, хранимые процедуры могут обладать входными и выходными параметрами; разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур.
Для передачи входных и выходных данных в создаваемой хранимой процедуре имена параметров должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры.
Для определения типа данных параметров хранимой процедуры подходят любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT.
Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры.
Хранимые процедуры имеют очень много общего с обычными процедурами, широко используемыми в различных языках программирования. Определение хранимой процедуры заключается в исполнении следующей команды:
CREATE PROCEDURE] procedure_name [;number]
[ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [,...n]
[WITH { RECOMPILE
| ENCRYPTION
| RECOMPILE. ENCRYPTION } ]
[FOR REPLICATION]
AS sql_statement [...n]
Синтаксис имеет следующее назначение:
|
|
|
procedure_name – имя создаваемой процедуры. Используя префиксы sp_, # и ##, можно определить создаваемую процедуру как системную или временную. При этом, однако, необходимо позаботиться и о размещении процедуры в соответствующей системной базе данных, поскольку команда CREATE PROCEDURE создает хранимую процедуру в текущей базе данных. Поэтому перед созданием процедуры необходимо выполнить команду USE, чтобы сделать требуемую базу данных текущей;
number – параметр определяет идентификационный номер хранимой процедуры, однозначно определяющий ее в группе процедур;
@parameter – определяет имя параметра, который будет использоваться создаваемой хранимой процедурой для передачи входных или выходных данных. Параметры, определяемые при создании хранимой процедуры, являются локальными переменными, поэтому несколько хранимых процедур могут иметь абсолютно идентичные параметры;
data_type – определяет, к какому типу данных должны относиться значения параметра описываемой процедуры. Для определения параметров можно использовать любые типы данных;
OUTPUT – использование этого ключевого слова определяет указанный параметр как выходной;
VARYING – ключевое слово, которое используется совместно с параметром, относящимся к типу данных cursor. Определяет, что в качестве выходного параметра будет представлено результирующее множество;
default – позволяет определить для параметра значение по умолчанию, которое хранимая процедура будет использовать в случае, если при ее вызове указанный параметр был опущен.
RECOMPILE – ключевое слово, предписывающее системе создавать план выполнения процедуры при каждом ее вызове;
FOR REPLICATION – процедура, определенная с использованием данного ключевого слова, предназначена исключительно для осуществления процесса репликации. Вы не можете сочетать это ключевое слово с ключевым словом WITH RECOMPILE;
ENCRYPTON – если при определении процедуры было использовано данное ключевое слово, то текст процедуры непосредственно перед записью в системную таблицу syscomments будет зашифрован. Вы можете прибегнуть к шифрованию, если необходимо скрыть от пользователя особенности реализации хранимой процедуры;
AS – ключевое слово, определяющее начало кода хранимой процедуры. После этого ключевого слова следуют команды Transact-SQL, которые и составляют непосредственно тело процедуры (sql statement). Здесь можно использовать любые команды, включая вызов других хранимых процедур, за исключением команд, начинающихся с ключевого слова CREATE.
|
|
|
Выход из хранимой процедуры можно осуществить посредством команды RETURN.
Удаление хранимой процедуры
DROP PROCEDURE {имя_процедуры} [,...n]
|
|
|
