DB

SQL 함수

짱코딩러 2022. 8. 3. 17:15

<Dual 테이블>

간단하게 함수를 이용해서 계산 결과값을 확인 할 때 사용하는 테이블

SYS사용자가 소유하지만 어느 사용자에게나 접근 가능함

숫자 처리 함수

ABS : 절대값을 구하는 함수 (절대값은 주어진 데이터 음수일 경우 양수로 표현함)

SELECT -10, ABS(-10)
FROM DUAL;

COS :COSINE 값을 반환한다.

EXP : e(2.71828183…)의 n승을 반환한다.

FLOOR : 소수점 아래를 버리는 함수 (정수 부분만 출력됨)

SELECT 34.5678, FLOOR(34.5678)
FROM DUAL;

LOG : LOG값을 반환한다.

POWER : POWER(m, n) m의 n승을 반환한다.

SIGN :SIGN (n) n<0이면 –1, n=0이면 0, n>0이면 1을 반환한다.

SIN : SINE값을 반환한다.

TAN : TANGENT값을 반환한다.

ROUND : 특정 자릿수에서 반올림 하는 함수(입력한 자릿수 +1자리에서 반올림 됨)

ROUND(대상, 자릿수)
정수는 소수점 뒤로 감. 음수는 정수로 올라가고 0으로 채워짐
SELECT 34.5678, ROUND(34.5678, -1)
FROM DUAL;
  =>30

TRUNC : 지정한 자릿수 이하를 버린 결과를 구해주는 함수(지정한 자릿수 +1부터 버림)

TRUNC(대상, 자릿수)
(여러개를 적어줄 수도 있다.)
SELECT TRUNC(34.5678, 2), TRUNC(34.5678, -1),TRUNC(34.5678)
FROM DUAL;

MOD : 나누기 연산을 한 후에 나머지 값을 되돌려주는 함수

SELECT MOD (27, 2), MOD (27, 5), MOD (27, 7)
FROM DUAL;

 

문자 처리 함수

LOWER : 대문자로 변환하는 함수

UPPER : 소문자로 변환하는 함수

SELECT 문자열 혹은 칼럼명, UPPER('문자열')
FROM DUAL;

INITCAP : 이니셜만 대문자로 변환하는 함수

SELECT  '문자열',
               INITCAP('문자열')
FROM DUAL;

CONCAT : 문자의 값을 연결한다.

SUBSTR : 문자열 일부만 추출하는 함수

SUBSTR('문자열', 시작위치, 추출할 개수)
(시작 위치를 음수값으로 주면 뒤에서부터 세어줌)
SELECT SUBSTR('Welcome to Oracle', -4, 3)
FROM DUAL;

SUBSTRB : 바이트 수를 기준으로 문자열 일부를 추출하는 함수

SUBSTRB("문자열", "시작위치", "길이")

LENGTH : 컬럼에 저장 된 데이터 값이 몇 개의 문자로 구성되었는지 알려주는 함수

SELECT LENGTH('Oracle'), LENGTH('오라클')
FROM DUAL;

LENGTHB : 바이트 수를 알려주는 함수

INSTR : 문자열이나 칼럼에서 특정 문자가 나타나는 위치를 알려주는 함수             

(시작위치, 몇번째발견 생략시, 디폴트값은 1이고 첫번째 발견된 위치를 반환한다.)

INSTR(대상, 찾을글자, 시작위치, 몇_번째_발견)

LPAD : 칼럼이나 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채운다.

SELECT LPAD('Oracle', 20, '#')
FROM DUAL;
  =>##############Oracle

RPAD :  칼럼이나 문자열을 명시된 자릿수에서 왼쪽에 나타내고, 남은 오른쪽 자리를 특정 기호로 채운다.

SELECT RPAD('Oracle', 20, '#')
FROM DUAL;
  =>Oracle##############

TRIM : 칼럼이나 문자열의 특정 문자가 첫번째 글자이거나 마지막 글자이면 잘라내고 남은 문자열만 반환한다.

SELECT TRIM('a' FROM 'aaaaOracleaaaa')
FROM DUAL;
  =>Oracle
(공백 적으면 공백 지워줌)
SELECT TRIM('   Oracle   ')
FROM DUAL;

RTRIM : 오른쪽(뒤)에서 공백 문자를 삭제하는 함수

LTRIM : 왼쪽(앞)에서 공백 문자를 삭제하는 함수

SELECT LTRIM('   Oracle   ')
FROM DUAL;

CONVERT : CHAR SET을 변환한다.

CHR : ASCII 코드 값으로 변환한다.

ASCII : ASCII 코드 값을 문자로 변환한다.

REPLACE : 문자열에서 특정 문자를 변경한다.

 

날짜 함수

날짜 연산 : 날짜형 데이터에 숫자를 더하면 그 날짜로부터 그 기간(DAY)만큼 지난 날짜를 계산한다.

SYSDATE : 시스템에 저장된 현재 날짜를 반환하는 함수(+1하면 내일, -1하면 어제)

SELECT SYSDATE
FROM DUAL;

MONTHS_BETWEEN : 날짜와 날짜 사이의 개월 수를 구하는 함수

MONTHS_BETWEEN (date1, date2)
(값이 소수점 이하까지 구해질 경우 소수점 이하를 잘라내는 예제)
SELECT ENAME, HIREDATE,
     TRUNC( MONTHS_BETWEEN(SYSDATE, HIREDATE))
FROM EMP;

