oracle 检查字符中数据类型 函数&存储过程

dump函数用来判断字符串中的字符类型是属于number或者string

存储过程

create or replace procedure pro_isNum(I_STRING in varchar2,o_note out varchar2)
 as
        string1 varchar2(1000);--字段通过dump()转换后
        num1 number(20);--字段中字符的个数
        num2 number(20);--ASCII比较变量
        i number(20);--循环累积变量
 begin
   IF I_String is not null then
      select
            ','||substr(dump(I_STRING),instr(dump(I_STRING),':')+1,length(dump(I_STRING))-instr(dump(I_STRING),':'))||','
           into string1 from dual;
   select nvl(length(string1)-length(replace(string1,',','')),-1) into num1 from dual;
     i:=1;
     o_note:='number';
     <<first_loop>>
       loop
         if i =num1 then
            dbms_output.put_line('循环已结束数据类型为'||o_note);
          exit first_loop;
         end if;
         select to_number(substr(string1,instr(string1,',',1,i)+1,instr(string1,',',1,i+1)-instr(string1,',',1,i)-1)) into num2 from dual;
         IF num2<48 or num2>57 then
           o_note:='string';
           return;
         end IF;
         i:=i+1;
      end loop fst_loop;
      dbms_output.put_line('LOOP循环已经结束！');
   else
     o_note:='null';
   end if;
 end;

 

函数

create or replace function func_isNum(I_STRING in varchar2)
 return varchar2--返回类型
 is
        string1 varchar2(1000);--字段通过dump()转换后
        num1 number(20);--字段中字符的个数
        num2 number(20);--ASCII比较变量
        i number(20);--循环累积变量
        o_note varchar2(20);--返回类型
 begin
   IF I_String is not null then
      select
            ','||substr(dump(I_STRING),instr(dump(I_STRING),':')+1,length(dump(I_STRING))-instr(dump(I_STRING),':'))||','
           into string1 from dual;
   select nvl(length(string1)-length(replace(string1,',','')),-1) into num1 from dual;
     i:=1;
     o_note:='number';
     <<first_loop>>
       loop
         if i =num1 then
            dbms_output.put_line('循环已结束数据类型为'||o_note);
          exit first_loop;
         end if;
         select to_number(substr(string1,instr(string1,',',1,i)+1,instr(string1,',',1,i+1)-instr(string1,',',1,i)-1)) into num2 from dual;
         IF num2<48 or num2>57 then
           o_note:='string';
         end IF;
         i:=i+1;
      end loop fst_loop;
      dbms_output.put_line('LOOP循环已经结束！');
   else
     o_note:='null';
   end if;
   return o_note;
 end;