如何美化命令提示符下SELECT查询的输出? [英] How to prettify the output coming from the SELECT query in command prompt?
问题描述
我在命令提示符下运行了简单的select查询，但是输出行不在一行中.见下文:
I ran the simple select query in the command prompt,but the output rows are not coming in a single line. See below:
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
jabba@thecompany.com
18
002 Simpson
Homer 382947382
homer@thecompany.com
20
003 Kent
Clark 082736194
superman@thecompany.com
5
004 Kid
Billy 928743627
billythkid@thecompany.com
9
005 Stranger
Perfect 389209831
nobody@thecompany.com
23
006 Zoidberg
Dr 094510283
crustacean@thecompany.com
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 jabba@thecompany.com
18
002 Simpson Homer
382947382 homer@thecompany.com
20
003 Kent Clark
082736194 superman@thecompany.com
5
004 Kid Billy
928743627 billythkid@thecompany.com
9
005 Stranger Perfect
389209831 nobody@thecompany.com
23
006 Zoidberg Dr
094510283 crustacean@thecompany.com
1
6 rows selected.
SQL>
推荐答案
设置列宽以适合屏幕
例如:
column EMAIL_ADDR format a30
其中a是列宽.您可以使用WRA包裹列 例如
where a is hte column width. you can use WRA to wrap the column eg
column EMAIL_ADDR format a30 WRA
或TRU截断，WOR截断单词边界
例如:
SQL> select * from emp;
ID FIRST_NAME
---------- ------------------------------
LAST_NAME
------------------------------
EMAIL_ADDR
--------------------------------------------------
1 Dazza
Smith
d_dazzal@dazzal.com
因此，由于email_addr被填充为300个字符(如我的表将其定义为sql * plus用于格式化输出的varchar2(300)一样)，因此读取输出时有些棘手.
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).
首先设置适当的行大小:
first set an appropriate 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 d_dazzal@dazzal.com
因此，现在这些列可以轻松安装到大小合适的终端上.
so now the columns fit easily onto a reasonably sized terminal.
first_name和last_name是varchar2(50)，但是它们中的数据要小得多，所以我将从column first_name format a15开始(与last_name相同).如果使用电子邮件，则您的列为varchar2(100)，但最大输出为25个字符，因此请按column email format a25作为入门.
in your case first_name and last_name are 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 a25 for a starter.
如果这样做，则应该得到输出(如果行大小足够高)，如:
if you did that, you should get output (if linesize is high enough) like:
SQL> select * from xtern_empl_rpt ;
EMP LAST_NAME FIRST_NAME SSN EMAIL_ADDR YEARS_OF_SERVICE
--- --------------- -------------- --------- ------------------------- ----------------
001 Hutt Jabba 896743856 jabba@thecompany.com 18
最后根据要求. WRA TRU和WOR.顺便说一句，WRA是默认设置，因此您不必使用它，但是可以说我们有:
finally as requested. WRA TRU and WOR. WRA is default by the way, so you dont have to use it but lets say we had:
SQL> select * from test;
A
--------------------------------------
THIS IS A SIMPLE WRAPPING TEST
但我想将此格式设置为10个字符的宽度:
but i wanted to format this as 10 characters width:
S
QL> col a format a10 WRA
SQL> select * from test;
A
----------
THIS IS A
SIMPLE WRA
PPING TEST
WRA的意思是将字符串切成10个字符，而不管我们是否在单词中间.如果我们只想在单词结尾处打断(可能的话，一个单词> 10仍然需要打断):
the WRA means 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):
SQL> col a format a10 WOR
SQL> select * from test;
A
----------
THIS IS A
SIMPLE
WRAPPING
TEST
现在输出在单词边界处断开了，而不一定是10个字符.
now the output is broken at word boundaries and not necessarily at 10 chars.
如果我们只需要前10个字符而没有换行，则可以使用TRU:
if we only wanted the first 10 chars and no line wrapping, we could use TRU:
SQL> col a format a10 TRU
SQL> select * from test;
A
----------
THIS IS A
这篇关于如何美化命令提示符下SELECT查询的输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
