Съхранени как да експортирате и импортирате съхранени процедури от phpmyadmin. Съхранените процедури на MySQL ефективно лекуват сънливост Извикване на съхранена процедура

7,8K

Казано по-просто, съхранените процедури („SP“) са процедури, съхранени в база данни (написани с помощта на SQL и други изрази), които могат да се нарекат двигател на база данни или свързан език за програмиране.

В тази статия ще ви кажа как да създадете HP с помощта на MySQL и да го изпълните на MySQL сървъра и чрез PHP.

Забележка: Тук няма да покрием всеки аспект на HP. За да получите помощ по въпроси, които не са обхванати в статията, винаги можете да използвате официалния MySQL документация.

HP се поддържа и от други често срещани сървъри за бази данни (Postgre, например), така че това, което ще обсъдим днес, се отнася и за тях.

Повечето от нас са добре запознати с обичайните настройки, които ни позволяват да създадем приложение за база данни: създаване на база данни, създаване на таблица, създаване на индекс, CRUD данни, генериране на заявки от страна на клиента и допълнителна обработка, ако е необходимо.

Този процес работи чудесно в повечето случаи, но има един важен аспект, който програмирането на базата данни не покрива: съхранената процедура.

Мога да се сетя за поне четири добри причини да използвам HP в приложения за бази данни. Първо, намалява мрежовия трафик и натоварването на сървъра.

Има четири компонента на стандартно PHP приложение за уеб база данни:

  • Клиентско ниво, което обикновено се представя от уеб браузър. Той осигурява взаимодействие с потребителя и позволява въвеждане на данни през потребителски интерфейс;
  • Ниво на уеб сървър, където потребителските заявки се обработват и отговорите се изпращат обратно на ниво клиент;
  • PHP слой, който обработва всички PHP компоненти, създава логика на приложението и генерира PHP частта от отговора;
  • Ниво база данни, който обработва всички заявки към база данни, включително (но не само) SELECT заявки, оператори INSERT и т.н.

Обикновено, когато става въпрос за голямо приложение, тези елементи най-вероятно не са на една и съща машина, дори е възможно да не са в една и съща мрежа.

Въпреки че мрежовите скорости се увеличиха значително през последните няколко години, това все още е най-бавният и ненадежден (в сравнение с други) канал за пренос на данни (CPU кеш, памет, харддиски т.н.)

По този начин, за да се увеличи скоростта на приложението и да се подобри надеждността, в някои случаи се прибягва до повече обработка на данни и логика, извършвана от страната на сървъра (по-специално MySQL сървър) и по-малко данни, прехвърлени по мрежата.

Второ, подобрява производителността. HP се съхраняват и стартират директно на MySQL сървъра. Те могат да бъдат предварително компилирани и анализирани на сървъра на базата данни.

Това е доста различно от обработката на същата заявка от страна на клиента, където заявката се анализира от драйверите на базата данни, анализира се и се оптимизира (ако е възможно) всеки път, когато се извиква операторът на заявката.

Това е подобно на изпълнение на интерпретиран език (от страната на клиента) и компилиран език (от страната на сървъра на базата данни). И знаем, че компилираната програма ще работи по-бързо.

Трето, веднъж написан HP може да се изпълни навсякъде. SQL е стандартен и 100% независим от платформата. Той разчита само на сървъра на базата данни. Помислете колко различни езици/библиотеки има, които се използват за работа с база данни.

Те се фокусират върху подобряването на ефективността на извличане и обработка на данни от страна на сървъра, вместо да определят една и съща логика за обработка на данни, синтактично описана по различен начин от всички тези езици/библиотеки. Тъй като тази логика за обработка на данни е толкова широко използвана.

Не на последно място, HP е основен аспект на сигурността на базата данни.

нека помислим лесен монтажза базата данни. Да речем в информационна системаСистемата за управление на човешките ресурси (HRIS) разполага с таблица, съдържаща информация за заплатата на всеки служител. Служителят по човешки ресурси трябва да може да получи някои данни от тази таблица: обща сума на заплатите, средна заплата и т.н.

Но този служител не трябва да има достъп до подробна информациявърху заплатата на всеки служител, тъй като тази информация е поверителна и може да бъде достъпна само за някои мениджъри.

Знаем, че MySQL има пълна система за управление на привилегиите. Очевидно в този случай не можем да предоставим на този служител по човешки ресурси дори привилегията SELECT (защото ако направим това, това ще означава, че той/тя може да види подробната заплата на всеки служител).

