SQL 단일행 함수

날짜 데이터를 다루는 날짜 함수

날짜 + 숫자 ==> 날짜보다 숫자만큼 일 수 이후의 날짜
날짜 - 숫자 ==> 날짜보다 숫자만큼 일 수 이전의 날짜
날짜 - 날짜 ==> 두 날짜 의 일 수 차이
날짜 + 날짜 ==> 연산 불가

SYSDATE
ADD_MONTHS( )
MONTHS_BETWEEN( )
NEXT_DAY( ), LAST_DAY( )

1. 별다른 데이터 입력없이 DBMS가 설치된 OS의 현재 날짜와 시간을 알려주는 SYSDATE 함수
   : +,- 의 연산이 가능

select sysdate as now,          //==>22/08/04
sysdate-1 as yesterday,         //==> 22/08/03
sysdate+1 as tomorrow        //==> 22/08/05
from dual;

ex)

select sysdate,
round(sysdate, 'ddd') as format_ddd,   //지정일의 정오를 기준으로 반올림해서 시간은버리고 날짜 출력 , 정오 전이면 지정일 출력.
round(sysdate, 'hh') as format_hh      // 지정일 기준 현재 시각으로 30분이 넘으면시간을 올려서 분단위 버리고 시간까지 출력.
from dual;

select sysdate, trunc(sysdate, 'ddd') as format_ddd,    //지정일의 시간은 버리고 날짜만 출력 , 정오 전이면 지정일 전날 출력.
trunc(sysdate, 'hh') as format_hh                       //지정일 기준 현재의 시간에서  분단위 버리고 시간까지 출력.
from dual;

!!!!!! 년도까지 포함한 모든 날짜와 시간 조회

select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS')  
from dual;

2. 몇개월 이후 날짜를 구하는 ADD_MONTHS함수
   : 특정 날짜에서 지정한 개월 수 이후 날짜 데이터를 반환.

• ADD_MONTHS(기준날짜, 개월수)

ex)

select sysdate, add_months(sysdate,3)       //22/11/04
from dual;

select empno, ename, hiredate, add_months(hiredate, 120)     // 입사일기준 120개월(10년후 ) 날짜출력.
as work10years from emp;

select empno, ename, hiredate,sysdate  from emp
where add_months(hiredate, 480)< sysdate;             //입사일 기준 (480개월)40년 후가 오늘보다 전인 직원출력.
                                                        = 입사 40주년이 이미 지난 사람.

3. 두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수

• MONTHS_BETWEEN (A날짜, B날짜) : A-B

ex)

select empno, ename, hiredate, sysdate,
        months_between(hiredate,sysdate) as month1,            // -123.45   
        months_between(sysdate,hiredate) as month2,            // 123.45 
        trunc( months_between(sysdate,hiredate)) as month3     // 123  : 일수 버리고 개월수만  출력 (만 ○개월)  
from emp;

4. 돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY함수

• NEXT_DAY(기준날짜,'지정요일')
• LAST_DAY(지정날짜)

ex)

select sysdate, 
        next_day(sysdate,'월요일'),       //22/08/08  : 오늘기준 다음에 오는 월요일의 날짜
        last_day(sysdate)                //22/08/31  : 오늘이 포함된 달의 마지막 날짜.
from dual;

형변환

select empno, ename, empno+500
from emp
where ename='SCOTT';
|| ==> 같은 결과 (자동 형변환= 암시적 형 변환)
select empno, ename, empno+'500' ' ' 로 묶은 문자형 숫자도 숫자로 인식해서 계산해줌.
from emp
where ename='SCOTT';

select 'ABCD'+empno, ename, empno+500 ==> 계산되지 않음. 문자 +숫자
from emp 사용자가 직접 형을 변경하는 명시적 형 변환을 해서 계산해줘함.
where ename='SCOTT';

TO_CHAR( ) : 숫자 또는 날짜를 문자로 변환.
TO_NUMBER( ) :문자를 숫자로 변환.
TO_DATE( ) : 문자를 데이터로 변환.

숫자==>문자==>날짜
날짜==>문자==>숫자 : 문자데이터를 중심으로 숫자나 날짜는 문자로 바꿨다가 변환해줘야함.

1. 숫자나 날짜 데이터를 문자로 바꿔주는 TO_CHAR 함수.

• TO_CHAR( 날짜나 숫자', '바꿀형식')

  ex) AS현재 날짜 시간

select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS')  
from dual;
ex) 오전과 오후시간 나눠서 보기 
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'),
        to_char(sysdate,'YYYY/MM/DD HH12:MI:SS A.M.'),
        to_char(sysdate,'YYYY/MM/DD HH:MI:SS P.M.')
from dual;

ex)

