SQL*Plus
User's Guide and Reference
Release 8.1.6 A75664-01 |
|
Command Reference, 10 of 52
Specifies where and how formatting will change in a report, such as
Also lists the current BREAK definition.
BRE[AK] [ON report_element [action [action]]] ...
where:
report_element |
Requires the following syntax:
{column|expr|ROW|REPORT} |
action |
Requires the following syntax:
[SKI[P] n|[SKI[P]] PAGE][NODUP[LICATES]|DUP[LICATES]] |
Refer to the following list for a description of each term or clause:
a change in the value of a column or expressionthe output of a rowthe end of a reportWhen you omit action(s), BREAK ON column suppresses
printing of duplicate values in column and marks a place in the
report where SQL*Plus will perform the computation you specify in a corresponding
COMPUTE command.
You can specify ON column one or more times. If you specify multiple ON clauses, as in
SQL> BREAK ON DEPTNO SKIP PAGE ON JOB - > SKIP 1 ON SAL SKIP 1
the first ON clause represents the outermost break
(in this case, ON DEPTNO) and the last ON clause represents the innermost
break (in this case, ON SAL). SQL*Plus searches each row of output
for the specified break(s), starting with the outermost break and proceeding--in
the order you enter the clauses--to the innermost. In the example, SQL*Plus
searches for a change in the value of DEPTNO, then JOB, then SAL.
Next, SQL*Plus executes actions beginning with the action
specified for the innermost break and proceeding in reverse order toward
the outermost break (in this case, from SKIP 1 for ON SAL toward SKIP PAGE
for ON DEPTNO). SQL*Plus executes each action up to and including the action
specified for the first occurring break encountered in the initial search.
If, for example, in a given row the value of JOB changes--but
the values of DEPTNO and SAL remain the same--SQL*Plus skips two
lines before printing the row (one as a result of SKIP 1 in the ON SAL
clause and one as a result of SKIP 1 in the ON JOB clause).
Whenever you use ON column, you should also use an
ORDER BY clause in the SQL SELECT command. Typically, the columns used
in the BREAK command should appear in the same order in the ORDER BY clause
(although all columns specified in the ORDER BY clause need not appear
in the BREAK command). This prevents breaks from occurring at meaningless
points in the report.
If the BREAK command specified earlier in this section is used, the following SELECT command produces meaningful results:
SQL> SELECT DEPTNO, JOB, SAL, ENAME 2 FROM EMP 3 ORDER BY DEPTNO, JOB, SAL, ENAME;
All rows with the same DEPTNO print together on one page, and within that page all rows with the same JOB print in groups. Within each group of jobs, those jobs with the same SAL print in groups. Breaks in ENAME cause no action because ENAME does not appear in the BREAK command.
When you include action(s), specifies action(s) for SQL*Plus
to take when the value of the expression changes.
When you omit action(s), BREAK ON expr suppresses
printing of duplicate values of expr and marks a place in the report
where SQL*Plus will perform the computation you specify in a corresponding
COMPUTE command.
You can use an expression involving one or more table
columns or an alias assigned to a report column in a SQL SELECT or SQL*Plus
COLUMN command. If you use an expression in a BREAK command, you must enter
expr exactly as it appears in the SELECT command. If the expression
in the SELECT command is a+b, for example, you cannot use b+a or (a+b)
in a BREAK command to refer to the expression in the SELECT command.
The information given above for ON column also applies to ON expr.
Marks a place in the report where SQL*Plus will perform
the computation you specify in a corresponding COMPUTE command. Use BREAK
ON REPORT in conjunction with COMPUTE to print grand totals or other "grand"
computed values.
The REPORT break becomes the outermost break regardless
of where you specify it in the BREAK command.
Note that SQL*Plus will not skip a page at the end of a report, so you cannot use BREAK ON REPORT SKIP PAGE.
Refer to the following list for a description of each action:
Skips the number of lines that are defined to be a page before printing the row where the break occurred. The number of lines per page can be set via the PAGESIZE clause of the SET command. Note that PAGESIZE only changes the number of lines that SQL*Plus considers to be a page. Therefore, SKIP PAGE may not always cause a physical page break, unless you have also specified NEWPAGE 0. Note also that if there is a break after the last row of data to be printed in a report, SQL*Plus will not skip the page.
Enter BREAK with no clauses to list the current break definition.
Each new BREAK command you enter replaces the preceding one.
To remove the BREAK command, use CLEAR BREAKS.
To produce a report that prints duplicate job values, prints the average of SAL and inserts one blank line when the value of JOB changes, and additionally prints the sum of SAL and inserts another blank line when the value of DEPTNO changes, you could enter the following commands. (The example selects departments 10 and 30 and the jobs of clerk and salesman only.)
SQL> BREAK ON DEPTNO SKIP 1 ON JOB SKIP 1 DUPLICATES SQL> COMPUTE SUM OF SAL ON DEPTNO SQL> COMPUTE AVG OF SAL ON JOB SQL> SELECT DEPTNO, JOB, ENAME, SAL FROM EMP 2 WHERE JOB IN ('CLERK', 'SALESMAN') 3 AND DEPTNO IN (10, 30) 4 ORDER BY DEPTNO, JOB;
The following output results:
DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 10 CLERK MILLER 1300 ********* ---------- avg 1300 ********** ---------- sum 1300 30 CLERK JAMES 950 ********* ---------- avg 950 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 30 SALESMAN ALLEN 1600 SALESMAN WARD 1250 SALESMAN MARTIN 1250 SALESMAN TURNER 1500 ********* ---------- avg 1400 ********** ---------- sum 6550 6 rows selected.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|