Но ако той/тя няма достъп до таблицата със заплатите, как тогава този служител ще може да получи обобщената информация за заплатите? Как можем да предоставим на служителя по човешки ресурси достъп до тази информация, без да компрометираме политиката за човешки ресурси на компанията?

Заобиколно решение за този проблем е да се използва съхранена процедура, която издава исканата информация и предоставя на служител, който има привилегията EXECUTE. (За подробен списък и описание на привилегиите на MySQL можете да намерите в официалната документация.

В този случай HP е мост между потребителя (наш служител по човешки ресурси) и таблицата (заплата), до която потребителят няма директен достъп:


Това е всичко! С помощта на HP можем да предоставим на потребителя възможността да изпълни задача, без да компрометира сигурността на базата данни (и политиката за персонала)!

Недостатъци на използването на съхранени процедури

След като изброим всички предимства от използването на HP, трябва да разберем ясно някои от недостатъците и да видим дали има начини да подобрим ситуацията:

  • Липса на контрол на версиите на самото HP. Когато версията на HP се промени, тя се променя, без да се запазва историята на действията за предишни версииот страната на сървъра. Това може да създаде известно неудобство, когато потребителят иска да върне обратно промените.

    В такива случаи предлагам да напишете HP от страна на клиента и да предоставите контрол на версиите тук. Когато HP е готов, кодът може лесно да се копира в, да речем, MySQL Workbench и да се създаде процедура от страната на сървъра. По този начин можем да получим известна степен на контрол на версиите.

  • Отсъствие прост начин « синхронизирам» прилагане на промените и принуждаване на всички субекти да използват последна версия, особено когато всеки член на екипа има собствена локална база данни за разработка и тестване.

    Контролът на версиите може да е решение, но все още е необходима ръчна намеса чрез актуализиране на локалното копие на HP до локален сървър DB. Друг начин е да използвате " условен обект" Членовете на екипа могат да бъдат разделени, така че поне един човек да отговаря за поддръжката на HP и решенията, използвайки неговия код за повикване.

    Останалата част от групата, която се нуждае от резултатите от изпълнението на HP, може да разработи и тества своята част с помощта на условен обект, като се предполага, че „условните“ извиквания към HP ще доведат до желания резултат. На по-късен етап можете да комбинирате различните части, като премахнете условния код.

  • Трудности при създаване на резервни копия/експортиране. HP е от страната на сървъра. Разработчиците ще имат само основни привилегии (ИЗБОР, ИЗПЪЛНЕНИЕ и т.н.) и няма администраторски права за архивиране и експортиране. В известен смисъл това изобщо не е недостатък, а по-скоро един от основните аспекти на сигурността на базата данни.

    И не можете да заобиколите това ограничение и не се препоръчва. Очаква се екипът да има специален DBA, назначен за тази работа. Редовен архивиранеБазата данни може да служи и за експорт (и импорт) цели.

Създаване на съхранена процедура в MySQL

Нека да разгледаме как да създадете HP в MySQL сървъра, да създадете потребител, да му присвоите привилегии и да стартирате (под това влизане) HP, за да проверите дали данните се обработват правилно. В моята работна среда използвам 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’ за служителя HR, който трябва да получи обобщена информация за заплатата от тази таблица (средна заплата за компанията, максимална, минимална и т.н.):

СЪЗДАВАНЕ НА ПОТРЕБИТЕЛ "tr"@"localhost" ИДЕНТИФИЦИРАН ОТ "mypass";

Ние присвояваме само привилегията EXECUTE на този потребител за таблицата, която съдържа данни за заплати:

предоставяне на изпълнение на hris.* на tr@`%`

Можем да изясним необходимостта от предоставяне на определени привилегии, като прочетете раздела „ Потребители и привилегии» MySQL документация:


Сега нека създадем HP, както следва:

DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) НАЧАЛО изберете avg(sal) в avg_sal от salary; КРАЙ

ЗАБЕЛЕЖКА: Всички горепосочени операции изискват администраторски права на MySQL сървъра.

След изпълнение на командата в MySQL Workbench, avg_sal HP ще бъде създаден и готов за извикване. Той извежда средната заплата от таблицата със заплатите.

За да проверим дали потребителят tr може да управлява HP без достъп до таблицата със заплатите, можем да променим нашата роля, като влезем в MySQL сървъра като потребител tr. Това може да стане чрез създаване на нова връзка в MySQL Workbench с различно потребителско име и парола.

