create function is_number(p_str in varchar2) return number is begin for i in 1..length(p_str) loop if substr(p_str,i,1) not in (1,2,3,4,5,6,7,8,9,0) then return 0; end if; end loop; return to_number(p_str); end; /
----------------------- --収支年月判断(英数字) ----------------------- FOR i IN 1..LENGTHB(jigyousyo) LOOP IF SUBSTRB(jigyousyo, i, 1) NOT IN ( '1', '2', '3', '4', '5', '6', '7', '8', '9', '0', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z' ) THEN RAISE my_suji_error; END IF; END LOOP;如何优化呢??是不是太冗余了
FOR i IN 1..LENGTHB(jigyousyo) LOOP IF ascii(SUBSTRB(jigyousyo, i, 1))>=49 and ascii(SUBSTRB(jigyousyo, i, 1))<=57 THEN --是数字 elsif ascii(SUBSTRB(jigyousyo, i, 1))>=65 and ascii(SUBSTRB(jigyousyo, i, 1))<=90 then --是大写英文 elsif ascii(SUBSTRB(jigyousyo, i, 1))>=97 and ascii(SUBSTRB(jigyousyo, i, 1))<=122 then --是小写英文 else --除了数字和英文52字母以外的字符. END IF; END LOOP;
return number
is
begin
for i in 1..length(p_str) loop
if substr(p_str,i,1) not in (1,2,3,4,5,6,7,8,9,0) then
return 0;
end if;
end loop;
return to_number(p_str);
end;
/
--収支年月判断(英数字)
-----------------------
FOR i IN 1..LENGTHB(jigyousyo) LOOP
IF SUBSTRB(jigyousyo, i, 1) NOT IN (
'1', '2', '3', '4', '5', '6', '7', '8', '9', '0',
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n',
'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'
)
THEN
RAISE my_suji_error;
END IF;
END LOOP;如何优化呢??是不是太冗余了
IF ascii(SUBSTRB(jigyousyo, i, 1))>=49 and ascii(SUBSTRB(jigyousyo, i, 1))<=57 THEN
--是数字
elsif ascii(SUBSTRB(jigyousyo, i, 1))>=65 and ascii(SUBSTRB(jigyousyo, i, 1))<=90 then
--是大写英文
elsif ascii(SUBSTRB(jigyousyo, i, 1))>=97 and ascii(SUBSTRB(jigyousyo, i, 1))<=122 then
--是小写英文
else
--除了数字和英文52字母以外的字符.
END IF;
END LOOP;