第1页:第二十三讲 SQL*Plus格式化输出
第二十三讲ORACLE之SQL*PLUS的格式化输出
第2页:本讲内容概览
本讲内容
- 通过介绍SQL*PLUS命令来说明如何通过该工具输出具有一定格式的查询结果。
- 介绍执行批文件
第3页:格式化输出
格式化输出
- 在SQL*Plus中,有许多参数可控制SQL*Plus的输出显示格式。利用SQL*Plus命令show all。
第4页:页和行的大小
页和行的大小
- 命令set linesize告诉Oracle页宽是多少,最常用的设置为80和132。为设置行宽为80,可输入命令set linesize 80。setpagesize命令告诉Oracle页的长度是多少,常用设置为55和60。为了更容易地看到分页,可以用命令set pagesize 30设置页长为30行。
第5页:页头标
页头标:用 ttitle 命令设置每页的标题,缺省设置下标题文本居中,每页上还有日期和页号。
ttitle 'Database techologies|Customer Report'
竖字符“|”用于分隔两行头标。
第6页:页脚标
页脚标:用 btitle 命令在每页底部放置信息,建议放程序名以便识别报告。
btitle '---sample.sql---'
可使用 left 或 right 关键字定位,缺省时文本放在底行中央。
第7页:结果写入文件
将SQL*Plus结果写到文件中
第8页:Spool命令
Spool命令
- spool命令使Oracle将SQL*Plus的输出保存到一个数据文件中。spool命令中要指定输出文件名。如在Windows NT上,要将SQL*Plus的输出保存到数据文件out.lis中,可用命令
- spool c:\report\out.lis。
- 在大多数操作系统中,如果用户未指定后缀,系统会自动在文件名后增加后缀“.lst”。例如,命令spoolreport会自动产生一个名为report.lst的文件。
- 提示:SQL*Plus自动把文件扩展名加到spool命令中指定的文件名后。在不同的操作系统中,扩展名可能不同。
第9页:Spool输出示例
Spool 示例:将查询结果保存到指定文件。
set linesize 70 set pagesize 23 col sales format 99999999 spool c:\report\out.lis select * from customer; spool off
sales 表列用 99999999 格式化后显示结果中没有小数;spool out 会关闭输出文件并打印输出。
第10页:格式化输出表列
格式化输出表列
- 大多数情况下,用户需要格式化实际的表列数据,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”分两行打印在报告的头上。
第11页:Column命令
Column命令
- 命令
- column state_cd format a8 heading 'State|Code‘
- 让SQL*Plus以8个字符位置显示state_cd,并且在state_cd表列的顶部放一个两行的列标题“State Code”。现在同样的SQL语句
- select * from customer;
第12页:数值列格式化
Format 命令:格式化数值型域。
column sales format 999,999,999,999.00 heading 'Sales'
format 子句规定用于显示每个数值的位数,并指明在何处插入逗号。
第13页:断开逻辑
断开逻辑(Break Logic)
- 现在增加断开逻辑。在SQL*Plus中,增加断开逻辑的方法很简单。只要执行break命令,SQL*Plus就会管理所有的断开逻辑。
第14页:未断开逻辑的输出
未断开逻辑
- col sales format 999999.00和SQL查询语句selectstate_cd,last_name, sales from customer order bystate_cd,last_name;
第15页:断开逻辑输出
断开逻辑
- 执行断开命令break on state_cd,上述查询语句的输出如图所示。注意,在图中,state_cd为CA的在第1行显示后,第2行至第5行都不显示(因为从第一行后state_cd没有改变),然后第6行和第7行的值不同,又再显示。
第16页:断开与排序
排序
- 提示:为在SQL*Plus中实现断开逻辑,必须在断开命令指定的表列上进行查询排序。
- 为说明此点,执行查询
- select state_cd,last_name, sales from customer order by last_name;
- 再执行命令
- break on state_cd
- 输出如图所示。
第17页:断开和跳过
断开和跳过(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;
第18页:在断开点计算合计
在断开点计算表列值
- 用compute sum命令,用户可以让SQL*Plus计算所需的和。现在讨论完成断开格式化和计算YTD合计值所需的命令。
- 命令
- compute sum of sales on report
- 是在报表结尾处,计算报表的合计值。report在此处用于触发数值域求和,并显示在报表的结尾处。而命令“compute sum of sales on state_cd”使在打印新的state_cd时打印合计。
第19页:报表合计与断开合计
续
- 为同时打印报表合计和断开点合计,需要用命令
- break on report skip 1 on state_cd skip 1
- 重置断开条件。修改SQL语句以便用state_cd排序数据。对于断开报表,必须用break语句中同样的表列排序数据。
- 注意:为使本节的输出能打印在一页中,必须增加页尺寸。为此输入命令
- set pagesize 28
第20页:带总计的断开报表
结尾处带总计的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语句,
- 其断开输出如图所示
第21页:基于双表的断开逻辑
基于双表的断开逻辑与求和
- 在多个表中,通过匹配表列值选择数据。下面显示州名(state_name)而非州名代码(state_cd)。
第22页:连接查询与断开设置
最后的修饰:通过 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 设置断开。
第23页:最终输出结果
最终结果
第24页:第二十三讲小结
小结
- 介绍了SQL*Plus命令中关于格式化输出的部分内容,包括页头、页脚等。