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

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

Деклариране на HANDLER:

Използвайте следния синтаксис:

DECLARE action HANDLER FOR condit_value statement;

Ако се появи грешка, която да е от тип condit_value,  то се изпълнява statement, и или се продължава, или се прекратява изпълнението на процедурата (в зависимост от стойността на action). Нека да разгледаме какви стойности може да поставяте на местата на action, condit_value и statement. За action възможностите са две:

• CONTINUE – Изпълнението на блока, в който се намираме (код, заключен между begin и end), продължава.
• EXIT– Изпълнението на текущия блок, в който Handler-ът  е деклариран се прекратява.

За condition_value:

• MySQL код за грешка – това са обикновено кодове, за най-често срещаните грешки в езика. Например 1051 е код при неоткрита таблица.
• SQLSTATE стойност: 5 символен низ, който показва определена грешка. Например ’42S01′ отново е за неоткрита таблица.  Синтактично това изглежда така:

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S01' SELECT 'SQLSTATE 42S01 occured';

• SQLWARNING, NOTFOUND, SQLEXCEPTION. Това са стандартни съобщения или предупреждения за грешки, които заявките връщат след изпълнението си. NOTFOUND се използва, когато правим SELECT INTO или, когато курсор достигне до края на сет-а, който обходжда. Синтактично това изглежда така:

DECLARE CONTINUE HANDLER FOR NOTFOUND SELECT 'NOT FOUND exception occured';

В процедурите по-долу са дадени и по-конкретни примери.

 

Добре е да може да прекъсвате даден програмен блок от код в определна ситуация или при възникване на конкретно условие. Това се случва като означите вашия блок от код с label (етикет). Дава се произволно име на този label и се поставя знак “:” Всичко след него до END label се разглежда като един програмен блок. В един блок може да вграждате и други блокове. Ако не поставите END, то се подразбира, че блокът важи до края на процедурата. Ако искате да напуснете даден блок, използвате LEAVE <label_name>;

Например:

 use school_sport_clubs;
 drop procedure testProc;
 delimiter $$
 CREATE PROCEDURE  testProc(IN param int)
  out_block: 
  BEGIN
   DECLARE res int;
   SET res = param;
       inner_block: 
         BEGIN
           IF (res = 1) 
	   THEN
	   LEAVE inner_block;
	   END IF;
       SELECT 'This will be excuted only if param is 0';
       END inner_block;
  SELECT 'End of program';
  END out_block;
  $$	
  delimiter ;

Извикайте я с 0 или с 1 и ще видите резултата.

 

 INSERT…ON DUPLICATE KEY UPDATE:

Когато имаме създаден ключ в някаква таблица и искаме да въведем входни данни, но за този ключ вече такива са въведени, а ние не искаме да изтрием старите и да въведем нови на тяхно място, то може да използваме  INSERT…ON DUPLICATE KEY UPDATE.  Например. Не знаем дали са преведени някакви пари на даден трньор. Правим опит да въведем данни salarypayments, използвайки уникалния ключ, създаен при създаването на таблицата UNIQUE KEY(`coach_id`,`month`,`year`). Тоест опитваме се на един треньор да преведем още пари за даден месец от дадена година. Ако за него вече съществува такъв запис, поради наличието на уникалния ключ, една такава insert заявка не би минала. Но затова може да изпозлваме on duplicate key update:

INSERT INTO salarypayments(`coach_id`, `month`, `year`, `salaryAmount`, `dateOfPayment`) 
VALUES (1, 1,2016, 1800, NOW())
ON duplicate key update
salaryAmount = salaryAmount + 1800,
dateOfPayment = NOW();

Ако ключът (1,1,2016) не присъства в таблицата, то заявката си се държи като обикновена INSERT заявка, а ако го има, то тя се държи като UPDATE заявка.

