Transfer code from PL /SQL to PL/pgSQL(index table)
I need to rewrite the code. But the problem is that the solution is based on index tables (and they are not in the PL /pgSQL). Do you have any ideas how to replace them? Create a procedure that for an arbitrary input character string consisting of from an arbitrary set of words, will select the sequences with the greatest the number of words repeated at least twice (sequences of one words also take into account).
create OR REPLACE procedure SEARCH_KEYWORD (line1 IN varchar2) is - the input string
type word is table of
varchar2 (200)
index by pls_integer;
str_word word; - table for words from a string
str word; - tables for all phrases
str2 word; - table for all repeated phrases in the string
str3 word; - table for longest repeating phrases
type numb is table of
NUMBER
index by pls_integer;
quantity numb; - the number of words in the phrase
red1 numb; - the number of the word in the line with which the phrase begins
red2 numb; - the number of the word in the line with which the phrase ends
j NUMBER: = 1; - the number of words in a line
d NUMBER: = 1; - number of phrases per line
k NUMBER: = 1; - auxiliary variable
ind NUMBER: = 1; - auxiliary variable
t NUMBER: = 1; - number of repeated phrases
r NUMBER: = 1; - number of phrases with maximum number of words
line varchar2 (200); - changed input line (there is no more space between lines in it
BEGIN
if REGEXP_COUNT (line1, '. * \ d')> = 1 then
raise_application_error (-20455, 'This line contains numbers');
end if;
line: = regexp_replace (line1, '[] +', ''); - remove all extra spaces in the line
- fill the str table, write all the words from the string
for i in 1..length (line)
loop
if substr (line, i, 1) = '' then --if we encounter a space
str_word (j): = substr (line, k, i-k);
j: = j + 1;
k: = i + 1;
end if;
- if the end of the line we write the last word into the table
if i = length (line) then
str_word (j): = substr (line, k, i-k + 1);
j: = j + 1;
k: = i + 1;
end if;
end loop;
- in the loop, write down all possible phrases from the string
for i in 1..j-1
loop
str (d): = str_word (i) || ' ';
red1 (d): = i;
red2 (d): = i;
d: = d + 1;
for h in i + 1..j-1
loop
str (d): = str (d-1) || str_word (h) || ' ';
red1 (d): = i;
red2 (d): = h;
d: = d + 1;
end loop;
end loop;
- looking for repeating phrases
for i in 1..d-1
loop
for g in i + 1..d-1
loop
- if a repeating phrase, then write to a new table
if str (i) = str (g) and red2 (i) <red1 (g) then
str2 (t): = str (i);
quantity (t): = REGEXP_COUNT (str2 (t), '[^ [: space:]]? [^ [: space:]]'); - find the number of words in the phrase
t: = t + 1;
end if;
end loop;
end loop;
- error handler if there are no duplicate phrases
if t = 1 then
raise_application_error (-20454, 'There are no duplicate phrases on this line');
end if;
- in two for we search for the longest phrases and write them into a new table
for i in 2..t-1
loop
if quantity (i)> quantity (ind) then
ind: = i;
end if;
end loop;
for i in 1..t-1
loop
if quantity (i) = quantity (ind) then
str3 (r): = str2 (i);
r: = r + 1;
end if;
end loop;
- here the output occurs, but with a double bypass (so that if the phrase is repeated more than 2 times, it is not displayed more than 1 time)
for i in 1..r-1
loop
t: = 0;
for g in i + 1..r-1
loop
if str3 (i) = str3 (g) then
t: = 1;
end if;
end loop;
if t = 0 then
dbms_output.put_line (str3 (i));
end if;
end loop;
end SEARCH_KEYWORD;