CREATE PROCEDURE PROCEDURE4 IN name_w VARCHAR 20 IN name_d VARCHAR 20

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