Логически оператори, цикли, временни таблици

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

  1. 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);

Резултатът е:

11

  1. 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);

Резултатът е :
12
Тест за 2015:

CALL getPaymentPeriod(1,1, 2015);

13

Тест за 2017:
14

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);

15
Ако подадем невалидна комбинация от месеци:

CALL getAllPaymentsAmount(6,1,2015,1);

16

Ако подадем несъществуващо studId:

CALL getAllPaymentsAmount(1,6,2015,101);

17

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

Временни таблици.

Временна таблица се създава така:

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 и трием таблицата. Тоест, при всяко извикване на процедурата ще се създава таблица, точно преди приключването и, тя ще се трие.
18
4. Цикъл Repeat- Until – има абсолютно същото действие като WHILE, но с разликата, че дори и условието да е грешно, тялото на цикъла ще се изпълни поне един път.

Синтаксис: 
REPEAT 
<заявки>; 
UNTIL <условие>; 
END REPEAT;

Задача: създайте процедура, с необходимите входни параметри, с които да може да премествате ученици от една група в друга група. Направете нужните проверки и извеждайте коректни съобщения. Удобно е да използвате фукнцията ROW_COUNT, която връща броя на засегнатите редове след последната заявка Update ili Delete.

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

10 thoughts on “Логически оператори, цикли, временни таблици”

  1. 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 ;

  2. временната таблица трябва да е във ELSE часта , веднага след While , защото в този случай тя винаги ще върне резултат таблица , и ако бъде прихваната някоя от горните проверки ще се върне просто като празна таблица , както и съобщението за съответната грешка 🙂

  3. Изпращам решението на задачата от 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 ;

  4. 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 ;

  5. 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 ;

  6. 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;

  7. 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 ;

  8. Задачата от упражнението на 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);

  9. Доколкото разбрах процедурата трябва да добавя студент и да го записва в група:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *