Methods to add split and splitstr functions to Oracle

There has been a lot of demand for batch operations in recent projects, where the client passes a comma-separated set of ID strings to the database, and the stored procedure needs to split them up and process them one by one.
The previous treatment methods are as follows:

1. Write a loop within the stored procedure, analyzing ID in the string one by one, and then processing one by one. Disadvantages: loop 1 to handle 1, if each judgment is too many, efficiency will be greatly affected. It is suitable for each case to be judged separately.

2. Using a temporary table, first call a stored procedure to split ID and insert it into the temporary table, and then combine the temporary table to write SQLto handle multiple pens once. Disadvantages: need to insert temporary table, low efficiency, the greater the amount of data, the more serious the impact.

In previous projects, the second method was used most often, because it was convenient and more efficient than the first one.
Now the project used a lot of batch operations, a lot of repetitive code let me tired. It occurred to me that both.Net and JS have functions similar to split, which is very convenient for splitting strings. If only oracle had the same function.

A multi-party search shows that adding the split function to oracle is entirely feasible and avoids inserting temporary tables, so it is much more efficient than the second method above.

Later I added the splitstr function, which makes it easy to get the specified node in a string.
With these two functions, processing batch operation, is really a tiger with wings added, efficiency doubled, hey hey......
Well, without further ado, code! If there are any problems, please kindly correct them.

 
/* 
* Oracle  create  split  and  splitstr  function  
*/ 
/*  create 1 A table type  */ 
create or replace type tabletype as table of VARCHAR2(32676) 
/ 
/*  create  split  function  */ 
CREATE OR REPLACE FUNCTION split (p_list CLOB, p_sep VARCHAR2 := ',') 
RETURN tabletype 
PIPELINED 
/************************************** 
* Name: split 
* Author: Sean Zhang. 
* Date: 2012-09-03. 
* Function:  Returns the type of table in which the string is separated by the specified character.  
* Parameters: p_list:  The string to be split.  
p_sep:  The delimiter, the default comma, can also specify a character or string.  
* Example: SELECT * 
FROM users 
WHERE u_id IN (SELECT COLUMN_VALUE 
FROM table (split ('1,2'))) 
 return u_id for 1 and 2 Two lines of data.  
**************************************/ 
IS 
l_idx PLS_INTEGER; 
v_list VARCHAR2 (32676) := p_list; 
BEGIN 
LOOP 
l_idx := INSTR (v_list, p_sep); 
IF l_idx > 0 
THEN 
PIPE ROW (SUBSTR (v_list, 1, l_idx - 1)); 
v_list := SUBSTR (v_list, l_idx + LENGTH (p_sep)); 
ELSE 
PIPE ROW (v_list); 
EXIT; 
END IF; 
END LOOP; 
END; 
/ 
/*  create  splitstr  function  */ 
CREATE OR REPLACE FUNCTION splitstr (str IN CLOB, 
i IN NUMBER := 0, 
sep IN VARCHAR2 := ',' 
) 
RETURN VARCHAR2 
/************************************** 
* Name: splitstr 
* Author: Sean Zhang. 
* Date: 2012-09-03. 
* Function:  Returns the specified node string after the string is split by the specified character.  
* Parameters: str:  The string to be split.  
i:  Returns which node. when i for 0 return str All characters in, when i  Returns null if the number of separables is exceeded.  
sep:  The delimiter, the default comma, can also specify a character or string. When the specified delimiter does not exist at str When to return to sep The characters in.  
* Example: select splitstr('abc,def', 1) as str from dual;  get  abc 
select splitstr('abc,def', 3) as str from dual;  get   empty  
**************************************/ 
IS 
t_i NUMBER; 
t_count NUMBER; 
t_str VARCHAR2 (4000); 
BEGIN 
IF i = 0 
THEN 
t_str := str; 
ELSIF INSTR (str, sep) = 0 
THEN 
t_str := sep; 
ELSE 
SELECT COUNT ( * ) 
INTO t_count 
FROM table (split (str, sep)); 
IF i <= t_count 
THEN 
SELECT str 
INTO t_str 
FROM (SELECT ROWNUM AS item, COLUMN_VALUE AS str 
FROM table (split (str, sep))) 
WHERE item = i; 
END IF; 
END IF; 
RETURN t_str; 
END; 
/