SQL*Plus 中的丑陋格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5771573/
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
Ugly formatting in SQL*Plus
提问by Richard Knop
It is really annoying that when I run a select command in SQL*Plus such as:
当我在 SQL*Plus 中运行 select 命令时，这真的很烦人，例如：
SELECT * FROM books;
The output is really badly formatted and unreadable (row cells are not in a row but separated by line breaks etc):
输出格式非常糟糕且无法读取（行单元格不在一行中，而是由换行符等分隔）：
How can I configure it to show SELECT results in a nicer way?
如何配置它以更好的方式显示 SELECT 结果？
EDIT:
编辑：
This is my login.sql file contents:
这是我的 login.sql 文件内容：
SET ECHO OFF
SET SERVEROUTPUT ON SIZE 1000000
SET PAGESIZE 999
SET LINESIZE 132
EDIT2:
编辑2：
Affer increasing the LINESIZE:
增加 LINESIZE：
SET LINESIZE 32000
It now looks like this:
现在看起来像这样：
回答by a_horse_with_no_name
Increase the linesize, e.g SET LINESIZE 32000
增加线宽，例如 SET LINESIZE 32000
or use SET WRAP OFF(but this will truncate long values)
或使用SET WRAP OFF（但这会截断长值）
回答by APC
SQLPlus is a simple command line tool. It's not really intended for pretty reporting. However, it does have some formatting commands, which are documented in the SQLPlus User's Guide. Find out more.
SQL Plus 是一个简单的命令行工具。它并不是真正用于漂亮的报告。但是，它确实有一些格式化命令，这些命令记录在 SQLPlus 用户指南中。 了解更多。
For instance you might choose to format the TITLE column to display only the first twenty characters and display the SUMMARY column in its entirety like this:
例如，您可能会选择将 TITLE 列格式化为仅显示前 20 个字符，并像这样完整显示概要列：
COLUMN title FORMAT a20 TRUNCATED
COLUMN summary FORMAT a4o WORD_WRAPPED
This will allow you to see your query laid out more neatly without embedding formatting commands in its projection.
这将使您能够更整齐地查看您的查询，而无需在其投影中嵌入格式化命令。
Alternatively, use an IDE such as Quest's TOAD or Oracle's own SQL Developer. These tools include a query browser which automagically displays our query results in a more pleasing grid. (Other similar tools are available).
或者，使用 IDE，例如 Quest 的 TOAD 或 Oracle 自己的 SQL Developer。这些工具包括一个查询浏览器，它会自动以更令人愉悦的网格显示我们的查询结果。（其他类似工具可用）。
回答by tbone
Some may not like this advice (I can think of a few DBAs who LOVE SqlPlus), but you may want to use an IDE like Toador SQL Developer. If you're new to Oracle, sqlplus will make you feel like you just jumped back in time! IMO, spend your time learning Oracle, not SQLPlus. (oh, and read the Concepts guidewhile playing around in your IDE of choice)
有些人可能不喜欢这个建议（我可以想到一些喜欢 SqlPlus 的 DBA），但您可能想要使用像Toad或SQL Developer这样的 IDE 。如果您是 Oracle 的新手，sqlplus 会让您感觉自己回到了过去！IMO，花时间学习 Oracle，而不是 SQLPlus。（哦，在您选择的 IDE 中玩耍时阅读概念指南）
回答by user4909653
Make a script like below
制作一个像下面这样的脚本
#!/bin/ksh
FILE="/tmp/queryResult.csv"
sqlplus -s /nolog << !EOF!
connect username/password
SET PAGESIZE 50000
SET LINESIZE 250
SET NUMWIDTH 5
SET FEEDBACK OFF
set echo off
set heading on
set headsep off
set wrap off
SET COLSEP ","
column Title format a22
column Summary format a15
SPOOL $FILE
Select * from books;
SPOOL OFF
EXIT
!EOF!
Save script in a file namely sqlscript.sql set permission on file
将脚本保存在一个文件中，即 sqlscript.sql 设置文件权限
chmode +x sqlscript.sql
run the script and pipe to less command
运行脚本和管道到 less 命令
./sqlscript.sql | less -S
"S" option will allow you to scroll with arrow keys, if output is longer than columns set in terminal.
如果输出比终端中设置的列长，“S”选项将允许您使用箭头键滚动。
Alternatively you can download and open FILE="/tmp/queryResult.csv" in text editor of your choice.
或者，您可以在您选择的文本编辑器中下载并打开 FILE="/tmp/queryResult.csv"。
Adjust the LINESIZE,NUMWIDTH, column character size (a22) as per your requirement
根据您的要求调整 LINESIZE、NUMWIDTH、列字符大小 (a22)
回答by Michael Ballent
Just define the column widths so that it fits the actual content of the columns
只需定义列宽，使其适合列的实际内容
col column_name1 format a20 -- sets column to be 20 characters wide
col column_name2 format a15 -- sets column to be 15 characters wide
set line 80
select column_name1, column_name2 from books;
This should help you out.
这应该可以帮助你。
回答by Bohdan
This can make output more pretty:
这可以使输出更漂亮：
SET PAGESIZE 0
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP ' '
Source: http://larig.wordpress.com/2011/05/29/formatting-oracle-output-in-sqlplus/
来源：http: //larig.wordpress.com/2011/05/29/formatting-oracle-output-in-sqlplus/
回答by Brad
This worked for me:
这对我有用：
SELECT ISBN, SUBSTR(TITLE, 0, 16), SUBSTR(SUMMARY, 0, 16), DATE_PUBL, PAGE_COUNT FROM books;
回答by Rijad Hadzic
Additionally to all these answers:
除了所有这些答案：
set colsep "&TAB"
回答by lainatnavi
Csv style, but clean:
Csv 风格，但干净：
SQL> set markup csv on
