함수(FUNCTION)
함수(FUNCTION) 두 종류
A.단일 행 함수
B.그룹 함수
A.단일 행 함수
1.문자 관련 함수 : LENGTH, INSTR,LPAD,LTRIM,TRIM,SUBSTR, CONCAT,REPLACE
2.숫자 관련 함수 : ABS,MOD,ROUND,CEIL,FLOOR/TRUNC
3.날짜 관련 함수 : MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY,LAST_DAT,EXTRACT
4.형변환 함수 : TO_CHAR, TO_DATE, TO_NUMBER
5.null처리 함수 : NVL, NVL2, NULLIF
6.선택 함수 : DECODE, CASE WHEN THEN
B.그룹함수
SUM, AVG, MIN/MAX, COUNT
---------------------------------------------------------------------------
---------------------------------------------------------------------------
1.단일 행 함수
n개의 데이터를 넣으면 n개가 나오는 것
n개의 값을 읽어서 n개의 결과 리턴
2.그룹 함수
n개의 데이터를 넣으면 1개 나오는 것
n개의 값을 읽어 1개의 결과 리턴
Q. SELECT절에 단일 행 함수와 그룹 함수를 함께 사용할 수 있다/없다?
A. 없다
WHY?
result set의 결과는 테이블이었다
단일행 쓰면 여러 결과가 나오는데 그룹은 하나의 테이블이 나온다
같이 쓰면 테이블 하나가 제대로 안나오게 됨
-- SELECT절에 단일 행 함수와 그룹함수를 함께 사용할 수 없다
결과 행의 개수가 다르기 때문
함수를 사용할 수 있는 위치
SELECT 절
WHERE 절
GROUP BY 절
HAVING 절
ORDER BY 절
( 사실상 프론트빼고 다 가능)
<SELECT문 실행순서 및 사용형식>
1. FROM 테이블명 - 조회 대상 컬럼이 있는 테이블명 기술
2. WHERE 컬럼명 연산자 조건 - 행을 선택하는 조건을 기술
3. GROUP BY 컬럼명 } 계산식 - 그룹묶을 컬럼명, 계산식 기술
4. HAVING 그룹함수 연산자 비교값 - 그룹묶은 값들을 그룹함수로 계산후 선택을 위한 조건기술
5. SELECT * | [DISTINCT] 컬럼명, 계산식 [AS] 별칭
6. ORDER BY 컬럼명 | 별칭 | 컬럼순서 [ASC] | DESC
*/
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 1.단일 행 함수
-- 1)문자 관련 함수
-- LENGTH / LENGTHB
-- 인자 : 문자,숫자,특수기호, 컬럼명
-- B = BYTE
-- LENGTH : 길이
-- LENGTHB : 글자의 바이트 사이즈 반환
-- 한글 : 3 byte
-- 영어, 숫자, 특수기호 : 1 byte
SELECT LENGTH('AAAB') FROM DUAL; -- 4
SELECT LENGTHB('AAAB') FROM DUAL; -- 4
SELECT LENGTH('컴퓨터') FROM DUAL; -- 3
SELECT LENGTHB('컴퓨터') FROM DUAL; -- 9
SELECT LENGTH(EMAIL), LENGTH(EMAIL) FROM EMPLOYEE;
-- 리터럴값도 가능하지만 컬럼도 가능
SELECT LENGTH(EMP_NAME), LENGTH(EMAIL) FROM EMPLOYEE; -- 3 15
-- 이름, 이메일 -> 길이, 바이트 사이즈
SELECT LENGTHB(EMP_NAME), LENGTHB(EMAIL) FROM EMPLOYEE; -- 9 15
SELECT EMP_NAME, LENGTHB(EMP_NAME), LENGTHB(EMP_NAME) FROM EMPLOYEE; -- 길이B 9
SELECT EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME),
EMAIL, LENGTH(EMAIL), LENGTHB(EMAIL)
FROM EMPLOYEE;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- INSTR
-- 해당 문자열이 처음 나온 위치 인덱스번호 반환(ZERO-INDEX X)
-- INSTR(’문자열’or컬럼명, ‘찾아낼 문자‘, 시작 index, 2번째인자인 문자가 몇번째 나오는 문자인지)
--1번째 인자 : 찾을 문자열 or 컬럼명
--2번째 인자 : 첫번째 중에 찾고 싶은 문자
--3번째 인자 : 몇번째부터 읽기 시작할 것이냐. 안적으면 1부터 시작
--4번째 인자 : ' '의 문자가 세번째인자로부터 세기 시작해서 "몇번째로 나오는 문자인가”
-- 1,2번째 인자는 리터럴( ‘ ‘ )로 감싼다
-- 없는 문자열은 0반환 // 만약, 제로인덱스였다면 -1반환
-- AABAACAABBAA
SELECT INSTR('AABAACAABBAA','A') FROM DUAL; -- 1
SELECT INSTR('AABAACAABBAA','A',3) FROM DUAL; -- 4
SELECT INSTR('AABAACAABBAA','A',1,3) FROM DUAL; -- 4
SELECT INSTR('AABAACAABBAA','A',1,2) FROM DUAL; -- 2
SELECT INSTR('AABAACAABBAA','B') FROM DUAL; -- 3
SELECT INSTR('AABAACAABBAA','B',1,1) FROM DUAL; -- 3
SELECT INSTR('AABAACAABBAA','B',1,2) FROM DUAL; -- 9
SELECT INSTR('AABAACAABBAA','B',9,2) FROM DUAL; -- 10
-- 코딩해설 : 7번쨰부터 읽기 시작해서 B가 처음 나올 때까지 읽어서, 처음 나오는 B의 위치 반환해
SELECT INSTR('AABAACAABBAA','B',7,1) FROM DUAL; -- 9
-- 마이너스(-): 거꾸로 읽기
SELECT INSTR('AABAACAABBAA','B',-1) FROM DUAL; -- 10
SELECT INSTR('AABAACAABBAA','B',-1,1) FROM DUAL; -- 10
SELECT INSTR('AABAACAABBAA','B',-1,2) FROM DUAL; -- 9
SELECT INSTR('AABAACAABBAA','B',-1,3) FROM DUAL; -- 3
SELECT INSTR('AABAACAABBAA','B',-1,-1) FROM DUAL; -- ERROR : 4번째 인자는 양수만 가능
SELECT INSTR('AABAACAABBAA','B',-1,0) FROM DUAL; -- ERROR : 4번째 인자는 양수만 가능
-- 역순 뒤에서 찍은 기준점 기준으로 왼쪽으로 세고 인덱스번호는 맨왼쪽에서 오른쪽으로
-- 1에서부터 두번째 있는 'B'를 찾아라
SELECT INSTR('AABAACAABBAA','B',1,2) FROM DUAL; -- 9
SELECT INSTR('AABAACAABBAA','B',1,2) FROM DUAL; -- 9
-- EMPLOYEE테이블에서 이메일의 @위치 반환
SELECT EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___@%';
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- LPAD / RPAD
-- 왼쪽 공백 / 오른쪽 공백
-- 오른쪽 정렬 / 왼쪽 정렬
-- LPAD(컬럼명, 문자 총 길이,공백 대신 대체할 문자)
-- 주어진 값에 임의의 문자열을 왼쪽/오른쪽에 덧붙여 길이 n개의 문자열 반환
-- 전체 길이에서 남은 공간에 공백으로 채움
-- sun_di@kh.or.kr// 세팅 :20
SELECT LPAD('sun_di@kh.or.kr',20) FROM DUAL;
-- sun_di@kh.or.kr //
SELECT RPAD('sun_di@kh.or.kr',20) FROM DUAL;
-- 값보다 지정한 총길이가 짧으면 뒤부터 짤림
SELECT LPAD('sun_di@kh.or.kr',5) FROM DUAL;
-- 공백대신 대체할 문자 삽입
SELECT LPAD('sun_di@kh.or.kr',20,'^') FROM DUAL;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- LTRIM / RTRIM
-- 좌/우에서부터 지정한 문자를 제거한 나머지 반환
-- 문자를 지정하지 않을 경우, 공백 제거
-- LTRIM : 왼쪽부터 순서대로 제거
-- RTRIM : 오른쪽부터 순서대로 제거
-- 자바 STRING클래스에서 TRIM은 공백제거 메소드
-- KH//
SELECT LTRIM(' KH ') FROM DUAL; -- KH //
-- KH//
SELECT RTRIM(' KH ')FROM DUAL; -- KH//
-- 000123456
-- 0만 제거하고 나머지를 반환
SELECT LTRIM('000123456',0) FROM DUAL;
-- 0001230456
-- 중간에 껴있는 해당 문자는 제거X
SELECT LTRIM('000123456',0) FROM DUAL;
-- ACABACCKH // KH
-- 'ABC' 묶여진 문자 하나로 보는게 아니라, A 또는 B 또는 C가 있으면 다 지우는 것
-- 'ABC' 안에 순서 상관X
SELECT LTRIM('ACABACCKH','ABC') FROM DUAL; -- KH
-- KH // KH
-- 오른쪽부터 공백제거
SELECT LTRIM('KH ') FROM DUAL; -- KH //
SELECT RTRIM('KH ') FROM DUAL; -- KH//
-- 01230456000 // 01230456
SELECT RTRIM('01230456000',0) FROM DUAL;
-- KHACABACC // KH
-- 오른쪽부터 지우기 시작
SELECT RTRIM('KHACABACC','ABC') FROM DUAL; -- KH
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- TRIM
-- 앞/뒤/양쪽에서 지정한 문자를 제거한 나머지 반환
-- TRIM(제거방향 '제거할 문자 1개' FROM '찾을 문자열OR컬럼명')
-- LEADING : 앞부터 제거
-- TRAILING : 뒤부터 제거
-- BOTH : 양쪽에서부터 제거
-- KH // KH // A는 별칭
SELECT TRIM(' KH ') FROM DUAL; -- KH
SELECT TRIM(BOTH FROM ' KH ') FROM DUAL; -- KH//
SELECT TRIM(BOTH ' ' FROM ' KH ') FROM DUAL; -- KH//
SELECT TRIM(BOTH FROM ' KH ') FROM DUAL; -- KH// 2번인자 제거할 문자 : 생략가능
SELECT TRIM(LEADING 'K' FROM ' KH ') FROM DUAL; -- KH // 공백에서 멈춰 K안지워짐
SELECT TRIM(TRAILING 'K' FROM ' KH ') FROM DUAL; -- KH //공백에서 멈춰 K안지워짐
SELECT TRIM(TRAILING ' ' FROM ' KH ') FROM DUAL; -- KH//
-- ZZZKHZZZ // KH
SELECT TRIM(BOTH 'Z' FROM 'ZZZKHZZZ') FROM DUAL; -- KH
-- 123KH123123 // error : trim set should have only one character
-- TRIM은 제거할 문자 1개만 받는다
-- ZZZKHZZZ // KHZZZ
-- LEADING 앞부터 제거
SELECT TRIM(LEADING 'Z' FROM 'ZZZKHZZZ') FROM DUAL; -- KHZZZ
-- ZZZKHZZZZ // ZZZKH
-- TRAILING 뒤부터 제거
SELECT TRIM(TRAILING 'Z' FROM 'ZZZKHZZZZ') FROM DUAL; -- ZZZKH
-- ZZZKHZZZZ //KH
-- BOTH 양쪽에서부터 제거
SELECT TRIM(BOTH 'Z' FROM 'ZZZKHZZZ') FROM DUAL; -- KH
--LTRIM/RTRIM처럼 인자 추가 형식이 아닌
--코드 구조를 바꿔서 써야함
--제거를 지우겠다 어디서부터? 컬럼명부터
------------------------------------------------------------------------------
------------------------------------------------------------------------------
--SUBSTR
-- String.substring()
--SUBSTR(컬럼명, 시작위치 인덱스, 시작위치부터 값을 반환할 길이)
-- HELLOMYGOODFRIENDS // YGOODFRIENDS
-- 7번째서부터 끝까지 반환
SELECT SUBSTR('HELLOMYGOODFRIENDS',7) FROM DUAL;
-- HELLOMYGOODFRINEDS // OM
-- 5번째 문자부터 2개만 반환
SELECT SUBSTR('HELLOMYGOODFRIENDS',5,2) FROM DUAL;
-- HELLOMYGOODFRINEDS // (null)
-- 5번쨰 문자부터 0개 반환 // 0개 반환이 불가능하기에 NULL 출력됨
SELECT SUBSTR('HELLOMYGOODFRIENDS',5,0) FROM DUAL; -- --(NULL)
-- HELLOMYGOODFRINEDS // HELLOM
SELECT SUBSTR('HELLOMYGOODFRIENDS',0,5) FROM DUAL; -- HELLO // 0부터 시작해도 1부터 적용
SELECT SUBSTR('HELLOMYGOODFRIENDS',1,5) FROM DUAL; -- HELLO // 0부터 시작해도 1부터 적용
-- HELLOMYGOODFRINEDS // DFR
-- 뒤에서 8번째(D)부터 오른쪽으로 3개의 문자 반환
-- 위에서 마이너스(-)였으면 기준점이 뒤에서부터 세고 왼쪽방향으로 카운팅했지만 얘는 오른쪽으로 카운팅
SELECT SUBSTR('HELLOMYGOODFRIENDS',-8,3) FROM DUAL; -- DFR
-- HELLOMYGOODFRINEDS // OO
SELECT SUBSTR('HELLOMYGOODFRIENDS',-10,2) FROM DUAL; -- OO
SELECT SUBSTR('HELLOMYGOODFRIENDS',-10,-2) FROM DUAL; -- 3RD 인자 몇번째문자인지가 -이면 NULL값
-- EMPLOYEE 테이블에서 이름, 이메일, 이메일의 아이디 조회
-- (이메일의 @ 도메인말고 아이디까지만)
SELECT EMP_NAME, EMAIL, SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1)
FROM EMPLOYEE;
-- 문제풀이 사고과정
-- 1.이메일 아이디의 규칙은 항상 @이 앞에 있음
-- 2. @ 위치 파악
-- 3. 1~@앞까지 찾기
-- EMPLOYEE 테이블에서 이름, 이메일, 이메일의 아이디 조회
-- (이메일의 @ 도메인말고 아이디까지만)
SELECT EMP_NAME, EMAIL,SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1)
FROM EMPLOYEE;
-- 주민등록번호를 이용하여 이름과 성별을 나타내는 부분 조회
-- EMPLOYEE 테이블에서 남자만 조회(사원명, '남')
-- EMPLOYEE 테이블에서 여자만 조회(사원명, '여')
SELECT EMP_NAME 사원명, CASE WHEN SUBSTR(EMP_NO,8,1)=1 THEN '남'
WHEN SUBSTR(EMP_NO,8,1)=2 THEN '여'
END 성별
FROM EMPLOYEE;
SELECT EMP_NAME 사원명, DECODE(SUBSTR(EMP_NO,8,1),1,'남',2,'여') 성별
FROM EMPLOYEE;
-- EMPLOYEE테이블에서 직원들의 주민번호를 이용하여 사원명, 생년, 생월, 생일 조회
SELECT EMP_NAME 사원명, SUBSTR(EMP_NO,1,2) 생년, SUBSTR(EMP_NO,3,2) 생월, SUBSTR(EMP_NO,5,2) 생일
FROM EMPLOYEE;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- LOWER / UPPER / INITCAP
-- INITCAP :각 단어의 첫글자만 대문자
-- 예제 Welcome To my World
-- welcome to my world
SELECT LOWER('Welcome To my World') FROM DUAL;
-- WELCOME TO MY WORLD
SELECT UPPER('Welcome To my World') FROM DUAL;
-- Welcome To My World
SELECT INITCAP('Welcome To my World') FROM DUAL;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- CONCAT
-- 문자열 이어붙이기
-- 가나다라123
SELECT CONCAT('가나다라','123') FROM DUAL;
SELECT CONCAT(DEPT_CODE, '-333') FROM EMPLOYEE; -- D9-333
SELECT CONCAT(JOB_CODE, ' BY S') FROM JOB; -- J1 BY S
SELECT CONCAT(DEPT_CODE, JOB_CODE) FROM EMPLOYEE; -- D9J1
-- 연결연산자 사용 : 가나다라123
-- 연결연산자 : ||
SELECT '가나다라' || '123' FROM DUAL; -- 가나다라123
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- REPLACE
-- REPLACE(컬럼명,바꿔질 단어, 바꿀 단어)
-- 값 교체
-- 서울시 강남구 역삼동 // 서울시 강남구 삼성동
SELECT REPLACE('서울시 강남구 역삼동','역삼동','삼성동') FROM DUAL;
-- 강사님은 오라클을 수업중이다 // 선생님은 오라클을 수업중이다
-- 띄어쓰기 상관없이 적용가능
SELECT REPLACE('강사님은 오라클을 수업중이다','강사님','선생님') FROM DUAL;
-- 케이크 달다. 케이크 싫은사람? // 공부 달다. 공부 싫은사람?
-- 여러개 들어있어도 복수로 교체가능. 일치만 하면 전부 교체함
SELECT REPLACE('케이크 달다. 케이크 싫은사람?','케이크','단 거') FROM DUAL;
-- EMPLOYEE테이블에서 사원명, 주민번호 조회
-- 주민번호의 뒷자리는 *로 바꿔서 조회(EX.001122-*******)
-- REPLACE+SUBSTR
-- REPLACE+SUBSTR+INSTR
/*
REPLACE(EMP_NO,SUBSTR(EMP_NO,8,14/2)
SELECT REPLACE(EMP_NO,SUBSTR(EMP_NO,8,14/2)) FROM EMPLOYEE;
SELECT REPLACE(EMP_NO,SUBSTR(EMP_NO,8,LENGTH(EMP_NO)/2)) FROM EMPLOYEE;
SELECT LENGTH(EMP_NO)/2 FROM EMPLOYEE;
*/
-- RPAD+SUBSTR
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO,1,7),14,'*******')
FROM EMPLOYEE;
-- 방법1
-- REPLACE+SUBSTR 끝수 지정 & 미지정
-- 방법2
-- RPAD+SUBSTR
SELECT EMP_NAME, REPLACE(EMP_NO,SUBSTR(EMP_NO,8,7),'*******') 주민번호
FROM EMPLOYEE;
-- RPAD+SUBSTR+INSTR
SELECT EMP_NAME, REPLACE(EMP_NO,SUBSTR(EMP_NO,8,7),'*******')
FROM EMPLOYEE;
-- RPAD+SUBSTR+INSTR+LENGTH
SELECT EMP_NAME, REPLACE(EMP_NO,SUBSTR(EMP_NO,INSTR(EMP_NO,'-')+1,LENGTH(EMP_NO)/2),'*******')
FROM EMPLOYEE;
-- 방법3
-- REPLACE+SUBSTR+INSTR+LENGTH
-- 방법4
-- SUBSTR+연결연산자(||)
SELECT EMP_NAME, SUBSTR(EMP_NO, 1, 7) || '*******' 주민번호
FROM EMPLOYEE;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 2) 숫자 관련 함수
-- ABS
-- 절대값 반환
SELECT ABS(FLOOR(1.234)) FROM DUAL;
-- 10.9 10.9 10 10
-- 전부 양수 출력
SELECT ABS(-10.9), ABS(10.9), ABS(-10) FROM DUAL;
-- MOD
-- 모듈러
-- 나머지 구하기
-- MOD(나눠지는수 ,나누는 수)
-- 1 -1 1 -1 1.9
-- 내가 나누어지는 수에 대한 부호를 따라가게 됨
SELECT MOD(10,3), MOD(-10,3), MOD(10,-3), MOD(-10,-3), MOD(10.9,3) FROM DUAL;
-- ROUND
-- 몇번째 자리에서 반올림할지 소수점 자리수 지정가능
-- 123.456
-- 123 124 123 123.5 123.46 120 100
-- 소수점 자리지정 반올림
-- 소수점 -의 반올림
SELECT ROUND(123.456,0),ROUND(123.456), ROUND(123.456,1),ROUND(123.456,2), ROUND(123.456,-1) FROM DUAL;
SELECT ROUND(123.456,-1), ROUND(123.456,-2) FROM DUAL;
-- -11
-- 마이너스(-)의 반올림은?
-- 마이너스는 숫자가 클수록 작은 수 ex) -11이 -10보다 작은 수
-- -10.61에서 .61이 5이상이라 반올림이 되는데 -11로
-- CEIL
-- 124
-- 무조건 1의 자리에서 올림하게 되어있음
-- 자릿수 지정 불가. 다른 인자값 집어넣으면 에러
SELECT CEIL(123.456) FROM DUAL;
-- FLOOR / TRUNC
-- FLOOR : 수학적 내림(배웠던 수학의 내림) // 자릿수 지정 불가
-- TRUNC : 버림. 절삭. 몇번째 자리까지 버리겠다 // 자릿수 지정가능
SELECT FLOOR(123.456), TRUNC(123.456) FROM DUAL;
SELECT FLOOR(123.456), TRUNC(123.456,1) FROM DUAL;
-- 123 123 123.7
-- TRUNC : 몇번째 자리까지 버리겠다 자릿수 지정가능
-- 파이썬에서 FLOOR가 -쪽으로 내리고 TRUNC가 0쪽으로 수렴하는 것과 같은 개념
-- math.trunc(-3.14) #결과는 -3
-- math.floor(-3.14) #결과는 -4
