1.使用underline以及heading
SQL> host cat /home/oracle/test/sqlplustest.sql
set underline =
column ename heading 'Employee|Name' format A20
column sal heading 'Employee|Salary' format L99,999
select ename,sal from emp where deptno = 20;
column ename clear
column sal clear
set underline -
SQL> @/home/oracle/test/sqlplustest.sql
Employee Employee
Name Salary
==================== =================
SMITH $800
JONES $2,975
SCOTT $3,000
ADAMS $1,100
FORD $3,000
underline设置报表头跟数据之间的分隔符，heading设置报表的列明，如果要换行则可以使用|
2.使用break和compute分割数据结果并生成摘要行
SQL> host cat /home/oracle/test/sqlplustest.sql
set pagesize 50
break on deptno skip 1
compute sum of sal on deptno
select deptno,ename,sal from emp order by deptno;
clear break
SQL> @/home/oracle/test/sqlplustest.sql
DEPTNO ENAME SAL
========== ========== ==========
10 CLARK 2450
KING 5000
MILLER 1300
********** ----------
sum 8750
20 JONES 2975
FORD 3000
ADAMS 1100
SMITH 800
SCOTT 3000
********** ----------
sum 10875
30 WARD 1250
TURNER 1500
ALLEN 1600
JAMES 950
BLAKE 2850
MARTIN 1250
********** ----------
sum 9400
3.使用REPHEADER定义报表标题，REPFOOTER定义报表脚注，TTITLE定义页标题，BTITLE定义页脚
SQL> host cat /home/oracle/test/sqlplustest.sql
set pagesize 20
set linesize 60
repheader right 'Begin Report'
repfooter rigth 'End Report'
ttitle 'Employee Report'
btitle 'Employee Report'
select ename,sal,deptno from emp;
repheader off
repfooter off
ttitle off
btitle off
SQL> @/home/oracle/test/sqlplustest.sql
Wed Dec 05 page 1
Employee Report
Begin Report
ENAME SAL DEPTNO
========== ========== ==========
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
Employee Report
Wed Dec 05 page 2
Employee Report
ENAME SAL DEPTNO
========== ========== ==========
FORD 3000 20
MILLER 1300 10
rigthEnd Report
Employee Report
14 rows selected.
--将页标题和页脚分别放到右边跟左边，默认是放在中间
SQL> host cat /home/oracle/test/sqlplustest.sql
set pagesize 20
set linesize 60
repheader right 'Begin Report'
repfooter right 'End Report'
ttitle right 'imployee Report'
btitle left 'Employee Report'
select ename,sal,deptno from emp;
repheader off
repfooter off
ttitle off
btitle off
SQL> @/home/oracle/test/sqlplustest.sql
imployee Report
Begin Report
ENAME SAL DEPTNO
========== ========== ==========
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
Employee Report
imployee Report
ENAME SAL DEPTNO
========== ========== ==========
End Report
Employee Report
14 rows selected.
4.使用spool存储打印的结果
SQL> spool /home/oracle/test/spoolout.txt
SQL> @/home/oracle/test/sqlplustest.sql
imployee Report
Begin Report
ENAME SAL DEPTNO
========== ========== ==========
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
Employee Report
imployee Report
ENAME SAL DEPTNO
========== ========== ==========
End Report
Employee Report
14 rows selected.
SQL> spool off
SQL> !
bash: et: command not found
[oracle@redhat test]$ ls -la
total 20
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 11:21 .
drwx------ 5 oracle oinstall 4096 Dec 5 11:16 ..
-rw-r--r-- 1 oracle oinstall 1555 Dec 5 11:21 spoolout.txt
-rw-r--r-- 1 oracle oinstall 115 Dec 4 19:36 sqlplustest2.sql
-rw-r--r-- 1 oracle oinstall 237 Dec 5 11:16 sqlplustest.sql
--查看打印结果
[oracle@redhat test]$ cat spoolout.txt
SQL> @/home/oracle/test/sqlplustest.sql
imployee Report
Begin Report
ENAME SAL DEPTNO
========== ========== ==========
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
Employee Report
imployee Report
ENAME SAL DEPTNO
========== ========== ==========
End Report
Employee Report
14 rows selected.
SQL> spool off
[oracle@redhat test]$
