개발 공부/Oracle

[Oracle] - 오라클 함수

징_ 2023. 11. 28. 04:58

* 오라클 함수 

: 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어 

 

- 내장 함수(built-in function)

: 오라클 기본 제공 함수

  - 단일행 함수(single-now function)

: 데이터가 한 행씩 입력되고 한 행당 결과가 하나씩 나오는 함수

  - 다중행 함수(multiple-row funcction) 

: 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수

 

- 사용자 정의 함수(user-defined function) 

: 사용자가 필요에 의해 직접 정의한 함수

 

 

 

 

 

 

* 문자 함수 - 문자 데이터 가공

- 대소문자 변환 함수(UPPER, LOWER, INITCAP)

  • UPPER(문자열) : 괄호 안 문자 데이터를 모두 대문자로 변환 후 반환
  • LOWER(문자열) : 괄호 안 문자 데이터를 모두 소문자로 변환 후 반환
  • INITCAP(문자열) : 괄호 안 문자 데이터 중 첫 글자는 대문자, 나머지는 소문자로 변환 후 반환

<활용>

SELECT *
  FROM EMP
 WHERE UPPER(ENAME) = UPPER('scott');
 
 SELECT *
  FROM EMP
 WHERE UPPER(ENAME) LIKE UPPER('%scott%');

=> 대문자로 변환되어서 비교됐기 때문에 SCOTT데이터 출력됨

 

 

 

- 문자열 길이를 반환하는 함수 (LENGTH) 

: 특정 문자열의 길이 구할 때 사용하는 함수 

SELECT ENAME, LENGTH(ENAME)
  FROM EMP
 WHERE LENGTH(ENAME) >= 5;

 

✔️바이트 수를 반환하는 함수 - LENGTHB

: 문자열의 바이트 수를 반환하는 함수로 LENGTHB(문자열)형식으로 사용

 

✔️DUAL 테이블

: 더미(dummy) 테이블(가상 테이블) 로 데이터 저장공간이 아닌 임시 연산이나 함수의 결과 값 확인 용도로 사용 된다.

 

 

 

 

- 문자열 일부를 추출하는 함수 (SUBSTR)

↔ 바이트는 SUBSTRB

  • SUBSTR(JOB, 1, 2)의미 : SALEMAN에서 첫 번째 글자부터 두 글자 SA 출력
  • SUBSTR(JOB, 3, 2)의미 : SALEMAN에서 세 번째 글자부터 두 글자 LE 출력
SELECT JOB, SUBSTR(JOB, 1, 2)
  FROM EMP;
  
SELECT JOB, SUBSTR(JOB, 3) => 세 번째 글자부터 끝까지 출력
  FROM EMP;

 

✔️SUBSTR과 다른 함수 함께 사용

SELECT JOB,
	   SUBSTR(JOB, -LENGTH(JOB)),
       SUBSTR(JOB, -LENGTH(JOB), 2),
       SUBSTR(JOB, -3)
  FROM EMP;

=> 음수로 시작 위치값을 사용한 것 주의하기

  • SUBSTR(JOB, -LENGTH(JOB)) : -5자리 부터 끝까지 출력
  • SUBSTR(JOB, -LENGTH(JOB), 2) : -5자리 부터 두 글자 출력
  • SUBSTR(JOB, -3) : -3자리부터 끝까지 출력 

 

 

 

- 특정 문자 위치 찾는 함수 (INSTR)

: 총 4개의 입력 값을 지정할 수 있으며 대상 문자열 데이터와 위치를 찾으려는 부분 문자는 필수로 지정해야 한다. 

INSTR([대상 문자열 데이터],			-- 필수
	  [위치 찾으려는 부분 문자],    -- 필수
      [위치찾기를 시작할 대상 문자열 데이터 위치],		--(선택, 기본값은 1)
      [시작 위치에서 찾으려는 문자가 몇 번쨰인지 지정])	  --(선택, 기본값은 1)

 

