是否可以描述一个表并首先显示NOTNULL列? [英] Is it Possible to Describe a table and show the NOTNULL columns first?

问题描述

我不能在DESC中使用ORDER BY， 还有其他方法吗?

i cant use ORDER BY with DESC, are there any other ways?

推荐答案

您不能使用内置的describe命令执行此操作，否.但是您可以构建自己的查询(如此处)，但可以在order-by子句中包含可空标志:

You can't do that with the built-in describe command, no. But you can build your own query (as here) but include the nullable flag in the order-by clause:

select column_name as "Column",
  case when nullable = 'N' then 'NOT NULL' end as "Null?",
  cast (data_type || case 
    when data_type in ('VARCHAR2', 'CHAR', 'TIMESTAMP')
      then '(' || data_length || ')'
    when data_type in ('NUMBER')
        and (data_precision is not null or data_scale is not null)
      then '(' || data_precision || case
        when data_scale > 0 then ',' || data_scale
      end || ')'
    end as varchar2(30)) as "Type"
  from user_tab_columns
 where table_name = 'YOUR_TABLE'
 order by nullable, column_id;

使用创建的表:

create table t42 (id number primary key, col1 varchar2(10),
  col2 number(10,3) not null, col3 date);

该查询将为您提供:

Column                         Null?    Type                         
------------------------------ -------- ------------------------------
ID                             NOT NULL NUMBER                        
COL2                           NOT NULL NUMBER(10,3)                  
COL1                                    VARCHAR2(10)                  
COL3                                    DATE                          

如果要大量使用它，可以使它成为传递表名的流水线函数.不过，这不会解析同义词或描述存储的程序.

If you're going to use it a lot you could make it a pipelined function that you pass a table name to, perhaps. This won't resolve synonyms or describe stored programs, though.

这篇关于是否可以描述一个表并首先显示NOTNULL列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！