ORACLE之SQL*PLUS的格式化输出 第二十三讲 ORACLE之SQL*PLUS的格式化输出

本讲内容 通过介绍SQL*PLUS命令来说明如何通过该工具输出具有一定格式的查询结果。 介绍执行批文件

格式化输出 在SQL*Plus中，有许多参数可控制SQL*Plus的输出显示格式。利用SQL*Plus命令show all。

页和行的大小 命令set linesize告诉Oracle页宽是多少，最常用的设置为80和132。为设置行宽为80，可输入命令set linesize 80。set pagesize命令告诉Oracle页的长度是多少，常用设置为55和60。为了更容易地看到分页，可以用命令set pagesize 30设置页长为30行。

页头标 可以用ttitle命令设置每页的标题。ttitle命令包括许多选项。通常使用的缺省设置为：标题文本在行中央，每页上都有日期和页号。如果需要两行头标，则需要使用竖字符(|)。命令 ttitle 'Database techologies|Customer Report‘ 使Oracle居中放置文本“Database Technologies”为第一个头标行，然后将文本“Customer Report”居中放置在第二行。

页脚标 可用btitle命令在每页的底部放置一些信息。建议将程序名放在此处。如果用户需要改一个报告时，只要指定页底部的程序名，就可知道要改那个报告，这有助于避免混淆。命令 btitle '---sample.sql---‘ 使SQL*Plus把文本“---sample.sql---”放于每一页底部中央。用户还可以使用left或right关键字将btitle命令中的文字放到相应的位置。如果btitle命令中没有用于定位的关键字，Oracle将文本放在底行的中央。

将SQL*Plus结果写到文件中 格式化输出样例

Spool命令 spool命令使Oracle将SQL*Plus的输出保存到一个数据文件中。spool命令中要指定输出文件名。如在Windows NT上，要将SQL*Plus的输出保存到数据文件out.lis中，可用命令 spool c:\report\out.lis。 在大多数操作系统中，如果用户未指定后缀，系统会自动在文件名后增加后缀“.lst”。例如，命令spool report会自动产生一个名为report.lst的文件。 提示：SQL*Plus自动把文件扩展名加到spool命令中指定的文件名后。在不同的操作系统中，扩展名可能不同。

续 如果已执行了命令 set linesize 70 set pagesize 23 并用命令 col sales format 99999999 格式化了sales表列，要保存SQL语句select * from customer的输出到一个文件。这个输出将放在用spool命令指定的文件中。请注意，因为sales表列是用99999999格式化的，因此显示结果中没有小数。为停止假脱机(spooling)，可用命令spool off或spool out。后者关闭输出文件并打印输出。

格式化输出表列 大多数情况下，用户需要格式化实际的表列数据，Column命令可完成这项工作。下面先执行两条格式化命令，然后再查询customer表。命令 column last_name format a8 wrap heading 'Last|Name'告诉SQL*Plus在last_name表列只显示8个字符。8代表last_name的显示宽度，a表示每个位置只能是字符。其中wrap部分说明如果last_name多于8个字符，多余的字符显示在下一行的对应位置。Heading部分告诉SQL*Plus把“Last Name”分两行打印在报告的头上。

Column命令 命令 column state_cd format a8 heading 'State|Code‘ 让SQL*Plus以8个字符位置显示state_cd，并且在state_cd表列的顶部放一个两行的列标题“State Code”。现在同样的SQL语句 select * from customer；

Format命令 现在我们来格式化数值型域。format子句可规定用于显示每个数值的位数，并指明在何处插入逗号。语句 column sales format 999,999,999,999.00 heading 'Sales‘ 使SQL*Plus显示12位数字，两位小数，用逗号作分隔符。

断开逻辑(Break Logic) 现在增加断开逻辑。在SQL*Plus中，增加断开逻辑的方法很简单。只要执行break命令，SQL*Plus就会管理所有的断开逻辑。

未断开逻辑 col sales format 999999.00 和SQL查询语句select state_cd,last_name, sales from customer order by state_cd,last_name；

断开逻辑 执行断开命令break on state_cd，上述查询语句的输出如图所示。注意，在图中，state_cd为CA的在第1行显示后，第2行至第5行都不显示(因为从第一行后state_cd没有改变)，然后第6行和第7行的值不同，又再显示。

排序 提示：为在SQL*Plus中实现断开逻辑，必须在断开命令指定的表列上进行查询排序。 为说明此点，执行查询 select state_cd,last_name, sales from customer order by last_name； 再执行命令 break on state_cd 输出如图所示。

断开和跳过(Break and Skip) 通常，实现断开逻辑后，我们希望在出现新的断开列值前空几行，这时就要用到skip命令。用命令 col sales format $999,999,999.99 heading 'YTD|Sales‘ 重新格式化sales表列，再执行断开命令 break on state_cd skip 1 现在，语句 select state_cd, last_name, sales from customer order by state_cd, last_name；

在断开点计算表列值 用compute sum命令，用户可以让SQL*Plus计算所需的和。现在讨论完成断开格式化和计算YTD合计值所需的命令。 命令 compute sum of sales on report 是在报表结尾处，计算报表的合计值。report在此处用于触发数值域求和，并显示在报表的结尾处。而命令“compute sum of sales on state_cd”使在打印新的state_cd时打印合计。

续 为同时打印报表合计和断开点合计，需要用命令 break on report skip 1 on state_cd skip 1 重置断开条件。修改SQL语句以便用state_cd排序数据。对于断开报表，必须用break语句中同样的表列排序数据。 注意：为使本节的输出能打印在一页中，必须增加页尺寸。为此输入命令 set pagesize 28

结尾处带总计的state_cd断开报表 SQL语句 select state_cd,last_name, sales from customer order by state_cd,last_name； 用语句 col sales format $999,999,999.00 heading 'YTD|Sales‘ 重新格式化sales表列后 执行上述select语句， 其断开输出如图所示

基于双表的断开逻辑与求和 在多个表中，通过匹配表列值选择数据。下面显示州名(state_name)而非州名代码(state_cd)。

最后的修饰 需要state表中的state_name表列的值，可以通过state_cd连接state和customer两个表。 选择语句 select state_name, last_name, sales from state a,customer b where a.state_cd=b.state_cd order by state_name, last_name； 因为计算时使用state_name表列，所以必须用 break on report skip 1 on state_name skip 1 重新向SQL*Plus描述断开条件。我们也需要用 compute sum of sales on state_name 和 compute sum of sales on report 语句重新定义断开点合计和报表合计。

最终结果

小结 介绍了SQL*Plus命令中关于格式化输出的部分内容，包括页头、页脚等。