Получение списка созданных хранимых процедур и функций

Циклы

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

Замечание! Вне хранимых процедур описываемые в данном разделе операторы применять нельзя.

1.7.1. Оператор while

Оператор while выполняет цикл и имеет следующий синтаксис:

[ label:] WHILE search_condition DO

statement_list

END WHILE [ label ]

Цикл while выполняет операторы statement_list до тех пор, пока условие search_condition истинно. При каждой итерации условие search_condition проверяется, и если при очередной проверке оно будет ложным (0), цикл завершит сове выполнение. Это означает, что если условие search_condition ложно с самого начала, цикл не выполнит ни одной итерации.

Если в цикле требуется выполнить более одного оператора, не обязательно заключать их в блок begin…end, т. к. эту функцию выполняет сам оператор while.

Выведем 3 раза текущую дату при помощи цикла while.

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

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

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

1.7.2. Досрочный выход из цикла

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

LEAVE label

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

Хранимая процедура nown() из вышеприведенного примера обладает недостатком – если задать очень большое значение аргумента num, можно создать псевдобесконечный цикл, который позволит злоумышленнику загрузить сервер бесполезной работой. Для предотвращения такой ситуации можно воспользоваться оператором leave, который прекратит выполнение цикла по достижении критического числа итераций. Ниже приводится пример хранимой процедуры, где число итераций ограничено двумя.

Условие if > 2 then leave wet; проверяет, не превысило ли значение счетчика i числа 2, и если это так, происходит прекращение цикла while. Использование меток позволяет точно указать, какой цикл необходимо прервать. Если имеется вложенный цикл, можно явно указать, какой из двух циклов требуется прервать (см. пример ниже).

При достижении условия i > 2 && j > 2 оператор leave прервет не вложенный цикл second, а внешний цикл first, т. к. метка внешнего цикла явно указана после оператора.

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

Значение 1 в условии цикла всегда будет истинным, и цикл не прекратится до тех пор, пока его не прервет оператор leave.

Еще одним оператором, выполняющим досрочное прекращение цикла, является оператор iterate, который имеет следующий синтаксис:

ITERATE label

В отличие от оператора leave, оператор iterate не прекращает выполнение цикла, он лишь выполняет досрочное прекращение текущей итерации.

Рассмотрим программу, которая в цикле формирует бинарную последовательность, добавляя к строке две единицы на четных итерациях и две единицы и два нуля на нечетных (см. пример ниже).

Бинарная последовательность хранится во временной строке bin, которая обязательно должна быть инициирована пустой строкой:

declare bin tinytext default ‘’;

Если инициализация не проведена, переменная получит значение null, и все операции с этой переменной также будут приводить к null. На каждой итерации переменной bin при помощи функции concat() прибавляется последовательность ‘11’. Если индекс i является четным (0, 2, 4, 6, 8), текущая итерация прекращается при помощи ключевого слова iterate, если индекс является нечетным (1, 3, 5, 7, 9), то итерация выполняется до конца, т. е. к временной строке bin добавляется еще и последовательность ‘00’. На четность индекс i проверяется при помощи строки

i/2 – ceiling(i/2)

Если индекс i делится на 2 без остатка, это выражение вернет 0 (ложь), если число является нечетным, то выражение вернет 0,5 (истина).

1.7.3. Оператор repeat

Оператор repeat, так же как и оператор while, реализует цикл:

[ label:] REPEAT

statement_list

UNTIL search_condition

END REPEAT [ label ]

Отличительной особенностью данного цикла является тот факт, что условие цикла search_condition проверяется не в начале, как в цикле while, а в конце оператора (ключевое слово until). Таким образом, цикл выполняет, по крайней мере, одну операцию независимо от условия. Следует отметить, что цикл repeat выполняется, пока условие search_condition ложно.

Оператор repeat может быть снабжен необязательной меткой label, по которой легко осуществлять досрочный выход из цикла при помощи операторов leave и iterate, рассмотренных в предыдущем разделе.

В следующем примере представлена процедура binrand(), которая генерирует и выводит случайную бинарную последовательность из 20 символов. Для формирования бинарной последовательности применяется цикл repeat.

