写了一个字符串内排序的函数,但是总是报错,请大神看下,谢谢!
create or replace function SP_STRSORTFUNC(str in VARCHAR2, split_param in VARCHAR2) return varchar2 as
t_temp varchar2(2048);---获取的数组项内容
t_str varchar2(2048);---返回的内容
split_param_count number;----参数的个数
t_begin number :=0;--循环变量开始参数
t_end number := 1;--截取字符长度的开始位置结束参数
t_length number :=0;--字符串的长度
t_paramLength number :=0;--传入字符串截取参数的长度
i_temp number:=0; ----标识位
v_strsql VARCHAR(300);----执行的语句
begin
---创建临时表
select count(1) INTO i_temp from all_tables where lower(table_name)='table_temp' and owner='TEST' ;
IF(i_temp>=1) THEN
v_strsql :='drop table table_temp';
execute immediate v_strsql;
i_temp:=0;
END IF;
v_strsql :='CREATE TABLE table_temp (item varchar2(2048))';
execute immediate v_strsql;
/*----判断 split_param在str中出现了多少次
select LENGTH(REGEXP_REPLACE(REPLACE(str, '+', '@'), '[^@]+', '')) into split_param_count from dual;----获取参数的个数*/
t_length := length(str);--获取传入字符的长度
t_paramLength := length(split_param);--截取参数的长度
WHILE t_begin <t_length loop
--起始位置从1开始截取匹配split_param字符串并返回他所在的位置
t_begin := instr(str,split_param,t_end);
IF t_begin = 0 THEN
t_begin := t_length;
t_temp := SUBSTR (str, t_end,t_begin);
v_strsql :='insert into table_temp select '||t_temp||' from dual';
execute immediate v_strsql;
commit;
IF t_end >= t_length THEN
EXIT;
END IF;
ELSE
IF t_end >= t_length THEN
EXIT;
END IF;
t_temp := SUBSTR(str,t_end,t_begin-t_end); ---获取的临时项内容
t_end := t_begin+t_paramLength;
----将临时项插入临时表
v_strsql :='insert into table_temp select '||t_temp||' from dual';
execute immediate v_strsql;
commit;
END IF;
END LOOP;
--------------将临时表内的字符串进行拼接
select wmsys.wm_concat(item) into t_str from table_temp order by item;
return(t_str);
end SP_STRSORTFUNC;
报错如下:
错误:PLS-00103: Encountered the symbol "?" when expecting one of the following:
. ( * @ % & - + / at loop mod remainder rem
<an exponent (**)> and or || multiset
The symbol "?" was ignored.
行:25
文本:WHILE t_begin <t_length loop
错误:PLS-00103: Encountered the symbol "?" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
行:28
文本:IF t_begin = 0 THEN