如何格式化 Oracle SQL 纯文本选择输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/144167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to format Oracle SQL text-only select output
提问by Ogre Psalm33
I am using Oracle SQL (in SQLDeveloper, so I don't have access to SQLPLUS commands such as COLUMN) to execute a query that looks something like this:
我正在使用 Oracle SQL（在 SQLDeveloper 中，因此我无权访问 SQLPLUS 命令，例如 COLUMN）来执行如下所示的查询：
select assigner_staff_id as staff_id, active_flag, assign_date,
complete_date, mod_date
from work where assigner_staff_id = '2096';
The results it give me look something like this:
它给我的结果是这样的：
STAFF_ID ACTIVE_FLAG ASSIGN_DATE COMPLETE_DATE MOD_DATE ---------------------- ----------- ------------------------- ------------------------- ------------------------- 2096 F 25-SEP-08 27-SEP-08 27-SEP-08 02.27.30.642959000 PM 2096 F 25-SEP-08 25-SEP-08 25-SEP-08 01.41.02.517321000 AM 2 rows selected
This can very easily produce a very wide and unwieldy textual report when I'm trying to paste the results as a nicely formatted quick-n-dirty text block into an e-mail or problem report, etc. What's the best way to get rid of all tha extra white space in the output columns when I'm using just plain-vanilla Oracle SQL? So far all my web searches haven't turned up much, as all the web search results are showing me how to do it using formatting commands like COLUMN in SQLPLUS (which I don't have).
当我尝试将结果作为格式良好的快速脏文本块粘贴到电子邮件或问题报告等中时，这很容易产生非常宽泛且笨拙的文本报告。 摆脱的最佳方法是什么当我只使用普通的 Oracle SQL 时，输出列中的所有额外空格？到目前为止，我所有的网络搜索都没有出现太多结果，因为所有网络搜索结果都向我展示了如何使用 SQLPLUS 中的 COLUMN 等格式命令（我没有）进行搜索。
采纳答案by Thomas Jones-Low
What are you using to get the results? The output you pasted looks like it's coming from SQL*PLUS. It may be that whatever tool you are using to generate the results has some method of modifying the output.
你用什么来得到结果？您粘贴的输出看起来像是来自 SQL*PLUS。可能是您用来生成结果的任何工具都有一些修改输出的方法。
By default Oracle outputs columns based upon the width of the title or the width of the column data which ever is wider.
默认情况下，Oracle 根据标题的宽度或列数据的宽度（以较宽者为准）输出列。
If you want make columns smaller you will need to either rename them or convert them to text and use substr() to make the defaults smaller.
如果您想让列更小，您需要重命名它们或将它们转换为文本并使用 substr() 使默认值更小。
select substr(assigner_staff_id, 8) as staff_id,
active_flag as Flag,
to_char(assign_date, 'DD/MM/YY'),
to_char(complete_date, 'DD/MM/YY'),
mod_date
from work where assigner_staff_id = '2096';
回答by Jim Clouse
In your statement, you can specify the type of output you're looking for:
在您的语句中，您可以指定您要查找的输出类型：
select /*csv*/ col1, col2 from table;
select /*Delimited*/ col1, col2 from table;
there are other formats available such as xml, html, text, loader, etc.
还有其他可用的格式，例如 xml、html、text、loader 等。
You can change the formatting of these particular options under tools > preferences > Database > Utilities > Export
您可以在工具 > 首选项 > 数据库 > 实用程序 > 导出下更改这些特定选项的格式
Be sure to choose Run Script rather than Run Statement.
请务必选择 Run Script 而不是 Run Statement。
* this is for Oracle SQL Developer v3.2
* 这是针对 Oracle SQL Developer v3.2
回答by Thomas Jones-Low
What you can do with sql is limited by your tool. SQL Plus has commands to format the columns but they are not real easy to use.
您可以使用 sql 执行的操作受您的工具的限制。SQL Plus 有格式化列的命令，但它们并不容易使用。
One quick approach is to paste the output into excel and format it there or just attach the spreadsheet. Some tools will save the output directly as a spreadsheet.
一种快速的方法是将输出粘贴到 excel 中并在那里格式化，或者只是附加电子表格。一些工具会将输出直接保存为电子表格。
回答by Matthew Watson
If you don't have alot of rows returned I'll often use Tom Kytes print_tablefunction.
如果您没有返回很多行，我会经常使用Tom Kytes print_table函数。
SQL> set serveroutput on
SQL> execute print_table('select * from all_objects where rownum < 3');
OWNER : SYS
OBJECT_NAME : /1005bd30_LnkdConstant
SUBOBJECT_NAME :
OBJECT_ID : 27574
DATA_OBJECT_ID :
OBJECT_TYPE : JAVA CLASS
CREATED : 22-may-2008 11:41:13
LAST_DDL_TIME : 22-may-2008 11:41:13
TIMESTAMP : 2008-05-22:11:41:13
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
-----------------
OWNER : SYS
OBJECT_NAME : /10076b23_OraCustomDatumClosur
SUBOBJECT_NAME :
OBJECT_ID : 22390
DATA_OBJECT_ID :
OBJECT_TYPE : JAVA CLASS
CREATED : 22-may-2008 11:38:34
LAST_DDL_TIME : 22-may-2008 11:38:34
TIMESTAMP : 2008-05-22:11:38:34
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
-----------------
PL/SQL procedure successfully completed.
SQL>
If its lots of rows, i'll just do the query in SQL Developer and save as xls, businessy types love excel for some reason.
如果它有很多行，我只会在 SQL Developer 中进行查询并保存为 xls，商务类型出于某种原因喜欢 excel。
回答by AJ.
Nice question. I really had to think about it.
好问题。我真的不得不考虑一下。
One thing you could do is change your SQL so that it only returns the narrowest usable columns.
您可以做的一件事是更改您的 SQL，使其只返回最窄的可用列。
e.g. (I'm not very hot on oracle syntax, but something similar should work):
例如（我对 oracle 语法不是很感兴趣，但类似的东西应该可以工作）：
select substring( convert(varchar(4), assigner_staff_id), 1, 4 ) as id,
active_flag as act, -- use shorter column name
-- etc.
from work where assigner_staff_id = '2096';
Does that make sense?
If you were doing this on unix/linux, I would suggest running it from the command line and piping it through an awk script.
那有意义吗？
如果您在 unix/linux 上执行此操作，我建议从命令行运行它并通过 awk 脚本进行管道传输。
If I've miss-understood, then please update your question and I'll have another go :)
如果我错过了理解，那么请更新您的问题，我会再试一次:)
回答by Bubba
Why not just use the "cast" function?
为什么不直接使用“cast”功能？
select
(cast(assigner_staff_id as VARCHAR2(4)) AS STAFF_ID,
(cast(active_flag as VARCHAR2(1))) AS A,
(cast(assign_date as VARCHAR2(10))) AS ASSIGN_DATE,
(cast(COMPLETE_date as VARCHAR2(10))) AS COMPLETE_DATE,
(cast(mod_date as VARCHAR2(10))) AS MOD_DATE
from work where assigner_staff_id = '2096';
