create or replace function f1(p1 varchar2) return varchar2 is a char(1); b varchar2(20); c varchar2(20); d varchar2(20); begin a :=substr(p1,0,1); if (a = '-' or a = '+' ) then b :=substr(p1,2,instr(p1,'.',2,1)-2); c := lpad(b,15,'0'); d := rpad(substr(p1,instr(p1,'.',1,1)+1),3,'0'); else a := '+'; b :=substr(p1,0,instr(p1,'.',1,1)-1); c := lpad(b,15,'0'); d := rpad(substr(p1,instr(p1,'.',1,1)+1),3,'0'); end if; return a || c || '.' || d; end; /
SQL> declare 2 aa varchar2(20); 3 begin 4 aa :=f1('12345.5'); 5 dbms_output.put_line(aa); 6 end; 7 /
+000000000012345.500
PL/SQL procedure successfully completed
--直接to_char是可以的 select to_char(12345.5,'S000000000000000.000') from dual;
单句sql也可以 就是太麻烦了
不知道有没有更好的方法
a char(1);
b varchar2(20);
c varchar2(20);
d varchar2(20);
begin
a :=substr(p1,0,1);
if (a = '-' or a = '+' ) then
b :=substr(p1,2,instr(p1,'.',2,1)-2);
c := lpad(b,15,'0');
d := rpad(substr(p1,instr(p1,'.',1,1)+1),3,'0');
else
a := '+';
b :=substr(p1,0,instr(p1,'.',1,1)-1);
c := lpad(b,15,'0');
d := rpad(substr(p1,instr(p1,'.',1,1)+1),3,'0');
end if;
return a || c || '.' || d;
end;
/
2 aa varchar2(20);
3 begin
4 aa :=f1('12345.5');
5 dbms_output.put_line(aa);
6 end;
7 /
+000000000012345.500
PL/SQL procedure successfully completed
select to_char(12345.5,'S000000000000000.000') from dual;