Вопрос 24: Хранимые процедуры.

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

• обрабатывают входные параметры и возвращают вызывающей программе значения в виде выходных параметров;
• содержат программные инструкции, которые выполняют операции в базе данных, включая вызов других процедур;
• возвращают значение состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).

Типы:

• Системные хранимые процедуры предназначены для выполнения различных административных действий. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных;
• Пользовательские хранимые процедуры реализуют те или иные действия. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется;
• Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание хранимой процедуры предполагает решение следующих задач:

• определение типа создаваемой хранимой процедуры: временная или пользовательская. Кроме этого, можно создать свою собственную системную хранимую процедуру, назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет доступна в контексте любой базы данных локального сервера;
• планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;
• определение параметров хранимой процедуры. Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами ;
• разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур.

Хранимые процедуры создаются посредством инструкции CREATE PROCEDURE.

CREATE PROCEDURE имя_процедуры (параметры)
     begin
        операторы
     end

Пример создания процедуры:

USE productsdb;
GO
CREATE PROCEDURE ProductSummary AS
BEGIN
    SELECT ProductName AS Product, Manufacturer, Price
    FROM Products
END;

Внести изменения в алгоритм работы процедуры можно с помощью инструкции ALTER PROCEDURE:

ALTER PROCEDURE ProductSummary AS
BEGIN
    SELECT ProductName AS Product, Manufacturer, Price
    FROM Products
END;

Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE:

EXEC ProductSummary

Для удаления процедуры применяется команда DROP PROCEDURE:

DROP PROCEDURE ProductSummary