【Oracle专栏】sqlplus显示设置+脚本常用显示命令

引言

在Oracle数据库中，sqlplus 是一个常用的命令行工具，广泛用于与数据库交互。无论是进行日常的数据库管理、查询、调试，还是运行SQL脚本，sqlplus 都能帮助用户高效地完成工作。为了提升工作效率并避免冗长的查询结果，sqlplus 提供了多种显示设置和命令，这些设置不仅可以让我们定制化查询的输出，还能够帮助我们在运行脚本时获得更清晰、更易读的结果。

本文将重点介绍 sqlplus 中的显示设置和常用的显示命令，并通过实例来演示它们的实际应用。通过本文，你将学会如何调整 sqlplus 的显示行为，便于更好地进行数据库操作和脚本编写。

1. sqlplus 显示设置概述

sqlplus 提供了多种设置选项，能够让用户定制化查询结果的显示方式。常见的显示设置包括行宽、列宽、页面大小、分页行为、行号等。理解并灵活运用这些设置，能够显著提高查询结果的可读性。

1.1 行宽设置：set linesize

在 sqlplus 中，行宽决定了查询结果在一行内显示的最大字符数。默认情况下，sqlplus 的行宽为80个字符。如果查询结果行数过多，可能会导致显示不完全，或者结果被自动换行，影响阅读体验。

设置示例：

sqlCopy Code
set linesize 120

这条命令将行宽设置为120个字符。通过调整行宽，可以确保查询结果在一行内显示完毕，避免换行。

1.2 列宽设置：set colsep

set colsep 用于设置列之间的分隔符。默认情况下，列之间的分隔符是空格。如果查询结果中的某些列数据较长，可能会导致列之间的数据难以区分。通过设置分隔符，可以改善结果的可读性。

设置示例：

sqlCopy Code
set colsep ','

将列分隔符设置为逗号（,），使得查询结果更加清晰。例如：

sqlCopy Code
SELECT first_name, last_name, hire_date FROM employees;

输出结果将会是：

Copy Code
John,Doe,2022-01-01
Jane,Smith,2022-02-01

1.3 页面大小设置：set pagesize

set pagesize 用于设置每一页的显示行数。默认情况下，sqlplus 的页面大小为24行。调整页面大小可以避免输出内容过长导致分页混乱。

设置示例：

sqlCopy Code
set pagesize 50

将页面大小设置为50行。这意味着在输出超过50行数据时，sqlplus 会自动分页并在每一页的底部显示分页信息。

1.4 其他显示设置

除了上述常用设置外，sqlplus 还提供了一些其他有用的设置，例如：

• set feedback: 控制反馈信息的显示。可以设置为 ON 或 OFF，如果开启，会显示查询影响的行数。
• set heading: 控制列标题是否显示。
• set echo: 控制是否显示输入的命令。

2. 常用显示命令

sqlplus 中有很多内置的显示命令，用于在脚本执行过程中显示信息或格式化查询结果。这些命令能有效提高脚本的可读性和执行效率。

2.1 SELECT 查询的格式化命令

在 sqlplus 中，使用 SELECT 查询时，我们可以通过调整显示命令来优化查询结果的展示方式。例如：

SELECT 查询列宽

我们可以使用 column 命令来调整查询结果中各列的宽度：

sqlCopy Code
column first_name format a20
column last_name format a30

以上命令将 first_name 列的宽度设置为20个字符，last_name 列的宽度设置为30个字符。这样，查询结果在显示时会根据指定的列宽进行适当的调整。

SELECT 查询数字格式

对于数值类型的列，可以使用 column 命令来设置数字的显示格式：

sqlCopy Code
column salary format \$999,999.00

以上命令将 salary 列的数字格式化为带有逗号和小数点的货币格式。例如，查询结果将显示为 1,234.56。

SELECT 查询日期格式

同样，日期类型的列也可以使用 column 命令来设置显示格式：

sqlCopy Code
column hire_date format a10

以上命令将 hire_date 列的日期格式设置为10个字符宽度。

2.2 使用 break 和 compute 进行分组与汇总

在 sqlplus 中，我们还可以使用 break 和 compute 命令来进行分组和汇总。

break 命令

break 命令用于在查询结果中按某列的值进行分组显示。例如：

sqlCopy Code
break on department_id skip 1

这条命令会根据 department_id 对查询结果进行分组，并且在每个分组之间插入一行空白行，以提高可读性。

compute 命令

compute 命令用于对查询结果中的列进行汇总统计。例如：

sqlCopy Code
compute sum of salary on department_id

这条命令将在按 department_id 分组的结果中对 salary 列进行求和操作，并在每个分组结束后显示总计。

2.3 处理长文本和大数据

在查询中，如果涉及到长文本列或者大数据，sqlplus 的默认显示方式可能无法显示完整的内容。为了确保完整显示，可以使用以下命令：

set long 和 set longchunksize

当查询结果中包含长文本数据时，使用 set long 可以控制每个列的最大显示字符数。例如：

sqlCopy Code
set long 5000

将文本列的显示最大长度设置为5000个字符，确保完整显示。

2.4 动态显示命令：print 和 accept

在脚本中，我们可以使用 print 和 accept 来动态显示变量的值或提示用户输入。

accept 命令

accept 命令允许用户在运行脚本时输入参数。例如：

sqlCopy Code
accept dept_id prompt 'Please enter department id: '

这条命令将在脚本执行时提示用户输入 department_id。

print 命令

print 命令用于显示变量的值。例如：

sqlCopy Code
print dept_id

将输出用户输入的 dept_id 值。

3. 常见应用场景与实例

3.1 查询员工信息并格式化输出

假设我们需要查询公司员工的信息，并且希望按照 department_id 进行分组显示，并对薪资进行汇总。我们可以结合使用 break 和 compute 命令：

sqlCopy Code
set linesize 120
set pagesize 50
set colsep '|'

column first_name format a20
column last_name format a30
column salary format \$999,999.00

break on department_id skip 1
compute sum of salary on department_id

SELECT department_id, first_name, last_name, salary
FROM employees
ORDER BY department_id;

3.2 导出查询结果到文件

在某些情况下，我们可能希望将查询结果导出到文件中。sqlplus 提供了 spool 命令来实现这一功能。例如：

sqlCopy Code
spool employees_report.txt

SELECT department_id, first_name, last_name, salary
FROM employees
ORDER BY department_id;

spool off

以上命令将查询结果导出到名为 employees_report.txt 的文件中。

结语

sqlplus 提供了强大的显示设置和格式化命令，使得在日常使用中可以根据需求定制输出格式。通过合理配置这些设置和命令，用户不仅能够提升查询结果的可读性，还能有效提高脚本的执行效率。希望通过本文的介绍，能够帮助大家更好地理解和运用 sqlplus 的显示设置及常用命令，在数据库管理和脚本编写过程中游刃有余。