Kongzz' blog

오라클의 분석함수중에 Lag와 Lead에 대하여 정리해 보았다.

select lag(dates, 2) over (order by dates) pprevDate,
       lag(dates, 1) over (order by dates) prevDate,
       dates,
       lead(dates, 1) over (order by dates) nextDate, 
       lead(dates, 2) over (order by dates) nnextDate,
       '' space,
       lag(no, 2) over (order by dates) pprevNo,
       lag(no, 1) over (order by dates) prevNo,
       no, 
       lead(no, 1) over (order by dates) nextNo, 
       lead(no, 2) over (order by dates) nnextNo
  from (
        select no, to_char(sysdate+no,'YYYY-MM-DD') dates 
          from dual,
               (select level no from dual connect by level < 10)
       )

lag(column_name, n)

  : 현재 row 기준으로 n 개 이전 row 의 값의 column의 값을 표시한다.

  ex) lag(no, 2) over (order by dates)

     는 정렬순서는 dates를 기준으로 하여서 no 컬럼의 2번째 앞에 row의 값을 표시하게 하는 함수이다.

lead(column_name, n)

  :  현재 row 기준으로 n개 이후 row의 값의 column의 값을 표시한다.

  ex) lead(no, 2) over (order by dates)

     는 정렬순서는 dates를 기준으로 하여서 no 컬럼의 2번째 뒤의 row의 값을 표시하게 하는 함수이다.

Oracle 10g 이상에서 V$SESSION에  Client_Info 라는 컬럼이 존재한다.

여기에 유용한 Trigger를 하나 적용을 해서 이 컬럼을 아주 유용하고 이용하고 있어 정리해 보았다.

위의 트리거를 DB에 생성해 놓으면

접속하는 Client 의 ip 정보를 V$SESSION 의 Client_Info라는 컬럼에서 조회해 볼 수 있다.

Database 사용하다가 실수로 Update 나 Delete를 잘못할수도 있다.

Commit을 하였어도 어느 정도의 시간내에서는 시간을 거슬러서 그 시점에서의 Data 상태를 볼수가 있다.

※ 어느 정도의 시간이란 운영중인 DB의 트랜잭션양에 영향을 받기 때문에 그 정도를 가늠하기는 어렵다.

AS OF TIMESTAMP 문의 위치는 FROM [table_name] 과 WHERE 사이에 위치한다.

또 다른 사용방법은 원하는 시간으로 바로 설정하여 조회할 수 있다.

* 위의 기본문법으로 아래와 같이 응용을 해볼수도 있다.

- 동일한 데이터를 시간차를 두고 비교를 하는 형식으로도 써보았다.

  두 시간 사이의 데이터 비교도 유용하게 써먹어 본적이 있다.

- 조회된 데이터를 근거로 현재의 데이터를 다시 Update하거나, 

삭제된 경우라면 

 이런 형식으로 데이터를 복구할 수 있다.

SQL> select * from student order by class, id;

CLASS      ID              SCORE    RANKING
---------- ---------- ---------- ----------
1-1        11                 78
1-1        12                 67
1-1        13                 88
1-1        14                 69
1-1        15                 78
1-1        16                 63
1-1        17                 84
1-1        18                 92
1-2        11                 86
1-2        12                 67
1-2        13                 94
1-2        14                 87
1-2        15                 72

13 개의 행이 선택되었습니다.

SQL> MERGE INTO student A
  2      USING
  3      (WITH v_student
  4            AS (SELECT CLASS, ID,
  5                       rank() over (partition by CLASS  order by score) NEW_RANK
  6                  FROM STUDENT
  7                 WHERE CLASS like '1%'
  8                )
  9                SELECT * FROM v_student
 10      ) B
 11      ON (A.CLASS = B.CLASS AND A.ID = B.ID)
 12   WHEN MATCHED THEN
 13     UPDATE SET A.RANKING = B.NEW_RANK;

13 행이 병합되었습니다.

SQL> SELECT * FROM student order by class, ranking;

CLASS      ID              SCORE    RANKING
---------- ---------- ---------- ----------
1-1        16                 63          1
1-1        12                 67          2
1-1        14                 69          3
1-1        11                 78          4
1-1        15                 78          4
1-1        17                 84          6
1-1        13                 88          7
1-1        18                 92          8
1-2        12                 67          1
1-2        15                 72          2
1-2        11                 86          3
1-2        14                 87          4
1-2        13                 94          5

