00653 oracle,plsql简单的切割字符串函数

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(str)-length(replace(str,pattern,''));-- 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.extend(1);

cstr(j):=res;

up_len:=i;

elsif j

i:=instr(str,pattern,1,j);

down_len:=i;

res:=substr(str,up_len+1,down_len-up_len-1);

cstr.extend(1);

cstr(j):=res;

up_len:=i;

else

res:=substr(str,down_len+1);

cstr.extend(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错误。 方法三：

将表格的某一列转换成逗号分隔的字符串的另类方法

A：dbms_utility.table_to_comma的使用

一：首先建立测试表

create table table1

(

dz varchar2(100)

)

insert into table1 values('v');

insert into table1 values('dywm');

commit;

SQL> select * from table1;

DZ

--------------------------------------------------------------------------------

v

dywm

二：建立存储过程 create or replace procedure test_column_row as qq1 dbms_utility.uncl_array; l_tablen number; l_tab varchar2(1000);