CREATE DEFINER root localhost PROCEDURE AddWordDictionary IN name_d VA

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
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