13 개의 행이 선택되었습니다.

* line 3: WITH문을 이용하여 v_student 라는 subQuery에서 window function을 이용하여 원하는 결과치의 쿼리를 작성한다.

* line 11: student table과 v_student를 조인한다.

* line 13: student 의 ranking에 v_student의 new_rank가 update 된다.

※ MERGE 문은 Oracle 9i부터 나왔으며,

    9i때는 WHEN NOT MATCHED THEN 에 대한 내용이 없으면 에러가 난다고 한다.

    10g 이상에서는 오류없이 잘 수행된다.

Oracle pivot 사용법(11g부터 가능)

SQL> select * from (
  2      select job, sal, deptno
  3        from emp
  4  )
  5  pivot
  6  (
  7    SUM(sal) as sal, count(sal) as cnt
  8    for job in ('CLERK',  'MANAGER', 'ANALYST')
  9  )
 10  order by deptno
 11  ;

    DEPTNO 'CLERK'_SAL 'CLERK'_CNT 'MANAGER'_SAL 'MANAGER'_CNT 'ANALYST'_SAL 'ANALYST'_CNT
---------- ----------- ----------- ------------- ------------- ------------- -------------
        10        1300           1          2450             1                           0
        20        1900           2          2975             1          6000             2
        30         950           1          2850             1                           0

SQL>

line8 : group by 에 해당하는 필드(job)을 기재한다.

         in 안에는 title 분류들이 들어간다.

* Oracle 11g에서 생긴 개념

<Read Only Table>

SQL> select * from bbb;

DATE1      DATE2      PTNO     ROOM1      ROOM2
---------- ---------- -------- ---------- ----------
2011-01-01 2011-06-09 00000001
2011-06-09 2012-01-01 00000001
2012-01-01 2012-04-05 00000001

SQL> alter table bbb read only;

테이블이 변경되었습니다.

SQL> delete from bbb where date1 = '2012-01-01' ;
delete from bbb where date1 = '2012-01-01'
            *
1행에 오류:
ORA-12081: "DREAMER"."BBB" 테이블에 작업을 갱신하는 것이 허용되지 않습니다

SQL> alter table bbb read write;

테이블이 변경되었습니다.

SQL>

※ Read Only Table은 DML에 대해서만 Read only가 된다.

    DDL은 일반 명령어처럼 적용된다.

SQL> alter table bbb read only;

테이블이 변경되었습니다.

SQL> drop table bbb;

테이블이 삭제되었습니다.

SQL>

1. 테이블 삭제

SQL> drop table emp;

테이블이 삭제되었습니다.

2.휴지통 조회

SQL> select original_name, operation, droptime from recyclebin;

ORIGINAL_NAME                    OPERATION DROPTIME
-------------------------------- --------- -------------------
EMP2                             DROP      2014-12-09:20:21:15
EMP                              DROP      2014-12-09:20:23:44

3.지운 테이블의 복구

SQL> flashback table emp to before drop;

플래시백이 완료되었습니다.

- 새로운 이름으로 복구를 할려고 할 때는

SQL>flashback table emp to before drop rename to emp_back;

※ Oracle 의 휴지통은 Windows의 휴지통과 달라서 복구가 안될수도 있다.

<기타 1>

 *. 삭제시 purge문을 사용하게 되면 flashback을 못 시킨다.

SQL> drop table emp purge;

테이블이 삭제되었습니다.

SQL> select original_name, operation, droptime from recyclebin;

ORIGINAL_NAME                    OPERATION DROPTIME
-------------------------------- --------- -------------------
EMP2                             DROP      2014-12-09:20:21:15

<기타 2>

*.Recycbin에서 특정 객체만 지우는 방법.

- create table이 여러분 수행되어있어서 특정 시간대의 삭제된 테이블을 복구할려고 할때,

  복구를 수행할려고 하는 바로 그 이전 객체까지 지우고 복구시 유용하게 사용했던 명령어.

SQL> select object_name, original_name, operation, droptime from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME                    OPERATION DROPTIME
------------------------------ -------------------------------- --------- -------------------
BIN$yC3OBhceROujpA/W46vgow==$0 EMP2                             DROP      2014-12-09:20:21:15

SQL> purge table "BIN$yC3OBhceROujpA/W46vgow==$0";

테이블이 지워졌습니다.

<기타 3>

*. 휴지통을 모두 비울때.

