使用Oracle的PL/SQL编写自定义函数来实现split
Oracle数据库没有内置的split函数，但是你可以使用Oracle的PL/SQL编写自定义函数来实现类似的功能。以下是一个例子，展示了如何创建一个split函数，它将字符串按照指定的分隔符进行拆分，并返回一个PL/SQL表类型。
1、创建fn_split函数
1.1、创建或替换一个名为tabletype的一个表类型
create or replace type tabletype as table of varchar2(32676);
/
1.2、fn_split函数
create or replace function fn_split (p_list clob, p_sep varchar2 := ',')return tabletype pipelined
isl_idx pls_integer;v_list varchar2 (32676) := p_list;
beginloopl_idx := instr (v_list, p_sep);if l_idx > 0thenpipe row (substr (v_list, 1, l_idx - 1));v_list := substr (v_list, l_idx + length (p_sep));elsepipe row (v_list);exit;end if;end loop;
end;
/
2、调用fn_split函数
2.1、指定字符串调用
HR@PROD1> select * from table(fn_split('oracle11g,oracle12c,oracle19c',','));COLUMN_VALUE
--------------------------------------------------------------------------------
oracle11g
oracle12c
oracle19c
2.2、指定表字段关联调用
CREATE TABLE T_DBATest( id INT PRIMARY KEY,c_name VARCHAR2(128) NOT NULL,c_description VARCHAR2(100) NOT NULL);INSERT INTO T_DBATest VALUES (1, 'DEV001','SGCIT-Oracle');
INSERT INTO T_DBATest VALUES (2, 'DEV002','SQLServer-MongoDB-PostgreSQL');
INSERT INTO T_DBATest VALUES (4, 'DEV003','InfluxDB-MySQL-SAPHANA');
INSERT INTO T_DBATest VALUES (5, 'DEV004','JAVA-ABAP-C++');
commit;
支持Oracle11g/Oracle12c(12.1/12.2)/Oracle19c环境中调用
HR@PROD1> SELECT * FROM T_DBATest T1 CROSS JOIN table (fn_split (T1.C_DESCRIPTION,'-'));ID C_NAME C_DESCRIPTION COLUMN_VALUE
----- -------------------- ---------------------------------------- ----------------------------------------1 DEV001 SGCIT-Oracle SGCIT1 DEV001 SGCIT-Oracle Oracle2 DEV002 SQLServer-MongoDB-PostgreSQL SQLServer2 DEV002 SQLServer-MongoDB-PostgreSQL MongoDB2 DEV002 SQLServer-MongoDB-PostgreSQL PostgreSQL4 DEV003 InfluxDB-MySQL-SAPHANA InfluxDB4 DEV003 InfluxDB-MySQL-SAPHANA MySQL4 DEV003 InfluxDB-MySQL-SAPHANA SAPHANA5 DEV004 JAVA-ABAP-C++ JAVA5 DEV004 JAVA-ABAP-C++ ABAP5 DEV004 JAVA-ABAP-C++ C++11 rows selected.
支持Oracle12c(12.2)/Oracle19c 调用
Oracle 12c(12.1) 新增了 CROSS APPLY TABLE 和 OUTER APPLY TABLE 子句，它们用于表值函数（TVF）的 lateral 查询。CROSS APPLY 用于交叉联接，而 OUTER APPLY 用于外部联接。
以下是一个简单的例子，使用 CROSS APPLY TABLE 来展示如何将表值函数的结果与原始表结合起来
HR@ORCL> col id format 9999
HR@ORCL> col c_name format a20
HR@ORCL> col C_DESCRIPTION format a40
HR@ORCL> col COLUMN_VALUE format a40
HR@ORCL> set linesize 200
HR@ORCL> SELECT * FROM T_DBATest T1 CROSS JOIN (fn_split (T1.C_DESCRIPTION,'-'));ID C_NAME C_DESCRIPTION COLUMN_VALUE
----- -------------------- ---------------------------------------- ----------------------------------------1 DEV001 SGCIT-Oracle SGCIT1 DEV001 SGCIT-Oracle Oracle2 DEV002 SQLServer-MongoDB-PostgreSQL SQLServer2 DEV002 SQLServer-MongoDB-PostgreSQL MongoDB2 DEV002 SQLServer-MongoDB-PostgreSQL PostgreSQL4 DEV003 InfluxDB-MySQL-SAPHANA InfluxDB4 DEV003 InfluxDB-MySQL-SAPHANA MySQL4 DEV003 InfluxDB-MySQL-SAPHANA SAPHANA5 DEV004 JAVA-ABAP-C++ JAVA5 DEV004 JAVA-ABAP-C++ ABAP5 DEV004 JAVA-ABAP-C++ C++11 rows selected.
-- 以上 CROSS JOIN 在12c开始关联表值函数时，可以不明确指定 table 关键字HR@ORCL> SELECT * FROM T_DBATest T1 CROSS JOIN (fn_split (T1.C_DESCRIPTION,'-'));ID C_NAME C_DESCRIPTION COLUMN_VALUE
----- -------------------- ---------------------------------------- ----------------------------------------1 DEV001 SGCIT-Oracle SGCIT1 DEV001 SGCIT-Oracle Oracle2 DEV002 SQLServer-MongoDB-PostgreSQL SQLServer2 DEV002 SQLServer-MongoDB-PostgreSQL MongoDB2 DEV002 SQLServer-MongoDB-PostgreSQL PostgreSQL4 DEV003 InfluxDB-MySQL-SAPHANA InfluxDB4 DEV003 InfluxDB-MySQL-SAPHANA MySQL4 DEV003 InfluxDB-MySQL-SAPHANA SAPHANA5 DEV004 JAVA-ABAP-C++ JAVA5 DEV004 JAVA-ABAP-C++ ABAP5 DEV004 JAVA-ABAP-C++ C++11 rows selected.HR@ORCL> SELECT * FROM T_DBATest T1 OUTER APPLY table (fn_split (T1.C_DESCRIPTION,'-'));ID C_NAME C_DESCRIPTION COLUMN_VALUE
----- -------------------- ---------------------------------------- ----------------------------------------1 DEV001 SGCIT-Oracle SGCIT1 DEV001 SGCIT-Oracle Oracle2 DEV002 SQLServer-MongoDB-PostgreSQL SQLServer2 DEV002 SQLServer-MongoDB-PostgreSQL MongoDB2 DEV002 SQLServer-MongoDB-PostgreSQL PostgreSQL4 DEV003 InfluxDB-MySQL-SAPHANA InfluxDB4 DEV003 InfluxDB-MySQL-SAPHANA MySQL4 DEV003 InfluxDB-MySQL-SAPHANA SAPHANA5 DEV004 JAVA-ABAP-C++ JAVA5 DEV004 JAVA-ABAP-C++ ABAP5 DEV004 JAVA-ABAP-C++ C++11 rows selected.Oracle 12c(12.2) 新增了 CROSS APPLY 和 OUTER APPLY 子句，它们用于表值函数（TVF）的 lateral 查询。CROSS APPLY 用于交叉联接，而 OUTER APPLY 用于外部联接。
关联表值函数时，可以不明确指定 table 关键字
以下是一个简单的例子，使用 CROSS APPLY 来展示如何将表值函数的结果与原始表结合起来
HR@ORCL> SELECT * FROM T_DBATest T1 CROSS APPLY (fn_split (T1.C_DESCRIPTION,'-'));ID C_NAME C_DESCRIPTION COLUMN_VALUE
----- -------------------- ---------------------------------------- ----------------------------------------1 DEV001 SGCIT-Oracle SGCIT1 DEV001 SGCIT-Oracle Oracle2 DEV002 SQLServer-MongoDB-PostgreSQL SQLServer2 DEV002 SQLServer-MongoDB-PostgreSQL MongoDB2 DEV002 SQLServer-MongoDB-PostgreSQL PostgreSQL4 DEV003 InfluxDB-MySQL-SAPHANA InfluxDB4 DEV003 InfluxDB-MySQL-SAPHANA MySQL4 DEV003 InfluxDB-MySQL-SAPHANA SAPHANA5 DEV004 JAVA-ABAP-C++ JAVA5 DEV004 JAVA-ABAP-C++ ABAP5 DEV004 JAVA-ABAP-C++ C++11 rows selected.HR@ORCL> SELECT * FROM T_DBATest T1 OUTER APPLY (fn_split (T1.C_DESCRIPTION,'-'));ID C_NAME C_DESCRIPTION COLUMN_VALUE
----- -------------------- ---------------------------------------- ----------------------------------------1 DEV001 SGCIT-Oracle SGCIT1 DEV001 SGCIT-Oracle Oracle2 DEV002 SQLServer-MongoDB-PostgreSQL SQLServer2 DEV002 SQLServer-MongoDB-PostgreSQL MongoDB2 DEV002 SQLServer-MongoDB-PostgreSQL PostgreSQL4 DEV003 InfluxDB-MySQL-SAPHANA InfluxDB4 DEV003 InfluxDB-MySQL-SAPHANA MySQL4 DEV003 InfluxDB-MySQL-SAPHANA SAPHANA5 DEV004 JAVA-ABAP-C++ JAVA5 DEV004 JAVA-ABAP-C++ ABAP5 DEV004 JAVA-ABAP-C++ C++11 rows selected.支持Oracle12c(12.2)/Oracle19c 调用
Lateral View是Oracle 12c中的一个新特性，它允许你从一个表达式生成的集合中为每个元素应用一个查询。这是一个非常强大的特性，可以用来分析数组、集合、列表或者多维数组等复杂的数据结构。
-- oracle12c (12.2)/oracle19c
col id format 9999
col c_name format a20
col C_DESCRIPTION format a40
col COLUMN_VALUE format a40
set linesize 200
SELECT * FROM T_DBATest T1 ,lateral (SELECT * FROM fn_split (T1.C_DESCRIPTION,'-'));HR@orcl> SELECT * FROM T_DBATest T1 ,lateral (SELECT * FROM fn_split (T1.C_DESCRIPTION,'-'));ID C_NAME C_DESCRIPTION COLUMN_VALUE
----- -------------------- ---------------------------------------- ----------------------------------------1 DEV001 SGCIT-Oracle SGCIT1 DEV001 SGCIT-Oracle Oracle2 DEV002 SQLServer-MongoDB-PostgreSQL SQLServer2 DEV002 SQLServer-MongoDB-PostgreSQL MongoDB2 DEV002 SQLServer-MongoDB-PostgreSQL PostgreSQL4 DEV003 InfluxDB-MySQL-SAPHANA InfluxDB4 DEV003 InfluxDB-MySQL-SAPHANA MySQL4 DEV003 InfluxDB-MySQL-SAPHANA SAPHANA5 DEV004 JAVA-ABAP-C++ JAVA5 DEV004 JAVA-ABAP-C++ ABAP5 DEV004 JAVA-ABAP-C++ C++11 rows selected.-- oracle12c (12.2)/oracle19c
SELECT * FROM T_DBATest T1 left join lateral (SELECT * FROM fn_split (T1.C_DESCRIPTION,'-')) T2 on 1=1;HR@orcl> SELECT * FROM T_DBATest T1 left join lateral (SELECT * FROM fn_split (T1.C_DESCRIPTION,'-')) T2 on 1=1;ID C_NAME C_DESCRIPTION COLUMN_VALUE
----- -------------------- ---------------------------------------- ----------------------------------------1 DEV001 SGCIT-Oracle SGCIT1 DEV001 SGCIT-Oracle Oracle2 DEV002 SQLServer-MongoDB-PostgreSQL SQLServer2 DEV002 SQLServer-MongoDB-PostgreSQL MongoDB2 DEV002 SQLServer-MongoDB-PostgreSQL PostgreSQL4 DEV003 InfluxDB-MySQL-SAPHANA InfluxDB4 DEV003 InfluxDB-MySQL-SAPHANA MySQL4 DEV003 InfluxDB-MySQL-SAPHANA SAPHANA5 DEV004 JAVA-ABAP-C++ JAVA5 DEV004 JAVA-ABAP-C++ ABAP5 DEV004 JAVA-ABAP-C++ C++11 rows selected.