오라클 데이터베이스 함수다뤄보기

데이터베이스 함수

---

• LOWER : 소문자 변환
• INITCAP : 첫글자만 대문자

SELECT ENAME, LOWER(ENAME), INITCAP(ENAME)
    FROM EMP;

 

• UPPER : 대문자 변환

SELECT *
    FROM EMP
WHERE ENAME LIKE UPPER('%s%');

 

 

• LENGTH : 글자수 리턴

SELECT ENAME, LENGTH(ENAME)
    FROM EMP;

LENGTH를 이용해 ENAME에 몇글자인지 확인할 수 있다.

 

SELECT *
    FROM EMP
WHERE LENGTH(ENAME) = 5;

좀 더 응용하면 글자수를 이용해 5글자인 데이터만 추출하는것도 가능하다.

 

 

• LENGTHB : 바이트로 리턴

SELECT LENGTH('가나'), LENGTHB('가나')
    FROM DUAL;

SELECT 20*30
    FROM DUAL;

DUAL은 더미 테이블이다. 단순 계산식을 조회하고 싶을때 많이 사용된다.

 

 

• ABS : 절대값 구함

 

• ROUND : 반올림
• ROUND
  • ROUND(반올림할 숫자, 반올림한 위치[생략가능]=0)
  • 양수(소수), 음수(정수부분)
  • 2 : 소수 셋째자리에서 반올림
  • 1 : 소수 둘째자리에서 반올림
  • 0 : 소수 첫째자리에서 반올림[생략가능]
  • -1 : 일의 자리에서 반올림
  • -2 : 십의 자리에서 반올

SELECT 12.3456, ROUND(12.3456), ROUND(12.3456, 2), ROUND(12.3456, -1)
    FROM DUAL;

 

 

• TRUNC : 버림

SELECT 12.3456 , TRUNC(12.3456), TRUNC(12.3456, 2), TRUNC(12.3456, -1)
    FROM DUAL;

 

• CEIL : 올림(두번째 인수 없음)
  • 소수있으면 그냥 올려버림 (정수로 바뀜)
• FLOOR : 버림(두번째 인수 없음)
  • 소수가 있으면 그냥 날

SELECT CEIL(12.345), FLOOR(12.345)
    FROM DUAL;

 

 

 

 

• 나머지
  • MOD(X, Y) -> X나누기 Y의 나머지를 구해주는 함수

SELECT MOD (10, 3) FROM DUAL;

 

• POWER(X, Y) -> X를 제곱한 결과를 출력

SELECT POWER (3, 4) FROM DUAL;

 

• SUBSTR (문자열, X, Y)
  • 해당 문자열(컬럼)에서 X번째부터 Y개만큼 출력
  • Y가 생략시 끝까지 출력해준다
  • X가 양수면 왼쪽부터 1로 시작
  • X가 음수면 오른쪽부터 -1로 시작
•  

SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5)
    FROM EMP;

 

• INSTR
  • 해당 문자의 위치를 출력

 

• INSTR(문자 (컬럼명), 찾을 문자) -> 문자열에서 찾을 문자가 몇번째인지 찾아준다.
  • 단, 첫번째거 위치가 나온다.

SELECT INSTR('HELLO ORACLE', 'L') FROM DUAL;

 

 

• INSTR(문자 (컬럼명), 찾을 문자, X) ->문자열에서 찾을 문자가 몇번째인지 찾아준다.
  
  • X번째부터 검색을 시작함

SELECT INSTR('HELLO ORACLE', 'L', 5) FROM DUAL;

 

 

• INSTR(문자 (컬럼명), 찾을 문자, X, Y) ->문자열에서 찾을 문자가 몇번째인지 찾아준다.
  • X번째부터 검색 시작해서 Y 번째 찾을 문자의 위치를 출력

SELECT INSTR('HELLO ORACLE', 'L', 1, 2) FROM DUAL;

 

• REPLACE(문자열(컬럼명), X, Y)
  • 해당 문자열(컬럼)에서 X를 Y로 바꿔주는 함

SELECT REPLACE('010-1234-5678', '-', 'X')
    FROM DUAL;

 