SQL> purge recyclebin;

휴지통이 지워졌습니다.

Oracle 재귀Query

재귀Query의 시작은 Start With 라는 문장으로

C:\>sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on 금 9월 5 17:18:58 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 11g Release 11.2.0.1.0 - Production

SQL> set line 150
SQL> set pagesize 200
SQL> col level format 99
SQL> col first_name format a12
SQL> col last_name format a12
SQL> col pr_emp format 999
SQL> col manager_id format 999
SQL> col cheif format a10
SQL> col isLeaf format 9
SQL> col path format a30
SQL>
SQL> select level, employee_id, first_name, last_name, prior employee_id pr_emp, manager_id,
  2          connect_by_root last_name cheif,
  3          connect_by_isleaf isLeaf,
  4          sys_connect_by_path(last_name, '/') path
  5     from employees
  6    start with manager_id is null
  7    connect by prior employee_id = manager_id
  8    order siblings by last_name;   (선택)

* 3 : 최종 노드인 경우 : 1

* 4 : 최초에서 최종까지를 구분자를 넣어서 표시를 해 줌

* 7 : connect by에서 나의 manager_id라는 값과 나의 선행에 해당하는 employee_id(prior employee_id) 값과 동일한 값

* 8 : 재귀 쿼리 내에서만 정렬됨 

