'개발자 노트'에 해당되는 글 331건

KEEP (최초값, 최종값)

KEEP 함수의 사용방법은 아래와 같으며, 예제를 실습하면서 이해해 보겠습니다.

• - MAX() KEEP(DENSE_RANK FIRST[LAST] ORDER BY)
• - MIN() KEEP(DENSE_RANK FIRST[LAST] ORDER BY)

아래는 부서별 최대급여자 급여(MAX_SAL)와 사번(MAX_E), 최소급여자 급여(MIN_SAL)와 사번(MIN_E)을 조회하는 예제입니다.

분석: 순서대로 보기 바람 위에 keep(최초값, 최종값)은 무시하고 보기바람.

KEEP을 사용하지 않을경우 아래와 같은 SQL문으로 동일한 결과를 얻을 수 있습니다.

분석:분석눈으로만 해보세요.

LISTAGG (문자열합치기)

• - 함수 이름 그대로 LIST를 구분자로 AGGREGATE 해주는 함수이며, 행 데이터를 특정 구분자를 가지는 열로 쉽게 표현할 수 있다.
• - Oracle11g R2 버전부터 사용 할 수 있다.
• - LISTAGG(컬럼, 구분값) WITHIN GROUP( ORDER BY 절) 형식으로 사용한다.

아래는 버전별 정리로 문자열 합치는 예제이다.

WM_CONCAT은 정렬을 사용 할 수 없으며, 그 외 방법들은 정렬 기능을 사용 한 것을 확인 할 수 있다.

태그

• KEEP LISTAGG WM_CONCAT XMLAGG 문자열합치기

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2676
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

LAG, LEAD (다른 행의 값을 참조)

• - LAG (컬럼명[,offset][,default]) : 현재 ROW 기준으로 이전 행을 참조
• - LEAD (컬럼명[,offset][,default]) : 현재 ROW 기준으로 다음 행을 참조

아래 예제는 이전 row(S_1), 다음 row(S_2), 다음 두 번째 row(S_3)의 급여를 조회하는 예제이다. 다음 두 번째 row(S_3) 값이 NULL인 경우 0을 출력한다.

분석: 순서대로 보기 바람

RATIO_TO_REPORT (점유율)

RATIO_TO_REPORT 함수는 합에 대한 값의 비율을 계산한다. 아래 예제를 통해서 RATIO_TO_REPORT 함수에 대해 이해해보자

위 예제에 대해서 좀 더 설명하면 아래와 같다.

• - SAL : 부서별 급여 합계를 집계함수를 사용하여 계산한 결과이다.
• - TOT : SUM 분석함수를 이용하여 부서별 급여 합계의 전체 합계를 계산한 결과이다.
• - RAT1 : 집계함수를 이용해서 계산한 부서별 합계에서 분석함수를 이용해 계산한 전체 합계를 나누어 비율을 계산한 결과이다.
• - RAT2 : RATIO_TO_REPORT 분석함수를 사용하여 부서별 급여 합계의 비율을 계산한 결과이다.

분석:순서대로 보기 바람.

FIRST_VALUE, LAST_VALUE

• - FIRST_VALUE : 정렬된 값 중에서 첫 번째 값을 반환한다.
• - LAST_VALUE : 정렬된 값 중에서 맨 마지막 값을 반환한다.

아래 예제는 사원순 정렬의 첫번째 사원 급여(F_EMP)와 마지막 사원 급여(L_EMP)를 조회하는 예이다.

분석:순서대로 보기 바람

LAST_VALUE 직전값 대체

LAST_VALUE 사용시 값이 없을 경우 이전 값을 참조하는 예제이다.

태그

• LEAD LAG FIRST_VALUE LAST_VALUE RATIO_TO_REPORT 분석함수

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2675
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

윈도우 함수란

