복잡한 RDBMS 오라클 함수를 정리해보았습니다.
목차
NVL
NVL2
Order by
escape
initcap
substr
instr
reverse
ltrim
rtrim
trim
round
trunc
to_yminterval
to_dsinterval
last_day
extract
to_char
to_date
case when then else end
decode
rank() over(order by 컬럼지정 asc 또는 desc)
NVL( 컬럼 , null이면 출력 ) : null을 처리해주는 함수
select nvl( 지정 컬럼 , 2. 값이 null이면 출력해주세요 )
from dual;
활용방법 -> null이 포함된 값은 쿼리문이 복잡해지는 경우 제외되는 경우가 있다. (그룹함수)
제외되지 않게끔 하고 싶다면 nvl(컬럼, -9999)를 통해서 특정 값을 주자!
NVL2(컬럼, !null이면 출력, null이면 출력 ) : null을 처리해주는 함수
select nvl( 지정 컬럼 , 컬럼이 null이 아니면 출력할 값, 컬럼이 null이면 출력할 값)
Order by : 이 컬럼은 오름차순으로 저 컬럼은 내림차순으로..
ordery by 부서번호 asc, 월급 desc 라고 하면 부서번호는 오름차순으로 월급은 내림차순으로 컬럼 2개가 정렬 가능하다.
sysdate : 현재시각을 알려준다
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') -> 현재시각을 2023-02-12 09:20:12 라는 문자타입으로 표현한다..
escape '원하는 문자' : escape 를 지정해줄 수 있다?!
where name like '%kime%' escape 'e';
=> escape로 e를 설정했기 때문에 kime 뒤에 '%'가 원래의 기능을 상실하고 (앞에는 상관없음)kim% 가 name인 조건이 설정된다.
initcap('문자열') : 단어의 첫글자만 대문자로 바꾸는 방법
upper() lower()가 있는건 알지만 initcap 처럼 첫글자만 대문자로 바꿔주는 함수도 아셨나요?
select initcap('kIm min') from dual;
=> Kim Min
substr('문자열', 시작할 글자 index, 글자길이) : 자주 쓰는 문자자르기 함수
select substr('가나다라마바사', 2, 3)
오라클에서 인덱스는 1부터 시작이다. 따라서 '나다라'가 출력된다.
글자길이를 입력하지 않는다면 끝까지 출력된다.
instr('문자열', '시작 문자', '검색을 시작할 글자 index', 몇번째로 나오는?)
select instr('나방 나비 호랑나비', '나비', 1, 2) from dual;
12 3 45 6 78910
나방 나비 호랑나비
1부터 시작해서 만약 첫 번째 나비였다면 4가 출력되겠지만 우리는 2번째 나비를 찾고 있으므로 9가 출력됩니다~
reverse('문자열') : 문자열을 거꾸로 뒤집습니다
reverse instr substr을 모두 활용한 예시
파일경로를 입력받으면 파일의 이름과 형식을 출력하는 방법
filepath = 'mydocument\myfolder\myfile.hwp' 라고 할 때
select instr(reverse(filepath), '\', 1) -- 먼저 파일경로를 거꾸로 뒤집고 \의 인덱스를 얻습니다. pwh.elif\ => 9
select substr( reverse(filepath), 1, instr(reverse(filepath), '\', 1) - 1 ) -- 다시 파일경로를 뒤집고 맨 뒤부터 \가 시작된 직전 부분까지 자릅니다.(-1을 해주는 이유)
pwh.elif 만 가져옵니다
select reverse( substr( reverse(filepath), 1, instr(reverse(filepath), '\', 1) - 1 ) ) -- 다시 뒤집으면 끝
ltrim('문자열', '제거할 문자열') : 왼쪽부터 지정된 문자를 제거
ltrim('가가가나가가나나다다가가나', '가나') => '다다가가나' 왼쪽 가, 나 제거
rtrim('문자열', '제거할 문자열') : 오른쪽부터 지정된 문자를 제거
rtrim('가나나다다가가나', '가나') => '가나나다다' 오른쪽 가, 나 제거
trim('문자열', '제거할 문자열') : 왼쪽, 오른쪽 둘 다 지정된 문자를 제거
trim('가나나다다가가나', '가나') => '다다' 양쪽 가나 전부 제거됨..
round(실수, 반올림위치) : 반올림
반올림 위치는 0이면 정수, 1이면 소수 첫째 자리, 2이면 소수 둘째 자리 이런식이다.
-1이면 10의자리 -2이면 100의 자리로 반올림 한다.
만약 반올림위치를 생략하면 정수로 반환한다.
trunc(실수, 절삭위치) : 뒤에 숫자 모두 제거
절삭위치는 반올림위치와 같지만 반올림하지 않고 없앤다.
trunc(54.2151, 0) => 54
trunc(54.2151, 1) => 54.2
trunc(54.2151, -1) => 50
to_yminterval('00-00') : 년과 월을 연산하기
기본적으로 date에 연산자를 쓰면 날로 더해준다. 년과 월을 더한다면 숫자가 커지기 때문에 이 함수를 쓰면 간편하다.
to_dsinterval(000 00:00:00) : 일 시간 분 초를 연산하기
기본적으로 date에 연산자를 쓰면 날로 더해준다. 시간 분 초 을 더한다면 일을 나눠줘야 하기 때문에 이 함수를 쓰면 간편하다.
sysdate + to_dsinterval(002 02:02:02) => 현재시간에 2일 2시간 2분 2초를 더한다.(빼기도 가능)
last_day(특정날짜) : 특정날짜가 포함된 달력에서 맨 마지막 날짜를 알려준다.
last_day('23/01/06') => 23/01/31
extract(년달일 from date)
extract(year from sysdate) => 2023 현재 시각의 년도를 반환합니다.
extract(month from sysdate)
extract(day from sysdate)
to_char : 문자타입으로 전환
to_char(sysdate, 'yyyy')
to_char(sysdate, 'mm')
to_char(sysdate, 'yyyy-mm-dd')
to_char(12345 , '99,999') => 12,345
to_char(100, '999.0') => 100.0
to_date : date 타입으로 전환
to_date('2023-01-01', 'yyyy-mm-dd') + 1 => 23/01/02 날짜 연산이 가능해짐
case when then else end : if else 조건문
select case 10-5
when 4 then '틀렸습니다'
when 5 then '맞았습니다'
else '오류'
end
from ***
decode : 간략한 조건문
decode(10-5, 4, '틀렸습니다', 5, '맞았습니다', '오류')
앞서 설명한 case when then else end 보다 훨씬 간편합니다.
rank() over(order by 컬럼지정 asc 또는 desc) : 랭크를 보여주는 함수
select rank() over(order by salary desc) as 월급내림차순
