解決方法：利用Oracle數(shù)據(jù)庫自帶的TO_NUMBER函數(shù)（增加了異常處理部分，防止非數(shù)字類型導(dǎo)致函數(shù)異常而中斷執(zhí)行）。

SQL> CREATE OR REPLACE FUNCTION F_IS_NUM(P_NUM IN VARCHAR2) RETURN VARCHAR2 AS

2 V_TMP NUMBER;

3 BEGIN

4 IF P_NUM IS NULL THEN

5 RETURN NULL;

6 END IF;

7 V_TMP := TO_NUMBER(P_NUM);

8 RETURN 'T';

9 EXCEPTION

10 WHEN OTHERS THEN

11 RETURN 'N';

12 END;

13 /

函數(shù)已創(chuàng)建。

SQL> CREATE OR REPLACE FUNCTION F_IS_NUM1(P_NUM IN VARCHAR2) RETURN VARCHAR2 AS

2 V_NUM_DOT NUMBER DEFAULT 0;

3 BEGIN

4 IF P_NUM IS NULL THEN

5 RETURN NULL;

6 END IF;

7 FOR I IN 1..LENGTH(P_NUM) LOOP

8 CASE SUBSTR(P_NUM, I, 1)

9 WHEN '0' THEN NULL;

10 WHEN '1' THEN NULL;

11 WHEN '2' THEN NULL;

12 WHEN '3' THEN NULL;

13 WHEN '4' THEN NULL;

14 WHEN '5' THEN NULL;

15 WHEN '6' THEN NULL;

16 WHEN '7' THEN NULL;

17 WHEN '8' THEN NULL;

18 WHEN '9' THEN NULL;

19 WHEN '.' THEN

20 V_NUM_DOT := V_NUM_DOT + 1;

21 IF V_NUM_DOT > 1 THEN

22 RETURN 'N';

23 END IF;

24 WHEN '-' THEN

25 IF I != 1 THEN

26 RETURN 'N';

27 END IF;

28 WHEN '+' THEN

29 IF I != 1 THEN

30 RETURN 'N';

31 END IF;

32 ELSE RETURN 'N';

33 END CASE;

34 END LOOP;

35 RETURN 'T';

36 END;

37 /

函數(shù)已創(chuàng)建。

SQL> CREATE TABLE T (NUM_STR VARCHAR2(100));

表已創(chuàng)建。

SQL> INSERT INTO T VALUES ('-5');

已創(chuàng)建 1 行。

SQL> INSERT INTO T VALUES ('2.2342');

已創(chuàng)建 1 行。

SQL> INSERT INTO T VALUES ('+123.1234');

已創(chuàng)建 1 行。

SQL> INSERT INTO T VALUES ('-5-34');

已創(chuàng)建 1 行。

SQL> INSERT INTO T VALUES ('1230234J342');

已創(chuàng)建 1 行。

SQL> INSERT INTO T VALUES ('5.524.2');

已創(chuàng)建 1 行。

SQL> COMMIT;

提交完成。

SQL>

SQL> COL NUM_STR FORMAT A12

SQL> COL IS_NUM FORMAT A6

SQL> SELECT NUM_STR, F_IS_NUM(NUM_STR) IS_NUM FROM T;

NUM_STR IS_NUM

------------ ------

-5 T

2.2342 T

+123.1234 T

-5-34 N

1230234J342 N

5.524.2 N

已選擇6行。

SQL> SELECT NUM_STR, F_IS_NUM1(NUM_STR) IS_NUM FROM T;

NUM_STR IS_NUM

------------ ------

-5 T

2.2342 T

+123.1234 T

-5-34 N

1230234J342 N

5.524.2 N

已選擇6行。

采用第二種方法在處理較大數(shù)據(jù)量且其中大部分為非數(shù)字類型的數(shù)據(jù)時(shí)，效率較高。

SQL> SET AUTOT TRACE STAT

SQL> SET TIMING ON

SQL> SELECT F_IS_NUM(OBJECT_ID) FROM DBA_OBJECTS;

已選擇6291行。

已用時(shí)間: 00: 00: 00.04

Statistics

----------------------------------------------------------

7 recursive calls

0 db block gets

4809 consistent gets

0 physical reads

0 redo size

82590 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows PRocessed

SQL> SELECT F_IS_NUM(OBJECT_ID) FROM DBA_OBJECTS;

已選擇6291行。

已用時(shí)間: 00: 00: 00.04

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4807 consistent gets

0 physical reads

0 redo size

82590 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM1(OBJECT_ID) FROM DBA_OBJECTS;

已選擇6291行。

已用時(shí)間: 00: 00: 00.04

Statistics

----------------------------------------------------------

7 recursive calls

0 db block gets

4809 consistent gets

0 physical reads

0 redo size

82591 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM1(OBJECT_ID) FROM DBA_OBJECTS;

已選擇6291行。

已用時(shí)間: 00: 00: 00.04

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4807 consistent gets

0 physical reads

0 redo size

82591 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

假如輸入的參數(shù)以數(shù)字類型為主，則兩者效率差不多。

假如輸入的參數(shù)大部分無法轉(zhuǎn)化為數(shù)字類型，則第二種方法的效率會更高。

SQL> SELECT F_IS_NUM(OBJECT_NAME) FROM DBA_OBJECTS;

已選擇6291行。

已用時(shí)間: 00: 00: 00.08

Statistics

----------------------------------------------------------

7 recursive calls

0 db block gets

4809 consistent gets

0 physical reads

0 redo size

82591 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM(OBJECT_NAME) FROM DBA_OBJECTS;

已選擇6291行。

已用時(shí)間: 00: 00: 00.07

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4807 consistent gets

0 physical reads

0 redo size

82591 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM1(OBJECT_NAME) FROM DBA_OBJECTS;

已選擇6291行。

已用時(shí)間: 00: 00: 00.04

Statistics

----------------------------------------------------------

7 recursive calls

0 db block gets

4809 consistent gets

0 physical reads

0 redo size

82592 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

SQL> SELECT F_IS_NUM1(OBJECT_NAME) FROM DBA_OBJECTS;

已選擇6291行。

已用時(shí)間: 00: 00: 00.04

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4807 consistent gets

0 physical reads

0 redo size

82592 bytes sent via SQL*Net to client

5112 bytes received via SQL*Net from client

421 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6291 rows processed

假如不采用TO_NUMBER而是使用對字符串中每個(gè)字符依次判斷的方法，則會增加復(fù)雜的程度。