此函数用来判断传入的字符串参数是否全是字母数字的组合

 

create or replace function fun_onlynumchar(str in varchar2,flag in number) return integer is
  len  number;
  temp varchar2(10);
begin
  if str is null then
    return(0);
  end if;

  len := length(str);
  if flag=2 then
    for i in 1 .. len loop
      temp := substr(str, i, 1);
      if ascii(temp) < 48 then
        return(0);
      elsif ascii(temp) > 57 and ascii(temp) < 65 then
        return(0);
      elsif ascii(temp) > 90 and ascii(temp) < 97 then
        return(0);
      elsif ascii(temp) > 122 then
        return(0);
      end if;
    end loop;
  elsif flag=0 then
 
    for i in 1 .. len loop
      temp := substr(str, i, 1);
      if ascii(temp) < 48 then
        return(0);
      elsif ascii(temp) > 57 then
        return(0);
      end if;
    end loop;
 
  elsif flag=1 then
 
    for i in 1 .. len loop
      temp := substr(str, i, 1);
      if ascii(temp) < 65 then
        return(0);
      elsif ascii(temp) > 90 and ascii(temp) < 97 then
        return(0);
      elsif ascii(temp) > 122 then
        return(0);
      end if;
    end loop;
 
  end if;
  return(1);
 
end fun_onlynumchar;