오라클 내장함수 모음

오라클 내장함수

-- 04_Function_1.sql
-- 오라클 내장 함수
-- 1) 문자 함수
-- 사용법) 대문자 변환 : UPPER('문자열') -- 문자열 대신 컬럼명도 올 수 있음
--        소문자 변환 : LOWER('문자열')
--        첫글자만 대문자 변환 : INITCAP('문자열')
-- 위의 변환 : 원본을 조작하지 않음(화면에만 임시로 바뀜)
SELECT 'Oracle mania'
       ,UPPER('Oracle mania') AS 대문자
       ,LOWER('Oracle mania') AS 소문자
       ,INITCAP('Oracle mania') AS "첫글자만 대문자"
FROM DUAL;

-- 연습 1) 사원테이블에서(EMPLOYEE) 사원명(ENAME) 을 소문자로 출력하고,
--        직위(JOB) 은 첫글자만 대문자로 출력하세요
SELECT UPPER(ENAME)
      ,LOWER(ENAME)
      ,INITCAP(JOB)
FROM EMPLOYEE;

-- 연습 2) SCOTT 사원을 조회해서 사원번호(ENO), 사원명(ENAME), 부서번호(DNO) 출력하기
-- 단, SCOTT 이 대소문자 구분없이 입력된다고 가정하고 작성하세요
--WHERE ENAME = 'Scott' -- 어쨋든 SCOTT 사원의 정보를 화면에 출력하세요 
SELECT ENO, ENAME, DNO FROM EMPLOYEE
WHERE ENAME = UPPER('scott');

-- 1-2) 문자 길이를 RETURN 하는 함수
-- LENGTH(문자열) : 한글/영어 상관없이 문자개수를 세어서 RETURN 하는 함수
-- 코딩 언어셋 : 영어 표현(1BYTE), 한글 표현(3BYTE) [UTF-8 표현식(국제표준)]
SELECT LENGTH('Oracle mania')
      ,LENGTH('오라클매니아')
FROM DUAL;

-- 1-3) 문자 조작 함수 : 문자열 붙이기
-- 함수 : CONCAT('문자열','문자열2')
-- 기호 : '문자열' || '문자열2'
SELECT 'Oracle'
      ,'mania'
      ,CONCAT('Oracle','mania')
      ,'Oracle' || 'mania'
FROM DUAL;

-- 1-4) 문자 조작 함수 : 문자열 자르기
-- SQL : 인덱스번호(1부터 시작)
-- 사용법) SUBSTR(대상컬럼,시작위치,자를개수)
-- 단, 시작위치가 음수이면 뒤에서 셈)
SELECT SUBSTR('Oracle mania',4,3)
      ,SUBSTR('Oracle mania',-1,1)
FROM DUAL;

-- 예제 3) 이름이 N 으로 끝나는 사원명(ENAME) 화면에 출력하기
-- 단, SUBSTR() 함수를 이용하세요
-- 사원 : EMPLOYEE
-- (참고)
-- SQL 성능이 좋게 작성하는것이 어려움 
-- WHERE 왼편(함수 사용) = 오른쪽값 ==> 성능이 대폭 저하
SELECT * FROM EMPLOYEE
WHERE SUBSTR(ENAME, -1,1) = 'N';

-- 1-5) 문자열에서 대상 문자를 찾아서 인덱스번호를 RETURN 함수
-- SQL) 인덱스번호 : 1부터 시작
-- 사용법) INSTR('문자열','찾는문자')
SELECT INSTR('Oracle mania','a')
      ,INSTR('오라클매니아','라')
FROM DUAL;

-- 1-6) 컬럼에 어떤 문자를 붙이기 함수 : LPAD, RPAD
-- LPAD 함수 : 어떤 문자를 왼쪽에 채우기 함수
-- 사용법) LPAD(대상컬럼, 자리수, 채울문자)
--       ( 자리수에서 빈칸은 채울문자로 채워짐(왼쪽))
SELECT LPAD(SALARY, 10, '*')
FROM EMPLOYEE;

-- RPAD 함수 : 어떤 문자를 오른쪽에 채우기 함수
-- 사용법) RPAD(대상컬럼, 자리수, 채울문자)
--       ( 자리수에서 빈칸은 채울문자로 채워짐(오른쪽))
SELECT RPAD(SALARY, 10, '*')
FROM EMPLOYEE;

