함수(Function)

Oracle

함수(Function)

기정님 2025. 5. 12. 10:50

함수(Function)

-- 함수: 컬럼의 값을 읽어서 계산한 결과를 리턴
-- 단일행 함수: n개의 값을 읽어서 n개의 결과 리턴
-- 그룹 함수: n개의 값을 읽어서 1개의 결과 리턴
-- 함수를 사용할 수 있는 위치 : 셀렉트 where 그룹바이 해빙 오더바이

문자 처리 함수

-- 문자 관련 함수
-- LENGTH/LENGTHB
SELECT LENGTH('오라클'),LENGTHB('오라클') FROM DUAL; -- DUAL 가상테이블
SELECT LENGTH(EMAIL), LENGTHB(EMAIL) FROM EMPLOYEE;

-- INSTR
SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL; -- 처음으로 발견된 위치  3번째인자 시작위치, 4번째인자 몇번에 등장하는거 고를지
SELECT INSTR(EMAIL, '@') FROM EMPLOYEE; -- 이메일의 @이 어디에 있는지

-- LPAD/RPAD
SELECT LPAD(EMAIL,20), RPAD(EMAIL,20,'#') FROM EMPLOYEE; -- 꽉꽉 채워서 내보내기

-- LTRIM/RTRIM
SELECT LTRIM ('     KH') A FROM DUAL; -- 왼쪽부터 삭제

-- SUBSTR 일부 문자열 반환
SELECT SUBSTR('HELLOMYGOODEFRIENDS', -8,3) FROM DUAL;
SELECT EMP_NAME, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') - 1) FROM EMPLOYEE; -- 이름 ,이메일 @ 이후를 제외한 아이디 조회
SELECT EMP_NAME, SUBSTR(EMP_NO, 1, INSTR(EMP_NO, '-') + 1) FROM EMPLOYEE; --이름과 주민번호에서 성별을 나타내는 부분 조회
SELECT EMP_NAME, SUBSTR (EMP_NO, INSTR(EMP_NO, '-')+1,1) 성별 FROM EMPLOYEE; --이름과 주민번호에서 성별을 나타내는 부분 조회
SELECT EMP_NAME, SUBSTR (EMP_NO, 1,2) 생년, SUBSTR(EMP_NO,3,2) 생월, SUBSTR(EMP_NO,5,2) 생일 FROM EMPLOYEE;
SELECT EMP_NAME, '남자' 성별
FROM EMPLOYEE
WHERE SUBSTR (EMP_NO, INSTR(EMP_NO, '-')+1,1) IN (1,3,5,7);

-- LOWER/UPPER/INITCAP = 대문자 소문자 만들기
-- CONCAT = 문자열 합치기
SELECT CONCAT('가나다라','ABC') FROM DUAL;

-- REPLACE
SELECT REPLACE('서울시 강남구 역삼동','역삼동','삼성동') FROM DUAL;
SELECT EMP_NAME, SUBSTR(EMP_NO,1,6)||'-*******'  FROM EMPLOYEE; -- 생년월일 다음에는 *로 보이게 하기
SELECT EMP_NAME, SUBSTR(EMP_NO, 1, INSTR(EMP_NO,'-')) ||'*******'  FROM EMPLOYEE;
SELECT EMP_NAME, CONCAT(SUBSTR(EMP_NO, 1, INSTR(EMP_NO,'-')),'*******')  FROM EMPLOYEE;
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO, 1, INSTR(EMP_NO,'-')),LENGTH(EMP_NO) ,'*')  FROM EMPLOYEE;

SELECT EMP_NAME, REPLACE(EMP_NO, SUBSTR(EMP_NO, INSTR(EMP_NO, '-')+1), '*******')
FROM EMPLOYEE;

숫자 처리 함수

-- ABS ( 절대값 )
SELECT ABS(10.9), ABS(-10.9), ABS(10), ABS(-10) FROM DUAL;

-- MOD ( 나눈 나머지 )
SELECT MOD(10,3), MOD(-10,3) FROM DUAL;

-- ROUND ( 반올림 )
SELECT ROUND(123.456) FROM DUAL;

-- FLOOR, TRUNC, CEIL
SELECT FLOOR(123.456), FLOOR(123.856) FROM DUAL; -- 소숫점 버림
SELECT CEIL(123.456), CEIL(123.856) FROM DUAL; -- 무조건 올림
SELECT TRUNC(123.456), TRUNC(123.856) FROM DUAL; -- 절삭

날짜 처리 함수

-- 날짜 관련 함수
-- MONTHS_BETWEEN = 개월 수 차이
SELECT EMP_NAME, HIRE_DATE, CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE))||'개월차'  -- 근무 개월수 조회
FROM EMPLOYEE;

-- ADD_MONTHS = 달 추가 하기
SELECT ADD_MONTHS(SYSDATE,5) FROM DUAL;

-- NEXT_DAY = 가장 가까운 요일 반환하기
SELECT SYSDATE, NEXT_DAY(SYSDATE,'목요일') FROM DUAL;

-- LAST_DAY 사원의 이름, 입사일, 입사한 달의 마지막 날 조회
SELECT EMP_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE)
FROM EMPLOYEE;