• - 분석함수 중에서 윈도우절(WINDOWNING 절)을 사용하는 함수를 윈도우 함수라고 한다.
• - 윈도우절을 사용하면 PARTITION BY 절에 명시된 그룹을 좀 더 세부적으로 그룹핑 할 수 있다.
• - 윈도우절은 분석함수중에서 일부(AVG, COUNT, SUM, MAX, MIN)만 사용 할 수 있다.

WINDOWNING 절 Syntax

• - ROWS : 물리적인 ROW 단위로 행 집합을 지정한다.
• - RANGE : 논리적인 상대번지로 행 집합을 지정한다.
• - BETWEEN ~ AND 절 : 윈도우의 시작과 끝 위치를 지정한다.
• - UNBOUNDED PRECEDING : PARTITION의 첫 번째 로우에서 윈도우가 시작한다.
• - UNBOUNDED FOLLOWING : PARTITION의 마지막 로우에서 윈도우가 시작한다.
• - CURRENT ROW : 윈도우의 시작이나 끝 위치가 현재 로우 이다.

WINDOWING 절 예제

ROWS 사용 예제1

아래는 부서별(PARTITION BY deptno)로 이전 ROW(ROWS 1 PRECEDING)의 급여와 현재 ROW의 급여 합계를 출력하는 예제이다

이쿼리 분석: 순번대로 보기 바람.

SELECT empno, ename, deptno, sal, 

       SUM(sal) OVER (PARTITION BY deptno  //1번 부서별로 그룹을 맺으며

                          ORDER BY empno //2번 정렬은 사번으로.

                           ROWS 1 PRECEDING ) pre_sum //3번 이전 로우1개와의 합을 pre_sum으로 출력

  FROM emp;

ROWS 사용 예제2

아래 예제는 첫 번째 ROW부터 마지막 ROW까지의 합과(SAL1), 첫 번째 ROW부터 현재 ROW까지의 합(SAL2) 그리고 현재 ROW부터 마지막 ROW까지의 합(SAL3)을 출력하는 예제이다.

이쿼리 분석: 순서대로 보기 바람.

RANGE 사용 예제

아래는 월별 금액 리스트를 출력하고, 직전 3개월 합계(AMT_PRE3)와 이후 3개월 합계(AMT_FOL3)를 함께 표시하는 예제이다.

아래 예제에서는 7월 데이터가 없기 때문에 직전 3개월 합계(AMT_PRE3) 8월의 경우 6월,5월 두 달치만 누적된 것을 확인 할 수 있다.

분석:순서대로 보기 바람. //좀힘들었음.

SELECT yyyymm

     , amt

     , SUM(amt) OVER(ORDER BY TO_DATE(yyyymm,'yyyymm')

                RANGE BETWEEN INTERVAL '3' MONTH PRECEDING

                          AND INTERVAL '1' MONTH PRECEDING) amt_pre3 //1번 시작이 3개월 전부터  1개얼 전까지의 합임 정렬은 당연히 날짜순.. 

     , SUM(amt) OVER(ORDER BY TO_DATE(yyyymm,'yyyymm')

                RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING

                          AND INTERVAL '3' MONTH FOLLOWING) amt_fol3 //2번 1개월후부터 3개월후까지의 합 정렬은 날짜순 

  FROM test

;

태그

• PRECEDING FOLLOWING WINDOWING 절 윈도우 함수 분석함수

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2674
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

SUM, MIN, MAX, AVG, COUNT등의 집계 함수도 분석함수로 사용할 수 있다.

SUM (누적합계)

아래는 사원의 전체급여 합계(all_sum), 부서별 급여 합계(dept_sum), 부서별로 누적 합계(nujuk_sum)를 조회하는 예이다.

• - ALL_SUM : PARTITION BY절과 ORDER BY절이 생략되어 전체 급여 합계를 계산한다.
• - DEPT_SUM : PARTITION BY절에 deptno를 사용하여 부서별 급여 합계를 계산한다.
• - NUJUK_SUM : 부서별로 구분하고, empno로 ORDER BY절을 사용하였다. 사원번호 별로 급여 합계가 누적되어 계산되는 것을 확인 할 수 있다.

