MySQL збережені процедури (исходники), Різне, Програмування, статті

Довго мучився з цим питанням. Літератури в інтернеті мало, особливо російською мовою. Довелося попитати на закордонних форумах, глибше покопатися в мануали і роз’яснити для себе деякі незрозумілі моменти. Отже, коротко про збережених процедурах в MySQL.


Stored procedures – що це?


Збережені процедури з’явилися починаючи з 5 версії MySQL. Вони дозволяють автоматизувати складні процеси на рівні MySQL, ніж використовувати для цього зовнішні скрипти. Це дає нам найбільш високу швидкість виконання, тому що ми не ганяємо велику кількість запитів, а всього лише один раз викликаємо ту чи іншу процедуру (або функцію).


Що для цього потрібно? Встановіть MySQL сервер версії 5 або вище (dev.mysql.com/downloads). Процедури можна створювати як запити, наприклад через командний рядок MySQL, але для зручності раджу скачати MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Даний пакет включає в себе три програми – MySQL Administrator, MySQL Query Browser і MySQL Migration Toolkit. Нам знадобляться перші дві. (Хоча можна обійтися одним MySQL Query Browser, але всі ці $ $ В збережених процедурах іноді можуть збити з пантелику).


Перша збережена процедура


Отже, відкриваємо MySQL Administrator, підключаємося до сервера MySQL і створюємо нову схему (базу даних): клацніть Catalogs, виберіть Create New Schema в області Schemata (Ctrl + N). Назвіть її як-небудь (наприклад db). Відкрийте щойно створену схему, виберіть вкладку Stored procedures і клацніть кнопку Create Stored Proc. Назвіть свою процедуру procedure1. У тіло процедури (між BEGIN і END) впишіть наступне:


SELECT “This is my stored procedure”;

І натисніть Execute SQL – процедура створена. Відкрийте MySQL Query Browser, виберіть свою схему (db) і впишіть наступний запит:


CALL procedure1();

Вуала! Вітаю.


Змінні в MySQL


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


Прості змінні


DECLARE iVar INT DEFAULT 0;SET iVar = 5;SELECT * FROM `data` WHERE `id` = iVar;

DECLARE iVar INT DEFAULT 0;SELECT COUNT(*) INTO iVar FROM `data`;

Системні змінні


SET @iVar = 5;SELECT @iVar;

Різниця між простими і системними змінними в тому, що системні змінні доступні з поза збереженої процедури. Тобто, щоб отримати якісь дані потрібно користуватися системними, а змінні які потрібні тільки всередині процедури повинні бути простими.


Параметри у збережених процедурах


Тут теж все досить просто. Змінюємо перший рядок, що оголошує саму процедуру:


CREATE PROCEDURE `procedure1`(IN iInput1 INT, IN iInput2 INT)

Тут, ключове слово IN вказує на те, що параметр вхідний. Далі з цим параметром працюємо як з звичайної змінної всередині процедури:


SELECT * FROM `data` WHERE `id` = iInput1 AND `id2` = iInput2;

Умови, Цикли. IF THEN ELSE, WHILE


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


IF умова THEN
дію;
ELSE
дію;
END IF;

WHILE умова DO
дію;
END WHILE;

Простий приклад


Один з хороших випадків застосування процедур – тоді, коли вам потрібно об’єднати декілька запитів в один, наприклад додавання теми в форум і збільшення загальної кількості тем. Припустимо таблиця threads


 CREATE TABLE `threads` ( `id` INT NOT NULL AUTO_INCREMENT , `title` VARCHAR(255) NOT NULL, `tag` VARCHAR(255) NOT NULL, PRIMARY KEY ( `id` ) ) ENGINE = MYISAM;

Тут title у нас буде заголовком нової теми. Ну і таблиця, наприклад з різними статистичними змінними сайту, в тому числі загальна кількість тем у формі.


 CREATE TABLE `variables` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(255) NOT NULL, `value` INT NOT NULL DEFAULT 0, PRIMARY KEY ( `id` ) ) ENGINE = MYISAM;

Тут начебто все зрозуміло, припустимо у нас там є запис з name = threads і value = 0. Створимо нову процедуру, що зберігається procedure2.






1
2
3
4
5
CREATE PROCEDURE `procedure2`(IN sTitle VARCHAR(255))BEGIN
INSERT INTO `threads` (`title`) VALUES (sTitle);
UPDATE `variables` SET `value` = `value` + 1 WHERE `name` = `threads`;
END

Пояснювати особливо нічого, просто два запити об’єднали в один. Тепер ми можемо викликати цю процедуру таким чином:


CALL procedure2(`My new thread`);

Таким чином, замість того, щоб передати два або більше запитів (наприклад через php), ми можемо передати один – оптимізація, чистий код і можна змінити в будь-який момент не зачіпаючи інші скрипти.


Курсори (MySQL Cursors)


Курсори дозволяють пройтися по всім отриманим результатам запиту. На теорії пояснити складно, покажу на практиці. Додамо ще одну таблицю до нашої бази даних – hits:


 CREATE TABLE `tags` ( `id` INT NOT NULL AUTO_INCREMENT , `tag` VARCHAR(255) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM

Сюди ми будемо записувати все теги з усіх тем. Процедура буде виглядати приблизно так:






1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE PROCEDURE `procedure3`()BEGIN
DECLARE done INT DEFAULT 0;
DECLARE sTag VARCHAR(255);
DECLARE iCount INT DEFAULT 0;
 
DECLARE rCursor CURSOR FOR SELECT `tag` FROM `threads` WHERE 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE `02000` SET done=1;
 
OPEN rCursor;
FETCH rCursor INTO sTag;
 
WHILE done = 0 DO
SELECT COUNT(*) INTO iCount FROM `tags` WHERE `tag` = sTag;
IF iCount = 0 THEN
INSERT INTO `tags` (`tag`) VALUES (sTag);
END IF;
 
FETCH rCursor INTO sTag;
END WHILE;
 
CLOSE rCursor;
END

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


Курсор для запиту SELECT, який вибере теги з усіх тем (WHERE 1). Після курсора оголошуємо щось на зразок виключення – що робити, коли результати закінчаться (SQLSTATE “02000 ‘означає це закінчення). В цьому випадку ми в змінну done запишемо 1, щоб надалі вийти з циклу.


Відкриваємо курсор, і отримуємо перший запис. Далі в циклі – Вибираємо кількість збігів з таблиці тегів для поточного тега і поміщаємо результат в змінну iCount. Якщо результатів немає, то запитом INSERT вставляємо новий тег.


Зрештою закриваємо курсор і виходимо з процедури. Ну от і все.


Витяг даних


Згадаймо системні змінні і розглянемо ще одну маніпуляцію над нашими таблицями – отримати загальну кількість тегів і тем. Перейдемо відразу до процедури:






1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE `procedure4`()BEGIN
DECLARE iTags INT DEFAULT 0;
DECLARE iThreads INT DEFAULT 0;
 
SELECT COUNT(*) INTO iTags FROM `tags`;
SELECT COUNT(*) INTO iThreads FROM `threads`;
 
SET @tags = iTags, @threads = iThreads;
END

Оголошуємо дві змінних – iTags – кількість тегів, і iThreads – загальна кількість тем.


Далі два прості запиту на вибірку, заповнюючи наші змінні. Ну і наприкінці присвоюємо системним змінним значення поточних простих змінних. При виклику дана процедура нічого не повертає, але після її виклику ми можемо вважати необхідні значення з системних змінних:


CALL procedure4();SELECT @tags, @threads;

 

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*