Bohemian life

select last_name, first_name, nullif(substr(last_name, -1, 1), substr(first_name, -1, 1)) 

  from employees;

접기

NULLIF

문법

nullif::=

그림 설명
 

목적

NULLIF는 expr1과 expr2를 비교한다.만약 expr1과 expr2가 같으면, NULL값을 반환한다. 만약 expr1과 expr2가 같지 않으면, expr1을 반환한다. expr1에 대하여는 NULL을 지정할수 없다.

만약 양쪽 인수가 수치형이라면, 오라클 데이터 베이스는 더 높은 수치 순위를 가지는 인수를 판단하여, 나머지 인수를 그 데이터형으로 암묵적으로 변환하여 반환한다. 만약 인수가 수치가 아니라면, 두 인수는 같은 데이터형이여야 하며, 그렇지 않은 경우 에러가 발생한다.

NULLIF함수는 다음의 CASE문장과 논리적으로 동등하다.

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

참 조: "CASE Expressions "

예제

다음 예제는 샘플 스키마 HR에서 고용된 이후 직종이 변경된 사람을 선택하는데, 현재 job_id와 job_history의 job_id를 비교하여 확인한다.

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
   FROM employees e, job_history j
   WHERE e.employee_id = j.employee_id
   ORDER BY last_name;

LAST_NAME                 Old Job ID
------------------------- ----------
De Haan                   AD_VP
Hartstein                 MK_MAN
Kaufling                  ST_MAN
Kochhar                   AD_VP
Kochhar                   AD_VP
Raphaely                  PU_MAN
Taylor                    SA_REP
Taylor
Whalen                    AD_ASST 

Whalen

접기

접기

NVL

문법

nvl::=

그림 설명
 

목적

NVL함수는 쿼리의 결과에서 NULL(공백으로 반환)값을 치환한다. 만약 expr1이 NULL이면, NVL함수는 expr2를 반환한다. 만약 expr1이 NULL이 아니면, NVL함수는 expr1을 반환한다.

인수 expr1과 expr2는 임의의 데이터 형을 가진다. 만약 데이터 형이 다르면,

• 만약 expr1이 문자 데이터 이면, 오라클 데이터 베이스는 비교하기 전에 expr2의 데이터형은 expr1의 데이터형으로 변환하여, expr1의 문자 세트에 VARCHAR2를 반환한다.

• 만약 expr1이 숫자형이면, 오라클 데이터 베이스는 더 높은 수치 순위를 가지는 인수를 판단하여, 다른 인수의 데이터형을 암묵적으로 변환하여 반환한다.

 

예제

다음 예제는 종업원이 수수료를 받지 않은 경우 "Not Applicable"를 표시한다.

SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
   "COMMISSION" FROM employees
   WHERE last_name LIKE 'B%'
   ORDER BY last_name;
 
LAST_NAME                 COMMISSION
------------------------- ----------------------------------------
Baer                      Not Applicable
Baida                     Not Applicable
Banda                     .1
Bates                     .15
Bell                      Not Applicable
Bernstein                 .25
Bissot                    Not Applicable
Bloom                     .2 

Bull Not Applicable

접기

접기

NVL2

문법

nvl2::=

그림 설명
 

목적

NVL2함수를 사용하면, 지정한 표현이 NULL인지 여부에 근거하여 쿼리의 반환될 값을 판단할수 있다. 만약 expr1이 NULL이 아니라면, NVL2는 expr2를 반환한다. 만약 expr1인 NULL이라면, NVL2는 expr3을 반환한다.

인수 expr1은 임의의 데이터형을 가질수 있다. 인수 expr2와 expr3은 LONG이외의 임의의 데이터 형을 가질수 있다.

만약 expr2와 expr3의 데이터형이 다르다면,

• 만약 expr2가 문자 데이터형이라면, 오라클 데이터 베이스는 expr3이 NULL 상수가 아닌 한, 그것들을 비교하기 전에 expr3을 expr2의 데이터형으로 변환한다. expr3이 NULL상수인 경우에는, 데이터형 변환은 필요하지 않는다.  오라클은 expr2의 문자 세트에서 VARCHAR2를 반환한다.

 

• 만약 expr2가 수치인 경우에는, 오라클은 더 높은 수치 순위를 판단하여, 다른 인수의 데이터형을 암묵적으로 변환하여, 그 데이터형으로 반환한다.

예제

다음 예제는 employee테이블의 commission_pct 열이  NULL인지 여부에 의해서, 종업원의 수입이 급여와 수수료가 더해졌는지, 단지 급여로 구성되어 있는지 나타낸다.

