Интернет, компьютеры, софт и прочий Hi-Tech

Подписаться через RSS2Email.ru

Начало этой статьи доступно по ссылке «Хранимые процедуры в MySQL и PHP»:

Проще говоря, хранимые процедуры («ХП») — это сохраненные в базе данных процедуры (написанные с помощью SQL и других управляющих операторов), которые могут быть выполнены движком баз данных и вызваны из программного кода, который с этим движком работает. »»» Читать полностью

Хранимые процедуры в MySQL и PHP. Часть 2

Тэйлор Рен (Taylor Ren), 03.01.2014

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

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

Давайте рассмотрим, как создать ХП на сервере 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 в той схеме, где находится таблица salary:

grant execute on hris.*  to tr@`%`

Мы можем убедиться в том, что назначили нужную привилегию, открыв «Users and Privileges» в MySQL Bench:

Users and Privileges в MySQL Bench

Теперь создадим саму ХП следующим образом:

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

Очевидно, что пользователь tr не может обращаться ни к одной из таблиц (а значит, не может видеть и подробную информацию о зарплатах из таблицы salary), но может запустить созданную нами ХП, которая вернет ему среднюю зарплату по компании:

call avg_sal(@out);
select @out;

Будет отображена средняя зарплата.

Итак, мы выполнили всю подготовительную работу: создали пользователя, назначили ему привилегии, создали ХП и протестировали ее. Теперь посмотрим, как вызывать эту ХП из PHP.

Вызов хранимой процедуры из PHP

При использовании PDO вызов ХП довольно прост. Вот соответствующий PHP-код:

$dbms = 'mysql';

// Замените следующие параметры соединения на соответствующие вашему окружению:
$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. Теперь пользователь может производить дальнейшую обработку вывода с помощью PHP.

Выводы

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

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

Эта статья не покрывает всю тему хранимых процедур. Некоторые важные аспекты, такие как параметры ввода/вывода, управляющие операторы, курсоры, полный синтаксис и др. не были освещены в этой краткой статье.

Если вы заинтересовались, пожалуйста, оставьте здесь свой комментарий. Если нужно, мы будем рады предложить более глубокие статьи о таком полезном и мощном аспекте MySQL, как хранимые процедуры.

Автор: Тэйлор Рен, 03.01.2014
Перевод с английского: Дмитрий Скоробогатов, специально для xBB.uz, 12.03.2014
Оригинальный текст может быть найден по адресу http://www.sitepoint.com/stored-procedures-mysql-php/

Тэйлор Рен

Тэйлор — свободный разработчик веб- и десктопных приложений, проживающий в Сужоу в восточном Китае. Начинал со средств разработки Borland (C++Builder, Delphi), опубликовал книгу по InterBase. С 2003 является сертифицированным экспертом Borland. Затем переключился на веб-разработку в типичной конфигурации LAMP. Позднее начал работать с jQuery, Symfony, Bootstrap, Dart и т.д.


Предыдущие публикации:

Биржа долевых инвестиций SIMEX.

Последнее редактирование: 2014-03-12 05:31:13

Метки материала: db, mysql, базы данных, php, процедуры, базы данных mysql, хп, sql, хранимые процедуры, бд, программирование, mysql и php, базы

Оставьте, пожалуйста, свой комментарий к публикации

Представиться как     Антибот:
   

Просьба не постить мусор. Если вы хотите потестить xBB, воспользуйтесь кнопкой предварительного просмотра на панели инструментов xBBEditor-а.


© 2007-2017, Дмитрий Скоробогатов.
Разрешается воспроизводить, распространять и/или изменять материалы сайта
в соответствии с условиями GNU Free Documentation License,
версии 1.2 или любой более поздней версии, опубликованной FSF,
если только иное не указано в самих материалах.