05. Function : 함수

5. Function : 함수
함수명(인수1, 인수2, ...) : 함수 특징에 따라 인수가 없기도, 1개 이상이기도 함
ex. ADDER(10,20) = 사용자가 인수를 넣으면 함수가 명령을 실행. 10+20=30


[숫자처리 함수]
① ABS(n) : n의 절대값

SELECT ABS(-10) FROM DUAL; -> 도출값 : 10

* DUAL 의사테이블 (=임의테이블) : 테이블에 적용하지 않고 값을 보고 싶을 때 이용

② FLOOR(n) : 소수점 아래를 버림

SELECT FLOOR(34.5678) FROM DUAL; -> 도출값 : 34
SELECT FLOOR(-34.5678) FROM DUAL; -> 도출값 : 35

③ ROUND(n, [m]) : 반올림한 결과값

SELECT ROUND(34.5678) FROM DUAL; -> 도출값 : 35
SELECT ROUND(34.5678, 2) FROM DUAL; -> 도출값 : 34.57
SELECT ROUND(34.5678, -1) FROM DUAL;-> 도출값 : 30

④ TRUNC(n,m) : n값을 절삭

SELECT TRUNC(34.5678) FROM DUAL; -> 도출값 : 34
SELECT TRUNC(34.5678, 2) FROM DUAL; -> 도출값 : 34.56
SELECT TRUNC(34.5678, -2) FROM DUAL; -> 도출값 : 30

[FLOOR VS TRUNC]
- 음수일 때, FLOOR는 작은 정수를 반환, TRUNC는 절삭

SELECT FLOOR(-45.67), TRUNC(-45.67) FROM DUAL; -> 도출값 : FLOOR -46, TRUNC -45

⑤ MOD(m,n) : 나눗셈 연산 후 나머지 값

SELECT MOD (27, 2), MOD (27, 5), MOD (27, 7)  FROM DUAL; -> 도출값 : 1, 2, 6

[예제] 홀수인 값을검색

SELECT *
FROM 테이블명
WHERE MOD (칼럼명) != 0 ;

* WHERE MOD (칼럼명)=0 은 짝수 출력

⑥ LOG : 자연로그 (밑수 : 2 or e) * e는 무리수
* 무리수 : 분수로 나타낼 수 없는, 순환하지 않는 무한소수 ex 파이, 루트2, 루트3...

밑수가 2일때

SELECT LOG (2,8) FROM DUAL;

* 8에 대한 지수값 3을 출력. 8=2^3

밑수가 e일때

SELECT EXP(1) FROM DUAL; -- e = 2.71828 = EXP(1)
SELECT LOG (EXP(1) , 8) FROM DUAL;

* 8에 대한 지수값 2.07944를 출력. 8=e^2.07944...

⑦ EXP함수 : 지수함수

SELECT EXP(1) FROM DUAL; -- e = 2.71828 = e^1
SELECT EXP(2.07944) FROM DUAL; -- 7.9999... = 8의 근사치 = e^2.07944

* 밑수 e일 때, LOG와 EXP함수는 역함수 관계
* 로그함수 : 지수값 반환, 지수함수 : 로그값 반환

⑧ POWER : 거듭제곱

SELECT POWER(2.71828, 2.07944) FROM DUAL; -- e^2.07944

⑨ SQRT : 괄호 안 숫자를 양의 제곱근 취하여 그 값을 반환
* 제곱근 : x^2=a일 때, x는 a의 제곱근

SELECT SQRT(49) FROM DUAL;

[문자처리 함수]
① UPPER(char) : 문자열을 모두 대문자로 변경

SELECT 'Welcome to Oracle', UPPER('Welcome to Oracle') FROM DUAL; -> 출력값 : WELCOME TO ORACLE

② LOWER(char) : 문자열을 모두 소문자로 변경

SELECT 'Welcome to Oracle', LOWER('Welcome to Oracle') FROM DUAL; -> 출력값 welcome to oracle

③ INITCAP(char) : 단어 시작만 대문자로 변경

SELECT 'WELCOME TO ORACLE', INITCAP('WELCOME TO ORACLE') FROM DUAL; -> 출력값 Welcome To Oracle

