SQLPlus varchar2输出空格 [英] SQLPlus varchar2 outputs whitespaces

问题描述

当我如下查询我的表时，"NAME"列的输出太长.我最近将数据类型更改为VARCHAR2(150)而不是VARCHAR(150)，以不保存空格.但是，无论如何，输出似乎包含(某些?)空格.谁能弄清楚这里发生了什么?我是在这里查看空格吗，还是仅在终端/控制台上出现问题，还是SQLPLUS本身可以解决这个问题?使用Windows的终端.

When I query my table like below, the output of the column "NAME" is way too long. I recently changed the data type to VARCHAR2(150) instead of VARCHAR(150) to not save whitespaces. However, the output seems to include (some?) whitespaces anyway. Can anyone clearify what's going on here? Am I looking at whitespaces here, or is the problem only my terminal/console, or can SQLPLUS itself has something to do with it? Using Windows' terminal.

SQL> SELECT * FROM SYS.O1_Orders;

        ID
----------
NAME
--------------------------------------------------------------------------------

  QUANTITY      PRICE
---------- ----------
         1
Cakes
        25        200

推荐答案

varchar2在存储时没有空白填充，但是varchar也没有空白填充，这与SQL * Plus的显示方式没有任何区别

varchar2 doesn't have blank padding when stored, but neither does varchar, and that would make no difference to how SQL*Plus displays it anyway.

SQL * Plus将显示具有其可能包含的最大宽度的列，该列是在检索实际结果集之前从查询元数据获得的.否则，它将必须获取所有值，将它们保留在某个位置，找到最长值的长度，然后才开始产生输出.输出会随执行的不同而变化，这对于报表可能没有用.

SQL*Plus displays the column with the maximum width that it could contain, which it obtains from the query metadata before retrieving the actual result set. Otherwise it would have to fetch all the values, keep them somewhere, find the length of the longest value, and only then start to produce the output. And the output would vary with different executions, which probably wouldn't be useful for a report.

您有一些选择.如果您知道该值永远不会超过特定长度(在这种情况下，为什么列的最大大小大于该长度?)，则可以将其强制转换为该值，或者告诉SQL * Plus列宽使用column命令

You have a few options. If you know the value will never be longer than a certain length (in which case why is the maximum size of the column greater than that?) then you could cast it to that, or tell SQL*Plus the column width to use with the column command:

column name format a20

文档中有关格式化的更多信息.但是，如果出现较长的值，则会将它们包装起来(否则，如果在查询中进行强制转换/截断，则会丢失数据).

The is much more on formatting in the documentation. But longer values will be wrapped if they do occur (or data will be lost if you cast/truncate in the query).

您还可以将所有输出放在一行上，您没有明确询问过，但是会使事情看起来更整洁:

You can also put all the output on one line, which you haven't explicitly asked about but would make things look neater:

set linesize 200

...或所有列的足够宽的值.当然，您可以将这两种设置结合在一起.单行可能仍会包裹在您的终端窗口中，因此您可以根据需要将其扩大.

... or some value wide enough for all your columns. And you can combine both settings, of course. The single line may still wrap within your terminal window, so you can make that wider if needed.

这篇关于SQLPlus varchar2输出空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！