關于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.
?
?