SQLPLUS - 关于Break On

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.

 

-The End-

转自：http://www.blogjava.net/wxqxs/archive/2009/04/03/264493.html