태그

• 집계함수 SUM COUNT 누적합 분석함수

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2673
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

순위함수

• - RANK 함수는 순위를 부여하는 함수로 동일순위 처리가 가능하다. (중복 순위 다음 순서 건너뜀 - 1,2,2,4)
• - DENSE_RANK 함수는 RANK 함수와 같은 역할을 하지만 동일 등수가 순위에 영향이 없다.(중복순위 다음 순위 연속 - 1,2,2,3)
• - ROW_NUMBER 함수는 특정 순위로 일련번호를 제공하는 함수로 동일순위 처리가 불가능하다. (중복순위 없이 유일값 - 1,2,3,4)
• - 순위 함수 사용시 ORDER BY절은 필수로 입력해야 한다.

순위함수 - RANK

아래는 급여가 높은 순서대로 순위를 구하는 예제이다.

급여가 같은 경우 아래와 같이 동일 순위로 처리되는 것을 확인 할 수 있다.

• [그림] RANK 실행결과
• 

아래는 부서별(PARTITION BY deptno)로 급여가 높은 순서대로(ORDER BY sal DESC) 순위를 구하는 예제이다.

순위함수 비교

아래 예제를 통해서 RANK, DENSE_RANK, ROW_NUMBER 함수의 차이점을 이해하자

• [그림] 순위함수 비교 실행결과
• 

순위함수 - NTILE (분류)

NTILE 함수는 쿼리의 결과를 n개의 그룹으로 분류하는 기능을 제공한다. 아래 예제에서 GRP2는 두 개의 그룹으로, GRP3는 세 개의 그룹으로, GRP5는 다섯개의 그룹으로 분류하는 것을 알 수 있다.

태그

• RANK DENSE_RANK ROW_NUMBER NTILE 순위함수 분석함수

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2672
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

분석함수란?

• - 테이블에 있는 데이터를 특정 용도로 분석하여 결과를 반환하는 함수
• - 복잡한 계산을 단순하게 처리해주는 함수
• - 쿼리 결과Set을 대상으로 계산을 수행하는 함수
• - SELECT 절에서 수행됨
  • FROM, WHERE, GROUP BY 절에서 사용 불가
  • ORDER BY 구문에서는 사용 가능
• - 오라클 8.1.6 부터 분석함수 지원

집계함수 vs 분석함수

집계함수

집계함수는 여러행 또는 테이블 전체 행으로부터 그룹별로 집계하여 결과를 반환한다.

• [그림] 집계함수 실행결과
• 

분석함수

분석 함수는 집계 결과를 각 행마다 보여준다.

• [그림] 분석함수 실행결과
• 

집계함수 vs 분석함수

• - 집계함수는 그룹별 최대, 최소, 합계, 평균, 건수 등을 구할 때 사용되며, 그룹별 1개의 행을 반환한다.
• - 분석함수는 그룹단위로 값을 계산한다는 점에서 집계함수와 유사하지만, 그룹마다가 아니라 결과Set의 각 행마다 집계결과를 보여준다는 점에서 집계함수와 상당한 차이가 있다.
• - 분석함수는 쉽게 생각해서, 그룹별 계산결과를 각 행마다 보여주는 것이다.

Syntax

• - ANALYTIC_FUNCTION : 분석함수명(입력인자)
• - OVER : 분석함수임을 나타내는 키워드.
• - PARTITION BY : 계산 대상 그룹을 정한다.
• - ORDER BY : 대상 그룹에 대한 정렬을 수행한다.
• - WINDOWING 절 : 분석함수의 계산 대상 범위를 지정한다.
  • ORDER BY 절에 종속적이다.
  • 기본 생략 구문 : 정렬된 결과의 처음부터 현재행까지 [RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]

분석함수의 종류

