SQL*Plus User's Guide and Reference

Contents Index Home Previous Next

BREAK

Purpose

Specifies where and how formatting will change in a report, such as

Also lists the current BREAK definition.

Syntax

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]]
Terms and Clauses

Refer to the following list for a description of each term or clause:

ON column [action [action]] When you include action(s), specifies action(s) for SQL*Plus to take whenever a break occurs in the specified column (called the break column). (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) A break is one of three events:

When 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. 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, jobs with the same SAL print in groups. Breaks in ENAME cause no action because ENAME does not appear in the BREAK command.
ON expr [action [action]] 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.
ON ROW [action [action]] When you include action(s), specifies action(s) for SQL*Plus to take when a SQL SELECT command returns a row. The ROW break becomes the innermost break regardless of where you specify it in the BREAK command. You should always specify an action when you BREAK on a row.
ON REPORT [action] 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:

SKI[P] n Skips n lines before printing the row where the break occurred.
[SKI[P]] PAGE 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.
NODUP[LICATES] Prints blanks rather than the value of a break column when the value is a duplicate of the column's value in the preceding row.
DUP[LICATES] Prints the value of a break column in every selected row.
Enter BREAK with no clauses to list the current break definition.

Usage Notes

Each new BREAK command you enter replaces the preceding one.

To remove the BREAK command, use CLEAR BREAKS.

Example

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           1045
          *********           ----------
          avg                       1045

          SALESMAN  ALLEN           1760
          SALESMAN  MARTIN          1375
          SALESMAN  TURNER          1650
          SALESMAN  WARD            1375
          *********           ----------
          avg                       1540

**********                    ----------
sum                                 7205

Contents Index Home Previous Next