힘내보자

함수(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