[Oracle] : DAY21 _ 단일행함수와 그룹함수

함수 

컬럼의 값을 읽어서 연산한 결과를 반환 

- 단일행(SINGLE ROW)함수 : N개의 값을 읽어서 N개의 결과를 반환 
- 그룹(GROUP)함수 : N개의 값을 읽어 1개의 결과 반환

---

1.  단일행함수 (N개의 값을 읽어서 N개의 결과를 반환 )

1-1) 문자 관련 함수 

LENGTH ,  INSTR , SUBSTR, TRIM

 

 

LENGTH (문자열|칼럼) : 문자열 길이 반환

SELECT LENGTH ('HELLO WORLD') FROM DUAL;  //출력 11

 

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

-- 문자열에서 맨 앞에 있는 B 위치 조회 // 출력 3
SELECT INSTR('AABAACAABBAA','B')FROM DUAL; 

--문자열에서 5번째 부터 검색해서 맨 앞에 있는 B 위치를 조회 /출력 9
SELECT INSTR('AABAACAABBAA','B',5)FROM DUAL;

 

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

: 지정한 위치부터 길이만큼 잘라냄 

-- EMPLOYEE 테이블에서 사원명, 이메일 중 아이디만 조회 sun_di@or.kr
SELECT EMP_NAME,SUBSTR(EMAIL, 1,INSTR(EMAIL,'@')-1) 아이디
FROM EMPLOYEE
ORDER BY 아이디;

 

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

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

: 주어진 칼럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자를 제거

-- 옵션 : LEADING(앞쪽), TRAILING(뒤쪽), BOTH(양쪽, 기본값)
SELECT '     K         H     ', TRIM('     K  H     ')FROM DUAL;--> 양쪽 공백 제거(중간미포함)

SELECT '---KH---',TRIM(TRAILING'-'FROM'---KH---') FROM DUAL;
-- BOTH 또는 생략 시 : 양쪽 '-' 기호 제거 
-- LEADING : 앞쪽만 제거 
-- TRAILING : 뒤쪽만 제거

 

1-2) 숫자처리 함수 

ABS, MOD, ROUND, CEIL,FLOOR.TRUNC

 

 

ABS(숫자|컬럼명) : 절대값 

 

MOD(숫자|컬럼명, 숫자|컬럼명) : 나머지 값 반환 

 

ROUND(숫자 |칼럼명 [,소숫점위치]):반올림

 

CEIL(숫자|컬럼명) : 올림 값 반환

 

FLOOR(숫자 |컬럼명) : 내림 값 반환 

 

TRUNC(숫자|컬럼명 [,위치]) :특정 위치 아래를 버림 (절삭)

---

1-3) 날짜(DATE) 관련 함수

SYSDATE , LAST_DAY, EXTRACT, FLOOR,TRUNC

 

 

SYSDATE : 시스템에 현재 시간(년,월,일,시,분,초)을 반환 

 

SYSTIMESTAMP: SYSDATE + MS(시차) 단위 추가

 

MONTHS_BETWEEN(날짜,날짜) : 두 날짜의 개월 수 차이 반환

 

ADD_MONTHS(날짜,숫자):날짜에 숫자만큼의 개월 수를 더함 

 

LAST_DAY(날짜) : 해당 달의 마지막 날짜를 구함 

 

EXTRACT:년,월,일 정보를 추출하여 리턴 
-EXTRACT(YEAR FROM 날짜) : 년도만 추출
-EXTRACT(MONTH FROM 날짜) : 월만 추출
-EXTRACT(DAY FROM 날짜) : 일도만 추출

 

1-4) 형변환 함수

TO_CHAR, TO_NUMBER, TO_DATE, 

 

TO_CHAR

문자열로 반환

 

TO_CHAR(날짜,(포맷)) : 날짜형 데이터를 문자형 데이터로 변경
TO_CHAR(숫자,(포맷)) : 문자형 데이터를 숫자형 데이터로 변경

 

(숫자: 패턴)

9 : 숫자 한칸을 의미, 여러개 작성 시 오른쪽 정렬 
0 : 숫자 한칸을 의미, 여러개 작성 시 오른쪽 정렬 + 빈칸 0추가 
L : 현재 DB에 설정된 나라의 화폐 기호 

 

(날짜: 패턴) 

YYYY :년도 / YY:년도 (짧게)
RRRR :년도 / RR:년도 (짧게) 
MM : 월 / DD : 일
AM 또는 PM : 오전 / 오후 표시 
HH : 시간 / HH24 :24시간 표기법
MI : 분 / SS :초 
DAY : 요일(전체) / DY : 요일(요일명만 표시)

 

TO_DATE