-- 1-7) 문자열에서 공백제거하기 : LTRIM, RTRIM, TRIM(*)
-- 사용법) LTRIM(컬럼명): 왼쪽 공백제거
-- 사용법) RTRIM(컬럼명): 오른쪽 공백제거
-- 사용법) TRIM(컬럼명): 양쪽 공백제거
SELECT 'Oracle mania'
      ,LTRIM('   Oracle mania   ')
      ,RTRIM('   Oracle mania   ')
      ,TRIM('   Oracle mania   ')
FROM DUAL;

 숫자 함수 / 날짜 함수

-- 04_Function_2.sql
-- 숫자 함수
-- 1) 반올림 : ROUND()
-- 사용법) ROUND(대상컬럼, 자리수)
-- 참고) 자리수가 음수일경우 일의 자리부터 반올림이 됨
SELECT 98.7654
     , ROUND(98.7654)     -- 0 자리에서 반올림
     , ROUND(98.7654, 2)  -- 2nd 자리에서 반올림
     , ROUND(98.7654, 1)  -- 1st 자리에서 반올림
     , ROUND(98.7654, -1) -- 일의 자리에서 반올림(참고)
FROM DUAL;

-- 2) TRUNC : 버림(내림), 특정자리수에서 버림
-- 사용법) TRUNC(대상컬럼, 자리수)
-- 참고) 자리수가 음수일경우 일의 자리부터 버림이 됨
SELECT 98.7654
     , TRUNC(98.7654)
     , TRUNC(98.7654, 2)
     , TRUNC(98.7654, -1)
FROM DUAL;

-- 3) MOD : 나머지 연산 함수 ( == % : 나머지 연산자(js))
-- 사용법) MOD(대상컬럼, 나눌숫자) : 결과 나머지가 RETURN 됨
SELECT MOD(31, 2)
     , MOD(31, 5)
     , MOD(31, 8)
FROM DUAL;

-- 연습 1) 모든 사원의 급여를(SALARY) 각각 500으로 나눈 나머지를 계산해서 출력하세요
-- 사원 : EMPLOYEE
-- 함수 : MOD() 이용
SELECT MOD(SALARY, 500)
FROM EMPLOYEE;

-- 날짜함수
-- 1) SYSDATE : 시스템에 저장된 현재 날짜를 RETURN 하는 함수(*****)
-- DB 의 자료형 : 문자열(VARCHAR2 형, CHAR 형), 숫자(NUMBER 형), 날짜(DATE 형)
-- 단, 오라클 기준
SELECT SYSDATE FROM DUAL;

-- 예제 1) 오늘 , 어제, 내일 날짜를 출력해 보세요
-- 참고) 날짜와 산술연산이 됨
SELECT SYSDATE - 1 AS 어제
     , SYSDATE     AS 오늘
     , SYSDATE + 1 AS 내일
FROM DUAL;

-- 예제 2) 사원테이블에서 근무일수 계산해서 조회하기
-- 공식 : 현재시간 - 입사일(HIREDATE) : 근무일수
-- 사원 : EMPLOYEE

SELECT ROUND(SYSDATE - HIREDATE) FROM EMPLOYEE;

-- 2) MONTHS_BETWEEN(현재날짜, 과거날짜) : 두 날짜 사이의 개월수를 RETURN 하는 함수
-- 예제 3) 각 사원들이 근무한 개월수 구하기
-- 날짜(시간) 연산 : 실수가 나옴(ROUND, TRUNC 고려)
SELECT ENAME, SYSDATE, HIREDATE
      , TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS 개월수
FROM EMPLOYEE;

결과 값 엑셀로 저장하기

-- 3) ADD_MONTHS(날짜컬럼, 숫자)
-- 예제 4) 입사일에서 6개월이 지난 날짜 구하기
SELECT ENAME, HIREDATE,
      ADD_MONTHS(HIREDATE, 6) AS 개월수더하기
FROM EMPLOYEE;

-- 4) NEXT_DAY(날짜컬럼, '요일')
--   날짜컬럼에서 최초로 도래하는 요일의 날짜를 RETURN 함수
-- 예제 5) 오늘을 기준으로 최초로 도래하는 토요일의 날짜 구하기
SELECT SYSDATE
      ,NEXT_DAY(SYSDATE, '토요일')
FROM DUAL;

-- 5) LAST_DAY(날짜컬럼)
-- 날짜컬럼의 그 달(월)의 마지막 날의 날짜를 구해주는 함수
-- 예제 6) 입사한 날의 그 달의 마지막 날 구하기
SELECT ENAME, HIREDATE
      ,LAST_DAY(HIREDATE)
