농부지기

☞ GROUP 관련 기본문장형태

☞ SUBGROUP으로 데이터 분류

☞ GROUP된 데이터 조건 검색

☞ ROLLUP과 CUBE를 이용한 데이터 집계

☞ 정의

테이블에 대해 조건적으로 갱신이나 삽입을 할 수 있도록 하는 "upsert" 기능을 제공한다.
 만일 그 행이 테이블에 이미 존재한다면 갱신을 수행하고, 그렇지 않으면 새로 행을 삽입한다.
 데이터 웨어하우징 애플리케이션의 ETL 과정에 유용하다.

☞ 이점

 MERGE 문을 사용함으로써 하나의 SQL 문으로 하여금 갱신이나 삽입, 또는 둘 다를 수행할 수 있다.
 MERGE 문은 사용자에게 투명한 방식으로 병렬 수행된다.
 원천 테이블에 대한 스캔이 덜 일어나므로 성능의 향상이 있다.

☞ 문법

MERGE INTO table_name alias
          USING (table|view|subquery) alias
          ON    (join condition)
          WHEN MATCHED THEN
                       UPDATE SET col1 = val1[, col2 = val2…]
          WHEN NOT MATCHED THEN
                       INSERT (컬럼리스트) VALUES (값들....);
- INTO clause : data가 update 되거나 insert될 table 이름  
- USING clause : 대상 table의 data와 비교한 후  update 또는 insert할 때 사용할 data의 source.
- ON clause : update나 insert를 하게 될 condition으로, 해당 condition을 만족하는 row가 있으면
    WHEN MATCHED 이하를 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 된다
    WHEN MATCHED : ON clause의 조건이 TRUE인 row에 수행할 내용
    WHEN NOT MATCHED : ON clause의 조건에 맞는 row가 없을 때 수행할 내용

☞ 예제

예1)       MERGE INTO customer C
           USING cust_src s
           ON    (c.customer_id = s.src_customer_id)
           WHEN MATCHED THEN
                      UPDATE SET c.cust_address = s.cust_address
           WHEN NOT MATCHED THEN
                     INSERT (customer_id, cust_first_name, ...)
                     VALUES (src_customer_id, src_first_name, …);

           MERGE INTO Temp1 a
           USING ( select first from Temp2) t
           ON    (a.first = t.first)            
           WHEN MATCHED THEN
                     UPDATE SET    second = 'second',
                                   third  = 'third',
           WHEN NOT MATCHED THEN
                      INSERT (first,second,third)
                     VALUES ('first','second','third') ;

☞ 형식

☞  SELECT 문을 이용한 INSERT

1.  INSERT  INTO TABLE_NAME1
             (   SELECT ...
                 FROM   TBL_NAME2 ) ;

  : Oralce 에서는 Insert 와 Select 문을 같이 사용 할 경우에는 VALUES  문을 사용하지 않는다.

☞ 조건부 INSERT ALL

예시) INSERT ALL
      WHEN   product_id IN ( SELECT product_id FROM promotional_items )
      INTO   promotional_sales
      VALUES (product_id, list_price)
      WHEN   order_mode = 'online'
      INTO   web_orders
      VALUES (product_id, order_total)
      SELECT product_id, list_price, order_total,order_mode
      FROM   orders;

▣ 조건절이 없는 INSERT
        : SQL> INSERT ALL
                 INTO sal_log VALUES (id , salary , title )
                 INTO date_log VALUES (id , start_date , title )
                 SELECT id , salary , start_date , title
                 FROM s_emp
                 WHERE id > 20 ;      

▣ 조건절이 있는 ALL INSERT
      예시) 사번이 20번보다 큰 사원의 사번, 급여, 입사일, 직책을 선택하여 사번, 급여,
            직책은 SAL_LOG 테이블에 입력하되 급여가 850보다큰 직원만 입력하고, 사번, 입사일,
            직책은 DATE_LOG 테이블에 입력하되 단 입사일이 91년 1월 1일 이전인 직원만 입력.

     SQL>    INSERT ALL
             WHEN salary > 850 THEN
             INTO sal_log VALUES (id , salary , title )
             WHEN start_date < '91/01/01' THEN
             INTO date_log VALUES (id , start_date , title )
             SELECT id , salary , start_date , title
             FROM s_emp
             WHERE id > 20 ;

☞ 조건부 INSERT FIRST

INSERT FIRST
WHEN order_total> 10000 THEN
    INTO priority_handling VALUES (id)
WHEN order_total > 5000 THEN
    INTO special_handling VALUES (id)
WHEN total > 3000 THEN
    INTO privilege_handling VALUES (id)
ELSE
    INTO regular_handling VALUES (id)
    SELECT order_total, order_id id  FROM  orders;

※ 무조건 첫번째 조건인 salary>=4000인 조건의 데이터는 DEPT_SAL 테이블에 들어가고,
   나머지 데이터 salary<4000인 데이터 중에서 입사일 조건에 따라 각각의 테이블에 입력
      예시) SQL>  INSERT FIRST
                     WHEN salary >= 4000 THEN
                        INTO dept_sal VALUES (dept_id , salary)
                     WHEN start_date LIKE  '90%' THEN
                        INTO dept_date_90 VALUES (dept_id , start_date)
                     WHEN start_date LIKE  '91%' THEN
                        INTO dept_date_91 VALUES (dept_id , start_date)
                     ELSE
                     INTO dept_date VALUES (dept_id , start_date )
                     SELECT dept_id , SUM(salary) SALARY, MAX(start_date) START_DATE
                     FROM S_EMP
                     GROUP BY dept_id ;

