Bohemian life

select *

from emp

where regexp_like(ename, 'A');

select *

from emp

where regexp_like(ename, 'A.E');

select *

from emp

where regexp_like(ename, 'L+');

select *

from emp

where regexp_like(ename, '[AB]');

select *

from emp

where regexp_like(ename, '[^AB]');

select *

from emp

where regexp_like(ename, '^A');

select *

from emp

where regexp_like(ename, 'N$');

select *

from emp

where regexp_like(ename, '^A.*N$');

select *

from emp

where regexp_like(ename, '[A-D]');

§문제) 다음의 테이블 데이터 중 아래의 regular expression을 

만족하는 데이터는 무엇입니까?

regular expression: '[^Ale|ax.r$]'

       NO NAME

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

        1 Alex

        2 Alax

        3 Alexer

        4 Alexendar

        5 Alexender

drop table t_names;

create table t_names (

 no number

, name varchar2(30)

);

insert into t_names values (1, 'Alex');

insert into t_names values (2, 'Alax');

insert into t_names values (3, 'Alexer');

insert into t_names values (4, 'Alexendar');

insert into t_names values (5, 'Alexender');

commit;

select * from t_names

where regexp_like(name, '[^Ale|ax.r$]');

       NO NAME

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

        4 Alexendar

        5 Alexender

'[^Alex|ax.r$]' ==> NOT IN ('A', 'l', 'e', 'x', '|', 'a', '.', 'r', '$')

       NO NAME

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

        1 Alex ==> 'A', 'l', 'e', 'x'가 위 리스트에 모두 포함 ==> 탈락

        2 Alax ==> 'A', 'l', 'a', 'x'가 위 리스트에 모두 포함 ==> 탈락

        3 Alexer ==> 'A', 'l', 'e', 'x', 'r'이 위 리스트에 모두 포함 ==> 탈락

        4 Alexendar ==> 'n', 'd'는 위 리스트에 포함되어 있지 않으므로 ==> 통과

        5 Alexender ==> 'n', 'd'는 위 리스트에 포함되어 있지 않으므로 ==> 통과

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

  cf.) select * from t_names

where regexp_like(name, '^Ale|ax.r$');

↕ ↕ ↕

select * from t_names

where regexp_like(name, '^Ale')

  or regexp_like(name, 'ax.r$');

§예제 두가지 

  http://goo.gl/FyNSq

    * POSIX Operators         ==> http://goo.gl/KevzQ

- POSIX Character Class ==> http://goo.gl/OSdcS

ex) [abcdefghijklmnopqrstuvwxyz] ==> [:lower:]

    * PERL-Influenced Extensions to POSIX Standard ==> http://goo.gl/Avm1I

    * Or operator: |

- 'ski|ull' = 'ski' or 'ull'

- 'sk(i|u)ll' = 'skill' or 'skull'

- 'ski|ull|ful' = 'ski' or 'ull' or 'ful'

- '^ski|ull$' = '^ski' or 'ull$'

    * Matching Character List operator: [ ]

- all operators except these are treated as literals:

~ Range operator: -

~ POSIX character class: [: :]

~ POSIX collation element: [. .]

~ POSIX character equivalence class: [= =]

- '[a-d]' = IN ('a', 'b', 'c', 'd')

- '[skill]' = IN ('s', 'k', 'i', 'l')

- '[ski|ull]' = IN ('s', 'k', 'i', '|', 'u', 'l')

- '[[:alpha:]]' = all alphabetic characters

- '[^skill$]' = ???, 아래 NonMatching Character List operator 참조

   * NonMatching Character List operator: [^ ]

- '[^a-d]' = NOT IN ('a', 'b', 'c', 'd')

- '[^skill$]' = NOT IN ('s', 'k', 'i', 'l', '$')

- '[^ski|ull$]' = NOT IN ('s', 'k', 'i', '|', 'u', 'l', '$')

- '[^sk(i|u)ll]'= all except for ...

§Regular Expression (Regexp, Regex, 정규표현식)

  = 특정한 규칙을 가진 문자열의 집합을 표현하는 데 사용하는 형식 언어

  # 컴퓨터 언어에서의 정규표현식

    - 대상 문자열에서 특정 패턴의 문자열을 찾을 때 사용

§오라클에서 Regular expression

  # Regular Expression Condition & Functions

    * REGEXP_LIKE ─> Condition ─> WHERE, CHECK, WHEN, etc.

    * REGEXP_INSTR ┐

    * REGEXP_SUBSTR ┼> Functions ─> expression, argument, etc.

    * REGEXP_REPLACE ┤

    * REGEXP_COUNT (11g)┘

      - REGEXP_LIKE 제외하고 starting position, occurrence 옵션 가능

# 추가 : connect by level <= 100

==> http://goo.gl/X8uHT

        col level format 999

col empno format a20

col path format a30

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal

    , sys_connect_by_path(ename, '/') as path

from emp e

start with empno = 7839    

connect by prior empno = mgr

order siblings by sal;

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal

    , sys_connect_by_path(ename, '/') as path

from emp e

where empno = 7369

start with empno = 7839

connect by prior empno = mgr

order siblings by sal;

# 정렬: Order siblings by : connect by 절이 있을 때만 사용 가능

  - Do NOT specify either ORDER BY or GROUP BY, because they will destroy the hierarchical order.

==> ORDER SIBLINGS BY 사용

    - 계층 질의 원결과

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal 