-- EXTRACT EMPLOYEE테이블에서 사원의 이름, 입사 년, 입사 월, 입사 일 조회
SELECT EMP_NAME, EXTRACT(YEAR FROM HIRE_DATE) YEAR,
EXTRACT(MONTH FROM HIRE_DATE) MONTH,
EXTRACT(DAY FROM HIRE_DATE) DAY
FROM EMPLOYEE;

형 변환 함수

-- 형변환 함수
-- TO_CHAR : 날짜/숫자 데이터를 문자 데이터로 변경
SELECT 1234, TO_CHAR(1234) FROM DUAL; -- 숫자는 오른쪽 문자는 왼쪽 정렬해서 나온다
SELECT TO_CHAR(1234) + 4321 FROM DUAL; --5555
SELECT TO_CHAR(1234, '999999') A, 
TO_CHAR(1234,'FM00000') B,
TO_CHAR(1234, 'FML99,999') C, 
TO_CHAR(1234, 'FM$99,999') D
FROM DUAL;

-- 테이블에서 사원명 급여 9,000,000 형식 조회
SELECT EMP_NAME, TO_CHAR(SALARY,'FML999,999,999') 급여  --FM 공백지우기
FROM EMPLOYEE;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY HH:MI:SS') FROM DUAL; --DATE를 CHAR로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY"년" FMMM"월" DD"일" DAY HH24:MI:SS') FROM DUAL; --DATE를 CHAR로 변환

SELECT TO_CHAR(SYSDATE, 'YYYY'),TO_CHAR(SYSDATE, 'YY'),TO_CHAR(SYSDATE, 'YEAR') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MM'),TO_CHAR(SYSDATE, 'MONTH'),TO_CHAR(SYSDATE, 'MON'),TO_CHAR(SYSDATE, 'RM') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DDD')년,TO_CHAR(SYSDATE, 'DD')달,TO_CHAR(SYSDATE, 'D')주 FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'Q')분기,TO_CHAR(SYSDATE, 'DAY'),TO_CHAR(SYSDATE, 'DY') FROM DUAL;

-- TO_DATE : 문자/숫자 데이터를 날짜 데이터로 변환
SELECT TO_DATE('20000131','YYYYMMDD') FROM DUAL;
SELECT TO_DATE('250919 175014','YYMMDD HH24MISS') FROM DUAL; --시분초 저장이 가능하지만 보여주지는 않는다
SELECT TO_CHAR(TO_DATE('250919 195014','YYMMDD HH24MISS'), 'YYYY"년" FMMM"월" DD"일" AM DAY HH:MI:SS') FROM DUAL;
-- YY와 RR의 차이 YY는 2000년도지만 RR은 2000년도 또는 1900년도가 되는데 50이상이면 20세기 그 아래면 21세기가 뜬다

-- TO_NUMBER : 문자  데이터를 숫자로 변환 
SELECT TO_NUMBER('123456') FROM DUAL;
SELECT '123' + '321' FROM DUAL;
SELECT TO_NUMBER('1,000,000','999,999,999') + TO_NUMBER('2,000,000','999,999,999') FROM DUAL;

NULL 처리 함수

선택 함수

-- 선택함수
-- DECODE (계산식| 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2)
SELECT EMP_ID, EMP_NAME, EMP_NO,
DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') AS 성별
FROM EMPLOYEE;

-- 직원의 급여를 인상하고자 한다
-- 직급코드가 J7인 직원은 급여의 10%를 인상하고, 직급코드가 J6인 직원은 급여의 15%를,
-- 직급코드가 J5인 직원은 급여의 20%를 인상하고, 그 외 직급의 직원은 급여의 5%만 인상한다.
-- 테이블에서 직원명, 직급코드 ,급여, 인상급여 조회
SELECT 
    EMP_NAME, 
    JOB_CODE, 
    SALARY,
    DECODE(JOB_CODE, 
           'J7', SALARY * 1.10,
           'J6', SALARY * 1.15,
           'J5', SALARY * 1.20,
           SALARY * 1.05) AS 인상급여
FROM EMPLOYEE;

SELECT EMP_ID, EMP_NAME, EMP_NO,
    CASE 
    WHEN SUBSTR(EMP_NO, 8, 1) = 1 THEN '남'
    ELSE '여'
    END AS 성별
FROM EMPLOYEE;

SELECT EMP_NAME, SALARY,
    CASE 
    WHEN SALARY > 5000000 THEN '1등급'
    WHEN SALARY > 3500000 THEN '2등급'
    WHEN SALARY > 2000000 THEN '3등급'
    ELSE '4등급'
    END 등급
FROM EMPLOYEE;

그룹 함수

-- 그룹 함수
-- SUM
SELECT SUM(SALARY) FROM EMPLOYEE;
SELECT SUM(SALARY) 
FROM EMPLOYEE 
WHERE SUBSTR(EMP_NO, INSTR(EMP_NO, '-')+1,1) = 1;

--AVG
SELECT AVG(SALARY) FROM EMPLOYEE;
SELECT AVG(NVL(BONUS, 0)) FROM EMPLOYEE;

-- MIN/MAX
SELECT MAX(SALARY), MIN(SALARY)
FROM EMPLOYEE;
SELECT MAX(HIRE_DATE), MIN(HIRE_DATE)
FROM EMPLOYEE;


-- COUNT 
SELECT COUNT(*)
FROM EMPLOYEE;

SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE
WHERE DEPT_CODE = ‘D5’;

SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;