Null관련 Function

1. NVL

오라클 함수, Null값을 가진 컬럼값 변화
첫번째 인자가 Null 일 경우,        두번째 인자를 리턴,
첫번째 인자가 Null 이 아닐경우,  첫번째 인자를 리턴

select NVL('AAA',0) from Dual;   --> AAA

Null값에 어떤값을 연산해도 Null이기 때문에, 계산시  많이 사용.

2. NVL2

첫번째 인자가 Null이 아니면, 표현식1을 리턴,
첫번째 인자가 Null이면,        표현식2을 리턴

select NVL2('AAA','표현식1','표현식2') from Dual; -->표현식1

CASE CASE함수와 같다

CASE 컬럼 WHEN 조건 THEN 실행
               WHEN 조건 THEN 실행
        ELSE Default
END

3. NULLIF

두 값을 비교하여 같으면 NULL, 다르면 첫번째 값 리턴

NULLIF(비교값1, 비교값2)

select NULLIF(1, 2) from Dual; –-->1
select NULLIF(2, 1) from Dual; --->2
select NULLIF(1, 1) from Dual; --->Null
select NULLIF(2, 2) from Dual; ---> Null

 

4. COALESCE

여러개의 값들중 NULL이 아닌 첫번째 값 리턴
첫번째 인자가 Null일 경우, 두번째 인자가 Null인지 평가하고,
                                   두번째 인자가 Null이 아닐 경우, 두번째 인자를 리턴
Null이 아닌 값이 나올때 까지

select COALESCE(null,null,null,1) from dual ---> 1
select COALESCE(null,2,null,1)    from dual ---> 2
select COALESCE(null,2,null,null) from dual --–> null

 

* DECODE

값을 비교하여 해당하는 값으로 치환하여 돌려줌

    DCODE (형식1, 비교값1, 결과치1,
                          비교값2, 결과치2, ...기본치)

* CASE

DECODE와 같은 기능이나 와일드카드 대소, LIKE 등의 비교처리가 가능함.

    CASE [형식] WHEN 비교식1 THEN 치환값1
                 [ WHEN 비교식2 THEN 치환값2
                    WHEN 비교식n THEN 치환값n
                    ELSE else_치환값              ]
    END

* RANK()

ORDER BY 절을 포함한 쿼리문에서 특정 컬럼에 대한 순위를 구하는 함수

      RANK ( ) OVER ( [ PARTITION BY 칼럼] ORDER BY 절 )

                                파티션에 따라 구분..

   SQL>SELECT dept_id , name , salary ,
     2  RANK() OVER ( PARTITION BY dept_id ORDER BY salary DESC) as "급여 순위"  
     3  FROM s_emp  ;

문장처리 순서

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

INSERT ALL - 여러테이블에 한꺼번에 값 INSERT

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

AS SELECT구문을 사용하여 테이블을 생성 시 데이터는 받지 않고 테이블의
구조만 받고 싶다면 WHERE절의 조건을 항상 FALSE가 되도록 하면 됩니다.
이 때 각 컬럼들에 제약조건(Constraint)이 설정되어 있다면 NOT NULL 조건만
복사될 뿐 입니다.

SQL> CREATE TABLE emp_113
  2    AS SELECT id 사번 , name 이름 ,
  3                 mailid 메일, start_date 입사일
  4    FROM s_emp
  5    WHERE dept_id = 113 ;

테이블 이름 변경

SQL> RENAME salgrade TO salary_grade ;

TRUNCATE 명령이 행의 삭제를 ROLLBACK 할 수 없는데 비해, 

DELETE 명령은 삭제된 행을 ROLLBACK 가능합니다.

오라클 채번시 DECODE, NVL 조건을 사용한 SELECT 단건조회 쿼리
오라클이나 MySQL을 사용하여 금융 IT 업무를 개발하다보면 특정 조건에 맞는 일련번호로 채번을 해야하는 경우가 있습니다.
채번에는 여러가지 방식이 사용될 수 있는데..
쿼리문 내에서 처리하지 않고 C 프로그래밍 상에서 처리할 수 있으나 경험상 쿼리문 내에서 조건에 맞게 채번을 해서 결과를 내 주는것이 편할때가 많습니다.
NVL 과 DECODE 를 사용해 채번하는 쿼리문을 알아보도록 하겠습니다.

NVL(expr1, expr2)

expr1 : 널을 포함하는 표현식의 소스 값, 반환되는 값은 항상 expr1의 데이터 유형과 동일
expr2 : 널을 변환할 대상 값
이라고 표현할 수 있으나 이러면 무슨말인지 잘 모르는 분이 많습니다. 저역시 처음 공부할때 이게 문말인가 했구으니...
이 함수는 간단히 말하자면
expr1 의 값이 null 이 아니라면 expr1의 값이 결과값이 되고,
만약 expr1의 값이 null 이라면 expr2의 값이 된다는 의미 입니다.

SELECT
NVL(substr(max(tup_c),1,2) || to_char(to_number(substr(max(tup_c),3,4)) + 1, 'fm0000'))  , ('N10001') ) AS tup_c
FROM tb_cs_jh_cm_tupbsc
WHERE tup_c like 'N' || '%'

NVL은 null 값 혹은 특정한 값을 원할경우 채번에 사용될 수 있습니다.
다만, expr1의 값이 만약 null이 아닌 값일 수밖에 없는경우 (예를들면 상수)는
NVL로는 채번 조건 쿼리를 만들 수 없습니다.
그러므로 아래 예제처럼 substr(:cd_lclc,1,1)와 같은 상수가 포함된 구분은 DEXODE를 사용하여 쿼리를 작성해야 합니다.

DECODE(expr1, expr2, expr3, expr4)

expr1 : 조건을 평가하는 값
expr2 : expr1 이 expr2 인지를 평가할 수 있는 기준값
expr3 : expr1 이 expr2 의 값일 경우 (여기서는  null 일 경우)의 결과
expr2 : expr1 이 expr2 의 값이 아닐 경우 (여기서는  null 이 아닐 경우)의 결과
이 함수는 expr1 이 expr2와 같은지 혹은 다른지 여부를 비교해 같다면 expr3의 결과를 만약 다르다면 expr4의 결과를 얻을 수 있습니다.
C나 JAVA 에서의 if else 문과 유사하다고 생각하시면 이해가 쉬울 것입니다.

SELECT  
DECODE(max(tup_c), NULL, ('N' || substr(:cd_lclc,1,1)  || '0001'), (substr(max(tup_c),1,1) || substr(:cd_lclc,1,1)  || to_char(to_number(substr(max(tup_c),3,4)) + 1, 'fm0000'))) AS tup_c
FROM tb_cs_jh_cm_tupbsc
WHERE tup_c like 'N' || substr(:cd_lclc,1,1) || '%'

NVL이 좀더 간편할 수도 있지만 NVL로 만들 수 없는 채번일 경우도 DECODE 문으로는 가능하기 때문에
NVL과 DECODE 의 쿼리를 둘다 활용가능 하도록 확실히 익혀 놓는 것이 좋습니다.

<참조>
http://blog.naver.com/hjc426?Redirect=Log&logNo=130029780002
http://blog.naver.com/whitefre/140092477119
http://mixiworld.tistory.com/201