DBMS/Oracle
D+26 [06.SQL 함수]
구일일구
2022. 8. 29. 17:53
반응형
06. SQL 함수
SQL*PLUS : 콘솔창에서 하는 거 : 필요X
DUAL 테이블과 SQL 함수 분류
DUAL 테이블
* 한 행으로 결과를 출력하기 위해선 DUAL 테이블을 이용함
오라클 프롬프트에 24*60*60을 입력하면 오류 발생. SQL 문이 아니기 때문
따라서 출력하기 위해 테이블을 이용함
* DEPT 테이블 자체가 4줄짜리 테이블이기 때문에 결과식이 4개나 나옴.
* 그래서 DUAL 테이블은 산술 연산이나 가상 칼럼 등의 값을 한번만 출력하고 싶을 때 많이 사용하는 유용한 테이블.
DUMMY라는 한 개의 칼럼으로 구성되어 있다.
* DESC DUAL: VARCHAR2(1)
DUMMY칼럼에는 한 개의 문자만을 저장할 수 있는데 X라는 값을 가진 단 하나의 로우만을 저장하고 있다.
단일행 함수와 그룹함수로 SQL 함수 분류
단일행 함수 : 여러개의 행에 대해 단일행 함수의 결과는 여러개로 구해짐
그룹 함수 : 여러개의 행에 대해서 그룹함수의 결과는 1개의 행으로 구해짐
그룹 함수
| 구분 | 설명 |
| SUM | 그룹의 누적 합계를 반환 |
| AVG | 그룹의 평균을 반환 |
| COUNT | 그룹의 총 개수를 반환 |
| MAX | 그룹의 최대값 반환 |
| MIN | 그룹의 최소값 반환 |
| STDDEV | 그룹의 표준편차를 반환 |
| VARIANCE | 그룹의 분산을 반환 |
단일행함수(1)문자 함수
| 구분 | 설명 |
| LOWER | 소문자로 변환 |
| UPPER | 대문자로 변환 |
| INITCAP | 첫 글자만 대문자로 나머지 글자는 소문자로 변환 |
| CONCAT | 문자의 값을 연결 |
| SUBSTR | 문자를 잘라 추출 (한글 1Byte) |
| SUBSTRB | 문자를 잘라 추출 (한글 2Byte) |
| LENGTH | 문자의 길이 반환 (한글 1Byte) |
| LENGTHB | 문자의 길이 반환 (한글 2Byte) |
| INSTR | 특정 문자의 위치 값을 반환 (한글 1Byte) |
| INSTRB | 특정 문자의 위치 값을 반환 (한글 2Byte) |
| LPAD, RPAD | 입력 받은 문자열과 기호를 정렬하여 특정 길이 문자열로 반환 |
| TRIM | 잘라내고 남은 문자를 표시 |
| CONVERT | CHAR SET을 변환 |
| CHR | ASCII 코드 값으로 변환 |
| ASCII | ASCII 코드 값을 문자로 변환 |
| REPLACE | 문자열에서 특정 문자를 변경 |
더보기
SUBSTRB 함수는 한글의 경우 문자 단위로 자를 때 깨지는 경우가 있음.
오라클 세팅에 따라서 한글이 3byte 또는 2 btye 일수 있으니 아래의 문자셋 쿼리로 오라클 세팅을 확인 후 사용하면
깨지는걸 막을 수 있음
| SUBSTRB("문자열", "시작위치", "길이") |
|
1
2
3
4
|
-- 오라클 문자셋 확인 쿼리
SELECT *
FROM nls_database_parameters
WHERE parameter LIKE '%CHARACTERSET%'
|
단행함수(2) 숫자함수
| 구분 | 설명 |
| ABS | 절대값 반환 |
| COS | 코사인값 반환 |
| EXP | e(2.71828183...)의 n승을 반환 |
| FLOOR | 소수점 아래 잘라냄(버림) |
| LOG | 로그값 반환 |
| POWER | POWER(m, n) : m의 n승을 반환 |
| SIGN | SIGN(n) : n<0이면 -1 / n=0이면 0 / n>0이면 1 반환 |
| SIN | 사인값 반환 |
| TAN | 탄젠트값 반환 |
| ROUND | 특정 자리수에서 반올림 |
| TRUNC | 특정 자리수에서 잘라냄 |
| MOD | 입력 받은 수를 나눈 나머지값을 반환 |
단행함수(3) 날짜함수
| 구분 | 설명 |
| SYSDATE | 시스템 저장된 현재 날짜를 반환 |
| MONTHS_BETWEEN(DATE1, DATE2) | 두 날짜 사이가 몇 개월인지 반환 |
| ADD_MONTHS(DATE1, 더할 개월수) | 특정 날짜에 개월 수 더하기 |
| NEXT_DAY(DATE1, 요일) | 특정 날짜를 기준으로, 가장 빨리 다가오는 요일에 해당되는 날짜를 반환 |
| LAST_DAY | 해당 달의 마지막 날짜 반환 |
| ROUND | 인자로 받은 날짜를 특정 기준으로 반올림 |
| TRUNC | 인자로 받은 날짜를 특정 기준으로 버림 |
단행함수(4) 변환함수
| 구분 | 설명 |
| TO_CHAR | 날짜형 혹은 숫자형을 문자형으로 변환 |
| TO_DATE | 문자형을 날짜형으로 변환 |
| TO_NUMBER | 문자형을 숫자형으로 변환 |
TO_CHAR : 날짜형 혹은 숫자형을 문자형으로 변환
| TO_CHAR(날짜 데이터, '출력 형식') |
날짜 출력 형식의 종류
| 종류 | 의미 |
| YYYY | 년도 표시 (4자리) |
| YY | 년도 표시 (2자리) |
| MM | 월을 숫자로 표시 |
| MON | 월을 알파벳으로 표시 |
| DD | 일 표시 |
| DAY | 요일 표시 |
| DY | 요일을 약어로 표시 |
시간 출력 형식의 종류
| 종류 | 의미 |
| AM 또는 PM | 오전(AM), 오후(PM) 시각 표시 |
| A.M 또는 P.M | 오전(A.M), 오후(P.M) 시각 표시 |
| HH 또는 HH12 | 시간 (1 ~ 12) |
| HH24 | 시간 (0 ~ 23) |
| MI | 분 표시 |
| SS | 초 표시 |
숫자 출력 형식 표
| 구분 | 설명 |
| 0 | 자릿수를 나타냄. 자릿수가 맞지 않을 경우 0으로 채움 |
| 9 | 자릿수를 나타냄. 자릿수가 맞지 않아도 채우지 않음 |
| L | 각 지역별 통화 기호를 앞에 표시함 |
| . | 소수점 |
| , | 천 단위 자리 구분 |
숫자 출력 예시
| 숫자 | 형식 | 결과 |
| 12345.67 | 999,999.999 | 12,345.67 |
| 12345.67 | 999999 | 12345 |
| 12345.67 | $999,999.99 | $12,345.67 |
| 12345.67 | L999,999.99 | ￦12,345.67 |
| 12345.67 | S999,999.99 | +12,345.67 |
일반 함수
| 구분 | 설명 |
| NVL | 첫번째 인자 값이 NULL이면 두번째 인자값으로 변경 |
| DECODE | 첫번째 인자 값을 조건에 맞춰 변경 (if와 유사) |
| CASE | 조건에 맞는 문장을 수행 (switch와 유사) |
NVL(인자값1, 인자값2) ➡️ 인자값1이 null이면 인자값2로 변경DECODE (표현식, 조건1, 결과1,
조건2, 결과2,
조건3, 결과3,
기본결과n)CASE 표현식 WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
ELSE 결과n
ENDCase then 뒤에서 end 뒤에 무언가를 붙이는건 별칭 붙이는거임
반응형