[SIST] Oracle_days06

1. 집계함수 == 그룹함수 == 복수행 함수

*** 주의할 점) NULL 값을 제외한 ***

1) SUM(n) : NULL 값을 제외한 n의 합계

 

2) AVG(n) : NULL 값을 제외한 N개 행의 평균값을 리턴한다.

 

3) COUNT(n) : NULL을 제외한 행의 갯수를 리턴한다. COUNT(*)은 NULL 값을 포함한 행(레코드)갯수.

 

4) MAX() : 최대값을 리턴한다.

 

5) MIN() : 최소값을 리턴한다.

 

6) STDDEV(n) : NULL 값을 제외한표준편차 구하는 함수

 

7) VARIANCE(n) : NULL 값을 제외한 분산값 구하는 함수

---

예제1) emp 테이블에서 최고급여액을 받는 사원의 정보를 출력

 

예제2) emp 테이블에서 최저급여를 받는 사원의 정보를 출력

 

예제3) emp 테이블에서 최고, 최저 급여를 받는 사원의 정보를 조회

<ALL과 EXISTS 연산자 맛보기>

- ALL 연산자

- EXISTS 연산자

 

예제4) 

---

2. 단일행 함수 중 날짜 함수(days05 이어서..)

- 날짜형 - 날짜형 = 일수
- 날짜형 + 숫자형 = 숫자(일수)만큼 증가한 날짜
- 날짜형 - 숫자형 = 숫자(일수)만큼 빼진 날짜
- 날짜형 + 숫자/24 = 시간이 더해진 날짜형
- 날짜형 - 숫자/24 = 시간이 빼진 날짜형

 

1) MONTHS_BETWEEN() 함수 : 날짜, 날짜 사이의 개월 수를 리턴하는 함수

---

2) ADD_MONTH() : 특정 수의 달을 더한 날짜를 리턴하는 함수

- 달의 마지막 날짜에서 1을 더하면 다음 달의 마지막 날짜를 가져옴

  ex) 2월 28일 -> 3월 31일

 

 

예제1) emp 테이블에서 각 사원의 입사일을 기준으로 100일 후 날짜, 10일전 날짜, 1시간 후 날짜, 3개월 전 날짜 출력

 

예제2) emp 테이블에서 각 사원의 입사일자를 기준으로 10년 5개월 20일째 되는 날은??

---

3) LAST_DAY() : 특정날짜가 속한 달(월)의 가장 마지막 날짜를 반환하는 함수

---

4) NEXT_DAY() : 명시된 요일이 돌아오는 가장 최근의 날짜를 반환하는 함수

---

3. 변환함수(형변환 함수)

1) TO_DATE() 함수 : 문자열을 날짜형으로 형변환 해주는 함수

[형식]

TO_DATE( char [,'fmt' [,'nlsparam']])

format = fmt는 TO_CHAR() 함수에 사용하는 것과 동일

---

2) TO_CHAR() 함수 : 숫자, 날짜형을 문자열로 형변환 해주는 함수

[형식]

TO_CHAR(date || n [,fmt [,nlsparam]] )

 

[숫자 표기 지정 접미사]

 

[숫자 표기 형식]

---

3) TO_NUMBER() : 문자열을 숫자형으로 형변환 해주는 함수

- 오라클에서는 순수한 숫자로 이루어진 문자열은 묵시적으로 형변환이 된다.

   ex) '20' -> 20

- 자동으로(묵시적) 문자를 숫자로 변환이 되어서 잘 사용X

---

TO_DATE 예제1) 개강일로부터 오늘 날짜까지의 수업일수(토, 일, 공휴일 포함)

 

아래와 같이 쿼리를 작성하면 에러가 발생한다.

- ORA-00932: inconsistent datatypes: expected CHAR got DATE
- 해석 : CHAR와 DATE 데이터 타입이 맞지 않는다.
- 오라클 '문자열' '날짜형' -> 홑따옴표를 붙이는데.. '2022.02.15' 날짜형이 아닌 문자열로 처리함

 

따라서 아래와 같이 TO_DATE를 사용하여 날짜형으로 변환해 주어야 한다.

추가로 02/15/2022 이런 형식으로 날짜 데이터가 있다면 포맷을 인식하지 못하기 때문에 포맷을 따로 지정해주어야 한다.

포맷을 지정해주지 않으면 아래와 같은 에러 메시지가 발생한다.

- ORA-01841: (full) year must be between -4713 and +9999, and not be 0

 

TO_DATE 예제2)

 

TO_DATE() 예제3) yyyy년 mm월 dd일

* 년, 월, 일을 포맷 형식에 넣을 때는 "" 을 붙여서!

 

TO_DATE() 예제4) insa 테이블에서 생일을 얻어와서 생일이 지났는지 안지났는지 확인

---

TO_NUMBER() 예제)

---

TO_CHAR() 예제1)

 

TO_CHAR() 예제2)

TO_CHAR() 예제3)

---

4. 일반함수 3가지

1) COALESCE(expr1 [,expr2,...]) : 값을 순차적으로 체크해서 NULL이 아닌 값을 반환하는 함수

- NULL이 아닌 값을 찾아가는 함수이다.

 

- expr1이 NULL이면 expr2를, expr2이 NULL이면 expr3를, expr3이 NULL이면 expr4를.. 이런식으로 NULL이 아닌 값을 찾아가는 것!

 

---

2) DECODE() : 여러 개의 조건을 주어 조건에 맞을 경우에 해당 값을 리턴하는 함수

(1) 프로그램 언어(Java)의 if문과 비슷하다.

(2) FROM 절에서만 사용할 수 없다.

(3) 조건식에 == 연산자 하나 밖에 없다. -> if( == )

(4) DECODE() 함수의 확장 함수는 CASE() 함수이다.

예제1)

 

예제2)

 

예제3)

 

> DECODE를 사용하여 조건에 해당하지 않은 값은 null이 들어가진다.

 

예제4)

 

예제5)

 

예제6)

 

예제7)

---

3) CASE() : DECODE 함수의 확장함수

(1) 범위 비교가 가능하다.

(2) IF~THEN~ELSE문장과 똑같은 결과를 출력할 수 있다.

(3) 산술연산, 관계연산, 논리연산과 같은 다양한 비교가 가능하다.

(4) WHEN 절에서 표현식을 다양하게 정의할 수 있다.

 

    【형식1】
        CASE 컬럼명|표현식 WHEN 조건1 THEN 결과1
                  [WHEN 조건2 THEN 결과2
                                    ......
                   WHEN 조건n THEN 결과n
                  ELSE 결과4]
        END [ALIAS 별칭]    -- if문의 괄호 닫는 것과 같음

 

    【형식2】
        CASE 컬럼명|표현식 WHEN 조건1 THEN 결과1
                  [WHEN 조건2 THEN 결과2
                                    ......
                   WHEN 조건n THEN 결과n
                  ELSE 결과4]
        END [ALIAS 별칭]    -- if문의 괄호 닫는 것과 같음

 

예제1)

 

예제2)

---

5. SELECT문 7개의 절 중 GROUP BY 절 설명

- 집계함수와 같은 역할을 한다.

- 특정 컬럼을 기준으로 집계

 

예제1)

 

예제2)

GROUP BY 설명은 내일 수업할 때 더 이어서 할 예정!