'21.01.29. - 내용 추가
교재: 'Introduction to Oracle9i: SQL'
3. 단일 행 함수
SQL 함수에는 '단일 행 함수'와 '여러 행 함수'가 있다.
어렵게 생각하지 않아도 말 그대로 단일 행 내에서 이뤄지는 함수로 단일 행만 연산하며 행 당 하나의 결과를 반환한다.
유형으로는 다음과 같이 존재한다.
- 문자형: 문자를 입력 값으로 받으며 문자 또는 숫자 값을 반환
- 숫자형: 숫자를 입력하면 숫자 값을 반호나
- 날짜형: DATE 데이터 유형의 값에 동작, 모든 날짜 함수는 DATE 데이터 유형 값을 반환하며 MONTHS_BETWEEN 함수만 숫자를 반환
- 변환형: 값의 데이터 유형을 변환
- 일반형: NVL, NVL2, NULLIF, COALSECE, CASE, DECODE
3-1. 문자함수
문자 함수는 또다시 크게 두 개로 분류할 수 있다.
1. 대소문자 조작 함수
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'Higgins';

- LOWER(): 대소문자 또는 대문자 문자열을 소문자로 변환
- UPPER(): 대소문자 또는 소문자 문자열을 대문자로 변환
- INITCAP: 각 단어의 첫 문자는 대문자로, 나머지 문자는 소문자로 변환
출력은 결국 SELECT문에서 이뤄진다는 것을 다음 예를 통해 확인할 수 있다.
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'Higgins';

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, 4) = 'REP';

- CONCAT(exp,exp): 첫 번째 문자 값을 두 번쨰 문자 값에 연결, 연결 연산자(||)와 동일
- SUBSTR(exp,m,n): 문자 값의 위치 m에서 n까지 지정된 문자를 반환
(m이 음수이면 문자 값의 끝부터, n이 생략되면 문자열의 끝까지 반환)
- LENGTH(exp): 표현식의 문자 수를 반환
- INSTR(exp,'str',m,n): 지정된 문자열의 위치를 숫자로 반환
검색 시작 위치 m과 문자열 발생 횟수 n을 지정 가능(m, n의 기본 값은 1)
- L(R)PAD(exp, n, 'str'): 전체 폭이 n이 되도록 문자 값을 오른쪽(왼쪽) 정렬하고 빈 곳을 string으로 채움
- TRIM(trim_char FROM trim_source): 문자열에서 접두어나 접미어 또는 두 가지 모두를 자름. 문자 literal이면 ''(작은 따옴표)로 묶어야 함
- REPLACE(text,search_str,replacement_str): 텍스트 표현 식에서 문자열을 검색하여 해당 문자열을 발견한 경우 지정된 대체 문자열로 바꾼다.
3-2. 숫자 함수
SELECT ROUND(45.923,2), TRUNC(45.923)
FROM DUAL;
- ROUND(exp, n): 열, 표현식 또는 값을 소수점 n째 자리로 반올림
(n을 지정하지 않은 경우, 소수점 이하 값이 없어짐, n이 음수이면 소수점 왼쪽의 수를 반올림)
- TRUNC(exp, n): 열 표현식 또는 값을 소수점 n째 자리까지 남기고 버림.
(n을 지정하지 않은 경우, 기본 값 0이 지정됨)
- MOD(m, n): m을 n으로 나눈 나머지를 반환
DUAL 테이블은 system이 소유하는 테이블로 함수 및 계산 결과를 보는 데 사용할 수 있는 dummy table
3-3. 날짜 함수
기본 날짜 표기 형식 DD-MON-RR
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), round(hire_date, 'MONTH'),
trunc(hire_date, 'MONTH')
FROM employees
where months_between (sysdate, hire_date) <36;
- MONTH_BETWEEN(date1, date2): date1과 date2 간의 달 수를 계산, 양수 또는 음수가 나올 수 있다.
- ADD_MONTHS(date, n): date에 n달을 더함. n은 정수이어야하며 음수 가능
- NEXT_DAY(date, 'char'): date보다 이후 날짜로 지정한 요일('char')에 해당하는 날짜
- LAST_DAY(date): date를 포함하는 달의 마지막 날짜
- ROUND(date[, 'fmt']): fmt에 의해 지정된 단위로 반올림한 date를 반환, fmt 생략 시 가장 가까운 날로 반올림
- TRUNC(date[, 'fmt']): 날짜의 시간 부분을 fmt로 지정한 단위까지 남기고 버린 후 반환, fmt 생략 시 가장 가까운 날에 맞추어 버려짐.
3-4. 변환 함수
암시적 변환(implicit)
- VARCHAR2 or CHAR -> NUMBER
- VARCHAR2 or CHAR -> DATE
- NUMBER -> VARCHAR2
- DATE -> VARCHAR2
명시적 변환(explicit)

숫자 표시에 영향을 주는 접미어 지정
- TH: 서수 Ex.4TH -> DDTH
- SP: 문자로 표현한 숫자 Ex.FOUR -> DDSP
- SPTH or THSP: 문자로 표현한 서수 Ex.FOURTH -> DDSPTH
숫자에 TO_CHAR 함수 사용
- 9: 숫자를 표시
- 0: 0을 강제로 표시
- $: 부동 $ 기호를 넣음
- L: 부동 지역 통화 기호를 사용
- .: 소수점을 출력
- ,: 천 단위 구분자를 출력
3-5. 일반 함수
모든 데이터 유형을 사용할 수 있으며 표현식 목록에 null 값 가능
--NVL
select last_name, salary, NVL(commission_pct, 0)
from employees;
--NVL2
select last_name, salary, commision_pct, NVL2(commision_pct, 'SAL+COMM', 'SAL') income
from employees where department_id IN(50,80);
--NULLIF
select first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
from employees;
--COALESCE
select last_name, COALESCE(commission_pct, salary, 10) comm
from employees;
--CASE
select last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
ELSE salary END "REVISED_SALARY"
from employees;
--DCODE
select last name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary) REVISED_SALARYY
from employees;
- NVL(expr1, expr2)
null을 실제 값으로 변환, 데이터 유형은 서로 일치해야 한다.
- NVL2(expr1, expr2, expr3)
expr1을 검사하여 null이 아닌 경우 expr2를, null인 경우 expr3를 반환한다.
- NULLIF(expr1, expr2)
두 표현식을 비교하여 동일한 경우 null을 반환, 동일하지 않은 경우 expr1을 반환(expr1에 null 리터럴 지정을 할 수 없다.)
- COALESCE(expr1, expr2, ... exprn)
첫 번째부터 null 값을 확인하며 널이 아닌 경우 반환, null 값이면 다음 표현식으로 넘어가 반복하며 모두 널인 경우 exprn을 반환한다.
- CASE expr WHEN comparison_expr1 THEN return_expr1 ... ELSE else_expr END
프로시저 호출 필요 없이 SQL문에서 IF-THEN-ELSE 논리 사용
- DECODE(expr, search1, result1, ...)
expr을 각 search 값과 비교한 후 해독하여 expression이 search 값과 동일하면 result 반환