CREATE DEFINER=`root`@`localhost` PROCEDURE `AddWordDictionary`(IN `name_d` VARCHAR(20), IN `name_w` VARCHAR(20))
BEGIN
DECLARE id_d INT;
DECLARE id_w INT;
DECLARE cur_num INT;
DECLARE name_s VARCHAR(50);
DECLARE id_s INT;
DECLARE size INT;
DECLARE last_row INT;
DECLARE specialty CONDITION FOR SQLSTATE '45000';
DECLARE counter INT;
DECLARE v_finished INTEGER DEFAULT 0;
DEClARE my_cursor CURSOR FOR
SELECT id, name FROM Dictionary;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
SET cur_num = 27;
SET id_d = (SELECT id FROM dictionary WHERE name = name_d);
SET id_w = (SELECT id FROM word WHERE name = name_w);
START TRANSACTION;
IF ( ISNULL(id_w) and ISNULL(id_d) ) THEN
select 'insert into WORD';
select 'insert into DICTIONARY';
insert into word(`name`) values(name_w);
set id_w = LAST_INSERT_ID();
insert into dictionary(`name`) values(name_d);
set id_d = LAST_INSERT_ID();
insert into dictionary_word(`word_id`, `dictionary_id`)
values(id_w, id_d);
select id_w;
select id_d;
END IF;
IF ((id_w is not null) and ISNULL(id_d)) THEN
select 'insert dictionary';
insert into dictionary(`name`) values(name_d);
set id_d = LAST_INSERT_ID();
insert into dictionary_word(`word_id`, `dictionary_id`)
values(id_w, id_d);
select id_w;
select id_d;
END IF;
IF ((id_d is not null) and ISNULL(id_w)) THEN
select 'insert word';
insert into word(`name`) values(name_w);
set id_w = LAST_INSERT_ID();
insert into dictionary_word(`word_id`, `dictionary_id`)
values(id_w, id_d);
select id_w;
select id_d;
END IF;
OPEN my_cursor;
SET last_row = (SELECT FOUND_ROWS());
FETCH my_cursor INTO id_s, name_s;
set counter = 1;
set size = length(name_s) + SizeInt(id_s);
size: LOOP
if counter = cur_num or last_row = counter THEN
set size = size + length(name_s) + SizeInt(id_s);
end if;
FETCH my_cursor INTO id_s, name_s;
set counter = counter + 1;
IF v_finished = 1 THEN
LEAVE size;
END IF;
END LOOP size;
CLOSE my_cursor;
IF last_row > cur_num THEN
select size;
ELSE
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = ' :( ';
select MESSAGE_TEXT;
END IF;
IF `_rollback` THEN
ROLLBACK;
end IF;
COMMIT;
END