В предната статия се натъкнахме на често срещан проблем. Имахме да извършим множество подобни операции за всички обекти от дадена таблица. Възползвахме се от възможността да ползваме цикъл, но как да итерираме през оригиналната таблица, използвайки броят на редовете и за край на цикъла и итератор, движещ се по id. Има възможност много от записите в тази таблица да са вече изтрити. Съответно и id-тата им също са били изтрити. Ще имаме много празни итерации. Друг вариант е да правим селект от таблицата и накрая на заявката да ограничаваме до LIMIT 1 OFFSET iterator и на всяка итерация да инкрементираме iterator. Това е добра стратегия, но не е съвсем традиционна. Има специални обекти, чрез които можем да итерираме през всякакви селект заявки, дори и през многотаблични заявки.  Наричат се курсори.

Курсорите дават възможност да итерираме върху множество от редове, върнати в резултат от SELECT заявка и да обработваме всеки един ред по отделно. Курсорите в MySQL са: read-only, non-scrollable, asensitive.

• Read-only: Използват се само за четене на ред от селект заявка. Чрез тях не можете да модифицирате информация от таблиците, от които тя се взима.
• Non-scrollable: Курсорите не позволяват прескачане или пропускане на редове. Единствената възможност, която предлагат, е последователно изваждане на следващ ред от резултата от SELECT заявката, за която са създадени.
• Asensitive: asensitive и insensitive курсорите се различават по актуалността на данните, от които четат. Sensitive- курсорите четат информация директно от оригиналните таблици, участващи в SELECT заявките, а insensitive – курсорите четат информация от временно копие на тези таблици.Asensitive са по-бързи, тъй като не отнемат време от създаване на временно копие на оригиналните данни, но пък ако някой модифицира данните в същото време, то курсорът ще чете модифицираните данни.  Някои СУБД предлагат и двата вида курсори. MySQL-курсорът е asensitive. Можете да ги използвате в процедури, в тригери и във функции.

Синтаксис:

DECLARE <име на курсора> CURSOR  FOR<заявка от тип SELECT>;

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

OPEN  <име на курсора>;

Преди курсора е редно да сте си декларирали променливи, в които да записвате временните стойности на всяка колона от селект заявката на курсора. След това, когато решите да изваждате редове от курсора и стойността на всяка колона да записвате в променлива, трябва да използвате команда:

FETCH <име на курсора> INTO <списък с променливи>;

Извършвате нужните действия с променливите и накрая го затваряте чрез:

CLOSE <име на курсора>;

Възможни грешки и начин за прихващане и обработка:

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

DECLARE CONTINUE HANDLER FOR NOT FOUND  SET finished = 1;

С променливата finished означаваме, че курсорът е достигнал края, а самата декларация на handler-а трябва да бъде точно след декларацията на променливите и на курсора, за да важи от там надолу.

Нека да разгледаме следния пример. Ще направим курсор, който взима всички треньори с ненулева месечна заплата и ги разпечатва един по един на екрана. Без да ползва временна таблица, на всяка итерация на while цикъла, ще се измъква по един ред от курсова и ще се присвоява на две временни променливи. Когато в курсора няма нищо, то се генерира грешка NOT FOUND и handler-а, дефиниран по-горе променя стойността на finished на 1. Проверка дали finished не е станало 1 се прави вътре в цикъла, защото за да се генерира грешка поради край на курсор, то ние трябва да сме вече в цикъла. Ако това е така, то напускаме текущия цикъл с команда LEAVE, последвана от името на етикета, което сме избрали. След като излезем от цикъла затваряме отворения курсор.

 

#курсори
use school_sport_clubs;
drop procedure if exists  CursorTest;
delimiter |
create procedure CursorTest()
begin
declare finished int;
declare tempName varchar(100);
declare tempEgn varchar(10);
declare coachCursor CURSOR for
SELECT name, egn
from coaches
where month_salary is not null;
declare continue handler FOR NOT FOUND set finished = 1;
set finished = 0;
OPEN coachCursor;
coach_loop: while( finished = 0)
DO
FETCH coachCursor INTO tempName,tempEgn;
       IF(finished = 1)
       THEN 
       LEAVE coach_loop;
       END IF;	
       SELECT tempName,tempEgn; # or do something with these variables...
