<code class="hljs powershell">--<span class="hljs-number">1、利用 to_number
CREATE OR REPLACE <span class="hljs-keyword">FUNCTION isnumeric(str <span class="hljs-keyword">IN VARCHAR2)
<span class="hljs-keyword">RETURN NUMBER
IS
v_str FLOAT;
<span class="hljs-keyword">BEGIN
<span class="hljs-keyword">IF str IS NULL
THEN
<span class="hljs-keyword">RETURN <span class="hljs-number">0;
<span class="hljs-keyword">ELSE
<span class="hljs-keyword">BEGIN
SELECT TO_NUMBER (str)
INTO v_str
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER
THEN
<span class="hljs-keyword">RETURN <span class="hljs-number">0;
<span class="hljs-keyword">END;
<span class="hljs-keyword">RETURN <span class="hljs-number">1;
<span class="hljs-keyword">END <span class="hljs-keyword">IF;
<span class="hljs-keyword">END isnumeric;
/
--<span class="hljs-number">2、利用 regexp_like
CREATE OR REPLACE <span class="hljs-keyword">FUNCTION isnumeric (str <span class="hljs-keyword">IN VARCHAR2)
<span class="hljs-keyword">RETURN NUMBER
IS
<span class="hljs-keyword">BEGIN
<span class="hljs-keyword">IF str IS NULL
THEN
<span class="hljs-keyword">RETURN <span class="hljs-number">0;
<span class="hljs-keyword">ELSE
<span class="hljs-keyword">IF regexp_like (str, <span class="hljs-string">'^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$')
THEN
<span class="hljs-keyword">RETURN <span class="hljs-number">1;
<span class="hljs-keyword">ELSE
<span class="hljs-keyword">RETURN <span class="hljs-number">0;
<span class="hljs-keyword">END <span class="hljs-keyword">IF;
<span class="hljs-keyword">END <span class="hljs-keyword">IF;
<span class="hljs-keyword">END isnumeric;
/
--<span class="hljs-number">3、利用 translate
CREATE OR REPLACE <span class="hljs-keyword">FUNCTION isnumeric (str <span class="hljs-keyword">IN VARCHAR2)
<span class="hljs-keyword">RETURN NUMBER
IS
v_str VARCHAR2 (<span class="hljs-number">1000);
<span class="hljs-keyword">BEGIN
<span class="hljs-keyword">IF str IS NULL
THEN
<span class="hljs-keyword">RETURN <span class="hljs-number">0;
<span class="hljs-keyword">ELSE
v_str := translate(str, <span class="hljs-string">'.0123456789', <span class="hljs-string">'.');
<span class="hljs-keyword">IF v_str = <span class="hljs-string">'.' OR v_str = <span class="hljs-string">'+.' OR v_str = <span class="hljs-string">'-.' OR v_str IS NULL
THEN
<span class="hljs-keyword">RETURN <span class="hljs-number">1;
<span class="hljs-keyword">ELSE
<span class="hljs-keyword">RETURN <span class="hljs-number">0;
<span class="hljs-keyword">END <span class="hljs-keyword">IF;
<span class="hljs-keyword">END <span class="hljs-keyword">IF;
<span class="hljs-keyword">END isnumeric;
/</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>