SELECT last_name, salary, NVL2(commission_pct, 
   salary + (salary * commission_pct), salary) income
   FROM employees WHERE last_name like 'B%'
   ORDER BY last_name;

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6882
Bates                           7300       8468
Bell                            4000       4000
Bernstein                       9500      11970
Bissot                          3300       3300
Bloom                          10000      12100
Bull                            4100       4100

접기

select empno, ename, comm, nvl(comm, 0)

  , nvl2(comm, comm, 0)

  from emp;

  select empno, ename, sal, comm

, sal*12 + nvl(comm, 0) as ann_sal

, decode(nvl(to_char(comm), 'null'), 'null', 'sal*12', 'sal*12+comm') as gubun

  from emp;

  select empno, ename, sal, comm, nvl2(comm, sal*12+comm, sal*12) as ann_sal, nvl2(comm, 'sal*12+comm', 'sal*12') as gubun

  from emp;

  select empno, ename, sal, comm

, sal*12 + nvl2(comm, comm, 0) as ann_sal, nvl2(comm, 'sal*12+comm', 'sal*12') as gubun

  from emp;

  * NVL2를 이용해서 NVL의 기능을 구현

select nvl(comm, 0)

    , nvl2(comm, comm, 0) 

from emp;

  * return type

    - NVL: expr1

    - NVL2: expr2

  - YY Date Format Element

  - RR Date Format Element

alter session set nls_language = american;

drop table df_test1 purge;

create table df_test1 (

 cen varchar2(5),

 naljja date);

alter session set nls_date_format = 'YYYY-MM-DD';

insert into df_test1 values ('20xx', '2012-01-09');

insert into df_test1 values ('00xx', '12-01-09');

insert into df_test1 values ('000x', '2-01-09');

insert into df_test1 values ('20xx', '01-09'); --> ORA-01840: 입력된 값의 길이가 날짜 형식에 비해 부족합니다

commit;

select * from df_test1;

insert into df_test1 values ('19xx', '1912-01-09');

insert into df_test1 values ('20xx', '2052-01-09');

insert into df_test1 values ('19xx', '1952-01-09');

commit;

select * from df_test1;

alter session set nls_date_format = 'YY-MM-DD';

select * from df_test1;

select * from df_test1

where naljja = '12-01-09';

select * from df_test1

where naljja = '52-01-09';

  - 1912년을 찾고 싶다.

select * from df_test1

where naljja = to_date('1912-01-09', 'YYYY-MM-DD');

    또는

select * from df_test1

where naljja = '1912-01-09';

    만약 애플리케이션에서 넘어온 날짜 값이 다음과 같다면: '09/01/1912'

select * from df_test1

where naljja = '09/01/1912';

↓ ↓ ↓ 

select * from df_test1

where naljja = to_date('09/01/1912', 'DD/MM/YYYY');

  - RR Format으로 변환

alter session set nls_date_format = 'RR-MM-DD';

select * from df_test1;

select * from df_test1

where naljja = '12-01-09';

select * from df_test1

where naljja = '52-01-09';

SQL> select * from df_test1

    where naljja = '2052-01-09';

==> 세기 정보를 함께 주면 RR date format이더라도 2052년을 찾을 수 있습니다.

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

drop table df_test2 purge;

create table df_test2 (

 d_format varchar2(5),

 naljja date);

alter session set nls_date_format = 'YY-MON-DD';

insert into df_test2 values ('YY', '12-JAN-09');

insert into df_test2 values ('YY', '77-SEP-09');

alter session set nls_date_format = 'RR-MON-DD';

insert into df_test2 values ('RR', '12-JAN-09');

insert into df_test2 values ('RR', '77-SEP-09');

commit;

select naljja from df_test2;

alter session set nls_date_format = 'RRRR-MM-DD'; --> RRRR은 YYYY와 똑같습니다.

select * from df_test2;

문제 1) 10번 및 20번 부서의 사원들의 정보를 아래와 같이 출력하세요. 

(단, 명시적 변환을 사용하세요)

사원번호 및 이름          급여            입사일

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

사번:7369 (SMITH)         800달러         1980년 12월 17일

사번:7566 (JONES)         2975달러        1981년 04월 02일

사번:7782 (CLARK)         2450달러        1981년 06월 09일

사번:7788 (SCOTT)         3000달러        1982년 12월 09일

사번:7839 (KING)          5000달러        1981년 11월 17일

사번:7876 (ADAMS)         1100달러        1983년 01월 12일

사번:7902 (FORD)          3000달러        1981년 12월 03일

