MySQL хранимая процедура
Хранимая процедура
Процедура (часто называемая хранимой процедурой) - это подпрограмма, подобная подпрограмме на обычном вычислительном языке, которая хранится в базе данных. У процедуры есть имя, список параметров и оператор (ы) SQL. Все большинство всех реляционных систем баз данных поддерживают хранимые процедуры, в MySQL 5 введены хранимые процедуры. В следующих разделах мы подробно обсудили процедуру MySQL и использовали MySQL 5.6 под Windows 7. MySQL 5.6 поддерживает «подпрограммы» и существует два вида подпрограмм: хранимые процедуры, которые вы вызываете, или функции, возвращаемые значения которых вы используете в других операторах SQL так же, как вы используете предустановленные функции MySQL, такие как pi (). Основное отличие состоит в том, что пользовательские функции могут использоваться как любое другое выражение в операторах SQL, тогда как хранимые процедуры должны вызываться с помощью оператора CALL.
Содержание:
- Почему хранимые процедуры?
- Как создать процедуру MySQL?
- Выберите разделитель
- Пример процедуры MySQL
- Инструменты для создания MySQL
- Вызвать процедуру
- Процедура: характеристики
- MySQL: составное заявление
- НАЧАЛО ... END Синтаксис составного оператора
- Заявление на этикетке
- Объявить заявление
- Переменные в хранимых программах
- Параметры процедуры
- Процедура MySQL: пример параметра IN
- Процедура MySQL: пример параметра OUT
- Процедура MySQL: пример параметра INOUT
- MySQL: если заявление
- MySQL: описание ситуации
- MySQL: ITERATE, Оставьте заявление
- MySQL: оператор LOOP
- MySQL: оператор REPEAT
- MySQL: заявление RETURN
- MySQL: WHILE
- MySQL: ИЗМЕНЕНИЕ ПРОЦЕДУРЫ
- MySQL: ПРОЦЕДУРА КАПЛИ
- MySQL: курсор
- Контроль доступа к хранимым программам
Почему хранимые процедуры?
- Хранимые процедуры быстрые. Сервер MySQL использует некоторые преимущества кэширования, как и подготовленные операторы. Основной прирост скорости происходит за счет сокращения сетевого трафика. Если у вас есть повторяющаяся задача, требующая проверки, зацикливания, нескольких операторов и отсутствия взаимодействия с пользователем, выполните это с помощью одного вызова процедуры, которая хранится на сервере.
- Хранимые процедуры переносимы. Когда вы пишете свою хранимую процедуру в SQL, вы знаете, что она будет работать на каждой платформе, на которой работает MySQL, без необходимости устанавливать дополнительный пакет среды выполнения, или устанавливать разрешения для выполнения программы в операционной системе, или развертывать различные пакеты. если у вас разные типы компьютеров. В этом преимущество написания на SQL, а не на внешнем языке, таком как Java, C или PHP.
- Хранимые процедуры всегда доступны в виде «исходного кода» в самой базе данных. И имеет смысл связать данные с процессами, которые работают с данными.
- Хранимые процедуры являются миграционными! MySQL придерживается довольно близко к стандарту SQL: 2003. Другие (DB2, Mimer) также придерживаются.
Процедура создания
Следующие операторы создают хранимую процедуру. По умолчанию процедура связана с базой данных по умолчанию (используемой в настоящее время базой данных). Чтобы связать процедуру с заданной базой данных, укажите имя как имя_базы_данных . сохраненное_процедурное_имя при его создании. Вот полный синтаксис:
Синтаксис:
CREATE [DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }]
ПРОЦЕДУРА sp_name ([proc_parameter [, ...]])
[характеристика ...] рутинное тело
параметр_процесса: [IN | OUT | INOUT] тип param_name
тип:
Любой действительный тип данных MySQL
характеристика:
КОММЕНТАРИЙ 'Строка'
| ЯЗЫК SQL
| [НЕ] ДЕТЕРМИНИСТИЧЕСКИЙ
| {СОДЕРЖИТ SQL | НЕТ SQL | ЧИТАЕТ ДАННЫЕ SQL
| ИЗМЕНЕНИЯ ДАННЫХ SQL}
| SQL SECURITY {DEFINER | INVOKER}
routine_body:
Допустимый оператор SQL
Перед созданием процедуры нам понадобится некоторая информация, которая описана в этом разделе:
Проверьте версию MySQL:
Следующая команда отображает версию MySQL:
mysql> SELECT VERSION (); + ----------- + | ВЕРСИЯ () | + ----------- + | 5.6.12 | + ----------- + 1 ряд в наборе (0,00 сек)
Проверьте привилегии текущего пользователя:
CREATE PROCEDURE и CREATE FUNCTION требуют привилегии CREATE ROUTINE. Им также может потребоваться привилегия SUPER, в зависимости от значения DEFINER, как описано далее в этом разделе. Если двоичное ведение журнала включено, CREATE FUNCTION может потребовать привилегию SUPER. По умолчанию MySQL автоматически предоставляет привилегии ALTER ROUTINE и EXECUTE рутинному создателю. Это поведение можно изменить, отключив системную переменную automatic_sp_privileges.
mysql> ПОКАЗАТЬ ПРИВИЛЕГИИ; + ----------------- + ---------------------------- + - -------------------------------------------------- --- + | Привилегия | Контекст | Комментарий | + ----------------- + ---------------------------- + - -------------------------------------------------- --- + | Альтер | Столы | Чтобы изменить таблицу | | Изменить рутину | Функции, процедуры | Чтобы изменить или удалить сохраненные функции / процедуры | | Создать | Базы данных, таблицы, индексы | Для создания новых баз данных и таблиц | | Создать рутину | Базы данных | Использовать CREATE FUNCTION / PROCEDURE | | Создать временный | Базы данных | Использовать CREATE TEMPORARY TABLE | | столы | | | | Создать вид | Столы | Для создания новых взглядов | | Создать пользователя | Администратор сервера | Для создания новых пользователей | | Удалить | Столы | Чтобы удалить существующие строки | | Drop | Базы данных, таблицы | Чтобы удалить базы данных, таблицы и представления | | Событие | Администратор сервера | Создавать, изменять, удалять и выполнять события | | Выполнить | Функции, процедуры | Для выполнения хранимых процедур | | Файл | Доступ к файлам на сервере | Для чтения и записи файлов на сервере | | Грант вариант | Базы данных, таблицы, | Чтобы предоставить другим пользователям те привилегии, которыми вы обладаете | | | Функции, процедуры | | | Индекс | Столы | Чтобы создать или удалить индексы | | Вставить | Столы | Вставить данные в таблицы | | Блокировка столов | Базы данных | Чтобы использовать LOCK TABLES (вместе с привилегией SELECT) | | Процесс | Администратор сервера | Для просмотра простого текста выполняющихся в данный момент запросов | | Прокси | Администратор сервера | Сделать возможным использование прокси пользователя | | Отзывы | Базы данных, таблицы | Иметь ссылки на таблицы | | Перезагрузить | Администратор сервера | Чтобы перезагрузить или обновить таблицы, журналы и привилегии | | Репликация | Администратор сервера | Спросить, где находится подчиненный или главный сервер | | клиент | | | | Репликация | Администратор сервера | Чтобы прочитать двоичный журнал событий от мастера | | раб | | | | Выбрать | Столы | Извлечь строки из таблицы | | Показать базы данных | Администратор сервера | Чтобы просмотреть все базы данных с SHOW DATABASES | | Показать вид | Столы | Чтобы увидеть взгляды с SHOW CREATE VIEW | | Выключение | Администратор сервера | Чтобы выключить сервер | | Супер | Администратор сервера | Чтобы использовать KILL thread, SET GLOBAL, CHANGE MASTER и т. Д. | | Триггер | Столы | Использовать триггеры | | Создать | Администратор сервера | Для создания / изменения / удаления табличных пространств | | табличное пространство | | | | Обновление | Столы | Чтобы обновить существующие строки | | Использование | Администратор сервера | Нет привилегий - разрешить подключение только | + ------------------------- + -------------------- + - -------------------------------------------------- --- + 31 ряд в наборе (0,00 сек)
Выберите базу данных:
Прежде чем создать процедуру, мы должны выбрать базу данных. Давайте посмотрим на список баз и выберите одну из них.
mysql> ПОКАЗАТЬ БАЗЫ ДАННЫХ; + -------------------- + | База данных | + -------------------- + | информационная схема | | ч | | mysql | | performance_schema | | сакила | | тест | | мир | + -------------------- + 7 рядов в наборе (0,06 сек)
Теперь выберите базу данных «hr» и перечислите таблицы:
mysql> USE hr; База данных изменена
mysql> ПОКАЗАТЬ СТОЛЫ; + -------------- + | Tables_in_hr | + -------------- + | alluser | | отделы | | emp_details | | job_history | | рабочие места | | места | | регионы | | пользователь | | user_details | + -------------- + 9 рядов в наборе (0,00 сек)
Выберите разделитель
Разделитель - это символ или строка символов, которые используются для завершения оператора SQL. По умолчанию мы используем точку с запятой (;) в качестве разделителя. Но это вызывает проблему в хранимой процедуре, потому что процедура может иметь много операторов, и каждый должен заканчиваться точкой с запятой. Таким образом, в качестве разделителя выберите строку, которая редко встречается в выражении или в процедуре. Здесь мы использовали двойной знак доллара, т.е. $$. Вы можете использовать все, что хотите. Чтобы возобновить использование ";" позже в качестве разделителя произнесите «DELIMITER; $$». Смотрите здесь, как изменить разделитель:
mysql> DELIMITER $$;
Теперь по умолчанию РАЗДЕЛИТЕЛЬ - «$$». Давайте выполним простую команду SQL:
mysql> SELECT * FROM пользователя $$ + ---------- + ----------- + -------- + | идентификатор пользователя | пароль | имя | + ---------- + ----------- + -------- + | scott123 | [электронная почта защищена] | Скотт | | ferp6734 | [электронная почта защищена] & 3 | Палаш | | diana094 | [электронная почта защищена] | Диана | + ---------- + ----------- + -------- + 3 ряда в наборе (0,00 сек)
Теперь выполните следующую команду, чтобы возобновить « ; » в качестве разделителя:
mysql> DELIMITER; $$
Пример: процедура MySQL
Здесь мы создали простую процедуру под названием job_data, когда мы выполним процедуру, она отобразит все данные из таблиц "jobs".
mysql> DELIMITER $$; mysql> CREATE PROCEDURE job_data () > ВЫБРАТЬ * ИЗ РАБОТ; $$ Запрос в порядке, затронуто 0 строк (0,00 с)
Объяснение:
- Команда CREATE PROCEDURE создает хранимую процедуру.
- Следующая часть - это название процедуры. Здесь имя процедуры "job_data".
- Имена процедур не чувствительны к регистру, поэтому job_data и JOB_DATA одинаковы.
- Вы не можете использовать две процедуры с одинаковым именем в одной базе данных.
- Вы можете использовать квалифицированные имена вида, например , «hr.job_data» «имя-базы данных процедуры-имя.».
- Имена процедур могут быть разделены. Если имя разделено, оно может содержать пробелы.
- Максимальная длина имени составляет 64 символа.
- Избегайте использования имен встроенных функций MySQL.
- Последняя часть «CREATE PROCEDURE» представляет собой пару круглых скобок. «()» содержит список параметров, так как в этой процедуре нет параметров, список параметров пуст.
- Следующая часть SELECT * FROM JOBS; $$, который является последним утверждением тела процедуры. Здесь точка с запятой (;) необязательна, так как $$ - это реальное выражение-оператор.
Инструменты для создания MySQL
Вы можете написать процедуру в инструменте командной строки MySQL или использовать MySQL Workbench, который является отличным интерфейсным инструментом (здесь мы использовали версию 5.3 CE).
Инструмент командной строки MySQL: -
Выберите команду MySQL Client из меню «Пуск»:
При выборе командной строки MySQL появится следующий экран:
После успешного входа в систему вы можете получить доступ к командной строке MySQL:
Теперь вы пишете и запускаете свою собственную процедуру, смотрите следующий пример:
MySQL верстак (5.3 CE): -
Выберите MySQL верстак из меню Пуск:
После выбора MySQL верстак появится следующий экран входа в систему:
Теперь введите данные для входа:
После успешного входа появится новый экран и на панели браузера объектов выберите базу данных:
После выбора базы данных правой кнопкой мыши на подпрограммах появится новое всплывающее окно:
После выбора «Создать процедуру» появится следующий экран, где вы можете написать свою собственную процедуру.
После написания процедуры нажмите кнопку «Применить», и появится следующий экран:
Следующим экраном будет просмотр сценария и его применение в базе данных.
Теперь нажмите кнопку Готово и запустите процедуру:
Вызвать процедуру
Оператор CALL используется для вызова процедуры, которая хранится в базе данных. Вот синтаксис:
CALL sp_name ([параметр [, ...]]) CALL sp_name [()]
Хранимые процедуры, которые не принимают аргументы, могут быть вызваны без скобок. Поэтому CALL job_data () и CALL job_data эквивалентны. Пусть выполнят процедуру.
mysql> CALL job_data $$ + ------------ + --------------------------------- + - ---------- + ------------ + | JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY | + ------------ + --------------------------------- + - ---------- + ------------ + | AD_PRES | Президент | 20000 | 40000 | | AD_VP | Администрация Вице-президент | 15000 | 30000 | | AD_ASST | Помощник Администратора | 3000 | 6000 | | FI_MGR | Финансовый менеджер | 8200 | 16000 | | FI_ACCOUNT | Бухгалтер | 4200 | 9000 | | AC_MGR | Бухгалтер менеджер | 8200 | 16000 | | AC_ACCOUNT | Общественный бухгалтер | 4200 | 9000 | | SA_MAN | Менеджер по продажам | 10000 | 20000 | | SA_REP | Торговый представитель | 6000 | 12000 | | PU_MAN | Менеджер по закупкам | 8000 | 15000 | | PU_CLERK | Делопроизводитель | 2500 | 5500 | | ST_MAN | Фондовый менеджер | 5500 | 8500 | | ST_CLERK | Клерк акций | 2000 | 5000 | | SH_CLERK | Клерк доставки | 2500 | 5500 | | IT_PROG | Программист | 4000 | 10000 | | MK_MAN | Менеджер по маркетингу | 9000 | 15000 | | MK_REP | Представитель по маркетингу | 4000 | 9000 | | HR_REP | Представитель отдела кадров | 4000 | 9000 | | PR_REP | Представитель по связям с общественностью | 4500 | 10500 | + ------------ + --------------------------------- + - ---------- + ------------ + 19 строк в наборе (0,00 с) Запрос в порядке, затронуто 0 строк (0,15 с)
ПОКАЗАТЬ СОЗДАТЬ ПРОЦЕДУРУ
Этот оператор является расширением MySQL. Он возвращает точную строку, которая может быть использована для воссоздания именованной хранимой процедуры. Оба утверждения требуют, чтобы вы были владельцем рутины. Вот синтаксис:
ПОКАЗАТЬ ПРОЦЕДУРУ СОЗДАНИЯ proc_name
Давайте выполним вышеизложенное и увидим вывод
mysql> ПОКАЗАТЬ ПРОЦЕДУРУ СОЗДАНИЯ job_data $$
MySQL: характеристики
В синтаксисе CREATE PROCEDURE есть несколько разделов, которые описывают характеристики процедуры. Пункты идут после скобок, но перед телом. Все эти пункты являются необязательными. Вот пункты:
характеристика:
КОММЕНТАРИЙ 'Строка'
| ЯЗЫК SQL
| [НЕ] ДЕТЕРМИНИСТИЧЕСКИЙ
| {СОДЕРЖИТ SQL | НЕТ SQL | ПРОЧИТАЕТ ДАННЫЕ SQL | ИЗМЕНЕНИЯ ДАННЫХ SQL}
| SQL SECURITY {DEFINER | INVOKER}
КОММЕНТАРИЙ :
Характеристика COMMENT является расширением MySQL. Он используется для описания хранимой подпрограммы, а информация отображается в инструкциях SHOW CREATE PROCEDURE.
ЯЗЫК:
Характеристика LANGUAGE указывает на то, что тело процедуры написано на языке SQL.
НЕ ДЕТЕРМИНИСТИЧЕСКИЙ:
НЕ ДЕТЕРМИНИСТИЧЕСКИЙ, является информационным, подпрограмма считается «детерминированной», если она всегда дает один и тот же результат для одних и тех же входных параметров, и «недетерминированной» в противном случае.
СОДЕРЖИТ SQL | НЕТ SQL | ПРОЧИТАЕТ ДАННЫЕ SQL | ИЗМЕНИТ ДАННЫЕ SQL
СОДЕРЖИТ SQL:
СОДЕРЖИТ SQL означает, что в процедуре нет операторов, которые читают или записывают данные. Например, операторы SET @x = 1 или DO RELEASE_LOCK ('abc'), которые выполняются, но не читают и не записывают данные. Это значение по умолчанию, если ни одна из этих характеристик не указана явно.
НЕТ SQL:
NO SQL означает, что подпрограмма не содержит операторов SQL.
ЧИТАЕТ ДАННЫЕ SQL:
READS SQL DATA означает, что подпрограмма содержит операторы, которые читают данные (например, SELECT), но не операторы, которые записывают данные.
ИЗМЕНИТ ДАННЫЕ SQL:
MODIFIES SQL DATA означает, что подпрограмма содержит операторы, которые могут записывать данные (например, INSERT или DELETE).
SQL SECURITY {DEFINER | INVOKER}
SQL SECURITY, может быть определен как SQL SECURITY DEFINER или SQL SECURITY INVOKER для указания контекста безопасности; то есть, выполняется ли подпрограмма с использованием привилегий учетной записи, указанной в условии подпрограммы DEFINER, или пользователем, который ее вызывает. Эта учетная запись должна иметь разрешение на доступ к базе данных, с которой связана подпрограмма. Значением по умолчанию является DEFINER. Пользователь, который вызывает подпрограмму, должен иметь для нее привилегию EXECUTE, равно как и учетную запись DEFINER, если подпрограмма выполняется в определенном контексте безопасности.
Все приведенные выше характеристики имеют значения по умолчанию. Следующие два утверждения дают одинаковый результат:
mysql> CREATE PROCEDURE job_data () > ВЫБРАТЬ * ИЗ РАБОТ; $$ Запрос в порядке, затронуто 0 строк (0,00 с)
такой же как :
mysql> CREATE PROCEDURE new_job_data () -> КОММЕНТАРИЙ '' -> ЯЗЫК SQL -> НЕ ДЕТЕРМИНИСТИЧЕСКИЙ -> СОДЕРЖИТ SQL -> ОПРЕДЕЛИТЕЛЬ БЕЗОПАСНОСТИ SQL -> ВЫБРАТЬ * ИЗ РАБОТ; -> $$ Запрос в порядке, затронуто 0 строк (0,26 с)
В следующем разделе мы обсудим параметры
Прежде чем перейти к параметрам MySQL, давайте обсудим некоторые составные операторы MySQL:
MySQL: составное заявление
Составной оператор - это блок, который может содержать другие блоки; объявления для переменных, обработчиков условий и курсоров; и конструкции управления потоком, такие как циклы и условные тесты. Начиная с версии 5.6 MySQL имеет следующие составные операторы:
В этом разделе мы обсудим первые четыре оператора, которые охватывают часть параметров оператора CREATE PROCEDURE.
НАЧАЛО ... END Синтаксис составного оператора
Блок BEGIN ... END используется для написания составных операторов, т. Е. Когда вам требуется более одного оператора в хранимых программах (например, хранимые процедуры, функции, триггеры и события). Вот синтаксис:
[Метка_начала:] НАЧАТЬ [Statement_list] КОНЕЦ [Метка_конца])
Statement_list: представляет одно или несколько операторов, оканчивающихся точкой с запятой (;). Сам оператор Statement_list является необязательным, поэтому пустой составной оператор BEGIN END действителен.
begin_label, end_label : см. следующий раздел.
Заявление на этикетке
Метки разрешены для блоков BEGIN ... END и для операторов LOOP, REPEAT и WHILE. Вот синтаксис:
[ begin_label :] НАЧАТЬ [ Statement_list ] END [ end_label ] [ begin_label :] LOOP statement_list КОНЕЦ ЦИКЛА [ end_label ] [ begin_label :] ПОВТОРЕНИЕ statement_list UNTIL search_condition КОНЕЦ REPEAT [ end_label ] [ begin_label :] WHILE search_condition ДЕЛАТЬ statement_list END WHILE [ end_label ]
Используйте метку для тех утверждений, которые следуют следующим правилам:
- begin_label должен сопровождаться двоеточием
- begin_label можно указывать без end_label. Если end_label присутствует, он должен совпадать с begin_label
- end_label не может быть дано без begin_label.
- Метки на одном уровне вложенности должны быть разными
- Метки могут быть длиной до 16 символов.
Объявить заявление
Оператор DECLARE используется для определения различных элементов, локальных для программы, например, локальных переменных, условий и обработчиков, курсоров. DECLARE используется только внутри составного оператора BEGIN ... END и должен находиться в его начале перед любыми другими операторами. Объявления следуют в следующем порядке:
- Объявления курсора должны появляться перед объявлениями обработчика.
- Объявления переменных и условий должны появляться перед объявлениями курсора или обработчика.
Переменные в хранимых программах
Системные переменные и пользовательские переменные могут использоваться в хранимых программах так же, как они могут использоваться вне контекста хранимой программы. Хранимые программы используют DECLARE для определения локальных переменных, а хранимые подпрограммы (процедуры и функции) могут быть объявлены для получения параметров, которые передают значения между подпрограммой и ее вызывающей стороной.
Объявить переменную:
ОБЪЯВИТЬ имя_переменной [, имя_переменного] ... тип [значение ПО УМОЛЧАНИЮ]
Чтобы обеспечить значение по умолчанию для переменной, включите предложение DEFAULT. Значение может быть указано как выражение; это не должно быть постоянным. Если предложение DEFAULT отсутствует, начальное значение равно NULL.
Пример: локальные переменные
Локальные переменные объявляются в хранимых процедурах и действительны только в блоке BEGIN… END, где они объявлены. Локальные переменные могут иметь любой тип данных SQL. В следующем примере показано использование локальных переменных в хранимой процедуре.
РАЗДЕЛИТЕЛЬ $$ CREATE PROCEDURE my_procedure_Local_Variables () НАЧАТЬ / * объявить локальные переменные * / ОБЪЯВИТЬ INT DEFAULT 10; ОБЪЯВИТЬ b, c INT; / * используя локальные переменные * / SET a = a + 100; SET b = 2; SET c = a + b; BEGIN / * локальная переменная во вложенном блоке * / ОБЪЯВИТЬ c INT; SET c = 5; / * локальная переменная c имеет приоритет над одной из то же самое имя, объявленное в ограждающем блоке. * / ВЫБЕРИТЕ a, b, c; КОНЕЦ; ВЫБЕРИТЕ a, b, c; END $$
Теперь выполните процедуру:
mysql> CALL my_procedure_Local_Variables (); + ------ + ------ + ------ + | а | б | с | + ------ + ------ + ------ + | 110 | 2 | 5 | + ------ + ------ + ------ + 1 ряд в наборе (0,00 сек) + ------ + ------ + ------ + | а | б | с | + ------ + ------ + ------ + | 110 | 2 | 112 | + ------ + ------ + ------ + 1 ряд в наборе (0,01 с) Запрос в порядке, затронуто 0 строк (0,03 сек)
Пример: пользовательские переменные
В хранимых процедурах MySQL на пользовательские переменные ссылается амперсанд (@) с префиксом имени пользовательской переменной (например, @x и @y). В следующем примере показано использование пользовательских переменных в хранимой процедуре:
РАЗДЕЛИТЕЛЬ $$ CREATE PROCEDURE my_procedure_User_Variables () НАЧАТЬ SET @x = 15; SET @y = 10; ВЫБЕРИТЕ @x, @y, @ class = "__ cf_email__" data-cfemail = "aed683eed7"> [электронная почта защищена]; END $$
Теперь выполните процедуру:
mysql> CALL my_procedure_User_Variables (); + ------ + ------ + ------- + | @x | @y | @ [электронная почта защищена] | + ------ + ------ + ------- + | 15 | 10 | 5 | + ------ + ------ + ------- + 1 ряд в наборе (0,04 сек) Запрос в порядке, затронуто 0 строк (0,05 с)
MySQL: параметры процедуры
Вот часть параметров синтаксиса CREATE PROCEDURE:
СОЗДАЙТЕ
[DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }]
ПРОЦЕДУРА sp_name ([proc_parameter [, ...]])
[характеристика ...] рутинное тело
параметр_процесса: [IN | OUT | INOUT] тип param_name Мы можем разделить вышеприведенный оператор CREATE PROCEDURE следующим образом:
1. СОЗДАТЬ ПРОЦЕДУРУ sp_name () ...
2. СОЗДАЙТЕ ПРОЦЕДУРУ sp_name ([IN] тип param_name) ...
3. СОЗДАНИЕ ПРОЦЕДУРЫ sp_name ([OUT] тип param_name) ...
4. СОЗДАТЬ ПРОЦЕДУРУ sp_name (тип [INOUT] param_name) ...
В первом примере список параметров пуст.
Во втором примере, параметр IN передает значение в процедуру. Процедура может изменить значение, но изменение не будет видно вызывающей стороне, когда процедура вернется.
В третьем примере параметр OUT передает значение из процедуры обратно вызывающей стороне. Его начальное значение равно NULL в процедуре, а его значение видно вызывающей стороне, когда процедура возвращается.
В четвертом примере параметр INOUT инициализируется вызывающей стороной, может быть изменен процедурой, и любое изменение, внесенное процедурой, будет видно вызывающей стороне, когда процедура вернется.
В процедуре каждый параметр по умолчанию является параметром IN. Чтобы указать иное для параметра, используйте ключевое слово OUT или INOUT перед именем параметра.
Процедура MySQL: пример параметра IN
В следующей процедуре мы использовали параметр IN 'var1' (тип integer), который принимает число от пользователя. В теле процедуры есть оператор SELECT, который выбирает строки из таблицы 'jobs', и количество строк будет предоставлено пользователем. Вот процедура:
mysql> CREATE PROCEDURE my_proc_IN (IN var1 INT) -> НАЧАТЬ -> SELECT * FROM jobs LIMIT var1; -> КОНЕЦ $$
Запрос в порядке, затронуто 0 строк (0,00 с)
Чтобы выполнить первые 2 строки из таблицы 'jobs', выполните следующую команду:
mysql> CALL my_proc_in (2) $$ + --------- + ------------------------------- + ------- ----- + ------------ + | JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY | + --------- + ------------------------------- + ------- ----- + ------------ + | AD_PRES | Президент | 20000 | 40000 | | AD_VP | Администрация Вице-президент | 15000 | 30000 | + --------- + ------------------------------- + ------- ----- + ------------ + 2 строки в наборе (0,00 с) Запрос в порядке, затронуто 0 строк (0,03 с)
Теперь выполните первые 5 строк из таблицы «jobs»:
MySQL> ЗВОНИТЕ my_proc_in (5) $$ + ------------ + ------------------------------- + ---- -------- + ------------ + | JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY | + ------------ + ------------------------------- + ---- -------- + ------------ + | AD_PRES | Президент | 20000 | 40000 | | AD_VP | Администрация Вице-президент | 15000 | 30000 | | AD_ASST | Помощник Администратора | 3000 | 6000 | | FI_MGR | Финансовый менеджер | 8200 | 16000 | | FI_ACCOUNT | Бухгалтер | 4200 | 9000 | + ------------ + ------------------------------- + ---- -------- + ------------ + 5 строк в наборе (0,00 с) Запрос в порядке, затронуто 0 строк (0,05 с)
Процедура MySQL: пример параметра OUT
В следующем примере показана простая хранимая процедура, в которой используется параметр OUT. В рамках процедуры функция MySQL MAX () извлекает максимальную зарплату из MAX_SALARY таблицы заданий.
mysql> CREATE PROCEDURE my_proc_OUT (OUT Hig__Salary INT) -> НАЧАТЬ -> ВЫБЕРИТЕ МАКС. (MAX_SALARY) В САМОГО ВЫСОКОГО ЗАПИСИ ОТ РАБОТ; -> КОНЕЦ $$ Запрос в порядке, затронуто 0 строк (0,00 с)
В теле процедуры параметр получит самую высокую зарплату из столбца MAX_SALARY. После вызова процедуры слово OUT сообщает СУБД, что значение выходит из процедуры. Здесь самое высокое_салари - это имя выходного параметра, и мы передали его значение переменной сеанса с именем @M в операторе CALL.
mysql> CALL my_proc_OUT (@M) $$ Запрос в порядке, затрагивается 1 строка (0,03 сек) mysql <SELECT @M $$ + ------- + | @M | + ------- + | 40000 | + ------- + 1 ряд в наборе (0,00 сек)
Процедура MySQL: пример параметра INOUT
В следующем примере показана простая хранимая процедура, которая использует параметр INOUT и параметр IN. Пользователь предоставит «M» или «F» через параметр IN (emp_gender) для подсчета количества мужчин или женщин из таблицы user_details. Параметр INOUT (mfgender) вернет результат пользователю. Вот код и вывод процедуры:
mysql> CALL my_proc_OUT (@M) $$ Query OK, 1 строка затронута (0,03 сек) mysql> CREATE PROCEDURE my_proc_INOUT (INOUT mfgender INT, IN emp_gender CHAR (1)) -> НАЧАТЬ -> ВЫБЕРИТЕ СЧЕТ (пол) В mfgender ОТ пользователя_детали ГДЕ пол = emp_gender; -> КОНЕЦ $$ Запрос в порядке, затронуто 0 строк (0,00 с)
Теперь проверьте количество мужчин и женщин, использующих указанные таблицы:
mysql> CALL my_proc_INOUT (@ C, 'M') $$
Запрос в порядке, затрагивается 1 строка (0,02 сек) mysql> SELECT @ C $$ + ------ + | @C | + ------ + | 3 | + ------ + 1 ряд в наборе (0,00 сек) mysql> CALL my_proc_INOUT (@ C, 'F') $$ Запрос в порядке, затрагивается 1 строка (0,00 с) mysql> SELECT @ C $$ + ------ + | @C | + ------ + | 1 | + ------ + 1 ряд в наборе (0,00 сек)
MySQL: операторы управления потоком
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE, LOOP, WHILE и REPEAT для управления потоком в хранимых программах. Он также поддерживает возврат в хранимых функциях.
MySQL: если заявление
Оператор IF реализует базовую условную конструкцию в хранимых программах и должен заканчиваться точкой с запятой. Существует также функция IF () , которая отличается от оператора IF. Вот синтаксис оператора if:
IF условие THEN оператор (ы) [ELSEIF условие THEN оператор (ы) ] ... [ELSE заявление (я) ] КОНЕЦ ЕСЛИ
- Если условие истинно, соответствующий THEN или ELSEIF заявление (ы) пункта выполняет.
- Если ни одно из условий не соответствует, выполняется оператор (ы) предложения ELSE.
- Каждый оператор (ы) состоит из одного или нескольких операторов SQL; пустые операторы не допускаются.
Пример:
В следующем примере мы передаем user_id через параметр IN, чтобы получить имя пользователя. В рамках процедуры мы использовали IF ELSEIF и оператор ELSE, чтобы получить имя пользователя для нескольких идентификаторов пользователей. Имя пользователя будет сохранено в INOUT параметре user_name.
CREATE DEFINER = `root` @` 127.0.0.1` ПРОЦЕДУРА `GetUserName` (INOUT user_name varchar (16), IN user_id varchar (16)) НАЧАТЬ ОБЪЯВИТЬ uname varchar (16); ВЫБЕРИТЕ имя INTO uname ОТ пользователя ГДЕ userid = user_id; IF user_id = "scott123" ЗАТЕМ SET user_name = "Скотт"; ELSEIF user_id = "ferp6734" ЗАТЕМ SET user_name = "Palash"; ELSEIF user_id = "diana094" ЗАТЕМ SET user_name = "Диана"; END IF; КОНЕЦ
Выполните процедуру:
mysql> CALL GetUserName (@ A, 'scott123') $$
Запрос в порядке, затрагивается 1 строка (0,00 с)
mysql> SELECT @A;
-> $$
+ ------- +
| @A |
+ ------- +
| Скотт |
+ ------- +
1 ряд в наборе (0,00 сек) MySQL: описание ситуации
Оператор CASE используется для создания сложной условной конструкции в хранимых программах. Оператор CASE не может иметь предложение ELSE NULL, и он заканчивается END CASE вместо END. Вот синтаксис:
CASE case_value WHEN when_value THEN Statement_list [WHEN when_value THEN Statement_list] ... [ELSE Statement_list] END CASE
или же
ДЕЛО WHEN search_condition THEN Statement_list [КОГДА search_condition THEN Statement_list] ... [ELSE Statement_list] END CASE
Пояснение: Первый синтаксис
- case_value является выражением.
- Это значение сравнивается с выражением when_value в каждом предложении WHEN, пока одно из них не станет равным.
- Когда найдено равное когда_значение, выполняется соответствующий оператор THEN предложение Statement_list.
- Если значение not_value не равно, выполняется выражение Statement_list предложения ELSE, если оно есть.
Объяснение: Второй синтаксис
- Каждое выражение search_condition предложения WHEN оценивается до тех пор, пока оно не станет истинным, после чего выполняется соответствующий ему оператор Statement_list предложения THEN.
- Если никакое условие search_condition не равно, выполняется выражение Statement_list предложения ELSE, если оно есть.
- Каждый список операторов состоит из одного или нескольких операторов SQL; пустой список операторов не разрешен.
Пример:
У нас есть таблица под названием «jobs» со следующими записями:
+ ------------ + --------------------------------- + - ---------- + ------------ + | JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY | + ------------ + --------------------------------- + - ---------- + ------------ + | AD_PRES | Президент | 20000 | 40000 | | AD_VP | Администрация Вице-президент | 15000 | 30000 | | AD_ASST | Помощник Администратора | 3000 | 6000 | | FI_MGR | Финансовый менеджер | 8200 | 16000 | | FI_ACCOUNT | Бухгалтер | 4200 | 9000 | | AC_MGR | Бухгалтер менеджер | 8200 | 16000 | | AC_ACCOUNT | Общественный бухгалтер | 4200 | 9000 | | SA_MAN | Менеджер по продажам | 10000 | 20000 | | SA_REP | Торговый представитель | 6000 | 12000 | | PU_MAN | Менеджер по закупкам | 8000 | 15000 | | PU_CLERK | Делопроизводитель | 2500 | 5500 | | ST_MAN | Фондовый менеджер | 5500 | 8500 | | ST_CLERK | Клерк акций | 2000 | 5000 | | SH_CLERK | Клерк доставки | 2500 | 5500 | | IT_PROG | Программист | 4000 | 10000 | | MK_MAN | Менеджер по маркетингу | 9000 | 15000 | | MK_REP | Представитель по маркетингу | 4000 | 9000 | | HR_REP | Представитель отдела кадров | 4000 | 9000 | | PR_REP | Представитель по связям с общественностью | 4500 | 10500 | + ------------ + --------------------------------- + - ---------- + ------------ + 19 рядов в наборе (0,03 сек)
Теперь мы хотим посчитать количество сотрудников с соблюдением следующих условий:
- MIN_SALARY> 10000
- MIN_SALARY <10000
- MIN_SALARY = 10000
Вот процедура (процедура записана в MySQL Workbench 5.2 CE):
РАЗДЕЛИТЕЛЬ $$ СОЗДАНИЕ ПРОЦЕДУРЫ `hr``my_proc_CASE` (INOUT no_employees INT, IN зарплата INT) НАЧАТЬ ДЕЛО КОГДА (зарплата> 10000) THEN (ВЫБЕРИТЕ COUNT (job_id) INTO no_employees ОТ рабочих мест ГДЕ min_salary> 10000); КОГДА (зарплата <10000) THEN (ВЫБЕРИТЕ COUNT (job_id) INTO no_employees ОТ рабочих мест ГДЕ min_salary <10000); ELSE (ВЫБЕРИТЕ COUNT (job_id) INTO no_employees ОТ заданий, ГДЕ min_salary = 10000); КОНЕЦ ДЕЛА; END $$
В приведенной выше процедуре мы передаем переменную salary (сумму) через параметр IN. Внутри процедуры есть оператор CASE вместе с двумя WHEN и ELSE, который проверит условие и вернет значение счетчика в no_employees. Давайте выполним процедуру в командной строке MySQL:
Количество сотрудников, чья зарплата превышает 10000:
mysql> CALL my_proc_CASE (@ C, 10001); Запрос в порядке, затрагивается 1 строка (0,00 с) mysql> SELECT @C; + ------ + | @C | + ------ + | 2 | + ------ + 1 ряд в наборе (0,00 сек)
Количество работников, чья зарплата менее 10000:
mysql> CALL my_proc_CASE (@ C, 9999); Запрос в порядке, затрагивается 1 строка (0,00 с) mysql> SELECT @C; + ------ + | @C | + ------ + | 16 | + ------ + 1 ряд в наборе (0,00 сек)
Количество работников, чья зарплата равна 10000:
mysql> CALL my_proc_CASE (@ C, 10000); Запрос в порядке, затрагивается 1 строка (0,00 с) mysql> SELECT @C; + ------ + | @C | + ------ + | 1 | + ------ + 1 ряд в наборе (0,00 сек)
MySQL: заявление ITERATE
ITERATE означает «начать цикл заново». ITERATE может появляться только внутри операторов LOOP, REPEAT и WHILE. Вот синтаксис:
ITERATE label
MySQL: Оставьте заявление
Инструкция LEAVE используется для выхода из конструкции управления потоком, имеющей заданную метку. Если метка предназначена для самого внешнего сохраненного программного блока, LEAVE выходит из программы. LEAVE может использоваться внутри BEGIN ... END или в конструкциях цикла (LOOP, REPEAT, WHILE). Вот синтаксис:
ОСТАВИТЬ этикетку
MySQL: оператор LOOP
LOOP используется для создания повторного выполнения списка операторов. Вот синтаксис:
[Метка_начала:] LOOP statement_list КОНЕЦ ЦИКЛА [Метка_конца]
список_инстанций состоит из одного или нескольких операторов, каждый оператор заканчивается точкой с запятой (;). операторы внутри цикла повторяются до тех пор, пока цикл не завершится. Обычно инструкция LEAVE используется для выхода из конструкции цикла. Внутри сохраненной функции также можно использовать RETURN, которая полностью выходит из функции. Оператор LOOP может быть помечен.
Пример:
В следующей процедуре строки будут вставлены в таблицу 'number' до тех пор, пока x не станет меньше num (число, предоставленное пользователем через параметр IN). Случайное число будет храниться каждый раз.
РАЗДЕЛИТЕЛЬ $$ CREATE PROCEDURE `my_proc_LOOP` (IN num INT) НАЧАТЬ ОБЪЯВИТЬ х INT; SET x = 0; loop_label: LOOP Вставить в числовые значения (rand ()); SET х = х + 1; IF x> = num ЗАТЕМ ОСТАВИТЬ loop_label; END IF; КОНЕЦ ЦИКЛА; END $$
Теперь выполните процедуру:
mysql> CALL my_proc_LOOP (3); Запрос в порядке, 1 строка затронута, 1 предупреждение (0,19 с) mysql> select * from number; + -------------- + | номер | + -------------- + | 0,1228974146 | | 0.2705919913 | | 0,9842677433 | + -------------- + 3 ряда в наборе (0,00 сек)
MySQL: оператор REPEAT
Оператор REPEAT выполняет оператор (ы) несколько раз, пока условие выполняется. Условие проверяется каждый раз в конце операторов.
[Метка_начала:] ПОВТОРЕНИЕ statement_list UNTIL search_condition КОНЕЦ ПОВТОРЕНИЕ [Метка_конца]
Statement_list: Список из одного или нескольких операторов, каждое из которых заканчивается точкой с запятой (;).
search_condition: выражение.
Оператор REPEAT может быть помечен.
Пример:
Четные числа - это числа, которые можно равномерно разделить на 2. В следующей процедуре пользователь пропускает число через параметр IN и составляет сумму четных чисел от 1 до этого конкретного числа.
РАЗДЕЛИТЕЛЬ $$ CREATE PROCEDURE my_proc_REPEAT (IN n INT) BEGI NSET @sum = 0; SET @x = 1; ПОВТОРЕНИЕ IF mod (@x, 2) = 0 ЗАТЕМ SET @sum = @sum + @x; END IF; SET @x = @x + 1; ДО @x> n КОНЕЦ ПОВТОРЕНИЯ; КОНЕЦ $$
Теперь выполните процедуру:
mysql> call my_proc_REPEAT (5); Запрос в порядке, затронуто 0 строк (0,00 с) mysql> SELECT @sum; + ------ + | @sum | + ------ + | 6 | + ------ + 1 ряд в наборе (0,00 сек) mysql> call my_proc_REPEAT (10); Запрос в порядке, затронуто 0 строк (0,00 с) mysql> SELECT @sum; + ------ + | @sum | + ------ + | 30 | + ------ + 1 ряд в наборе (0,00 сек)
MySQL: заявление RETURN
Оператор RETURN завершает выполнение сохраненной функции и возвращает значение expr вызывающей функции. В хранимой функции должен быть хотя бы один оператор RETURN. Может быть более одного, если функция имеет несколько точек выхода. Вот синтаксис:
Возврат expr
Этот оператор не используется в хранимых процедурах или триггерах. Инструкция LEAVE может использоваться для выхода из хранимой программы этих типов.
MySQL: WHILE
Оператор WHILE выполняет оператор (ы), пока условие выполняется. Условие проверяется каждый раз в начале цикла. Каждое утверждение заканчивается точкой с запятой (;). Вот синтаксис:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Оператор WHILE может быть помечен.
Пример:
Нечетные числа - это числа, которые нельзя разделить точно на 2. В следующей процедуре пользователь пропускает число через параметр IN и составляет сумму нечетных чисел между 1 и этим конкретным числом.
РАЗДЕЛИТЕЛЬ $$ CREATE PROCEDURE my_proc_WHILE (IN n INT) НАЧАТЬ SET @sum = 0; SET @x = 1; WHILE @x <n ДЕЛАТЬ
ЕСЛИ мод (@x, 2) <> 0 ТО SET @sum = @sum + @x; END IF; SET @x = @x + 1; END WHILE; END $$
Теперь выполните процедуру:
mysql> CALL my_proc_WHILE (5); Запрос в порядке, затронуто 0 строк (0,00 с) mysql> SELECT @sum; + ------ + | @sum | + ------ + | 3 | + ------ + 1 ряд в наборе (0,00 сек) mysql> CALL my_proc_WHILE (10); Запрос в порядке, затронуто 0 строк (0,00 с) mysql> SELECT @sum; + ------ + | @sum | + ------ + | 25 | + ------ + 1 ряд в наборе (0,00 сек)
mysql> CALL my_proc_WHILE (3); Запрос в порядке, затронуто 0 строк (0,00 с) mysql> SELECT @sum; + ------ + | @sum | + ------ + | 4 | + ------ + 1 ряд в наборе (0,00 сек)
MySQL: ИЗМЕНЕНИЕ ПРОЦЕДУРЫ
Этот оператор может использоваться для изменения характеристик хранимой процедуры. В инструкции ALTER PROCEDURE может быть указано более одного изменения. Однако вы не можете изменить параметры или тело хранимой процедуры, используя этот оператор; чтобы внести такие изменения, вы должны удалить и заново создать процедуру, используя DROP PROCEDURE и CREATE PROCEDURE. Вот синтаксис:
ALTER PROCEDURE proc_name [характеристика ...] характеристика:
КОММЕНТАРИЙ 'Строка'
| ЯЗЫК SQL
| {СОДЕРЖИТ SQL
| НЕТ SQL | ЧИТАЕТ ДАННЫЕ SQL
| ИЗМЕНЕНИЯ ДАННЫХ SQL}
| SQL SECURITY {DEFINER
| INVOKER} У вас должна быть привилегия ALTER ROUTINE для этой процедуры. По умолчанию эта привилегия предоставляется автоматически создателю процедуры. В нашей предыдущей процедуре "my_proc_WHILE" раздел комментариев был пустым. Чтобы ввести новый комментарий или изменить предыдущий комментарий, используйте следующую команду:
mysql> ALTER PROCEDURE my_proc_WHILE КОММЕНТАРИЙ «Изменить комментарий»; > Запрос в порядке, затронуто 0 строк (0,20 с)
Вы можете проверить результат с помощью команды SHOW CREATE PROCEDURE, которую мы обсуждали ранее.
MySQL: ПРОЦЕДУРА КАПЛИ
Этот оператор используется для удаления хранимой процедуры или функции. То есть указанная подпрограмма удаляется с сервера. У вас должна быть привилегия ALTER ROUTINE для этой процедуры. (Если системная переменная automatic_sp_privileges включена, эта привилегия и EXECUTE автоматически предоставляются создателю подпрограммы, когда подпрограмма создается и удаляется из создателя, когда подпрограмма отбрасывается
DROP {ПРОЦЕДУРА | ФУНКЦИЯ} [ЕСЛИ СУЩЕСТВУЕТ] sp_name Предложение IF EXISTS является расширением MySQL. Это предотвращает возникновение ошибки, если процедура или функция не существует. Выдается предупреждение, которое можно просмотреть с помощью ПОКАЗАТЬ ПРЕДУПРЕЖДЕНИЯ. Вот пример:
mysql> DROP PROCEDURE new_procedure; Запрос в порядке, затронуто 0 строк (0,05 с)
Вы можете проверить результат с помощью команды SHOW CREATE PROCEDURE, которую мы обсуждали ранее.
MySQL: курсоры
Курсор базы данных - это управляющая структура, которая обеспечивает возможность обхода записей в базе данных. Курсоры используются программистами базы данных для обработки отдельных строк, возвращаемых системными запросами базы данных. Курсоры позволяют манипулировать целыми наборами результатов одновременно. В этом случае курсор позволяет последовательно обрабатывать строки в наборе результатов. В процедурах SQL курсор позволяет определить результирующий набор (набор строк данных) и выполнять сложную логику построчно. Используя ту же механику, процедура SQL также может определять набор результатов и возвращать его непосредственно вызывающей стороне процедуры SQL или клиентскому приложению.
MySQL поддерживает курсоры внутри хранимых программ. Синтаксис такой же, как во встроенном SQL. Курсоры имеют следующие свойства:
- Asensitive: сервер может или не может сделать копию своей таблицы результатов
- Только чтение: не обновляется
- Неконтролируемый: может проходить только в одном направлении и не может пропускать строки
Чтобы использовать курсоры в процедурах MySQL, вам нужно сделать следующее:
- Объявить курсор.
- Откройте курсор.
- Получить данные в переменные.
- Закройте курсор, когда закончите.
Объявить курсор:
Следующий оператор объявляет курсор и связывает его с оператором SELECT, который извлекает строки, которые должен пройти курсор.
ОБЪЯВИТЬ имя курсора CURSOR FOR select_statement
Откройте курсор:
Следующий оператор открывает ранее объявленный курсор.
ОТКРЫТЬ имя курсора
Получить данные в переменные:
Этот оператор выбирает следующую строку для оператора SELECT, связанного с указанным курсором (который должен быть открыт), и перемещает указатель курсора. Если строка существует, извлеченные столбцы сохраняются в именованных переменных. Количество столбцов, извлекаемых оператором SELECT, должно соответствовать количеству выходных переменных, указанных в операторе FETCH.
FETCH [[NEXT] FROM] имя_курсора INTO var_name [, var_name] ...
Закройте курсор, когда закончите:
Этот оператор закрывает ранее открытый курсор. Ошибка возникает, если курсор не открыт.
ЗАКРЫТЬ
Пример:
Процедура начинается с трех объявлений переменных. Кстати, порядок важен. Сначала объявите переменные. Тогда объявите условия. Затем объявите курсоры. Затем объявите обработчики. Если вы разместите их в неправильном порядке, вы получите сообщение об ошибке.
РАЗДЕЛИТЕЛЬ $$ CREATE PROCEDURE my_procedure_cursors (INOUT return_val INT) НАЧАТЬ ОБЪЯВИТЬ a, b INT; ОБЪЯВИТЬ cur_1 CURSOR FOR ВЫБЕРИТЕ max_salary ОТ заданий; ОБЪЯВЛЯЙТЕ ПРОДОЛЖИТЕЛЬНОЕ ОБРАЩЕНИЕ ДЛЯ НЕ НАЙДЕНО b = 1; OPEN cur_1; REPEATFETCH cur_1 INTO a; ДО b = 1END REPEAT; ЗАКРЫТЬ cur_1; SET return_val = a; КОНЕЦ; $$
Теперь выполните процедуру:
MySQL> CALL my_procedure_cursors (@R); Запрос в порядке, затронуто 0 строк (0,00 с) mysql> SELECT @R; + ------- + | @R | + ------- + | 10500 | + ------- + 1 ряд в наборе (0,00 сек)
Мы приведем больше примеров на курсоры в ближайшее время.
Контроль доступа к хранимым программам
Хранимые программы и представления определяются перед использованием и при обращении к ним выполняются в контексте безопасности, который определяет их привилегии. Этими привилегиями управляет их атрибут DEFINER и, если он есть, их характеристика SQL SECURITY.
Все хранимые программы (процедуры, функции и триггеры) и представления могут иметь атрибут DEFINER, который называет учетную запись MySQL. Если атрибут DEFINER отсутствует в сохраненной программе или определении представления, учетной записью по умолчанию является пользователь, который создает объект.
MySQL использует следующие правила для управления тем, какие учетные записи пользователь может указать в атрибуте объекта DEFINER:
- Вы можете указать значение DEFINER, отличное от вашей учетной записи, только если у вас есть привилегия SUPER.
- Если у вас нет привилегии SUPER, единственным допустимым значением пользователя является ваша собственная учетная запись, указанная буквально или с помощью CURRENT_USER. Вы не можете установить определитель для какой-либо другой учетной записи.
- Для хранимой подпрограммы или представления используйте SQL SECURITY INVOKER в определении объекта, когда это возможно, чтобы его могли использовать только пользователи с разрешениями, соответствующими операциям, выполняемым объектом.
- Если вы создаете хранимые программы или представления определенного контекста при использовании учетной записи с привилегией SUPER, укажите явный атрибут DEFINER, который присваивает имя учетной записи, обладающей только привилегиями, необходимыми для операций, выполняемых объектом. Укажите учетную запись DEFINER с высоким уровнем привилегий только в случае крайней необходимости.
- Администраторы могут запретить пользователям указывать учетные записи DEFINER с высокими привилегиями, не предоставляя им привилегию SUPER.
- Объекты определителя контекста должны быть написаны с учетом того, что они могут иметь доступ к данным, для которых вызывающий пользователь не имеет привилегий. В некоторых случаях вы можете предотвратить ссылки на эти объекты, не предоставляя несанкционированным пользователям определенные привилегии:
- На хранимую процедуру или функцию не может ссылаться пользователь, не имеющий для нее привилегии EXECUTE.
- На представление не может ссылаться пользователь, у которого нет соответствующих привилегий для него (SELECT для выбора из него, INSERT для вставки в него и т. Д.).
Ссылка: руководство по MySQL 5.6
Предыдущая: Подзапросы MySQL
Далее: Триггеры MySQL
Новый контент: Composer: менеджер зависимостей для PHP , R программирования