TIL 210128 | SQL 기초 - 3. 단일 행 Function

'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 반환