Ще решим следната задача: В таблицата coaches добавяме колона, която показва колко е фиксираната месечна заплата на всеки треньор. Също така ще добавим и колона, в която ще записваме колко е хонорара на час за дадения треньор.  Създаваме още една таблица coach_work. В нея всеки треньор ще записва часовете, които направи. По-рано създадохме и таблица, в която планирахме да запазваме плащанията на треньорите. Сега идва моментът да я ползваме. Трябва да реализираме  следната постановка: един треньор взима месечна заплата, но и допълнително пари от хонорари за всеки час. За целта първо ще направим процедура, която да “плаща” на треньорите всички техни хонорувани часове, но преди да бъдат платени те, ще извикаме процедура, която да им “плати” основните заплати.

script

Първо правим процедурата, която да “плаща” основните месечни заплати:

use school_sport_clubs;
drop procedure if exists monthSalaryPayment;
delimiter |
create procedure monthSalaryPayment(IN monthOfPayment INT, in yearOFpayment INT, OUT success bit)
procLab: begin
    declare tempCoachSalary decimal;
    declare currentcoachSalary decimal;
    declare iterator int;
    declare countOfCoaches int;
    declare tempCoachId int;
    declare updatedSalaryCounter int;
    declare countOfCoachesWithoutMonthSalary int;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'SQLException occured';

create temporary table tempCoach(
id int auto_increment primary key,
coach_id int not null,
month_salary int not null
)Engine = Memory;

INSERT INTO tempCoach(coach_id,month_salary)
    SELECT coaches.id, coaches.month_salary
    FROM coaches
    WHERE month_salary IS NOT NULL ;
    
    SELECT COUNT(*)
    INTO countOfCoaches
    FROM tempCoach;

    SET countOfCoachesWithoutMonthSalary = 0;
    set iterator = 1;
    WHILE(iterator >= 1 AND iterator <= countOfCoaches) # преброяваме колко са треньорите, които не са си полчуили заплатата все още.
	DO
	SELECT coach_id, month_salary
        INTO tempCoachId, tempCoachSalary
        FROM tempCoach
        where id = iterator;
        
        SELECT salaryAmount
        INTO  currentCoachSalary #има ли заплата вече преведена за този месец и тази година този треньор
        FROM salarypayments
        WHERE `month` = monthOfPayment
        AND `year` = yearOFpayment
        AND `coach_id` = tempCoachId;
        
    IF(IFNULL(currentCoachSalary,0) <= tempCoachSalary) 
THEN 
    SET countOfCoachesWithoutMonthSalary = countOfCoachesWithoutMonthSalary +1; 
 END IF; 
 SET iterator = iterator +1; 
 end while; 
IF(countOfCoachesWithoutMonthSalary = 0) # ако няма треньори, които си чакат превод на заплатата 
THEN 
   drop table tempCoach; 
   set success = 0; 
LEAVE procLab;  
 ELSE set iterator = 1;  
 SET updatedSalaryCounter = 0;  
 START TRANSACTION; 
 WHILE(iterator >= 1 AND iterator <= countOfCoaches)
	DO
	SELECT coach_id, month_salary
        INTO tempCoachId, tempCoachSalary
        FROM tempCoach
        where id = iterator;
        
        SELECT salaryAmount
        INTO  currentCoachSalary #има ли заплата вече преведена за този месец и тази година този треньор
        FROM salarypayments
        WHERE `month` = monthOfPayment
        AND `year` = yearOFpayment
        AND `coach_id` = tempCoachId;
        
        
	IF(IFNULL(currentCoachSalary,0) < tempCoachSalary)
	THEN
        INSERT INTO salarypayments(`coach_id`,`month`,`year`,`salaryAmount`,`dateOfPayment`) 
VALUES (tempCoachId, monthOfPayment,yearOFpayment, tempCoachSalary, NOW())
        ON duplicate key update   #ако вече хонорарите му са преведени, то към тези пари да се добави и месечната му заплата.
        salaryAmount = salaryAmount + tempCoachSalary,
        dateOfPayment = NOW();
        set updatedSalaryCounter = updatedSalaryCounter + 1;
   ELSE
        SELECT 'The coach has a month salary yet!';
   END IF;    
        SET iterator = iterator +1;
  end while;
	IF(countOfCoachesWithoutMonthSalary = updatedSalaryCounter) # преведени са толкова заплати, колкото е трябвало
	THEN 
          commit;
	  SET success = 1;
          drop table tempCoach;
    ELSE
	rollback;
	SET success = 0;
        drop table tempCoach;
     END if;   
