Oracle:类似于describe命令的脚本 [英] Oracle: Script similar to describe command

问题描述

系统要求我创建一个脚本，该脚本基本上执行与describe命令相同的功能. 我已经弄清楚了如何编写脚本并获得所需的输出，但是任务的第二部分是复制我所描述的describe命令的格式"，真的有一种方法可以简单地复制该格式吗?

I'm being asked to create a script that basically does the same thing as the describe command. I have figured out how to write the script and get the output I want, but the second part of the assignment is to "duplicate the format of the describe command" I'm stumped, is there really a way to simply duplicate that format?

推荐答案

假设您的查询类似于(请注意，我不会打扰处理每种不同的数据类型)

Assuming your query is something like (note that I'm not bothering to handle every different data type)

select column_name "Name",
       (case when nullable = 'N'
             then 'NOT NULL'
             else null
         end) "Null?",
       (case when data_type = 'DATE'
             then data_type
             when data_type = 'NUMBER' and data_scale > 0
             then data_type || '(' || data_precision || ',' || data_scale || ')'
             when data_type = 'NUMBER' and data_scale = 0
             then data_type || '(' || data_precision || ')'
             when data_type = 'VARCHAR2'
             then data_type || '(' || data_length || ')'
        end) "Type"
  from dba_tab_cols
 where table_name = 'EMP'
 order by column_id

您可以发出一些SQL * Plus命令来格式化输出

You can issue some SQL*Plus commands to format the output

SQL> column "Nmae" format a30;
SQL> column "Null?" format a8;
SQL> column "Type" format a30;

，然后查询的输出将与DESCRIBE命令的输出匹配

and then the output of your query will match up with the output of the DESCRIBE command

SQL> ed
Wrote file afiedt.buf

  1  select column_name "Name",
  2         (case when nullable = 'N'
  3               then 'NOT NULL'
  4               else null
  5           end) "Null?",
  6         (case when data_type = 'DATE'
  7               then data_type
  8               when data_type = 'NUMBER' and data_scale > 0
  9               then data_type || '(' || data_precision || ',' || data_scale || ')'
 10               when data_type = 'NUMBER' and data_scale = 0
 11               then data_type || '(' || data_precision || ')'
 12               when data_type = 'VARCHAR2'
 13               then data_type || '(' || data_length || ')'
 14          end) "Type"
 15    from dba_tab_cols
 16   where table_name = 'EMP'
 17*  order by column_id
SQL> /

Name                           Null?    Type
------------------------------ -------- ------------------------------
EMPNO                          NOT NULL NUMBER(4)
ENAME                                   VARCHAR2(10)
JOB                                     VARCHAR2(9)
MGR                                     NUMBER(4)
HIREDATE                                DATE
SAL                                     NUMBER(7,2)
COMM                                    NUMBER(7,2)
DEPTNO                                  NUMBER(2)

8 rows selected.

这篇关于Oracle:类似于describe命令的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！