[Oracle] 숫자함수/날짜함수/일반함수/변환함수/그룹함수/조인(JOIN)

숫자함수 

: 매개변수로 숫자값을 전달받아 가공하여 결과값을 반환하는 함수

 

ROUND(숫자 값, 소수점 자릿수)

: 숫자 값을 전달받아 소수점 자릿수 위치만큼 검색되도록 반올림 처리하여 반환하는 함수

 

DUAL 테이블

: 테이블 없이 검색대상을 SELECT 명령으로 작성할 경우 사용되는 가상의 테이블

TRUNC (숫자값, 소숫점자릿수) 

: 숫자값을 전달받아 소숫점자릿수 위치만큼 검색되도록 절삭 처리하여 반환하는 함수 (절삭처리)

CEIL(숫자값)

 : 숫자값을 전달받아 소수점 이하 값이 존재할 경우 증가된 숫자값(정수값)을 반환하는 함수 (올림)

FLOOR(숫자값)

 : 숫자값을 전달받아 소수점 이하 값이 존재할 경우 감소된 숫자값(정수값)을 반환하는 함수(내림)

MOD(숫자값1, 숫자값2) 

: 두개의 숫자값을 전달받아 숫자값1을 숫자값2로 나눈 나머지를 반환하는 함수

POWER(숫자값1, 숫자값2) 

: 두개의 숫자값을 전달받아 숫자값1에 숫자값2의 제곱근을 반환하는 함수

---

날짜함수

• 매개변수로 날짜값을 전달받아 가공하여 결과값을 반환하는 함수 

SYSDATE 

• 시스템의 현재 날짜와 시간을 제공하기 위한 키워드 
• SYSDATE 키워드의 검색값은 기본적으로 [RR/MM/DD] 패턴으로 검색되지만 내부적으로는 날짜와 시간을 제공

ADD_MONTHS(날짜값, 숫자값)

 : 날짜값을 전달받아 숫자값만큼 개월수를 더한 날짜값을 반환하는 함수

 

날짜값은 + 연산자 및 - 연산자를 이용하여 연산이 가능하다.

날짜값 + 숫자값 = 날짜값 >> 일 증가

날짜값 + 숫자값/24 = 날짜값 >> 시간 증가

날짜값 - 숫자값 = 날짜값 >> 일 감소

SELECT SYSDATE, SYSDATE-7 FROM DUAL;

 

날짜값 - 숫자값/24 = 날짜값 >> 시간 감소

SELECT SYSDATE, SYSDATE-100/24 FROM DUAL; --100시간이 감소

 

날짜값 - 날짜값 = 숫자값 >> 일(실수값)

CEIL 이용해 올림처리 

>> ALIAS 네임 명확하게 구분하기위해 “” 쓴것일 뿐, 필수는 아니다

 

 

NEXT_DAY(날짜값, 요일) 

: 날짜값을 전달받아 미래의 특정 요일에 대한 날짜값을 반환하는 함수

>> 현재로부터 다가오는 금요일

 

오라클에 접속된 현재 사용자 환경(세션 : SESSION)에 따라 사용 언어 및 날짜와 시간 패턴이 다르게 적용된다.

세션의 사용 언어 및 날짜와 시간 패턴을 변경할 수 있다. 

TRUNC(날짜값, 표현단위) 

: 날짜값을 전달받아 원하는 단위만 표현하고 나머지는 절삭하여 초기값으로 반환하는 함수

>> 월까지 표현, 년도까지 표현

---

변환함수

: 매개변수로 전달받은 값을 원하는 자료형의 값으로 변환하여 반환하는 함수

 

TO_NUMBER(문자값)

• 문자값을 전달받아 숫자값으로 변환하여 반환하는 함수
• 주의) 매개변수로 전달받은 문자값에 숫자가 아닌 형태의 문자가 존재할 경우 에러 발생

비교 컬럼의 자료형이 숫자형인 경우 비교값이 문자값이면 TO_NUMBER 함수를 사용하여 문자값을 숫자값으로 변환하여 반환받아 비교가 가능하다 - 강제 형변환

비교 컬럼의 자료형이 숫자형인 경우 비교값이 문자값이면 자동으로 숫자값으로 변환되어 비교되어 자동 형변환 되기 때문에 굳이 TO_NUMBER를 사용할 필요가 없다. 

문자값을 산술 연산할 경우 문자값이 자동으로 숫자값으로 변환되어 연산 처리된다. - 자동 형변환

예시)

EMP 테이블에서 사원번호가 7839인 사원의 사원번호,사원이름,급여,세후급여(급여*0.9) 검색

자동 형변환 되어, 모두 같은 결과 값이 잘 출력된다.

 

 

TO_DATE(문자값[,패턴문자])

• 문자값을 전달받아 날짜값으로 변환하여 반환하는 함수
• 날짜와 시간 관련 패턴문자를 사용하여 원하는 패턴의 문자값을 전달받아 날짜값으로 변환 
• 패턴에 맞지 않는 문자값을 전달받은 경우 에러 발생 – 기본패턴 : [RR/MM/DD]