☞ Pivot INSERT

예시)  SQL> INSERT ALL
             INTO sale_data VALUES (id, week_id, sales_mon)
             INTO sale_data VALUES (id, week_id, sales_tue)
             INTO sale_data VALUES (id, week_id, sales_wed)
             INTO sale_data VALUES (id, week_id, sales_thur)
             INTO sale_data VALUES (id, week_id, sales_fri)
             SELECT id,week,sales_mon,sales_tue,sales_wed,sales_thur, sales_fri
             FROM main_sale ;

0. SUB QUERY

1. 정의 : SELECT에서 결과로 나오는 행의 수가 한 행 이상이며, 단 한 컬럼인 경우

2. 예) FROM  TEMP
       WHERE DEPT_CODE IN (SELECT DEPT_CODE FROM TDEPT  WHERE  AREA = '인천');

1. 정의 : SELECT에서 결과로 나오는 행의 수가 한 행 이상이며, 여러 컬럼인 경우

2. 예) FROM  TEMP
       WHERE (DEPT_CODE, EMP_ID) IN (SELECT DEPT_CODE, BOSS_ID FROM TDEPT WHERE AREA = '인천');

1. 정의 : InnerQuery에서 OuterQuery의 어떤 컬럼 값을 사용하는 경우이다.
          InnerQuery(흔히 SUB QUERY라고 함)와 OuterQuery(흔히 MAIN QUERY라고 함)가 서로
          단순히 여관관계가 있는 것이 아니고 서로 값을 주고 받는 연관관계가 성립한다.

2. 참고 : 일반적인 서브쿼리는 SUBQUERY의 결과를 MAIN QUERRY가 이용한다.
          하지만 CORRELATED 서브쿼리는 SUB QUERRY가 MAIN QUERY의 값을 이용하고,
          그렇게 구해진 SUB QUERY의 값을 다시 MAIN QUERY가 다시 이용하게 된다.

3. 예)  FROM   TEMP A
        WHERE  SALARY  >  (SELECT AVG(SALARY)
                           FROM   TEMP B
                           WHERE  B.LEV  =  A.LEV);
  설명) 직원 중 자신의 연봉이 자신과 같은 LEV에 해당하는 직원의 평균 SALARY보다 많은 경우에
        이 사원의 사번과 성명을 읽어 오는 QEURY이다.

4. 예) UPDATE  TDEPT  A
       SET     A.BOSS_ID  =  (SELECT  MIN(B.EMP_ID)
                              FROM    TEMP  B
                              WHERE   B.DEPT_CODE  = A.DEPT_CODE);

1. 정의 : ANY와 ALL 연산자는 서브쿼리와 함께 쓰이거나 값들의 집합과 함께 쓰여 비교된다.

2. 예) SALARY 가 (100, 200, 300)중의 어떤 값 하나보다 큰 값인가를 비교하고 싶을 때
       SALARY > ANY(100, 200, 300)

3. 예) ALL은 집합의 모든 값 보다 어떤가로 비교가 된다.
       SALARY > ALL(100, 200, 300)은  100, 200, 300모두 보다 큰 값인가?로 될 것이다.

4. 예) FROM   TEMP
       WHERE  SALARY > ANY (SELECT  SALARY
                            FROM    TEMP
                            WHERE   LEV = '과장');
   설명) 서브쿼리를 통해 LEV='과장'인 직원의 SALRY를 TEMP에서 읽고 이중의 어떤 값 하나보다
         큰 연봉을 가진 직원의 사번,성명, 연봉을 읽어오는 쿼리이다.

5. 예) FROM   TEMP
       WHERE  SALARY > ALL (SELECT  SALARY
                            FROM    TEMP
                            WHERE   LEV = '과장');
   설명) 어떤 과장보다도 연봉이 많은 직원을 읽어온다.

Join

1. 정의 : JOIN조건 절에 조인 조건이 '='(equal)이 아닌 다른 연산기호로 주어지는 경우

2. 예) FROM  TEMP  A,   EMP_LEVEL B
       WHERE A.BIRTH_DATE  BETWEEN  ADD_MONTHS(SYSDATE, -1 * B.TO_AGE * 12)
                           AND      ADD_MONTHS(SYSDATE, -1 * B.FROM_AGE * 12)

1. 정의 : 두 개 이상의 테이블 조인 시 한쪽 테이블의 행에 대해 다른 쪽 테이블에 일치하는 행이
          없더라도 다른 쪽 테이블의 행을 Null로 하여 행을 Return 하는 조인이다.

2. 추가 : OUTER JOIN 기호(+)는 NULL열이 작성되어야 하는 쪽에 기호가 붙는다.
          하나의 테이블이 OUTER JOIN의 대상으로 삼을 수 있는 테이블의 수가 한 개 뿐이다.

3. 예) FROM  TABLE1,  TABLE2, ....
       WHERE TABLE1.COLUMN = TABLE2.COLUMN(+)

   설명) JOIN된 컬럼 값이 TABLE2에 없더라도 TABLE1에 있으면, TABLE2의 값들 중 TABLE1과
         일치되는 값이 없는 행은 모두 NULL로 RETURN 된다.

1. 정의 : 다른 테이블간의 JOIN이 아닌 같은 테이블을 마치 다른테이블처럼 인식시켜
          JOIN을 하는 것이다.
2. SELF JOIN을 JOINING TABLE ITSELF 라고도 한다.