Метаданные

Существуют четыре способа просмотреть данные, относящиеся к хранимым процедурам или функциям:

1. Операторы SHOW PROCEDURE STATUS и SHOW FUNCTION STATUS;

2. Операторы SHOW CREATE PROCEDURE и SHOW CREATE FUNCTION;

3. Запрос SELECT FROM mysq.proc;

4. Запрос SELECT FROM information_schema.

Оператор SHOW PROCEDURE STATUS

Этот оператор возвращает список хранимых процедур, исключая функции, которые удовлетворяют указанному шаблону. Синтаксис:

SHOW PROCEDURE STATUS [шаблон]

Например,

mysql> SHOW PROCEDURE STATUS LIKE ‘bin%’;

Аналогично для функций.

Оператор SHOW CREATE

Выводит оператор CREATE PROCEDURE, при помощи которого была создана хранимая процедура. Аналогично выполняется оператор SHOW CREATE FUNCTION для функций.

Оператор SELECT FROM mysq.proc

Этот оператор позволяет извлечь строки таблицы proc системной базы данных mysql, куда сохраняются все хранимые процедуры.

Оператор SELECT FROM information_schema

Каждая база данных имеет системную базу данных, в которой хранится самая разнообразная информация, начиная с привилегий и заканчивая хранимыми процедурами. Недостатком системной базы данных является ее непереносимость – каждая СУБД организует системную базу данных по-своему. Устранить недостаток несовместимости призван стандартный набор представлений системной таблицы, который называют информационной схемой.

Удаление хранимых процедур осуществляется оператором DROP PROCEDURE, который имеет синтаксис:

DROP PROCEDURE, который имеет следующий синтаксис:

DROP PROCEDURE [IF EXISTS] имя_процедуры

Удаление хранимой функции осуществляется оператором

DROP FUNCTION

Редактирование хранимой процедуры осуществляется оператором

ALTER PROCEDURE имя_процедуры [характеристика], а хранимой функции – оператором ALTER FUNCTION имя_функции [характеристика].

Характеристика может принимать следующие значения:

· SQL SECURITY {DEFINER ç INVOKER} – данное предложение определяет режим выполнения: хранимая процедура выполняется либо с правами создавшего ее пользователя (DEFINER), либо с правами вызвавшего ее пользователя (INVOKER);

· COMMENT ‘строка’ – данное предложение позволяет назначить комментарий для хранимой процедуры.

Пример использования оператора ALTER PROCEDURE:

mysql> CREATE PROCEDURE test ()

− > SELECT VERSION ();

mysql> SELECT name, type, security_type, comment

− > FROM mysql.proc

− > WHERE db = ‘test’;

При создании, модификации, удалении хранимых подпрограмм сервер манипулирует с таблицейmysql.proc

Начиная с MySQL 5.0.3 требуются следующие привилегии:

CREATE ROUTINE для создания хранимых процедур

ALTER ROUTINE необходимы для изменения или удаления процедур. Эта привилегия автоматически назначается создателю процедуры (функции)

EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER, который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД.

23. Назначение и виды хранимых функций. Команды по созданию хранимых функций. Оператор Declare. Операторы управления потоком данных: IF…THEN…ELSE, CASE, WHILE, REPEAT, LOOP, GOTO. Отличие функций от процедур. Удаление (модификация) хранимых функций.

Хранимые процедуры в среде MS SQL Server

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

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

В SQL Server имеется несколько типов хранимых процедур.

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

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

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

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

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

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

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

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

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

Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:

< определение_процедуры>:: =

{CREATE | ALTER } [PROCEDURE] имя_процедуры

[; номер]

[{@имя_параметра тип_данных } [VARYING ]

[=default][OUTPUT] ][,...n]

[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,

ENCRYPTION }]

[FOR REPLICATION]

AS

sql_оператор [...n]

Для использования переменных в процедурах используется оператор DECLARE, который имеет следующий синтаксис:

DECLARE имя_переменной тип DEFAULT значение_по_умолчанию_если_есть

Итак, давайте в нашей процедуре объявим переменную s, в которую будем сохранять значение суммы покупки с помощью ключевого слова INTO:

