Создание хранимых процедур

Типы хранимых процедур

В 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]

Дата добавления: 2014-11-20