Oracle PL/SQL Split function

In the software development process, programmers often encounter string splicing and splitting work.

Take Java development as an example:

The foreground passes in a string concatenation of a JSON data, such as: "1001,1002,1003", which may represent a set of ordinal numbers.

Programmers need to turn the serial number to the name and output it in the same format, such as: "Zhang San, John Doe, Harry".

The common practice of Java programmers is to split the "1001,1002,1003" received at the service layer (using the Java Split function), then encapsulate the list and pass the list to DAO,

The XML that is passed to the ORM persistence layer invokes SQL execution, and the results of the SQL return are received with the list and the service layer traverses the list and stitching strings.

Encapsulates the stitched string in the entity class (BO/VO) and returns it to the foreground in JSON format.

This approach is implemented, but it calls a database connection more than once, writes a DAO method, and writes an ORM persistence layer method.

What about handing the question to PL/SQL programmers?

PL/SQL Programmers do not seem to have a better way to do this, and for each of these functions, it is expensive to write the stored procedure separately.

This article is to do is a sentence SQL implementation of this function.

First of all, this business has two key points.

one is the string concatenation , Oracle (11.2) provides the LISTAGG function has implemented this function, we can directly use.

The second is string splitting , which is not implemented by Oracle, but Java provides the split function to implement the string splitting function.

We can refer to the Split function in Java to write an Oracle version of the Split function.

The function of the split function is to separate the string into multiple small strings by specific characters, and the returned results are saved as a list or array type.

First, create a type with the following code:

Create or Replace type type_str is table of VARCHAR2 (100);

then create the Split function with the following code:

Create or Replace function split (p_str varchar2,p_delimiter varchar2 default ', ') return TYPE_STR

Is

RS Type_str:=type_str ();

L_STR varchar2 (4000): = ";

L_len number:=0;

Begin

L_STR:=P_STR;

L_len:=length (P_delimiter);

While length (L_STR) >0 loop

If InStr (L_str,p_delimiter) >0 Then

Rs.extend;

RS (Rs.count): =substr (L_str,1,instr (L_str,p_delimiter)-1);

L_str:=substr (L_str,instr (l_str,p_delimiter) +l_len);

Else

Rs.extend;

RS (rs.count): =l_str;

Exit

End If;

End Loop;

Return RS;

End

/

Show err;

Test:

1. Basic functions
Sql> Select Column_value from table (Split (' 1001,1002,1003 ', ', '));

Column_value
--------------------------------------------------------------------------------
1001
1002
1003

2. Character to number + default delimiter

sql> Select To_number (column_value) from table (Split (' 1001,1002,1003 '));
 
To_number (column_value)
-----------------------
                    1001
                    1002
                    1003
  3. Multi-delimiter support

sql> Select Column_value from table (Split (' [Email protected]#[email  protected] #1003 ', ' @# ');
 
Column_value
--------------------------------------------------------------------------------
1001
1002
1003

The single split function was tested successfully and used in conjunction with the Listagg function, two tables need to be built.

For ease of understanding, let's build a business scenario.
Building a business scenario (This business scenario is purely fictitious, if there is a coincidence):

There is an author table that records the author's personal information (such as name, age, etc.), and the primary key is the serial number generated.

There is a book table that records information about a book (e.g., title, publisher, author, etc.), and the primary key is the serial number generated.

An author may write too many books, and a book may be co-authored by multiple authors.

For many-to-many scenarios, the general design principle is to add a many-to-many relationship table that records the Book table primary key and the author table primary key.

For a variety of reasons, we are not talking about the general design,

Instead of directly in the Book table how the author attribute, take the value as the author table primary key, but there are multiple authors with ', ' delimited.

Good. The business scenario is well-documented and now begins to build tables and initialize the data.

CREATE TABLE author
(
A_ID Number (8) is not NULL,
A_name varchar2 (100),
A_age Number (3)
);
CREATE TABLE book
(
B_ID Number (8) is not NULL,
B_name varchar2 (100),
A_ID VARCHAR2 (100)
);
Insert into author values (1001, ' Zhangsan ', 40);
Insert into author values (1002, ' Lisi ', 30);
Insert into author values (1003, ' Wangwu ', 50);
Commit
Insert INTO book values (2001, ' Think in PL/SQL ', ' 1001,1002,1003 ');
Commit

Joint query with Listagg function:

Select b_id,b_name,a_id,
(Select Listagg (a_name, ', ') within group (order by a_id) from author
where a_id in (select To_number (column_value) from table (Split (b.a_id, ', ')))) A_name
from book B;

Note: Listagg is a new feature of the Oracle 11.2 release.

Oracle PL/SQL Split function