使用Oracle的PL/SQL编写自定义函数来实现split

2024-08-25 14:28

本文主要是介绍使用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.

这篇关于使用Oracle的PL/SQL编写自定义函数来实现split的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1105803

相关文章

SpringBoot使用GZIP压缩反回数据问题

《SpringBoot使用GZIP压缩反回数据问题》:本文主要介绍SpringBoot使用GZIP压缩反回数据问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录SpringBoot使用GZIP压缩反回数据1、初识gzip2、gzip是什么,可以干什么?3、Spr

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

Python实现特殊字符判断并去掉非字母和数字的特殊字符

《Python实现特殊字符判断并去掉非字母和数字的特殊字符》在Python中,可以通过多种方法来判断字符串中是否包含非字母、数字的特殊字符,并将这些特殊字符去掉,本文为大家整理了一些常用的,希望对大家... 目录1. 使用正则表达式判断字符串中是否包含特殊字符去掉字符串中的特殊字符2. 使用 str.isa

Spring Boot 集成 Quartz并使用Cron 表达式实现定时任务

《SpringBoot集成Quartz并使用Cron表达式实现定时任务》本篇文章介绍了如何在SpringBoot中集成Quartz进行定时任务调度,并通过Cron表达式控制任务... 目录前言1. 添加 Quartz 依赖2. 创建 Quartz 任务3. 配置 Quartz 任务调度4. 启动 Sprin

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七

Android实现悬浮按钮功能

《Android实现悬浮按钮功能》在很多场景中,我们希望在应用或系统任意界面上都能看到一个小的“悬浮按钮”(FloatingButton),用来快速启动工具、展示未读信息或快捷操作,所以本文给大家介绍... 目录一、项目概述二、相关技术知识三、实现思路四、整合代码4.1 Java 代码(MainActivi

Linux下如何使用C++获取硬件信息

《Linux下如何使用C++获取硬件信息》这篇文章主要为大家详细介绍了如何使用C++实现获取CPU,主板,磁盘,BIOS信息等硬件信息,文中的示例代码讲解详细,感兴趣的小伙伴可以了解下... 目录方法获取CPU信息:读取"/proc/cpuinfo"文件获取磁盘信息:读取"/proc/diskstats"文

Java使用SLF4J记录不同级别日志的示例详解

《Java使用SLF4J记录不同级别日志的示例详解》SLF4J是一个简单的日志门面,它允许在运行时选择不同的日志实现,这篇文章主要为大家详细介绍了如何使用SLF4J记录不同级别日志,感兴趣的可以了解下... 目录一、SLF4J简介二、添加依赖三、配置Logback四、记录不同级别的日志五、总结一、SLF4J

使用Python实现一个优雅的异步定时器

《使用Python实现一个优雅的异步定时器》在Python中实现定时器功能是一个常见需求,尤其是在需要周期性执行任务的场景下,本文给大家介绍了基于asyncio和threading模块,可扩展的异步定... 目录需求背景代码1. 单例事件循环的实现2. 事件循环的运行与关闭3. 定时器核心逻辑4. 启动与停