Some collection methods I’d like to see added to PL/SQL
In building a code generator I found the need to write a number of helper methods for doing basic modifications of arrays that are indexed by integer – such as appending one array onto another, inserting, and shifting. These arrays represent an ordered sequence of strings (e.g. lines of source code).
I think these would be a useful addition to the language if they were made native – e.g. (new commands in UPPERCASE):
declare type str_array_type is table of varchar2(32767) index by binary_integer; l_lines str_array_type; l_new str_array_type; l_idx binary_integer; begin .. (some code to fill the arrays here) .. -- get a subset of lines l_new := l_lines.SLICE(50, 59); -- extend l_lines with l_new at the end: l_lines.APPEND(l_new); -- shift l_lines forwards and insert l_new -- at the beginning: l_lines.PREPEND(l_new); -- insert l_new into l_lines at the given index; -- shift any existing lines at that location -- forward: l_lines.INSERT(l_new, at_idx => 21); -- remove the given range of indices from -- l_lines, replace with whatever is in l_new: l_lines.UPDATE(l_new, from_idx => 120, to_idx => 149); -- apply the given substitution on each line l_lines.REPLACE_ALL( old_val => 'foo', new_val => 'bar'); -- shift the given range of lines by the given -- offset (raise exception if existing data -- would get overwritten): l_lines.SHIFT( from_idx => 20, to_idx => 29, offset => 1000); -- shift and renumber all indices in the array -- with the given starting index and increment: l_lines.RENUMBER(start_idx => 10, increment => 10); -- make the array contiguous (i.e. remove gaps): l_lines.RENUMBER; -- loop over every line in the array that contains -- the given string: l_idx := l_lines.FIND_NEXT(contains => 'hello'); loop exit when l_idx is null; .. do something with l_lines(l_idx) .. l_idx := l_lines.FIND_NEXT(contains => 'hello', from_idx => l_idx); end loop; end;
I’ve illustrated these with a little sample package that may be viewed here:
What do you think?