Oracle string segmentation in work

Oracle:

Definition type (for string segmentation ):

Create or replace
TYPE "STR_SPLIT" is table of VARCHAR2 (4000 );

String segmentation functions:

Create or replace
FUNCTION splitstr (p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
V_length NUMBER: = LENGTH (p_string );
V_start NUMBER: = 1;
V_index NUMBER;
BEGIN
WHILE (v_start <= v_length)
LOOP
V_index: = INSTR (p_string, p_delimiter, v_start );

IF v_index = 0
THEN
Pipe row (SUBSTR (p_string, v_start ));
V_start: = v_length + 1;
ELSE
Pipe row (SUBSTR (p_string, v_start, v_index-v_start ));
V_start: = v_index + 1;
End if;
End loop;

RETURN;
END splitstr;

 

Running effect:

SQL: select CRM. SPLITSTR (', 100001240, 4567890,', ') from dual;

 

SQL: SELECT * FROM TABLE (SPLITSTR (', 100001240, 4567890 ,',','))

 

Extended SQL:

SELECT * from table (SPLITSTR (', 100001240, 20171241,4567890,') WHERE column_value IN ('20140901', '20160301', '20160301 ', '123 ');

 

 

Mybaits + oracle:

SQL: (Description: Splits a string by a specific character, concatenates it into a specific record, and compares it with existing table data (by specific field) filter out records that do not exist and splice the records to complete the insert operation)

Function Description: This function allows you to insert records in batches. Existing records are excluded.

Insert into CRM. d_business_link (
Select t. *, # {account, jdbcType = VARCHAR}, sysdate from
(SELECT # {customer, jdbcType = VARCHAR} as host,
MERs .*,
# {Relationship, jdbcType = VARCHAR} AS RELATION
FROM
(SELECT * from table (CRM. SPLITSTR (# {selected_dCuscode, jdbcType = VARCHAR },','))
) MERs
Minus
Select Host, CUSTOMER, relation from CRM. d_business_link
) T)

 

I don't know whether the design is faulty or the demand is abnormal, but I did not care about it. I really admire myself,

First of all, design: dictionary tables (it should be understood for Dynamic Maintenance: The ing between dictionaries is saved in the form of groups)

There is no problem in the design of a dictionary table for a table (product table). The problem is that a field stores dictionary items in the form of delimiters.

Requirement: convert a batch of records into meaningful records that can be recognized through dictionary tables.

Original record:

Gjzdgc, tljs, slsl, shsc, jcjs, and qljs
Gycf, slsl, sydc, ggjz, jcjs, and qljs
Slsl, shsc, sydc, qljs
Gjzdgc, gsgl, yzsc, shsc, jcjs, qljs
Grzx, gsgl, qljs
Grzx
Gjzdgc, gsgl, slsl, shsc, sydc, qljs

Target record:

Key national projects, water conservancy hubs, mixed commercial production, Railway Engineering, Airport Engineering, and Bridge Engineering
Water conservancy hub, commercial real estate, public buildings, industrial plants, Airport Engineering, and Bridge Engineering
Water conservancy hub, mixed commercial production, commercial real estate, and Bridge Engineering
Key national projects, highways, mixed commercial production, prefabricated production, Airport Engineering, and Bridge Engineering
Personal decoration, highway, Bridge Engineering
Personal Decoration
Key national projects, highways, water conservancy hubs, mixed commercial production, commercial real estate, and bridge projects

SQL:

Select
(
Select wmsys. WM_CONCAT (dd_infoname)
FROM C_DICTIONARY_DETAILS cdd
WHERE cdd. DD_INFOCODE IN (select * from table (splitstr (p_userful ,',')))
) As P_USERFUL, p_userful
, .*
FROM c_product;

Ideas:

1.

SELECT dd_infoname
FROM C_DICTIONARY_DETAILS cdd
WHERE cdd. DD_INFOCODE IN (select * from table (splitstr ('gycf, slsl, sydc, ggjz, jcjs, qljs ',',')))

2. Use the WM_CONCAT function to convert a record into a comma-separated record (which can be 10 Gb later). For more information, see du Niang)

SELECT WM_CONCAT (dd_infoname)
FROM C_DICTIONARY_DETAILS cdd
WHERE cdd. DD_INFOCODE IN (select * from table (splitstr ('gycf, slsl, sydc, ggjz, jcjs, qljs ',',')))

3. oracle underlying processing is not clear

SQL:

Select
(
SELECT WM_CONCAT (dd_infoname)
FROM C_DICTIONARY_DETAILS cdd
WHERE cdd. DD_INFOCODE IN (select * from table (splitstr (p_userful ,',')))
) As P_USERFUL, p_userful
FROM c_product;

So far OK. Finished