<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> |