1.7.4. Оператор loop

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

[l abel:] LOOP

statement_list

END LOOP [ label ]

Цикл loop, в отличие от операторов while и repeat, не имеет условий выхода. Поэтому данный вид цикла должен обязательно иметь в своем составе оператор leave.

Хранимую процедуру binrand(), приведенную в примере выше, можно переписать с использованием цикла loop (см. пример ниже).

1.7.5. Отсутствующие идентификаторы

Таблица tbl имеет первичный ключ id, снабженный атрибутом auto_increment. По мере работы с таблицей некоторый записи удаляются таким образом, что образуются пропуски (см. пример ниже).

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

Просмотреть список уже созданных хранимых процедур можно при помощи оператора show procedure status, который имеет следующий синтаксис:

SHOW PROCEDURE STATUS [LIKE ‘pattern’ ];

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

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

• Db – имя базы данных, в которой сохранена процедура;
• Name – имя процедуры;
• Type – тип хранимой процедуры, принимает значение procedure для оператора хранимой процедуры и function для хранимой функции (список хранимых функций возвращается оператором show procedure status, который рассматривается далее);
• Definer – учетная запись, от имени которой была создана хранимая процедура;
• Modified – дата последней модификации хранимой процедуры;
• Created – дата создания хранимой процедуры;
• Security_type – режим выполнения хранимой процедуры. Если это поле принимает значение definer, то хранимая процедура выполняется с правами доступа пользователя, создавшего данную процедуру. Если поле Security_type принимает значение invoker, то хранимая процедура выполняется с правами доступа пользователя, вызывающего процедуру при помощи оператора call;
• Comment – комментарий к хранимой процедуре.

Для просмотра списка хранимых функций предназначен оператор show function status. Оператор имеет следующий синтаксис:

SHOW FUNCTION STATUS [LIKE ‘pattern’ ];

Оператор выводит список хранимых функций, который не включает в свой состав хранимые процедуры (см. пример ниже). При использовании ключевого слова like можно вывести информацию только о тех функциях, имена которых удовлетворяют шаблону pattern.

Формат вывода оператора show function status совпадает с форматом оператора show procedure status, рассмотренного ранее.

Помимо представленных выше операторов show, существует еще одни способ извлечь информацию о хранимых процедурах – извлечь строки таблицы proc системной базы данных mysql, куда помещаются все хранимые процедуры. В примере ниже приводится select -запрос, извлекающий запись, соответствующую хранимой функции FormatSecond().

Как видно из примера, результирующая таблица имеет 16 полей:

• db – имя базы данных, в которую сохранена процедура;
• name – имя процедуры;
• type – тип хранимой процедуры, может принимать два значения: procedure или function, для хранимой процедуры или функции соответственно;
• specific_name – имя процедуры;
• language – язык, на котором написана хранимая процедура, в настоящий момент принимает единственное значение – SQL;
• sql_data_access – поле показывает, насколько зависит хранимая процедура от данных, и может принимать следующие значения: contains_sql, no_sql, reads_sql_data, modifies_sql_data, если процедура, соответственно использует константы, не использует SQL, читает данные или модифицирует их;
• is_deterministic – данное поле определяет, является ли хранимая процедура детерминированной (YES) или нет (NO). На состояние данного поля оказывает влияние характеристика deterministic;
• security_type – режим выполнения хранимой процедуры. Если это поле принимает значение DEFINER, то хранимая процедура выполняется с правами доступа создавшего ее пользователя. Если поле security_type принимает значение INVOKER, то хранимая процедура выполняется с правами доступа пользователя, вызывающего процедуру при помощи оператора call;
• param_list – список параметров хранимой процедуры;
• returns – тип результата, возвращаемого хранимой функцией, для хранимых процедур поле принимает пустую строку;
• body – тело хранимой процедуры от оператора begin до оператора end;
• definer – учетная запись, из-под которой была создана хранимая процедура;
• created – дата и время создания хранимой процедуры;
• modified – дата и время последней модификации хранимой процедуры;
• sql_model – режимы выполнения хранимой процедуры;
• comment – комментарий к хранимой процедуре.

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

Дата добавления: 2015-05-09