안녕하세요, 코린이의 코딩 학습기 채니 입니다.
개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.
FUNCTION
- 일련의 작업 절차를 모아 놓은 database 객체
- 호출 시 인자를 전달하고 리턴 값을 받아 처리
- SQL의 function은 반드시 리턴 값을 가짐
| 함수 유형 | |
| 단일행 처리 함수 |
문자 처리 함수 |
| 숫자 처리 함수 | |
| 날짜 처리 함수 | |
| 형변환 처리 함수 | |
| 기타 함수 | |
| 그룹 처리 함수 |
그룹마다 호출 |
단일행 처리 함수
- 매 행마다 함수 호출, 리턴된 값을 결과 집합에 반영
형변환 처리 함수
☞ to_char(date, format)
- date를 지정한 format 형식으로 변환한 문자열 반환
- day 요일 / dy 짧은 요일 / d 숫자 요일 (일:1, 월:2 ~ 토:7)
- am 오전/오후
select
to_char(sysdate, 'yyyy/mm/dd (dy) hh24:mi:ss') 날짜,
to_char(sysdate, 'yyyy/mm/dd (dy) hh24:mi:ss', 'nls_date_language = korean') 날짜,
to_char(sysdate, 'yyyy"년" mm"월" dd"일"') 날짜,
to_char(sysdate, 'fmyyyymmdd') 날짜, -- 포맷팅에 의해 생겨난 공백, 0을 제거 (맨 앞에 한 번만 사용)
to_char(sysdate, 'day', 'nls_date_language = korean') 날짜
from
dual;
지정 format에 따라 결과 값이 상이하게 나오는 것을 확인할 수 있습니다.
'nls_date_langauge = korean' 은 현재 시스템을 영문으로 설정해두어서 '요일'이 영문으로 출력되어 지정한 국가로 출력해달라는 옵션입니다.
FM은 포맷팅에 의한 생겨난 공백 또는 0을 제거하는 것으로 '20220415' 가 아닌 '2022415'으로 출력되는 것을 확인할 수 있습니다.
☞ to_char(number, format)
- 충분한 자릿수의 포맷을 사용할 것
select
to_char(123456789, 'fm9,999,999,999') "1",
to_char(123.456, '99999.99999') "2", -- 해당 자리수가 없을 때 소수점 이상은 공백, 소수점 이하는 0으로 처리 (fm으로 제거 가능)
to_char(123.456, 'fm99999.99999') "3",
to_char(123.456, '00000.00000') "4", -- 해당 자리수가 없을 때 소수점 이상/이하를 모두 0으로 처리 (fm으로도 제거 불가)
to_char(123.456, 'fm00000.00000') "5",
to_char(123456789, 'FML9,999,999,999') "6" -- L은 지역 통화 기호를 가져옴 (FML -> 공백제거 + 통화기호)
from
dual;
숫자 포맷을 사용하면 불필요한 공백이 생기는 경우가 있는데 이를 'FM'으로 제거해주었습니다.
또한 숫자 포맷에 '9'를 이용하면 해당 자리수가 없을 때 소수점 이상은 공백, 소수점 이하는 0으로 처리 (fm으로 제거 가능!!)
'0'을 이용하면 해당 자리수가 없을 때 소수점 이상/이하 모두 0으로 처리해줍니다.(fm으로도 제거 불가!!)
또한, 실제 값보다 포맷의 길이가 짧으면 오류가 발생하므로 충분한 자릿수의 포맷을 사용해야 합니다.
응용) 사원조회 (사원명, 급여, 보너스율, 입사일)
- 급여 3자리 콤마 적용
- 보너스율은 3.3% 형식
- 입사일은 1990년 2월 15일 (수) 형식
select
emp_name 사원명,
to_char(salary, 'FML9,999,999,999') 급여,
to_char(nvl(bonus * 100, 0), 'fm990.0') || '%' 보너스율,
to_char(hire_date, 'FMyyyy"년" mm"월" dd"일" (dy)', 'nls_date_language = korean') 입사일
from
employee;
☞ to_number(char, format)
select
to_number('￦123,456,789', 'L9,999,999,999') "a",
to_number('￦123,456,789', 'L9,999,999,999') + 1 "b"
from
dual;
+1이 연산 되는 것을 보면 숫자형으로 잘 변환이 되었다는 것을 확인할 수 있습니다.
☞ to_date(char, format)
- 날짜 데이터인 문자열을 지정된 형식에 맞게 날짜형으로 변환 후 반환
select
to_date('1999/01/19', 'yyyy/mm/dd') "a",
to_date('1999/01/19', 'yyyy/mm/dd') +1 "b",
to_date('2022/04/14 (목) 19:08:47', 'yyyy/mm/dd (dy) hh24:mi:ss', 'nls_date_language = korean') "c"
from
dual;
+1을 하니 다음 날짜가 출력 되는 것을 보면 날짜형으로 잘 변환 되었다는 것을 확인할 수 있습니다.
또한, 지정한 format대로 출력이 되지는 않았지만 내부적으로는 해당 포맷과 정보대로 잘 입력이 되어있습니다.
응용) '2018년 2월 8일 12시 23분 50초'에서 3시간 후의 날짜 정보를 년/월/일 시:분:초 형식으로 출력
select
to_char(to_date('2018년 2월 8일 12시 23분 50초', 'yyyy"년" dd"월" mm"일" hh24"시" mi"분" ss"초"') + (3 / 24),
'yyyy"년" dd"월" mm"일" hh24"시" mi"분" ss"초"') 시각
from
dual;
응용) 지금으로부터 1일 2시간 3분 4초 후의 시각 출력 (yyyy/mm/dd hh24:mi:ss)
select
to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') 현재,
to_char(sysdate + 1 + (2 / 24) + (3 / 24 / 60) + (4 / 24 / 60/ 60), 'yyyy/mm/dd hh24:mi:ss') 미래
from
dual;
기간 interval 타입
① interval year to month : n년 m개월
② interval day to second : n일 m시간 l분 p초
☞ to_yminterval('+01-02')
- 1년 2개월 후를 의미
☞ to_dsinterval('01 02:03:04')
- 1일 2시간 3분 4초 후를 의미
select
to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') 현재,
-- to_char(sysdate + 1 + (2 / 24) + (3 / 24 / 60) + (4 / 24 / 60/ 60), 'yyyy/mm/dd hh24:mi:ss') 미래,
to_char(sysdate + to_dsinterval('1 02:03:04'), 'yyyy/mm/dd hh24:mi:ss') 미래,
to_char(sysdate + to_dsinterval('-1 02:03:04'), 'yyyy/mm/dd hh24:mi:ss') 과거
from
dual;
to_dsinterval()를 이용하여 동일한 결과를 조금 더 직관적이고 간편하게 출력해낼 수 있습니다.
