Oralce自定义函数实现字符串分割 Myth丶恋晨 2022-04-14 05:21 415阅读 0赞 1.定义自定义类型,函数返回自定义类型 SQL> create or replace type str\_split is table of varchar2(1000); 2 / Type created 2.用一个Function来实现字符串分割的功能。 /\* \* 功能:字符串分割。 \* 参数:p\_str 分割的字符串 \* p\_delimiter 分隔符 \*/ create or replace function fun\_str\_split(p\_str in varchar2, p\_delimiter in varchar2) return str\_split pipelined is v\_start number := 1; --开始查找的位置 v\_index number := 0; --查找到的索引 v\_length number := length(p\_str); --字符串长度 begin while (v\_start <= v\_length) loop --从开始位置开始查找,每次查找完毕更新开始位置为查找到的位置。 v\_index := instr(p\_str, p\_delimiter, v\_start); if v\_index = 0 then --未找到,说明结束了 pipe row(substr(p\_str, v\_start)); v\_start := v\_length + 1; else pipe row(substr(p\_str, v\_start, v\_index - v\_start)); v\_start := v\_index + 1;--查找到的索引 + ','占的长度 end if; end loop; return; end; 3.测试 SQL> select \* from table(fun\_str\_split('hello,world,abc',',')); COLUMN\_VALUE \-------------------------------------------------------------------------------- hello world abc //注意上面使用了column\_value,这是一个PIPELINED function返回的默认列的名字。 关于pipelined(摘自[http://www.linuxidc.com/Linux/2011-05/35797.htm][http_www.linuxidc.com_Linux_2011-05_35797.htm]) pipelined是Oracle的一个关键字。 普通函数的结尾加一个pipelined关键字,就是管道函数。 这个函数的返回参数类型为集合,这是为了使其能作为表函数使用。 表函数就是在from子句中以table(v\_resultset)调用的,v\_resultset就是一个集合类型的参数。 管道化表函数必须返回一个集合。 在函数中,PIPE ROW 语句被用来返回该集合的单个元素,该函数必须以一个空的 RETURN 语句结束,以表明它已经完成。 一旦我们创建了上述函数,我们就可以使用 TABLE 操作符从 SQL 查询中调用它。 //典型的Pipelined例子 //当使用PL/SQL表函数时,下面是典型的步骤 //生产函数必须在其定义中使用PIPELINED关键字 //生产函数必须使用一个out参数,这个参数是一个集合,对应到返回的结果集 //一旦有结果产生,通过使用PIPE ROW关键字将其输送给消费函数 //生产函数必须以RETURN语句结束,但是不需要制定返回值 //消费函数必须使用TABLE关键字,将从PIPELINE function返回的行集当成一个常规表 //第一步.定义一个返回的行集的格式。 //在这个实例中,我们返回这三个类型的值:int,date,varchar2(25) CREATE OR REPLACE TYPE myObjectFormat AS OBJECT ( A INT, B DATE, C VARCHAR2(25) ) / //下一步,为第一步定义的类型定义一个集合类型 CREATE OR REPLACE TYPE myTableType AS TABLE OF myObjectFormat / //最后,生产函数被打包到一个包里面,它是一个pipelined function就像被pipelined关键字所标记一样。 CREATE OR REPLACE PACKAGE myDemoPack AS FUNCTION prodFunc RETURN myTableType PIPELINED; END; / CREATE OR REPLACE PACKAGE BODY myDemoPack AS FUNCTION prodFunc RETURN myTableType PIPELINED IS BEGIN FOR i in 1 .. 5 LOOP PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i)); END LOOP; RETURN; END; END; / //测试结果: alter session set nls\_date\_format='yyyy-mm-dd'; SELECT \* FROM TABLE(myDemoPack.prodFunc()); A B C \----------------- ----------- ------------ 1 2011-05-05 Row 1 2 2011-05-06 Row 2 3 2011-05-07 Row 3 4 2011-05-08 Row 4 5 2011-05-09 Row 5 //结论: //在一个select语句里面,我们需要一个数据源,而不是一张表的话,Pipelined functions非常有用。 [http_www.linuxidc.com_Linux_2011-05_35797.htm]: http://www.linuxidc.com/Linux/2011-05/35797.htm
还没有评论,来说两句吧...