• - 순위함수 : RANK, DENSE_RANK, ROW_NUMBER, NTILE
• - 집계함수 : SUM, MIN, MAX, AVG, COUNT
• - 기타함수 : LEAD, LAG, FIRST_VALUE, LAST_VALUE, RATIO_TO_REPORT
• - 분석함수(?) : KEEP, LISTAGG

태그

• 분석함수 OVER PARTITION BY 집계함수 WINDOWING 절 PRECEDING FOLLOWIN

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2671
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

A CONNECT BY LEVEL  여기서 A는 무시해도 될꺼 같음. 

WITH t ( sd, dt, cd, lv ) AS

(

    SELECT '2014-10-11',    '2014-10-12',    'BGO',    5    FROM DUAL UNION ALL

    SELECT '2014-10-12',    '2014-10-14',    'BGA',    1    FROM DUAL UNION ALL

    SELECT '2014-10-14',    '2014-10-15',    'BGB',    1    FROM DUAL UNION ALL

    SELECT '2014-10-18',    '2014-10-19',    'BGC',    1    FROM DUAL UNION ALL

    SELECT '2014-10-18',    '2014-10-19',    'BGD',    1    FROM DUAL UNION ALL

    SELECT '2014-10-18',    '2014-10-19',    'BGE',    1    FROM DUAL UNION ALL

    SELECT '2014-10-18',    '2014-10-19',    'BGF',    1    FROM DUAL

)

////이렇게 데  T라는 가상의 데이터 테이블 T를 만들어 넣고... 순서대로 보자... 

SELECT A.*,

       T.CD,

       T.lv

  FROM

       (SELECT TO_CHAR(TO_DATE(SD,'YYYY-MM-DD') + (LEVEL-1),'YYYY-MM-DD') SD

         FROM

              (SELECT MIN(SD) SD

                FROM T //2번 최소 날짜를 구함.

              ) A CONNECT BY LEVEL <=

              (SELECT TO_DATE(MAX(DT),'YYYY-MM-DD')+1 - TO_DATE(MIN(SD),'YYYY-MM-DD')

                FROM T                   //1번 DT의 후속날자최대값에서 SD의 최소날짜값을빼면 9일이 나옴.. 

              )

       ) A //3번 최소날짜로부터 LEVEL일수만큼 더해진 날을 구함 LEVEL은 1부터 시작하므로 9까지 총 9번 연산되어 11일부터 19일까지 구해짐.

   LEFT OUTER JOIN T

       ON A.SD BETWEEN T.SD AND T.DT

WHERE T.CD IS NOT NULL; //4번 레프트 아웃트 조인으로 인하여 위에 구하여진 날짜값을 기준으로 그날짜에 포함되어진 값을 구하는데 T.CD 가 널이 아닌경우에만 구함. 

추가로 CONNECT BY LEVEL 에 대해서 검색내용을 추가함. 

Oracle10g 부터 CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 기능에 대해서 알아보자

CONNECT_BY_ROOT

계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있다.

CONNECT_BY_ISLEAF

계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환한다. 최하이 레벨이면 1, 아니면 0

SYS_CONNECT_BY_PATH

계층구조 쿼리에서 현재 로우 까지의 PATH 정보를 쉽게 얻어 올 수 있다.

아래와 같이 Leaf Node만 전체 PATH 정보가 나오도록 작성 할 수 있다

ORDER SIBLINGS BY

계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해준다. 아래 예제를 가지고 ORDER SIBLINGS BY와 ORDER BY로 테스트 해보자

태그

• CONNECT_BY_ROOT SYS_CONNECT_BY_PATH CONNECT_BY_ISLEAF CONNECT BY START WITH PRIOR ORDER SIBLINGS BY계층구조쿼리

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/1903
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

출처: http://zent.tistory.com/64

