create or replace PROCEDURE PROC1 NAME_D IN VARCHAR2 NAME_W IN VARCHAR

 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
create or replace PROCEDURE PROC1
(
NAME_D IN VARCHAR2
, NAME_W IN VARCHAR2
, var1 OUT INT
) AS
BEGIN
DECLARE
id_w INT;
id_d INT;
tmp_id VARCHAR(20);
tmp_name VARCHAR(20);
size_r INT;
number_row INT;
last_row INT;
counter INT;
CURSOR my_cursor
IS
SELECT id, name
FROM Dictionary;
begin
SAVEPOINT start_tran;
number_row := 3;
--dbms_output.Put_line(number_row);
select count(*) into id_w from Word where name = name_w;
select count(*) into id_d from Dictionary where name = name_d;
select count(*) into last_row from Dictionary;
if(id_w = 0 and id_d = 0) then
insert into word(name) values(name_w)
returning id into id_w;
insert into dictionary(name) values(name_d)
returning id into id_d;
insert into dictionary_word(dictionary_id, word_id) values(id_d, id_w);
dbms_output.Put_line('INSERT WORD AND DICTIONARY');
end if;
if(id_w = 0 and id_d > 0) then
insert into word(name) values(name_w)
returning id into id_w;
insert into dictionary_word(dictionary_id, word_id) values(id_d, id_w);
dbms_output.Put_line('INSERT WORD ');
end if;
if(id_w > 0 and id_d = 0) then
insert into dictionary(name) values(name_d)
returning id into id_d;
insert into dictionary_word(dictionary_id, word_id) values(id_d, id_w);
dbms_output.Put_line('INSERT DICTIONARY');
end if;
counter := 0;
size_r := 0;
IF last_row >= number_row THEN
OPEN my_cursor;
FETCH my_cursor INTO tmp_id, tmp_name;
WHILE (my_cursor%FOUND) LOOP
counter := counter + 1;
IF counter = 1 or counter = last_row or counter = number_row THEN
--INSERT INTO Log(var1,var2) VALUES(var1, var2);
size_r := size_r + length(tmp_name) ;
END IF;
--dbms_output.Put_line(tmp_name);
FETCH my_cursor INTO tmp_id, tmp_name;
END LOOP;
CLOSE my_cursor;
dbms_output.Put_line(size_r);
dbms_output.Put_line('byte');
ELSE
dbms_output.Put_line('INCORRECT NUMBER');
dbms_output.Put_line(size_r);
END IF;
--dbms_output.Put_line(size_r);
--insert into Log(text) values(size_r);
end;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO start_tran;
RAISE;
NULL;
END PROC1;