SQL*Plus break与compute的简单用法

[img]http://dl2.iteye.com/upload/attachment/0099/4950/75ee3f77-368a-30c4-a9e5-1e6949c09ae8.jpg[/img]
在SQL*Plus提示符下输出求和报表，我们可以借助break与compute两个命令来实现。这个两个命令简单易用，可满足日常需求，其实质也相当于在编写SQL语句时使用分组及聚合函数。不同的是在报表中的分组的最下方或整个报表的最下方我们可以得到如sum,avg以及自定义的聚合字样。见下面的演示。

1、break的用法

a、获取帮助信息
--如果帮助不可用，需要安装SQL*Plus help，参考: SQL*PLus 帮助手册(SP2-0171) http://blog.youkuaiyun.com/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.