След като влезем като потребител tr, първото нещо, което ще видим е, че не можем да видим таблици, само HP е достъпен за нас:


Ясно е, че потребителят tr няма да може да извлече никакви данни от никоя таблица (т.е. няма да може да види конкретни суми на заплатите от таблицата със заплатите), но той ще може да стартира HP, който току-що създадохме, за да определи средната заплата за компанията :

извикване avg_sal(@out); изберете @out;

Резултатът е средната заплата.

На този етап приключихме цялата подготвителна работа: създадохме потребител, присвоихме му привилегии, създадохме HP и проверихме изпълнението му. След това ще покажем как да извикате този HP от PHP.

Извикване на съхранена процедура от PHP

Използвайки PDO, извикването на HP е доста лесно. PHP кодът изглежда така:

$dbms = "mysql"; //Заменете параметрите на връзката по-долу, за да отговарят на вашата среда $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=ново PDO($dsn, $user, $pass); $q=$cn->exec("извикване avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);

$res ще съдържа средната стойност на заплатата от таблицата със заплатите. След това потребителят ще може допълнително да обработва изходящите данни.

Заключение

В тази статия разгледахме неразделен компонент на базата данни MySQL: съхранени процедури.

Ползите от използването на HP са очевидни, но нека ги подчертая отново: Съхранените процедури ви позволяват да осигурите строг контрол на достъпа до определени данни в базата данни, за да отговаряте на бизнес изискванията.

Също така показахме основните стъпки за създаване на съхранени процедури, създаване на потребител и присвояване на привилегии и как да извикате HP чрез PHP.

Тази статия не обхваща всички аспекти, които се отнасят до съхранените процедури. някои важни точки, като I/O параметри, контролни оператори, курсори, пълен синтаксис и др. Не сме разгледали това в тази кратка статия.

Ако се интересувате от тази статия, моля, оставете коментар и ние ще се радваме да публикуваме по-задълбочен материал за този полезен и мощен елемент на 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 КОМЕНТАР "Процедура" BEGIN SELECT "Hello World!"; КРАЙ//

Първата част от кода създава съхранена процедура. Следващият съдържа незадължителни параметри. След това идва името и накрая тялото на самата процедура.

Имената на запомнените процедури са чувствителни към малки и големи букви. Също така не можете да създавате множество процедури с едно и също име. Не може да има изрази в съхранена процедура, които променят самата база данни.

4 характеристики на съхранена процедура:

  • Език: За целите на преносимостта по подразбиране е SQL.
  • Детерминиран: ако процедурата винаги връща един и същ резултат и приема едни и същи входни параметри. Това е за процеса на репликация и регистрация. Стойността по подразбиране НЕ е ДЕТЕРМИНИСТИЧНА.
  • SQL сигурност: потребителските права се проверяват по време на разговора. INVOKER е потребителят, който извиква съхранената процедура. DEFINER е “създателят” на процедурата. Стойността по подразбиране е DEFINER.
  • Коментар: За целите на документацията стойността по подразбиране е ""

Извикване на съхранена процедура

За да извикате съхранена процедура, трябва да въведете ключовата дума CALL, последвана от името на процедурата, последвано от параметрите (променливи или стойности) в скоби. Скобите са задължителни.

CALL име на_съхранена_процедура (param1, param2, ....) CALL procedure1(10, "параметър на низ", @parameter_var);

Модифициране на съхранена процедура

MySQL има оператор ALTER PROCEDURE за промяна на процедури, но той е подходящ само за промяна на определени характеристики. Ако трябва да промените параметрите или тялото на процедура, трябва да я изтриете и да я създадете отново.

Премахване на съхранена процедура

ОТПУСКАНЕ НА ПРОЦЕДУРА, АКО СЪЩЕСТВУВА p2;

Това е проста команда. Операторът IF EXISTS улавя грешка, ако такава процедура не съществува.

Стъпка 3: Опции

Нека да видим как можем да предадем параметри на съхранена процедура.

  • CREATE PROCEDURE proc1(): празен списък с параметри
  • CREATE PROCEDURE proc1 (IN varname DATA-TYPE): един входен параметър. Думата IN не е задължителна, защото параметрите по подразбиране са 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 резултат; КРАЙ//

Примерен OUT параметър

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Това е тест"; КРАЙ //

