create
or
replace
function
addSingleQuotes(
v_oldStr VARCHAR2
)
return
VARCHAR2
--该函数将逗号分隔的字符串的每个字串两边加上单引号
--如0001,0002,0003改为'0001','0002','0003'
as
oldStr VARCHAR2(4000):=v_oldStr;
newStr VARCHAR2(4000):=
null
;
--返回加单引号之后的值
v_commaPos number(4);
--逗号位置
v_curItem VARCHAR2(4000):=
''
;
--当前添加项(不包含单引号)
begin
loop
exit
when
oldStr
is
null
;
v_commaPos := instr(oldStr,
','
);
--v_startPos
if(v_commaPos=0)
then
--没有找到
v_curItem:=oldStr;
oldStr:=
null
;
--停止查找
else
v_curItem:=substr(oldStr,1,v_commaPos-1);
--修改oldStr
oldStr:=substr(oldStr,v_commaPos+1,length(oldStr)-length(v_curItem)-1);
end
if;
--dbms_output.put_line('oldStr'||oldStr||'当前添加项:'||v_curItem||' 原来newStr:'||newStr);
if(newStr
is
null
)
then
newStr:=
''
''
||v_curItem||
''
''
;
else
newStr:=newStr||
','
''
||v_curItem||
''
''
;
end
if;
end
loop;
return
newStr;
end
;