Головна  /  Принтери  /  Збережені процедури MySQL ефективно лікують сонливість. Збережені процедури в MySQL і PHP Як відкрити в phpmyadmin процедури, що зберігаються

Збережені процедури MySQL ефективно лікують сонливість. Збережені процедури в MySQL і PHP Як відкрити в phpmyadmin процедури, що зберігаються

Принтери
05.04.2021

Видалити DEFINER = root@localhost

у локальному, у той час як імпорт,

він буде виконано.

Чи можете ви, хлопці, допомогти мені щодо процедур, що зберігаються. Коли я експортую процедуру, що зберігається з phpmyadmin, вона вказується як

CREATE DEFINER = `root` @ `localhost` PROCEDURE `c4mo_get_cities_prc` (IN `p_state_code` VARCHAR(3), IN`p_country_code` `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 - Ви маєте error in your SQL syntax; check the manual that corresponds to your MySQL server version for right syntax to use near "" at line 13

Це досить просто, якщо ви використовуєте інтерфейс phpmyadmin.

Для експорту :

Ви побачите звичайну вкладку, оскільки ця вкладка буде відображатися тільки в тому випадку, якщо у вас вже є хоча б одна процедура, що зберігається.

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

Внизу позначте галочкою прапорець і потім експортуйте. Вам просто потрібно скопіювати весь код і зберегти його в будь-якому місці на локальній машині з your_stored_procedure.sql файлу your_stored_procedure.sql.

Для імпорту:

Просто виберіть базу даних і імпортуйте файл, що зберігається your_stored_procedure.sql, як зазначено вище, оскільки ви зазвичай імпортуєте файл.sql (таблиці) для свого db.

Простіше кажучи, процедури, що зберігаються («ХП») - це збережені в базі даних процедури (написані за допомогою 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:

Тепер створимо саму ХП наступним чином:

DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN select avg(sal) в avg_sal з лари; END

Після виконання цієї команди MySQL Workbench, буде створена готова до використання ХП avg_sal . Вона повертає середню зарплату за таблицею salary.

Щоб перевірити, чи дійсно tr може запустити ХП і не має доступу до таблиці salary , нам потрібно перепідключитися до серверу MySQL, залогінившись як tr. У MySQL Workbench це можна зробити створивши інше з'єднання та вказавши потрібного користувача та його пароль.

Після підключення з під 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, як процедури, що зберігаються.

Тейлор Рен

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

Попередні публікації:

У 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 = "Тим є"; 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 досі ведеться робота. Очікуйте покращень, що стосуються функціональності та покращень. Прошу, не соромтеся ділитися думками.

Продовжуємо працювати базою даних mytest, створеною в попередній . Сьогодні додамо в наш додаток можливість відповіді на коментар користувача, а також навчимося створювати процедури, що зберігаютьсята функції.

Створення процедури, що зберігається

Відкриваємо phpmyadmin. Вибираємо базу даних mytest і натискаємо її заголовок, або на значок Browse. Потім переходимо на вкладку Routines та створюємо нову процедуру, натиснувши на напис Add routine.

З'явиться форма, яку потрібно заповнити.

Routine Name (ім'я процедури/функції) ReplyToComment.

Type (тип) – процедура. Відмінність процедури від функції полягає в тому, що функція завжди повертає якесь значення і містить оператор return.

Parameters (параметри) наша процедура прийматиме два параметри: текст відповіді та id коментаря, на який ми відповідаємо. Обидва параметри передаватимуться з нашої клієнтської програми.

Створення параметрів процедури

Створимо перший параметр

Directionвказуємо напрямок параметра (IN, OUT, INOUT). У нашій процедурі обидва параметри, що передаються, будуть вхідними (IN).

Name (ім'я параметра) Content.

Type (тип) INT, VARCHAR, DATETIME тощо. Параметр Content містить текст відповіді, який зберігатиметься в стовпці comment_content. Дана колонка має певний тип, щоб визначити, відкриваємо таблицю wp_comments і переходимо на вкладку Structure, знаходимо потрібне нам ім'я стовпця і дивимося на його тип у колонці Type. У даному прикладіколонка має тип - text, цей тип потрібно вказати нашого параметра.

Length/Values ​​(довжина або значення) для типу Text це поле не можна встановити, але зазвичай тут вказується довжина, наприклад VARCHAR(20), INT(10), або якесь значення за замовчуванням.

Options як додаткові опції можна вказати поточне кодування стовпця, її також можна подивитися на вкладці Structure колонка Collation. Встановимо значення utf8.

результат

Додамо другий параметр, натиснувши кнопку Add parameter.

Direction - IN Name - ComID Type - BIGINT Length/Values ​​- 20 Options - UNSIGNED

Обидва параметри створені, продовжуємо заповнювати форму.

Definitionтут ми описуємо тіло процедури. Тіло є блок, який починається з ключового слова BEGIN і закінчуються ключовим словом END. Всередині тіла процедури можна розміщувати текст запиту, оголошувати змінні, використовувати конструкції розгалуження, цикли та багато іншого, як у будь-якій мові програмування.

тіло процедури

Для початку створимо блок початку та кінця тіла нашої процедури.

BEGIN END;

Тепер додамо текст запиту, який заповнюватиме поля в таблиці wp-comments, при додаванні нового коментаря (відповіді).

BEGIN INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (підставлені значення); END;

Зберігати значення будемо в змінних. Для створення змінної використовується ключове слово DECLARE, потім вказується ім'я, тип і довжина змінної, так само можна вказати значення за замовчуванням. Якщо змінна має параметр DEFAULT, то така змінна є ініціалізованою.

DECLARE ім'я тип (довжина) DEFAULT значення за замовчуванням;

Також можна встановити значення, для будь-якої змінної використовуючи оператор SET.

SET ім'я змінної = значення;

І так створимо три змінні: Author, Email, UsedID, які зберігатимуть значення для колонок: comment_author, comment_author_email, user_id.

BEGIN DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; -- Оголосили змінну Email DECLARE Email varchar (100); -- Встановили змінну Email SET Email = " [email protected]"; END;

comment_contentЦя колонка зберігає текст коментаря, який передається в процедуру у вигляді вхідного параметра Cоntent. Ми не створюватимемо окрему змінну, а просто підставимо в VALUES значення вхідного параметра.

comment_dateі comment_date_gmtобидві колонки при першому заповненні матимуть однакові значення. Створимо змінну Date і надамо їй як значення результату, який повертатиме вбудована функція NOW. Ця функціяповертає поточну датута час у форматі DATETIME.

DECLARE MyCurDate DATETIME DEFAULT NOW();

DECLARE MyCurDate DATETIME; SET MyCurDate = NOW();

comment_approvedЧи схвалено коментар, 1 (так) інакше 0. Створимо змінну Approved, але перед встановленням значення зробимо невелику перевірку.

DECLARE Approved varchar(20); IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; END IF;

comment_parentтут як значення потрібно вказати id коментаря, який ми відповідаємо. ID передається у процедуру як другий вхідний параметр. Створимо змінну ParentCom і надамо їй значення переданого параметра.

DECLARE ParentCom varchar(20); SET ParentCom = ComID;

Залишився останній параметр comment_post_idтут потрібно вказати id посту, в якому буде розміщено наш коментар. Оголосимо змінну з ім'ям PostID.

DECLARE PostID BIGINT(20);

на даний моменттіло процедури має виглядати так

BEGIN - блок оголошення змінних DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; DECLARE Email varchar(100); DECLARE Date DATETIME DEFAULT NOW(); DECLARE ParentCom varchar(20); DECLARE Approved varchar(20); DECLARE PostID BIGINT(20); -- Встановлення значень змінних IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; END IF; SET Email = " [email protected]"; UserID);

Від автора:чого це ви спите на робочому місці! Ви не спите, а чекаєте, доки СУБД виконає запит? Тож її треба розігнати. Збережені процедури MySQL застосовували? Чи не знаєте як? Ну, тоді прокидайтеся, бо зараз ми розглядатимемо саме цю тему.

Що за процедури ще?

Якщо у вас фобія щодо медичних процедур, то ці структури «не з тієї теми». Тож можна не боятися. А якщо серйозно, то процедури, що зберігаються – це зручна і корисна для «здоров'я» СУБД річ. Їх ще називають «збереженими функціями MySQL», але це не зовсім точне визначення. Хоча давайте розбиратися з усім по порядку.

Збережені процедури дозволяють значно оптимізувати роботу сервера та підвищити швидкість його роботи, оскільки їхній код після першого виконання зберігається в кеші оперативної пам'яті. При всіх наступних дзвінках процедура витягуватиметься з кешу, а не відправлятиметься знову на виконання.

У MySQL виклик процедури, що зберігається, означає, що вписані в її код запити опрацюються лише наполовину. І те, якщо буде змінено значення їхніх параметрів. Але не все так ідеально. Опишемо спочатку позитивні сторони використання процедур:

Інкапсуляція функціональності весь код зберігається в одному місці, що полегшує доступ до нього для інших додатків. У цьому процедури, що зберігаються, схожі з програмними функціями.

Ізоляція доступу до даних – всі користувачі отримують доступ не до рядків таблиць, а лише до процедур, що зберігаються. Що, у свою чергу, підвищує рівень захищеності всіх даних.

Підвищення швидкості роботи сервера – за рахунок кешування та поєднання запитів.

У MySQL процедури, що зберігаються в теорії, є структурами, що належать до більш «високих матерій» — програмування СУБД. Так що ми з вами (як майстри) хоч потихеньку, але . Але повернемося до процедур і опишемо негативні сторони їх використання:

Навантаження на сервер БД підвищується – більшість коду процедур виконується за сервера. Дана СУБДпобудована за моделлю «клієнт-сервер», в якій задіяні кілька пристроїв.

Ускладнюється маніпуляція даними – розробки додатків доведеться прописувати частину коду і клієнтській стороні.

Ускладнюється процес перенесення БД інші рейки (СУБД).

Процедури в phpMyAdmin

Для початку розглянемо застосування в MySQL процедур, що зберігаються на прикладі phpMyAdmin. Таким чином, нам буде легше розібратися з цим типом структур. Стартуємо!

Запускаємо програмну оболонку, праворуч вибираємо тестову базу даних. У мене є база world. Потім у головному меню зверху переходимо по вкладці "Процедури". Тут тиснемо на «Додати процедуру».

Після цього з'явиться вікно діалогу "Додати процедуру". Заповнюємо у ній усі вказані на знімку поля. Вказуємо ім'я процедури, тип. У графі "Визначення" вводимо обліковий запискористувача, і в коментарях (необов'язково) вказуємо для себе, що це лише приклад процедури, що зберігається.

Вже на цьому етапі ми знайомимося з особливостями синтаксису створення збережених процедур MySQL. У полі "Визначення" прописуємо тіло структури. Зверніть увагу, що запит, що виконується між ключовими словами BEGIN та END:

BEGIN SELECT "HELLO, WORD!"; END

BEGIN

SELECT "HELLO, WORD!" ;

Цей запит не виконує жодних дій з базою, лише виводить напис. Це ми вказали у полі «Доступ до SQL даними».

Для закінчення створення нашої першої процедури тиснемо внизу "Ок". Після цього програма виводить «зелене» повідомлення про успішне виконання запиту. Його код наведено нижче. У MySQL процедури і функції, що зберігаються, створюються за допомогою спеціальної команди CREATE PROCEDURE. Але про це згодом.

Тепер запустимо створену структуру виконання. Для цього в розділі "Процедури" натискаємо посилання "Виконати". Але що це за неподобство! Куди подівся наш улюблений «зелененький»? Чому програма «лається» та «кричить», що їй не вистачає виділеної пам'яті?

Куди дивився автор цієї публікації ...! Вибачте, трохи заплутався. Адже автор – це я. Спокій, зараз все виправимо! Така помилка виникає через те, що в головному файлі конфігурації значення параметра thread_stack залишено без змін. За промовчанням для кожного потоку виділяється 128 Kb. Виділеного ліміту оперативної пам'яті для виконання простих запитівцілком вистачає, але процедур мало.

Це ще раз доводить, що на виконання тригерів і збережених процедур MySQL витрачається більше ресурсів.

Перейдіть до конфігураційного файлу my.ini і збільште ліміт оперативної пам'яті, встановленої для кожного потоку, до 256 kb. Тепер ще раз запустіть на виконання створену процедуру. На цей раз все пройшло як треба, і програма повернула результат без помилки.

Зверніть увагу, що для виклику використовується команда CALL із зазначенням імені процедури та параметрів, що приймаються (у дужках).

Більш складний приклад

Але все ж phpMyAdmin за своїми можливостями більше підходить для швидкого складання процедур. А для розробки в MySQL процедури, що зберігається, з динамічною кількістю аргументів (наприклад) буде потрібно більш зручне ПЗ. Чому:

phpMyAdmin не хоче нормально "розуміти" процедури, створені не через спеціальний конструктор.

Програма не виконує структури, запущені під root та порожнім паролем, а в Денвері створити нового користувача і зайти під ним у phpMyAdmin ціла проблема.

Якщо ви уважно стежите за моїми публікаціями і виконуєте всі прописані в них побажання, то у вас вже повинен стояти MySQL Administrator. У прив'язку до нього залишилося завантажити MySQL Query Browser за цим посиланням. Ці дві програми краще використовувати разом: у першій створювати процедури, а в другій тестувати їх. Поїхали:

Зліва нагорі переходимо через вкладку «Catalog».

Вибираємо потрібну БД, і у верхньому меню тиснемо на "Stored procedures", а внизу на "Create Stored Proc"

У вікні редактора вводимо код процедури і тиснемо «Execute SQL».

CREATE DEFINER=`roman`@`localhost` PROCEDURE `proc5`() BEGIN declare a int; set a="SELECT COUNT(*) FROM city as a"; if(a > 1000)THEN SELECT "<1000"; ELSE SELECT ">1000"; END IF; END

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

BEGIN

declare a int;

set a = "SELECT COUNT(*) FROM city as a";

if (a > 1000) THEN

SELECT "<1000" ;

ELSE

SELECT ">1000";

END IF;

Ви, мабуть, помітили, що ми не передаємо жодних значень усередину процедури. Крім цього MySQL може бути невідома кількість параметрів в збереженій процедурі, які потім можна передати через нові оголошення змінних, розташовані всередині циклів.

Для запуску процедури заходимо до MySQL Query Browser. Спочатку вводимо свій облік і пароль, а потім ліворуч в Object Explorer знаходимо папку з потрібною базою. Решта черговості дії показана на наступному знімку.

Запуск процедури у PHP

Тепер розглянемо, як відбувається в PHP виклик процедури MySQL, що зберігається. Для цього нам доведеться трохи перекроїти код нашого попереднього прикладу. Ми додамо в процедуру параметр виведення, а також змінимо код запиту:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(out col decimal) BEGIN SELECT COUNT(*) в col FROM city; END

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

BEGIN

SELECT COUNT (*) в col FROM city;

Для виклику з файлу PHPпроцедури та виведення результату будемо використовувати можливості класу PDOStatement, створеного спеціально для роботи з БД через SQL

Цей клас реалізований порівняно недавно і підтримується PHP, починаючи з версії 5.1.0. Раджу перед використанням перевірити версію мови за допомогою вбудованої функції phpversion().