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

Jongyhyuk Kim·2021년 1월 28일
0

SQL

목록 보기
3/4
post-thumbnail

'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 반환
profile
Bellhyuk, Bottom out

0개의 댓글