end while;
CLOSE coachCursor;
SET finished = 0;
SELECT 'Finished!';
end;
|
delimiter |

Можете да извикате процедурата и ще видите, че тя ги разпечатва един по един.

Нека да преработим втората от двете процедури в предната статия – тази, която смята хонорарите и ги добавя към месечните заплати на треньорите:

 

use school_sport_clubs;

drop procedure if exists OPTIMIZED_monthHonorariumPayment;

delimiter |
create procedure OPTIMIZED_monthHonorariumPayment(IN monthOfPayment INT, in yearOFpayment INT)
procLabel: begin
declare countOfCoaches int;
declare iterator int;
declare countOfRowsBeforeUpdate int;
declare countOfRowsAfterUpdate int;
declare finished int;
declare tempCoachId int;
declare tempSumOfHours int;

 DECLARE tempCoachCursor CURSOR FOR
    SELECT  coach_id, SUM(number_of_hours)
	FROM coach_work
	where month(coach_work.date) = monthOfPayment
	AND YEAR(coach_work.date) = yearOFpayment
	AND isPayed = 0
	GROUP BY coach_work.coach_id;
    
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'SQL Exception';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

create temporary table tempTbl(
id int auto_increment primary key,
coach_id int,
number_of_hours int,
pay_for_hour decimal,
amount decimal,
paymentMonth int,
paymentYear int
)Engine = Memory;
	
	#Плащане на редовна месечна заплата:
    SET @RESULT =0;
    call monthSalaryPayment(monthOfPayment, yearOFpayment, @RESULT);
    SELECT @RESULT as resultFromMonhtPayment; #only for control and test
    
   	SELECT COUNT(*)
    INTO countOfRowsBeforeUpdate
    FROM coach_work
    where month(coach_work.date) = monthOfPayment
    AND YEAR(coach_work.date) = yearOFpayment
    AND isPayed = 0;
    
    START TRANSACTION;
    OPEN tempCoachCursor;
    set finished = 0;
while_loop_label: WHILE(finished = 0)
	DO
	FETCH tempCoachCursor INTO  tempCoachId, tempSumOfHours;
		
   IF(finished = 1)
      THEN leave while_loop_label;
      ELSE
	SELECT  tempCoachId, tempSumOfHours;
	INSERT INTO tempTbl(coach_id, number_of_hours, pay_for_hour, amount, paymentMonth, paymentYear)
	SELECT tempCoachId, tempSumOfHours, c.hour_salary, tempSumOfHours*c.hour_salary, monthOfPayment, yearOFpayment
        FROM coaches as c
	WHERE c.id = tempCoachId;
	END IF;
	END WHILE;
    CLOSE tempCoachCursor;
    
    SELECT * FROM tempTbl;#only for control and test
	INSERT INTO salarypayments(`coach_id`, `month`,`year`,`salaryAmount`,`dateOfPayment`)
        SELECT coach_id, paymentMonth, paymentYear, amount, NOW()
	FROM tempTbl
        ON DUPLICATE KEY UPDATE 
        salaryAmount = salaryAmount + amount,
        dateOfPayment = NOW();

	UPDATE coach_work
	SET isPayed = 1
	WHERE month(coach_work.date) = monthOfPayment
	AND YEAR(coach_work.date) = yearOFpayment
	AND isPayed = 0;
	SELECT  ROW_COUNT() INTO countOfRowsAfterUpdate;
    SELECT countOfRowsAfterUpdate as countOfRowsAfterUpdate; #only for control and test
    SELECT countOfRowsBeforeUpdate as countOfRowsBeforeUpdate;#only for control and test
	  IF(countOfRowsBeforeUpdate = countOfRowsAfterUpdate)
      THEN 
		commit;
      ELSE
		rollback;
      END IF;
	drop table tempTbl;
END;
|
delimiter ;

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

Даниел Джолев