with EMP as (
SELECT 10 AS DEPTNO, 'CLARK  ' AS ENAME FROM DUAL
UNION
SELECT 10 AS DEPTNO, 'KING   ' AS ENAME FROM DUAL
UNION
SELECT 10 AS DEPTNO, 'MILLER ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'SMITH  ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'ADAMS  ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'FORD   ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'SCOTT  ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'JONES  ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'ALLEN  ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'BLAKE  ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'MARTIN ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'JAMES  ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'TURNER ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'WARD   ' AS ENAME FROM DUAL
) 
SELECT DEPTNO, LTRIM(SYS_CONNECT_BY_PATH(ENAME, ','),',') ENAME
FROM (
SELECT DEPTNO
, ENAME
, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM
, COUNT(*) OVER (PARTITION BY DEPTNO) CNT
FROM EMP
)
WHERE LEVEL = CNT
START WITH RNUM = 1
CONNECT BY PRIOR DEPTNO = DEPTNO 
AND PRIOR RNUM = RNUM - 1

결과

DEPTNO   ENAME 
--------    ----------------------
10             MILLER ,KING   ,CLARK   
20             JONES  ,FORD   ,ADAMS  ,SCOTT  ,SMITH   
30             ALLEN  ,BLAKE  ,JAMES  ,MARTIN ,TURNER ,WARD    

* 다른방법
select deptno, XMLAgg(XMLElement("nm", ename||',')).EXTRACT('//text()').GetStringVal() ename 
from emp
group by deptno

결과

DEPTNO ENAME 
--------  --------------------
10           CLARK  ,KING   ,MILLER , 
20           ADAMS  ,SMITH  ,SCOTT  ,FORD   ,JONES  , 
30           ALLEN  ,JAMES  ,TURNER ,WARD   ,MARTIN ,BLAKE  , 


* Decode 를 사용하는 방법 (컬럼갯수에 따라 달라진다 번거로움..)

SELECT DEPTNO,
            MAX(DECODE(RID, 1, ENAME)) EMP1,
            MAX(DECODE(RID, 2, ENAME)) EMP2,
            MAX(DECODE(RID, 3, ENAME)) EMP3,
            MAX(DECODE(RID, 4, ENAME)) EMP4,
            MAX(DECODE(RID, 5, ENAME)) EMP5,
            MAX(DECODE(RID, 6, ENAME)) EMP6
     FROM ( 
          SELECT DEPTNO, 
                 ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY ENAME) RID, 
                ENAME
          FROM EMP
          )
     GROUP BY DEPTNO

결과

DEPTNO EMP1   EMP2   EMP3      EMP4     EMP5    EMP6 
-------- ------   -----    -----      -----      -----     -----
10         CLARK   KING   MILLER   
20         ADAMS  FORD   JONES   SCOTT    SMITH 
30         ALLEN   BLAKE JAMES   MARTIN   TURNER WARD

출처: http://blog.naver.com/whdahek/70179338216

◆데이터베이스 트리거

트리거란 테이블에 어떤 조작이 가해졌을 때에 미리 지정해 놓은 처리를 자동으로 실행시키는 블록을 말하며,

PL/SQL 블록으로 작성한다.

트리거는 테이블, 뷰에 대한 처리 내용, 실행 조건, 실행 시간 등을 설정하고 설정 조건에 따라 자동으로 실행된다.

특히 데이터가 변경되는 중요한 테이블에 트리거를 설정하였는데 문제가 발생할 경우 데이터 추적이 가능하다.

하지만 너무 많이 사용하면 성능이 저하되는 문제점도 발생한다.

트리거는 위의 설정된 이벤트가 발생하면 실행된다.

이벤트가 발생되는 경우는 다음과 같다.

DML 실행, DDL실행 과 데이터베이스 동작 실행 (ex - logon , logoff, startup, shutdown, severerror)

◆ DML 트리거

DML트리거란 INSERT, UPDATE, DELETE문에 의해 테이블의 내용이 변경될 때마다 자동으로 실행되는

PL/SQL 블록을 말한다.

<생성 구문>

before|after = before는 트리거링 이벤트 발생 전에 PL/SQL block을 실행하고 after는 발생 후에 실행한다.

