Bohemian life

  * 흐름 제어 (Flow Control)

- if

- case

- loops

- exit

  * 블럭 (Blocks)

- anonymous, procedure, function

- modularization

- reusable

- 예외 처리

  * 변수 (Variables)

- 데이터를 담는 그릇

- 형(Type)과 크기(Size)

- %TYPE attribute

- 레코드와 배열

- Cursor

  * Integration of procedural constructs with SQL

  * Improved performance

  * Modularized program development - logical group of statements

                                    - nest blocks

                                    - smaller modules

                                    - easily maintain and debug

  * Integration with Oracle tools

- applications/tools (PL) + database (SQL)

  * Portability

  * Exception handling

  * PL/SQL Engine: 절차문 처리

  * Oracle Database Server: SQL문 처리

  * SQL(Manipulating Power) + 3GL(Processing Power)

  * Pascal -> Ada -> PL/SQL

  * Block Structured Language: Anonymous/Unnamed Block

                                        vs. 

                               Named Block/Subprogram (Procedure, Function, Package, Trigger)

declare

 선언부

begin

 실행부           ==> # 1-9: PL/SQL Block Structure

exception

 예외처리부

end;

create or replace procedure 이름 (매개변수 설정)

as

 선언부

begin

 실행부

exception

 예외처리부

end;

create or replace funcition 이름 return 데이터_타입 (매개변수 설정)

as

 선언부

begin

 실행부

 return ...;

exception

 예외처리부

end;

# Program?

  - 해야 할 일을 (논리적 흐름/순서에 따라) 미리 기술해 놓은 것

oj_SQL.txt

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;