oracle的split函数
资料⼀：
PL/SQL 中没有split函数，需要⾃⼰写。
  代码：
create or replace type type_split as table of varchar2(50);  --创建⼀个  type  ，如果为了使split函数具有通⽤性，请将其size 设⼤些。
--创建function
create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
)  return type_split pipelined
is
l_idx  pls_integer;
v_list  varchar2(50) := p_list;
begin
java中split的用法
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;
测试：
SQL> select * from table(split('northsnow，塞北的雪',','));
COLUMN_VALUE
--------------------------------------------------
northsnow
塞北的雪
SQL>
补充：
-----PIPELINED关键字是什么意思？
---- pipe row是什么意思？
pipelined声名此function是pipe的，如果这么声名了，就必须使⽤pipe row的⽅式把数据返回，常规函数最后的"return 变量"，就变成了"return".
pipelined的function主要是为了提⾼效率，不⽤等所有的数据都处理完成了才返回客户端，它是边处理边返回.适⽤于⼤数据量的交互.
资料⼆：
⽅法⼀：
⾃⼰写的⼀个简单的string的split函数
因为有这个需求，需要切割字符串，在oracle中没有到类似java的split函数，⾃⼰写了⼀个
plsql code:
/**************************************************************************************************************************************************************
1.判断有多少个','号，确定要查的次数len= length(trim(translate(str, replace(str,','),' ')))，那么分割的集合元素个数是len+1
2.循环查','号出现的位置,⽤instr函数，如第1次出现位置是pos1=instr(str,',',1,1)，第2次出现的位置pos2=instr(str,',',1,2),⼀致到第len次出现的位置instr(str,',',1,len)
3.查⼦串:substr函数.第1个⼦串substr(str,1,pos1-1)，第2个⼦串substr(str,pos1+1,pos2-pos1-1),2到n-1依次类堆，到第
len+1个是substr(str,pos_len+1)
function name:str_plit
功能：str_splite(str,pattern)，第⼀个参数是源，第⼆个参数是分割的模式，返回⼀个集合nested table
author:dj
date:2008-09-17
****************************************************************************************************************************************************************
**/
create or replace type t_varray is table of varchar2(20);
create or replace function str_split(str in varchar2,pattern in varchar2) return t_varray
as
len number;--分割的数组元素个数
i number;--position位置
res varchar2(100);
up_len number;--上⼀个位置
down_len number;--下⼀个位置
cstr t_varray:=t_varray();--声明集合
begin
len:=length(trim(translate(str,replace(str,pattern),' ')));        for j in 1..len+1 loop--j是集合元素下标
if j=1 then
i:=instr(str,pattern,1,j);
res:=substr(str,1,i-1);
cstr(j):=res;
up_len:=i;
elsif j<len+1 then
i:=instr(str,pattern,1,j);
down_len:=i;
res:=substr(str,up_len+1,down_len-up_len-1);
cstr(j):=res;
up_len:=i;
else
res:=substr(str,down_len+1);
cstr(j):=res;
end if;
end loop;
return cstr;
end;
--test
/*
declare
str varchar2(100):='aa|bb|cc,dd,ee';
pattern varchar2(10):='|';
res varchar2(10);
cstr t_varray:=t_varray();
begin
cstr:=str_split(str,pattern);
for i in cstr.first .. cstr.last loop
dbms_output.put_line(cstr(i));
end loop;
end;
*/
⽅法⼆：
在⽹上看了⼏个，下⾯发上来
/
**
⽤pipe函数实现
**/
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)          RETURN ty_str_split PIPELINED
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
PIPE ROW (str);
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
PIPE ROW (str);
END IF;
END LOOP;
RETURN;
END fn_split;
/
--测试：
SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));
/**结果：
1
12
123
1234
12345
**/
PLS-00653错误及解决
声明为PIPELINED的函数不能在PL/SQL中使⽤，需要使⽤平⾯函数或者select * bulk collect into tab_oe_list from table(oe_list(pi_hr4u_id))；代替。
如执⾏以下过程将会发⽣错误：
create or replace type hrp_oe_list as table of varchar2(10)；
/
FUNCTION oe_list(pi_hr4u_id IN hrp_users.usr_hr4u_id%TYPE)
RETURN hrp_oe_list
PIPELINED
As
Begin
…
end;
/
然后在PL/SQL中调⽤将会失败：
tab_oe_list hrp_oe_list;
tab_oe_list := oe_list(pi_hr4u_id);
会发⽣PLS-00653错误。
plsql简单的string的split函数和PLS-00653错误

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。