triggering-event = DML의 INSERT, UPDATE, DELETE를 기술한다. 지정된 컬럼(들)에 대해서만 이벤트가 발생하며 지정하지 않을 시 모든 컬럼에 적용된다.

for each row = 선택이며 각 행이 변할 때마다 실행되는 행 수준의 트리거일 때 지정한다.

PL/SQL block = 트리거에서 처리할 블록의 명령문을 기술한다.

위의 특징들에 따라 DML트리거의 유형은 3가지가 있다.

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

1. 문장 수준과 행 수준의 트리거

2. DML문장

3. 타이밍

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

1. 문장 수준과 행 수준의 트리거 =

문장 수준의 트리거는 한 문장의 실행에 의한 변경/삭제가 되는 행의 수와 관계없이 한번만 이벤트가 발생되고 실행된다.

for each row 구문을 포함하지 않는다.

triggering-event 마다 한번만 실행한다.

행 수준의 트리거는 한 문장의 실행에 의한 변경/삭제되는 행의 수만큼 이벤트가 발생되어 실행된다.

for each row 구문을 포함한다.

행이 추가되거나 수정되거나 삭제될 때마다 실행한다.

2.DML문장 =

INSERT는 행이 추가될 때 실행된다.

UPDATE는 변경될 때, DELETE는 삭제될 때 실행된다.

3. 타이밍 =

before는 테이블에 DML 문장이 실행되기 전에 트리거가 실행되고 after는 후에 실행된다.

따라서 테이블에 적용가능한 트리거의 유형은

triggering-event 3종류, for each row 유뮤에 의한 2종류, before과after에 대해 2종류

즉 3 * 2 * 2 종류로 12가지 유형이 된다.

DML트리거에서 컬럼 값을 참조하는 방법에 대해서 보자.

DML트리거의 PL/SQL 블록에서 테이블에 입력,수정,삭제될 때 테이블에 관련된 값을 참조할 수 있다.

이 값은 DML트리거가 실행될 때 :new , :old 두 종류의 의사 레코드(Pseude Record)를 통하여 DML트리거에 나타난다. 형식은 아래와 같다.

예를 들어,

INSERT문이 실행되면 추가할 행의 컬럼 값이 :new.컬럼명 으로 나타나고

UPDATE문이 실행되면 수정전 컬럼 값은 :old.컬럼명 으로 나타나고

수정할 컬럼 값은 :new.컬럼명 으로 나타난다.

DELETE문이 실행되면 컬럼 값은 :old.컬럼명 으로 나타난다.

<트리거 작성시 고려사항>

1. DML트리거에서 쩌장된 프로시저나 함수 등을 호출할 수 있다.

2. DML트리거에는 트랜잭션 제어문을 사용할 수 없다.

예외가 발생하거나, 변경된 데이터를 취소할 경우가 발생되었을 때 COMMIT문으로 저장된 값들은

ROLLBACK문으로 취소할 수 없기 때문이다.

 예제.. 

SQL> CREATE OR REPLACE TRIGGER triger_test
       BEFORE
       UPDATE ON dept
       FOR EACH ROW
	   
	   BEGIN
        DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : ' || : old.dname);
        DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : ' || : new.dname);
     END;
     /

-- DBMS_OUTPUT.PUT_LINE을 출력
SQL> SET SERVEROUTPUT ON ; 

-- UPDATE문을 실행시키면.. 
SQL> UPDATE dept SET dname = '총무부' WHERE deptno = 30

-- 트리거가 자동 실행되어 결과가 출력된다. 
변경 전 컬럼 값 : 인사과
변경 후 컬럼 값 : 총무부

1 행이 갱신되었습니다.
    

* Pseudo Column
    1) SYSDATE - 날짜
    2) ROWNUM - 행의 순서(가상의 숫자, 따라서 where 조건을 쓸 수 없음)
    3) ROWID - 모든 행에 대한 식별자의 역할