• REPLACE(문자열(컬럼명), X)
  • 해당 문자열(컬럼)에서 X를 지움

SELECT REPLACE('010-1234-5678', '-')
    FROM DUAL;

 

 

• LPAD(문자열, X, Y)  RPAD(문자열, X, Y)
  • 해당문자열 (컬럼)을 X칸만큼 공간을 만들어서 빈칸은 Y로 채운다(L은 왼쪽에 채움, R은 오른쪽에 채움)

SELECT LPAD('ORACLE', 10, '+'), RPAD('ORACLE', 10, '+')
    FROM DUAL;

 

    SELECT RPAD('010-1234-', 13, '*')
        FROM DUAL;

위 방식 보다 더 좋은 방식이 존재한다. 위 방식은 보통 잘 사용하지 않는다.

 

 

• CONCAT : 두개를 연결시켜준다
• SELECT CONCAT(EMPNO, ENAME), EMPNO || ENAME FROM EMP;

 

• SYSDATE : 날짜 확인
  • 단순 더하기, 빼기 정도만 가능하다.

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE+1 FROM DUAL;

 

• ADD_MONTHS(날짜, X개월수) -> 해당 날짜에서 X개월수를 더한 날짜가 나옴
  • 개월수를 구할때는 위 방법을 이용한다.

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

SELECT SYSDATE, ADD_MONTHS(SYSDATE, -3) FROM DUAL;

 

-- EMP 테이블에서 입사일에 10년뒤 날짜를 출력
SELECT HIREDATE, ADD_MONTHS(HIREDATE, 120) FROM EMP;

 

SELECT SYSDATE -HIREDATE FROM EMP;

 

• MONTHS_BETWEEN
  • (SYSDATE, HIREDATE) -> (HIREDATE, SYSDATE)로 순서를 바궈서 쓰면 음수가 나온다

SELECT HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE)
    FROM EMP;

 

• NEXT_DAY

SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일')
    FROM DUAL;

 

• LAST_DAY

SELECT LAST_DAY(SYSDATE)
    FROM DUAL;

 

• TO_CHAR : 날짜 데이터를 형식에 맞춰 바꿔줌

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

 

• YYYY : 년(4자리)
• YY : 년(2자리)
• MM : 월 (MOM : 약자, MONTH : 다나옴)
• DD : 일
• DDD : 일(365일 중에 몇번째 일)
• DY : 요일(약자)
• DAY : 요일(다나옴)
• W : 몇번째주인지 나옴(1년 중 총 몇번째 주인지)
• HH24 : 시(24시간 형식) -> 오후 1시면 13시
• HH : 시(12시간 형식) -> 오후 1시면 1시
• MI : 분
• SS : 초

SELECT TO_CHAR(SYSDATE, 'YYYY') ---2023
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YY') ---23
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MM') ---01
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD') ---10
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DY') ---화
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DAY') ---화요일
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'W') ---1년중 2째주여서 2나옴
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH24') ---시
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH') ---시
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MI') ---분
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'SS') ---초
    FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'PM') ---오전오후
    FROM DUAL;

 

 

SELECT TO_CHAR(SYSDATE, 'MON')
    FROM DUAL;

 

언어 변경도 가능하

SELECT TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = ENGLISH')
    FROM DUAL;

 

SELECT TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = ENGLISH')
    FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = ENGLISH')
    FROM DUAL;

 

 

select to_char(sal, '$9,999')
    from emp;

 

L을 사용하면 해당 지역 화폐 단위로 인식을 자동으로 해준다

• L : 지역 화폐 기호
• 9 : 숫자 (0을 표시 안해줌)
• 0 : 숫자 (0을 표시 해줌)

TO_CHAR(123123123, '9')

select to_char(sal, 'l9,999')
    from emp;

 

select to_char(sal, 'L999,999')
    from emp;
    
select to_char(sal, 'L000,000')
    from emp;

 

• 자동 형변환

select 1300 - '1100'
    from dual;

 

• to_number : 숫자로 형변환

select to_number('1300') - to_number('1100')
    from dual;

 

