An ORDER BY clause that mirrors the BREAK command : Break « SQL PLUS Session Environment « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060
725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860
221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900
315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990
421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980
808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960
104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980
212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,
                  Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020
415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer

02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78            Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78            Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78            Tester

06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78            Manager

08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester


SQL>
SQL>
SQL>
SQL>
SQL> BREAK ON City skip1
SQL> /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer

02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78            Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78            Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78            Tester

06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78            Manager

08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester


SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer

02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78            Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78            Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78            Tester

06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78            Manager

08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester


SQL> select * from Employee order by city
  2  /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78            Tester

01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer

05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
08   James      Cat        17-SEP-96 15-APR-02    1232.78            Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78            Tester
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78            Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78            Manager


SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>








29.6.Break
29.6.1.Break the report on some column to produce easy-to-read output
29.6.2.If a blank line is desired: BREAK ON columnName skip1
29.6.3.An ORDER BY clause that mirrors the BREAK command
29.6.4.Two Break commands
29.6.5.COMPUTE calculates an aggregate value and place the result at the break point
29.6.6.BREAK may contain more than one ON clause.
29.6.7.break on department skip 1 on job
29.6.8.CLEAR BREAKS
29.6.9.Break on two