关于Break On [SQLPlus]
今天继续来学习一下SQLPLUS中的Report函数。这个在工作中使用到的机会不多，但是做好之后把结果打印出来看着会很爽。
参考文档：《SQL*Plus User's Guide and Reference Release 10.2 B14357-01》
Break ON
The BREAK command suppresses duplicate values by default in the column or expression you name. Thus, to suppress the duplicate values in a column specified in an ORDER BY clause, use the BREAK command in its simplest form:
BREAK ON break_column
Note: Whenever you specify a column or expression in a BREAK command, use an ORDER BY clause specifying the same column or
expression. If you do not do this, breaks occur every time the column value changes.
SQL> break on deptno
SQL> select deptno,empno,sal from emp
2 where sal>1500
3 order by deptno;
DEPTNO EMPNO SAL
---------- ---------- ----------
10 7782 2450
7839 5000
20 7566 2975
7788 3000
7902 3000
30 7499 1600
7698 2850
7 rows selected.
Break ON
... Skip
You can insert blank lines or begin a new page each time the value changes in the break column. To insert n blank lines, use the BREAK command in the following form:
BREAK ON break_column SKIP n
SQL> break on deptno skip 1
SQL> /
DEPTNO EMPNO SAL
---------- ---------- ----------
10 7782 2450
7839 5000
20 7566 2975
7788 3000
7902 3000
30 7499 1600
7698 2850
7 rows selected.
Skip Every Row
You may wish to insert blank lines or a blank page after every row. To skip n lines after every row, use BREAK in the following form:
BREAK ON ROW SKIP n
To skip a page after every row, use
BREAK ON ROW SKIP PAGE
SQL> break on row skip 1
SQL> /
DEPTNO EMPNO SAL
---------- ---------- ----------
10 7782 2450
10 7839 5000
20 7566 2975
20 7788 3000
20 7902 3000
30 7499 1600
30 7698 2850
7 rows selected.
SQL> break on row skip page
SQL> /
DEPTNO EMPNO SAL
---------- ---------- ----------
10 7782 2450
DEPTNO EMPNO SAL
---------- ---------- ----------
10 7839 5000
DEPTNO EMPNO SAL
---------- ---------- ----------
20 7566 2975
DEPTNO EMPNO SAL
---------- ---------- ----------
20 7788 3000
DEPTNO EMPNO SAL
---------- ---------- ----------
20 7902 3000
DEPTNO EMPNO SAL
---------- ---------- ----------
30 7499 1600
DEPTNO EMPNO SAL
---------- ---------- ----------
30 7698 2850
7 rows selected.
Other Example
To show that SKIP PAGE has taken effect, create a TTITLE with a page number:
SQL> ttitle col 35 format 9 'Page:' SQL.PNO
SQL> /
Page: 1
DEPTNO EMPNO SAL
---------- ---------- ----------
10 7782 2450
7839 5000
Page: 2
DEPTNO EMPNO SAL
---------- ---------- ----------
20 7566 2975
7788 3000
7902 3000
Page: 3
DEPTNO EMPNO SAL
---------- ---------- ----------
30 7499 1600
7698 2850
7 rows selected.
Listing and Removing Break
You can list your current break definition by entering the BREAK command with no clauses:
BREAK
You can remove the current break definition by entering the CLEAR command withthe BREAKS clause:
CLEAR BREAKS
You may wish to place the command CLEAR BREAKS at the beginning of every scriptto ensure that previously entered BREAK commands will not affect queries you run ina given file.
Break and Compute
If you organize the rows of a report into subsets with the BREAK command, you can perform various computations on the rows in each subset. You do this with the functions of the SQL*Plus COMPUTE command. Use the BREAK and COMPUTE commands together in the following forms:
BREAK ON break_column
COMPUTE function LABEL label_name OF column column column
... ON break_column
SQL> compute sum of sal on deptno
SQL> /
DEPTNO EMPNO SAL
---------- ---------- ----------
10 7782 2450
7839 5000
********** ********** ----------
sum 7450
DEPTNO EMPNO SAL
---------- ---------- ----------
20 7566 2975
7788 3000
7902 3000
********** ********** ----------
sum 8975
DEPTNO EMPNO SAL
---------- ---------- ----------
30 7499 1600
7698 2850
********** ********** ----------
sum 4450
7 rows selected.
Over.