在sql*plus里制作报表 (4)

介绍两个经典的制作报表台头(TITLE)的脚本，该脚本可在台头中显示当前的日期，时间，连接的用户名，当前页码，同时用户可以自己输入抬头标题

title80.sql -- 报表的行宽为80个字符

title132.sql -- 报表的行宽为132个字符

[@more@]

title80.sql脚本：

可参见http://www.redwingnet.com/hioug/Presentations/Kens-25-Jun-05-Presentation/TITLE80.SQL

rem
rem TITLE80.SQL
rem
rem FUNCTION: This SQL*Plus script builds a standard report heading
rem heading for database reports that are 80 columns
rem
column TODAY NEW_VALUE CURRENT_DATE NOPRINT
column TIME NEW_VALUE CURRENT_TIME NOPRINT
column DATABASE NEW_VALUE DATA_BASE NOPRINT
column PASSOUT NEW_VALUE DBNAME NOPRINT
rem
define COMPANY = " "
define HEADING = "&1"
rem
set lines 75
TTITLE LEFT "Date: " current_date CENTER company col 66 "Page:" format 999 -
SQL.PNO SKIP 1 LEFT "Time: " current_time CENTER heading RIGHT -
format a15 SQL.USER SKIP 1 CENTER data_base SKIP 2
rem
rem
set heading off
set pagesize 0
rem
set termout off
SELECT TO_CHAR(SYSDATE,'MM/DD/YY') TODAY,
TO_CHAR(SYSDATE,'HH:MI AM') TIME,
value||' database' DATABASE,
rtrim(value) passout
FROM v$parameter
where name = 'db_name';
rem
set termout on
set heading on
set pagesize 58
set newpage 0
DEFINE DB = '&DBNAME'

title132.sql脚本：

可参见http://www.redwingnet.com/hioug/Presentations/Kens-25-Jun-05-Presentation/TITLE132.SQL

rem
rem TITLE132.SQL
rem
rem FUNCTION: This SQL*Plus script builds a standard report heading
rem heading for database reports that are 132 columns
rem
column TODAY NEW_VALUE CURRENT_DATE NOPRINT
column TIME NEW_VALUE CURRENT_TIME NOPRINT
column DATABASE NEW_VALUE DATA_BASE NOPRINT
column PASSOUT NEW_VALUE DBNAME NOPRINT
rem
define COMPANY = " "
define HEADING = "&1"
rem
TTITLE LEFT "Date: " current_date CENTER company col 118 "Page:" format 999 -
SQL.PNO SKIP 1 LEFT "Time: " current_time CENTER heading RIGHT -
format a15 SQL.USER SKIP 1 CENTER format a20 data_base SKIP 2
rem
rem
set heading off termout off
rem
SELECT TO_CHAR(SYSDATE,'MM/DD/YY') TODAY,
TO_CHAR(SYSDATE,'HH:MI AM') TIME,
value||' database' DATABASE,
rtrim(value) passout
FROM sys.v_$parameter
where name = 'db_name';
rem
set heading on termout on
set newpage 0
DEFINE DB = '&DBNAME'

运行结果（仅以title80.sql为例，title132.sql与之类似）

SQL> @title80 'SQL*PLUS VERSION INFOMATION'
SQL> select * from v$version;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/207/viewspace-815510/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-815510/