깡통

-- 반올림
SELECT ROUND(345.123, 0), ROUND(345.123,2), ROUND(345.123, -1) FROM DUAL;

-- 나머지
SELECT MOD(23,5) FROM DUAL;

-- 승수값
SELECT POWER(3,2), POWER(3, -2) FROM DUAL;

-- 버림값
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;

-- IF + THEN 1 ELSEIF 0 THEN 0 ELSE -1
SELECT SIGN(5.342), SIGN(0), SIGN(-2334) FROM DUAL;
SELECT DECODE( SIGN(POWER(2,10)-1000), 1, '2의 10승이 1000보다 크다',
-1, '2의 10승이 1000보다 작다', '2의 10승이 1000이다') 비교답 FROM DUAL;

-- ASCII <--> CHAR
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;

-- 대소문자 변환
SELECT LOWER('My name is KIMJINDOO') "LOWER", UPPER('My name is kimjindoo') "UPPER" FROM DUAL;

-- 자릿수 맞춰 채우기
SELECT LPAD('DALMA', 10, '*') "LPAD", RPAD('DALMA', LENGTH('DALMA')+11, '@dalcom.net') "RPAD" FROM DUAL;
SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "1234567890" FROM DUAL;
SELECT LPAD('1,234,567', 30, ' ') "LPAD 사용으로 30자리 맞춤", '1,234,567' "단순문자 사용",
1234567 "단순숫자 사용" FROM DUAL;

-- 공백 제거
SELECT REPLACE(LTRIM('    AAA    '), ' ', '0') "LTRIM",
REPLACE(RTRIM('    AAA    '), ' ', '0') "RTRIM" FROM DUAL;

-- 문자열 치환
SELECT REPLACE('ORACLE', 'A', 'a') "REPLACE" FROM DUAL;
SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박" FROM PERSONNEL WHERE EMP_NAME LIKE '이%';

-- 문자열의 일부만을 취하기
SELECT SUBSTR('ORACLE 프로젝트', 1, 3) SUBSTR1, SUBSTR('오라클 프로젝트', 4, 5) SUBSTR2,
SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL;
SELECT SUBSTRB('ORACLE 프로젝트', 2, 3) SUBSTRB1, SUBSTRB('오라클 프로젝트', 4, 5) SUBSTRB2,
SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL;

-- 문자열의 길이
SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO), EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME) FROM
PERSONNEL WHERE EMPNO>'98102'
SELECT LENGTH('가나다') "LENGTH", LENGTHB('가나다') "LENGTHB" FROM DUAL;

-- LANGUAGE : KOREAN_KOREA.KO16KSC5601
SELECT LENGTH('학교') FROM DUAL;   RESULT: 2
SELECT LENGTHB('학교') FROM DUAL;  RESULT: 4

-- LANGUAGE : AMERICAN_AMERICA.US7ASCII
SELECT LENGTH('학교') FROM DUAL;   RESULT: 4

-- 문자열에서 특정 문자나 문자열의 위치

INSTR[B](string1, [char||string], start_position, n_counted)
SELECT INSTR('ORACLE PROJECT', 'R', 1, 1) INSTR1, INSTR('ORACLE PROJECT', 'R', 1, 2) INSTR2,
INSTR('ORACLE PROJECT', 'R', 1, 3) INSTR3 FROM DUAL;
SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2) INSTR, INSTRB('CORPORATE FLOOR', 'OR', 3, 2) INSTRB FROM DUAL;
SELECT INSTR('하늘 아래 하늘이 또 있겠는가.', '하늘', 1, 2) 하늘1,INSTRB('하늘 아래 하늘이 또 있겠는가.', '하늘', 1, 2) 하늘2 FROM DUAL;

-- SYSTEM 시간
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL;

-- 주어진 날짜가 속하는 월의 마지막 날짜
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL;

-- 주어진 두 날짜간의 기간을 월 단위로 계산 
-- MONTHS_BETWEEN(date1, date2)  : IF date1 > date2  THEN +
SELECT MONTHS_BETWEEN('2006/12/26', '2007/01/25') "MONTHS_BETWEEN(-)",
MONTHS_BETWEEN('2006/01/13', '2005/11/13') "MONTHS_BETWEEN(+)" FROM DUAL;
SELECT ROUND(MONTHS_BETWEEN('2006/12/28', '2006/12/01'), 1) FROM DUAL;

-- 월단위 계산
SELECT ADD_MONTHS(SYSDATE, 12) "ADD_MONTHS(+)", ADD_MONTHS(SYSDATE, -12) "ADD_MONTHS(-1)" FROM DUAL;

-- 주어진 날짜를 기준으로 주어진 요일이 처음 오는 날짜
-- 일요일:1 ~ 토요일:7
SELECT SYSDATE, NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1", NEXT_DAY(SYSDATE, 1) "NEXT_DAY 2" FROM DUAL;