④ LENGTH(char) : 음절 길이 출력

SELECT LENGTH('Oracle'), LENGTH('오라클') FROM DUAL; -> 출력값 : oracle 6, 오라클 3
SELECT LENGTHB('Oracle'), LENGTHB('오라클') FROM DUAL;  -> 출력값 : oracle 6, 오라클 9

* LENGTHB(char) : Byte단위로 처리
* 한글은 2or3바이트

⑥ SUBSTR(char, m ,[n]) : 시작위치부터 선택 개수만큼의 문자를 추출

SELECT SUBSTR('Welcome to Oracle', 4, 3) FROM DUAL;
FROM DUAL; -> 출력값 com (왼쪽 기준) 

SELECT SUBSTR('Welcome to Oracle', -4, 3)
FROM DUAL; -> 출력값 acl (오른쪽 기준)

* SUBSTRB(char, m ,[n]) : Byte단위로 처리

[예제] 생년과 월 출력
SUBSTR(HIREDATE, 1, 2) 년도,
SUBSTR(HIREDATE, 4, 2) 달 FROM 테이블명;

⑦ TRIM (char1[,char2]) : 특정 문자(공백) 제거

SELECT TRIM(' Oracle ') FROM DUAL; -> 출력값 : Oracle
SELECT TRIM('o' FROM 'oracleclub') name FROM DUAL; -> 출력값 : racleclub

⑧ REPLACE(char1, str1, str2) : 문자열 치환 

SELECT REPLACE('홍길동', '홍', '김') FROM DUAL; -> 출력값 : 김길동

 

[날짜처리 함수]
① SYSDATE : 시스템기준 날짜와 시간

SELECT SYSDATE FROM DUAL; -- 2021-09-07 -> 21/09/07

* 최소 단위는 1초

② 일(DAY) 계산
SELECT SYSDATE-1 어제
SYSDATE 오늘
SYSDATE+1 내일
FROM DUAL;

③ ROUND(d[,F]) : 날짜 반올림

SELECT HIREDATE, ROUND(HIREDATE, 'MONTH') FROM EMP;

* F값이 생략되면 가장 가까운 날짜로 반올림

④ MONTHS_BETWEEN(a1, a2) 
 a1과 a2 사이의 달의 수를 NUMBER형으로 출력

SELECT ENAME, SYSDATE, HIREDATE,
ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE)) FROM EMP;

[형 변환 함수]
to_char() : 날짜형, 숫자형 -> 문자형 변환(format 이용) 
to_date() : 문자형, 숫자형 -> 날짜형 변환(format 이용)
to_number() : 문자형 -> 숫자형 변환(format 이용) 


① TO_CHAR : 날짜형, 숫자형 -> 문자형

SELECT 칼럼명, TO_CHAR(칼럼명, 'YYYY-MM-DD') FROM DUAL;

SELECT 칼럼명 TO_CHAR (칼럼명, 'YYYY/MM/DD DAY') FROM 테이블명;

SELECT 칼럼명 TO_CHAR (칼럼명, 'YY/MON/DD DY') FROM 테이블명;

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS') FROM DUAL;

* 숫자형 -> 문자형 

SELECT ENAME, SAL, TO_CHAR (SAL, 'L999,999') -- 화폐단위, 천단위 콤마 
FROM EMP;

② TO_DATE : 숫자형/문자형 -> 날짜형 

SELECT ENAME, HIREDATE FROM EMP
WHERE HIREDATE=TO_DATE(19810220,'YYYYMMDD');

SELECT TRUNC(SYSDATE-TO_DATE('2008/01/01', 'YYYY/MM/DD'))
FROM DUAL;

③ TO_NUMBER  : 문자형 -> 숫자형 

SELECT '20,000' - '10,000' FROM DUAL;  -- error 

SELECT TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '99,999')
FROM DUAL;  -- 10000

 

[Decode 함수]
encoding : 기계어 / decoding : 인간어 

SELECT ename, deptno FROM emp;

SELECT ename, deptno, 
DECODE(deptno, 10, '기획실', 20, '연구실', '기타') as "decoding"
FROM emp;