database oracle sqlplus如何更清晰的显示表数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3006431/
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 display table data more clearly in oracle sqlplus
提问by Nubkadiya
I want to be able to display the resulting data from a select in a pretty way, not all columns under others.
我希望能够以漂亮的方式显示选择的结果数据，而不是其他列下的所有列。
Here is the way sqlplus displays my table data:
这是 sqlplus 显示我的表数据的方式：
But I want to show them as:
但我想将它们显示为：
Name | Address | Phone |
-------+---------------+-------------+
name1 | address1 | phone1 |
name2 | address2 | phone2 |
name3 | address3 | phone3 |
Not each column under the other
不是每一列都在另一列之下
回答by Alex Poole
I usually start with something like:
我通常从以下内容开始：
set lines 256
set trimout on
set tab off
Have a look at help setif you have the help information installed. And then select name,addressrather than select *if you really only want those two columns.
看看help set你是否安装了帮助信息。然后select name,address而不是select *如果你真的只想要这两列。
回答by Tony Andrews
If you mean you want to see them like this:
如果你的意思是你想像这样看到它们：
WORKPLACEID NAME ADDRESS TELEPHONE
----------- ---------- -------------- ---------
1 HSBC Nugegoda Road 43434
2 HNB Bank Colombo Road 223423
then in SQL Plus you can set the column widths like this (for example):
然后在 SQL Plus 中，您可以像这样设置列宽（例如）：
column name format a10
column address format a20
column telephone format 999999999
You can also specify the line size and page size if necessary like this:
如有必要，您还可以指定行大小和页面大小，如下所示：
set linesize 100 pagesize 50
You do this by typing those commands into SQL Plus before running the query. Or you can put these commands and the query into a script file e.g. myscript.sql and run that. For example:
您可以通过在运行查询之前将这些命令键入 SQL Plus 来完成此操作。或者您可以将这些命令和查询放入一个脚本文件中，例如 myscript.sql 并运行它。例如：
column name format a10
column address format a20
column telephone format 999999999
select name, address, telephone
from mytable;
回答by Jagga5589
You can set the line size as per the width of the window and set wrap off using the following command.
您可以根据窗口的宽度设置线条大小，并使用以下命令设置环绕。
set linesize 160;
set wrap off;
I have used 160 as per my preference you can set it to somewhere between 100 - 200and setting wrap will not your data and it will display the data properly.
我已经根据我的喜好使用了 160，您可以将其设置为介于两者之间的某个值，100 - 200并且设置 wrap 不会是您的数据，它将正确显示数据。
回答by netikras
In case you have a dump made with sqlplus and the output is garbled as someone did not set those 3 values before, there's a way out.
如果您使用 sqlplus 进行转储并且由于有人之前没有设置这 3 个值而导致输出出现乱码，则有办法解决。
Just a couple hours ago DB admin send me that ugly looking output of query executed in sqlplus (I dunno, maybe he hates me...). I had to find a way out: this is an awk script to parse that output to make it at least more readable. It's far not perfect, but I did not have enough time to polish it properly. Anyway, it does the job quite well.
就在几个小时前，数据库管理员向我发送了在 sqlplus 中执行的查询的丑陋输出（我不知道，也许他讨厌我......）。我必须找到出路：这是一个 awk 脚本，用于解析该输出以使其至少更具可读性。它远非完美，但我没有足够的时间来正确地打磨它。无论如何，它可以很好地完成工作。
awk ' function isDashed(ln){return ln ~ /^---+/};function addLn(){ln2=ln1; ln1=ln0;ln0=[oracle@ora ~]$ cat prettify_sql
set lines 256
set trimout on
set tab off
set pagesize 100
set colsep " | "
};function isLoaded(){return l==1||ln2!=""}; function printHeader(){hdr=hnames"\n"hdash;if(hdr!=lastHeader){lastHeader=hdr;print hdr};hnames="";hdash=""};function isHeaderFirstLn(){return isDashed(ln0) && !isDashed(ln1) && !isDashed(ln2) }; function isDataFirstLn(){return isDashed(ln2)&&!isDashed(ln1)&&!isDashed(ln0)} BEGIN{_d=1;h=1;hnames="";hdash="";val="";ln2="";ln1="";ln0="";fheadln=""} { addLn(); if(!isLoaded()){next}; l=1; if(h==1){if(!isDataFirstLn()){if(_d==0){hnames=hnames" "ln1;_d=1;}else{hdash=hdash" "ln1;_d=0}}else{_d=0;h=0;val=ln1;printHeader()}}else{if(!isHeaderFirstLn()){val=val" "ln1}else{print val;val="";_d=1;h=1;hnames=ln1}} }END{if(val!="")print val}'
In case anyone else would like to try improve this script, below are the variables: hnames -- column names in the header, hdash - dashed below the header, h -- whether I'm currently parsing header (then ==1), val -- the data, _d - - to swap between hnames and hdash, ln0 - last line read, ln1 - line read previously (it's the one i'm actually working with), ln2 - line read before ln1
如果其他人想尝试改进此脚本，以下是变量：hnames -- 标题中的列名，hdash - 标题下方的虚线，h -- 我当前是否正在解析标题（然后 ==1）， val - 数据，_d - 在 hnames 和 hdash 之间交换，ln0 - 最后一行读取，ln1 - 之前读取的行（这是我实际使用的行），ln2 - 在 ln1 之前读取的行
Happy parsing!
快乐解析！
Oh, almost forgot... I use this to prettify sqlplus output myself:
哦，差点忘了……我自己用这个来美化 sqlplus 输出：
##代码##colsep is optional, but it makes output look like sqlite which is easier to parse using scripts.
colsep 是可选的，但它使输出看起来像使用脚本更容易解析的 sqlite。
EDIT: A little preview of parsed and non-parsed output
编辑：解析和非解析输出的一点预览