Пример за INOUT параметър

DELIMITER // СЪЗДАВАНЕ НА ПРОЦЕДУРА `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; КРАЙ //

Стъпка 4: Променливи

Сега ще ви науча как да създавате променливи и да ги съхранявате в процедурите. Трябва да ги декларирате изрично в началото на блока BEGIN/END, заедно с техните типове данни. След като сте декларирали променлива, можете да я използвате по същия начин като сесийни променливи, литерали или имена на колони.

Синтаксисът за деклариране на променлива изглежда така:

DECLARE varname DATA-TYPE DEFAULT стойност по подразбиране;

Нека декларираме някои променливи:

DECLARE a, b INT ПО ПОДРАЗБИРАНЕ 5; DECLARE str VARCHAR(50); ДЕКЛАРИРАНЕ днес TIMESTAMP ПО ПОДРАЗБИРАНЕ CURRENT_DATE; ДЕКЛАРИРАНЕ 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); ДЕКЛАРИРАНЕ днес TIMESTAMP ПО ПОДРАЗБИРАНЕ CURRENT_DATE; ДЕКЛАРИРАНЕ v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "Аз съм низ"; SELECT CONCAT(str,paramstr), днес FROM table2 WHERE b >=5; КРАЙ //

Стъпка 5: Структури за контрол на нишки

MySQL поддържа IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT конструкции за управление на нишки в съхранена процедура. Ще разгледаме как да използваме IF, CASE и WHILE, тъй като те са най-често използваните.

IF дизайн

Използвайки конструкцията IF, можем да изпълняваме задачи, съдържащи условия:

DELIMITER // СЪЗДАВАНЕ НА ПРОЦЕДУРА `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET променлива1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; ENDIF; IF param1 = 0 THEN SELECT „Стойност на параметъра = 0“; ELSE SELECT „Стойност на параметър<>0"; КРАЙ АКО; КРАЙ //

CASE дизайн

CASE е друг метод за тестване на условията и избор на подходящо решение. Това страхотен начинзамяна на множество IF конструкции. Конструкцията може да бъде описана по два начина, осигурявайки гъвкавост при управлението на множество условни изрази.

DELIMITER // СЪЗДАВАНЕ НА ПРОЦЕДУРА `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET променлива1 = param1 + 1; CASE променлива1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); КРАЙНА КАСА; КРАЙ //

DELIMITER // СЪЗДАВАНЕ НА ПРОЦЕДУРА `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET променлива1 = 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); КРАЙНА КАСА; КРАЙ //

WHILE дизайн

Технически има три вида цикли: цикъл WHILE, цикъл LOOP и цикъл REPEAT. Можете също така да зациклите, като използвате техниката за програмиране на Дарт Вейдър: оператори GOTO. Ето един примерен цикъл:

РАЗДЕЛИТЕЛ // СЪЗДАВАНЕ НА ПРОЦЕДУРА `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET променлива1 = 0; WHILE променлива1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Стъпка 6: Курсори

Курсорите се използват за обхождане на набора от редове, върнати от заявка, и обработка на всеки ред.

MySQL поддържа курсори в съхранени процедури. Ето кратък синтаксис за създаване и използване на курсор.

DECLARE име на курсора CURSOR FOR SELECT ...; /*Деклариране на курсор и попълването му */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Какво да се направи, когато няма повече записи*/ OPEN cursor-name; /*Отворете курсора*/ FETCH cursor-name INTO променлива [, променлива]; /*Присвояване на стойност на променлива, равна на текущата стойност на колоната*/ 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; ДЕКЛАРИРАНЕ НА ПРОДЪЛЖАВАНЕ НА ОБРАБОТАТЕЛ ЗА НЕНАМЕРЕНО НАБОР b = 1; ОТВОРЕНО cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; ENDIF; КРАЙ ДОКАТО; ЗАТВОРИ cur1; SET param1 = c; КРАЙ //

Курсорите имат три свойства, които трябва да разберете, за да избегнете неочаквани резултати:

  • Не е чувствителен: курсор, който се отваря веднъж, няма да отразява промените в таблицата, настъпили по-късно. В действителност MySQL не гарантира, че курсорът ще бъде актуализиран, така че не разчитайте на него.
  • Само за четене: Курсорите не могат да се променят.
  • Без превъртане назад: курсорът може да се движи само в една посока - напред, няма да можете да пропускате редове, без да ги изберете.