비교 컬럼의 자료형이 날짜형인 경우 비교값이 문자값이면 TO_DATE 함수를 사용하여 문자값을 날짜값으로 변환하여 반환받아 비교 가능하다. - 강제 형변환

비교 컬럼의 자료형이 날짜형인 경우 비교값이 문자값이면 자동으로 날짜값으로 변환되어 비교된다. - 자동 형변환

날짜값의 기본패턴은 [RR/MM/DD] 패턴을 기본으로 사용하지만 [YYYY-MM-DD] 패턴도 사용 가능하다.

패턴에 맞지 않는 문자값을 사용할 경우 에러가 발생한다.

 

TO_DATE 함수에 패턴문자를 사용하여 원하는 패턴의 문자값을 전달받아 날짜값으로 변환이 가능하다.

예시)

2000년 1월 1일에 태어난 사람이 오늘까지 살아온 날짜를 검색

>> 날짜값이 아닌 문자값의 연산처리이므로 에러 발생

 

TO_CHAR({숫자값|날짜값},패턴문자) 

: 숫자값 또는 날짜값을 전달받아 원하는 패턴의 문자값으로 변환하여 반환하는 함수

 

날짜패턴문자

: RR(년),YYYY(년),MM(월),DD(일),HH24(시간),HH12(시간),MI(분),SS(초)

 

예시)

EMP 테이블에서 1981년에 입사한 사원의 사원번호,사원이름,입사일 검색

>> 현재 접속된 사용자 환경(세션)의 날짜와 시간의 기본패턴이 [RR/MM/DD]인 경우에만 검색 가능하다

 

TO_CHAR 함수를 사용하여 날짜값을 원하는 패턴의 문자값으로 변환하여 반환받아 비교 처리

>> 권장하는 방법

 

숫자패턴문자 

: 9(숫자 또는 공백), 0(숫자), L(화폐단위), $(달러)

 

매개변수로 전달받은 숫자값의 길이가 패턴문자의 길이보다 큰 경우 모든 패턴문자가 [#]으로 변환되어 반환

예시)

EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,급여 검색

 

1.

2.

3.

 

4.

 

---

일반함수 

: 매개변수로 전달받은 값이 특정 조건에 참(TRUE)인 경우 변경값으로 변환하여 반환하는 함수

 

NVL(전달값,변경값)

• 전달값이 NULL인 경우 변경값으로 변환하여 반환하는 함수 
• 변경값은 전달값과 동일한 자료형의 값으로만 변경 가능하다. - 다른 자료형의 값으로 변경할 경우 에러 발생

예시)

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,연봉((급여+성과급)*12) 검색

>> 성과급이 NULL인 경우 연산이 불가능하므로 NULL 결과값 검색 – 검색 오류

 

NVL 함수를 사용하여 성과급이 NULL인 경우 0으로 변환하여 연산 처리해 오류 해결 가능

 

NVL2(전달값,변경값1,변경값2) 

: 전달값이 NULL이 아닌 경우 변경값1로 변환하여 반환하고 NULL인 경우 변경값2으로 변환하여 반환하는 함수

 

DECODE(전달값,비교값1,변경값1,비교값2,변경값2,...[,기본값])

• 전달값을 비교값과 차례대로 비교하여 같은 경우 변경값으로 변환하여 반환하는 함수
• 비교값이 모두 다른 경우 기본값으로 변환하여 반환하고, 기본값이 생략된 경우 NULL 반환

예시)

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,업무별 실급여 검색

(업무별 실급여 : 사원의 업무를 구분하여 실제로 지불된 급여) >>업무에 따른 급여 차등지급

 

-> ANALYST : 급여*1.1, CLERK : 급여*1.2, MANAGER : 급여*1.3, PRESIDENT : 급여*1.4, SALESMAN : 급여*1.5

 

 

예시)

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무별 급여 검색 - 해당 업무가 아닌 경우에는 NULL 검색

---

그룹함수

: 매개변수로 다수의 값을 전달받아 가공하여 결과값을 반환하는 함수

 

 

COUNT(컬럼명)

 : 매개변수로 전달받은 컬럼값의 갯수를 반환하는 함수 - 검색행의 갯수 반환

 

주의) 그룹함수는 다른 검색대상과 같이 사용할 경우 에러 발생

 

>> 그룹함수는 다른 검색대상과 같이 사용할 경우 그룹함수와 검색대상의 검색행의 갯수가 서로 다르므로 에러 발생

 

그룹함수는 NULL을 값으로 처리하지 않고 결과값 반환

 

COUNT 함수는 컬럼명 대신 [*] 기호를 사용하여 모든 컬럼을 표현하여 테이블에 저장된 행의 갯수를 반환

 

MAX(컬럼명) 