ADD_MONTHS : 특정 개월 수를 더한 날짜를 구하는 함수

ADD_MONTHS (날짜, 개월수)

NEXT_DAY : 해당 날짜를 기준으로 가장 가까운 요일에 해당되는 날짜를 반환하는 함수

NEXT_DAY (날짜, 요일)

LAST_DAY : 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수

LAST_DAY(날짜)

ROUND : 날짜를 반올림하는 함수(숫자도 되고 날짜도 되고~)

ROUND (날짜, 형식)
더보기
포맷 모델 단위
CC, SCC  4자리 연도의 끝 두 글자를 기준으로 반올림
SYYY, YYYY, YEAR
SYEAR, YYY, YY, Y		 년(7월 1일부터 반올림)
DDD, D, J 일을 기준
HH, HH12, HH24 시를 기준
Q 한 분기의 두 번째 달의 16일을 기준으로 반올림
MONTH, MON, MM, RM 월(16일을 기준으로 반올림)
DAY,DY,D 한주가 시작되는 날짜
MI 분을 기준

일을 기준으로 16일보다 적으면 이번 달 1일을 크면 다음달 1일을 구한다.

6월 9일 반올림하며 6월 1일이 되고 11월 17일은 반올림하여 12월 1이 된다.

1월 23일을 반올림하면 2월 1일이 된다. 

TRUNC : 특정 기준으로 날짜를 잘라내는 함수

TRUNC (날짜, 포멧)

 

형변환 함수

TO_CHAR : DATE형태의 데이터를 지정한 양식에 의해 VARCHAR2(문자열)형의 문자로 변환한다.

1.날짜

더보기
종류 의미
YYYY 년도 표현(4자리)
YY 년도 표현(2자리)
MM 월을 숫자로 표현
MON 월을 알파벳으로 표현
DAY 요일 표현
DY 요일을 약어로 표현
TO_CHAR (날짜 데이터, '출력형식')
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY')
FROM DUAL;

2.시간

더보기
종류 의미
AM 또는 PM 오전(AM), 오후(PM) 시각 표시
A.M 또는 P.M 오전(A.M), 오후(P.M) 시각 표시
HH또는 HH12 시간(1~12)
HH24 24시간으로 표현(0~23)
MI 분 표현
SS 초 표현
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS')
FROM DUAL;

3.숫자

더보기
구분 설명
0 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다.
9 자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다.
L 각 지열별 통화 기호를 앞에 표시한다.
. 소수점
,(콤마) 천 단위 자리 구분
SELECT TO_CHAR (123456, '000000000'),
               TO_CHAR (123456, '999,999,999')
FROM DUAL;
  =>000123456        123,456

 

TO_DATE : 문자열을 날짜형으로 변환하는 함수

TO_DATE(‘문자’, ‘format')
올해 며칠이 지났는지 현재 날짜에서 2022/01/01을 뺀 결과를 출력하는 예제
SELECT TRUNC(SYSDATE-TO_DATE('2022/01/01', 'YYYY/MM/DD'))
FROM DUAL;

 

TO_NUMBER : 특정 데이터를 숫자형으로 변환해 주는 함수.

SELECT TO_NUMBER 컬럼명
('20,000'을 '10,000'의 차이를 알아보기 위해서 빼기를 하는 예제)
SELECT TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '99,999')
FROM DUAL;

 

여러가지 함수들

NVL : NULL을 0 또는 다른 값으로 변환하기 위해서 사용하는 함수

NVL("값", "지정값")
NVL하기 위해 NVL안에서 형변환 해주는 예시
select empno, ename, NVL(TO_CHAR(MGR,'9999'),'CEO')AS MANAGER
from emp
where mgr is null;

NVL2 : 맨 앞의 값이 NULL이 아닌 경우 지정값1을  출력하고, NULL인 경우 지정값2를 출력한다.

NVL2("값", "지정값1", "지정값2")
NVL2("값", "NOT NULL", "NULL")

DECODE : 여러 가지 경우에 대해서 선택할 수 있도록 해주는 함수(switch case 문과 같은 기능)

                  조건이 일치(= 비교 연산자)하는 경우에 대해서만 적용

DECODE (컬럼명, 조건1, 결과1,
                                조건2, 결과2,
                                조건3, 결과3,
                                 기본결과n )

부서번호에 해당되는 부서명을 구하는 예제
SELECT ENAME, DEPTNO,
                DECODE(DEPTNO, 10, 'ACCOUNTING',
                                                  20, 'RESEARCH',
                                                  30, 'SALES',
                                                  40, 'OPERATIONS' )
                                                    AS DNAME
FROM EMP;

CASE : 여러 가지 경우에 대해서 하나를 선택하는 함수(if else if else 와 유사한 구조)

            DECODE 함수와 다르게 다양한 비교 연산자를 이용하여 조건을 제시할 수 있으므로 범위를 지정해줄 수 있다.

CASE 표현식 WHEN 조건1 THEN 결과1
                     WHEN 조건2 THEN 결과2
                     WHEN 조건3 THEN 결과3
                     ELSE 결과n
END

부서번호에 해당되는 부서명을 구하는 예제
SELECT ENAME, DEPTNO,
                CASE WHEN DEPTNO=10 THEN 'ACCOUNTING'
                           WHEN DEPTNO=20 THEN 'RESEARCH'
                           WHEN DEPTNO=30 THEN 'SALES'
                           WHEN DEPTNO=40 THEN 'OPERATIONS'
                END AS DNAME
FROM EMP;