오늘은 형변환함수를 배웠다.
형변환함수를 알아보기에 앞서, 날짜포맷과 숫자와 소수점, 천 단위 표시, 통화기호 등은 지역과 언어에 종속된다. 따라서 지역과 언어를 확인하거나 변경하는 방법을 알아야한다.
SELECT * FROM nls_session_parameters;현재 세션의 파라미터, 즉 앞서 이야기한 지역, 언어 같은 값들이 어떤 값을 취하고 있는지 알 수 있다.
ALTER SESSION SET nls_language = american;
ALTER SESSION SET nls_territory = america;현재 세션의 언어와 지역 셋을 영어와 미국으로 변경한다는 뜻이다.(영국은 english, 'united kingdom'이라고 쓴다.)
무작정 쿼리문을 작성하기 보다 현재 사용하고 있는 지역과 언어 셋이 어떻게 이뤄지는지를 알아야 형변환이 훨씬 수월하다.
형변환함수
to_char(날짜->문자)
: date(날짜형)을 char(문자형)으로 변환하는 함수
to_char(날짜, '날짜모델요소')
SELECT
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss.sssss')
FROM dual;현재 서버의 날짜와 시간을 yyyy-mm-dd hh24:mi:ss.sssss 이라고 하는 문자형으로 형 변환할 수 있다.
to_char(sysdate, 'month mon mm fmmm') -- 결과는 3월, 3월, 03, 3여기서 fm은 앞에 붙은 0을 제거하는 옵션이다. 예를 들어 오늘 날짜로부터 '2023년 03월 01일'을 '2023-3-1'로 출력하고 싶다면,
to_char(sysdate-16, 'fmyyyy-mm-dd') -- fm은 선행되는 제로를 제거하는 옵션 yyyy 앞에 전체적으로 fm을 붙여주면 월과 일에 붙어있는 0을 나란히 제거해줄 수 있다.
to_char(sysdate, 'ddd dd d') -- d는 일요일을 기준으로 일요일 1 ~ 토요일 7 여기서 ddd는 1월 1일부터 며칠째인지, d는 일요일은 1, 월요일은 2, ... 토요일은 7이라고 표현할 수 있다. d라는 인수값은 요일을 정렬할 때 꽤 유용하게 쓰이는 인수값이다. 예를 들면,
SELECT
employee_id,
to_char(hire_date, 'day') 요일
FROM employees
ORDER BY to_char(hire_date, 'd');위의 예제에서 ORDER BY 절을 살펴보자. 날짜형을 문자형으로 형변환하는 to_char 함수를 썼다. 입사날짜와 요일을 문자형(숫자)로 받아서 결과를 출력하는데, 이에 따라 정렬을 하면 일요일(d 숫자로는 1)부터 오름차순 정렬하게 된다. 만약 월요일부터 오름차순 정렬하고 싶다면 어떻게 해야할까?
SELECT
employee_id,
to_char(hire_date, 'day') 요일
FROM employees
ORDER BY to_char(hire_date-1, 'd');ORDER BY 절에서 hire_date에서 1씩 빼주면 d값이 하나씩 줄어들기 때문에 일요일(1)이 아닌 월요일(2)부터 오름차순이 가능해진다.(ex. 월요일(2)-1 = 1, ..., 토요일(7)-1 = 6, 일요일은 해당 없으므로 가장 아랫쪽에 정렬된다.)
to_char(sysdate, 'day dy') -- day는 요일, dy는 요일의 약어.(월요일이면 '월')
to_char(sysdate, 'ww iw w') -- ww는 현재 몇주 차, iw는 국제 기준, w는 이번달의 몇주 차
to_char(sysdate, 'q"분기"') -- q는 quarter, 분기
to_char(sysdate, 'q')||'분기' -- 리터럴 문자열을 의미하는 작은 따옴표 안에 큰 따옴표로
리터럴 문자열을 넣어줄 수도 있다.(연결연산자(||)도 가능)
to_char(sysdate, 'dd ddth ddsp ddthsp Ddthsp')
-- dd는 일, ddth는 일을 서수로(ex. 17th), ddsp는 일을 스펠링으로(ex. seventeen),
ddthsp는 일의 서수형을 스펠링으로(ex. seventeenth),
Ddthsp는 일의 서수형을 스펠링으로 쓰되 첫 글자를 대문자로(ex. Seventeenth) 라는 뜻
to_char(sysdate, 'hh hh12 hh24 am pm')
-- hh는 시간(ex.07), hh12는 12시간으로 따졌을때의 시간(ex.07),
hh24는 24시간으로 따졌을 때의 시간(ex.19), am(또는 pm)은 오전이나 오후, 둘 중에 하나만 씀to_char(숫자->문자)
: number(숫자형)을 char(문자형)으로 변환하는 함수이기도 하다. (이렇게 함수는 하나인데 인수값에 따라 다르게 돌아가는 것을 객체 지향 프로그래밍이라고 한다.)
to_char(숫자, '숫자모델요소')
SELECT
to_char(salary, '999,999') -- 숫자 하나하나를 9로 표현,
FROM employees; 9는 자릿수에 값이 없으면 아무 값도 채우지 않는다.예제에서는 총 6개의 자릿수가 있고 만약 salary의 값이 24000 이라면 24,000이라고 표현된다. 자릿수에 맞게 콤마를 표시하고 빈 자릿수는 아무 값도 채우지 않는다.
to_char(salary, '000,999') -- 0은 빈 자릿수를 채워줌, 0과 9는 혼용도 가능함
to_char(salary, 'l999g999d00') -- 지역통화부호를 출력할 땐 소문자 l을 붙인다.
(단, 달러 표시는 그냥 부호 그대로 써줌)앞서 언급했듯이 통화기호는 지역에 종속되기 때문에 아무렇게나 넣어줄 수 없다. 따라서 소문자 l을 앞에 넣어서 표현해줄 수 있다. 마찬가지로 천 단위 표시나 소수점 또한 지역에 종속된다. 따라서 콤마와 마침표 대신 g와 d를 알맞는 자리수에 작성해준다.
SELECT
to_char(-1000, '9999'),
to_char(-1000, '9999pr'), -- 음수일 경우 <>로 묶는다.
to_char(-1000, '9999mi'), -- 음수부호를 뒤에 표현
to_char(1000, 's9999') -- 양수는 원래 부호 표현이 x. 부호를 표현하고 싶다면 s
FROM dual;to_number
: 문자형(숫자)을 숫자형으로 형변환하는 함수
to_char(문자(숫자), '숫자모델요소'(생략가능))어떤 함수나 다 마찬가지이지만 인수값끼리의 타입(형)을 맞춰주는 것이 중요하다. 예를 들면,
SELECT 1 + '2'
FROM dual;이런 쿼리문이 있다고 가정해보자. 1은 숫자, '2'는 엄연히 작은 따옴표로 묶인 리터럴 문자열이다. SQL Developer에서 실행을 해보면 결과는 3이라는 값으로 도출이 되기는 한다. 하지만 developer 내부에서 암시적으로 형변환을 해주기 때문에 오류가 나지 않는 것일 뿐, 원칙적으로는 말이 안되는 쿼리문이다. 이런 오류를 방지하기 위해 문자형을 숫자형으로 형변환하는 함수가 바로 to_number이다.
SELECT *
FROM employees
WHERE mod(to_number(to_char(hire_date, 'mm')), 2) = 0;입사날짜에서 month만 추출한 뒤, 문자형으로 형변환하고, 그 값을 또다시 숫자형으로 변환한 뒤, 2라는 값으로 나눴을 때 나머지가 0이 되는, 즉 짝수값이 도출되는 쿼리문이다. 해당 예제는 입사날짜가 짝수달인 사원의 사원정보를 출력하라는 예제였다. to_number는 단순히 문자형을 숫자형으로 변환한다기 보다 이런 식으로 함수가 중첩되어 사용될 때 유용하게 쓰인다.
to_date
: 문자형(날짜)을 날짜형으로 형변환 하는 함수
to_date(문자(날짜), '날짜모델요소')
SELECT *
FROM employees
WHERE mod(to_number(to_char(hire_date, 'mm')), 2) <> 0
AND hire_date >= to_date('2006/01/01','yyyy/mm/dd')
AND hire_date <= to_date('2006/12/31','yyyy/mm/dd');날짜는 지역과 언어에 종속적이기 때문에 그 지역에 맞게 세션을 변경해야 하는데 다른 지역은 한국처럼 '문자열'을 알아서 '날짜형'으로 변환해주지 않는다. 때문에 날짜형은 그냥 '06/01/01'로 쓰지말고 날짜형으로 변환하는 to_date('2006/01/01','yyyy/mm/dd')로 쓰도록 하자! 반드시 연도는 네자리, 월은 숫자 달로 쓰는 방식으로 습관을 들이자! 그리고 내가 작성한 날짜와 날짜모델요소의 생김새를 똑같은 모양으로 만들자!!!
기타 함수
nvl
: null 값을 실제값으로 리턴하는 함수
nvl(컬럼, 실제값) -- 실제값은 분석 시 기준을 세워서 스스로 채워야하는 값
SELECT
nvl(to_char(commission_pct),'no comm'),
FROM employees;
-- 숫자를 문자형으로 형 변환한 뒤, commission_pct가 null 값이면 'no comm'으로 리턴한다는 의미nvl2
: 첫번째 exp1이 null이 아니면 exp2를 수행하고, exp1이 null이면 exp3를 수행한다. exp2, exp3의 데이터 타입이 일치해야한다.
nvl2(exp1, exp2, exp3)coalesce
: 첫번째 exp1이 null이면 exp2를 수행하고, exp2도 null이면 exp3를 수행하고 exp3도 null이면 다음 exp을 수행한다. 즉, null이 발생하지 않을 때까지 인수를 수행한다.
coalesce(exp1, exp2, exp3,...,expn)nullif
: exp1와 exp2가 일치하면 null, 일치하지 않으면 exp1을 리턴
nullif(exp1, exp2)조건제어문
: SQL문에서는 IF문을 사용해서 조건제어문을 수행할 수 없지만 PL/SQL에서는 조건제어문을 수행할 수 있다.
※ PL/SQL IF문
IF 기준값1 = 비교값1 THEN
참값1
ELSE IF 기준값2 = 비교값2 THEN
참값2
ELSE IF 기준값3 = 비교값3 THEN
참값3
..
ELSE
기본값
END IF;DECODE
: 기준값과 비교값이 내부적으로 같다(=)는 비교연산자를 사용
DECODE(기준값,
비교값1, 참값1,
비교값2, 참값2,
비교값3, 참값3,
...
기본값)
예를 들면,
SELECT
employee_id,
salary,
job_id,
decode(job_id,
'IT_PROG', salary * 1.1,
'ST_CLERK', salary * 1.2,
'SA_REP', salary * 1.3,
salary) revised_salary
FROM employees;위와 같은 형태로 나타낼 수 있다. 단, DECODE 함수는 같다 라는 비교연산자만 쓸 수 있어서 범위에 대한 내용을 비교하기 어렵다는 단점이 있다. 이를 보완하기 위해 CASE 표현식이 나타났다.
CASE 표현식
: 기준값과 비교값에 대해서 모든 비교연산자 사용 가능
CASE 기준값
WHEN 비교값1 THEN 참값1
WHEN 비교값2 THEN 참값2
WHEN 비교값3 THEN 참값3
..
ELSE 기본값
ENDCASE 표현식을 사용할 때의 기본적인 형태는 위와 같다. 하지만 비교연산자(=, >, >=, <, <= !=, ^=, <>), 기타 비교연산자(IN, BETWEEN AND, LIKE, IS NULL, IS NOT NULL), 논리연산자(NOT, AND, OR) 등의 연산자를 사용할 때에는 모든 인수값마다 조건이 다르기 때문에 이를 테면 아래와 같이 표현해주어야 한다.
CASE
WHEN 기준값 >= 비교값1 THEN 참값1
WHEN 기준값 <= 비교값2 THEN 참값2
WHEN 기준값 <> 비교값3 THEN 참값3
..
ELSE 기본값
ENDDECODE 함수에서 NULL 체크는 NULL 키워드를 사용한다. CASE 표현식에서 NULL 체크는 IS NULL, IS NOT NULL 연산자를 사용한다.
날짜 타입에서 연도를 표시하는 rr과 yy의 차이점도 배웠다. 1999년에서 2000년대로 넘어가는, 즉 20세기에서 21세기로 변화할 때 일어날 수 있는 여러가지 전산 상의 문제(속칭 Y2K 오류)를 해결하기 위해 나온 연도 타입이 바로 rr 타입이다.
yy 타입은 현재 연도의 세기를 반영한다. 하지만 rr 타입은 2000년도 부터 표기법을 자동으로 변경해준다. 예를 들어 1995년 10월 27일이라는 날짜가 있다고 해보자.
SELECT
to_char(to_date('95-10-27','yy-mm-dd'),'yyyy-mm-dd'), -- 2095년
to_char(to_date('95-10-27','rr-mm-dd'),'yyyy-mm-dd') -- 1995년
FROM dual;예제에서 yy 타입은 현재 연도의 세기를 반영하여 결과를 2095년으로 출력하였다. 반면 rr 타입은 표기법을 내부에서 자동으로 변경하여 1995년이라는 값으로 출력해주었다. rr 타입이 연도를 결정하는 기준은 아래와 같다.
