08. Oracle DB, 오라클 함수 (nvl, chr, concat, 날짜, 숫자, 문자)

sungjuk테이블  및 입력 데이터

○ 자주 쓰이는 오라클 함수

    ① nvl()

        - null값을 원하는 다른값으로 바꿈

        - 형식) nvl(원래 값, 대체할 값)

-- 주소가 'Incheon'인 행의 국어점수 최대값, 인원수를 조회하시오

select max(kor), count(*)
from sungjuk
where addr='Incheon';   -- max(kor)=(null) / count(*)=0 : 'Incheon'이 없기 때문에

select count(*)+1              
from sungjuk
where addr='Incheon';   -- 2 : 연산 가능    

select max(kor)+1
from sungjuk
where addr='Incheon';   -- (null) : null과는 연산이 불가능

select nvl(max(kor), 0)
from sungjuk
where addr='Incheon';   -- null이 나올시 0으로 대체

select nvl(max(kor), 0)+1
from sungjuk
where addr='Incheon';   -- 1 : 연산 가능해짐

    ② chr()

        - ASCII 문자변환

        - 참고 : dual 가상테이블

select chr(65) from dual;   --'A'
select chr(66) from dual;   --'B'
select chr(97) from dual;   --'a'
select chr(98) from dual;   --'b'
select chr(43) from dual;   --'+'
select chr(33) from dual;   --'!'
select chr(48) from dual;   --'0', 정수0이 아닌 문자열0 기호

 

    ③ concat()

        - 문자열과 문자열 또는 컬럼과 문자열을 붙임 (결합연산자 ||와 비슷한 결과)

        - 형식) concat(칼럼명, '문자열') / concat('문자열', '문자열')

select concat('로미오', '줄리엣') from dual;
    
select concat(uname, '의 평균은'), concat(aver, '점 입니다')
from sungjuk;

 

○ 날짜 관련 함수

    ① sysdate

        - 시스템의 현재 날짜/시간을 리턴하는 함수

 

    ② sysdate에서 년월일/시분초 추출하기

        - extract(year|month|day from sysdate)

        - extract(hour|minute|second from cast(sysdate as timestamp)) 

select sysdate from dual;

select extract(year from sysdate) as year, 
	   extract(month from sysdate) as month, 
       extract(day from sysdate) as day 
from dual;

select extract(hour from cast(sysdate as timestamp)) as hour, 
	   extract(minute from cast(sysdate as timestamp)) as minute, 
	   extract(second from cast(sysdate as timestamp)) as second 
from dual;

    ③ 날짜데이터의 연산

        - 날짜데이터의 연산 가능

        - 두 개의 날짜데이터에서 개월 수 계산 : months_between('날짜데이터1', '날짜데이터2')

select sysdate+100,    -- 오늘 날짜 +100일 22/08/25
       sysdate-100     -- 오늘 날짜 -100일 22/02/26
from dual;

select months_between('2021-09-24', '2021-05-25') from dual;    -- 3.96
select months_between('2021-09-24', '2021-12-25') from dual;    -- -3.03

    ④ to_date()

        - 문자열을 날짜형으로 변환

        - 형식) to_date('문자열')

select to_date('2021-03-16') from dual;
select to_date('2021/10/05')-to_date('2021/09/05') from dual;   -- 30

 

○ 숫자 관련 함수

    ① 올림, 버림, 반올림

        - ceil(숫자) : 올림, 소수점 지정 불가

        - trunc(숫자, 나타낼 소수점) : 버림

        - round(숫자, 나타낼 소수점) : 반올림

select avg(kor)                 -- 66.3636...
      ,ceil(avg(kor))           -- 67      
      ,trunc(avg(kor), 3)       -- 66.363 (버림)
      ,round(avg(kor), 3)       -- 66.364 (반올림)
from sungjuk;

 

    ② to_number()

        - 문자열을 숫자형으로

        - 형식) to_number('숫자형태의 문자열')

select to_number('123')+1 from dual;    -- 124

 

    ③ mod()

        - 나머지 연산자

        - 형식) mod(나뉘는 숫자, 나누는 숫자)

 

    ④ abs()

        - 절대값

        - 형식) abs(숫자)

select abs(-3)     -- 절대값
      ,mod(5, 3)   -- 5를 3으로 나누었을 때 나머지값
from dual;

 

○ 문자 관련 함수

    - lower('문자열') : 전부 소문자로 변환

    - upper('문자열') : 전부 대문자로 변환

    - substr('문자열', 시작위치, 끝위치) : 문자열을 원하는 부분만 출력

                                                   (일반적인 프로그래밍언어와 달리 DB는 인덱스가 0이 아닌 1부터 시작함)  

    - length('문자열') : 글자 개수

    - instr('문자열', '문자') : 특정 문자의 위치

    - trim('문자열') : 앞의 공백제거
    - lpad('문자열', 출력칸수, '문자') : 해당 칸수 내에서 출력하고 왼쪽 빈칸은 '문자'로 채움
    - rpad('문자열', 출력칸수, '문자') : 해당 칸수 내에서 출력하고 오른쪽 빈칸은 '문자'로 채움

    - replace('문자열', '원래문자', '바꿀문자') : 문자열내의 문자를 지정한 특정문자로 치환

select lower('Hello World')                         -- hello world
       ,upper('Hello World')                        -- HELLO WORLD
       ,substr('Hello World', 1, 5)                 -- Hello (1부터 5까지 출력)
       ,length('Hello World')                       -- 11 (글자개수)         
       ,instr('Hello World', 'W')                   -- 7 (W의 위치)
       ,'#' || trim('     H e  l l    o') || '#'    -- #H e  l l    o# (공백제거)     
       ,lpad('Hello', 8, '*')                       -- ***Hello (8칸내에서 출력, 왼쪽 빈칸은 *로)
       ,rpad('Hello', 8, '*')                       -- Hello*** (8칸내에서 출력, 오른쪽 빈칸은 *로)
       ,replace('Hello', 'l', 'L')                  -- HeLLo (l을 L로 치환)
from dual;

○ 일반적으로 함수 이름에 담긴 뜻

    - is함수명 : 불린(맞는지 아닌지 물어보는 함수)
    - get함수명 : 리턴값O (리턴값을 얻어오는)
    - set함수명 : 리턴값X (값을 정해주는)
    - to함수명 : 변환