* 검색 - 오라클 명령어에서 대소문자를 가리지는 않지만, 검색시에는 구분함.
    1) select
        a) distinct - 중복행제거
        b) * - all
        c) alias - 해당 컬럼에 다른 이름 부여 : as키워드를 사용하면 명시적이고
                                                                         따옴표로 묶으면, 대소문자 구분 가능
        d) 컬럼값중 숫자형 데이터는 산술연산 가능
        e) || - 연결연산자
        f) 작은따옴표 - 컬럼에 있는 데이터는 아니지만, 따옴표사이의 내용이 모든 행에 표시된다. 연결연산자와
           함께 사용하는 경우가 일반적
    2) where - from 절 다음에 와야 한다.
        a) and - AND 연산일 경우에는 거짓이 앞에 있는 게 좋고, OR 연산자일 경우에는 참이 앞에 있는 것이 좋다
        b) or
        c) between ~ and ~ - 이상, 이하의 개념임. 미만, 보다일경우는 부등호 기호 사용
        d) in ( 'x', 'y', 'z' )
        e) is null / not - NOT이 들어간 SQL 연산자는 사용을 하지 않는 것이 좋다. (ex. NOT IN, IS NOT NULL)
            null값으로 들어간 데이터를 확인하는 용도로 쓰인다.
        f) like
            i) %
            ii) _
            iii) like 'aa\%%' escape '\'
    3) order by 칼럼이름 [asc|desc]

    4) group by - 테이블보다 작은 그룹으로 묶어서 값을 얻으려 할 때 사용
        a) ROLLUP
        b) CUBE
    5) having - group by 의 수행결과에 조건을 부여해서 값을 얻으려 할 때 사용
    6) grouping sets
        a) UNION - 합집합
        b) INTERSECT - 교집합
        c) MINUS - 차집합
        d) UNION ALL - 합집합+교집합
    7) subquery - select 문 안에 삽입된 select 문


* SQL함수
    1) 단일행함수
        a) 숫자
        b) 문자
        c) 날짜
        d) 형변환
        e) 기타
    2) 집합함수
    3) 분석함수
    4) 정규표현식


* 숫자함수
    1) MOD(m, n) - m을 n으로 나누었을 때의 나머지를 반환

    2) ROUND(m, n) - m을 소수점 n+1자리에서 반올림한 결과를 반환

    3) WIDTH_BUCKET(대상값, 최소값, 최대값, 버켓수)

    4) CEIL(n) - 올림한 후 정수를 반환

    5) FLOOR(n) - 내림한 후 정수를 반환

    6) ABS(n) - 절대값을 반환

    7) TRUNC(m, n) - m을 n자리까지 절삭. n은 생략가능하며, 기본값은 0.
        n이 양수이면, 소수자리를 절삭, n이 음수이면, 정수자리를 절삭(= 0으로 만든다)

    8) POWER(m, n) - m의 n승값을 반환

    9) SQRT(n) - n의 제곱근 값을 반환

    10) SIGN(n) - n이 양수인지, 음수인지, 0인지를 반환

    11) CHR(n) - 10진수 n의 아스키코드에 해당하는 문자를 반환


* 문자함수
    1) LOWER('문자열') - 문자열을 소문자로 반환

    2) UPPER('문자열') - 문자열을 대문자로 반환

    3) INITCAP('문자열') - 문자열의 첫문자는 대문자로, 나머지 문자는 소문자로 반환

    4) CONCAT('문자열1', '문자열2') - 두개의 문자열1과 2를 연결해서 반환. 매개변수는 2개만 가능.

    5) SUBSTR('문자열', 시작위치값, 시작위치부터뽑아낼 문자열길이)

    6) LENGTH('문자열') - 문자열의 길이를 숫자값으로 반환

    7) INSTR('문자열', '표적문자', m, n) - 문자열중에서 표적문자를 왼쪽부터 m번째, m번째부터 n번째
        의 위치를 숫자값을 반환

    8) LPAD('문자열', 전체문자길이, '나머지문자값') - 전체문자길이중 문자열 길이만큼을 제외한
        공간을 왼쪽부터 나머지 문자값으로 채우고, 문자열을 덧붙여서 반환

    9) RPAD('문자열', 전체문자길이, '나머지문자값') - LPAD와 동일한 개념인데, 다만, 나머지 문자값을
        오른쪽으로 채워서 반환

    10) REPLACE('문자열', 'str1', 'str2') - 문자열중에서 str1에 해당하는 문자를 str2의 문자로 바꿔서 반환

    11) ASCII('문자') - 문자의 아스키코드값을 반환

    12) TRIM('타겟문자' from '문자열') - 문자열중에서 타겟문자를 삭제. 단 문자열의 바깥쪽 문자만이 해당됨.


