SQL 3. 단일행 함수
관련링크
본문
3-1. SQL함수
함수는 SQL에서 제공하는 강력한 기능으로
․ 데이터의 계산 수행
․ 개별 데이터 항목 수정
․ 행 그룹에 대한 출력 결과 조작
․ 표시할 날짜 및 숫자 형식 지정
․ 열 데이터 유형 변환
작업을 사용할 수 있다.
SQL함수는 경우에 따라 인수를 받아들이며 항상 값을 반환한다.
참고 : 이 다원에서 설명하는 대부분의 함수는 오라클의 SQL에서 사용되는 함수입니다.
☞ SQL함수의 두 유형
i) 단일 행 함수
단일 행 함수는 단일 행만 연산하면 행 당 하나의 결과를 반환한다.
단일 행 함수에도 여러 유형이 있는데 이 단원에서는 다음 유형을 설명한다.
․ 문자 함수 : 문자를 입력 값으로 받으며 문자 또는 숫자 값을 반환한다.
․ 숫자 함수 : 숫자를 입력하면 숫자 값을 반환한다.
․ 날짜 함수 : DATE 데이터 유형의 값에 동작한다.
모든 날짜 함수는 DATE 데이터 유형 값을 반환하여 MONTHS_BETWEEN함수만 숫자를 반환한다.
․ 변환 함수 : 값의 데이터 유형을 변환한다.
․ 일반 함수
- NVL
- NVL2
- NULLIF
- COALSECE
- CASE
- DECODE
ii) 여러 행 함수
행 그룹을 조작하여 행 그룹 당 하나의 결과를 제공하도록 하는 함수이다.
단일 행 함수
․ 데이터 항목을 조작합니다.
․ 인수를 사용하고 값을 하나 반환합니다.
․ 반환되는 각 행에 대해 작업합니다.
․ 행 당 하나의 결과를 반환합니다.
․ 데이터 유형을 수정할 수 있습니다.
․ 중첩될 수 있습니다.
․ 열 또는 표현식을 인수로 사용합니다.
형식 :
function_name [ (arg1, arg2,...) ]
구문 설명 :
function_name 함수이름입니다.
arg1, arg2 함수에서 사용하는 인수입니다. 열 이름 또는 표현식을 사용할 수 있다.
단일 행 함수를 사용하여 데이터 항목을 조작할 수 있다.
단일 행 함수에는 인수를 하나 이상 사용할 수 있으며 질의에서 반환하는 행마다 하나의 값을 반환한다.
* 인수로는 다음과 같은 것을 사용할 수 있다.
․ 사용자 지원 상수
․ 변수 값
․ 열 이름
․ 표현식
* 단일 행 함수의 기능은 다음과 같다.
․ 질의에서 반환되는 각 행에 대해 작업한다.
․ 행 당 하나의 결과를 반환한다.
․ 참조한 데이터 값과 다른 유형의 데이터 값을 반환할 수 있다.
․ 인수를 하나 이상 사용할 수 있습니다.
․ SELECT,WHERE 및 ORDER BY절에 사용할 수 있으며 중첩될 수 있다.
3-2. 문자 함수
단일 행 문자 함수는 문자 데이터를 입력 값으로 받으며 문자 또는 숫자 값을 모두 반환 할 수 있습니다. 단일 행 문
자 함수는 다음과 같이 나뉩니다.
․ 대소문자 조작 함수
LOWER(column/expression) 알파벳 값을 소문자로 변환합니다.
UPPER(column/expression) 알파벳 값을 대문자로 변환합니다.
INITCAPI(column/expression) 알파벳 값을 각 다언의 첫문자는 대문자로, 나머지 문자는 모두 소문자로 변환합니다.
CONCAT(column/exp ression,column2/expression2) 첫 번째 문자 값을 두 번째 문자 값에 연결합니다. 연결 연산자(||)와 동일합니다.
SUBSTR(column/expression,m [,n]) 문자 갑의 위치m에서 n까지 지정된 문자를 반환합니다.(m이 음수면 문자 값의 끝부터 세며 n을 생략하면 문자열의 끝까지 모든 문자가 반환됩니다.)
예제)
오라클 디비 예제 : SELECT 'The job id for ' || UPPER(last_name) || ' is '
|| LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;
MSSQL 디비 예제 :
select lower(title_id) + UPPER(title) as "Title Details" , price
from titles
예제) 사원 Higgins의 사원번호, 이름 및 부서 번호를 표시한다.
오라클 디비 예제 :
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins';
☞설명)
SQL문의 WHERE절은 사원이 이름을 higgins로 지정한다.
오라클의 EMPLOYEES테이블의 모든 데이터는 대소문자가 구분된 상태로 저장되어 있으므로 질의를 실행해도 이 테이블
에서 higgins와 일치하는 이름을 찾지 못하게 되고 따라서 행이 선택되지 않는다.
그러므로 오라클에서는 대소문자를 구별하지 못하는 경우 대소문자 변환 함수를 사용한다.
예제) 사원 Higgins의 사원번호, 이름 및 부서 번호를 표시한다. : 대소문자 조작 함수 사용
오라클 디비 예제 :
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';
☞설명)
SQL문의 WHERE절은 EMPLOYEES테이블의 사원 이름과 higgins를 비교하되, 비교 하기전에 LAST_NAME열을 소문자로
변환한다.
이제 두 이름이 모두 소문자이므로 일치하는 이름을 찾을 수 있게 되며 한 행이 선택된다.
WHERE절을 다음과 같이 변경해도 동일한 결과를 얻을 수 있다.
...WHERE last_name = 'Higgins'
출력되는 이름은 데이터베이스에 저장된 이름과 동일하게 표시된다.
MSSQL 디비 예제 :
--문자를 모두소문자로
SELECT lower(title_id) , title , price FROM titles
--문자를 모두 대문자로
SELECT title_id , upper(title) , price FROM titles
SELECT au_lname +' ' + au_fname as 성명 , au_id FROM authors
SELECT upper(au_lname) +' ' + lower(au_fname) as 성명 , au_id FROM authors
이름을 대문자로 표시하려면 SELECT문에서 UPPER함수를 사용한다.
예제) 문자열의 첫 문자만 대문자로 변환시켜 반환
오라클 디비 예제 :
SELECT employee_id, UPPER(last_name), department_id
FROM employees
WHERE INITCAR(last_name) = 'Higgins'; // MSSQL에선 제공되지 않는 함수
문자 조작 함수
오라클 문자 조작 함수 결과 MSSQL 문자 조작 함수
CONCAT('Hello', 'World') -> HelloWorld
SUBSTR('HelloWorld' ,1,5 ) -> Hello
LENGTH('HelloWorld') -> 10
INSTR('HelloWorld', 'W') -> 6
LPAD(salary, 10, '*') -> *****24000
RPAD(salary, 10, '*') -> 24000*****
TRIM('H' FROM 'HelloWorld') -> elloWorld
SUBSTRING('HelloWorld' ,1,5 )
LEN('HelloWorld')
Charindex('HelloWorld', 'W')
Ltrim(' AB CDE') //왼쪽 공백문자 제거
Rtrim(' ab cde ') //오른쪽 공백문자 제거
'S'+Space(10)+'E' // 지정한 수만큼의 공백 문자 반환
․ CONCAT : 값을 결합한다. CONCAT에는 파라미터를 두 개만 사용할 수 있다.
․ SUBSTR : 지정한 길이의 문자열을 추출한다.
․ LENGTH : 문자열의 길이를 숫자 값으로 표시한다.
․ INSTR : 지정된 문자의 위치를 숫자로 표시한다.
․ LPAD : 문자 값을 오른쪽 정렬하고 빈 곳을 지정한 문자열로 채운다.
․ RPAD : 문자 값을 왼쪽 정렬하고 빈 곳을 지정한 문자열로 채운다.
․ TRIM : 문자열에서 접두어나 접미어 또는 두가지 모두를 자fms다.
Trim_character 또는 trim_source가 문자 리터럴이면 작은 따옴표로 묶어야 한다.
예제) 문자 조작 함수 사용
오라클 디비 예제 :
SELECT employee_id, CONCAT(first name, last_name) NAME , job_id, LENGTH(last name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';
☞설명)
예제는 업무ID의 네 번째 문자부터 REP라는 문자열이 포함된 모든 사원에 대해 성과 이름을 합친 전체이름, 성의길이, 성
에서 문자a의 위치를 표시한다.
또는
SELECT employee_id, first name || last_name NAME, job_id, LENGTH(last name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';
예제)
오라클 디비 예제 :
SELECT employee_id, CONCAT(first_name, last_name) NAME,
LENGTH(last_name), INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(last_name, -1, 1) = 'n';
☞설명)
성이 n으로 끝나는 사원에 대한 데이터를 표시하도록 슬라이드의 SQL문을 수정합니다.
--문자 위치 찾기
SELECT title_id, title, charindex('a',title), price FROM titles
SELECT title_id, title, patindex('%a%',title), price FROM titles
---문자 연결하기 ( + ) ---//오라클에선 concat 또는 ||
SELECT au_lname + ' ' + au_fname as 성명 FROM authors
---지정된 위치부터 지정된 개수 만큼 가져오기
SELECT title, substring(title, 6, 5), title_id FROM titles
SELECT title, substring(title, -1 , 5), title_id FROM titles
또는
SELECT title, substring(title, 1 , 3), title_id FROM titles
--끝에서 몇글자 반환하기
SELECT title, right(title, 3) ,title_id from titles
--//오라클에선
SELECT title, substring(title, -1 , 3), title_id FROM titles
--앞(왼쪽)에서 몇 글자
SELECT title, left(title, 3), title_id FROM titles
--//오라클에선
SELECT title, substring(title, 1 , 3), title_id FROM titles
--- 문자열의 길이(개수)반환
SELECT title, LEN(title) "문자열 길이" , title_id FROM titles
3-3. 숫자 함수
ROUND(column|expression, n)
열, 표현식 또는 값을 소수점 n째 자리로 반올립합니다.
n을 지정하지 않은 경우 소수점 이하 값이 없어집니다.
n이 음수이면 소수점 왼쪽의 수가 반올림됩니다.
TRUNC(cloumn|expression, n)
열, 표현식 또는 값을 소수점 n째 자리까지 남기고 버립니
다. n을 지정하지 않은 경우에는 기본값 0이 지정됩니다.
MOD(m, n) m을 n으로 나눈 나머지를 반환합니다.
․ ROUND : 지정한 소수점 자리로 값을 반올림합니다.
ROUND(45.926, 2) -----------> 45.39
․ TRUNC : 지정한 소수점 자리까지 남기고 값을 버립니다.
TRUNC(45.926, 2) ----------> 45.92
․ MOD : 나눗셈의 나머지를 반환합니다.
MOD(1600, 300) ----------> 100
예제) ROUND함수 사용
오라클 디비 예제 :
SELECT ROUND (45.923, 2) , ROUND (45.923, 0), ROUND (45.923, -1)
FROM DUAL;
☞설명)
DUAL은 함수 및 계산 결과를 보는 데 사용할 수 있는 더미 테이블(dummy table)이다.
ROUND함수는 열, 표현식 또는 값을 소수점 n째 자리로 반올림한다.
두 번째 인수가 0 이거나 없으면 값을 일의 자리로 반올림하고 2면 값을 소수점 둘째 자리로 반올림한다.
반대로 두 번재 인수가 -2면 값을 백의 자리로 반올림한다.0
ROUND함수를 날짜함수와 함께 사용할 수도 있다.
MSSQL 디비 예제 :
SELECT title_id ,title, price / ytd_sales , round(price / ytd_sales, 3) as 반올림
FROM titles
* DUAL 테이블
DUAL 테이블은 사용자 SYS가 소유하며 모든 사용자가 액세스할 수 없습니다.
DUAL 테이블은 DUMMY라는 열과 값X가 들어 있는 행(row)을 하나씩 포함한다.
DUAL 테이블은 사용자데이터가 들어 있는 테이블에서 파생되지 않는 표현식, 의사 열, 상수 값 등을 한 번만 반환할 때
유용하다.
질의에서 SELECT절과 FROM절은 필수항목이므로 DUAL테이블을 사용하여 SELECT절 구문을 완성하면 계산만 수행되는
몇몇 경우에 실제 테이블에서 관련데이터를 선택할 필요가 없다.
예제) TRUNC함수 사용 --MSSQL에는 없는 함수
오라클 디비 예제 : SELECT TRUNC (45.923, 2) , TRUNC (45.923), TRUNC (45.923, -2)
FROM DUAL;
☞설명)
TRUNC함수는 열, 표현식 또는 값을 소수점 n째 자리까지 남기고 버린다.
TRUNC함수는 인수를 ROUND함수와 유사한방식으로 사용한다.
두 번째 인수가 0이거나 없으면 값을 일의자리까지 남기고 버리며 두 번째 인수가 2면 값을 소수점 둘째 자리까지 남기고 버린다.
반대로 두 번째 인수가 -2면 값을 백의 자리까지 남기고 버린다.
ROUND함수와 마찬가지로 TRUNC함수도 날짜 함수와 함께 사용할 수 있습니다.
예제) MOD함수 사용
오라클 디비 예제 : SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERH job_id = 'SA_REP';
☞설명)
업무가 영업 사원인 모든 사원에 대해 급여를 5000으로 나눈 나머지를 계산한다.
MOD함수는 첫 번재 값을 두 번째 값으로 나눈 나머지를 반환한다.
예제는 업무ID가 SA_REP인 모든사원에 대해 급여를 5000으로 나눈 나머지를 계산한다.
참고 : MOD함수는 값이 홀수인지 짝수인지를 확인하는데 많이 사용됩니다.
MSSQL 디비 예제 :
SELECT title_id ,title, price / ytd_sales , price % ytd_sales as 나머지
FROM titles
3-4. 날짜 사용
․ 오라클 데이터베이스는 다음과 같은 내부 숫자 형식으로 날짜를 저장한다.
세기,연도,월,일,시,분,초
․ 기본 날짜 표시 형식은 DD-MON-RR이다.
- 연도의 마지막 두 자리만 지정하여 20세기에 21세기 날짜를 저장할 수 있다.
- 동일한 방식으로 21세기 20세기 날짜를 저장할 수 있습니다.
예제) 날짜 출력 예제
오라클 디비 예제 : SELECT last_name, hire_date
FROM employees
WHERH last_name like 'G%';
☞설명)
오라클 데이터베이스는 세기,연도,월,일,시,분,초 형태의 내수 숫자 형식으로 날짜를 저장한다.
날짜의 기본표시 및 입력 형식은 DD-MON-RR이며 유효한 오라클 날짜범위는 B.C.4712년 1월1일부터 A.D.9999년 12월
31일 이다.
예제에서 사원 Gietz의 HIRE_DATE는 기본 형식인 DD-MON-RR로 표시도힌다.
그러나 데이터베이스에는 이 형식으로 저장되어 있지 않고 날짜 및 시간의 모든 요소가 저장 되어 있다.
따라서 O7-JUN-94와 같은 HIRE_DATE가 일, 월 및 연도로 표시되지만, 이와 관련된 시간 및 세기정보도 있다.
완전한 데이터는 1994년 6월 7일 오후 5시 10분 43초입니다.
이 데이터는 내부적으로 다음과 같이 저정됩니다.
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
19 94 06 07 5 10 43
함수 설명 사용 예 결과
Assume SYSDATE = '25-JUL-95';
MONTHS_BETWEEN 두 날짜 간의 달 수 MONTHS_BETWEEN ('01-SEP-95', '11-JAN-94') 19.6774194
ADD_MONTHS 날짜에 달 수 더하기 ADD_MONTHS ('11-JAN-94', 6) '11-JUL-94'
NEXT_DAY 지정한 날짜의 다음 날 NEXT_DAY ('01-SEP-95', 'FRIDAY') '08-SEP-95'
LAST_DAY 해당 달의 마지막 날 LAST_DAY ('01-FEB-95') '28-FEB-95'
세기와 2000년
Oracle server는 2000년 문제를 해결했습니다.
날짜 열이 포함된 레코드가 테이블에 삽입되는 경우, 세기정보가 SYSDATE함수에 추출된다.
하지만 날짜 열이 화면에 표시 될 때 기본적으로 세기 구성 요소는 표시되지 않는다.
DATE 데이터 유형은 내부적으로 연도 정보를 저장할 때 항상 네 자리 숫자로 저장하며, 이중 두 자리는 세기, 나머지 두
자리는 연도 정보입니다.
예를 들어, 오라클 데이터베이스는 연도를 96또는 01이 아닌 1996또는 2001로 저정한다.
① 날짜 함수
SYSDATE는 다음을 반환하는 함수입니다.
․ 날짜
․ 시간
SYSDATE 는 데이버 베이스 서버의 현재 날짜 및 시간을 반환하는 날짜 함수다.
열 이름 사용 방식과 동일한 방식으로 SYSDATE를 사용한다.
예를 들어, 테이블에서 SYSDATE를 선택하여 현재 날짜를 표시할 수 있다.
일반적으로 SYSDATE를 DUAL이라는 더미테이블(dummy table)에서 선택한다.
예제) DUAL 테이블을 사용하여 현재 날짜를 표시합니다.
오라클 디비 예제 :
SELECT SYSDATE
FROM DUAL;
MSSQL 디비 예제 :
SELECT getdate();
② 날짜 계산
․ 날짜에 숫자를 더하거나 빼서 날짜 값을 계산한다.
․ 한 날짜에서 다른 날짜를 빼서 날짜 간의 일 수를 알 수 있다.
․ 시간을 24로 나누어 날짜에 시간을 더한다.
Oracle 데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈과 뺄셈과 같은 산술 연산자를 사용하여 계산할 수 있다.
날짜뿐만 아니라 숫자 상수와도 더하거나 뺄 수 있다.
연산 결과 설명
date + number 날짜 날짜에 일 수를 더합니다.
date - number 날짜 날짜에서 일 수를 뺍니다.
date - date 일 수 한 날짜에서 다른 날짜를 뺍니다.
date + number/24 날짜 날짜에 시간 수를 더합니다.
예제) 날짜 산술 연산자 사용
오라클 디비 예제 : SELECT last_name, (SYSDATE-hire_date) /7 AS WEEKS
FROM employees
WHERH department_id = 90;
☞설명)
예제는 부서 90에 있는 모든 사원이 이름과 근무한 주 수를 표시한다.
현재, 날짜(SYSDATE)에서 입사일을 뺀 후 결과를 7로 나누어 사원이 근무한 주 수를 계산 한다.
참고 : SYSDATE는 현재 날짜 및 시간을 반환하는 SQL함수로서 계산 결과가 예제와 다를 수 있다.
과거 날짜에서 현재 날짜를 빼는 경우 차이는 음수 값이 된다.
MSSQL 디비 예제 :
SELECT lname, datediff(dd,hire_date, '1995.08.20')/7 as WEEKS
FROM employee
WHERE pub_id = 1389
날짜 함수
ROUND 날짜 반올림
ROUND (SYSDATE, 'MONTH')
ROUND (SYSDATE, 'YEAR')
01-AUG-95
01-JAN-96
TRUNC 날짜 버림
TRUNC (SYSDATE, 'MONTH')
TRUNC (SYSDATE, 'YEAR')
01-JUL-95
01-JAN-95
날짜 함수는 오라클 날짜를 연산한다.
모든 날짜 함수는 DATE데이터 유형 값을 반환하며 MONTHS_BETWEEN만 숫자 값을 반환한다.
․ MONTHS_BETWEEN(date1, date2) : dagte1과 date2간의 달 수를 계산한다. 결과는 양수또는 음수다.
date1이 date2보다 나중이면 결과는 양수고 date1이 date2보다 먼저면 결과는 음수다.
결과 중 정수가 아닌 부분은 달의 일부를 나타냅니다.
․ ADD_MONTHS(date, n) : date에 n달을 더한다. n값은 정수여야 하며 음수일 수 있다.
․ NEXT_DAY(date, 'char') : date보다 이후 날짜며 지정한요일(‘char’)에 해당하는 날짜를 찾는다.
char의 값은 요일을 나타내는 숫자 또는 문자열이다.
․ LSAT_DAY(date) : date를 포함하는 달의 마지막 날짜를 찾습니다.
․ ROUND(date[, 'fmt']) : 형식모델 fmt에 의해 지정된 단위로 반올림한 date를 반환한다.
형식모델 fmt를 생략하면 date는 가장 가까운 날로 반올림된다.
․ TRUNC(date[, 'fmt']) : 날짜의 시간부분을 형식모델fmt로 지정한 단위까지 남기고 버린 후 반환한다.
형식모델fmt를 생략하면 date는 가장 가까운 날에 맞추어 버려진니다.
예제) 날짜 함수
오라클 디비 예제 : SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
ADD_MONTHS (hire_date, 6) REVIEW,
NEXT_DAT (hire_date, 'FRIDAY'), LAST_DAY (hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hier_date) < 36;
☞설명)
36개월보다 적게 근무한 모든 사원이 사원번호, 입사일, 근무한 달 수,6개월 검토일, 입사후 첫 금요일 및 입사한 달의 마지막 날을 표시한다.
MSSQL 디비 예제 :
SELECT lname, hire_date + 5 FROM employee WHERe pub_id = 1389
SELECT lname, hire_date - 5 FROM employee WHERe pub_id = 1389
SELECT lname, '2008/08/20' - hire_date FROM employee WHERe pub_id = 1389 ---(x)
SELECT lname, datediff(dd, hire_date,'2008/08/20') FROM employee WHERe pub_id = 1389--(o)
SELECT lname, datediff(mm, hire_date, '2008-08-20',getdate()) FROM employee
WHERe pub_id = 1389--(o)
SELECT lname, datediff(yy, hire_date, '2008.08.20',getdate()) FROM employee
WHERe pub_id = 1389--(o)
SELECT lname, dateadd(mm,6, hire_date ) FROM employee WHERe pub_id = 1389
SELECT lname, dateadd(yy,6, hire_date ) FROM employee WHERe pub_id = 1389
SELECT lname, dateadd(dd,6, hire_date ) FROM employee WHERe pub_id = 1389
예제)
오라클 디비 예제 : SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hier_date, 'MONTH')
FROM employees
WHERE hire_date LIKE '%97';
☞설명)
1997년에 입사한 모든사원의 입사일을 비교합니다. 사원번호,입사일 그리고 ROUND 및 TRUNC함수를 사용하여 입사한
달을 표시한다.
ROUND 및 TRUNC함수는 숫자 값과 날짜 값에 사용할 수 있습니다. 날짜에 사용하면 지정한 형식모델에 따라 함수가 반
올림되거나 버려지므로 날짜를 가장 가까운 연도 또는 달로 반올림 할 수 있다.
3-5. 변환 함수
데이터베이스의 테이블 열은 오라클 데이터 유형 외에 ANSI,DB2 및 SQL/DS데이터유형을 사용하여 정의할 수 있다.
그러나 Orcale server는 이러한 데이터 유형도 내부적으로 오라클 데이터 유형으로 변환한다.
Oracle server에서 기대한 것과는 다른 유형의 데이터를 사용하는 경우가 있다.
이 경우 Oracle server는 해당 데이터 유형을 원하는 유형으로 자동 변환할 수 있다.
이러한 데이터 유형 변환은 Oracle server에 의해 암시적으로 수행되거나 사용자에 의해 명시적으로 수행될 수 있다.
암시적(implicit)데이터 유형 변환은 두 예제에서 설명하는 규칙에 따라 수행된다.
명시적(explicit)데이터 유형 변환은 변환 함수를 사용하여 수행된다.
변환 함수는 한 데이터 유형을 다른 데이터 유형으로 변환하며 함수 이름의 형식은 일반적으로 데이터 유형 TO 데이터
유형 규칙을 따른다.
전자는 입력 데이터 유형이며 후자는 출력 데이터 유형이다.
참고 : 암시적 데이터 유형 변환을 사용할 수 있지만 SQL문의 신뢰성을 높이기 위해서는 명시적 데이터 유형 변환을 사
용하는 것이 좋다.
① 암시적(implicit)데이터 유형 변환
원 본 대상
VARCHAR2 또는 CHAR NUMBER
VARCHAR2 또는 CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
할당문의 경우 Oracle server는 다음을 자동으로 변환합니다.
Oracle server가 할당문에 사용되는 값의 데이터 유형을 할당 대상의 데이터 유형으로 변환할 수 있을 경우에는 성공적으
로 할당된다.
할당문 변환 규칙이 적용되지 않을 때 데이터 유형을 변환해야 하는 경우 Oracle server는 일반적으로 표현식 변환규칙을
적용한다.
참고 : CHAR에서 NUMBER로의 변환은 문자열이 유효한 숫자를 나타내는 경우에만 성공한다.
② 명시적(explicit)데이터 유형 변환
SQL은 값의 데이터 유형을 변한하는 세 가지 함수를 제공한다.
함수 용도
TO_CHAR
(number|date,
[ fmt], [nlsparams])
형식 모델 fmt를 사용하여 숫자 또는 날짜 값을 VARCHAR2문자열로 변환합니다.
숫자 변환 : nlsparams 파라미터는 숫자 형식 요서에 의해 반환되는 다음 문자를 지정합
니다.
․ 십진 문자
․ 그룹 구분자
․ 지역 통화 기호
․ 국제 통화 기호
nlsparams 또는 다른 피라미터가 생략된 경우 이 함수는 해당 세션의 기본 파라미터를 사
용합니다.
날짜 변환 : nlsparams피라미터는 반환되는
월 및 일 이름과 약어에 사용되는 언어를 지정합니다. 이 파라미터가 생략된 경우 이 함수
는 해당 세션의 기본 날짜 언어를 사용합니다.
TO_CHAR(number|date,
[ fmt], [nlsparams])
숫자를 포함하는 문자열을 형식 모델fmt(선택사항)로 지정한 형식의 숫자로 변환합니다.
nlsparams파라미터는 숫자 변환의 경우
TO_CHAR함수와 용도가 같습니다.
TO_DATE(char,
[fmt], [nlsparams])
날짜를 나타내는 문자열을 지정되fmt에 따라 날짜 값으로 변환합니다. fmt가 생략된 경우
형식은 DD-MON-YY입니다.
nlsparams파라미터는 날짜 변환의 경우
TO_CHAR함수와 용도가 같습니다.
HH24 : MI : SS AM 15 : 45 : 32 PM
DD "of" MONTH 12 of OCTOBER
③ 날짜에 TO_CHAR함수 사용
TO_CHAR(date, 'format_model')
형식 모델 :
․ 작은 따옴표로 묶어야 하며 대소문자를 구분한다.
․ 모든 유효한 날짜 형식 요소를 포함할 수 있다.
․ 채워진 공백을 제거하거나 선행 제로를 제거하는 fm요소가 있다.
․ 쉼표로 날짜 값과 구분한다.
이전에는 모든 오라클 날짜 값이 DD-MON-YY형식으로 표시되었는데 TO_CHAR함수를 사용하면 기본 형식의 날짜를 사
용자가 지정하는 형식으로 변환할 수 있다.
예제)
오라클 디비 예제 : SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees
WHERE last_name = 'Higgins';
i) 날짜 형식 모델 요소
YYYY 네 자리 연도(숫자)
YEAR 연도(문자)
MM 두 자리 값으로 나타낸 달
MONTH 달 전체 이름
MON 세 자 약어로 나타낸 달
DY 세 자 약어로 나타낸 요일
DAY 요일 전체 이름
DD 숫자로 나타낸 달의 일
ii) 유효한 날짜 형시의 예제 형식 요소
요소 설명
SCC 또는 CC 세기. 기원전 날짜에는.를 접두어로 붙임
날짜의 연도 YYYY또는 SYYYY 연도. 기원전 날짜에는.를 접두어로 붙임
YYY 또는 YY또는 Y 연도의 마지막 세 자리, 두 자리 또는 한자리
Y,YYY 해당 위치에 쉼표가 있는 연도
IYYY,IYY,IY,I ISO표준을 따르는 네 자리,세 자리,두 자리 또는 한 자리 연도
SYEAR 또는 YEAR 연도(문자). 기원전 날짜에는.를 접두어로 붙임
BC 또는 AD B.C./A.D. 표시자
B.C. 또는 A.D. 마침표가 있는 B.C./A.D. 표시자
Q 일년 중의 분기
MM 월 : 두 가지 값
MONTH 9문자 길이가 되도록 공백을 채운 달의 이름
MON 달의 이름. 세 자 약어
RM 로마 숫자 달
WW 또는 W 일년 중의 주(week) 또는 한달 중의 주
DDD 또는 DD 또는 D 일년 중의 일(day), 한달 중의 일 또는 한주 중의 일
DAY 9문자 길이가 되도록 공백을 채운 요일의 이름
DY 요일의 이름. 세 자 약어
J 율리우스력의 일. 기원전 4713년 12월 31일부터의 일수
iii) 날짜 형식 모델 요소
․ 시간 요소는 날짜 중 시간 부분의 형식을 지정합니다.
․ 문자열은 큰 따옴표로 묶어 추가합니다.
․ 숫자 접미어는 숫자를 문자로 표기합니다
ddspth fourteenth .
요소 설명 예제 결과
9 숫자 위치입니다.(9개 개수가 표시 폭(width)을 결정한다.) 999999 1234
0 선행 제로를 표시한다. 099999 001234
$ 부동 달러 기호다. $999999 $1234
L 부동 지역 통화 기호다. L999999 FF1234
. 지정된 위치의 소수점이다. 999999.99 1234.00
, 지정된 위치의 쉼표다. 999,999 1,234
iv) 날짜 형식 요소 - 시간 형식
표에 있는 형식을 사용하여 시간 정보 및 리터럴을 표시하고 숫자를 문자로 변경한다.
요소 설명
AM 또는 PM 오전/오후 표시자
A.M. 또는 P.M. 마침표가 포함된 오전/오후 표시자
HH 또는 HH12
또는 HH24
하루 중의 시 또는 1-12 또는 0-23
으로 표시되는 시
MI 분(0-59)
SS 초(0-59)
SSSSS 자정부터의 초(0-86399)
v) 기타 형식
요소 설명
/ . , 구두점을 결과에 재사용
“ofthe" 인용 문자열을 결과에 재사용
vi)숫자 표시에 영향을 주는 접미어 지정
요소 설명
TH 서수(예를들어, 4TH의 경우 DDTH)
SP 문자로 표현한 숫자(예를 들어, FOUR의 경우 DDSP)
SPTH 또는 THSP 문자로 표현한 서수(예를 들어, FOURTH의 경우 DDSPTH)
예제) 출력 날짜형식 변경
오라클 디비 예제 : ALTER session set nls_date_format='YY-MM-DD HH24:MI:SS';
SELECT sysdate FROM dual;
ALTER session set nls_date_format='YY-MM-DD HH:MI:SS AM';
SELECT sysdate FROM dual;
예제) 날짜에 TO_CHAR함수 사용
오라클 디비 예제 : SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE
FROM employees;
☞설명)
SQL문은 모든 사원의 이름과 입사일을 표시한다.
입사일은 17 June 1987 형식으로 표시된다.
예제)
오라클 디비 예제 : SELECT last_name,
TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE
FROM employees;
☞설명)
날짜가 Seventh of June 1994 12:00:00 AM형식으로 표시되도록 예제를 수정한다.
달 이름은 지정한 형식 모델에 따라 첫 자는 대문자고 나머지는 소문자입니다.
vii) 숫자에 TO_CHAR함수 사용
TO_CHAR(number, 'format_model')
숫자 형식 요소
숫자를 문자 데이터 유형으로 변환할 경우 다음 형식 요소를 사용하면 됩니다.
MI 빼기 기호를 오른쪽에 붙인다(음수 값). 999999MI 1234-
PR 음수를 괄호로 묶는다. 999999PR <1234>
EEEE 과확표기L(형식에 4개의 E를 지정해야한다.) 99.999EEEE 1.234E+03
V 10을n번 곱한다(n=v뒤에 나오는 9개의개수) 9999V99 123400
B 0값을 0이 아닌 공백으로 표시한다. B9999.99 1234.00
TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, 'format_model'])
문자열과 같은 숫자 값을 사용할 때는 NUMBER데이터 유형의 값을 VARCHAR2데이터 유형으로 변환하는 TO_CHAR함수
를 사용하여 숫자를 문자 데이터 유형으로 변환해야 한다.
예제) L형식 지정하기
오라클 디비 예제 : ALTER session SET nls_currency='@';
SELECT TO_CHAR(3333, '999,999L') FROM dual;
TO_CHAR(3333, '999,999L')
---------------------
3,333@
ALTER session SET nls_currency='W';
SELECT TO_CHAR(3333, '999,999L') FROM dual;
TO_CHAR(3333, 'L999,999')
---------------------
W3,333
예제) 숫자에 TO_CHAR함수 사용
오라클 디비 예제 : SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';
☞설명)
지침
․ 정수가 형식 모델에서 지원하는 자릿수를 초과하는 경우 해시 기호(#)로 표시된다.
․ Oracle server는 저장된 십진수를 형식모델에서 지원하는 십진수로 반올림한다.
④ TO_NUMBER 및 TO_DATE함수 사용
․ TO_NUMBER함수를 사용하여 문자열을 숫자 형식으로 변환한다.
․ TO_DATE함수를 사용하여 문자열을 날짜 형식으로 변환한다.
․ 이들 함수에는 fx수정자가 있습니다. 이 수정자는 TO_DATE함수의 문자 인수 및 날짜 형식 모델이 정확하게
일치하도록 지정한다.
문자열을 숫자 또는 날짜로 변환해야 하는 경우 TO_NUMBER 또는 TO_DATE함수를 사용해야 하며 형식 모델은 이전에
설명한 형식 요소를 기반으로 한다.
“fx"수정자는 TO_DATE함수의 문자 인수 및 날짜 형식 모델이 정확하게 일치하도록 지정한다.
․ 문자 인수의 구두점 및 인용 텍스트는 형식 모델의 해당 부분과 대소문자를 제외하고 정확히 일치해야 한다.
․ 문자 인수에 여분의 공백이 있으면 안됩니다. fx가 없는 경우 오라클에서는 여분의 공백을 무시한다.
․ 문자 인수의 숫자 데이터는 형식 모델의 해당 요소와 지릿수가 동일해야 한다.
fx가 없는 경우, 문자 인수의 숫자는 선행 제로를 생략할 수 있다.
예제)
오라클 디비 예제 : SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY');
☞설명)
WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY')
*
ERROR at line 3:
ORA-01585: anon-numeric character was found where a numeric was expected
1999년 5월 24일 입사한 모든 사원의 이름과 입사일을 표시한다.
fx수정자가 사용되므로 정확히 일치해야 하며 ‘May'단어 뒤의 공백은 인식되지 않는다.
현재연도 지정한 날짜 (해석) RR형식 (해석) YY형식
1995 27-OCT-95 1995 1995
1995 27-OCT-17 2017 1917
2001 27-OCT-17 2017 2017
2001 27-OCT-95 1995 2095
지정한 연도(두 자리)
0-49 50-99
현재연도(두
자 자리)
0-49
반환일이 현재 세기
입니다.
반환일이 이전 세기
입니다.
50-99
반환일이 다음 세기입
니다.
반환일이 현재 세기
입니다.
RR날짜 형식
RR날짜 형식은 YY요소와 유사하지만 여러 세기를 지정할 수 있습니다. YY대신 RR날짜 형식 요소를 사용하면 지정한 두
자리 연도 및 현재 연도의 마지막 두 자리에 따라 반환되는 세기 값이 달라진다.
슬라이드의 표는 RR요소를 요약한 것이다.
예제) RR날짜 형식 예제
오라클 디비 예제 : SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERH hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');
☞설명)
1990년 이전에 고용된 사원을 찾는 경우 RR형식을 사용하면 명령을 1999년에 실행하든 지금 실행하든 동일한 결과를 얻
는다.
예제) RR날짜 형식 요소 예제
오라클 디비 예제 : SELECT last_name, TO_CHAR(hier_date, 'DD-Mon-yyyy')
FROM employees
WHERE TO_DATE(hier_date, 'DD-Mon-yy') < '01-Jan-1990';
no rows selected
☞설명)
1990년 이전에 고용된 사원을 찾는 경우 RR형식을 사용할 수 있다.
현재 연도가 1999보다 크기 때문에 RR형식은 날짜의 연도 부분을 1950에서 1999사이로 해석한다.
반면에 다음 명령은 YY형식이 날짜의 연도 부분을 현재 세기로 해석하므로(2090), 행(row)이 선택되지 않는다.
MSSQL 디비 예제 :
-- 데이터 변환
--//cast//
select '오늘 날짜는 ' + getdate() + ' 입니다' --// 데이터를 붙이려면 데이터 타입 같아야 한다.
select '오늘 날짜는 ' + cast(getdate() as varchar(20)) + ' 입니다'
--날짜를 문자로 변환한 경우 월일년시분AM/PM순으로 출력
--//문자를 날짜로 변환
select '2008-08-08' + au_lname from authors
select '2008-08-08' + getdate() --문자가 날짜로 자동변환 세기는 제외됨
select cast('2008-08-08' as datetime) + getdate()
select cast('11' as money) + price from titles --- 문자를 숫자로 변환
--//convert// -- 날짜 출력형태를 지정하자
select convert(varchar(20), getdate(), 112) --convert(변경할 데이터타입, 출력데이터, 변경형식)
․ 단일행(row)함수는 여러 번 중첩될 수 있다.
․ 중첩함수는 가장 안쪽부터 바깥쪽 순으로 계산된다.
예제)
.ㅇ..ㅇ..ㅇ.
ㅇ
ㅇ
ㅇ
짤림