Oracle PL/SQL/Select Query/With

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

With statement and subquery

   <source lang="sql">
 

SQL> 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> with g as

 2        (select   x.deptno
 3         ,        avg(x.sal) avg_sal
 4         from     emp x
 5         group by x.deptno)
 6  select e.ename, e.init, e.sal
 7  from   emp e
 8         join      g
 9         using (deptno)
10  where  e.sal > g.avg_sal;

ENAME INIT SAL


----- ----------

Peter CC 5000 Jane JM 2975 Fake MG 3000 Smart SCJ 3000 Jack JAM 1600 Black R 2850 6 rows selected. SQL> SQL> SQL> drop table emp; Table dropped. SQL>


 </source>
   
  


With the simple CASE statement, you can add many more WHEN blocks

   <source lang="sql">
 

SQL> SQL> BEGIN

 2    CASE
 3      WHEN 1 = 2 THEN
 4        dbms_output.put_line("Case [1 = 2]");
 5      WHEN 2 = 2 THEN
 6        dbms_output.put_line("Case [2 = 2]");
 7      ELSE
 8        dbms_output.put_line("No match");
 9    END CASE;
10  END;
11  /

Case [2 = 2] PL/SQL procedure successfully completed.


 </source>