SQL*Plus break与compute的简单用法
在SQL*Plus提示符下输出求和报表，我们可以借助break与compute两个命令来实现。这个两个命令简单易用，可满足日常需求，其实质也相当于在编写SQL语句时使用分组及聚合函数。不同的是在报表中的分组的最下方或整个报表的最下方我们可以得到如sum,avg以及自定义的聚合字样。
一、break用法：
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重复的也显示,不加该选项，默认就为nodup
SQL> conn scott/tiger@rhel201;
已连接。
SQL> show pagesize
pagesize
SQL> show linesize
linesize
SQL> select deptno,ename,sal from emp order by deptno; DEPTNO ENAME SAL
---------- ---------- ----------
CLARK
KING
MILLER
JONES
FORD
ADAMS
SMITH
SCOTT
WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN 已选择14行。 SQL> break on deptno --过滤重复的deptno内容，默认为nudup
SQL> break --查看当前的break设置信息
break on deptno nodup SQL> list
* select deptno,ename,sal from emp order by deptno
SQL> / DEPTNO ENAME SAL
---------- ---------- ----------
CLARK
KING
MILLER
JONES
FORD
ADAMS
SMITH
SCOTT
WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN 已选择14行。 SQL> break on deptno skip 1 --在每个分组后自动跳过1个空行
SQL> list
1* select deptno,ename,sal from emp order by deptno
SQL> / DEPTNO ENAME SAL
---------- ---------- ----------
CLARK
KING
MILLER JONES
FORD
ADAMS
SMITH
SCOTT WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN 已选择14行。 SQL> break on row skip 1 --基于row进行分组，且每行之后也跳过一行
SQL> / DEPTNO ENAME SAL
---------- ---------- ----------
CLARK KING MILLER JONES FORD ADAMS SMITH SCOTT WARD TURNER ALLEN JAMES BLAKE MARTIN 已选择14行。 SQL> SQL> show pagesize
pagesize
SQL> break on deptno skip page --基于页面进行跳页
SQL> / DEPTNO ENAME SAL
---------- ---------- ----------
CLARK
KING
MILLER DEPTNO ENAME SAL
---------- ---------- ----------
JONES
FORD
ADAMS
SMITH
SCOTT DEPTNO ENAME SAL
---------- ---------- ----------
WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN 已选择14行。 SQL>
SQL> break on deptno on job skip 1 --基于多列break
SQL> select deptno,ename,job,sal from emp order by deptno DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
CLARK MANAGER KING PRESIDENT MILLER CLERK JONES MANAGER DEPTNO ENAME JOB SAL
---------- ---------- --------- ---------- FORD ANALYST ADAMS CLERK
SMITH SCOTT ANALYST DEPTNO ENAME JOB SAL
---------- ---------- --------- ---------- WARD SALESMAN
TURNER
ALLEN JAMES CLERK DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
BLAKE MANAGER MARTIN SALESMAN
二、compute用法
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来用，否则相当于没有分组，聚合也就没有任何意义。
SQL> clear break
breaks 已清除
SQL> break on deptno skip
SQL> compute sum of sal on deptno; --基于deptno对sal求和
SQL> select deptno,ename,job,sal from emp order by deptno; DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
********** ----------
sum JONES MANAGER DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
FORD ANALYST
ADAMS CLERK
SMITH CLERK
SCOTT ANALYST
********** ----------
sum DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
WARD SALESMAN
TURNER SALESMAN
ALLEN SALESMAN
JAMES CLERK
BLAKE MANAGER
MARTIN SALESMAN
********** ---------- DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
sum 已选择14行。 SQL>
SQL> show pagesize
pagesize
SQL> break on report skip
SQL> compute sum of sal on report --基于整个report的sal进行求和
SQL> / DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
JONES MANAGER
FORD ANALYST
ADAMS CLERK
SMITH CLERK DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
SCOTT ANALYST
WARD SALESMAN
TURNER SALESMAN
ALLEN SALESMAN
JAMES CLERK
BLAKE MANAGER
MARTIN SALESMAN DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
sum 已选择14行。 SQL> compute sum avg of sal on report ------对整个report求和以及求平均
SQL> / DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
JONES MANAGER
FORD ANALYST
ADAMS CLERK
SMITH CLERK DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
SCOTT ANALYST
WARD SALESMAN
TURNER SALESMAN
ALLEN SALESMAN
JAMES CLERK
BLAKE MANAGER
MARTIN SALESMAN DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
----------
avg 2073.21429
sum 已选择14行。 SQL> SQL> compute sum avg of sal on deptno --对deptno分组进行求和，未平均值
SQL> clear break
breaks 已清除
SQL> break on deptno skip
SQL> list
* select deptno,ename,job,sal from emp order by deptno
SQL> / DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
********** ----------
avg 2916.66667
sum DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
JONES MANAGER
FORD ANALYST
ADAMS CLERK
SMITH CLERK
SCOTT ANALYST
********** ----------
avg DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
sum WARD SALESMAN
TURNER SALESMAN
ALLEN SALESMAN
JAMES CLERK
BLAKE MANAGER DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
MARTIN SALESMAN
********** ----------
avg 1566.66667
sum 已选择14行。 SQL> SQL> break on deptno skip
SQL> compute sum of sal comm on deptno --对sal及comm基于分组deptno同时求和
SQL> select deptno,ename,sal,job,comm from emp order by deptno DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
********** ---------- ----------
sum JONES MANAGER DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
FORD ANALYST
ADAMS CLERK
SMITH CLERK
SCOTT ANALYST
********** ---------- ----------
sum DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
WARD SALESMAN
TURNER SALESMAN
ALLEN SALESMAN
JAMES CLERK
BLAKE MANAGER
MARTIN SALESMAN
********** ---------- ---------- DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
sum 已选择14行。 SQL> SQL> compute sum avg of sal comm on deptno --基于dpetno，对sal和comm求和，求平均值
SQL> / DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
********** ---------- ----------
avg 2916.66667
sum DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
JONES MANAGER
FORD ANALYST
ADAMS CLERK
SMITH CLERK
SCOTT ANALYST
********** ---------- ----------
avg DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
sum WARD SALESMAN
TURNER SALESMAN
ALLEN SALESMAN
JAMES CLERK
BLAKE MANAGER DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
MARTIN SALESMAN
********** ---------- ----------
avg 1566.66667
sum 已选择14行。 SQL> SQL> compute avg of sal on deptno --对sal列基于分组deptno求平均值
SQL> compute sum of comm on deptno --对comm列基于分组deptno求和
SQL> list
* select deptno,ename,sal,job,comm from emp order by deptno
SQL> / DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
********** ---------- ----------
avg 2916.66667
sum DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
JONES MANAGER
FORD ANALYST
ADAMS CLERK
SMITH CLERK
SCOTT ANALYST
********** ---------- ----------
avg DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
sum WARD SALESMAN
TURNER SALESMAN
ALLEN SALESMAN
JAMES CLERK
BLAKE MANAGER DEPTNO ENAME SAL JOB COMM
---------- ---------- ---------- --------- ----------
MARTIN SALESMAN
********** ---------- ----------
avg 1566.66667
sum 已选择14行。 SQL>