Удалить DEFINER = root @ localhost
в локальном, в то время как импорт,
он будет выполнен.
Можете ли вы, ребята, помочь мне в отношении хранимых процедур. Когда я экспортирую хранимую процедуру из phpmyadmin, она указывается как
CREATE DEFINER = `root` @ `localhost` PROCEDURE `c4mo_get_cities_prc` (IN `p_state_code` VARCHAR (3 ), IN `p_country_code` VARCHAR (3 ), IN `p_language_code` VARCHAR (3 )) NO SQL BEGIN SELECT city_name , city_code FROM `c4mo_cities` WHERE enabled = "Y" AND language_code = p_language_code AND state_code = p_state_code AND country_code = p_country_code ; ENDИ когда я импортирую его из phpmyadmin, он дает ошибку как
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "" at line 13Это довольно просто, если вы используете интерфейс phpmyadmin.
Для экспорта :
Вы увидите обычную вкладку, так как эта вкладка будет отображаться только в том случае, если у вас уже есть хотя бы одна хранимая процедура.
Просто перейдите на вкладку подпрограмм, и вы увидите свою сохраненную процедуру (для db, которую вы сделали).
Внизу отметьте галочкой флажок и затем экспортируйте. Вам просто нужно скопировать весь код и сохранить его в любом месте на локальной машине с your_stored_procedure.sql файла your_stored_procedure.sql .
Для импорта:
Просто выберите базу данных и импортируйте хранимую процедуру your_stored_procedure.sql файл, как указано выше, поскольку вы обычно импортируете файл.sql (таблицы) для своего db.
7.8KЕсли по простому, то хранимые процедуры («ХП ») представляют собой хранимые в базе данных процедуры (написанные с помощью SQL и других операторов), которые можно назвать движком базы данных или связанным языком программирования.
В этой статье я расскажу, как с помощью MySQL создать ХП и выполнить ее на сервере MySQL и через PHP .
Примечание : Мы не собираемся осветить здесь все аспекты, касающиеся ХП. Для получения справки по незатронутым в статье вопросам вы всегда можете воспользоваться официальной документацией MySQL .
ХП также поддерживаются на других распространенных серверах баз данных (Postgre , например), так что то, что мы сегодня будем обсуждать, также применимо и к ним.
Большинство из нас хорошо знакомы с обычными настройками, которые позволяют создать приложение базы данных: создание базы данных, создание таблиц, создание индексов, CRUD-данные, формирование запросов со стороны клиента и дальнейшая обработка, если это необходимо .
Этот процесс отлично работает в большинстве случаев, но есть один важный аспект, который программирование баз данных не охватывает: хранимая процедура.
Я могу назвать, по крайней мере, четыре веских причины использовать ХП в приложениях баз данных. Во-первых , она уменьшает сетевой трафик и нагрузку на сервер.
В стандартном PHP веб-приложении базы данных существует четыре составляющих:
- Клиентский уровень , который обычно представлен веб-браузером. Он предоставляет возможность взаимодействия с пользователем и позволяет вводить данные через интерфейс пользователя;
- Уровень веб-сервера , на котором обрабатываются запросы пользователей и отправляются ответы назад на клиентский уровень;
- PHP-уровень , который обрабатывает все PHP-составляющие, создает логику приложений и генерирует PHP часть ответа;
- Уровень базы данных , на котором обрабатываются все запросы к базе данных, включая (но, не ограничиваясь только ими) запросы SELECT , операторы INSERT , и т.д.
В типичном случае, если речь идет о большом приложении, эти элементы, скорее всего, не находятся на одной и той же машине, возможно, даже, что они находятся не в одной сети.
Хотя скорость работы сети в последние несколько лет значительно возросла, это по-прежнему самый медленный и самый ненадежный (по сравнению с другими) канал передачи данных (кэша процессора, памяти, жесткого диска и т.д.)
Таким образом, чтобы увеличить скорость работы приложения и повысить надежность, в некоторых случаях прибегают к тому, чтобы больше операций по обработке данных и логики выполнялось на стороне сервера (в частности, сервера MySQL ), и меньше данных передавалось по сети.
Во-вторых , это повышает производительность. ХП хранятся и запускаются непосредственно на сервере MySQL . Они могут быть предварительно скомпилированы и проанализированы на сервере базы данных.
Это существенным образом отличается от обработки того же запроса со стороны клиента, где запрос разбирается драйверами базы данных, анализируется и оптимизируется (если это возможно) каждый раз, когда вызывается оператор запроса.
Это похоже на исполнение интерпретируемого языка (на стороне клиента) и компилируемого языка (на стороне сервера базы данных). А мы знаем, что скомпилированная программа будет работать быстрее.
В-третьих , написанные однажды ХП выполняются где угодно. SQL является стандартной и 100% независимой платформой. Она опирается только на сервер баз данных. Подумайте, как много существует различных языков/библиотек, которые используются для работы с базой данных.
Они акцентируют внимание на повышении эффективности извлечения и обработки данных на стороне сервера, вместо того, чтобы задать одинаковую логику обработки данных, по — разному синтаксически описываемую всеми этими языками/библиотеками. Коль скоро эта логика обработки данных столь широко используется.
Не в последнюю очередь, ХП является фундаментальным аспектом безопасности баз данных.
Давайте рассмотрим простую установку для базы данных. Допустим в информационной системе управления персоналом (HRIS ) существует таблица, содержащая информацию о зарплате каждого сотрудника. Сотрудник отдела кадров должен иметь возможность получить некоторые данные из этой таблицы: общие суммы зарплаты, средняя зарплата и т.д.
Но этот сотрудник не должен иметь доступа к детальной информации по зарплате каждого сотрудника, так как эта информация является конфиденциальной и может быть доступна только для некоторых руководителей.
Мы знаем, что MySQL имеет систему полного управления привилегиями. Очевидно, что в этом случае мы не можем предоставить этому сотруднику отдела кадров даже привилегию SELECT (потому что, если мы это сделаем, это будет означать, что он / она может видеть детальную зарплату каждого сотрудника).
Но если он /она не может получить доступ к таблице зарплаты, то, как этот сотрудник сможет получить сводные данные по зарплате? Как мы можем обеспечить работнику отдела кадров доступ к этой информации без ущерба для кадровой политики компании?
Решить эту проблему можно с помощью хранимой процедуры, которая выдает запрашиваемую информацию и гранты сотруднику, который имеет привилегию EXECUTE . (Подробный список и описание привилегий MySQL вы можете найти в официальной документации .
В данном случае ХП является мостом между пользователем (нашим сотрудником отдела кадров) и таблицей (salary ), к которой пользователь не имеет прямого доступа:
Вот и все! С помощью ХП мы можем обеспечить пользователю возможность выполнить задачу без ущерба для безопасности базы данных (и кадровой политики)!
Недостатки использования хранимых процедур
После перечисления всех преимуществ использования ХП мы должны получить четкое представление о некоторых недостатках и посмотреть, есть ли способы улучшить ситуацию:
- Отсутствие контроля версии самой ХП. Когда изменяется версия ХП, она изменяется без сохранения истории действий для предыдущих версий на стороне сервера. Это может создать некоторые неудобства, когда пользователь хотел бы откатить изменения.
Я предлагаю в таких случаях писать ХП на стороне клиента и обеспечивать контроль версии здесь. Когда ХП готова, код можно легко скопировать, скажем, в MySQL Workbench и создать процедуру на стороне сервера. Таким образом, мы можем получить некоторую степень контроля версии.
- Отсутствие простого способа «синхронизировать
» введение в силу изменений и заставить все субъекты использовать последнюю версию, в частности, когда каждый из членов команды имеет свою собственную локальную базу данных для разработки и тестирования.
Контроль версий может стать решением, но все еще требуется ручное вмешательство через обновление локальной копии ХП на локальном сервере БД. Другой способ заключается в использовании «условного объекта ». Членов команды можно разделить так, чтобы хотя бы один человек отвечал на поддержание ХП и решения, использующие ее вызов через код.
Остальные участники группы, которым нужны результаты исполнения ХП, могут разрабатывать и тестировать свою часть с помощью условного объекта, предполагая, что «условные » вызовы ХП будут выдавать нужный результат. На более позднем этапе, можно объединить различные части, удалив условный код.
- Сложности с созданием резервных копий/экспортом. ХП находится на стороне сервера. Разработчики будут иметь только основные привилегии (SELECT
, EXECUTE
и т.д.) и никаких прав администратора для резервного копирования и экспорта. В некотором смысле, это является вовсе не недостатком, а скорее одним из основополагающих аспектов безопасности баз данных.
И обойти это ограничение нельзя, это и не рекомендуется. Предполагается, что в команде будет назначен отдельный администратор базы данных, который будет заниматься этой работой. Регулярное резервное копирование БД может также служить для целей экспорта (и импорта).
Создание хранимой процедуры в MySQL
Давайте рассмотрим, как создать ХП в сервере MySQL , создать пользователя, назначить ему привилегии и запустить (под этим логином) ХП, чтобы проверить, правильно ли обрабатываются данные. В своей рабочей среде я использую MySQL Workbench .
Существуют также и другие инструменты (PHPMyAdmin , например), так что вы можете выбрать то, что лучше всего подходят именно вам.
Предположим, у нас есть такая таблица:
CREATE TABLE `salary` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, PRIMARY KEY (`empid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Сперва мы создаем пользователя ‘tr’ для сотрудника отдела кадров, который должен получить из этой таблицы сводную информацию о зарплате (средняя зарплата по фирме, максимальная, минимальная и т.д.):
CREATE USER "tr"@"localhost" IDENTIFIED BY "mypass";
Этому пользователю мы назначаем только привилегию EXECUTE для таблицы, в которой содержатся данные по зарплате:
grant execute on hris.* to tr@`%`
Необходимость предоставления тех или иных привилегий мы можем уточнить, ознакомившись с разделом «Пользователи и привилегии » документации MySQL :
Теперь давайте создадим ХП следующим образом:
DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN select avg(sal) into avg_sal from salary; END
ПРИМЕЧАНИЕ : Все вышеперечисленные операции требует прав администратора на сервере MySQL .
После выполнения команды в MySQL Workbench , ХП avg_sal будет создана и готова к вызову. Она выводит среднюю зарплату из таблицы salary .
Чтобы проверить, может ли пользователь tr запустить ХП, не получая при этом доступ к таблице salary , мы можем сменить свою роль, войдя на сервер MySQL под логином пользователя tr. Это можно сделать, создав новое соединение в MySQL Workbench с другими логином и паролем.
После входа в систему от имени пользователя tr первое, что мы увидим, это то, что мы не можем видеть таблицы, нам доступны только ХП:
Ясно, что пользователь tr не сможет извлечь какие-либо данные из любой таблицы (то есть будет не в состоянии увидеть конкретные суммы зарплаты из таблицы salary ), но он будет в состоянии выполнить ХП, которую мы только что создали для определения средней зарплаты по компании:
call avg_sal(@out); select @out;
В результате выводится средняя зарплата.
К этому моменту мы выполнили всю подготовительную работу: создали пользователя, назначили ему привилегии, создали ХП и проверили ее исполнение. Далее мы покажем, как вызвать эту ХП из PHP.
Вызов хранимой процедуры из PHP
С помощью PDO вызов ХП выполняется довольно просто. Код РНР выглядит следующим образом:
$dbms = "mysql"; //Replace the below connection parameters to fit your environment $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=new PDO($dsn, $user, $pass); $q=$cn->exec("call avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);
$res будет содержать значение средней зарплаты из таблицы salary . После этого пользователь сможет производить дальнейшую обработку исходящих данных.
Заключение
В этой статье мы рассмотрели неотъемлемый компонент базы данных MySQL : хранимые процедуры.
Преимущества использования ХП очевидны, но позвольте мне еще раз их подчеркнуть: хранимые процедуры позволяют обеспечить строгий контроль доступа к некоторым данным в базе данных, чтобы отвечать требованиям бизнеса .
Мы также показали основные этапы создания хранимых процедур, создания пользователя и назначения привилегий, и как вызывать ХП через PHP.
Данная статья не охватывает все аспекты, которые касаются хранимых процедур. Некоторые важные моменты, такие как параметры ввода / вывода, операторы управления, курсоры, полный синтаксис и т.д. в этой короткой статье мы не рассматривали.
Если вас заинтересовала эта статья, пожалуйста, оставьте свой комментарий, и мы будем рады разместить более углубленные материалы об этом полезном и мощном элементе MySQL .
В MySQL 5 есть много новых функций, одной из самых весомых из которых является создание хранимых процедур. В этом уроке я расскажу о том, что они из себя представляют, а также о том, как они могут облегчить вам жизнь.
Введение
Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.
Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.
За
- Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
- Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
- Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
- Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.
Против
- Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
- Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
- Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
- Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.
Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.
Шаг 1: Ставим ограничитель
Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.
Шаг 2: Как работать с хранимыми процедурами
Создание хранимой процедуры
DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World !"; END//
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
4 характеристики хранимой процедуры:
- Language: в целях обеспечения переносимости, по умолчанию указан SQL.
- Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
- SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
- Comment: в целях документирования, значение по умолчанию - ""
Вызов хранимой процедуры
Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);
Изменение хранимой процедуры
В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
Удаление хранимой процедуры
DROP PROCEDURE IF EXISTS p2;
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Шаг 3: Параметры
Давайте посмотрим, как можно передавать в хранимую процедуру параметры.
- CREATE PROCEDURE proc1 (): пустой список параметров
- CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).
- CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.
Естественно, вы можете задавать несколько параметров разных типов.
Пример параметра IN
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//
Пример параметра OUT
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //
Пример параметра INOUT
DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //
Шаг 4: Переменные
Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.
Синтаксис объявления переменной выглядит так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Давайте объявим несколько переменных:
DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Работа с переменными
Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:
DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; END //
Шаг 5: Структуры управления потоками
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
Конструкция IF
С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value <> 0"; END IF; END //
Конструкция CASE
CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
Конструкция WHILE
Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Шаг 6: Курсоры
Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.
DECLARE cursor-name CURSOR FOR SELECT ...; /*Объявление курсора и его заполнение */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/ OPEN cursor-name; /*Открыть курсор*/ FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/ CLOSE cursor-name; /*Закрыть курсор*/
В этом примере мы проведем кое-какие простые операции с использованием курсора:
DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //
У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:
- Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
- Доступен только для чтения: курсоры нельзя изменять.
- Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.
Заключение
В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.
Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.