from emp e

start with empno = 7839    

connect by prior empno = mgr;

    - ORDER BY 사용

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal 

from emp e

start with empno = 7839    

connect by prior empno = mgr

order by sal;

    - ORDER SIBLINGS BY 사용

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal 

from emp e

start with empno = 7839    

connect by prior empno = mgr

order siblings by sal;

  * Node 자르기: WHERE 절

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal 

from emp e

where empno <> 7698

start with empno = 7839    

connect by prior empno = mgr;

  * Branch 자르기: CONNECT BY 절

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal 

from emp e

start with empno = 7839    

connect by prior empno = mgr and empno <> 7698;

        col empno format a20

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal 

from emp e

start with empno = 7839    

connect by prior empno = mgr;

select level, lpad(empno, level*4, ' ') as empno, ename, job, mgr, sal 

from emp e

start with empno = 7369    

connect by empno = prior mgr;

   select level, e.* from emp e;

--> ORA-01788: CONNECT BY clause required in this query block

select level, e.*

from emp e

start with empno =  7839    

connect by prior empno = mgr;

    -- 원하는 레벨만 확인하기

select level, e.* 

from emp e

where level in (1, 2)

start with empno =  7839    

connect by prior empno = mgr;

 - PRIOR가 붙은 쪽이 parent row 임을 뜻함

  * Top-Down: PK에 PRIOR

select *

from emp

start with empno =  7839    

connect by prior empno = mgr;

  * Bottom-Up: FK에 PRIOR

select *

from emp

start with empno =  7521    

connect by empno = prior mgr;

  - a relationship exists between rows in the same table

      => a kind of self-join

  - restriction: SELECT can NOT contain a join or query of join

  - tree walking: null이 나올 때까지

  * Clauses

    - START WITH: starting point of tree walking

                  CAN contain a subquery

                  omitted, all row are considered as root rows

    - CONNECT BY: direction of tree-walking

                  can NOT contain a subquery

    - PRIOR: Parent Row

             can be in connect by and select-list clauses

             can be in either side of condition

  * Hierarchical Query Pseudocolumn

    - LEVEL

  * 쿼리 블럭: WITH 쿼리 블럭 이름 AS (subquery)

- 사용자의 임시 테이블스페이스에 저장

- in-line view 또는 임시 테이블로 쓰임

  * Benefits

- easy to read

- write once, read multiple times (reusability)

- performance improvement

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

WITH

dept_costs AS (

   SELECT   d.department_name, SUM(e.salary) AS dept_total

   FROM     employees e JOIN departments d

   ON       e.department_id = d.department_id

   GROUP BY d.department_name),

avg_cost   AS (

   SELECT   SUM(dept_total)/COUNT(*) AS dept_avg

   FROM     dept_costs)

SELECT * 

FROM     dept_costs 

WHERE    dept_total > (SELECT dept_avg 

      FROM avg_cost)

ORDER BY department_name;

  * Guidelines

- only with SELECT

- visibility of query name: all WITH elements and their subqueries defined after it

                                    the main query block and its subqueries

        - 쿼리 이름과 테이블 이름이 같다면 쿼리 이름이 우선권을 가짐

- WITH 절에 하나 이상의 쿼리 블럭이 올 수 있으며 콤마로 구분

  문제: 데이터가 중복일 경우 하나만 남기고 제거하기

drop table e1 purge;

create table e1 as select * from emp;

insert into e1 select * from emp;

insert into e1 select * from emp;

commit;

select * from e1 order by 1;

select rowid, e1.* from e1 order by empno;

delete from e1 ot

where rowid > (select min(rowid) from e1

              where empno = ot.empno);

select * from e1;

        drop table d1 purge;

drop table e1 purge;

create table d1 as select * from dept;

create table e1 as select * from emp;

  (1) 집계하는 경우 

alter table d1 add (sum_sal number);

update d1 

set sum_sal = (select sum(sal) from e1

      where deptno = 10)

where deptno = 10;

select * from d1;

↓↓

update d1 

set sum_sal = (select sum(sal) from e1

              where e1.deptno = d1.deptno);

select * from d1;

  (2) 컬럼을 추가하는 경우 

alter table e1 add (dname varchar2(30));

update e1 

set dname = 'ACCOUNTING'

where deptno = 10;

select * from e1;

↓↓

update e1

set dname = (select dname from d1

            where deptno = e1.deptno);

select * from e1;

  - 서브쿼리(내부 쿼리)의 조건을 만족하는 현재 행(외부 쿼리의 행)이 존재하는지 점검

  - 서브쿼리가 적어도 하나의 행을 반환

=> 참을 반환

=> 서브쿼리 탐색 종료

=> 외부 쿼리의 다른 행에 대해 탐색 시작

  문제 : 부하직원이 1명 이상인 사원?

select * from emp e

where 1 <= (select count(*) from emp

           where mgr = e.empno);

↓ ↓ ↓

select * from emp e

where exists (select 'x' from emp

             where mgr = e.empno);

  문제 : 사원이 한명도 없는 부서는?

select * from dept

where deptno not in (select deptno from emp

                    group by deptno);

↓ ↓ ↓

select * from dept d

where 0 = (select count(*) from emp

          where deptno = d.deptno);

↓ ↓ ↓

select * from dept d

where not exists (select 'x' from emp

 where deptno = d.deptno);