[Oracle] 다양한 함수 배워보기

날짜 포맷

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')
      ,TO_CHAR(SYSDATE, 'YYYY/MM/DD')
      ,TO_CHAR(SYSDATE, 'YYYY-MM-DD')
      ,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;

 

날짜에 0 없애기

SELECT TO_CHAR(SYSDATE, 'MM/DD') --07/03
      ,TO_CHAR(SYSDATE, 'FMMM/DD') --7/3
FROM DUAL;

 

구분자로 날짜 형식 만들기

SELECT TO_CHAR(SYSDATE, ""YYYY"년 "MM"월 "DD"일")
      ,TO_CHAR(SYSDATE, ""HH24"시 "MI"분 "SS"초")
  FROM DUAL;

 

 

시간의 오전, 오후 값 표현

SELECT TO_CHAR(SYSDATE, 'AM')
     ,TO_CHAR(SYSDATE, 'AM HH:MI:SS')
     ,TO_CHAR(SYSDATE, 'YYYY-MM-DD AMHH:MI:SS')
   FROM DUAL;

---

NULL관련 함수

함수	설명
NVL(a, b)	a값이 null 일 때, b를 반환
NVL2(a, b, c)	a값이 null이 아니면 b를, null이면 c를 반환
COALESCE(a, b, ...)	표현식들이 null이 아니면 첫번째 식의 결과 반환
LNNVL(a)	a 조건식의 결과가 false나 unknow이면  true, true이면 false를 반환
NULLIF(a, B)	a와 b를 비교해 같으면 null을 같지 않다면 a를 반환

 

 

SELECT NVL(NAME, 'b') --결과 : 'b'
     ,NVL2(NAME, 'b', 'c') --'c'
     ,COALESCE(NAME, NAME) --NULL
     ,NULLIF(NAME, NAME) --NULL
FROM CUSTOMERS
WHERE LNNVL(NAME != 'AECOM'); --TRUE

 

SELECT NVL(MGR, 'NULL')
      ,NVL2(MGR, MGR, 'NULL2')
  FROM EMP;

 

NVL - MGR이 NULL값인 경우 'NULL'을 반환

NVL2 - MGR 값이 NULL이 아니면 MGR 값을 그대로 반환하고, NULL인 경우 'NULL2'를 반환

 

---

변환 함수

 

함수	설명
TO_CHAR(a, format)	숫자나 날짜를 문자로 변환
TO_NUMBER(a, format)	문자나 다른 타입을 NUMBER로 변환
TO_DATE(a, format)	문자를 date 타입으로 변환

 

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') --현재 날짜 문자열
     ,TO_NUMBER('123')			--123
     ,TO_DATE('20200810', 'YYYY-MM-DD') --20/08/10
FROM DUAL;

---

 

SELECT TO_NUMBER(EMPNO) AS EMPNO
      ,TO_NUMBER(MGR) AS MGR
      ,TO_CHAR(EMPNO + MGR, '999,999') AS "ADD2" 
 FROM EMP 
WHERE MGR IS NOT NULL;

다 같은 숫자 타입으로 보이지만 마지막은 문자열 타입으로 서로의 값을 더한다.

 

'999,999'

=> 세자리 마다 나눈 것

 

SELECT EMPNO, MGR, TO_CHAR(TO_NUMBER(EMPNO) + TO_NUMBER(MGR), '999,999') AS "연습용"
 FROM EMP 
WHERE MGR IS NOT NULL;

 

 

'999,999.00'

=> .00 소수점을 의미

 

---

집계함수

함수	설명
COUNT	검색되는 데이터 수를 반환
MAX	선택된 컬럼값 중에서 최대값 반환
MIN	선택된 컬럼값 중에서 최소값 반환
AVG	선택된 컬럼의 평균값 반환
SUM	선택된 컬럼의 합 반환
STDDEV	선택된 컬럼의 표준 편차 값 반환 (잘 안씀)

 

---

GROUP BY

데이터 그루핑(집계함수) - 특정 컬럼을 기준으로 집계해서 데이터들을 원하는 그룹으로 나눈다. 

 

 

SELECT [GROUP BY절에 지정된 컬럼1] [GROUP BY 별로 집계할 값]
FROM [테이블명]
GROUP BY [그룹으로 묶을 컬럼값];

 