• to_date : 날짜로 형변환

select to_date('20200101', 'yy/mm/dd')
    from dual;

 

• nvl

select nvl(sal+comm, 0)
    from emp;

select nvl(sal+comm, sal)
    from emp;
    
select sal + nvl(comm, 0)
    from emp;

 

nvl(x, y)

x가 null 인지를 파악해서 만약 null이면 y가 출력 null이 아니면 x가 출력

nvl(sal_comm, sal)

sal+comm 계산한게 null? -> null이다 : sal 만 출력

                                              null아니다 : sal + comm 출력

 

 

nvl2 (x, y, z)

x가 null인지 아닌지 파악

만약 x가 null이 아니면 : y가 출력

만약 y가 null이면 : z가 출력

select comm, nvl2(comm, 'o', 'x')
    from emp;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

if : 두개가 같은지를 비교해서 같으면 null을 출력 / 같지 않으면 첫번째 인수가 출

select nullif(10, 10), nullif(10, 20)
    from dual;

 

 

• decode (switch - case 문이랑 같음)

select job, decode(job, 
                'MANAGER', 1000,
                'SALESMAN', 500,
                300)
        from emp;

 

 

select job,
        case
            when job = 'MANAGER' then 1000
            when job = 'SALESMAN' then 500
        end as 케이스
    from emp;

 

 

select sal,
    case
        when sal >= 2000 then '급여높음'
        else '급여낮음'
    end as 케이스
from emp

 

- 문제

--EMP 테이블에서 이름이 다섯글자인 사람들의
--사원번호, 이름 그리고
--사원번호 앞 두자리와 뒤 두자리는 *로 출력하고
--이름의 첫글자와 나머지 글자는 *로 출력

SELECT EMPNO, ENAME,
    SUBSTR(EMPNO, 1, 2) || '**' AS 사원번호처리,
    RPAD(SUBSTR(ENAME, 1, 1), LENGTH(ENAME), '*') AS 이름처리 
    FROM EMP
WHERE LENGTH(ENAME) = 5;

 

--결과는 사원번호, 이름, 급여, 일당, 시급 순으로 출력하고
--일당은 소수 세째자리에서 버림
--시급은 소수 둘째자리에서 반올림

SELECT EMPNO, ENAME, SAL,--사원번호, 이름, 급여
     TRUNC(SAL/20, 2) AS 일당, --일당
    ROUND(SAL/20/8, 1) AS 시급 --시급
FROM EMP;

 

 

--EMP 테이블에서 입사일을 기준으로 3개월이 지난 첫째주 월요일에 정직원이 된다
--사원들이 정직원이 되는 날짜를 YYYY-MM-DD형식으로 출력
--단, 추가수당이 없는 사원의 추가 수당은 'N/A'로 출력
--결과는 사원번호, 이름, 입사일, 정직원날짜, 추가수당 순으로 출력

SELECT EMPNO, ENAME, HIREDATE, --사원번호, 이름, 입사일
    TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 3), '월요일'), 'YYYY/MM/DD') AS 정직원날짜,--정직원 날짜 형식
    NVL(TO_CHAR(COMM), 'N/A') AS 추가수당 --추가수당
    FROM EMP; --EMP 테이블

 

 

SELECT EMPNO, ENAME, MGR,
    CASE
        WHEN MGR IS NULL THEN '0000'
        WHEN SUBSTR(MGR, 1, 2)='75' THEN '5555'
        WHEN SUBSTR(MGR, 1, 2)='76' THEN '6666'
        WHEN SUBSTR(MGR, 1, 2)='77' THEN '7777'
        WHEN SUBSTR(MGR, 1, 2)='78' THEN '8888'
        ELSE TO_CHAR(MGR)
    END AS 변환번호
FROM EMP;

 

 

-- 1. EMP테이블에서 COMM 의 값이 NULL이 아닌 정보 조회
SELECT *
    FROM EMP
WHERE COMM IS NOT NULL;

-- 2. EMP테이블에서 추가수당을 받지 못하는 직원 조회

SELECT * 
    FROM EMP
