oracle 如何美化来自命令提示符中 SELECT 查询的输出？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14547501/
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 prettify the output coming from the SELECT query in command prompt?
提问by Arup Rakshit
I ran the simple selectquery in the command prompt,but the output rows are not coming in a single line. See below:
我select在命令提示符下运行了简单查询，但输出行不在一行中。见下文：
SQL> set pagesize 2000
SQL> select * from xtern_empl_rpt ;
EMP LAST_NAME
--- --------------------------------------------------
FIRST_NAME SSN
-------------------------------------------------- ---------
EMAIL_ADDR
--------------------------------------------------------------------------------
YEARS_OF_SERVICE
----------------
001 Hutt
Jabba 896743856
[email protected]
18
002 Simpson
Homer 382947382
[email protected]
20
003 Kent
Clark 082736194
[email protected]
5
004 Kid
Billy 928743627
[email protected]
9
005 Stranger
Perfect 389209831
[email protected]
23
006 Zoidberg
Dr 094510283
[email protected]
1
6 rows selected.
SQL>
Could you please help me to make each rows in a single line?
你能帮我把每一行排成一行吗？
Edit
编辑
I tried below,but still is not prettified.
我在下面尝试过，但仍然没有美化。
SQL> SET LINESIZE 4000
SQL> select * from xtern_empl_rpt ;
EMP LAST_NAME FIRST_NAME
SSN EMAIL_ADDR
YEARS_OF_SERVICE
--- -------------------------------------------------- -------------------------
------------------------- --------- --------------------------------------------
-------------------------------------------------------- ----------------
001 Hutt Jabba
896743856 [email protected]
18
002 Simpson Homer
382947382 [email protected]
20
003 Kent Clark
082736194 [email protected]
5
004 Kid Billy
928743627 [email protected]
9
005 Stranger Perfect
389209831 [email protected]
23
006 Zoidberg Dr
094510283 [email protected]
1
6 rows selected.
SQL>
采纳答案by DazzaL
set your column widths to fit in the screen
设置您的列宽以适应屏幕
eg:
例如：
column EMAIL_ADDR format a30
where a is hte column width. you can use WRA to wrap the column eg
其中 a 是列宽。您可以使用 WRA 来包装列，例如
column EMAIL_ADDR format a30 WRA
or TRUto truncate, WORto break on word boundaries
或TRU截断，WOR打破单词边界
for example:
例如：
SQL> select * from emp;
ID FIRST_NAME
---------- ------------------------------
LAST_NAME
------------------------------
EMAIL_ADDR
--------------------------------------------------
1 Dazza
Smith
[email protected]
so the output is a bit tricky to read as email_addr was padded to 300 characters (as my table had it defined as varchar2(300) which sql*plus uses to format the output).
所以当 email_addr 被填充到 300 个字符时，输出有点难以阅读（因为我的表将它定义为 varchar2(300)，sql*plus 使用它来格式化输出）。
first set an appropriate linesize:
首先设置一个合适的linesize：
SQL> set linesize 100
now lets set the columns so they fit on one line (linesize should be greater than the total col widths):
现在让我们设置列，使其适合一行（行大小应大于总列宽）：
SQL> column email_addr format a30
SQL> column last_name format a20
SQL> column first_name format a20
SQL> select * from emp;
ID FIRST_NAME LAST_NAME EMAIL_ADDR
---------- -------------------- -------------------- ------------------------------
1 Dazza Smith [email protected]
so now the columns fit easily onto a reasonably sized terminal.
所以现在这些列可以很容易地安装到一个合理大小的终端上。
in your case first_nameand last_nameare varchar2(50)'s yet the data in them is much smaller, so i'd start with column first_name format a15(same for last_name). with email, your column is varchar2(100) yet the max sized output was 25 chars, so put column email format a25for a starter.
在您的情况下，first_name并且last_name是 varchar2(50)，但其中的数据要小得多，所以我将从column first_name format a15（last_name 相同）开始。对于电子邮件，您的列是 varchar2(100) 但最大大小的输出是 25 个字符，因此请column email format a25作为初学者使用。
if you did that, you should get output (if linesize is high enough) like:
如果你这样做，你应该得到输出（如果 linesize 足够高），如：
SQL> select * from xtern_empl_rpt ;
EMP LAST_NAME FIRST_NAME SSN EMAIL_ADDR YEARS_OF_SERVICE
--- --------------- -------------- --------- ------------------------- ----------------
001 Hutt Jabba 896743856 [email protected] 18
finally as requested. WRATRUand WOR. WRAis default by the way, so you dont have to use it but lets say we had:
最后按要求。WRATRU和WOR。WRA顺便说一下，它是默认的，所以你不必使用它，但可以说我们有：
SQL> select * from test;
A
--------------------------------------
THIS IS A SIMPLE WRAPPING TEST
but i wanted to format this as 10 characters width:
但我想将其格式化为 10 个字符的宽度：
S
秒
QL> col a format a10 WRA
SQL> select * from test;
A
----------
THIS IS A
SIMPLE WRA
PPING TEST
the WRAmeans just chop the string at 10 chars, regardless of whether we are in the middle of a word or not. if we wanted to break ONLY on word endings (where possible as a word > 10 still needs to break):
这WRA意味着只是将字符串截断为 10 个字符，无论我们是否在一个单词的中间。如果我们只想在词尾上打断（在可能的情况下，单词 > 10 仍然需要打断）：
SQL> col a format a10 WOR
SQL> select * from test;
A
----------
THIS IS A
SIMPLE
WRAPPING
TEST
now the output is broken at word boundaries and not necessarily at 10 chars.
现在输出在字边界处中断，不一定在 10 个字符处。
if we only wanted the first 10 chars and no line wrapping, we could use TRU:
如果我们只想要前 10 个字符而不需要换行，我们可以使用TRU：
SQL> col a format a10 TRU
SQL> select * from test;
A
----------
THIS IS A
回答by Musa almatri
This should fix your issue:
这应该可以解决您的问题：
set wrap off
回答by Musa almatri
Try something like:
尝试类似：
SET LINESIZE 120
(Adjust 120to required maximum width.)
（调整120到所需的最大宽度。）