<예시>

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,
       INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2,
       INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3
  FROM DUAL;

  • INSTR('HELLO, ORACLE!', 'L') : 시작 위치 미정으로 처음부터 'L'검색
  • INSTR('HELLO, ORACLE!', 'L', 5) : 시작 위치 5로 다섯 번째 글자 'O' 뒤에 오는 'L'을 찾음(12번째)
  • INSTR('HELLO, ORACLE!', 'L', 2, 2) : 시작 위치 2로 두 번째 글자 'E' 뒤에 오는 'L'중 두 번째  'L' 찾음(4번째)

 

✔️특정 문자를 포함하고 있는 행 찾기

SELECT *
  FROM EMP
 WHERE INSTR(ENAME, 'S') > 0;


       => INSTR 함수의 결과 값이 0보다 크다면 사원 이름에 S가 존재한다는 의미

 

 

 

- 특정 문자를 다른 문자로 바꾸는 함수 (REPLACE)

REPLACE([문자열 데이터 / 열 이름],	--필수
	    [찾는 문자],				 --필수
        [대체할 문자]                --선택

 

<예시>

SELECT '010-1234-5678' AS REPLACE_BEFORE,
	   REPLACE('010-1234-5678', '-', ' ') AS REPLACE_1
       REPLACE('010-1234-5678', '-') AS REPLACE_2
  FROM DUAL;
  • REPLACE_1 : 010 1234 5678  => -가 공백으로 변경
  • REPLACE_2 : 01012345678 => 대체할 문자 지정하지 않아서 -가 삭제됨

 

 

 

- 데이터의 빈 공간을 특정 문자로 채우는 함수 (LPAD, RPAD)

  • LPAD : Left Padding - 남은 빈 공간을 왼쪽에 채움
  • RPAD : Right padding - 남은 빈 공간을 오른쪽에 채움
LPAD([문자열 데이터 / 열이름], 		--필수
	 [데이터의 자릿수],			  --필수
     [빈 공간에 채울 문자]			 --선택
RPAD([문자열 데이터 / 열이름], 		--필수
	 [데이터의 자릿수],			  --필수
     [빈 공간에 채울 문자]			 --선택

 

<예시>

SELECT 'Oracle' --6자리
	   LPAD('Oracle', 10, '*')
  FROM DUAL;

=> ****Oracle이 된다. 10자리에서 기존 문자열 6자리 제외 4자리에 '*'가 들어옴

*만약 빈 공간에 채울 문자 자리에 아무 값도 넣지 않았다면 공백으로 처리된다.

 

 

 

 

- 두 문자열 데이터 합치는 함수 (CONCAT)

SELECT CONCAT(EMPNO, ENAME),
	   CONCAT(EMPNO, CONCAT(':', ENAME))
  FROM EMP
 WHERE ENAME = 'SCOTT';

✔️문자열 데이터 연결 ||연산자

SELECT EMPNO || ENAME,
       EMPNO || ':' || ENAME
  FROM EMP;

=> 위와 동일

 

 

 

 

- 특정 문자 지우는 함수 (TRIM, LTRIM, RTRIM)

  • 삭제할 문자 생략 : 공백을 제거
  • 삭제 옵션
    • LEADING : 왼쪽에 있는 글자 삭제
    • TRAILING : 오른쪽에 있는 글자 삭제
    • BOTH : 양쪽에 있는 글자 삭제 
TRIM([삭제 옵션] 			 --선택
	 [삭제할 문자]  			--선택
FROM [원본 문자열 데이터])   --필수


LTRIM([원본 문자열 데이터],   --필수
      [삭제할 문자 집합])	   --선택
RTRIM([원본 문자열 데이터],   --필수
      [삭제할 문자 집합])	   --선택
SELECT TRIM('  ORACLE  ') AS TRIM --TRIM으로 공백 없어짐
     , TRIM(LEADING FROM '  ORACLE  ') AS TRIM_LEADING      --TRIM방향 왼쪽
     , TRIM(TRAILING FROM '  ORACLE  ') AS TRIM_TRAILING    --TRIM방향 오른쪽
     , TRIM(BOTH FROM '  ORACLE  ') AS TRIM_BOTH            --TRIM방향 양쪽
  FROM DUAL;

 

 

 

 

 

 

 

* 숫자 함수 - 숫자 데이터 연산 및 수치 조정

- 반올림 함수 (ROUND)

: 반올림 할 때 사용하는 함수로 반올림 위치 지정 안하면 소수점 첫 번째 자리에서 반올림 함.

ROUND([숫자],  		--필수
	  [반올림 위치])   --선택
SELECT ROUND(3.1415, 2) ROUND_2     --소수점 둘째자리까지 출력
     , ROUND(3.6475) AS ROUND       --숫자 명시하지 않으면 정수자리까지
     , ROUND(3.6475, 1) AS ROUND_1  --소수점 첫째자리까지 출력
     , ROUND(3.6475, 0) AS ROUND_0  --정수자리까지 출력    
     , ROUND(1234.5678, -1) AS ROUND_MINUS1 --십의자리까지 출력
     , ROUND(1234.5678, -2) AS ROUND_MINUS2 --백의자리까지 출력     
  FROM DUAL;

 

 

 

 

- 버림 함수 (TRUNC)

: 특정 위치에서 숫자 버릴 때 쓰는 함수로 버림 위치 지정 안할 경우 소수점 첫 번째 자리에서 버림.

TRUNC([숫자],		  --필수
	  [버림 위치])  --선택
SELECT TRUNC(1234.5678) AS TRUNC        --숫자 명시하지 않으면 소수점 버림
     , TRUNC(1234.5678, 0) AS TRUNC_0   --소수점 버림
     , TRUNC(1234.5678, 1) AS TRUNC_1   --소수점 한자리까지 보고 나머지 버림
     , TRUNC(1234.5678, 2) AS TRUNC_2   --소수점 한자리까지 보고 나머지 버림
     , TRUNC(1234.5678, -1) AS TRUNC_MINUS1 --십의자리까지 보고 나머지 버림
     , TRUNC(1234.5678, -2) AS TRUNC_MINUS2 --백의자리까지 보고 나머지 버림    
  FROM DUAL;

=> 버림 위치까지는 버리지 않고 버림위치 그 다음 위치부터 없어진다.

 

 

 

 

- 지정한 숫자와 가까운 정수를 찾는 함수 (CEIL, FLOOR)

  • CEIL([숫자]) : 가장 가까운 큰 정수 찾는 함수 / 천장함수 / 정수올림
  • FLOOR([숫자]) : 가장 가까운 작은 정수 찾는 함 / 바닥함수 / 정수내림
SELECT CEIL(3.14)
     , FLOOR(3.14)
     , CEIL(-3.14)
     , FLOOR(-3.14)
  FROM DUAL;

 

 

 

 

- 숫자를 나눈 나머지 값을 구하는 함수 (MOD)

: 특정 숫자를 나누고 그 나머지를 출력하는 함수 

MOD([나눗셈 될 숫자], [나눌 숫자])
SELECT MOD(15, 6)   
     , MOD(10, 2)
     , MOD(11, 2)
  FROM DUAL;

 

 

 

 

 

 

 

* 날짜 함수

<DATE형 데이터의 연산> 

 

 

- 현재 날짜와 시간 출력 함수 (SYSDATE)

SELECT SYSDATE AS NOW       --오늘날짜
     , SYSDATE + 5          --5일 추가
  FROM DUAL;

 

 

- 몇 개월 이후 날짜 출력 함수 ( ADD_MONTHS )

: 특정 날짜 데이터에 입력한 개월 수만큼의 이후 날짜를 출력

ADD_MONTHS([날짜 데이터], [더할 개월 수(정수)])
SELECT SYSDATE
     , ADD_MONTHS(SYSDATE, 5)
  FROM DUAL;

 

<예제>

--입사 10주년, 42주년이 되는 사원들 데이터 출력하기

SELECT 10 * 12 FROM DUAL; --10년 x 12개월

SELECT EMPNO, ENAME, HIREDATE
     , ADD_MONTHS(HIREDATE, 120) AS WORK10YEAR
  FROM EMP;   

SELECT 42 * 12 FROM DUAL;   --42년 X 12개월

SELECT EMPNO, ENAME, HIREDATE
     , ADD_MONTHS(HIREDATE, 504) AS WORK42YEAR
  FROM EMP;  

--입사 42주년 미만이 되는 사원들 데이터 출력하기

SELECT EMPNO, ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 504) AS WORK10YEAR
  FROM EMP
 WHERE ADD_MONTHS(HIREDATE, 504) > SYSDATE;

 

 

 

- 두 날짜 간의 개월수 차이  출력 함수 ( MONTHS_BETWEEN  )

: 두 날짜 데이터 간의 날짜 차이를 개월 수로 계산하여 출력

MONTHS_BETWEEN([날짜 데이터1], [날짜 데이터2])
SELECT EMPNO, ENAME, HIREDATE, SYSDATE
     , TRUNC (MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTHS1
     , TRUNC (MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) AS YEARS1
  FROM EMP;

 

 

- 돌아오는 요일  출력 함수 ( NEXT_DAY)

: 특정 날자를 기준으로 돌아오는 요일의 날짜 출력해주는 함수

SELECT SYSDATE,
       NEXT_DAY(SYSDATE, '월요일')
  FROM DUAL;

 

 

- 달의 마지막 날짜  출력 함수 ( LAST_DAY  )

: 특정 날짜가 속한 달의 마지막 날짜를 출력해주는 함수

SELECT SYSDATE,
       LAST_DAY(SYSDATE)
  FROM DUAL;

 

 

- 날짜 반올림, 버림  출력 함수 ( ROUND, TRUNC)

입력데이터 종류 사용 방식
숫자 데이터 ROUND(숫자 , 반올림 위치)
TRUNC(숫자, 버림 위치)
날짜 데이터 ROUND(날짜데이터, 반올림 기준 포맷)
TRUNC(날짜데이터,, 버림 기준 포맷)

 

  • - 기준 포맷 값
포맷 모델 기준 단위
CC, SCC 네 자리 연도의 끝 두자리를 기준으로 사용
(2016년은 2050이하이므로 반올림하면 2001년)
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y 날짜 데이터의 해당 연, 월, 일의 7월 1일을 기준
(2016년 7/1 경우 2017로 처리)
IYYY, IYY, IY, I ISO 8601에서 제정한 날짜 기준년도 포맷을 기준
Q 각 분기의 두 번째 달의 16일 기준
MONTH, MON, MM, RM 각 달의 16일 기준
WW 해당 연도의 몇 주(1-53번째 주)를 기준
IW ISO 8601에서 제정한 날짜 기준 해당 연도의 주(week)를 기준
W 해당 월의 주(1-5번째 주)를 기준
DDD, DD, J 해당 이르이 정오(12;00;00)를 기준
DAY, DY, D 한 주가 시작되는 날짜를 기준
HH, HH12, HH24 해당일의 시간을 기준
MI 해당일 시간의 분을 기준

 

  • ROUND 함수로 날짜 데이터 출력하기 
SELECT SYSDATE
     , ROUND(SYSDATE, 'YYYY') AS FORMAT_YYYY
     , ROUND(SYSDATE, 'Q') AS FORMAT_Q
     , ROUND(SYSDATE, 'HH') AS FORMAT_HH
     , TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')    --중요!! 시분초까지 출력
  FROM DUAL;

 

  • TRUNC 함수로 날짜 데이터 출력하기
  SELECT SYSDATE
     , TRUNC(SYSDATE, 'YYYY') AS FORMAT_YYYY
     , TRUNC(SYSDATE, 'Q') AS FORMAT_Q
     , TRUNC(SYSDATE, 'HH') AS FORMAT_HH
     , TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')   
  FROM DUAL;

 

 

 

 

* 자료형을 반환하는 형 변환 함수

- TO_CHAR 함수 ★★★★★

: 숫자, 날짜 데이터 -> 문자데이터로 변환

  • 날짜 형식

  • 예제
--날짜 형식 지정하여 출력하기
SELECT TO_CHAR(HIREDATE, 'YYYY-MM-DD HH24:MI:SS')
  FROM EMP
 WHERE EMPNO = 9999;
 
-- 월과 요일 다양한 형식으로 출력하기 
SELECT TO_CHAR(SYSDATE, 'DAY')
     , TO_CHAR(SYSDATE, 'MON')
     , TO_CHAR(SYSDATE, 'DD')
     , TO_CHAR(SYSDATE, 'DD')
     , TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = ENGLISH')
     , TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = JAPANESE')      
  FROM DUAL;
  
-- 시간 형식 지정하여 출력하기 
SELECT SYSDATE
     , TO_CHAR(SYSDATE, 'HH24:MI:SS')       --자주쓰이는 포맷형식
     , TO_CHAR(SYSDATE, 'HH24:MI:SS AM')
     , TO_CHAR(SYSDATE, 'HH24:MI:SS P.M.')     
  FROM DUAL;

-- 여러가지 숫자 형식을 사용하여 급여 출력
SELECT ENAME, SAL
     , TO_CHAR(SAL, '$999,999') AS SAL_$    -- 숫자 한자리 (빈자리 채우지 않음)
     , TO_CHAR(SAL, '999,999') AS SAL_1     -- ,천단위 구분 기호
     , TO_CHAR(SAL, '000,999,999.00') AS SAL_2 -- 0 : 빈자리를 0으로 채움
     , TO_CHAR(SAL, '000999999') AS SAL_2 -- 0 : 빈자리를 0으로 채움     
  FROM EMP;

 

 

 

- TO_NUMBER 함수

: 문자데이터 -> 숫자데이터로 변환

--문자 데이터와 숫자데이터 연산
SELECT 1300+'1500', '1300' + 1500
  FROM DUAL;
    
SELECT '1,300' + 1500   -- invalid number오류
  FROM DUAL;
  
-- 함수로 연산하여 출력  
SELECT TO_NUMBER('1,300', '999,999') + 1500 
  FROM DUAL;

 

 

 

- TO_DATE 함수 ★★★

: 문자데이터 -> 날짜데이터로 변환

--문자데이터를 날짜데이터로 변환
SELECT TO_DATE('20231127', 'YYYY-MM-DD') AS TODATEE1   --문자를 날짜형태로 
  FROM DUAL;
  
--1981년 12월 1일 이후 입사한 사원정보
SELECT *
  FROM EMP
 WHERE HIREDATE > TO_DATE('1981-12-01', 'YYYY-MM-DD');

 

 

 

 

 

 

* NULL 처리 함수

- NVL 함수 ★★★

: NULL이 아니면 그대로, NULL이면 지정한 값 반환

SELECT EMPNO, ENAME, NVL(COMM, 0), NVL(MGR, 0)
  FROM EMP;

- NVL2 함수

: NULL이 아닐때와 NULL일 때 각각 지정한 값 반환

--NVL2(COMM, A, B) 명시한 컬럼값이 널이 아니면 A, 널이면 B
SELECT EMPNO, ENAME, COMM
     , NVL2(COMM, 'O', 'X') AS COMM_OX
     , NVL2(COMM, SAL*12+COMM, SAL*12) AS ANNSAL
  FROM EMP;

 

 

 

 

 

* DECODE 함수와 CASE문

- DECODE 함수

: 기준이 되는 데이터를 먼저 지정한 후 해당 데이터 값에 따라 다른 결과 값을 내보내는 함수

DECODE(검사 대상이 될 열/데이터,
       조건 1,  조건 1 일치할 때 반환 값,
       조건 2,  조건 2 일치할 때 반환 값,
       ...
       조건 N,  조건 N 일치할 때 반환 값,
       아무 조건도 일치하지 않을 때 반환 값)

 

 

- CASE 문

: 특정 조건에 따라 반환할 데이터를 설정하며 키워드는 CASE - WHEN - THEN - ELSE - END 사용

CASE(검사 대상이 될 열 / 데이터,
     WHEN 조건 1 THEN 조건 1 참일 때 반환 값,
     WHEN 조건 2 THEN 조건 2 참일 때 반환 값,
     ...
     WHEN 조건 n THEN 조건 n 참일 때 반환 값,
     ELSE 어떤 조건도 일치하지 않을 때 반환 값
 END