-- NUMBER와 DATE를 문자타입으로 변환
SELECT TO_CHAR(123456789) "NUMBER", TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') "DATE" FROM DUAL;

-- 문자를 숫자로 변환
SELECT TO_NUMBER('123456789') "TO_NUMBER1",
TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER2",
TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER3" FROM DUAL;

-- 문자형 데이타를 DATE형으로 변환
SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1",
TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2",
TO_DATE('200208', 'YYYYMM') FROM DUAL;

-- 평균값 구하기
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;

-- 최대값 최소값
SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;

-- 합계 구하기
SELECT SUM(WEIGHT) FROM PERSONNEL;

-- 조회 범위의 조회 건수
SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;

-- NULL 값 치환
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '없음') 특기
FROM PERSONNEL WHERE EMPNO BETWEEN '98001' AND '98005';

-- 연속 조건문
-- DECODE(a, b, c, d)  a가 b면 c고, 아니면 d
-- DECODE(a, b, c, d, e, f, g, h ....) a가 b면 c고, d면 e고, f면 g고 h면 ...
-- DECODE(a, b, c, DECODE(e, f, g, h))
SELECT EMPNO, EMP_NAME, DECODE(HT_CODE, '1', '현재원', '2', '휴직원', '퇴사') HT_CODE
FROM PERSONNEL WHERE EMPNO BETWEEN '98071' AND '98080';

-- 오라클 환경변수 값 구하기
SELECT USERENV('LANGUAGE') "LANGUAGE", USERENV('TERMINAL') "TERMINAL",
USERENV('SESSIONID') "SESSIONID" FROM DUAL;

-- 주어진 데이타중 최대값 최소값 구하기
SELECT GREATEST(132,33 ,45,99,22, 32, 77, 12) GREATEST, LEAST(132,33 ,45,99,22, 32, 77, 12) FROM DUAL;
SELECT GREATEST('가', '나', '다', '라', '마') GREATEST1, GREATEST('가', '나', '다', '라', '마', '마마') GREATEST2,
LEAST('가', '나', '다', '라', '마') FROM DUAL;

-- UID, USER
SELECT UID, USER FROM DUAL;

- 문법
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 (값들....);


MERGE는 UPDATE와 INSERT를 결합한 문장으로 각각의 쓰임새는 다음과 같다.
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가 없을 때 수행할 내용

--------------------------------------------- 예
SQL> create table emp_history (
  2  empno number(4) not null primary key,
  3  mydate date,
  4  salary number(7,2));

테이블이 생성되었습니다.

SQL> MERGE INTO emp_history eh
  2        USING emp e
  3        ON (e.empno = eh.empno)
  4  WHEN MATCHED THEN
  5        UPDATE SET eh.salary = e.sal
  6  WHEN NOT MATCHED THEN
  7        INSERT VALUES (e.empno, sysdate, sal);

14 행이 병합되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select * from emp_history;

     EMPNO MYDATE       SALARY
---------- -------- ----------
      7844 04/01/06       1500
      7839 04/01/06
      7782 04/01/06       2450
      7521 04/01/06       1250
      7654 04/01/06       1250
      7788 04/01/06       3000
      7698 04/01/06       2850
      7566 04/01/06       2975
      7499 04/01/06       1600
      7934 04/01/06       1300
      7902 04/01/06       3000
      7369 04/01/06        800
      7876 04/01/06       1100
      7900 04/01/06        950

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

===============================================================================

-. 추가하는 데이터가 테이블에 존재하지 않을 때는 INSERT가 수행되고, 이미 데이터가
   존재할 경우에는 UPDATE가 수행된다.
-. MERGE문장 작성 순서
   1. 새로운 행이 추가되거나 갱신이 될 테이블을 지정한다.
   2. 다른 테이블을 참조하여 데이터를 비교한다면 대상 테이블 명을 별칭과 함께 정의한다.
   3. 새로운 행을 추가하려고 하는 테이블과 대상 테이블 간에 조인이 필요하면 ON절을
       활용 한다.
   4. 조인 조건에 의해 만족하는 데이터가 있을 경우엔 UPDATE가 수행되고 만족하는
       데이터가 존재하지 않으면 INSERT문이 수행된다.

또다른 예문

merge 간단한 예제

1. 테이블을 생성한다.
create table table_dest (
   id  number primary key,
   txt varchar2(20)
);

insert into table_dest values (1,'one');
insert into table_dest values (3,'three');
insert into table_dest values (5,'five');

commit;

create table table_source (
   id  number primary key,
   txt varchar2(20)
);

insert into table_source values (2,'TWO');
insert into table_source values (3,'THREE');

commit;

2. merge를 사용하여 처리한다.

