There are many requirements for batch operations in recent projects. The client sends a set of IDS separated by commas to the database, and the stored procedure needs to split them and process them one by one.
There are many requirements for batch operations in recent projects. The client sends a set of IDS separated by commas to the database, and the stored procedure needs to split them and process them one by one.
Recently, many projects require batch operations. The client passes a set of IDS separated by commas (,) to the database, and the stored procedure needs to separate them and process them one by one.
The previous processing methods 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.
The Code is as follows:
/*
* Oracle creates split and splitstr functions.
*/
/* Create a table type */
Create or replace type tabletype as table of VARCHAR2 (32676)
/
/* Create the 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 table type after a string is separated by a specified character.
* Parameters: p_list: string to be split.
P_sep: delimiter. It is a comma by default. You can also specify characters or strings.
* Example: SELECT *
FROM users
WHERE u_id IN (SELECT COLUMN_VALUE
FROM table (split ('1, 2 ')))
Returns two rows of data whose u_id is 1 and 2.
**************************************/
IS
Rochelle idx PLS_INTEGER;
V_list VARCHAR2 (32676): = p_list;
BEGIN
LOOP
Rochelle 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 the 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: return the node string separated by a specified character.
* Parameters: str: string to be split.
I: return the nth node. If I is 0, all characters in str are returned. If I exceeds the number of splits, null is returned.
Sep: delimiter. It 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.
* Example: select splitstr ('abc, def ', 1) as str from dual; get abc
Select splitstr ('abc, def ', 3) as str from dual; get null
**************************************/
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;
/