* 날짜함수
    1) MONTHS_BETWEEN(date1, date2) - 두 날짜사이의 월수를 계산해서 숫자로 반환
        정수부분은 월을, 소수부분은 일을 의미함.

    2) ADD_MONTHS(date1, n) - n이 양수이면, date1에 더해지고, n이 음수이면, date1에서 뺄 값이 됩니다.

    3) NEXT_DAY(date1, n) - date1의 날짜를 기준으로, 다음번 n에 해당하는 요일을 반환
        ex) NEXT_DAY(sysdate, 'FRIDAY') - 시스템현재날짜로부터 다가올 금요일 날짜를 반환,
              NEXT_DAY(sysdate, '금요일') - 시스템이 한글로 설정되어있을 경우, 한글사용도 가능
              NEXT_DAY(sysdate, 1) - 숫자1은 일요일, 숫자7은 토요일
        cf) 오라클 언어세팅에 의해, 어느 한쪽의 예약어가 거부되는 경우가 있다.
             확인방법 : select * from nls_session_parameters;
             변경방법 : ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

    4) LAST_DAY(date1) - date1날짜가 속한 달의 마지막 날짜를 반환, 윤년,평년은 자동 계산

    5) ROUND(date1, 조건) - date1의 날짜를 월단위, 년단위로 반올림처리해서 반환하고, 조건이 없으면,
        가장 가까운 날짜로 반올림한다.
        ex) ROUND(sysdate, 'MONTH')
              ROUND(sysdate, 'YEAR')
              ROUND(sysdate)

    6) TRUNC(date1, 조건) - date1의 날짜를 가장 가까운 년도 또는 월로 절삭해서 반환. 조건이 없으면,
        가장 가까운 날짜로 절삭


* 변환함수
    1) 묵시적인 형변환
        a) varchar2, char -> number
        b) varchar2, char -> date
        c) number -> varchar2
        d) date -> varchar2
    2) 명시적인 형변환
        a) TO_CAHR
        b) TO_NUMBER
        c) TO_DATE


* 기타함수
    1) NVL(컬럼명, 원하는값) - 특정컬럼의 null값을 원하는 값으로 변환
    2) DECODE(조건, 값1, 처리1, 값2, 처리2, ...... , 디폴트값) - if 분기문과 같은 역할 - 오라클 함수
    3) CASE - DECODE함수와 동일한 처리를 한다. 다만 DECODE함수에서 지원하지않는
        범위비교가 가능하다. - 표준 SQL
    4) NULLIF(m, n) - m과 n이 같으면 null값을 반환하고, 다르면, m을 반환
    5) GREATEST - 나열된 값중 제일 큰 값을 반환
    6) LEAST - 나열된 값중 제일 작은 값을 반환


* 집합함수 - where 절 사용시 주의
    1) AVG - 여러 행으로부터 하나의 결과를 반환
        ex) AVG(컬럼명)
        ex) AVG(컬럼명) OVER(PARTITION BY 컬럼명)
    2) COUNT
    3) MAX
    4) MIN
    5) SUM
    6) RANK - 전체값을 대상으로 각 값의 순위를 반환
        ex) RANK(값) WITHIN GROUP(ORDER BY 컬럼명)
        ex) RANK() OVER (ORDER BY 컬럼명)