WHERE COMM IS NULL
    OR COMM = 0;

-- 3. EMP테이블에서 관리자가 없는 직원 정보 조회

SELECT *
    FROM EMP
WHERE MGR IS NULL;

-- 4. EMP테이블에서 급여를 많이 받는 직원 순으로 조회

SELECT *
    FROM EMP
ORDER BY SAL DESC;

-- 5. EMP테이블에서 급여가 같을 경우 추가수당을 내림차순 정렬 조회 (햇갈림)

SELECT *
    FROM EMP
ORDER BY SAL ASC, COMM DESC;

-- 6. EMP테이블에서 사원번호, 사원명,직급, 입사일 조회 (단, 입사일을 오름차순 정렬 처리)

SELECT EMPNO, ENAME, JOB, HIREDATE
    FROM EMP
ORDER BY HIREDATE;
      
-- 7. EMP테이블에서 사원번호, 사원명 조회 (사원번호 기준 내림차순 정렬)

SELECT EMPNO, ENAME
    FROM EMP
ORDER BY EMPNO DESC;

-- 8. EMP테이블에서 사번, 입사일, 사원명, 급여 조회 
-- (부서번호가 빠른 순으로, 같은 부서번호일 때는 최근 입사일 순으로 처리)

SELECT EMPNO, HIREDATE, ENAME, SAL
    FROM EMP
ORDER BY DEPTNO ASC, HIREDATE DESC;

-- 9. 오늘 날짜에 대한 정보 조회
   
SELECT SYSDATE FROM DUAL;
   
-- 10. EMP테이블에서 사번, 사원명, 급여 조회 
-- (단, 급여는 100단위까지의 값만 출력 처리하고 급여 기준 내림차순 정렬)

SELECT EMPNO, ENAME, ROUND(SAL, -2) AS 급여
    FROM EMP
ORDER BY 급여 DESC;

-- 11. EMP테이블에서 사원번호가 홀수인 사원들을 조회

SELECT *
    FROM EMP
WHERE MOD (EMPNO, 2)=1;

-- 12. EMP테이블에서 사원명, 입사일 조회 (단, 입사일은 년도와 월을 분리 추출해서 출력)
-- 12번은 하지마셈

SELECT ENAME,
    EXTRACT(YEAR FROM HIREDATE) AS 입사년도,
    EXTRACT(MONTH FROM HRIDATE) AS 입사월
FROM EMP;


-- 13. EMP테이블에서 9월에 입사한 직원의 정보 조회

SELECT *
    FROM EMP
WHERE TO_CHAR(HIREDATE, 'MM') = '09';
--WHERE EXTRACT(MONTH FROM HIREDATE) = '9'

-- 14. EMP테이블에서 81년도에 입사한 직원 조회

SELECT *
    FROM EMP
WHERE TO_CHAR(HIREDATE, 'YY') = '81';

-- 15. EMP테이블에서 이름이 'E'로 끝나는 직원 조회

SELECT *
    FROM EMP
WHERE SUBSTR(ENAME, -1, 1) = 'E'; 
--WHERE ENAME LIKE '%E';

-- 16. EMP테이블에서 이름의 세 번째 글자가 'R'인 직원의 정보 조회

SELECT *
    FROM EMP
WHERE ENAME LIKE '__R%';

-- 17. EMP테이블에서 사번, 사원명, 입사일, 입사일로부터 40년 되는 날짜 조회

SELECT EMPNO, ENAME, HIREDATE,
    ADD_MONTHS(HIREDATE, 40*12) AS "40년"
    FROM EMP;

-- 18. EMP테이블에서 입사일로부터 38년 이상 근무한 직원의 정보 조회

SELECT *
    FROM EMP
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE)/ 12 >= 41;

-- 19. 오늘 날짜에서 년도만 추출

SELECT EXTRACT(YEAR FROM STYSDATE) FROM DUAL;
//SELECT TO_CHAR(SYSDATE, 'YYYY')FROM DUAL;

 

- 단일 행 함수

그룹 함수

 

-- 총 레코드 수를 세줌
SELECT COUNT(*) FROM EMP;

 

