Chinaunix首页 | 论坛 | 博客
  • 博客访问: 549291
  • 博文数量: 65
  • 博客积分: 1158
  • 博客等级: 少尉
  • 技术积分: 1261
  • 用 户 组: 普通用户
  • 注册时间: 2012-07-18 22:07
文章分类

全部博文(65)

文章存档

2016年(1)

2014年(2)

2013年(9)

2012年(53)

分类: Oracle

2012-12-05 11:28:24

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]$ 
阅读(4181) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~