Создание хранимых процедур
Типы хранимых процедур
В SQL Server 2000 имеют место следующие типы хранимых процедур:
· Системные хранимые процедуры поставляются в составе SQL Server и предназначены для выполнения различных административных действий. Эти процедуры имеют префикс sp_ и харнятся в системной базе данных Master.
· Пользовательские хранимые процедуры хранятся в пользовательских базах данных.
· Временные хранимые процедуры – существуют лишь некоторое время, после чего автоматически уничтожаются сервером, и делятся на локальные и глобальные:
o Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором они созданы. Имя таких процедур начинается символом #.
o Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором была создана соответствующая процедура. Для определения такой процедуры достаточно перед ее именем поставить два знака ##.
Все временные процедуры хранятся в базе данных Tempdb.
Созданию хранимой процедуры предшествует этап предварительной работы, на котором необходимо решить следующее:
· Определить тип создаваемой хранимой процедуры. Следует отметить, что можно создать и собственную системную хранимую процедуру, назначив ей имя с префиксом sp_ и поместив ее в системную базу данных master. Такая процедура будет доступной в контексте любой БД.
· Планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что имеет создавший ее пользователь, а не пользователь, который вызывает эту процедуру на исполнение.
· Определение параметров хранимой процедуры. Максимальное число параметров – 1024.
· Разработка кода хранимой процедуры.
· Включение создаваемой процедуры в группу. Всем процедурам группы дают одинаковые имена и размещенный после символа «;» номер.
Структура команды создания хранимой процедуры:
CREATE PROC[EDURE] <имя процедуры> [;n]
[@parameter data_type [=default] [OUTPUT][,…n]
[WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION]
[FOR REPLICATION]
AS Выражение SQL […n]
Создадим хранимую процедуру, которая будет возвращать список авторов, проживающих в определенном штате (демонстрационная база MS SQL Server).
CREATE PROC MyProc @state char(2) = ‘CA’
AS
SELECT a.au_lname, a.au_fname, t.title
FROM authors a, titles t, titleauthors ta
WHERE ta.au_id = a.au_id AND t.title_id = ta.title_id AND
State = @state
Вызов процедуры осуществляется командой EXECUTE или указанием только имени. Второй вариант применим только в том случае, когда пакет включает только одну команду.
EXEC[UTE] <ИМЯ ПРОЦЕДУРЫ>
[[@parameter = ] {value | @variable [OUTPUT] | [DEFAULT]]
[,…n]