Oracle PL/SQL/SQL Plus/break

Материал из SQL эксперт
Перейти к: навигация, поиск

break on department skip 1 on job

    
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "TRAINER", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "TRAINER", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "TRAINER", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL> create table departments
  2  ( deptno NUMBER(2)     constraint D_PK primary key
  3  , dname  VARCHAR2(10)
  4  , location VARCHAR2(8)
  5  , mgr    NUMBER(4)
  6  ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7);
1 row created.
SQL> insert into departments values (20,"TRAINING",  "DALLAS",  4);
1 row created.
SQL> insert into departments values (30,"SALES",     "CHICAGO", 6);
1 row created.
SQL> insert into departments values (40,"HR",        "BOSTON",  9);
1 row created.
SQL>
SQL> break on department skip 1 on job
SQL>
SQL> select d.dname as department
  2  ,      e.job   as job
  3  ,      e.ename as emp
  4  from   emp e
  5         right outer join
  6         departments d
  7         using (deptno)
  8  order  by department, job;
DEPARTMENT JOB      EMP
---------- -------- --------
ACCOUNTING Designer Peter
                    Chris
           Manager  Mike
HR
SALES      Designer Black
           Manager  Jane
           Tester   Mary
                    Wil
                    Jack
                    Take
TRAINING   Designer Jane
           TRAINER  Ana
                    Fake
                    Tom
                    Smart

15 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table departments;
Table dropped.



Break on different columns

    
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "TRAINER", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "TRAINER", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "TRAINER", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL> clear  columns
columns cleared
SQL>
SQL> select deptno, job, empno, ename, sal, comm
  2  from   emp
  3  order  by deptno, job;
Enter...
DEPTNO JOB       EMPNO ENAME       SAL   COMM
------ -------- ------ -------- ------ ------
    10 Designer      7 Chris      2450  [N/A]
    10 Designer      9 Peter      5000  [N/A]
    10 Manager      14 Mike       1300  [N/A]
    20 Designer      4 Jane       2975  [N/A]
    20 TRAINER       8 Smart      3000  [N/A]
    20 TRAINER      11 Ana        1100  [N/A]
    20 TRAINER      13 Fake       3000  [N/A]
    20 TRAINER       1 Tom         800  [N/A]
    30 Designer      6 Black      2850  [N/A]
    30 Manager      12 Jane        800  [N/A]
    30 Tester       10 Take       1500      0
    30 Tester        2 Jack       1600    300
    30 Tester        3 Wil        1250    500
    30 Tester        5 Mary       1250   1400
14 rows selected.
SQL> break  on deptno skip 2
SQL> /
Enter... break
DEPTNO JOB       EMPNO ENAME       SAL   COMM
------ -------- ------ -------- ------ ------
    10 Designer      7 Chris      2450  [N/A]
       Designer      9 Peter      5000  [N/A]
       Manager      14 Mike       1300  [N/A]

    20 Designer      4 Jane       2975  [N/A]
       TRAINER       8 Smart      3000  [N/A]
       TRAINER      11 Ana        1100  [N/A]
       TRAINER      13 Fake       3000  [N/A]
       TRAINER       1 Tom         800  [N/A]

    30 Designer      6 Black      2850  [N/A]
       Manager      12 Jane        800  [N/A]
       Tester       10 Take       1500      0
       Tester        2 Jack       1600    300
       Tester        3 Wil        1250    500
       Tester        5 Mary       1250   1400
Enter...
14 rows selected.
SQL> break  on deptno page
SQL> set    pause on
SQL> /
Enter...
DEPTNO JOB       EMPNO ENAME       SAL   COMM
------ -------- ------ -------- ------ ------
    10 Designer      7 Chris      2450  [N/A]
       Designer      9 Peter      5000  [N/A]
       Manager      14 Mike       1300  [N/A]
Enter... drop table emp;
DEPTNO JOB       EMPNO ENAME       SAL   COMM
------ -------- ------ -------- ------ ------
    20 Designer      4 Jane       2975  [N/A]
       TRAINER       8 Smart      3000  [N/A]
       TRAINER      11 Ana        1100  [N/A]
       TRAINER      13 Fake       3000  [N/A]
       TRAINER       1 Tom         800  [N/A]
Enter...
DEPTNO JOB       EMPNO ENAME       SAL   COMM
------ -------- ------ -------- ------ ------
    30 Designer      6 Black      2850  [N/A]
       Manager      12 Jane        800  [N/A]
       Tester       10 Take       1500      0
       Tester        2 Jack       1600    300
       Tester        3 Wil        1250    500
       Tester        5 Mary       1250   1400
14 rows selected.
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/SQL_Plus/break&oldid=1698»