사번:7934 (MILLER)        1300달러        1982년 01월 23일

해답)

select *

from emp

where deptno in (10, 20);

↓ ↓ ↓

column "사원번호 및 이름" format a25

col 급여 for a15

col 입사일 for a25

문제2) 위 결과에서 급여를 우측정렬하여 출력하세요

해답)

select '사번:'||to_char(empno)||' ('||ename||')' as "사원번호 및 이름"

, LPAD(to_char(sal)||'달러', 10) as 급여

, to_char(hiredate, 'YYYY"년 "MM"월 "DD"일"') as 입사일

from emp

where deptno in (10, 20);

SQL> select last_name, hire_date

    from employees

    where hire_date = to_date('June  7, 2002', 'fxMonth DD, YYYY');

==> ERROR : ORA-01858: a non-numeric character was found where a numeric was expected

SQL> l

 1    select last_name, hire_date

 2    from employees

 3*   where hire_date = to_date('June  7, 2002', 'fxMonth DD, YYYY')

SQL> c/June  7/June 7

 3*   where hire_date = to_date('June 7, 2002', 'fxMonth DD, YYYY')

SQL> /

 where hire_date = to_date('June 7, 2002', 'fxMonth DD, YYYY')

==> ERROR : ORA-01862: the numeric value does not match the length of the format item

SQL> l

 1    select last_name, hire_date

 2    from employees

 3*   where hire_date = to_date('June 7, 2002', 'fxMonth DD, YYYY')

SQL> c/7/07

 3*   where hire_date = to_date('June 07, 2002', 'fxMonth DD, YYYY')

SQL> /

↓ ↓ ↓ 

  * 결론: fx를 사용하면 format element 간의 공백과 자릿수를 맞춰주어야 합니다.

                    (June  7 => June 7, 7 => 07)

※으아...집에서 하니까 안되네...강사님도 여러번 하셨는데... 아무튼 자릿수를 잘 맞춰라~~

oj_SQL.txt

* format model : http://goo.gl/9fKed

    : a character literal that describes the format of datetime or numeric data stored in a character string

  * format elements

  * format model modifiers: toggle

    - fm (fill mode): no leading or trailing blanks in format elements

          <= TO_CHAR

    - fx (format exact): exact matching between the character data and the format model

          <= TO_DATE

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

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

  set autot off

  set timing off

SQL> alter session set nls_language = american;

   SQL> select sysdate, hiredate, months_between(sysdate, hiredate), add_months(hiredate, 6)

from emp;

   SQL> select next_day(sysdate, 'Sat'), next_day(sysdate, 'wed')

from dual;

   SQL> select last_day('2011-02-11'), last_day('2016-02-21')

from dual;

   SQL> select  sysdate, next_day(add_months(sysdate, 6), 'fri')

from dual;

  * Single-Row functions

   SQL> select sysdate, hiredate, sysdate - hiredate, ceil(sysdate - hiredate)

from emp;

  * ROUND, TRUNC

   SQL> select sysdate, round(sysdate, 'month'), round(sysdate, 'year') from dual;

   SQL> select to_date('2011-12-15'), round(to_date('2011-12-15'), 'month') from dual;

   SQL> select hiredate, trunc(hiredate, 'Month'), trunc(hiredate, 'Year') from emp;

   SQL> select empno, ename, hiredate from emp where trunc(hiredate, 'Month') = '1981-02-01';   --> 1981년 2월 입사자

↑ ↑ ↑

cf.) SQL> select empno, ename, hiredate

         from emp

 where hiredate between to_date('1981-02-01') and to_date('1981-02-28');

   SQL> select empno, ename, hiredate from emp where trunc(hiredate, 'Year')  = '1982-01-01';   --> 1982년 입사자

 - 시, 분, 초

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

SQL> select sysdate, sysdate + 1, sysdate + 9 + 15/24 + 39/(24*60) + 22/(24*60*60)

    from dual;

  - 오늘 만나서 백일, 천일, 만 후

   SQL> select sysdate, sysdate + 100, sysdate + 1000, sysdate + 10000

from dual;

  - 만난 지 몇 일?

   SQL> select ceil(sysdate - to_date('2001-04-13')) as "만난지 몇 일" from dual;

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

SQL> alter session set nls_language = korean;

   SQL> select sysdate + (sysdate + 1)

from dual;

==> 오류: ORA-00975: 날짜와 날짜의 가산은 할 수 없습니다

   SQL> select sysdate * 5                        

from dual;

==> 오류: ORA-00932: 일관성 없는 데이터 유형: NUMBER이(가) 필요하지만 DATE임