SELECT JOB, SUM(SAL), COUNT(*), TO_CHAR(AVG(SAL), '9,990.90') AS "평균급여"
  FROM EMP
 WHERE SAL >= 1500
  GROUP BY JOB;

 

 

---

HAVING

GROUP BY 집계된 값 중 WHERE 처럼 특정 조건을 추가하는 경우

SELECT [GROUP BY절에서 지정된 컬럼1] [GROUP BY 별로 집계할 값]
FROM [테이블명]
GROUP BY [그룹으로 묶을 컬럼값]
HAVING [조건추가];

 

SELECT JOB, SUM(SAL), COUNT(*)
  FROM EMP
 GROUP BY JOB
 HAVING JOB <> '부장';
 
 SELECT JOB, SUM(SAL), COUNT(*)
  FROM EMP
 WHERE JOB <> '부장'
 GROUP BY JOB;

부장이 아닌 직급을 기준으로 직급, 금액을 다 더한 값, 개수를 가지고 온다.

 

 

~ 여기서 잠깐!! ~

 

WHERE와 HAVING의 차이

 

1

둘 다 조회를 해보면 출력값이 같다. 그럼 여기서는 둘은 완전히 같은 것 아닌가요?  할 수 있지만 내부에서 작동하는 방식을 뜯어보면 다르다! 자바에서는 컴파일러가 있다면 오라클의 경우 옵티마이저가 내부에서 처리를 한다는 걸 알아 둘 필요가 있다. 보통은 옵티마이저가 위에서 아래로 흘러가며 읽어내려가지만 GROUP BY(그룹화)를 할 때 WHERE가 없다면 그룹화를 먼저 하고 나서 HAVING을 처리하게 된다. 반대로 WHERE가 있을 경우 먼저 거를 항목들을 거르고 그룹화를 하게 된다. 때문에 같은 값이 나오더라도 더 방대한 양을 처리할 때는 WHERE를 사용하는 것이 더 빠르다는 걸 알면 된다! 

 

2

또한 그룹화를 했는데 직계함수를 사용한다면 HAVING에서 사용을 해야하는 것이다!!

 

3

직계함수가 나오지 않았다면 WHERE로 대체할 수 있다. 그러는게 속도에도 좋다!

 

---

DISTINCT

데이터를 조회한 후에 중복을 제거한 결과들을 나타낸다.

 

 

GROUP BY절은 그룹핑과 정렬 작업을 실행할 수 있지만, DISTINCT는 단순 그루핑 작업만 수행한다. 때문에 성능면에서 더 빠르다! 그러니 정렬이 필요 없는 그루핑 작업에서는 DISTINCT를 사용하고 집계함수가 필요한 경우는 GROUP BY절을 사용한다. 

 

 

	직계함수	정렬	속도
DISTINCT	X	X	빠름
GROUP BY	O	O	느림

 

 

 

---

ORDER BY 정렬

ORDER BY [ 컬럼명 ] [ ASC/DESC ];

 

 

ASC 오름차순

DESC 내림차순

 

SELECT *
  FROM EMP
ORDER BY DEPTNO ASC, EMPNO DESC;

 

---

JOIN

 

두 개 이상의 테이블을 서로 연결하고 데이터 검색을 할 때 사용한다. 보여줄 땐 두개의 테이블을 하나의 테이블 처럼 보여준다.

 

기본 사용방법

- 두 개의 테이블에 하나라도 같은 컬럼이 있어야한다.

- 두 컬럼 값은 공유되어야 한다.

- 조인을 위해 테이블의 식별값인 기본키와 테이블 간의 공통 값인 외래키 값을 사용해서 조인을 한다.

 

 

---

Equi Join(등가조인)

- 내부조인(INNER JOIN)과 자연조인(NATURAL JOIN)이 있으며 가장 많이 사용한다.

- 조인의 조건이 정확히 일치하는 경우 사용 => PK(기본키), FK(외래키)

- 조인의 조건에 '='을 넣어서 이용함

 

※주의※

1. 각각의 테이블에 대한 AS를 반드시 명시해야 함

2. 양쪽 테이블에 모두 데이터가 존재해야 함

 

 

내부조인(INNER JOIN)

- 두 개 이상의 테이블을 조인하면서 INNER JOIN을 사용

- INNER 생략 가능