FROM EMPLOYEE

 

※ 자료형 변환 함수

-- 04_Function_3.sql

-- DB 자료형 : 문자열(VARCHAR2(가변문자열), CHAR(고정문자열))
--            숫자(NUMBER, 실수, 정수)
--            날짜(DATE)
-- 1) TO_CHAR : 날짜 -> 문자열로 바꾸는 함수
-- 사원 : EMPLOYEE
-- 날짜컬럼(DATE) : HIREDATE(입사일)
-- 사용법) TO_CHAR(컬럼명, '날짜포맷')
-- 날짜포맷 : YYYY(년도(4자리)), YY(2자리) [YEAR]
--           MM(월(2자리))               [MONTH]
--           DD(일(2자리))               [DAY]
--           DAY(요일)                   [요일]
--           HH(1~12까지의시간)           [HOUR]
--           HH24(1~24까지의시간)         [HOUR]
--           MI(분(2자리)                [MINUTES]
--           SS(초(2자리))               [SECOND]
SELECT ENAME, HIREDATE
       ,TO_CHAR(HIREDATE, 'YY-MM') AS 단축날짜
       ,TO_CHAR(HIREDATE, 'YYYY-MM-DD HH24:MI:SS') AS 날짜
FROM EMPLOYEE;

-- 연습 1) 현재 날짜와 시간을 표현하세요(날짜 -> 문자열로 변환하세요)
-- 단 출력포맷 예) 2011/05/01, 15:07:43  으로 출력하세요
-- 힌트) 현재날짜 : SYSDATE(날짜형)
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS') FROM DUAL;

-- 2) TO_CHAR : 숫자 -> 문자로 변환
-- 사용법) TO_CHAR(숫자컬럼, '숫자포맷')
-- 예제 2) 급여를 출력하면서 통화기호를 (W, $ 등) 포함해서 출력하세요
-- 숫자포맷 : L - 각 나라별 통화 기호를 자동으로 붙여줌(W, $ 등)
--           9 - 3자리를 잡고 자리수가 모자라도 따로 0을 채워주지 않음
--           0 - 3자리를 잡고 자리수가 모자라면 0을 채움
--           , - 각 통화의 중간에 쉼표(,)를 자동으로 추가해줌
SELECT ENAME, SALARY
      ,TO_CHAR(SALARY, 'L999,999')
      ,TO_CHAR(SALARY, 'L000,000')
FROM EMPLOYEE;

 

-- 3) TO_DATE(문자[숫자], 날짜포맷) : 문자[숫자] -> 날짜형으로 바꾸어 주는 함수
-- HIREDATE 컬럼 : 날짜형
SELECT ENAME, HIREDATE
FROM EMPLOYEE
--WHERE HIREDATE = '19810220'; -- 자동 자료형 변환(내부적으로 문자 -> 날짜형으로 바뀜)
WHERE HIREDATE = TO_DATE('19810220', 'YYYYMMDD');

-- 4) TO_NUMBER(문자, 숫자포맷) : 문자 -> 숫자로 변환하는 함수
-- 예제 4) 숫자 형태의 문자(통화) 빼기
SELECT TO_NUMBER('100,000','999,999') - TO_NUMBER('50,000','999,999') 
FROM DUAL;

 

-- 일반 함수들
-- 1) NVL : NULL -> 숫자[문자]로 변경하는 함수
-- 상여금 컬럼 : COMMISSION (NULL 포함됨)
-- NULL 은 산술연산이 안됨 
SELECT ENAME, SALARY, COMMISSION
      ,NVL(COMMISSION, 0)
      ,SALARY*12+NVL(COMMISSION, 0)
FROM EMPLOYEE
ORDER BY JOB;

-- 2) DECODE 함수 : SQL 에서 조건문을 표시하는 함수
-- DECODE 로 부서이름 출력하기
-- 사원명   : ENAME
-- 부서번호 : DNO
-- 사용법) DECODE(DNO, 조건1, 결과1
--                 , 조건2, 결과2
--                 ...
--                 ,기본결과)
-- 설명) DNO 컬럼에 대해 조건1이 참이면 결과1이 출력되고
--                     조건2가 참이면 결과2가 출력되고
--                     모두 거짓이면 기본결과가 출력됨
SELECT ENAME, DNO
      ,DECODE(DNO, 10, '회계부'
                 , 20, '연구소'
                 , 30, '판매부'
                 , 40, '운영부'
                 ,'디폴트') AS 부서명
