Oralce自定义函数实现字符串分割

Myth丶恋晨 2022-04-14 05:21 485阅读 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