- 조건절에서 WHERE 대신에 ON을 사용

 

 SELECT E.EMPNO, E.ENAME ENAME, E.DEPTNO, D.DNAME
   FROM EMP E INNER JOIN DEPT D
     ON E.DEPTNO = D.DEPTNO;
     
SELECT E.EMPNO EMPNO, E.ENAME ENAME, E.DEPTNO DEPTNO, D.DNAME DNAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO;

 

 

 

INNER JOIN - ON을 사용하는 방법

INNER JOIN을 생략하고 WHERE를 사용하는 방법

 

두 가지 방법이 있지만 세 가지의 테이블을 이어 줄 때 INNER JOIN의 경우 문장이 복잡해지기 때문에 거의 쓰지 않는다. 

 

 

 

자연조인(NATURAL JOIN)

- 조건절 없이 양쪽에 해당하는 컬럼을 적어줌으로써 그 컬럼에 자동으로 조인

- 서로 동일한 컬럼 앞에 AS(alias)된 테이블의 별칭을 적어주면 에러 발생(동일한 컬럼이 두 개 이상이 있어도 상관 없음)

- 등가조인인 '=' 쓸 때 동일한 컬럼과 속성이 두 번 명시해 주어야 하기 때문에 이 중복을 제거하기 위해 사용

 

 

 

 

---

Non-Equi Join(비등가조인)

등가조인과 반대로 '=' 연산자가 아닌 다른 조건으로 JOIN을 수행

ex) 부등호,  BETWEEN AND, IS NULL, IS NOT NULL, IN 등

 

-- 조회항목은 직원번호, 직원명, 부서코드, 부서명, 직급, 급여액
-- 조건 : 급여액이 1500~2500 사이 직원에 대한 조회
SELECT E.EMPNO EMPNO, E.ENAME ENAME, E.DEPTNO DEPTNO, D.DNAME DNAME, E.JOB JOB, E.SAL SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN 1500 AND 2500;

 

SELECT절을 유심히 보자

SELECT E.EMPNO EMPNO ··· ?

이미 별칭이 들어가 있는데도 불구하고 왜 컬럼명이 두 번이나 들어가는 걸까? 

 

---

 

SELF JOIN(셀프 조인)

동일한 테이블에 다른 별명을 붙여 마치 여러 개의 테이블 간에 조인하는 것처럼 만들어 자기 자신을 참조하는 조인 방법이다. 테이블에 반드시 별명을 붙여야 하기 때문에 '별명을 이용한 조인'이라고도 불린다.

 

 

--관리자가 부장인 직원에 대하여 급여액, 부서명, 부서위치를 조회하는 쿼리
--조회 항목 : 직원코드, 직원명, 직급, 부서명, 부서위치

SELECT DISTINCT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DNAME, D.LOC
  FROM EMP E, EMP P, DEPT D
 WHERE E.EMPNO = P.MGR
   AND E.JOB = '부장'
   AND E.DEPTNO = D.DEPTNO;

 

 

다른 방법으로도 값을 추출할 수 있지만 셀프 조인 예제를 이용한다.

 

FROM EMP E, EMP P, DEPT D

여기서 셀프 조인을 하는 취지를 잘 봐야한다. <EMP 사원관리 테이블>의 경우 사원도 있고 그 사원을 관리하는 관리자도 있다. 그것을 구별하기 위해서 셀프조인을 사용하는 것이다. 때문에 하나의 테이블을 E와 P로 나누었다.

 

WHERE E.EMPNO(직원번호) = P.MGR(관리자)
'관리자'가 '부장'인 직원을 찾기 위해서 같은 테이블이지만 이어줌

 

AND E.JOB = '부장' 

내가 찾은 관리자 중에서 부장급을 뽑는다.

 

AND E.DEPTNO = D.DEPTNO;

내가 찾은 부장의 직원번호, 이름, 직급 등 다른 테이블에서 갖고 오기 위해 서로의 기본키를 이어준다. 

 

SELECT B.EMPNO, B.ENAME, B.JOB, B.SAL, D.DNAME, D.LOC
  FROM EMP A, EMP B, DEPT D
 WHERE B.JOB = '부장'
   AND A.EMPNO = B.MGR
   AND B.DEPTNO = D.DEPTNO;

 

값은 같지만 이 예제를 보면 보는 관점이 조금 다르다. '부장'이라고 되어 있지만 결국은 부장에 대한 컬럼이 아니라 '사장의 부하인 부장'인 관점이기 때문에 조심해야 한다!