FROM EMPLOYEE
ORDER BY DNO;

-- DECODE 업그레이드 사용법) CASE WHEN (결과는 같음)
-- 사용법) CASE WHEN 조건식1 THEN '결과1'
--             WHEN 조건식2 THEN '결과2'
--             ...
--             ELSE '기본결과'
--        END
SELECT ENAME, DNO
      ,CASE WHEN DNO=10 THEN '회계부'
           WHEN DNO=20 THEN '연구소'
           WHEN DNO=30 THEN '판매부'
           WHEN DNO=40 THEN '운영부'
           ELSE '디폴트'
       END AS 부서명
FROM EMPLOYEE
ORDER BY DNO;

 

연습문제

-- 04_Function_Exam.sql
-- 연습문제
-- 1) SUBSTR 함수를 사용하여 사원들의 
--  입사한 년도와(HIREDATE) 입사한 달만(HIREDATE) 출력하세요.
-- 사용법) SUBSTR(컬럼명, 인덱스위치, 잘라낼개수) 
-- : 문자열 자르기 함수, 날짜형 컬럼도 자르기가 가능
-- 사원테이블 : EMPLOYEE

SELECT HIREDATE
      ,SUBSTR(HIREDATE, 1, 2) AS 년도
      ,SUBSTR(HIREDATE, 4, 2) AS "달(월)"
FROM EMPLOYEE;

-- 2) 입사일(HIREDATE) 연도는 2자리(YY), 월은 (MON)로 표시하고, 
--    일(DD), 요일은 (DY)로 표기하세요. (날짜포맷)
-- TO_CHAR(날짜컬럼, '날짜포맷') : 날짜 -> 문자로 바꾸는 함수 사용
-- 출력 예) 80/12월/17 수
-- 사원테이블 : EMPLOYEE

SELECT HIREDATE
      ,TO_CHAR(HIREDATE, 'YY/MON/DD DY')
FROM EMPLOYEE;

-- 3) 올해 며칠이 지났는지 출력하시오. 
--    현재 날짜에서 올해 1월1일을 뺀 결과를 출력하고 
--    TO_DATE 함수를 사용하여 데이터 형을 일치시켜서 연산하세요
-- 힌트) 현재날짜 - '2023/01/01'(날짜로 변환)
-- 단, 포맷은 'YYYY/MM/DD' (참고), 버림 적용(TRUNC())

SELECT TRUNC(SYSDATE - TO_DATE('2023/01/01', 'YYYY/MM/DD'))
FROM DUAL;

SELECT HIREDATE, TO_DATE(HIREDATE, 'YYYY/MM/DD') AS "고용날짜", TO_DATE(SYSDATE, 'YYYY/MM/DD') AS "지금",
TO_DATE(SYSDATE, 'RRRR/MM/DD') - TO_DATE(HIREDATE, 'RRRR/MM/DD') AS "차이" 
FROM EMPLOYEE;

 

RRRR은
--50 ~ 99 이면 2000년대
--00 ~ 49 이면 1900년대

-- 4) 사원들의(EMPLOYEE) 매니저(MANAGER) 사번을(ENO) 출력하되 매니저가 없는 
--    사원에 대해서는 NULL값 대신에 0을 출력하세요. ( NVL함수 사용하라 )

SELECT ENO, ENAME, NVL(MANAGER, 0)
FROM EMPLOYEE;

-- 5) DECODE(CASE WHEN) 함수로 직급에(JOB) 따라 
--    급여를(SALARY) 인상하도록 하세요.
--    직급이(JOB) 'ANALYST' 이면 200, 
--    'SALESMAN' 이면 180, 'MANAGER' 이면 150
--    'CLERK' 이면 100을 인상하세요.
-- 출력 : ENO(사원번호), ENAME(사원명), JOB(직급), SALARY(월급),
--       급여인상 
-- 사원테이블 : EMPLOYEE

SELECT ENO, ENAME, JOB, SALARY
      ,DECODE(JOB, 'ANALYST', SALARY + 200
                 , 'SALESMAN', SALARY + 180
                 , 'MANAGER', SALARY + 150
                 , 'CLERK', SALARY + 100
                 , SALARY) AS 급여인상
FROM EMPLOYEE;

SELECT ENO, ENAME, JOB, SALARY, 
CASE WHEN JOB='ANALYST' THEN SALARY+200
     WHEN JOB ='SALESMAN' THEN SALARY+180
     WHEN JOB ='CLERK' THEN SALARY + 100
     ELSE SALARY
     END AS "급여인상"
