所有列的 Oracle 列宽
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15473059/
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
Oracle Column width for all columns
提问by MooHa
One of the issue when executing a long statement for displaying various columns for example
例如，执行用于显示各种列的长语句时的问题之一
select g.guestid, g.forename, g.surname, b.bookingid,
b.arrivedate, b.departdate, br.floorno, br.roomno from...
the column sizing on linux terminal seems to be an issue. For example the Forename VarChar(80) column takes up much of the width of the screen when executing the above statement and one way to cut it down would be through:
linux 终端上的列大小似乎是一个问题。例如，Forename VarChar(80) 列在执行上述语句时占据了屏幕的大部分宽度，一种减少它的方法是通过：
SET COLUMN FORENAME FORMAT A10
for example. However, many columns would need to be repeatedly go through this which is quite long. i.e.
例如。然而，许多列需要反复经过，这是相当长的。IE
SET COLUMN FORENAME FORMAT A10
SET COLUMN SURNAME FORMAT A10
and so on...
等等...
Is there a way to say adjust column width according to text width so that every fits in nicely. and not like this..
有没有办法说根据文本宽度调整列宽，以便每个都很好地适应。而不是这样..
I would prefer some solution that does not involve the use of FUNCTIONS.
我更喜欢一些不涉及使用 FUNCTIONS 的解决方案。
回答by Kirill Leontev
No, there is no simple way to make SQL*Plus "auto-adjust" column width to text width.
不，没有简单的方法可以让 SQL*Plus 将列宽“自动调整”为文本宽度。
However, scripting can make your life easier.
但是，编写脚本可以让您的生活更轻松。
First of all, reduce typing. Don't do SET COLUMN FORENAME FORMAT A10, do something like @c forename 10instead.
首先，减少打字。不要做SET COLUMN FORENAME FORMAT A10，做类似的事情@c forename 10。
17:33:31 SYSTEM@dwal> cl col
columns cleared
17:33:33 SYSTEM@dwal> select * from dual;
D
-
X
Elapsed: 00:00:00.01
17:33:37 SYSTEM@dwal> @c dummy 10
17:33:43 SYSTEM@dwal> select * from dual;
DUMMY
----------
X
Elapsed: 00:00:00.01
17:33:45 SYSTEM@dwal> get s:\c.sql
1* col &1. for a&2.
17:33:50 SYSTEM@dwal>
Or quickly hiding wide columns like this:
或者像这样快速隐藏宽列：
17:48:44 SYSTEM@dwal> select owner, table_name from all_tables where rownum = 1;
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS CON$
Elapsed: 00:00:00.24
17:48:49 SYSTEM@dwal> @np owner
17:48:53 SYSTEM@dwal> select owner, table_name from all_tables where rownum = 1;
TABLE_NAME
------------------------------
CON$
Elapsed: 00:00:00.26
17:48:56 SYSTEM@dwal> get s:\np
1 col &1 noprint
2* @undef
These are just a two of many scripts I use on a daily basis. This approach takes takes time and some personal attention to customization to get used to it and make it effective, but reduces the amount of keys you press dramatically.
这些只是我每天使用的许多脚本中的两个。这种方法需要时间和一些个人对自定义的关注才能习惯它并使其有效，但会显着减少您按下的按键数量。
Second, there is glogin.sql. It is a script that executes every time you connect somewhere. I assume you know a list of "long" columns that make your lines wrap.
其次，有glogin.sql。这是一个每次您连接到某个地方时都会执行的脚本。我假设您知道使您的行换行的“长”列列表。
Just list them there, and your
只需在那里列出它们，然后您的
SET COLUMN FORENAME FORMAT A10
SET COLUMN SURNAME FORMAT A10
column parameters would be set each time you (re)connect.
每次（重新）连接时都会设置列参数。
回答by René Nyffenegger
Tom Kyte has written a print_table procedure, that displays the result set in a vertical fashion.
Tom Kyte 编写了一个print_table 过程，它以垂直方式显示结果集。
For example:
例如：
SQL> exec print_table('select g.guestid, g.forename, ... from ...')
GUESTID : 210
FORENAME : DINGLE
...
etc...
....
-----------------
PL/SQL procedure successfully completed.
This procedure comes in handy especially when the result set is small, ideally not more than maybe 10 rows.
这个过程派上用场，特别是当结果集很小时，理想情况下不超过 10 行。
回答by miracle173
As already stated by other users there is no simple solution for sqlplus. Maybe it helps to use the glogin.sqlor login.sqlfile (@René Nyffenegger provided an article about those files).
You can put column definitions in these files. If you always query the same queries or if you have a consistent naming of your columns this may help.
正如其他用户已经指出的那样，sqlplus 没有简单的解决方案。也许使用glogin.sqlorlogin.sql文件会有所帮助（@René Nyffenegger 提供了一篇关于这些文件的文章）。您可以将列定义放在这些文件中。如果您总是查询相同的查询，或者您的列命名一致，这可能会有所帮助。
Or you put the column statements in a script that you call by
或者您将列语句放在您调用的脚本中
@scriptname.sql
if you want to use the column format. You put the script in a directory that is part of the SQLPATHvariableso that it can be called from any directory.
如果要使用列格式。您将脚本放在作为SQLPATH变量一部分的目录中，以便可以从任何目录调用它。
Or you use another tool. One user alredy pointed at SQLcl.
或者您使用其他工具。一位用户已经指出SQLcl。
回答by shelley
The ansiconsoleSQL format may be what you're looking for.
该ansiconsoleSQL的格式可能是你在找什么。
set sqlformat ansiconsole;
select g.guestid, g.forename, g.surname, b.bookingid,
b.arrivedate, b.departdate, br.floorno, br.roomno from...
Or:
或者：
select /*ansiconsole*/ g.guestid, g.forename, g.surname, b.bookingid,
b.arrivedate, b.departdate, br.floorno, br.roomno from...
This format auto-sizes columns to based on the size of the query results.
此格式根据查询结果的大小自动调整列大小。
回答by David Aldridge
The session does not know what the maximum length of the strings to be returned will be until it has run the query. The column should be sized appropriately of course, but maybe you really are going to get a list of forenames that is 80 characters long -- if not then your data type length is too large.
会话在运行查询之前不知道要返回的字符串的最大长度是多少。当然，列的大小应该适当，但也许您真的会得到一个长度为 80 个字符的名字的列表——如果不是，那么您的数据类型长度太大了。
As Eric says, GUI tools are better, and Oracle's SQL Developer is free and good.
正如 Eric 所说，GUI 工具更好，而且 Oracle 的 SQL Developer 是免费的而且很好。
