临时写的记笔记

最近需要用到格式字符，就写了一下，估计存在小bug，不过酬和着用了。

str1 的大小可以根据需要修改

-------------------------------------
--格式化字符串函数 只能一位加间隔符号
--select strutil('12345',',') from dual
--1,2,3,4,5
-------------------------------------
create or replace function strutil(strformat varchar2,args varchar)return varchar2
     as
       i integer:=0;
       j integer:=0;
       str1 varchar2(100) :='';
     begin
       if strformat is null then
          return '';
       end if;
       select length(strformat) into j from dual;
       if j = 1 then
          return strformat;
       end if;
       for i in 1..j loop
            if i = j then
               str1 := str1||substr(strformat,i,1);
            else
               str1 := str1||substr(strformat,i,1)||args;
            end if;
         end loop;
       return str1;
     end strutil;
--------------------------------
select strutil('12',',') from dual;
select null from dual;
-------------------------------------
--格式化字符串函数 扩展 strformat必须是字符串  args需要添加的符号  num 需要相几位分割  必须保持strformat长度正好可以被num整分
--例如：strformat='123456' args='-' num=1/2/3/6或者大于6的数
--num=1  '1-2-3-4-5-6'
--num=2  '12-34-56'
--num=3  '123-456'
--num>=6 '123456'
-------------------------------------
create or replace function strutil_two(strformat varchar2,args varchar,num integer)return varchar2
     as
       i integer:=1;
       j integer:=0;
       k number :=0;
       str1 varchar2(100) :='';
     begin
       if strformat is null then
          return '';
       end if;
       select length(strformat) into k from dual;
       if k <= num then
          return strformat;
       end if;
       select length(strformat)/num into j from dual;
       while i <= j*num loop
            if i = 1 then
               str1 := str1||substr(strformat,1,num)||args;
            elsif i = (j-1)*num+1 then
               str1 := str1||substr(strformat,i,num);
            else
               str1 := str1||substr(strformat,i,num)||args;
            end if;
            i := i+num;
            exit when i > j*num;
         end loop;
       return str1;
     end strutil_two;
--------------------------------
select strutil_two('123456789123',',',6) from dual;