Database
[Oracle Database] 4. 함수에 대해 알아보자.
myeongju
2024. 7. 14. 18:37
반응형
오라클 함수의 종류
- 내장 함수(built-in function): 오라클에서 기본을 제공하고 있는 함수
- 사용자 정의 함수(user-defined functino): 사용자가 필요에 의해 직접 정리하는 함수
내장 함수
- 단일행 함수(single-row function): 한 행당 결과가 하나씩 나오는 함수
- 다중행 함수(multiple-row function): 여러 행이 하나의 행으로 결과가 반환되는 함수
단일행 함수
- 문자 함수
- 숫자 함수
- 날짜 함수
- 변환 함수
- 일반 함수
문자 함수
대, 소문자 바꿔주는 함수
- `UPPER(문자열)` : 문자열을 모두 대문자로 변환
- `LOWER(문자열)` : 문자열을 모두 소문자로 변환
- `INITCAP(문자열)` : 문자열 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환
select *
from emp
where upper(ename) = upper('scott');
select loc, initcap(loc)
from dept;
select initcap('hello, world')
from dual;
DUAL 테이블?
오라클의 최고 권한 관리자 계정인 SYS 소유의 테이블로 SCOTT 계정도 사용할 수 있는 더미(dummy) 테이블
임시 연산이나 함수의 단일 결과 값 확인 용도로 종종 사용
문자열 길이 함수
LENGTH(문자열): 문자열의 길이를 반환LEGNTHB(문자열): 문자열의 바이트 수를 반환
select length('Tree'), length('우리나라')
from dept;
select length('Tree'), length('우리나라')
from dual;
select lengthb('Tree'), length('우리나라')
from dual;
문자열 추출 함수
SUBSTR(문자열 데이터, 시작 위치, 추출길이)- 문자열의 시작 위치부터 추출 길이 만큼 추출
- 시작 위치가 음수? 마지막 위치부터 거슬러 올라간 위치에서 다시 오른쪽으로 시작
SUBSTR(문자열 데이터, 시작 위치)- 시작 위치부터 문자열 데이터 끝까지 추출
- 시작 위치가 음수? 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출
select substr ('hello, world', 3), substr('hello, world', 3, 5)
from dual;
select instr('Hello, oracle', '1'),
instr('Hello, oracle', '1', 5),
instr('Hello, oracle', '1', 2, 2)
from dual;
INSTR('HELLO,ORACLE','1') INSTR('HELLO,ORACLE','1',5) INSTR('HELLO,ORACLE','1',2,2)
------------------------- --------------------------- -----------------------------
0 0 0
REPLACE 함수
특정 문자열 데이터에 포함될 문자를 다른 문자로 대해주는 함수
기본 형식
REPLACE([문자열 데이터 또는 열 이름(필수)], [찾는 문자(필수)], [대체할 문자(선택)])
// 값은 동일하지만 차이점이 있음
select replace ('010-1234-5678', '-', ' ')
from dual;
select translate ('010-1234-5678', '-', ' ')
from dual;
select ename, job, replace('job', 'CLERK', '점원')
from emp;//translate의 사용값
col "new sal" format a20
select ename, job, sal, translate(sal, '01234566789', '영일이삼사오육칠팔구') "new sal"
from emp;
ENAME JOB SAL new sal
-------------------- ------------------ ---------- --------------------
SMITH CLERK 800 구영영
ALLEN SALESMAN 1600 일육영영
WARD SALESMAN 1250 일이오영
JONES MANAGER 2975 이팔오
MARTIN SALESMAN 1250 일이오영
BLAKE MANAGER 2850 이구오영
CLARK MANAGER 2450 이사오영
SCOTT ANALYST 3000 삼영영영
KING PRESIDENT 5000 오영영영
TURNER SALESMAN 1500 일오영영
ADAMS CLERK 1100 일일영영
JAMES CLERK 950 오영
FORD ANALYST 3000 삼영영영
MILLER CLERK 1300 일삼영영
14 rows selected.
데이터의 빈 공간 채우는 함수
LPAD: Left Padding(왼쪽 패딩)RPAD: Right Padding(오른쪽 패딩)
데이터 길이가 지정한 자릿수보다 작을 경우에 나머지 공간을 특정 문자로 채우는 함수이다.
기본 형식
LPAD([문자열 데이터 또는 열이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)])
RPAD([문자열 데이터 또는 열이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)])
숫자 함수
ROUND: 지정된 숫자의 특정 위치에서 반올림한 값을 반환TRUNC: 지정된 숫자의 특정 위치에서 버림한 값을 반환CEIL: 지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환FLOOR: 지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환MOD: 지정된 숫자를 나눈 나머지 값을 반환
select round(sysdate, 'MONTH'), round(sysdate, 'YEAR')
from dual;
ROUND(SY ROUND(SY
-------- --------
24/07/01 25/01/01
select trunc(sysdate, 'MONTH'), trunc(sysdate, 'YEAR')
from dual;
TRUNC(SY TRUNC(SY
-------- --------
24/07/01 24/01/01
날짜 함수
날짜 데이터를 다루는 함수
ADD_MONTHSMONTHS_BETWEEN: 두 날짜 간의 개월수
기본 형식
ADD_MONTHS([날짜 데이터(필수)], [더할 개월수(정수)(필수)]
MONTHS_BETWEEN([날짜 데이터(필수)], [날짜 데이터2(필수)])
변환 함수
형변환 함수
TO_CHAR: 숫자 or 날짜 → 문자TO_NUMBER: 문자 → 숫자TO_DATE: 문자 → 날짜
to_char 함수
SELECT sysdate, to_char(sysdate, 'YYYY"년" MM"월" DD"일" HH24:MI:SS') as new_date From dual;
SYSDATE NEW_DATE
-------- --------------------------------------------------------
24/07/08 2024년 07월 08일 17:44:20
SELECT sysdate, to_char(sysdate, 'YYYY-MM-DD DAY HH24:MI:SS AM, WW"주" DDD"년일" Q"분기"') as new_dateual
FROM dual;
SYSDATE NEW_DATEUAL
-------- --------------------------------------------------------------------------------------------------------------------------------
24/07/08 2024-07-08 월요일 17:46:56 오후, 28주 190년일 3분기
일반 함수
NULL 처리 함수
NVL: NULL이 아니면 그대로, NULL이면 지정NVL2: NULL이 아닐때와 NULL일때 각각 지정
기본 형식
NVL([NULL인지 여부를 검사할 데이터 또는 열], [앞의 데이터가 NULL일 경우 반환할 데이터])
NVL2([NULL인지 여부를 검사할 데이터 또는 열],
[앞 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식],
[앞 데이터가 NULL일 경우 반환할 데이터 또는 계산식])SELECT ename, job, comm, sal * 12 + nvl(comm, 0) as 연봉
FROM emp;
ENAME JOB COMM 연봉
-------------------- ------------------ ---------- ----------
SMITH CLERK 9600
ALLEN SALESMAN 300 19500
WARD SALESMAN 500 15500
JONES MANAGER 35700
MARTIN SALESMAN 1400 16400
BLAKE MANAGER 34200
CLARK MANAGER 29400
SCOTT ANALYST 36000
KING PRESIDENT 60000
TURNER SALESMAN 0 18000
ADAMS CLERK 13200
JAMES CLERK 11400
FORD ANALYST 36000
MILLER CLERK 15600
14 rows selected.
SQL> SELECT ename, job, comm, nvl2(comm, sal * 12 + comm, sal * 12) as 연봉
FROM emp;
ENAME JOB COMM 연봉
-------------------- ------------------ ---------- ----------
SMITH CLERK 9600
ALLEN SALESMAN 300 19500
WARD SALESMAN 500 15500
JONES MANAGER 35700
MARTIN SALESMAN 1400 16400
BLAKE MANAGER 34200
CLARK MANAGER 29400
SCOTT ANALYST 36000
KING PRESIDENT 60000
TURNER SALESMAN 0 18000
ADAMS CLERK 13200
JAMES CLERK 11400
FORD ANALYST 36000
MILLER CLERK 15600
14 rows selected.
상황에 따라 다른 데이터를 반환하는 함수
DECODE: 기준이 되는 데이터를 먼저 지정 후 데이터에 따라 다른 결과를 내보내는 함수CASE: DECODE와 기능은 같음.CASE는 = 데이터 외에 다양한 조건을 사용할 수 있다.
기본 형식
DECODE([검사 대상이 될 열 또는 데이터 연산이나, 함수의 결과],
[조건1], [데이터가 조건1과 일치할 때 반환할 결과],
[위 조건1과 일치한 경우가 없을 때 반환할 결과])
CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
WHEN [조건1] THEN [조건1의 결과 값이 TRUE일 때, 반환할 결과]
ELSE [위 조건1과 일치하는 경구가 없을 때 반환할 결과]
END
decode와 case
Q) emp 테이블에서 업무가 CLERK인 사원의 급여는 20%인상하고, SALESMANE인 사원의 급여는 10% , MANAGER 인 사원은 5%인상하세요. 나머지 업무의 사원은 급여를 동결하는 쿼리를 작성하세요.
SELECT ename, job, sal,
decode(job,
'CLERK', sal * 1.2,
'SALESMAN', sal * 1.1,
'MANAGER', sal *1.05,
sal) as inc_sal
from emp;
ENAME JOB SAL INC_SAL
-------------------- ------------------ ---------- ----------
SMITH CLERK 800 960
ALLEN SALESMAN 1600 1760
WARD SALESMAN 1250 1375
JONES MANAGER 2975 3123.75
MARTIN SALESMAN 1250 1375
BLAKE MANAGER 2850 2992.5
CLARK MANAGER 2450 2572.5
SCOTT ANALYST 3000 3000
KING PRESIDENT 5000 5000
TURNER SALESMAN 1500 1650
ADAMS CLERK 1100 1320
JAMES CLERK 950 1140
FORD ANALYST 3000 3000
MILLER CLERK 1300 1560
14 rows selected.
SELECT ename, job,
case job
when 'CLERK' then sal * 1.2
when 'SALESMAN' then sal * 1.1
when 'MANAGER' then sal * 1.05
end as inc_sal
FROM emp;
ENAME JOB INC_SAL
-------------------- ------------------ ----------
SMITH CLERK 960
ALLEN SALESMAN 1760
WARD SALESMAN 1375
JONES MANAGER 3123.75
MARTIN SALESMAN 1375
BLAKE MANAGER 2992.5
CLARK MANAGER 2572.5
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN 1650
ADAMS CLERK 1320
JAMES CLERK 1140
FORD ANALYST
MILLER CLERK 1560
14 rows selected.
다중행 함수
SUM: 지정한 데이터들의 합 반환COUNT: 데이터들의 개수 반환MAX: 최댓값 반환MIN: 최소값 반환AVG: 지정한 데이터들의 평균값 반환
반응형