[DB] 함수(Function)_단일행, 그룹

✍함수(Function)

: 하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브 프로그램 호출하며 값을 전달하면 결과를 리턴하는 방식으로 사용

데이터가 모여지는 기반으로 데이터를 한 곳에 모은다.

 

단일행 함수(SINGLE ROW)

: 컬럼에 기록된 N개의 값을 읽어 N개의 결과를 반환
각 행마다 반복적으로 적용되어 입력 받은 행의 개수만큼 결과 반환

 

그룹 함수(SINGLE ROW)

: 컬럼에 기록된 N개의 값을 읽어 1개의 결과를 반환
특정 행들의 집합으로 그룹이 형성되어 적용됨 그룹 당 1개의 결과 반환

 

함수가 적용될 수 있는 위치
: SELECT절, WHERE절, ORDER BY절, GROUP BY절, HAVING절

---

단일행 함수

---

🚩 문자처리 함수

 

 

 

1.  LENGTH

주어진 컬럼 값/문자열의 길이(문자 개수) 반환

작성법

 LENGTH('문자열 | 컬럼명)

➡ 리턴 값: CHARACTER

예제 1. EMPLOYEE 테이블에서 이름, 이메일, 이메일 글자 수를 조회
SELECT EMP_NAME, EMAIL, LENGTH(EMAIL)
FROM EMPLOYEE;


예제 2. EMPLOYEE 테이블에서 이름, 이메일, 이메일 글자 수를 조회
단, 이메일 글자 수가 16자인 행만 조회
SELECT EMP_NAME,EMAIL, LENGTH(EMAIL)
FROM EMPLOYEE
WHERE LENGTH(EMAIL) = 16;

 

2.  INSTR

지정한 위치부터 지정한 순번째로 검색되는 문자의 시작 위치를 반환

작성법

 INSTR('문자열' | 컬럼명, '찾을문자', [찾을 위치 시작위치, [순번]])

예제 1. 4번 문자부터 검색해서 두 번째 나오는 B의 위치
SELECT INSTR('AABAACAABBAA', 'B', 4, 2) FROM DUAL; 

--10

 

3. SUBSTR

컬럼이나 문자열에서 지정한 위치부터 지정된 길이만큼 문자열을 잘라내서 반환
--> 잘라낼 길이 생략 시 끝까지 잘라냄

작성법

SUBSTR ('문자열' | 컬럼명, 잘라내기 시작할 위치[, 잘라낼 길이] )

예제 1. SHOWMETHEMONEY중에서 첫 번째 문자부터 검색해서 4번째 문자까지의 위치SELECT SUBSTR('SHOWMETHEMONEY', 1, 4) FROM DUAL; -- SHOW

 

4. TRIM ⭐

주어진 컬럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자를 제거 
--> (보통 양족 공백 제거에 많이 사용)

옵션 : LEADING(앞쪽), TRAILING(뒤쪽), BOTH(양쪽, 기본값)

 

작성법

TRIM([옵션] '문자열' | 컬럼명 [FROM '문자열' | 컬럼명])

TRIM( STRING )
TRIM( CHAR FROM STRING)
TRIM( LEADING | TRAILING | BOTH [CHAR] FROM STRING)

예제 1.
SELECT '   KH   ', TRIM('   KH   ') FROM DUAL; 
--   KH   |KH -- 양쪽 공백 제거

SELECT TRIM(BOTH '-' FROM '---KH---') FROM DUAL; --KH

 

5. LPAD|RPAD

주어진 컬럼이나 문자열에 임의의 문자열을 왼쪽 또는 오른쪽에 덧붙여 반환

작성법

LPAD|RPAD('문자열' | 컬럼명, 반환할 문자 길이[, 덧붙이려는 문자열])

예제 1. 주민등록번호 뒷자리 *로 바꾸기
SELECT EMP_NAME,
    RPAD(SUBSTR(EMP_NO, 1, 8), LENGTH(EMP_NO), '*')
FROM EMPLOYEE;

SELECT EMP_NAME,SUBSTR(EMP_NO, 1, 8) || '******'
FROM EMPLOYEE; -- 주민번호가 몇자리인지 정확하게 알때만 가능

 

5. LOWER | UPPER | INITCAP

컬럼의 문자 혹은 문자열을 소문자| 대문자 | 앞글자만 대문자로 변환하여 반환하는 함수

작성법

LOWER | UPPER | INITCAP('문자열' | 컬럼)

예제 1.주민등록번호 뒷자리 *로 바꾸기
SELECT 'Hello World', LOWER('Hello World'), UPPER('Hello World')
FROM DUAL;


SELECT INITCAP('hello world') FROM DUAL; --단어의 앞글자만 카멜표기법으로 바꿔줌

---

🚩⭐  숫자 처리 함수⭐ 

 

 

1. ABS(숫자|컬럼명)

ABSOLUTE
인자로 전달 받은 숫자의 절대값 반환

작성법

 LENGTH('문자열 | 컬럼명)

➡ 리턴 값: CHARACTER

예제 1. EMPLOYEE 테이블에서 이름, 이메일, 이메일 글자 수를 조회
SELECT EMP_NAME, EMAIL, LENGTH(EMAIL)
FROM EMPLOYEE;


예제 2. EMPLOYEE 테이블에서 이름, 이메일, 이메일 글자 수를 조회
단, 이메일 글자 수가 16자인 행만 조회
SELECT EMP_NAME,EMAIL, LENGTH(EMAIL)
FROM EMPLOYEE
WHERE LENGTH(EMAIL) = 16;

 

2. MOD

두 수를 나누어 나머지를 반환

작성법

 MOD(숫자| 컬럼명, 숫자 | 컬럼명)

SELECT ABS(10), ABS(-10) FROM DUAL;
-- 10 10

SELECT MOD(10.9, 3) FROM DUAL;
--1.9

 

3. ROUND

반올림

작성법

ROUND(숫자|컬럼명[, 소수점 위치])

예제 
SELECT ROUND(123.456) FROM DUAL;
-- 소수점 위치 미지정 = 소수점 첫째 자리에서 반올림 == 정수 부분까지 반올림
--123

SELECT ROUND(123.456 , 1) FROM DUAL; --123.5
SELECT ROUND(123.456, 2) FROM DUAL; --123.46

CEIL(숫자|컬럼명): 올림
FLOOR(숫자|컬럼명): 내림
-- 소수점 위치 지정 불가

 

 

4. TRUNC

인자로 전달 받은 숫자 혹은 컬럼에서 지정한 위치부터의 자리의 수를 버리고(절삭) 반환

작성법

 TRUNC(숫자|컬럼[, 위치])

내림, 버림의 차이 -> 음수일 때 확인 가능
SELECT FLOOR(-123.5), TRUNC(-123.5) FROM DUAL;

 -124 -123

---

🚩 날짜 처리 함수

 

 

1. SYSDATE

시스템에 저장되어있는 날짜(년, 월, 일, 시, 분, 초)를 반환하는 함수(현재시간)

SELECT ABS(10), ABS(-10) FROM DUAL;
-- 10 10

SELECT MOD(10.9, 3) FROM DUAL;
--1.9

SYSTIMESTAMP : SYSDATE + 밀리세컨드

 

2. MONTHS_BETWEEN

두 날짜의 개월 수 차이를 반환
날짜 두 개를 전달받아 개월 수 차이를 숫자 데이터형으로 반환

작성법

 MONTHS_BETWEEN(날짜, 날짜)

SELECT ROUND( MONTHS_BETWEEN('22/01/01', SYSDATE), 1) || '개월' AS 결과
FROM DUAL;
--3.4개월

 

3. ADD_MONTHS

날짜에 더해진 수 만큼 개월을 추가하여 반환
인자로 전달받은 날짜에 인자로 받은 숫자만큼 개월 수를 더하여 특정 날짜 반환

작성법

ADD_MONTHS(날짜, 숫자)

예제 
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;

 

4. LAST_DAY

인자로 전달받은 날짜가 속한 달의 마지막 날짜 반환

작성법

LAST_DAY(날짜)

SELECT LAST_DAY(SYSDATE) FROM DUAL;

NEXT_DAY : 인자로 전달받은 날짜에 인자로 받은 요일이 가장 가까운 날짜 반

 

 

5. EXTRACT

날짜 데이터(DATE)에서 년, 월, 일 정보를 추출하는 함수

작성법

EXTRACT(YEAR | MONTH | DAY FROM 날짜)

---

🚩 형 변환 함수

CHAR(문자열), NUMBER(숫자), DATE(시간)끼리의 형변환

 

1. TO_CHAR

날짜 혹은 숫자형 데이터를 문자형 데이터로 변환하여 반환

작성법

TO_CHAR(숫자 [, 포맷]) : 숫자 데이터를 지정된 포맷의 문자열로 변경
TO_CHAR(날짜 [, 포맷]) : 날찌 데이터를 지정된 포맷의 문자열로 변경

포맷 형식

AM|PM : 오전/오후 중 알맞은 것을 표시
HH24: 시간(24시간)
HH : 시간(12시간)
MI : 분
SS : 초

SELECT TO_CHAR(1234, '99999') FROM DUAL;
--'9' : 하나당 0~9사이 숫자 한칸, 숫자 없으면 빈칸, 오른쪽 정렬

SELECT TO_CHAR(1234, '00000') FROM DUAL;
--'9' : 하나당 0~9사이 숫자 한칸, 숫자 없으면 0, 오른쪽 정렬

SELECT TO_CHAR(1000000, 'L9999999') FROM DUAL;
-- 'L' : 현재 시스템상에 설정된 나라의 화폐 기호
--> 화폐 기호를 바꾸는 방법 : 1. 시스템의 설정 나라 변경, 2. L대신 화폐 기호를 직접작성

SELECT TO_CHAR(1000000, '$9999999') FROM DUAL;
-- $로 직접 지정해줌

SELECT TO_CHAR(1000000, 'L9,999,999') FROM DUAL;
-- 자릿수 구분 콤마(,) 사용 가능

SELECT TO_CHAR(1000000, 'L9999') FROM DUAL;
--(주의사항) 포맷에 지정된 칸 수가 숫자보다 적을경우 '#'으로 추정된다 .

 

2. TO_DATE

날짜 형태의 문자열로 날짜 데이터(DATE)로 변환

작성법

TO_DATE('문자열' : 컬럼 [, 포맷] : 문자열을 날짜로 변환
TO_DATE('숫자' : 컬럼 [, 포맷] : 숫자를 날짜로 변환

예제1. 여러 날짜 표기 방법 다 가능
SELECT '20210915143720', TO_DATE('20210915143720') FROM DUAL;
SELECT '2021-09-15', TO_DATE('2021-09-15') FROM DUAL;
SELECT '2021/09/15', TO_DATE('2021/09/15') FROM DUAL;
SELECT '2021.09.15', TO_DATE('2021.09.15') FROM DUAL;
--> 기본적으로 많이 사용되는 날짜 표기 문자열은 자동으로 DATE 타입으로 변환된다. 

예제 2. 연도 표기 
SELECT TO_DATE('2021년 09월 15일','YYYY"년" MM"월" DD"일"')
FROM DUAL;

예제 3. 연도 표기 포맷 R, Y
SELECT TO_DATE('990915', 'YYMMDD') FROM DUAL; --> 2099-09-15 / YY의 문제점
SELECT TO_DATE('990915', 'RRMMDD') FROM DUAL; --> 1999-09-15 / RR의 문제점

🔎⭐연도 표기 포맷 R, Y의 차이점 ⭐

-- Y : 현재 세기(21세기 == 2000년대)
-- R : 변환하는 숫자가 50 이상이면 이전 세기(1900)
-- 변환하는 숫자가 50 미만이면 현재 세기(2000)

 

3. TO_NUMBER

문자열을 숫자로 변환
 == Integer.parseInt("100"); Double.parseInt("3.14"); -> 자바에서 문자데이터를 숫자로 변환

작성법

TO_NUMBER('문자열' | 컬럼 [, 포맷] )

예제 
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;

---

🚩 NULL 처리 함수

DB에서 NULL은 컬럼 값이 없다(비어있다)

 

1. NVL

컬럼 값이 NULL일 경우 다른 값으로 변경
자바와 같이 쓸때 자바에서 INT는 NULL값을 쓸 수 없기 때문에 자주 쓰인다. 

작성법

NVL(컬럼명, NULL일 때 변경할 값) 

예제1. 사원의 이름, 급여, 보너스, 급여 * 보너스 조회 BONUS가 NULL인 경우 0으로 계산
SELECT EMP_NAME, SALARY, NVL(BONUS, 0), SALARY * NVL(BONUS, 0)
FROM EMPLOYEE;

 

2. NVL2

컬럼 값이 NULL일 경우 다른 값으로 변경

작성법

NVL2(컬럼명, NULL이 아닐 때 변경할 값, NULL일 때 변경할 값)

예제1. 회사에서 사원들에게 보너스를 지급하려고한다. 보너스는 0.2%씩 증가한 만큼 지급
SELECT EMP_NAME, 
    NVL2(BONUS, BONUS + 0.2, 0.2), 
    SALARY * NVL2(BONUS, BONUS + 0.2, 0.2)
FROM EMPLOYEE;

 

3. NULLIF

두 비교대상의 값이 동일하면 NULL, 아니면 비교대상1 반환
서브쿼리, 프로시저와 함께 많이 사용되는 함수

작성법

NULLIF(비교대상1, 비교대상2)

예제 
SELECT NULLIF(100, 100) FROM DUAL; --NULL
SELECT NULLIF(100, 200) FROM DUAL; -- 100 

---

🚩 선택 함수

여러 경우(조건)에 따라 원하는 값을 선택하는 기능을 가진 함수

 

1. DECODE

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환

계산식 또는 컬럼의 값이 조건과 같으면 해당 값을 반환
-- 자바의 SWITCH문과 비슷 /삼항연산자와도 비슷한 형식

작성법

DECODE( 계산식 | 컬럼명 , 조건1, 값1, 조건2, 값2, ...... , 아무 것도 아닐 경우의 값) 

예제1. EMPLOYEE 테이블에서 사원의 이름, 성별(남,여)를 조회
SELECT EMP_NAME,   
    DECODE( SUBSTR(EMP_NO, 8, 1) , 1, '남', 2, '여' ) AS 성별
FROM EMPLOYEE;

➡ DECODE로 감싸서 사원의 주민등록번호 8번째 자리인 성별을 알 수있는 한 자리의 숫자를 가지고

1이면 '남', 2이면 '여'로 선택하여 값을 반환한다. 

 

2. CASE

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환(조건은 범위 값 가능)
자바의 if - else if - else 와 비슷

작성법

CASE 
    WHEN 조건식 THEN 결과값
    WHEN 조건식 THEN 결과값
    WHEN 조건식 THEN 결과값
    ELSE 결과값
END

 

예제1. EMPLOYEE 테이블에서 사번, 사원명, 급여를 조회
급여가 500만원 이상이면 '고급'
급여가 300~500만원이면 '중급'
그 미만은 '초급'으로 출력처리하고 별칭은 '구분'으로 한다.
부서코드가 'D6'인 직원만 조회
직급코드 오름차순 정렬

SELECT EMP_ID, EMP_NAME, SALARY, 
    CASE
        WHEN SALARY >= 5000000 THEN '고급'
         --WHEN SALARY >= 3000000 AND SALARY < 5000000 THEN '중급'
        WHEN SALARY >= 3000000 THEN '중급'
        ELSE '초급'
    END AS 구분
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6'
ORDER BY JOB_CODE;

- WHEN SALARY >= 3000000 AND SALARY < 5000000 THEN '중급'

이렇게 쓰지 않는 이유

WHEN SALARY >= 5000000 THEN '고급'을 통해 이미 500만 이상인 경우를 만들었기 때문에

500만 미만이라는 코드를 작성하지 않아도 된다.

위의 두 경우가 아니라면 ELSE로 '초급'

 

정렬하기 위해 ORDER BY , 오름차순이니깐 ASC 생략

---

그룹 함수

하나 이상의 행을 그룹으로 묶어 하나의 결과만을 반환하는 함수
합계(SUM), 평균(AVG), 최대값(MAX), 최소값(MIN), 개수(COUNT)

---

1. SUM

합계를 구하여 반환
해당 컬럼 값들의 총합 반환

작성법

SUM(숫자가 기록된 컬럼)

예제1. EMPLOYEE 테이블에서 성별이 여성인 사원의 연봉(급여 * 12) 합 조회
SELECT SUM(SALARY)  * 12
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2';

 

2. AVG

평균 값 반환

작성법

AVG(숫자가 기록된 컬럼)

예제1. 전 사원의 보너스 평균 조회 (소수점 셋째 자리에서 반올림)

SELECT ROUND( AVG( NVL(BONUS , 0)  ) , 2 )
FROM EMPLOYEE;

➡  평균 계산 시 NULL 값은 제외

보너스 값이  NULL이라면 NVL을 사용해서 NULL값을 0으로 바꿔준다

ROUND 셋째 자리에서 반올림은 둘 째 자리까지 표현하는 거니깐 ROUND , 2

 

3. MAX / MIN

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환

계산식 또는 컬럼의 값이 조건과 같으면 해당 값을 반환
-- 자바의 SWITCH문과 비슷

작성법

MAX(컬럼명) : 최대값, 가장 미래, 문자 순서 뒤쪽
MIN(컬럼명) : 최소값, 가장 과거, 문자 순서 앞쪽)

예제1. 부서코드가 D6인 부서의 가장 높은 급여, 가장 낮은 급여
SELECT MAX(SALARY), MIN(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6';

 

4. COUNT( * | [DISTINCT] 컬럼명 )

RESULT SET에 포함되는 행의 개수를 반환
결과 행이 몇행인지를 알려준다

예제1. 전체 사원 수 조회
SELECT COUNT(*) FROM EMPLOYEE;

1) COUNT(*) : 조회되는 행 중 NULL값이 있으면 NULL 포함한 전체 행 개수를 반환
2) COUNT(컬럼명) : 특정 컬럼에서 NULL을 제외한 행의 개수를 반환
3) COUNT(DISTINCT 컬럼명) : 특정 컬럼에서 NULL + 중복을 제외한 행의 개수를 반환

예제1. DEPT_CODE가 있는 사원 수 조회
SELECT COUNT(*) FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL;

예제2. DEPT_CODE가 있는 사원 수 조회(중복 카운트 X)
SELECT COUNT(DISTINCT DEPT_CODE) FROM EMPLOYEE;

예제3. 남자 / 여자 사원 수 조회
예제3-1.  남자
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = '1';

예제3-2. 여자
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = '2';

예제3-3. 전체 사원 수 조회
SELECT COUNT(*) "전체 사원 수",
    COUNT( DECODE( SUBSTR(EMP_NO,8,1) , '1' , '남' ) ) "남자 사원 수",
    COUNT( DECODE( SUBSTR(EMP_NO,8,1) , '2' , '여' ) ) "여자 사원 수" 
FROM EMPLOYEE;

예제 3

➡ DECODE( SUBSTR(EMP_NO,8,1) , '1' , '남' )를 컬럼명으로 결과값에서 COUNT를 통해 NULL값을 제외하고 반환한다.

--> 조건식으로 WHERE절을 쓰지 않아도 조회할 수 있다.