SQL 3. 단일 행 함수를 사용하여 결과물 변경

🆎문자 함수

1. 대소문자 변환 함수

SELECT employee_id, last_name
FROM employees
WHERE UPPER(last_name) = 'higgins';

SELECT 'The job id for '||UPPER(last_name)||' is '||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;

 

2. 문자 조작 함수

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, -1, 1) = 'n';

 

🔢숫자 함수

round(ab.cde, 2)-> 소수점 셋째자리에서 반올림 ab.cd
round(ab.cde, 0)-> 소수점 첫째자리에서 반올림 ab
round(ab.cde, -1) ->일의자리에서 반올림 a0

trunc(ab.cde, 2) -> 소수점 셋째자리에서 버림 ab.cd
trunc(ab.cde) -> 소수점 첫째자리에서 버림 ab
trunc((ab.cde, -1) -> 일의 자리에서 버림 a0

mod(salary, 5000) -> salary를 5000으로 나눈 나머지

 

🔜날짜 함수

- 오라클 데이터베이스는 내부 숫자 형식(세기-년-월-일-시-분-초)으로 날짜 저장
- 기본 날짜 표시 및 입력 형식은 DD-MON-RR

 

1. 날짜 연산

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;

 

2. 날짜 조작 함수

함수	결과
MONTHS_BETWEEN (A, B)	두 날짜 간의 월 수
ADD_MONTHS (A, B)	날짜에 월 추가
NEXT_DAY (날짜, 요일)	지정된 날짜 다음으로 처음 만나는 요일
LAST_DAY (날짜)	월의 마지막 날
ROUND(A, B)	날짜 반올림
TRUNC(A, B)	날짜 내림

SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) "TENURE", 
ADD_MONTHS(hire_date, 6) "REVIEW",
NEXT_DAY(hire_date, '금요일'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 200;

함수	결과
SYSDATE = 03/07/25라고 가정
ROUND(SYSDATE, 'MONTH')	03/08/01
ROUND(SYSDATE, 'YEAR')	04/01/01
TRUNC(SYSDATE, 'MONTH')	03/07/01
TRUNC(SYSDATE, 'YEAR')	03/01/01

 

💨변환 함수

1. 암시적 데이터 유형 변환

: 오라클 서버는 표현식에서 자동으로(암시적으로) 변환 가능

소스	대상
VARCHAR2/CHAR	NUMBER
VARCHAR2/CHAR	DATE
NUMBER	VARCHAR2/CHAR
DATE	VARCHAR2/CHAR

2. 명시적 데이터 유형 변환

1) 날짜에 TO_CHAR함수를 사용

 TO_CHAR(date, 'format_model')

* format model 형식모델 :

작은 따옴표로 묶고, 대소문자 구분
쉼표로 날짜 값을 형식모델과 구분
임의의 유효한 날짜 형식 요소를 포함할 수 있음.
채워진 공백을 제거하거나, 선행0을 제거하려면 채우기모드 fm사용(fm 붙지않으면 빈자리에 0이 붙고, 붙으면 0없이 숫자만 나옴)

* 시간요소는 날짜에서 시간 부분의 형식지정 (HH24:MI:SS AM -> 15:45:32 PM)
* 문자열은 큰 따옴표로 묶어 추가 (DD "of" MONTH -> 12 of OCTOBER)
* 숫자 접미어는 숫자를 영어철차로 표기 (ddspth -> fourteenth)

SELECT last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE
FROM employees;

2) 숫자에 TO_CHAR함수를 사용

: 숫자를 문자데이터 유형으로 변환

TO_CHAR(number, 'format_model')

3) TO_NUMBER 및 TO_DATE함수 사용

TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, 'format model]')

* fx 수정자 : (fix) 모양새를 똑같이 만들어야함. TO_DATE 함수의 문자 인수 및 날짜 형식 모델에 대해 정확한 일치를 지정

SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('5월  11, 2004', '**fx**Month DD, YYYY'); // 오류 발생. 공백 두칸이기 때문에 일치하지 않음

 

♌일반 함수

함수	설명
NVL(number_column, expr2) NVL(date_column, '95/01/01') NVL(character_column, 'Unavailable')	null값을 expr2 값으로 변환
NVL2(expr1, expr2, expr3)	expr1이 null이 아니면 expr2반환, null이면 expr3 반환
NULLIF(expr1, expr2)	expr1=expr2 같으면 null반환, 다르면 expr1반환(expr1값에는 null지정할 수 없음)
COALESCE(expr1, expr2, ...exprn)	null이 아닌 경우에 해당 표현식 반환. (expr1이 null이면 expr2확인, expr2 null이 아니면 expr2 반환)
* 데이터 유형은 반드시 일치해야 함.

SELECT last_name, salary, commission_pct, COALESCE((salary+(commission_pct\*salary)), salary+2000, salary) "New Salary"
FROM employees;

👀 조건부 표현식 (IF-THEN-ELSE논리)

- CASE식 

SELECT last_name, job_id, salary, 
		CASE job_id WHEN 'IT_PROG'	THEN 1.10*salary
        	WHEN 'ST_CLERK'			THEN 1.15*salary 
            	WHEN 'SA_REP'			THEN 1.20*salary
            	ELSE salary END "Revised_Salary"
FROM employees;

- DECODE 함수

SELECT last_name, salary
		DECODE (TRUNC(salary/2000, 0), 
        		0, 0.00,
                        1, 0.09,
                        2, 0.20,
                        3, 0.30,
                           0.45) TAX_RATE
FROM employees
WHERE department_id = 80;