今天同事遇到下面一个问题：

order by decode(column_id,1,null,2,null,3,null,column_id);

有个问题，就是当列数大于10列时，column_id 的顺序成10，11，12，13，4，5，6，7，8，9了

这个排序的主要目的是让前3列排在后面，这3列的顺序无所谓。

对于小于10列的表是没问题的：

SQL> create table t(c1 number,c2 number,c3 number,c4 number,c5 number);

表已创建。

SQL> col column_name format a20

SQL> select column_name,column_id

2  from  user_tab_columns

3  where table_name='T'

4  order by decode(column_id,1,null,2,null,3,null,column_id);

COLUMN_NAME           COLUMN_ID

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

C4                            4

C5                            5

C2                            2

C1                            1

C3                            3

但是当表的列数大于10的时候就会混乱了。

SQL> select column_name,column_id, decode(column_id,1,null,2,null,3,null,column_id) sortcolumn

2  from  user_tab_columns

3  where table_name='T'

4  order by decode(column_id,1,null,2,null,3,null,column_id)

5  /

COLUMN_NAME           COLUMN_ID SORTCOLUMN

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

C10                          10 10

C11                          11 11

C12                          12 12

C4                            4 4

C5                            5 5

C6                            6 6

C7                            7 7

C8                            8 8

C9                            9 9

C3                            3

C2                            2

C1                            1

已选择12行。

显然ORACLE把SORTCOLUMN列作为为字符类型排序了。

加个TO_NUMBER即可解决这个问题。

SQL> select column_name,column_id, decode(column_id,1,null,2,null,3,null,column_id) sortcolumn

2  from  user_tab_columns

3  where table_name='T'

4  order by to_number(decode(column_id,1,null,2,null,3,null,column_id));

COLUMN_NAME           COLUMN_ID SORTCOLUMN

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

C4                            4 4

C5                            5 5

C6                            6 6

C7                            7 7

C8                            8 8

C9                            9 9

C10                          10 10

C11                          11 11

C12                          12 12

C1                            1

C3                            3

C2                            2

已选择12行。

但是为什么会导致这个问题，DECODE函数为何返回了字符类型。

这个问题yangtingkun大师专门写个几篇文章介绍。

有兴趣的可以找找看看。

在这里我借花献佛简单稍微说一下：

对于NULL 类型,ORACLE的默认返回类型是VARCHAR。

对于DECODE函数 ORACLE返回的类型依赖于第一个值。

如下所示：

SQL> CREATE TABLE A AS SELECT DECODE(DUMMY,'X',1,'Y','2',DUMMY) C1,

2  DECODE(DUMMY,'X','1','Y',2,DUMMY) C2 ,

3  DECODE(DUMMY,'X',NULL,'Y','HUATENG',DUMMY) C3 FROM DUAL;

表已创建。

SQL> DESC A

名称                                      是否为空? 类型

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

C1                                                 NUMBER

C2                                                 VARCHAR2(1)

C3                                                 VARCHAR2(7)

也正是因为DECODE函数的这种依赖于第一次的值类型作为返回类型，对于其他返回的值如果和第一个类型不匹配

，可能会让你遇到很蛋疼的问题：

SQL> DESC A;

名称                                      是否为空? 类型

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

C1                                                 NUMBER

C2                                                 VARCHAR2(1)

C3                                                 VARCHAR2(7)

SQL> INSERT INTO A VALUES(2,2,2);

已创建 1 行。

SQL> SELECT * FROM A;

C1 C2 C3

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

1 1

2 2  2

SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A;

ERROR:

ORA-01722: 无效数字

未选定行

SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=1;

DECODE(C1,1,1,2,'E',C1)

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

1

SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=2;

SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=2

*

第 1 行出现错误:

ORA-01722: 无效数字

上面的问题主要是字符'E'无法转为整数类型导致的。

阅读(3488) | 评论(0) | 转发(0) |