问题:判断一个字符串的内容是否是数值。
解决方法：利用Oracle数据库自带的TO_NUMBER函数（增加了异常处理部分，防止非数字类型导致函数异常而中断执行）。
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 /
函数已创建。
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 /
函数已创建。
SQL> CREATE TABLE T (NUM_STR VARCHAR2(100));
表已创建。
SQL> INSERT INTO T VALUES ('-5');
已创建 1 行。
SQL> INSERT INTO T VALUES ('2.2342');
已创建 1 行。
SQL> INSERT INTO T VALUES ('+123.1234');
已创建 1 行。
SQL> INSERT INTO T VALUES ('-5-34');
已创建 1 行。
SQL> INSERT INTO T VALUES ('1230234J342');
已创建 1 行。
SQL> INSERT INTO T VALUES ('5.524.2');
已创建 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行。
采用第二种方法在处理较大数据量且其中大部分为非数字类型的数据时，效率较高。
SQL> SET AUTOT TRACE STAT
SQL> SET TIMING ON
SQL> SELECT F_IS_NUM(OBJECT_ID) FROM DBA_OBJECTS;
已选择6291行。
已用时间: 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行。
已用时间: 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行。
已用时间: 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行。
已用时间: 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
假如输入的参数以数字类型为主，则两者效率差不多。
假如输入的参数大部分无法转化为数字类型，则第二种方法的效率会更高。
SQL> SELECT F_IS_NUM(OBJECT_NAME) FROM DBA_OBJECTS;
已选择6291行。
已用时间: 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行。
已用时间: 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行。
已用时间: 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行。
已用时间: 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而是使用对字符串中每个字符依次判断的方法，则会增加复杂的程度。 |
