I want to be able to display the resulting data from a select in a pretty way, not all columns under others.
我希望能够以一种漂亮的方式显示select中的结果数据，而不是其他列下的所有列。
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
不是每一列都在另一列下。
3 个解决方案
#1
45
I usually start with something like:
我通常这样开头:
set lines 256
set trimout on
set tab off
Have a look at help set if you have the help information installed. And then select name,address rather than select * if you really only want those two columns.
如果安装了帮助信息，请查看帮助设置。然后选择name,address，而不是select *，如果你只想要这两列的话。
#2
39
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;
#3
1
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.
就在几个小时前，DB admin给我发送了在sqlplus中执行的那个难看的查询输出(我不知道，也许他讨厌我…)。我必须找到一种方法:这是一个awk脚本，用于解析输出，使其至少更具可读性。它远不完美，但我没有足够的时间去好好润色它。不管怎样，它做得很好。
awk ' function isDashed(ln){return ln ~ /^---+/};function addLn(){ln2=ln1; ln1=ln0;ln0=$0};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输出:
[oracle@ora ~]$ cat prettify_sql
set lines 256
set trimout on
set tab off
set pagesize 100
set colsep " | "
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
编辑:解析输出和非解析输出的预览
#1
45
I usually start with something like:
我通常这样开头:
set lines 256
set trimout on
set tab off
Have a look at help set if you have the help information installed. And then select name,address rather than select * if you really only want those two columns.
如果安装了帮助信息，请查看帮助设置。然后选择name,address，而不是select *，如果你只想要这两列的话。
#2
39
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;
#3
1
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.
就在几个小时前，DB admin给我发送了在sqlplus中执行的那个难看的查询输出(我不知道，也许他讨厌我…)。我必须找到一种方法:这是一个awk脚本，用于解析输出，使其至少更具可读性。它远不完美，但我没有足够的时间去好好润色它。不管怎样，它做得很好。
awk ' function isDashed(ln){return ln ~ /^---+/};function addLn(){ln2=ln1; ln1=ln0;ln0=$0};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输出:
[oracle@ora ~]$ cat prettify_sql
set lines 256
set trimout on
set tab off
set pagesize 100
set colsep " | "
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
编辑:解析输出和非解析输出的预览