Заключение

В този урок ви запознах с основите на работата със запомнени процедури и някои от специфичните свойства, свързани с тях. Разбира се, ще трябва да задълбочите знанията си в области като сигурност, SQL изрази и оптимизация, преди да станете истински гуру на MySQL процедури.

Трябва да изчислите ползите от използването на съхранени процедури във вашето конкретно приложение и след това да създадете само необходимите процедури. По принцип използвам процедури; По мое мнение си струва да бъдат внедрени в проекти поради тяхната сигурност, поддръжка на код и цялостен дизайн. Също така имайте предвид, че MySQL процедурите все още са в процес на работа. Очаквайте подобрения по отношение на функционалността и подобренията. Моля, не се колебайте да споделите вашите мнения.

Просто казано, съхранените процедури („SP“) са процедури, съхранени в база данни (написани с помощта на SQL и други контролни оператори), които могат да бъдат изпълнени от машина на база данни и извикани от програмния код, който работи с тази машина. """ Прочетете напълно

Съхранени процедури в MySQL и PHP. Част 2

Тейлър Рен (Тейлър Рен), 03.01.2014

Създаване на съхранена процедура в MySQL

Тъй като HP се съхраняват на сървъра, препоръчително е да се създават директно на сървъра, т.е. Не трябва да използвате PHP или други езици за програмиране, за да изпълнявате SQL команди за създаване на съхранени процедури.

Нека да разгледаме как да създадем HP на MySQL сървър, как да създадем потребител за него и как да му присвоим привилегии да управлява нашия HP. След това ще проверим правилността на резултата. За това ще използвам 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", както следва:

СЪЗДАВАНЕ НА ПОТРЕБИТЕЛ "tr"@"localhost" ИДЕНТИФИЦИРАН ОТ "mypass";

Сега нека присвоим на този потребител единствената привилегия EXECUTE в схемата, където се намира таблицата със заплатите:

Предоставяне на изпълнение на hris.* на tr@`%`

Можем да проверим дали сме задали правилната привилегия, като отворим „Потребители и привилегии“ в MySQL Bench:

Сега нека създадем самия HP, както следва:

DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) НАЧАЛО изберете avg(sal) в avg_sal от salary; КРАЙ

След изпълнение на тази команда в MySQL Workbench ще бъде създаден готов за използване avg_sal HP. Връща средната заплата от таблицата със заплатите.

За да проверим дали потребителят tr действително може да управлява HP и няма достъп до таблицата със заплатите, трябва да се свържем отново с MySQL сървъра, като влезем като tr. В MySQL Workbench това може да стане чрез създаване на друга връзка и указване на желания потребител и неговата парола.

След свързване от под tr , първото нещо, което забелязваме е, че потребителят изобщо не вижда никакви таблици, той вижда само HP:

Очевидно потребителят tr няма достъп до никоя от таблиците (и следователно не може да види подробна информация за заплатите от таблицата със заплатите), но той може да стартира HP, който създадохме, което ще му върне средната заплата за компанията:

Извикване avg_sal(@out); изберете @out;

Ще се покаже средната работна заплата.

И така, свършихме цялата подготвителна работа: създадохме потребител, присвоихме му привилегии, създадохме HP и го тествахме. Сега нека видим как да извикаме това HP от PHP.

Извикване на съхранена процедура от PHP

Когато използвате PDO, извикването на HP е доста лесно. Ето съответния PHP код:

$dbms = "mysql"; // Заменете следните параметри на връзката с тези, подходящи за вашата среда: $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=ново PDO($dsn, $user, $pass); $q=$cn->exec("извикване avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);

Променливата $res съдържа средната заплата от таблицата със заплатите. Сега потребителят може да извърши допълнителна обработка на изхода с помощта на PHP.

заключения

В тази статия разгледахме един отдавна забравен компонент на базите данни MySQL: съхранени процедури. Ползите от използването на HP са очевидни, но нека ви напомня: Съхранените процедури ни позволяват да прилагаме по-строг контрол на достъпа до определени данни, когато бизнес логиката го изисква.

Освен това демонстрирахме основните стъпки при създаване на съхранени процедури, потребители и присвояване на подходящи привилегии и показахме как HP се извиква от PHP.

Тази статия не обхваща цялата тема за запомнените процедури. Някои важни аспекти като I/O параметри, контролни оператори, курсори, пълен синтаксис и т.н. не бяха обхванати в тази кратка статия.

Ако се интересувате, моля, оставете коментар тук. Ако е необходимо, ще се радваме да предложим по-задълбочени статии за полезния и мощен аспект на MySQL, запаметените процедури.

Тейлър Рен

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

Предишни публикации:

От автора:Защо спиш на работа! Будни ли сте и чакате СУБД да изпълни заявката? Така че трябва да се ускори. Използвали ли сте MySQL съхранени процедури? не знам как? Е, тогава се събудете, защото сега ще разгледаме точно тази тема.

Какви други процедури има?

Ако имате фобия относно медицинските процедури, тогава тези структури „не са правилната тема“. Така че не е нужно да се страхувате. Но сериозно, съхранените процедури са удобно и полезно нещо за „здравето“ на СУБД. Те се наричат ​​още „съхранени функции в MySQL“, но това не е съвсем точна дефиниция. Въпреки че нека се справим с всичко по ред.

Съхранените процедури могат значително да оптимизират производителността на сървъра и да увеличат неговата скорост, тъй като техният код се съхранява в RAM кеша след първото изпълнение. За всички следващи извиквания процедурата ще бъде извлечена от кеша, вместо да бъде изпратена отново за изпълнение.

В MySQL извикването на съхранена процедура означава, че заявките, записани в нейния код, ще бъдат обработени само наполовина. И само ако стойностите на техните параметри се променят. Но не всичко е толкова перфектно. Нека първо опишем положителните аспекти на използването на процедурите:

Капсулация на функционалността – целият код се съхранява на едно място, което улеснява достъпа на други приложения. По този начин запаметените процедури са подобни на програмните функции.

Изолиране на достъпа до данни – всички потребители нямат достъп до редовете на таблицата, а само до съхранените процедури. Което от своя страна повишава нивото на сигурност на всички данни.

Увеличаване на скоростта на сървъра чрез кеширане и обединяване на заявки.

В MySQL съхранените процедури на теория са структури, свързани с „по-висши въпроси“ - програмиране на СУБД. Така че вие ​​и аз (като професионалисти) поне бавно, но... Но да се върнем към процедурите и да опишем отрицателните страни на тяхното използване:

Натоварването на сървъра на базата данни се увеличава - по-голямата част от кода на процедурата се изпълнява от страната на сървъра. Тази СУБД е изградена по модел клиент-сървър, който включва няколко устройства.

Манипулирането на данните става по-сложно - когато разработвате приложения, ще трябва да напишете част от кода от страна на клиента.

Процесът на прехвърляне на бази данни към други релси (СУБД) става все по-сложен.

Процедури в phpMyAdmin

Първо, нека да разгледаме използването на запомнени процедури в MySQL, използвайки phpMyAdmin като пример. По този начин ще ни бъде по-лесно да разберем този тип структура. Да започваме!

Стартираме софтуерната обвивка, избираме тестовата база данни вдясно. Моята база данни е световна. След това в главното меню в горната част отидете на раздела „Процедури“. Тук кликнете върху „Добавяне на процедура“.

След това ще се появи диалоговият прозорец „Добавяне на процедура“. Попълваме всички полета, посочени на снимката. Посочете името и типа на процедурата. В колоната „Дефиниция“ въведете сметкапотребител, а в коментарите (незадължително) си посочваме, че това е само пример за съхранена процедура.

Още на този етап се запознаваме с особеностите на синтаксиса за създаване на MySQL съхранени процедури. В полето „Дефиниция“ записваме тялото на структурата. Обърнете внимание, че изпълняваната заявка е между ключови думиНАЧАЛО и КРАЙ:

BEGIN SELECT "HELLO, WORD!"; КРАЙ

НАЧАЛО

ИЗБЕРЕТЕ „ЗДРАВЕЙ, WORD!“ ;

Тази заявка не извършва никакви действия с базата данни, а само показва надпис. Посочихме това в полето „Достъп до SQL данни“.

За да завършите създаването на нашата първа процедура, щракнете върху „Ok“ в долната част. След това програмата показва „зелено“ съобщение, което показва, че заявката е изпълнена успешно. Кодът му е представен по-долу. В MySQL съхранените процедури и функции се създават с помощта на специалната команда CREATE PROCEDURE. Но повече за това по-късно.

Сега нека стартираме създадената структура за изпълнение. За да направите това, в секцията „Процедури“ щракнете върху връзката „Изпълнение“. Но какъв позор е това! Къде отиде любимото ни „зелено“? Защо програмата „псува“ и „крещи“, че няма достатъчно разпределена памет?

Къде гледаше авторът на тази публикация...! Съжалявам, малко объркан. Все пак авторът съм аз. Спокойно, сега ще оправим всичко! Тази грешка възниква, защото стойността на параметъра thread_stack в основния конфигурационен файл е оставена непроменена. По подразбиране за всеки поток се разпределят 128 Kb. Разпределеният RAM лимит е напълно достатъчен за извършване на прости заявки, но не е достатъчен за процедури.

Това още веднъж доказва, че повече ресурси се изразходват за изпълнение на тригери и съхранени процедури в MySQL.

Отидете до конфигурационния файл my.ini и увеличете лимита на RAM, зададен за всяка нишка, до 256 kb. Сега стартирайте създадената процедура отново. Този път всичко мина според очакванията и програмата върна резултата без грешка.

Моля, обърнете внимание, че извикването се извършва с помощта на командата CALL, като се посочва името на процедурата и приетите параметри (в скоби).

По-сложен пример

Но все пак възможностите на phpMyAdmin са по-подходящи за бързо създаване на процедури. А за да разработите съхранена процедура в MySQL с динамичен брой аргументи (например), ще ви трябва по-удобен софтуер. Защо:

phpMyAdmin не иска да „разбира“ правилно процедури, които не са създадени чрез специален конструктор.

Програмата не изпълнява структури, стартирани под root и с празна парола, а в Денвър създаването на нов потребител и влизането в phpMyAdmin под него е истински проблем.

Ако следите внимателно моите публикации и изпълнявате всички „желания“, посочени в тях, тогава вече трябва да имате инсталиран MySQL Administrator. Във връзка с това просто трябва да изтеглите MySQL Query Browser от тази връзка. По-добре е да използвате тези две програми заедно: създайте процедури в първата и ги тествайте в другата. Отивам:

Горе вляво преминете през раздела „Каталог“.

Изберете желаната база данни и в горното меню щракнете върху „Съхранени процедури“, а в долната част върху „Създаване на съхранена процедура“

В прозореца на редактора, който се появява, въведете кода на процедурата и щракнете върху „Изпълнение на SQL“.

CREATE DEFINER=`roman`@`localhost` PROCEDURE `proc5`() BEGIN декларира int; set a="SELECT COUNT(*) FROM city as"; if(a > 1000)THEN SELECT "<1000"; ELSE SELECT ">1000"; КРАЙ АКО; КРАЙ

CREATE DEFINER = ` roman ` @ ` localhost ` PROCEDURE ` proc5 ` ()

НАЧАЛО

декларирайте int;

задайте = „ИЗБЕРЕТЕ БРОЯ (*) ОТ град като“;

ако (a > 1000 ) ТОГАВА

ИЗБЕРЕТЕ "<1000" ;

ДРУГО

ИЗБЕРЕТЕ ">1000" ;

КРАЙ АКО ;

Може да сте забелязали, че не предаваме никакви стойности в процедурата. Освен това MySQL може да има неизвестен брой параметри в съхранена процедура, която след това може да бъде предадена през нови декларации на променливи, разположени в цикли.

За да започнете процедурата, отидете на MySQL Query Browser. Първо въведете своя акаунт и парола, а след това отляво в „Object Explorer“ намираме папката с необходимата база данни. Останалата част от последователността от действия е показана на следващата снимка.

Изпълнение на процедура в PHP

Сега нека да разгледаме как се извиква MySQL съхранена процедура в PHP. За да направим това, ще трябва леко да „преначертаем“ кода на предишния ни пример. Ще добавим изходен параметър към процедурата и ще променим кода на заявката:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(out col decimal) BEGIN SELECT COUNT(*) into col FROM city; КРАЙ

CREATE DEFINER = ` root ` @ ` localhost ` PROCEDURE ` proc6 ` (out col decimal )

НАЧАЛО

ИЗБЕРЕТЕ БРОЙ (*) в колона ОТ град;

За да се обадите от PHP файлпроцедура и резултат ще използваме възможностите на класа PDOStatement, създаден специално за работа с базата данни чрез SQL

Този клас е внедрен сравнително наскоро и се поддържа от PHP от версия 5.1.0. Преди употреба ви съветвам да проверите версията на езика, който използвате, като използвате вградената функция phpversion().

Публикации по темата