За да може в процедурите да имплементирате някаква логика, съществуват логически оператори и цикли, подобно на повечето езици за програмиране.
- IF-оператор. Оператор за проверка на логическо условие. Синтаксис:
IF <условие> THEN <заявки>; ELSE <заявки>; END IF;
Нека дадем пример с една процедура, която по подадено id на ученик, номер на група, месец и година, проверява дали е платена таксата му.
use school_sport_clubs; #drop procedure checkMothTax; delimiter | CREATE procedure checkMothTax(IN studId INT, IN groupId INT, IN paymentMonth INT, IN paymentYear INT) BEGIN DECLARE result char(1); SET result = 0; IF( (SELECT paymentAmount FROM taxespayments WHERE student_id = studId AND group_id = groupId AND MONTH = paymentMonth AND year = paymentYear) IS NOT NULL) THEN SET result = 1; ELSE SET result = 0; END IF; SELECT result as IsTaxPayed; end; | delimiter ;
Извикваме процедурата:
CALL `school_sport_clubs`.`checkMothTax`(1, 1,1,2015);
Резултатът е:
- CASE – оператор. Оператор за многовариантен избор, аналогичен на switch-case в езиците за програмиране. Синтаксисът му е:
CASE <променлива> WHEN <условие> THEN <заявки>; WHEN <условие> THEN <заявки>; ... ELSE <заявки> END CASE;
Частта ELSE се достига тогава, когато нито едно от условията по-горе не е изпълнено.
Нека демонстрираме с пример. Ще изведем годишен период, за който са платени таксите на даден студент. Възможни са периодите едномесечен, тримесечен, шестмесечен, годишен, като изискването е те да се връщат като стрингове. Например: за едномесечен период, нека бъде ONE_MONTH, за тримесечен THREE_MONTHS и тн. За всички останали бройки, нека върнатият низ бъде конкатенация от броя месеци и думата MONHTS. При неплатени такси за годината, нека се изведе подходящо съобщение. За целта е удобно да ползваме sql функцията concat(), на която като аргументи, разделени със запетая, се подават стринговете, които бихме искали да бъдат конкатенирани.
use school_sport_clubs; #drop procedure getPaymentPeriod; delimiter | CREATE procedure getPaymentPeriod(IN studId INT, IN groupId INT, IN paymentYear INT) BEGIN DECLARE countOfMonths tinyint; DECLARE monthStr VARCHAR(10); DECLARE yearStr varchar(10); SET monthStr = 'MONTH'; SET yearStr = 'YEAR'; SELECT COUNT(*) INTO countOfMonths FROM taxespayments WHERE student_id = studId AND group_id = groupId AND year = paymentYear; CASE countOfMonths WHEN 0 THEN SELECT 'This student has not paid for this group/year!' as PAYMENT_PERIOD; WHEN 1 THEN SELECT concat('ONE_', monthStr) as PAYMENT_PERIOD; WHEN 3 THEN SELECT concat('THREE_',monthStr, 'S') as PAYMENT_PERIOD; WHEN 6 THEN SELECT concat('SIX_',monthStr,'S') as PAYMENT_PERIOD; WHEN 12 THEN SELECT yearStr as PAYMENT_PERIOD; ELSE SELECT concat(countOfMonths,monthStr,'S') as PAYMENT_PERIOD; END CASE; END; | DELIMITER ;
Ще извикаме процедурата за студент номер 1, група номер 1 и година 2016:
CALL getPaymentPeriod(1,1, 2016);
CALL getPaymentPeriod(1,1, 2015);
3. WHILE-цикъл. Синтаксис:
WHILE <условие> DO <заявки>; END WHILE;
Ще демонстрираме употреба на while – цикъл като направим процедура, която приема като параметри два месеца, между които ще изведе всички такси, платени от студент(като параметър) за определена година (като параметър) по всички негови спортове.
use school_sport_clubs; #drop procedure getAllPaymentsAmount; delimiter | CREATE procedure getAllPaymentsAmount(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studId INT) BEGIN DECLARE iterator int; IF(firstMonth >= secMonth) THEN SELECT 'Please enter correct months!' as RESULT; ELSE IF((SELECT COUNT(*) FROM taxesPayments WHERE student_id =studId ) = 0) THEN SELECT 'Please enter correct student_id!' as RESULT; ELSE SET ITERATOR = firstMonth; WHILE(iterator >= firstMonth AND iterator <= secMonth) DO SELECT student_id, group_id, paymentAmount, month FROM taxespayments WHERE student_id = studId AND year = paymentYear AND month = iterator; SET iterator = iterator + 1; END WHILE; END IF; END IF; END; | DELIMITER ;
Извикваме:
CALL getAllPaymentsAmount(1,6,2015,1);
Ако подадем невалидна комбинация от месеци:
CALL getAllPaymentsAmount(6,1,2015,1);
Ако подадем несъществуващо studId:
CALL getAllPaymentsAmount(1,6,2015,101);
В този вид обаче резултатът не е особено удобен. Ще помислим за вариант, при който да изкараме резултата в една обща резултатна таблица. Това може да се случи като използваме временна таблица.
Временни таблици.
Временна таблица се създава така:
CREATE TEMPORARY TABLE ( , ….) ENGINE = MEMORY;
Временните таблици са „локални“ – т.е всяка нишка ще създава нова таблица, независимо, че имената може да са едни и същи. Добре е да се изтриват преди приключване на процедурата.
use school_sport_clubs; #drop procedure getAllPaymentsAmountOptimized; delimiter | CREATE procedure getAllPaymentsAmountOptimized(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studId INT) BEGIN DECLARE iterator int; CREATE TEMPORARY TABLE tempTbl( student_id int, group_id int, paymentAmount double, month int ) ENGINE = Memory; IF(firstMonth >= secMonth) THEN SELECT 'Please enter correct months!' as RESULT; ELSE IF((SELECT COUNT(*) FROM taxesPayments WHERE student_id =studId ) = 0) THEN SELECT 'Please enter correct student_id!' as RESULT; ELSE SET ITERATOR = firstMonth; WHILE(iterator >= firstMonth AND iterator <= secMonth) DO INSERT INTO tempTbl SELECT student_id, group_id, paymentAmount, month FROM taxespayments WHERE student_id = studId AND year = paymentYear AND month = iterator; SET iterator = iterator + 1; END WHILE; END IF; END IF; SELECT * FROM tempTbl; DROP TABLE tempTbl; END; | DELIMITER ;
Както виждате, първо създаваме временната таблица tempTbl, така, както бихме създали таблица, която не е временна, само че указваме storage engine ще бъде Engine = Memory. Това означава, че сървърът няма да съхрани таблицата в нормалния (по подразбиране) storage engine, който е InnoDb, а ще я съхрани в такъв, който играе роля на временен storage. Memory e известен още като Heap. За да вкарваме данни в таблицата по време на итерирането на цикъла, ползваме INSERT SELECT команда. Накрая правим SELECT и трием таблицата. Тоест, при всяко извикване на процедурата ще се създава таблица, точно преди приключването и, тя ще се трие.
4. Цикъл Repeat- Until – има абсолютно същото действие като WHILE, но с разликата, че дори и условието да е грешно, тялото на цикъла ще се изпълни поне един път.
Синтаксис: REPEAT <заявки>; UNTIL <условие>; END REPEAT;
Задача: създайте процедура, с необходимите входни параметри, с които да може да премествате ученици от една група в друга група. Направете нужните проверки и извеждайте коректни съобщения. Удобно е да използвате фукнцията ROW_COUNT, която връща броя на засегнатите редове след последната заявка Update ili Delete.
Даниел Джолев
DELIMITER $$
CREATE PROCEDURE `changeGroup`(in studentId int, in oldGroupId int, in newGroupId int)
begin
declare count int;
declare result tinyint(1);
set result = 0;
if((select count(*) # dali studentyt uchastva v dadenata grupa
from student_sport
where sportGroup_id = oldGroupId
AND student_id = studentId ) = 0)
THEN
SELECT ‘No such student in this group’ as message;
else
IF ((SELECT COUNT(*) ## dali novata grupa syshtestvuva
FROM sportgroups
WHERE id = newGroupId) = 0)
then SELECT ‘No such group like newgroup’ as result;
ELSE
start transaction;
UPDATE student_sport
SET sportGroup_id = newGroupId
where sportGroup_id = oldGroupId
AND student_id = studentId;
IF(ROW_COUNT() = 0)
THEN rollback;
SELECT ‘Transaction rollbacked’;
else
commit;
SELECT ‘Transaction completed’;
end if;
end if;
end if;
end$$
DELIMITER ;
временната таблица трябва да е във ELSE часта , веднага след While , защото в този случай тя винаги ще върне резултат таблица , и ако бъде прихваната някоя от горните проверки ще се върне просто като празна таблица , както и съобщението за съответната грешка 🙂
Изпращам решението на задачата от 5-то упражнение.
use school_sport_clubs;
drop procedure if exists isStudentSignedUp;
delimiter |
create procedure isStudentSignedUp(in studentID int, in groupID int, out isSigned bit)
begin
if((select students.id from students where students.id = studentID) is not null and
(select sportgroups.id from sportgroups where sportgroups.id = groupID) is not null)
then
if ((select students.id from students join student_sport join sportgroups on
students.id = student_sport.student_id and
sportgroups.id = student_sport.sportGroup_id
where students.id = studentID and sportgroups.id = groupID) is not null)
then
set isSigned = 0;
select ‘Already Exists’;
else
insert into student_sport(student_id,sportgroup_id) values (studentID, groupID);
set isSigned = 1;
select ‘Success’;
end if;
else
select ‘There is no such student or sportgroup’;
end if;
end|
delimiter ;
USE school_sports;
#drop procedure checkIfGroupExists;
delimiter &
create procedure checkIfGroupExists(IN groupId int, OUT ifExists bit)
begin
IF ((SELECT count(*)
FROM groups
WHERE id = groupId ) = 0 )
THEN
set ifExists = 0;
ELSE
set ifExists = 1;
END IF;
end;
&
delimiter ;
#drop procedure checkIfEntryAlreadyExists;
delimiter &
create procedure checkIfEntryAlreadyExists(IN studentId int, IN groupId int, OUT ifExists bit)
begin
IF ((SELECT count(*)
FROM students_sports
WHERE student_id = studentId
AND group_id = groupId) >= 1)
THEN
set ifExists = 1;
ELSE
set ifExists = 0;
END IF;
end;
&
delimiter ;
#drop procedure loadIntoTable;
delimiter &
create procedure loadIntoTable(IN studentId int, IN groupId int, OUT isSuccessfull bit)
begin
INSERT INTO students_sports(student_id, group_id) VALUES (studentId, groupId);
IF ( ROW_COUNT() = 0 )
THEN
SELECT ‘Something went wrong and we could not add the student into this group. Please, try again!’ as RESULT;
SET isSuccessfull = 0;
ELSE
SELECT ‘The student was successfully added into this group!’ as RESULT;
SET isSuccessfull = 1;
END IF;
end;
&
delimiter ;
#drop procedure loadStudentIntoGroup;
delimiter %
create procedure loadStudentIntoGroup(IN studentId int, IN groupId int, OUT isSuccessfull bit)
begin
declare ifGrpExists bit;
declare ifEntryExists bit;
IF ((SELECT name
FROM students
WHERE id = studentId ) IS NOT NULL )
THEN
call checkIfGroupExists(groupId, ifGrpExists);
CASE ifGrpExists
WHEN 0 THEN
SELECT ‘This group does not exists!’ as RESULT;
SET isSuccessfull = 0;
WHEN 1 THEN call checkIfEntryAlreadyExists(studentId, groupId , ifEntryExists);
IF (ifEntryExists = 0)
THEN call loadIntoTable(studentId, groupId, isSuccessfull);
ELSE
SELECT ‘The student has been added into this group already!’ as RESULT;
SET isSuccessfull = 0;
END IF;
END CASE;
ELSE
SELECT ‘The student does not exists in the DB!’ as RESULT;
set isSuccessfull = 0;
END IF;
end;
%
delimiter ;
use school_sport_clubs;
drop procedure if exists studentSigningUp;
delimiter |
create procedure studentSigningUp(out isSigned bit, in studentId int, in groupId int)
begin
if((select students.id from students where students.id = studentID) is not false AND
(select sportgroups.id from sportgroups where sportgroups.id = groupID) is not false)
then
if ((select students.id from students join student_sport join sportgroups on
students.id = student_sport.student_id and
sportgroups.id = student_sport.sportGroup_id
where students.id = studentID and sportgroups.id = groupID) is not null)
then
set isSigned = 0;
select ‘Already Exists’;
else
insert into student_sport(student_id,sportgroup_id) values (studentID, groupID);
set isSigned = 1;
select ‘Success’;
end if;
else
select ‘There is no such student or sportgroup’;
end if;
end|
delimiter ;
1)Създаване на лог таблица:
create table logger(
loggerLevel enum(‘DEBUG’, ‘INFO’, ‘ERROR’),
message varchar(255),
dateAndTime datetime);
2)Създаване на процедура за добавяне на студент в спортна група:
delimiter $$
create procedure insertStudentsIntoGroups`(in studentToInsert int, in sportGroupToBeInsertedIn int, out result bit)
begin
insert into logger
values(‘DEBUG’, concat(‘Trying to insert student with id = ‘, studentToInsert, ‘ into table with id = ‘, sportGroupToBeInsertedIn, ‘!’), now());
if( (select count(*)
from students s
where s.id = studentToInsert) = 0)
then
insert into logger
values(‘INFO’, ‘There is no student with that id!’, now());
set result = 0;
else
if( (select count(*)
from sportgroups sg
where sg.id = sportGroupToBeInsertedIn) = 0)
then
insert into logger
values(‘INFO’, ‘There is no sport group with that id!’, now());
set result = 0;
else
if( (select count(*)
from student_sport ss
where ss.sportGroup_id = sportGroupToBeInsertedIn
and ss.student_id = studentToInsert) > 0)
then
insert into logger
values(‘INFO’, ‘This student is already in this group!’, now());
set result = 0;
else
if( (select sg.hourOfTraining, sg.dayOfWeek
from sportgroups sg join student_sport ss
on sg.id = ss.sportGroup_id
where ss.student_id = studentToInsert
and sg.hourOfTraining in(
select sg.hourOfTraining
from sportgroups sg
where sg.id = sportGroupToBeInsertedIn)
and sg.dayOfWeek in(
select sg.dayOfWeek
from sportgroups sg
where sg.id = sportGroupToBeInsertedIn)) =
(select sg.hourOfTraining, sg.dayOfWeek
from sportgroups sg
where sg.id = sportGroupToBeInsertedIn))
then
insert into logger
values(‘INFO’, ‘This student is already in group at this time!’, now());
set result = 0;
else
start transaction;
insert into student_sport
values(studentToInsert, sportGroupToBeInsertedIn);
if(row_count()=0)
then
insert into logger
values(‘ERROR’, ‘Transaction on adding student failed!’, now());
set result = 0;
rollback;
else
insert into logger
values(‘INFO’, ‘Student added to group’, now());
set result = 1;
commit;
end if;
end if;
end if;
end if;
end if;
insert into logger
values(‘DEBUG’, concat(‘Procedure finished with student id = ‘, studentToInsert, ‘ and table id = ‘, sportGroupToBeInsertedIn, ‘!’), now());
end $$
delimiter ;
3) Тест за добавяне:
set @result = 0;
call insertStudentsIntoGroups(6,5,@result);
select @result;
Use StudentGroup;
delimiter | |
create procedure ZapisStudentVGrupa(IN student_id int, IN group_id int)
begin
if((select count(*) from students where students.id = student_id) = 0) then (select ‘Student not found’);
else if((select count(*)from groups where groups.id = group_id) = 0) then select ‘Group not found’;
else insert into student_group (student_id, sportGroup_id) values(student_id, group_id);
end if;
end if;
end;
||
delimiter ;
Задачата от упражнението на 24.04.2017 год.
delimiter |
create procedure LoadStudens (IN st_id int,OUT isInGroup boolean)
begin
If((select students.id
from students join student_sport
on student.id = student_sport.student_id)is not null)
set isInGroup = 1
select ‘Студентът участва в тази група ‘;
else
set isInGroup = 0
select ‘Студентът не участва в групата ‘;
end if;
end
|
delimiter ;
call LoadStudens(1,0);
Доколкото разбрах процедурата трябва да добавя студент и да го записва в група:
delimiter /
create procedure addStudent(IN s_name varchar(255), IN s_egn varchar(10), IN s_address varchar(255), IN s_phone varchar(20), IN s_class varchar(10), IN groupID int)
begin
if((select count(*) from students where students.egn=s_egn)>0)
then
SELECT ‘EGN exists’ as Result;
else if(char_length(s_egn)<10)
then
SELECT 'Invalid EGN';
else
insert into students(name, egn, address,phone, class) values(s_name, s_egn, s_address, s_phone, s_class);
insert into student_sport(student_id, sportGroup_id) values((select id from students where egn=s_egn), groupID);
end if;
end if;
end;
/
delimiter
Да.