날짜로 변환

 

- TO_DATE(문자형 데이터,[포맷]) :문자형 데이터를 날짜로 변경 
- TO_DATE(숫자형 데이터,[포맷]) :숫자형 데이터를 날짜로 변경 

-EMPLOYEE 테이블에서 각 직원이 태어난 생년월일 조회 
SELECT EMP_NAME,TO_DATE(SUBSTR(EMP_NO,1,6),'RRMMDD')FROM EMPLOYEE;

- 해당 날짜 구하는 문구 

SELECT TO_CHAR((TO_DATE(20201225)), 'day')FROM DUAL; -- 금요일 

 

TO_NUMBER

숫자로 변환

 

 

TO_NUMBER(문자열,[포맷]) : 문자형 데이터를 숫자 데이터로 변경 

NVL(컬럼명, 컬럼값이 NULL일때 바꿀 값):NULL인 컬럼값을 다른 값으로 변경

--EMPLOYEE 테이블에서 이름, 급여, 보너스, 급여+보너스 조회 
SELECT EMP_NAME,SALARY,NVL(BONUS,0),SALARY*NVL(BONUS,0)
FROM EMPLOYEE;

NVL2(컬럼명, 바꿀값1,바꿀값2)

해당 컬럼의 값이 있으면 바꿀값1로 변경 
해당 컬럼이 NULL이면 바꿀값2로 변경

-- EMPLOYEE테이블에서 기존 보너스를 받던 사원의 보너스를 0.8로 
-- 보너스를 받지 못했던 사원의 보너스를 0.3으로 변경하여 
-- 이름, 기존 보너스, 변경된 보너스 조회 
SELECT EMP_NAME,BONUS,NVL2(BONUS,0.8,0.3)
FROM EMPLOYEE;

*선택함수*

DECODE(계산식 | 칼럼명, 조건값1, 선택값1, 조건값2, 선택값2.....,아무것도 일치 하지 않을때)

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

-- 직원들의 성별구분하기
SELECT EMP_NAME,DECODE(SUBSTR(EMP_NO,8,1),'1','남자','2','여자')성별
FROM EMPLOYEE;

 

● DECODE(계산식 | 칼럼명, 조건값1, 선택값1, 조건값2, 선택값2.....,아무것도 일치 하지 않을때)

- 조건값1이 맞다면 선택값1을 실행하고 조건값 2가 맞다면 선택값2를 실행, 만일 둘다 조건이 일치하지 않다면

   아무것도 일치 하지 않을때 실행 

 

● DECODE(조건값 ,선택값1,선택값2)

- 조건값이 맞다면 선택값1을 실행하고 아니면 선택값 2을 실행해라

자바 (IF / ELSE IF /ELSE)라고 생각하면 편하다 

---

 

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

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

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

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

 

2.  그룹(GROUP)함수 (N개의 값을 읽어 1개의 결과 반환)

-  SUM, AVG, MAX, MIN,COUNT

 

SUM(숫자가 기록된 컬럼명) : 합계 

--부서코드가 'D9'인 직원들의 급여합 
SELECT SUM(SALARY) FROM EMPLOYEE
WHERE DEPT_CODE ='D9';

 

AVG(숫자가 기록된 컬럼명) : 평균 

SELECT ROUND(AVG(SALARY)) FROM EMPLOYEE;

 

MIN(컬럼명):최소값

MAX(칼럼명):최대값

--사번이 200이 아닌 사람들 중에서 
--EMPLOYEE 테이블에서 가장 높은 급여, 
--가장 높은 입사일, 
--알파벳 순서가 가장 늦은 이메일 
SELECT MAX(SALARY),MAX(HIRE_DATE),MAX(EMAIL) 
FROM EMPLOYEE
WHERE EMP_ID != 200;

 

COUNT

- COUNT (*|컬럼명) : 행 개수를 헤아려서 리턴 
- COUNT ([DISTINCT]칼럼명) : 중복을 제거한 행 개수를 헤아려서 리턴
- COUNT  (*) : NULL을 포함한 전체 행 개수를 리턴 
- COUNT(컬럼명)  : NULL을 제외한 실제 값이 기록된 행 개수를 리턴함 

-- EXPLOYEE테이블 전체 행의 개수 == 전체 직원 수 
SELECT COUNT(*) FROM EMPLOYEE;

--EMPLOYEE 테이블에 있는 부서 개수 
SELECT COUNT(DISTINCT DEPT_CODE) FROM EMPLOYEE;

--COUNT(칼럼명) : NULL을 제외한 실제 값이 기록된 행 개수를 리턴함 
SELECT COUNT(DEPT_CODE) FROM EMPLOYEE;