: 매개변수로 전달받은 컬럼값 중 최대값을 반환하는 함수

MIN(컬럼명) 

: 매개변수로 전달받은 컬럼값 중 최소값을 반환하는 함수

AVG(컬럼명) 

: 매개변수로 전달받은 컬럼값(숫자값)의 평균을 계산하여 반환하는 함수

EMP 테이블에 저장된 모든 사원의 평균 성과급을 계산하여 검색

>> 검색 오류 : 성과급이 NULL이 아닌 사원의 평균 성과급 계산

 

 

NVL 함수를 사용하여 성과급이 NULL인 경우 0으로 반환받아 평균 성과급 계산

 

 

 

EMP 테이블에 저장된 모든 사원을 부서로 구분하여 인원수 검색

GROUP BY 로 SELECT를 한번만 사용해 부서별 인원수를 검색할 수 있다.

GROUP BY 

• 그룹함수 사용시 컬럼값으로 그룹을 여러개 구분하여 검색하는 기능
• 컬럼값이 같은 경우 같은 그룹으로 처리하여 그룹함수의 결과값 반환

형식)SELECT 그룹함수(컬럼명)[,검색대상,...] FROM 테이블명 [WHERE 조건식]
    GROUP BY {컬럼명|연산식|함수},{컬럼명|연산식|함수},...
    [ORDER BY {컬럼명|연산식|별칭|COLUMN_INDEX} {ASC|DESC},...]

 

예제)

EMP 테이블에 저장된 모든 사원을 부서로 구분하여 인원수 검색

GROUP BY에서 사용한 표현식(컬럼명|연산식|함수)은 그룹함수와 같이 검색대상으로 검색 가능

GROUP BY의 표현식으로 컬럼의 별칭 사용 불가능

예제)

EMP 테이블에 저장된 모든 사원의 업무별 평균 급여를 계산하여 검색

EMP 테이블에서 업무가 PRESIDENT인 사원을 제외한 모든 사원의 업무별 평균 급여를 평균급여로 내림차순 정렬하여 검색

 

 

HAVING 

: GROUP BY에 의해 그룹화된 검색결과에서 그룹조건이 참(TRUE)인 그룹만 검색하는 기능 제공  >> 그룹에 대한 조건식

형식)SELECT 그룹함수(컬럼명)[,검색대상,...] FROM 테이블명 [WHERE 조건식]
    GROUP BY {컬럼명|연산식|함수},{컬럼명|연산식|함수},...HAVING 그룹조건식 
    [ORDER BY {컬럼명|연산식|별칭|INDEX} {ASC|DESC},...]

예시)

EMP 테이블에 저장된 모든 사원의 부서별 급여 합계 중 급여 합계가 9000 이상인 부서번호와 급여 합계 검색

---

JOIN 

: 두개이상의 테이블에서 행을 결합하여 원하는 컬럼값을 검색하기 위한 기능

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서번호 검색

DEPT 테이블에 저장된 모든 부서의 부서번호,부서이름,부서위치 검색

EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색 

 

두개이상의 테이블에서 컬럼값을 검색하기 위해 반드시 검색행을 결합하기 위한 조건을 제공하여 검색해야한다.

---

카타시안 프로덕트(CATASIAN PRODUCT)

: 두개이상의 테이블을 결합조건 없이 검색한 경우 발생되는 검색결과이다. 유효 join 조건 없이 검색한 경우, 두개 이상의 테이블에 저장된 모든 테이터틀 전부 결합하여 테이블에 존재하는 행 갯수를 곱한 만큼의 결과값이 반환된다.

 

동등조인(EQUI JOIN) 

• 두개이상의 테이블에서 결합조건에 = 연산자를 사용하여 참(TRUE)인 행만 결합하여 검색
• 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행만 결합 
• 결합조건은 WHERE의 조건식을 사용하여 결합
• 두개이상의 테이블에 같은 이름의 컬럼이 존재하는 경우 반드시 [테이블명.컬럼명] 형식으로 구분하여 표현

 

 

 

EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서번호,부서이름,부서위치 검색 

-결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행만 결합

결합 테이블에 같은 이름의 컬럼을 동시에 검색할 경우 첫번째로 검색되는 컬럼을 제외한 나머지 컬럼의 이름은 자동으로 변경되어 검색된다.

 결합 테이블에 같은 이름의 컬럼을 동시에 검색할 경우 컬럼 별칭을 사용하여 검색하는 것을 권장

TABLE ALIAS 

• 테이블에 일시적으로 새로운 이름을 부여하여 사용하는 기능 - 테이블 별칭
• 형식) SELECT 검색대상,... FROM 테이블명 별칭, 테이블명 별칭,...
• 테이블 결합시 테이블의 이름을 간단하게 표현하기 위해 사용하거나 하나의 테이블을 다수의 테이블로 표현하기 위해 사용

테이블 별칭 설정 후 기존 테이블명을 사용하면 에러 발생