merge into
  table_dest d 
  [설명] 처리되는 테이블를 명시한다.
using
  table_source s
  [설명] 비교할 대상이 여기에 해당되는데.. table, view, sub-query
on
  (s.id = d.id)
  [설명] 조건을 나열한 만약 여러개일 경우에는 and로 나열하면 됨
when matched then
  update set d.txt = s.txt
when not matched then
  insert (id, txt) values (s.id, s.txt);

3. 확인함.
select * from table_dest;

[요약정리]
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

[또 다른 예제]
MERGE INTO SALES_FACT D
     USING SALES_JUL01 S
     ON (D.TIME_ID = S.TIME_ID
        AND D.STORE_ID = S.STORE_ID
        AND D.REGION_ID = S.REGION_ID)
     WHEN MATCHED THEN
    UPDATE
     SET d_parts = d_parts + s_parts,
          d_sales_amt = d_sales_amt + s_sales_amt,
          d_tax_amt = d_tax_amt + s_tax_amt,
          d_discount = d_discount + s_discount
      WHEN NOT MATCHED THEN
     INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID,
        D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT)
     VALUES (
        S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
        S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);

=================================================================================

애플리케이션 로직에서  데이터 유뮤를 검사하고 있으면 ,  데이터를 UPDATE 하고

없으면 INSERT 하는 흔한 패턴을 아주 간단하게 쿼리 한문장으로 처리할 수 있게 하는 쿼리가

MERGE Statement 입니다.

예문)

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
   VALUES (S.employee_id, S.salary*0.1);

오라클 도큐먼트에 있는 예문입니다.

=============================================================================

짱가's 사족 : 한시간여 헤멘끝에 발견한...

         key관련절은 merge update불가능

( 키워드 오류가 남 )

MERGE INTO  TB_BUSI_MENU x
     USING ( SELECT '2005' BUSI_YEAR, '21S000' BUSI_CD, '21S001' DETAIL_BUSI_CD,
    '125010' DETAIL_MENU_CD,  '2' ORDER_RANK ,
    'testUserId' REG_ID, SYSDATE REG_DT, '127.0.0.1' REG_IP, 'Y' DEL_YN
 FROM DUAL )
        ON (  x.BUSI_YEAR = y.BUSI_YEAR AND BUSI_CD = '21S000' AND DETAIL_BUSI_CD = '21S001' AND DETAIL_MENU_CD = '125010' )
   WHEN MATCHED THEN
      UPDATE
         SET BUSI_YEAR= '2005',BUSI_CD= '21S000', DETAIL_BUSI_CD='21S001',
    DETAIL_MENU_CD='125010', ORDER_RANK='2',
    UPD_ID='testUserId', UPD_DT=SYSDATE, DEL_YN='Y'
         WHEN NOT MATCHED THEN
      INSERT (BUSI_YEAR, BUSI_CD, DETAIL_BUSI_CD,
   DETAIL_MENU_CD, ORDER_RANK ,
   REG_ID, REG_DT, REG_IP, DEL_YN)
      VALUES ('2005', '21S000', '21S001',
    '125010', '2',
    'testUserId', SYSDATE, '127.0.0.1', 'Y' ) 

( 밑의 문장은 몇개의 update문장을 제외하고 실행성공한 문장 )

그외에 몇가지 다른 이유가 있는 듯함 제외하고 실행하니 update는 되나

insert는 되지 않는 오류가 발생

그래서 dual에서 읽어오는 것 처럼 작성하여 join문을 작성.

MERGE INTO tb_busi_menu x
     USING (SELECT '2005' busi_year, '21S000' busi_cd, '21S001' detail_busi_cd,
                   '125010' detail_menu_cd, '2' order_rank, 'testUserId' reg_id,
                   SYSDATE reg_dt, '127.0.0.1' reg_ip, 'testUserId' upd_id,
                   SYSDATE upd_dt, 'Y' del_yn
              FROM DUAL) y
        ON (    x.busi_year = y.busi_year
            AND x.busi_cd = y.busi_cd
            AND x.detail_busi_cd = y.detail_busi_cd
            AND x.detail_menu_cd = y.detail_menu_cd)
        WHEN MATCHED THEN
                UPDATE
                   SET order_rank = y.order_rank, upd_id = y.upd_id,
                       upd_dt = y.upd_dt, del_yn = y.del_yn
        WHEN NOT MATCHED THEN
                INSERT (busi_year, busi_cd, detail_busi_cd, detail_menu_cd,
                        order_rank, reg_id, reg_dt, reg_ip, del_yn)
                VALUES (y.busi_year, y.busi_cd, y.detail_busi_cd,
                        y.detail_menu_cd, y.order_rank, y.reg_id, y.reg_dt,
                        y.reg_ip, y.del_yn)