4. [Oracle] 변환 함수와 조건부 표현식을 사용해보자.

-변환함수

오라클 데이터베이스에서 테이블의 열은 Oracle 데이터 유형 이외에 ANSI(American National Standards Institute), DB2 및 SQL/DS 데이터 유형을 사용하여 정의할 수도 있습니다. 하지만 Oracle 서버는 내부적으로 이러한 데이터 유형을 Oracle 데이터 유형으로 변환합니다.

 

어떤 경우에는 Oracle 서버가 특정 데이터 유형의 데이터를 사용하는 위치에 다른 데이터 유형의 데이터가 전달되기도 합니다. 이 경우Oracle 서버가 데이터를 예상 데이터 유형으로 자동으로 변환할 수 있습니다. 이러한 데이터 유형 변환은 Oracle 서버에 의해 암시적으로 수행되거나 유저에 의해 명시적으로 수행될 수 있습니다.

 

암시적 데이터 유형 변환은 다음 슬라이드에 설명된 규칙에 따라 수행됩니다. 명시적 데이터 유형 변환은 변환 함수를 사용하여 수행됩니다. 변환 함수는 값의 데이터 유형을 변환합니다. 일반적으로 함수 이름의 형식은 data type TO data type 규칙을 따릅니다. 첫번째 데이터 유형은 입력 데이터 유형이고 두번째 데이터 유형은 출력 데이터 유형입니다.

 

-암시적 데이터 유형 변환

Oracle 서버는 자동으로 표현식에서 데이터 유형 변환을 수행합니다. 예를 들어, 표현식 hire_date > '01-JAN-90'에서 문자열 '01-JAN-90'은 암시적으로 날짜로 변환됩니다. 따라서 표현식의 VARCHAR2 또는 CHAR 값은 암시적으로 숫자 또는 날짜 데이터 유형으로 변환될 수 있습니다.

 

일반적으로 Oracle 서버에서는 데이터 유형 변환이 필요한 경우 표현식에 대한 규칙을 사용합니다. 예를 들어, grade가 CHAR(2) 열인 경우 표현식 grade = 2에서 숫자 2이 문자열"2" 로 암시적으로 변환됩니다.

 

 

 

 

 

 

-명시적 데이터 유형 변환

SQL은 세 가지 데이터 유형 변환 함수를 제공합니다.