SELECT SUM(SAL) FROM EMP;

 

SELECT SUM(DEPTNO), SUM(DISTINCT DEPTNO) FROM EMP;

 

SELECT SUM(SAL), SUM(COMM) FROM EMP;

 

-- 부서번호가 20인 사람들의 급여합계
SELECT SUM(SAL) FROM EMP WHERE DEPTNO = 20;

 

--직책이 MANAGER인 인원수 구하기
SELECT COUNT(*) FROM EMP WHERE JOB='MANAGER';

 

--추가수당이 없는 사람수
SELECT COUNT(*) FROM EMP WHERE COMM IS NULL OR COMM = 0;

 

--MAX최대, MIN최소, AVG평균
SELECT MAX(SAL), MIN(SAL), ROUND(AVG(SAL),1) FROM EMP;

 

그룹화

• GROUP BY

--부서번호별 급여의 평균 계산해서 출력
SELECT DEPTNO, ROUND(AVG(SAL),1)
    FROM EMP
GROUP BY DEPTNO;

 

--직책별 부서번호별 인원수와 급여의 합계를 출력
SELECT JOB, COUNT(*), SUM(SAL), DEPTNO
    FROM EMP
GROUP BY JOB, DEPTNO;

 

--직책별 급여 합계를 출력
--급여합계가 5000이상인것만 보고싶음
SELECT JOB, SUM(SAL)
    FROM EMP
GROUP BY JOB
    HAVING SUM(SAL)>=5000;

 

• SELECT 컬럼(*)
• FROM 테이블명
• WHERE 조건식
• GROUP BY 그룹화할 컬럼
• HAVING 그룹에 대한 조건식
• ORDER BY 정렬할 컬럼

 

평균을 계산하기전에 급여가 2000보다 작은건 제외시킨 후 급여평균이 게산됨

계산이 처리된 급여평균이 2500이상인 것만 결과로 출력

--부서번호별 직책별 급여가 2000이상인 급여평균을 출력
--단, 급여평균은 2500이상인 데이터만 표시
--부서번호 기준으로 오름차순, 직책을 기준으로 내림차순 정렬

SELECT DEPTNO, JOB, AVG(SAL)
    FROM EMP
WHERE SAL>=2000 -- 다 제외가 된 후에 평균 계산이 시작
GROUP BY DEPTNO, JOB --부서번호 기준, 직책 정렬
    HAVING AVG(SAL)>=2500
    ORDER BY DEPTNO, JOB DESC;

 

--부서번호별 급여평균, 최고급여, 최저급여, 인원수를 출력
--급여평균은 소수첫째자리에서 반올림
SELECT DEPTNO AS 부서번호,
    ROUND(AVG(SAL),0) AS 급여평균,
    MAX(SAL) AS 최고급여,
    MIN(SAL) AS 최저급여,
    COUNT(*) AS 인원수
    FROM EMP
GROUP BY DEPTNO;

 

 

--직책이 동일한 인원수가 3명 이상인 데이터만 직책, 인원수를 출력
SELECT JOB, COUNT(*)
    FROM EMP
GROUP BY JOB
HAVING COUNT(*)>=3;

 

- 복습

--emp테이블에서 직책별 인원수를 출력
SELECT JOB, COUNT(*)
    FROM EMP
GROUP BY JOB;

--입사일을 이용해서 입사년도별 급여의 합계를 출력
SELECT TO_CHAR(HIREDATE, 'YYYY'), SUM(SAL) --입사년도별 출력, 급여합계
    FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'); --입사년도별로 그룹

--추가수당이 있으면 O 없으면 X로 처리해서
--추가수당별 인원수를 출력
--인원수를 기준으로 오름차순 정렬

SELECT NVL2(COMM, 'O', 'X') AS 추가수당여부,
    COUNT(*) AS 추가수당별인원수 --특정 필드를 찝어서 쓸 경우 비어있는건 인식을 못하다보니 *로 표시
    FROM EMP
GROUP BY NVL2(COMM, 'O', 'X') --그룹화
ORDER BY 2; --어떤 열을 기준으로 정렬할지