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屋!