함수	목적
TO_CHAR(number|date,[ fmt], [nlsparams])	형식 모델 fmt를 사용하여 숫자 또는 날짜 값을 VARCHAR2 문자열로 변환합니다. 숫자 변환: nlsparams 파라미터는 다음 문자를 지정하며, 이 문자는 숫자 형식 요소로 반환됩니다. • 소수점 문자 • 그룹 구분자 • 로컬 통화 기호 • 국제 통화 기호 nlsparams 또는 기타 파라미터가 생략된 경우 이 함수는 세션에 대해 기본 파라미터 값을 사용합니다.
TO_CHAR(number|date,[ fmt], [nlsparams])	날짜 변환: nlsparams 파라미터는 월 및 일 이름과 약어가 반환되는 언어를 지정합니다. 이 파라미터가 생략된 경우 이 함수는 세션에 대해 기본 날짜 언어를 사용합니다.
TO_NUMBER(char,[fmt], [nlsparams]	숫자를 포함한 문자열을 선택적 형식 모델 fmt로 지정된 형식의 숫자로 변환합니다. 이 함수에서 nlsparams 파라미터는 숫자 변환용 TO_CHAR 함수에서와 동일한 용도로 사용됩니다.
TO_DATE(char,[fmt],[nlsparams])	날짜를 나타내는 문자열을 지정된 fmt에 따라 날짜 값으로 변환합니다. fmt가 생략된 경우 형식은 DD-MON-YY입니다.   이 함수에서 nlsparams 파라미터는 날짜 변환용 TO_CHAR 함수에서와 동일한 용도로 사용됩니다.

 

*TO_CHAR, TO_DATE, TO_NUMBER함수

 

-날짜에 TO_CHAR 함수 사용(날짜를 문자로)

 

TO_CHAR(date, 'format_model')

 

형식 모델:

• 작은 따옴표로 묶어야 합니다.

• 대소문자를 구분합니다.

• 임의의 유효한 날짜 형식 요소를 포함할 수 있습니다.

• 채워진 공백을 제거하거나 선행 0을 출력하지 않는fm 요소를 갖습니다.

• 쉼표를 사용하여 날짜 값과 구분됩니다.

 

요소	결과
YYYY	숫자로 된 전체 연도
YEAR	영어 철자로 표기된 연도
MM	월의 2자리 값
MONTH	전체 월 이름
MON	월의 3자 약어
DY	3문자로 된 요일 약어
DAY	요일의 전체 이름
DD	숫자 형식의 월간 일

 

-날짜 형식 모델의 요소

 

• 시간 요소는 날짜에서 시간 부분의 형식을 지정합니다.

HH24:MI:SS AM > 15:45:32 PM

• 문자열은 큰 따옴표로 묶어 추가합니다

DD "of" MONTH > 12 of OCTOBER

• 숫자 접미어는 숫자를 영어 철자로 표기합니다.

ddspth > fourteenth

 

AM 또는 PM > 자오선 표시

A.M. 또는P.M. > 마침표가 있는 자오선 표시

HH 또는 HH12 또는 HH24 > 하루 시간 또는 반일 시간(1–12) 또는 전일 시간(0–23)

MI > 분(0–59)

SS > 초(0–59)

SSSSS > 자정 이후의 초(0–86399)

 

/ . , > 결과에 구두점 생성

"of the" > 결과에 따옴표로 묶은 문자열 생성

 

TH > 서수(예: 4TH를 DDTH로 표기)

SP > 영어 철자로 된 숫자(예: FOUR를 DDSP로 표기)

SPTH > 또는 THSP 영어 철자로 된 서수(예: FOURTH를 DDSPTH로 표기)

 

-숫자에 TO_CHAR 함수 사용

 

TO_CHAR(number, 'format_model')

 

다음은 TO_CHAR 함수와 함께 사용하여 숫자 값을 문자로 표시할 수 있는 몇 가지 형식 요소입니다.

요소	결과
9	숫자를 나타냄
0	0이 표시되도록 강제 적용
$	부동 달러 기호 배치
L	부동 로컬 통화 기호 사용
.	소수점 출력
,	천단위 표시자로 쉼표 출력

 

-TO_NUMBER 및 TO_DATE 함수 사용

 

• TO_NUMBER 함수를 사용하여 문자열을 숫자 형식으로 변환합니다.

TO_NUMBER(char[, 'format_model'])

• TO_DATE 함수를 사용하여 문자열을 날짜 형식으로 변환합니다.

TO_DATE(char[, 'format_model'])

• 이러한 함수는 fx 수정자를 가집니다. 이 수정자는 TO_DATE 함수의 문자 인수 및 날짜 형식 모델에 대한 정확한 일치를 지정합니다.

 

 

 

-RR 날짜 형식으로 TO_CHAR 및 TO_DATE 함수 사용

1990년 이전에 채용된 사원을 찾으려면 RR 날짜 형식을 사용합니다. 그러면 명령이 1999년에 실행되든 현재 실행되든 동일한 결과를 나타냅니다.

 

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')

FROM employees

WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');

 

TO_CAHR 이용 예제들

 

-함수 중첩

• 단일 행 함수는 어떠한 레벨로도 중첩될 수 있습니다.

• 중첩된 함수는 가장 깊은 레벨에서 가장 낮은 레벨로 평가됩니다.

F3(F2(F1(col,arg1),arg2),arg3)

 

-함수 중첩 예제

 

*일반함수

다음 함수는 임의의 데이터 유형을 사용하며 null 사용과 관련이 있습니다.

• NVL (expr1, expr2) : null 값을 실제 값으로 변환합니다.

• NVL2 (expr1, expr2, expr3) : expr1이 null이 아닌 경우 NVL2는 expr2를 반환합니다. expr1이 null인 경우 NVL2는 expr3을 반환합니다. 인수 expr1은 임의의 데이터 유형을 가질 수 있습니다.

• NULLIF (expr1, expr2) : 두 표현식을 비교하여 같으면 null을 반환하고 같지 않으면 첫번째 표현식을 반환합니다.

• COALESCE (expr1, expr2, ..., exprn) : 표현식 리스트에서 null이 아닌 첫번째 표현식을 반환합니다.

#NVL의 빈도 수가 가장 높습니다.

 

-NVL함수

null 값을 실제 값으로 변환합니다.

• 사용할 수 있는 데이터 유형은 날짜, 문자 및 숫자입니다.

• 데이터 유형이 일치해야 합니다.

– NVL(commission_pct,0)

– NVL(hire_date,'01-JAN-97')

– NVL(job_id,'No Job Yet')

 

모든 사원의 연봉을 계산하려면 월급에 12를 곱한 다음 결과에 커미션을 더합니다.

SELECT last_name, salary, commission_pct,

(salary*12) + (salary*12*commission_pct) AN_SAL

FROM employees;

 

연봉은 커미션을 받는 사원에 대해서만 계산된다는 점에 유의하십시오. 표현식의 열 값이 null인 경우 결과는 null입니다. 모든 사원에 대해 값을 계산하려면 산술 연산자를 적용하기 전에 null 값을 숫자로 변환해야 합니다. 슬라이드의 예제에서 NVL 함수는 null 값을 0으로 변환하는 데 사용됩니다.

 

-NVL2 함수

NVL2 함수는 첫번째 표현식을 검사합니다. 첫번째 표현식이 null이 아니면NVL2 함수는 두번째 표현식을 반환합니다. 첫번째 표현식이 null이면 세번째 표현식이 반환됩니다.

 

NVL2 (expr1, expr2, expr3)

• expr1은 null을 포함할 수 있는 소스 값 또는 표현식입니다 .

• expr2는 expr1이 null이 아닌 경우에 반환되는 값입니다.

• expr3은 expr1이 null인 경우에 반환되는 값입니다.

 

값이 발견되면 텍스트 리터럴 값 SAL+COMM이 반환됩니다. COMMISSION_PCT 열에 null 값이 있는 경우 텍스트 리터럴 값 SAL이 반환됩니다.

 

-NULLIF 함수

NULLIF 함수는 두 표현식을 비교합니다.

 

NULLIF (expr1, expr2)

 

• NULLIF는 expr1과 expr2를 비교합니다. 두 표현식이 같으면 이 함수는 null을 반환합니다. 두 표현식이 다르면 이 함수는 expr1을 반환합니다. 그러나 expr1에 대해 리터럴 NULL을 지정할 수 없습니다.

 

-COALESCE 함수

• NVL 함수 대신 COALESCE 함수를 사용했을 때의 이점은 COALESCE 함수가 여러 대체 값을 수용할 수 있다는 것입니다.

• 첫번째 표현식이 null이 아닌 경우COALESCE 함수는 해당 표현식을 반환합니다. 그렇지 않은 경우 나머지 표현식에 COALESCE를 수행합니다.

 

조직에서 커미션을 받지 않는 사원에게 $2,000의 급여 인상을 제공하려고 합니다. 또한 커미션을 받는 사원의 경우에는 기존 급여에 커미션 금액을 추가한 새 급여를 query에서 계산해야 합니다.

 

SELECT last_name, salary, commission_pct,

COALESCE((salary+(commission_pct*salary)), salary+2000, salary) "New Salary"

FROM employees;

 

출력을 검사합니다. 커미션을 받지 않는 사원의 경우 $2,000가 인상된 새 급여가 New Salary 열에 표시되고 커미션을 받는 사원의 경우 커미션 금액을 추가하여 계산된 급여가 New Salary 열에 표시됩니다.

 

*조건부 표현식

• SQL 문에서 IF-THEN-ELSE 논리를 사용할 수 있습니다.

• 다음 두 가지 방법을 사용합니다.

– CASE 식

– DECODE 함수

 

-CASE식

IF-THEN-ELSE 문 작업을 수행하여 조건부 조회를 편리하게 수행하도록 합니다.

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn ELSE else_expr]

END

 

-DECODE 함수

CASE 식 또는IF-THEN-ELSE 문의 작업을 수행하여 조건부 조회를 편리하게 수행합니다.

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])