CREATE PROCEDURE PROCEDURE4(IN name_w VARCHAR(20), IN name_d VARCHAR(20),
OUT var1 VARCHAR(20),
OUT var2 VARCHAR(20)
)
AUTONOMOUS
BEGIN
DECLARE EOF INT DEFAULT 0;
DECLARE id_w INT;
DECLARE id_d INT;
DECLARE counter INT;
declare last_row INT;
DECLARE number_row INT;
DECLARE size_t INT;
DECLARE my_cursor CURSOR FOR
SELECT id, name FROM Dictionary;
--
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET EOF = 1;
SET counter = 0;
SET size_t = 0;
SET number_row = 2;
set var2 = (select count(*) from Dictionary);
set id_w = (select id from Word where name = name_w);
set id_d = (select id from Dictionary where name = name_d);
--INSERTN INTO WORD AND DICTIONARY
IF( (id_w IS NULL) AND (id_d IS NULL)) THEN
insert into word(name) values(name_w);
set id_w = (select id from Word where name = name_w);
insert into dictionary(name) values(name_d);
set id_d = (select id from Dictionary where name = name_d);
set var2 = id_w;
set var1 = id_d;
insert into dictionary_word(word_id, dictionary_id)
values(id_w, id_d);
ELSE
--INSERTN INTO WORD
IF( (id_w IS NULL) AND (id_d IS NOT NULL)) THEN
insert into word(name) values(name_w);
set id_w = (select id from Word where name = name_w);
insert into dictionary_word(word_id, dictionary_id)
values(id_w, id_d);
else
--INSERTN INTO DICTIONARY
IF( (id_w IS NOT NULL) AND (id_d IS NULL)) THEN
insert into dictionary(name) values(name_d);
set id_d = (select id from Dictionary where name = name_d);
insert into dictionary_word(word_id, dictionary_id)
values(id_w, id_d);
END IF;
end if;
END IF;
set last_row = (select count(*) from Dictionary);
SET counter = 0;
if number_row <= last_row then
--cursor
OPEN my_cursor;
FETCH FROM my_cursor INTO var1, var2;
WHILE EOF = 0 DO
set counter = counter + 1;
IF counter = 1 or counter = last_row or counter = number_row THEN
--INSERT INTO Log(var1,var2) VALUES(var1, var2);
SET size_t = size_t + length(var2) ;
END IF;
--INSERT INTO Log(var1,var2) VALUES(var1,var2);
FETCH FROM my_cursor INTO var1, var2;
END WHILE;
CLOSE my_cursor;
INSERT INTO Log(var1,var2) VALUES('SIZE', size_t);
else
INSERT INTO Log(var1,var2) VALUES('SIZE_FALSE', size_t);
end if;
END