C, PHP, VB, .NET

Курсорите не съдържат нищо по-особено от обикновена резултатна таблица (т.е. result set) от SELECT заявка. Това, което ги прави по-специални, е че MySQL ни позволява да обхождаме тази резултатна таблица с цикли с помощта на итератор. Може да си представите курсора като референция (или указател) към пореден ред от дадена таблица.

Използването на курсори е доста неефективно и най-често се оказва, че ако правите нещо чрез курсор, то най-вероятно е имало много по-добър начин за постигане на същата цел. SQL е език, който се базира на операции с множества, а курсорите са начин за прилагане на процедурно програмиране в тях – в този смисъл те не са естествения начин за обработка на данните. Приема се, че ако можете да направите нещо с обикновена заявка, то ще бъде по-ефективно, отколкото ако го правите с курсор (аз лично не съм виждал контрапример досега). И все пак хипотетично има ситуации, когато не може да решите задачата с обикновена заявка. Например един тип задачи, които типично се решават чрез курсори, е когато имате таблица и трябва използвайки информацията от всеки ред от нея поотделно да направите редица различни типове промени (комбинация от INSERT, UPDATE и DELETE заявки в трансакция) по други таблици. Но честно казано дори в тези случаи програмистите предпочитат да вземат таблицата във вид на двумерен масив в любимия си език за програмиране (напр. Java) и там да обходят този масив чрез цикли, вместо да използват процедури с курсори. Така, че не е ненормално дори опитни администратори на бази от данни, които са с дългогодишен опит, да не са се сблъсквали никога с курсори през цялата си работна практика.

И все пак ще научим какво е курсор. Преди да започнем с пример, трябва да кажем следните важни неща за курсорите конкретно в MySQL.

• Те могат само да четат (read only) данните от таблицата, но не могат да ги променят;
• MySQL използва само т.нар.“asensitive“ курсори. Това означава, че трябва да приемете, че се работи директно с оригиналните данни, а не с техни копия (както биха правили евентуалните „insensitive“ курсори). Казваме, че „трябва да приемете“, но не е задължително да е така (сървърът по свое усмотрение може все пак да си направи копие на данните и да работи с него). В този смисъл asensitive курсорите могат в определени (независещи от вас) случаи да работят като insensitive, но обратното не е възможно. По принцип работата директно с оригиналните данни е по-бърза, отколкото ако трябва да правим копие и след това да работим с него. За сметка на това промяна върху оригиналните данни би направила сериозен проблем от гледна точка на заключване на данните. Това е и причината курсорите в MySQL да са само за четене – иначе трябваше да изчакват LOCKS от други заявки, а освен това и самите те биха били принудени да заключват всичко, до което се докоснат;
• Курсорите в MySQL са „non scrollable“ – това означава, че можете да обхождате таблицата само в реда, в който я е подредила SELECT заявката и освен това не може да пропускате редове при обхождането. Тоест ако си представите курсора като един итератор, вие имате само и единствено достъп до функцията му „next()“ и нищо повече – няма прескачане и няма връщане назад.

Тук е моментът да отбележим, че подходът описан по-горе за приемане на таблицата като двумерен масив в програма и обхождането ѝ с цикли е всъщност подход за реализиране на insensitive курсор, който е scrollable (и не е read-only, но променяте копието, а не оригинала на данните). Тоест курсорите в MySQL са по същество различни от тяхната класическа алтернатива. И съществуват, защото са по-бързи от нея! Ако се налага да обхождате резултатна таблица и да извършвате действия за всеки нейн ред, то ще бъде по-бързо ако го реализирате с курсор, отколкото ако го реализирате чрез предаване на резултатната таблица към външна програма и последващо обхождане с на получения масив с цикъл.

Курсорите могат да се използват само в съхранени процедури. Декларацията на курсора се прави в началото на процедурата (също като локалните променливи). Нека преди да дадем пример, да си създадем една тестова база от данни. Имаме таблица с потребители с потребителско име, парола и някакво ниво на достъп (level 0, 1 или 2):

CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  user VARCHAR(64) NOT NULL UNIQUE,
  pass VARCHAR(64) NOT NULL,
  level ENUM("0", "1", "2") NOT NULL
);

INSERT INTO users (user, pass, level) VALUES
("Ivan", "123456", "1"), ("petar", "password", "2"),
("Maria", "aaaaaa", "1"), ("Georgi", "qwerty", "3"),
("Pencho", "gfsdjgh", "2"), ("Tosho", "ZzzZZz", "3");

Имаме съхранена процедура „UserAction“, която по подаден параметър идентификатор на потребител извършва някакво специално действие за този потребител. За простота (за да бъде примера ни елементарен) нашата процедура няма да прави нищо.

CREATE PROCEDURE UserAction(IN var_id SERIAL)
BEGIN
END;

Искаме да създадем процедура, която намира потребителите от level 2 и 3, след което за всеки един от тях поотделно извиква процедурата UserAction. Ще реализираме тази функционалност чрез курсор:

DELIMITER //
CREATE PROCEDURE CursorExample()
BEGIN
  # Чрез тази променлива ще проверяваме
  # дали сме изчерпали всички редове от курсора
  DECLARE no_more_rows BIT DEFAULT 0;
  # В тази променлива ще взимаме отделните id-та
  DECLARE var_id SERIAL;
  # Това е нашият курсор
  DECLARE user_id_level_2_and_3_cursor CURSOR FOR
  SELECT id FROM users WHERE level IN ("2","3");
  # Чрез "Not found handler" казваме какво да стане
  # ако редовете на курсора свършат
  DECLARE CONTINUE HANDLER 
  FOR NOT FOUND SET no_more_rows = 1;
  # Отваряме курсора
  OPEN user_id_level_2_and_3_cursor;
  # Обхождаме го чрез цикъл
  our_loop: LOOP
    # Взимаме пореден ред от курсора и го записваме
    # във var_id. Ако имаме повече от една колона
    # трябва да запишем в повече от една променлива
    FETCH user_id_level_2_and_3_cursor INTO var_id;
    # Ако сме преминали края, излизаме от цикъла
    IF (no_more_rows = 1) THEN LEAVE our_loop;
    END IF;
    # В противен случай изпълняваме задачата
    CALL UserAction(var_id);
  END LOOP our_loop;
  # Затваряме курсора
  CLOSE user_id_level_2_and_3_cursor;
END//
DELIMITER ;

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

• INSERT-SELECT заявките – ако програмистът не знае този синтаксис, той се опитва да използва INSERT INTO…VALUES… заявки. Съответно вместо да подаде цялата SELECT заявка вместо VALUES, той я обхожда ред по ред и прави INSERT заявки една по една за всеки ред. Това е много по-бавно, отколкото INSERT-SELECT;
• UPDATE или DELETE заявки с JOIN – някои предпочитат (или не знаят за JOIN в UPDATE/DELETE) да вземат данните (от евентуалното WHERE условие) ред по ред и за всеки един от тях да изпълнят трансакция с поредица от UPDATE или DELETE заявки (които реално могат да се обединят в една заявка с JOIN условие).

Използвайте курсори само в краен случай!