Add split and splitstr functions to Oracle

To add the split and splitstr functions to Oracle, there are many requirements for batch operations in recent projects. The client transmits a set of IDS separated by commas to the database, and the stored procedure needs to split them, then process them one by one. The previous Processing Methods of www.2cto.com are as follows: 1. Write a loop in the stored procedure, analyze the IDs in strings one by one, and then process them one by one. Disadvantage: process one task in a loop. If there are many judgments at a time, the efficiency will be greatly affected. It is suitable for the case where separate judgment is required for each processing. 2. To use a temporary table, call a stored procedure to split the ID and insert it into the temporary table. Then, you can use the temporary table to write SQL statements to process multiple times at a time. Disadvantage: temporary tables need to be inserted, and the efficiency is not high. The larger the data volume, the more serious the impact will be. In the past, the most commonly used method was 2nd. After all, it was convenient and the efficiency was better than 1st. Now many batch operations are used in the project, and a lot of repetitive code makes me bored. I suddenly thought that. Net and JS both have functions similar to split, which is very convenient to split strings. How nice would it be to have such functions in oracle. Multi-party data search finds that adding the split function to oracle is completely feasible, avoiding the need to insert a temporary table. Therefore, the efficiency is much higher than the method above 2nd. Later, I added the splitstr function to easily obtain the specified node in the string. With these two functions, processing batch operations is a huge addition to efficiency ...... Okay, let's talk about the code! If there is anything wrong with it, please make an axe to your predecessors. 1/* 2 * Oracle create split and splitstr functions 3 */4 5/* create a table type */6 create or replace type tabletype as table of VARCHAR2 (32676) 7/8 9/* CREATE split FUNCTION */10 create or replace function split (p_list CLOB, p_sep VARCHAR2: = ',') 11 RETURN tabletype12 PIPELINED13 /*********************************** * ** 14 * Name: split15 * Author: Sean Zhang.16 * Date: 2012-09-03.17 * Function: returns the table type after a string is split by a specified character. 18 * Parameters: p_list: string to be split. 19 p_sep: delimiter, which is a comma by default. You can also specify characters or strings. 20 * Example: SELECT * 21 FROM users22 WHERE u_id IN (SELECT COLUMN_VALUE23 FROM table (split ('1, 2') 24 returns two rows of data whose u_id is 1 and 2. 25 **************************************/ 26 IS27 l_idx PLS_INTEGER; 28 v_list VARCHAR2 (32676): = p_list; 29 BEGIN30 LOOP31 l_idx: = INSTR (v_list, p_sep); 32 33 IF l_idx> 034 THEN35 pipe row (SUBSTR (v_list, 1, rochelle idx-1); 36 v_list: = SUBSTR (v_list, Rochelle idx + LENGTH (p_sep); 37 ELSE38 pipe row (v_list); 39 EXIT; 40 end if; 41 end loop; 42 END; 43/44 45/* CREATE the splitstr FUNCTION */46 CREATE OR REPLACE FUNCTION Splitstr (str in clob, 47 I IN NUMBER: = 0, 48 sep IN VARCHAR2: = ', '49) 50 RETURN VARCHAR251 /************************************ ** 52 * Name: splitstr53 * Author: Sean Zhang.54 * Date: 2012-09-03.55 * Function: returns the specified node string after the string is separated by a specified character. 56 * Parameters: str: string to be split. 57 I: return the number of nodes. If I is 0, all characters in str are returned. If I exceeds the number of splits, null is returned. 58 sep: delimiter, which is a comma by default. You can also specify characters or strings. If the specified separator does not exist in str, the characters in sep are returned. 59 * Example: select splitstr ('abc, def ', 1) as str from dual; obtain the 'abc' select splitstr ('abc, def', 3) as str from dual; get the null 61 ************************************* */62 IS63 t_ I NUMBER; 64 t_count NUMBER; 65 t_str VARCHAR2 (4000); 66 BEGIN67 IF I = 068 THEN69 t_str: = str; 70 ELSIF INSTR (str, sep) = 071 THEN72 t_str: = sep; 73 ELSE74 select count (*) 75 INTO t_count76 FROM table (split (str, sep); 77 78 IF I <= t_count79 THEN80 SELECT str81 INTO t_str82 FROM (SELECT ROWNUM AS item, COLUMN_VALUE AS str83 FROM table (split (str, sep) 84 WHERE item = I; 85 END IF; 86 END IF; 87 88 RETURN t_str; 89 END; 90/