select sysdate, to_char(sysdate,'MM') as mm             //월 :01,02,10,11....
                   to_char(sysdate, 'MON') as mon          
                   to_char(sysdate, 'MONTH') as month   // 8월
                   to_char(sysdate,'DD') as dd          // 일 :03, 15, 27...
                   to_char(sysdate, 'DY') as dy         // 월, 화, 수, 목...
                   to_char(sysdate, 'DAY') as day       // 월요일, 금요일,수요일 ...

!!!! NLS_DATE_LANGUAGE= 언어 로 출력되는언어를 지정해줄 수 있다.
ex)

select sysdate, to_char(sysdate,'MM') as mm,           
                   to_char(sysdate, 'MON','nls_date_language = korean') as mon,        //8월   
                   to_char(sysdate, 'MONTH','nls_date_language = japanese') as month,  //8月
                   to_char(sysdate,'DD') as dd,
                   to_char(sysdate, 'DY') as dy,
                   to_char(sysdate, 'DAY','nls_date_language = english') as day        //FRIDAY
                   from dual;

!!!!!여러가지 숫자 출력 형식

• 9 : 숫자의 자리수 표시
• 0 : 빈자리 채우기용
• $ : 달러 표시
• L : 지역화폐 단위
• . : 소수점표시
• , : 금액단위 표시

ex)

select to_char (sal,'$999,999,999'),
to_char (sal,'L999,999,999'),
to_char (sal,'000,999,999.00'),
to_char (sal,'000999.00999'),
to_char (sal,'999,999,00'),

2. 문자를 숫자로 변환하는 TO_NUMBER함수
   : 문자열 varchar2나 date 형식의 데이터를 숫자로 바꾸지는 못하지만 '숫자'로 이루어진 데이터는 숫자로 읽고 자동 형변환 가능.
   : 문자 (글자나 기호) 가 포함된 데이터는 읽지 못함.

   ex)

'1,300'과 '1,500' 더하기
select '1300'+1500 
        1300+'1500'                        //둘다 2800 : 숫자로 자동 형변환.
from dual;

select to_number('1,300','999,999') +to_number('1,500','999,999')   
//, 때문에 숫자로 읽지 못해 형변환 후 계산.
from dual;

3. 문자열을 날짜로 바꿔주는 TO_DATE 함수

• TO_DATE ( '문자열', 'YYYY-MM-DD')

ex)

 select to_date('2022-08-05','YYYY-MM-DD'),       //2022-08-05
         to_date('20220805','YYYY-MM-DD')         //2022-08-05
 from dual;

select *from emp 
where hiredate >to_date('1981/06/01','YYYY-MM-DD');

NULL처리 함수

: 데이터가 NULL 이면 산술연산자나 비교연산자가 동작 하지 않아서
NULL을 다른값으로 변환해주어야한다.

NVL( )
NVL2( )

1.데이터의 값이 NULL일때 값을 지정해주는 NVL함수

• NVL('컬럼명', '지정값') : 데이터 값이 null이면 지정값을 출력하고 null이 아니면 원래의 값 출력.

ex)

select empno,ename, sal,comm, sal+comm,        //comm이 null 값을 가지면 0으로 출력.
nvl(comm,0), sal+nvl(comm,0)
from emp;

2. 데이터 값이 NULL이 아닌경우도 함께 지정해주는 NVL2 함수
   -NVL2('컬럼명', '지정값1(not null)','지정값2(null)') : null이 아니면 지정값1 출력, null이면 지정값2 출력.
   -NVL2('컬럼명','컬럼명','지정값') : null 이 아닌경우 지정값을 주지 않으면 원래의값 출력.

ex)

select empno, ename, comm, 
nvl2(comm,'O','X'),nvl2(comm,sal*12+comm,sal*12)    
from emp;

여러개의 데이터에 같이 변화를 줄때

if~ else if 와 비슷한 방식으로 접근

DECODE( )
CASE( )

1. DECODE ('컬럼명', 조건1, 결과1,
   조건2, 결과2,....
   else 값)

ex) 직급별로 급여 인상률이 다를때

select empno,ename,job, sal,
DECODE(job,'MANAGER',sal*1.1,
                'SALESMAN',sal*1.05,
                'ANASYST',sal,
                 sal*1.03) as 인상급여
from emp;

2. CASE '컬럼명' WHEN 조건1 THEN 결과1
   WHEN 조건2 THEN 결과2...
   ELSE else값 END

ex) 직급별로 급여 인상률이 다를때

select empno,ename,job, sal,
CASE job when 'MANAGER' then sal*1.1
             when 'SALESMAN' then sal*1.05
             when 'ANASYST' then sal else sal*1.03 end as 인상급여
from emp;

ex) 상여금의 여부가 다를 때

select empno,ename,comm,
CASE  when comm is null then '해당사항 없음'
         when comm =0 then '수당 없음'
         when comm >0 then '수당:'||comm 
         end as comm_text