LEVEL EMPLOYEE_ID FIRST_NAME   LAST_NAME    PR_EMP MANAGER_ID CHEIF      ISLEAF PATH
----- ----------- ------------ ------------ ------ ---------- ---------- ------ ----------------------------
    1         100 Steven       King                           King            0 /King
    2         148 Gerald       Cambrault       100        100 King            0 /King/Cambrault
    3         172 Elizabeth    Bates           148        148 King            1 /King/Cambrault/Bates
    3         169 Harrison     Bloom           148        148 King            1 /King/Cambrault/Bloom
    3         170 Tayler       Fox             148        148 King            1 /King/Cambrault/Fox
    3         173 Sundita      Kumar           148        148 King            1 /King/Cambrault/Kumar
    3         168 Lisa         Ozer            148        148 King            1 /King/Cambrault/Ozer
    3         171 William      Smith           148        148 King            1 /King/Cambrault/Smith
    2         102 Lex          De Haan         100        100 King            0 /King/De Haan
    3         103 Alexander    Hunold          102        102 King            0 /King/De Haan/Hunold
    4         105 David        Austin          103        103 King            1 /King/De Haan/Hunold/Austin
    4         104 Bruce        Ernst           103        103 King            1 /King/De Haan/Hunold/Ernst
    4         107 Diana        Lorentz         103        103 King            1 /King/De Haan/Hunold/Lorentz
    4         106 Valli        Pataballa       103        103 King            1 /King/De Haan/Hunold/Pataballa
    2         147 Alberto      Errazuriz       100        100 King            0 /King/Errazuriz
    3         166 Sundar       Ande            147        147 King            1 /King/Errazuriz/Ande
    3         167 Amit         Banda           147        147 King            1 /King/Errazuriz/Banda
    3         163 Danielle     Greene          147        147 King            1 /King/Errazuriz/Greene
    3         165 David        Lee             147        147 King            1 /King/Errazuriz/Lee
    3         164 Mattea       Marvins         147        147 King            1 /King/Errazuriz/Marvins
    3         162 Clara        Vishney         147        147 King            1 /King/Errazuriz/Vishney
    2         121 Adam         Fripp           100        100 King            0 /King/Fripp
    3         130 Mozhe        Atkinson        121        121 King            1 /King/Fripp/Atkinson
    3         129 Laura        Bissot          121        121 King            1 /King/Fripp/Bissot
    3         185 Alexis       Bull            121        121 King            1 /King/Fripp/Bull
    3         187 Anthony      Cabrio          121        121 King            1 /King/Fripp/Cabrio
    3         186 Julia        Dellinger       121        121 King            1 /King/Fripp/Dellinger
    3         131 James        Marlow          121        121 King            1 /King/Fripp/Marlow
    3         132 TJ           Olson           121        121 King            1 /King/Fripp/Olson
    3         184 Nandita      Sarchand        121        121 King            1 /King/Fripp/Sarchand
    2         201 Michael      Hartstein       100        100 King            0 /King/Hartstein
    3         202 Pat          Fay             201        201 King            1 /King/Hartstein/Fay
    2         122 Payam        Kaufling        100        100 King            0 /King/Kaufling
    3         188 Kelly        Chung           122        122 King            1 /King/Kaufling/Chung
    3         189 Jennifer     Dilly           122        122 King            1 /King/Kaufling/Dilly
    3         190 Timothy      Gates           122        122 King            1 /King/Kaufling/Gates
    3         135 Ki           Gee             122        122 King            1 /King/Kaufling/Gee
    3         133 Jason        Mallin          122        122 King            1 /King/Kaufling/Mallin
    3         191 Randall      Perkins         122        122 King            1 /King/Kaufling/Perkins
    3         136 Hazel        Philtanker      122        122 King            1 /King/Kaufling/Philtanker
    3         134 Michael      Rogers          122        122 King            1 /King/Kaufling/Rogers
    2         101 Neena        Kochhar         100        100 King            0 /King/Kochhar
    3         204 Hermann      Baer            101        101 King            1 /King/Kochhar/Baer
    3         108 Nancy        Greenberg       101        101 King            0 /King/Kochhar/Greenberg
    4         110 John         Chen            108        108 King            1 /King/Kochhar/Greenberg/Chen
    4         109 Daniel       Faviet          108        108 King            1 /King/Kochhar/Greenberg/Faviet
    4         113 Luis         Popp            108        108 King            1 /King/Kochhar/Greenberg/Popp
    4         111 Ismael       Sciarra         108        108 King            1 /King/Kochhar/Greenberg/Sciarra

    4         112 Jose Manuel  Urman           108        108 King            1 /King/Kochhar/Greenberg/Urman
    3         205 Shelley      Higgins         101        101 King            0 /King/Kochhar/Higgins
    4         206 William      Gietz           205        205 King            1 /King/Kochhar/Higgins/Gietz
    3         203 Susan        Mavris          101        101 King            1 /King/Kochhar/Mavris
    3         200 Jennifer     Whalen          101        101 King            1 /King/Kochhar/Whalen
    2         124 Kevin        Mourgos         100        100 King            0 /King/Mourgos
    3         142 Curtis       Davies          124        124 King            1 /King/Mourgos/Davies
    3         197 Kevin        Feeney          124        124 King            1 /King/Mourgos/Feeney
    3         199 Douglas      Grant           124        124 King            1 /King/Mourgos/Grant
    3         143 Randall      Matos           124        124 King            1 /King/Mourgos/Matos
    3         198 Donald       OConnell        124        124 King            1 /King/Mourgos/OConnell
    3         141 Trenna       Rajs            124        124 King            1 /King/Mourgos/Rajs
    3         144 Peter        Vargas          124        124 King            1 /King/Mourgos/Vargas
    3         196 Alana        Walsh           124        124 King            1 /King/Mourgos/Walsh
    2         146 Karen        Partners        100        100 King            0 /King/Partners
    3         160 Louise       Doran           146        146 King            1 /King/Partners/Doran
    3         156 Janette      King            146        146 King            1 /King/Partners/King
    3         158 Allan        McEwen          146        146 King            1 /King/Partners/McEwen
    3         161 Sarath       Sewall          146        146 King            1 /King/Partners/Sewall
    3         159 Lindsey      Smith           146        146 King            1 /King/Partners/Smith
    3         157 Patrick      Sully           146        146 King            1 /King/Partners/Sully
    2         114 Den          Raphaely        100        100 King            0 /King/Raphaely
    3         116 Shelli       Baida           114        114 King            1 /King/Raphaely/Baida
    3         119 Karen        Colmenares      114        114 King            1 /King/Raphaely/Colmenares
.

.

.

    3         180 Winston      Taylor          120        120 King            1 /King/Weiss/Taylor
    2         149 Eleni        Zlotkey         100        100 King            0 /King/Zlotkey
    3         174 Ellen        Abel            149        149 King            1 /King/Zlotkey/Abel
    3         178 Kimberely    Grant           149        149 King            1 /King/Zlotkey/Grant
    3         175 Alyssa       Hutton          149        149 King            1 /King/Zlotkey/Hutton
    3         179 Charles      Johnson         149        149 King            1 /King/Zlotkey/Johnson
    3         177 Jack         Livingston      149        149 King            1 /King/Zlotkey/Livingston
    3         176 Jonathon     Taylor          149        149 King            1 /King/Zlotkey/Taylor

107 개의 행이 선택되었습니다.

SQL>