oracle格式化输出表列,格式化sqlplus报表

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 deptnoselect 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]$