CREATE PROCEDURE sum_sale(IN i INT, OUT ss DOUBLE)

COMMENT 'Возвращает сумму покупки по ее идентификатору.'

begin

DECLARE s INT;

DROP VIEW IF EXISTS sum_sale;

CREATE VIEW sum_sale AS SELECT magazine_sales.id_sale,

magazine_sales.id_product, magazine_sales.quantity,

prices.price, magazine_sales.quantity*prices.price AS summa

FROM magazine_sales, prices

WHERE magazine_sales.id_product=prices.id_product;

SELECT SUM(summa) INTO s FROM sum_sale WHERE id_sale=i;

CALL sum_discount(s, i, ss);

end

//

Операторы управления потоком данных

Хранимые процедуры – это не просто удобные контейнеры для группы запросов, они позволяют реализовать достаточно сложную логику, используя операторы ветвления и циклы.

Оператор IF … THEN … ELSE

Этот оператор имеет следующий синтаксис:

IF условие_1 THEN операторы_1

[ELSEIF условие_2 THEN операторы_2] …

[ELSE операторы_3]

END IF

Отметим, что кроме оператора IF существует в MySQL функция IF ().

Оператор CASE

Этот оператор позволяет осуществить множественный выбор и имеет две формы. Первая форма имеет следующий синтаксис:

CASE значение_case

WHEN значение_1 THEN операторы_1

[WHEN значение_2 THEN операторы_2] …

[ELSE операторы_3]

END CASE

Оператор CASE сравнивает значение выражения значение_case со значением значение_1, значение_2 и так далее. Как только соответствие будет найдено, то выполнится соответствующий блок операторов операторы_1, операторы_2 и так далее. Если ни одного соответствия не найдено, выполняются операторы_3.

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

..........

CASE

WHEN cc = 0 THEN

SESECT....

WHEN cc = 1 THEN

SESECT....

WHEN cc = 3 THEN

SESECT....

.......

ELSE

SESECT....

END CASE;

.......

Оператор WHILE

Это – оператор цикла с предусловием. Синтаксис:

[метка: ] WHILE условие DO

блок операторов

END WHILE [метка]

Блок операторов выполняется, пока условие истинно.

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

mysql> CREATE PROCEDURE nown (IN num INT)

− > BEGIN

− > DECLARE i DEFAULT 0;

− > IF (num > 0) THEN

− > wet: WHILE i < num DO

− > SELECT NOW ();

− > SET i = i + 1;

− > END WHILE wet;

− > ELSE

− > SELECT ‘Ошибочное значение параметра’;

− > END IF;

− > END

− > / /

Вызов процедуры:

mysql> CALL NOWN (2) / /

Метка в цикле предназначена не только для того, чтобы облегчить чтение кода при очень длинных циклах, она позволяет осуществлять досрочный выход из цикла с помощью оператора LEAVE, который имеет следующий синтаксис:

LEAVE метка

Оператор LEAVE прекращает выполнение блока, помеченного меткой, например:

........

− > IF (num > 0) THEN

− > wet: WHILE i < num DO

− > IF i > 5 THEN LEAVE wet;

− > END IF;

........

Еще одним оператором, выполняющим досрочное прекращение цикла, является оператор ITERATE. В отличие от оператора LEAVE оператор ITERATE не прекращает выполнение цикла, он лишь выполняет досрочное прекращение текущей итерации.

Оператор REPEAT

Этот оператор также реализует цикл, но с постусловием. Синтаксис:

[метка: ] REPEAT

блок операторов

UNTIL условие

END REPEAT [метка]

Блок операторов выполняется, пока условие ложно. Проверка условия осуществляется после выполнения блока операторов.

Оператор LOOP

Этот оператор предназначен для реализации циклов и имеет следующий синтаксис:

[Метка: ] LOOP

блок операторов

END LOOP [метка]

Этот цикл, в отличие от операторов WHILE и REPEAT, не имеет условий выхода. Поэтому выход из цикла нужно предусмотреть, например, с помощью оператора LEAVE.

Оператор GOTO

Этот оператор осуществляет безусловный переход по метке.