在SQL*Plus提示符下輸出求和報表，我們可以借助break與compute兩個指令來實作。這個兩個指令簡單易用，可滿足日常需求，其實質也相當于在編寫SQL語句時使用分組及聚合函數。不同的是在報表中的分組的最下方或整個報表的最下方我們可以得到如sum,avg以及自定義的聚合字樣。見下面的示範。
1、break的用法
a、擷取幫助資訊
--如果幫助不可用，需要安裝SQL*Plus help，參考: SQL*PLus 幫助手冊(SP2-0171) http://blog.csdn.net/robinson_0612/article/details/8852568
goex_admin@SYBO2SZ> help break
BREAK
-----
Specifies where changes occur in a report and the formatting
action to perform, such as:
- suppressing display of duplicate values for a given column
- skipping a line each time a given column value changes
(In iSQL*Plus, only when Preformatted Output is ON)
- printing computed figures each time a given column value
changes or at the end of the report.
Enter BREAK with no clauses to list the current BREAK definition.
BRE[AK] [ON report_element [action [action]]] ...
where report_element has the following syntax:
{column | expression | ROW | REPORT}
and where action has the following syntax:
[SKI[P] n | [SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]]
The SKIP option is not supported in iSQL*Plus
b、指令特性描述
break 指令主要用于過濾重複列，正如單詞所表達的意思及中斷，也就是說中斷顯示重複的列。
當下一行記錄的上指定的列與上一行相同，不顯示該列，否則顯示該列。
當使用break時，通常建議sql語句使用Order by 子句。可以基于order by子句使用多個列，同樣break 也可以使用多個列。
report_element表明可以基于列，表達式，行，以及report等多種不同類型來進行中斷顯示，也就是說break on對哪個進行分組。
action則表示
skip[n],在每個分組的最後，自動跳過n個空行。
skip page, 在每個分組的最後，自動換頁。
break on row skip[n],每一行後面跳過n個空行。
nodup 重複的顯示空，dup重複的也顯示。
c、示範break用法
--基于列deptno進行中斷顯示
goex_admin@SYBO2SZ> break on deptno
goex_admin@SYBO2SZ> break --break用于檢視目前break的設定資訊
break on deptno nodup
--下面查詢中，deptno列被中斷顯示
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300
7900 JAMES CLERK 7698 19811203 00:00:00 1050
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
14 rows selected.
--指定skip參數為n，則每一個新的分組之後插入指定的行數，如下面的查詢為1，則新分組後插入1空行
goex_admin@SYBO2SZ> break on deptno skip 1
goex_admin@SYBO2SZ> break
break on deptno skip 1 nodup
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300
7900 JAMES CLERK 7698 19811203 00:00:00 1050
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
14 rows selected.
--上面的查詢結果中分組deptno 30之後也被跳過一行，是以顯示的結果尾部與"14 rows selected"有兩行間隙
--下面基于row來分組，且插入1空行
goex_admin@SYBO2SZ> break on row skip 1;
goex_admin@SYBO2SZ> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--此時設定pagesize為8
goex_admin@SYBO2SZ> set pagesize 8
goex_admin@SYBO2SZ> break on deptno skip page -->基于頁面進行跳頁
--下面的查詢基于deptno被分為了4個頁面
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300
7900 JAMES CLERK 7698 19811203 00:00:00 1050
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 30
14 rows selected.
--基于多列的break
--下面的查詢中除了基于deptno分組之外，還增加了基于job進行分組
goex_admin@SYBO2SZ> break on deptno on job skip 1;
goex_admin@SYBO2SZ> select * from emp order by deptno,job;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 19820123 00:00:00 1400 10
7782 CLARK MANAGER 7839 19810609 00:00:00 2550
7839 KING PRESIDENT 19811117 00:00:00 5100
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20
7902 FORD 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH 7902 19801217 00:00:00 900
7566 JONES MANAGER 7839 19810402 00:00:00 3075
7900 JAMES CLERK 7698 19811203 00:00:00 1050 30
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
7521 WARD 7698 19810222 00:00:00 1350 500
7499 ALLEN 7698 19810220 00:00:00 1700 300
7844 TURNER 7698 19810908 00:00:00 1600 0
14 rows selected. 複制
2、compute的用法
a、擷取幫助資訊
goex_admin@SYBO2SZ> help compute
COMPUTE
-------
In combination with the BREAK command, calculates and prints
summary lines using various standard computations. Also lists
all COMPUTE definitions.
COMP[UTE] [function [LAB[EL] text] ...
OF {expr|column|alias} ...
ON {expr|column|alias|REPORT|ROW} ...]
b、指令特性描述
compute用于分組值計算指定的列上的數值，實際上等同于對分組列執行group by，然後調用聚合函數。
function為常用的聚合函數，如sum,avg,maximum,minimum,std,count等等。
of為指定的計算列，也就是說要計算哪一列。
on為分組條件，基于哪個列，表達式，report，row等進行分組。
compute通常結合break來用，否則相當于沒有分組，聚合也就沒有任何意義。
--Author : Robinson
--Blog : http://blog.csdn.net/robinson_0612
c、示範compute用法
goex_admin@SYBO2SZ> set pagesize 80
goex_admin@SYBO2SZ> clear break -->清除break的設定
breaks cleared
goex_admin@SYBO2SZ> break on deptno skip 1
goex_admin@SYBO2SZ> compute sum of sal on deptno -->基于deptno對sal求和
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
---------- **********
9050 sum
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
---------- **********
11375 sum
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300
7900 JAMES CLERK 7698 19811203 00:00:00 1050
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
---------- **********
10000 sum
goex_admin@SYBO2SZ> break on report skip 1
goex_admin@SYBO2SZ> compute sum of sal on report -->對整個report的sal進行求和
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100 10
7934 MILLER CLERK 7782 19820123 00:00:00 1400 10
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100 20
7876 ADAMS CLERK 7788 19870523 00:00:00 1200 20
7369 SMITH CLERK 7902 19801217 00:00:00 900 20
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 30
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 30
7900 JAMES CLERK 7698 19811203 00:00:00 1050 30
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 30
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 30
----------
sum 30425
14 rows selected.
goex_admin@SYBO2SZ> compute sum avg of sal on report -->對整個report求和以及求平均
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100 10
7934 MILLER CLERK 7782 19820123 00:00:00 1400 10
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100 20
7876 ADAMS CLERK 7788 19870523 00:00:00 1200 20
7369 SMITH CLERK 7902 19801217 00:00:00 900 20
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100 20
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0 30
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300 30
7900 JAMES CLERK 7698 19811203 00:00:00 1050 30
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950 30
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400 30
----------
avg 2173.21429
sum 30425
14 rows selected.
goex_admin@SYBO2SZ> compute sum avg of sal on deptno -->對deptno分組進行求和，同時求平均
goex_admin@SYBO2SZ> break on deptno skip 1;
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
---------- **********
3016.66667 avg
9050 sum
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
---------- **********
2275 avg
11375 sum
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300
7900 JAMES CLERK 7698 19811203 00:00:00 1050
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
---------- **********
1666.66667 avg
10000 sum
14 rows selected.
goex_admin@SYBO2SZ> break on deptno skip 1;
goex_admin@SYBO2SZ> compute sum of sal comm on deptno --對sal以及comm基于分組deptno同時求和
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
---------- ---------- **********
9050 sum
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
---------- ---------- **********
11375 sum
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300
7900 JAMES CLERK 7698 19811203 00:00:00 1050
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
---------- ---------- **********
10000 2200 sum
goex_admin@SYBO2SZ> compute avg of sal on deptno; --對sal列基于分組deptno求平均
goex_admin@SYBO2SZ> compute sum of comm on deptno; --對comm列基于分組deptno求和
goex_admin@SYBO2SZ> select * from emp order by deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 19810609 00:00:00 2550 10
7839 KING PRESIDENT 19811117 00:00:00 5100
7934 MILLER CLERK 7782 19820123 00:00:00 1400
---------- ---------- **********
3016.66667 avg
sum
7566 JONES MANAGER 7839 19810402 00:00:00 3075 20
7902 FORD ANALYST 7566 19811203 00:00:00 3100
7876 ADAMS CLERK 7788 19870523 00:00:00 1200
7369 SMITH CLERK 7902 19801217 00:00:00 900
7788 SCOTT ANALYST 7566 19870419 00:00:00 3100
---------- ---------- **********
2275 avg
sum
7521 WARD SALESMAN 7698 19810222 00:00:00 1350 500 30
7844 TURNER SALESMAN 7698 19810908 00:00:00 1600 0
7499 ALLEN SALESMAN 7698 19810220 00:00:00 1700 300
7900 JAMES CLERK 7698 19811203 00:00:00 1050
7698 BLAKE MANAGER 7839 19810501 00:00:00 2950
7654 MARTIN SALESMAN 7698 19810928 00:00:00 1350 1400
---------- ---------- **********
1666.66667 avg
2200 sum 複制