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

Во многих распространенных диалектах для создания хранимой процедуры применяется инструкция create procedure. Эта инструкция назначает новой процедуре имя, по которому в дальнейшем процедуру можно будет вызывать. Имя процедуры должно соответствовать общим правилам для идентификаторов SQL. Хранимая процедура может принимать ноль или более параметров. Обычно значения параметров указываются в виде разделенного запятыми списка, заключенного в скобки и следующего за именем вызываемой процедуры. Заголовок хранимой процедуры содержит имена параметров и типы их данных. Для параметров хранимых процедур могут использоваться те же типы данных, которые поддерживаются в СУБД для столбцов таблиц.

Параметры процедуры могут быть входными, при использовании ключевого слова IN, следующего за их именами в заголовке процедуры. Когда процедура вызывается, переданные ей аргументы присваиваются ее параметрам и начинается выполнение тела процедуры. Имена параметров могут использоваться в теле процедуры (и, в частности, в составляющих ее стандартных инструкциях SQL) везде, где допускается наличие констант. Встретив имя параметра, СУБД подставляет на его место текущее значение этого параметра.

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

Параметрам процедуры могут быть назначены значения по умолчанию в операторе CREATE PROCEDURE. Значения по умолчанию должны быть константами (константы могут принимать значения NULL). В качестве примера показана процедура использующая по умолчанию значение NULL для параметра IN, для того чтобы не выполнять запрос, не имеющий смысла (без указания параметра).

CREATE PROCEDURE

CustomerProducts(IN customer_id

INTEGER DEFAULT NULL)

RESULT (product_id INTEGER,

quantity_ordered INTEGER)

BEGIN

IF customer_id IS NULL THEN

RETURN;

ELSE

SELECT product.id,

sum(sales_order_items.quantity)

FROM product,

sales_order_items,

sales_order

WHERE sales_order.cust_id = customer_id

AND sales_order.id = sales_order_items.id

AND sales_order_items.prod_id=product.id

GROUP BY product.id;

END IF;

END

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

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

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

В диалекте Watcom-SQL процедуры вызываются с помощью оператора CALL и используют параметры для приема значений и передачи результатов. Процедуры могут вызывать другие процедуры и вызывать срабатывание триггеров. Для вызова процедуры необходимо быть владельцем процедуры или иметь право на ее выполнение (или обладать правами DBA).

Список аргументов может быть либо в позиционном формате или в формате с указанием имен параметров.

Контрольные вопросы

1. Какой вид контроля целостности представления задается с помощью предложения with CHECK OPTION:

2. Команда удаления представлений и ее особенности.

3. Определение снимка (Snapshot), его сравнение с представлением.

4. Дайте определение хранимой процедуры.