END IF;
end
|
DELIMITER ;

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

use school_sport_clubs;

drop procedure if exists monthHonorariumPayment;

delimiter |
create procedure monthHonorariumPayment(IN monthOfPayment INT, in yearOFpayment INT)
procLabel: begin
declare countOfCoaches int;
declare iterator int;
declare countOfRowsBeforeUpdate int;
declare countOfRowsAfterUpdate int;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'SQLException occured';


create temporary table tempCoaches(
id int auto_increment primary key,
coach_id int not null,
monthHourSum int
)Engine = Memory;


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;
	SELECt @RESULT; 
    call monthSalaryPayment(monthOfPayment,yearOFpayment,@RESULT);
    
   	SELECT COUNT(*)
    INTO countOfRowsBeforeUpdate
    FROM coach_work
	where month(coach_work.date) = monthOfPayment
	AND YEAR(coach_work.date) = yearOFpayment
    AND isPayed = 0;

INSERT INTO tempCoaches(coach_id, monthHourSum)  #записваме всички треньори и сумата от техните часове в табличка за дадения месец на дадената година
	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;

	set iterator = 1;
	SELECT COUNT(*)
	INTO countOfCoaches
	FROM tempCoaches;

IF(ifnull(countOfCoaches,0) = 0) #Няма треньори, за които да се извършва плащане
	THEN SELECT 'No coaches and hours for this payment period';
		drop table tempTbl;
		drop table tempCoaches;
		LEAVE procLabel;
ELSE
	START TRANSACTION;
	WHILE(iterator >= 1 AND iterator <= countOfCoaches)
	DO
	INSERT INTO tempTbl(coach_id, number_of_hours, pay_for_hour, amount, paymentMonth,paymentYear)
	SELECT tc.coach_id, tc.monthHourSum, c.hour_salary, tc.monthHourSum*c.hour_salary, monthOfPayment, yearOFpayment
        FROM tempCoaches as tc JOIN coaches as c
	ON tc.coach_id = c.id
	WHERE tc.id = iterator;
	SET iterator = iterator + 1;

	END WHILE;
	
	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;
    
       IF(countOfRowsBeforeUpdate = countOfRowsAfterUpdate)
      THEN 
		commit;
      ELSE
		rollback;
      END IF;
    
	drop table tempTbl;
	drop table tempCoaches;
END IF;
END
|
DELIMITER ;

По подразбиране вашият сървър работи в режим safe. Което означава, че ако се опитвате да правите UPDATE или DELETE и не слагате волно или неволно WHERE клауза, то той ще ви спре, генерирайки съобщение за грешка. Друг подобен случай е, когато правим Update без да посочваме колона, по която да има създаден ключ. Във втората процедура правим точно такъв UPDATE, при който не използваме уникална колона в WHERE клаузата и това е причината вашата процедура да завършва със съобщение за грешка:

UPDATE coach_work
        SET isPayed = 1
	WHERE month(coach_work.date) = monthOfPayment
	AND YEAR(coach_work.date) = yearOFpayment
        AND isPayed = 0;

Програмистът може да избира сам дали да работи в safe режим или не. Може да смените режима като изпълните командата

SET SQL_SAFE_UPDATES=0;

Може да го изпълните някъде и докато работите, safe режимът да бъде изключен. Можете също в началото на процедурата да го изключите, а в края на процедурата отново да го включите, като съответно смените 0 на 1 в горната заявка.

Разгледайте внимателно двете процедури. Едната извиква другата. Вмъкнете повече тестови данни и тествайте различни сценарии, с които да се опитате да откриете бъгове – със сигурност има такива. Например още  на пръв поглед се вижда, че не са валидирани входните данни – месеците могат да бъдат от 1 до 12 и тн. Направете необходимите валидации и се опитайте да откриете още бъгове. В следваща тема ще се опитаме да оптимизираме процедурите като използваме много по-подходящите за целта курсори.

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