我有一个用oracle编写的函数代码。想了解它

Question

首先创建my_table类型

CREATE OR REPLACE TYPE "MY_TABLE" AS TABLE OF VARCHAR2(3000);

然后创建功能

create or replace FUNCTION  "FUNC_STRING_TO_TABLE" (p_list IN VARCHAR2, p_delim IN VARCHAR2)
RETURN my_table
AS
l_string       VARCHAR2(32767);
l_comma_index  PLS_INTEGER;
l_index        PLS_INTEGER := 1;
l_tab          my_table := my_table();
BEGIN
l_string := p_list ||p_delim;
LOOP
l_comma_index := INSTR(l_string, p_delim, l_index);
EXIT WHEN l_comma_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END FUNC_STRING_TO_TABLE;

Answer 1

查看内联注释：

l_string := p_list ||p_delim;
-- Set l_string to be the input list with an extra delimiter appended.
LOOP
-- Start a loop.
l_comma_index := INSTR(l_string, p_delim, l_index);
-- Find the position of the next comma in the string.
EXIT WHEN l_comma_index = 0;
-- Exit the loop when a comma was not found.
l_tab.EXTEND;
-- Extend the l_tab collection by 1 element.
l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
-- Set the last element of the l_tab collection to the substring
-- between the last comma and the next comma.
l_index := l_comma_index + 1;
-- Skip to the position in the string after the next comma.
END LOOP;
-- Restart the loop.
RETURN l_tab;
-- Return the collection.