create or replace function numofstr(par in clob, str in varchar2)
--这个函数输出str在par中重复多少次
return number is
f_num number := 0;
f_clob clob;
f_instr number := -1;
f_strlength number := 0;
f_cloblength number := 0;
begin
f_instr := dbms_lob.instr(par, str);
if (f_instr < 0) then
return f_num;
end if;
f_strlength := dbms_lob.getlength(str);
f_clob := par;
f_cloblength := dbms_lob.getlength(f_clob);
while f_instr > 0 loop
f_num := f_num + 1;
f_clob := dbms_lob.substr(f_clob,
f_cloblength - f_instr + f_strlength,
f_instr + f_strlength);
f_instr := dbms_lob.instr(f_clob, str);
f_cloblength := dbms_lob.getlength(f_clob);
end loop;
return f_num;
exception
when others then
dbms_output.put_line(' error occurs!');
end;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/11813230/viewspace-628630/ ，如需转载，请注明出处，否则将追究法律责任。