FROM EMPLOYEE;

-- 6) 사원테이블에서(EMPLOYEE) 사원들의 월 평균 근무일 수는 21.5일입니다. 
-- 하루 근무 시간을 8시간으로 보았을 때 
-- 사원들의 하루 급여와 시급을 계산하여 
-- 사원번호(ENO), 사원명(ENAME), 급여(SALARY), 하루급여, 시급 결과를 출력합니다.
-- 단 하루 급여는 소수점 세 번째 자리에서 버리고, 시급은 두 번째 소수점에서 반올림하세요
-- 공식 : 하루 급여 : 급여(SALARY) / 21.5
--       시급     : 급여 / 21.5 / 8

SELECT ENO, ENAME, SALARY,
TRUNC(SALARY / 21.5, 2) AS "하루 급여",
ROUND(SALARY / 21.5  / 8, 1) AS "시급"
FROM EMPLOYEE;

-- 7) 사원테이블에서(EMPLOYEE) 사원들은 입사일을 기준으로 3개월이 지난 후 (조건1: ADD_MONTHS())
--  첫 월요일에 정직원이 됩니다.(조건2 : NEXT_DAY())
-- 사원들이 정직원이 되는 날짜를 YYYY-MM-DD 형식으로(조건3 : TO_CHAR(날짜, 포맷)) 출력해 주세요.
-- 단, 상여금이(COMMISSION) 없는 사원의 상여금은 N/A로 출력하세요
-- 힌트) ADD_MONTHS(), NEXT_DAY(), TO_CHAR(), NVL()
-- 힌트) TO_CHAR(NEXT_DAY(ADD_MONTHS(컬러명, 더할개월수),'월요일'), 'YYYY-MM-DD')

SELECT ENO, ENAME, HIREDATE
      ,TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 3),'월요일'), 'YYYY-MM-DD') AS R_JOB
      ,NVL(TO_CHAR(COMMISSION), 'N/A') AS COMMISSION
FROM EMPLOYEE;

-- 8) 사원 테이블의 모든 사원을 대상으로 직속 상관의 사원 번호를 다음과 같은 조건을 기준으로 
--    변환해서 CHG_MGR 별칭으로 출력하세요
-- 조건) DECODE, CASE WHEN 사용
-- 1) 직속 상관의 사원 번호가 존재하지 않을 경우    : 0000
-- 2) 직속 상관의 사원 번호 앞 두 자리가 75일 경우 : 5555
-- 3) 직속 상관의 사원 번호 앞 두 자리가 76일 경우 : 6666
-- 4) 직속 상관의 사원 번호 앞 두 자리가 77일 경우 : 7777
-- 5) 직속 상관의 사원 번호 앞 두 자리가 78일 경우 : 8888
-- 6) 그 외 직속 상관 사원 번호의 경우           : 본래 직속 상관의 사원 번호 그대로 출력

SELECT ENO, ENAME, MANAGER,
CASE WHEN MANAGER IS NULL THEN 0000
                 WHEN MANAGER LIKE '75__' THEN 5555
                 WHEN MANAGER LIKE '76__' THEN 6666
                 WHEN MANAGER LIKE '76%' THEN 7777
                 WHEN MANAGER LIKE '78%' THEN 8888
                 ELSE MANAGER
                 END AS CHG_MGR
FROM EMPLOYEE;

SELECT ENO, ENAME, MANAGER,
CASE WHEN MANAGER IS NULL THEN '0000'
                 WHEN MANAGER LIKE '75__' THEN '5555'
                 WHEN MANAGER LIKE '76__' THEN '6666'
                 WHEN MANAGER LIKE '76%' THEN '7777'
                 WHEN MANAGER LIKE '78%' THEN '8888'
                 ELSE TO_CHAR(MANAGER)
                 END AS CHG_MGR
FROM EMPLOYEE;

SELECT ENO, ENAME, MANAGER
      ,CASE WHEN MANAGER IS NULL THEN '0000'
           WHEN SUBSTR(MANAGER,1,2) = '75' THEN '5555'
           WHEN SUBSTR(MANAGER,1,2) = '76' THEN '6666'
           WHEN SUBSTR(MANAGER,1,2) = '77' THEN '7777'
           WHEN SUBSTR(MANAGER,1,2) = '78' THEN '8888'
           ELSE TO_CHAR(MANAGER)
       END AS CHG_MGR
FROM EMPLOYEE;