3. ORACLE 함수(Function) :: 단일행 함수 정리

함수(Function)?

하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리해서 작성한 서브 프로그램

호출하며 값을 전달하면 결과를 리턴해줌

1) 유형

단일행 함수와 그룹 함수로 나뉘어짐

모두 원본 데이터를 수정하지는 않고, 해당 함수를 따른 가상 컬럼을 출력해주는 것임

단일행 함수

각 행마다 반복적으로 적용 → 입력받는 행 개수만큼 결과 반환

• 자료형에 따라 제공되는 함수들이 별도로 존재
  • 자료형: 문자, 숫자, 날짜
• 조건에 따라 출력하는 선택 함수가 포함된다

그룹 함수

특정 행들의 집합(테이블)에 그룹이 형성되어 적용 → 그룹(테이블)당 1개의 결과 반환

• 데이터를 집계할 때 사용하는 편
  • 합계, 평균, 최대, 최소, 갯수 등

2) 단일행 함수

SELECT문의 컬럼 위치에 사용 가능

WHERE절에 조건으로 사용 가능

INSERT, UPDATE, DELETE 예약어 사용 가능

(1) 문자처리함수

1. 문자 갯수 세기

LENGHT(’문자’ 또는 칼럼)

CHAR | STRING의 길이(문자 개수) 반환

리턴타입 NUMBER

LENGHTB(’문자’ 또는 칼럼)

CHAR | STRING의 길이(BYTE) 반환

• 한글은 3바이트로 출력됨

리턴타입 NUMBER

-- 사원중 이메일 15글자 이상인 사람만 조회: 이름, 이메일, 이메일 길이
SELECT EMP_NAME, EMAIL, LENGTH(EMAIL) AS EMAIL_LENGHT
FROM EMPLOYEE
WHERE LENGTH(EMAIL) >= 15;

-- 문자열 바이트 수 출력 함수: LENGTHB
-- express 버전에서만 한글을 2바이트로 안하고 3바이트로 함
SELECT LENGTHB('ABCDE'), LENGTHB('아야오요')
FROM DUAL;

2. 문자열 찾기

1. INSTR(’문자열’/컬럼명, 찾을 문자[,시작위치, 찾을 횟수])
   : 지정 위치부터 지정 숫자 번째로 나타나는 문자의 시작 위치 반환
2. LPAD, RPAD(문자열/컬럼명, 최대길이, 채울문자)
   : 문자열의 길이가 지정된 길이만큼 저장되지 않을 경우 특정값으로 채우는 함수
   
   • 여기서는 한글이 2바이트로 계산됨
   • LPAD는 왼쪽에 붙고 RPAD는 오른쪽에 붙음
3. LTRIM(문자열/컬럼명[,문자]) → 공백 또는 지정문자 제거
4. 비교연산자 LIKE ‘찾을 내용 및 형식' → 포함되는 글자 찾기

--INSTR
SELECT INSTR('GD지드래곤 GD게임즈 GD음악사 GD지디지드래곤!', 'GD'), -- 1 반환
        -- 찾을 위치 지정 가능 : 3번째 GD의 위치
        INSTR('GD지드래곤 GD게임즈 GD음악사 GD지디지드래곤!', 'GD', 3), 
        -- (마이너스 값은 역순이라서 -1은 맨끝, 문자열을 오버할 경우 계속 순환) :  3번째 매개변수의 +-는 시작방향을 의미
        INSTR('GD지드래곤 GD게임즈 GD음악사 GD지디지드래곤!', 'GD', -10), --14 출력
        -- 반복 지정 빈도
        INSTR('GD지드래곤 GD게임즈 GD음악사 GD지디지드래곤!', 'GD', 1, 4) -- 1번째 위치, 4번째 빈도 20 출력
FROM DUAL;

--LPAD
SELECT LPAD('박댕댕', 10, '*'), RPAD('박댕댕', 9, '#'), RPAD('박댕댕', 6, '#') 
FROM DUAL;
-- ****박댕댕, 박댕댕###, 박댕댕 출력

SELECT '22222222댕댕', LTRIM('22222222댕댕2222', '2'), LTRIM('135777뿅', '0123456789') , LTRIM('1357ㅇ77뿅', '0123456789')
FROM DUAL; 
-- 22222222댕댕	댕댕2222	뿅 ㅇ77뿅 출력
-- 왼쪽것만 지워짐

-- LTRIM
SELECT '21435653412453금요일 화이팅 123329138121897',
    RTRIM(LTRIM('21435653412453금요일 화이팅 123329138121897', '0123456789'), '0123456789')
    -- TRIM은 한개 문자만 가능해서 이용이 불가능함
FROM DUAL;
--금요일 화이팅 출력

--유씨 성이 아닌 사원 조회 : NOT LIKE
SELECT *
FROM EMPLOYEE
WHERE EMP_NAME NOT LIKE '유%';

3. 문자열 정돈하기

1. TRIM([LEADING / TRAILING / BOTH] 문자열/컬럼명) : 양쪽에 있는 값을 제거하는 함수
   • 왼쪽, 오른쪽, 양쪽 모두 가능
   • 디폴트: 공백
     • 설정하면 설정값을 제거해줌 (한개 문자만 설정 가능)
2. SUBSTR(문자열/컬럼명, 시작인덱스[, 잘라낼길이]) : 원하는 부분 잘라내기 → 활용에 따라 해당 부분만 조건문을 걸수도 있다

SELECT '     금요일     ', TRIM('     금요일     '),
        'dddddd태권ddddd', TRIM(LEADING 'd' FROM 'dddddd태권ddddd'),
        'dddddd태권ddddd', TRIM(TRAILING 'd' FROM 'dddddd태권ddddd'),
        'dddddd태권ddddd', TRIM(BOTH 'd' FROM 'dddddd태권ddddd')
FROM DUAL;

SELECT SUBSTR('금요일은 너무 행복해 주말에 공부할 시간이 많잖아 하하', 6),
SUBSTR('금요일은 너무너무 행복해 주말에 공부할 시간이 많잖아 하하', 6, 8),
SUBSTR('금요일은 너무너무 행복해 주말에 공부할 시간이 많잖아 하하', -2) -- 음수면 뒤에서부터
FROM DUAL;

-- 사원 이메일에서 아이디만 출력하기
SELECT EMAIL, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@')-1)
FROM EMPLOYEE;

-- 이메일의 아이디가 7자 이상인 사원 이름, 이메일 조회
SELECT EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE LENGTH(SUBSTR(EMAIL, 1, INSTR(EMAIL, '@')-1)) >= 7;

-- 사원 중 여사원만 출력
SELECT *
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2'; --정확한 비교를 하려면 문자열로 적어줘야한다
--다른 함수도 이용한 예: SUBSTR(EMP_NO, INSTR(EMP_NO, '-') + 1, 1) = '2';

4. 문자열 변경해서 출력하기

	구분	설명
1	CONCAT(’A’, ‘B’)	문자열 결합
2	REPLACE(문자열/컬럼명, 찾을문자, 교체문자)	문자열 교체 출력
3	REVERSE(문자열/컬럼명)	문자열을 역으로 출력

5. 알파벳 대문자, 소문자 전환

	구분	설명
1	UPPER(’문자열’)	모두 대문자
2	LOWER(’문자열’)	모두 소문자
3	INITCAP(’문자열’)	띄어쓰기 직후를 대문자로

SELECT 'Welcome to oRACLE world',
    UPPER('Welcome to oRACLE world'),
    LOWER('Welcome to oRACLE world'),
    INITCAP('Welcome to oRACLE world')
FROM DUAL;

 

(2) 숫자 처리 함수

1. ABS(INT) → 부호 전환
2. MOD(x, y) → 나눗셈 나머지 반환
   1. 소수점 처리
      1. ROUND(숫자/컬럼명[, 자리수]) → 소수점 반올림
      2. FLOOR(숫자/컬럼명) → 소수점 다 버리기(정수로 변환)
      3. TRUNC(숫자/컬럼명, 자리수) → 소수점 지정수만큼만 버림
      4. CEIL(숫자/ 컬럼명) → 소수점 무조건 올림소수점 처리
   2. 랜덤 뽑기
      
      1. DBMS_RANDOM.VALUE(x, y) → x부터 y까지의 실수 출력
         • FLOOR와 함께 사용해서 정수 출력 가능

DBMS_RANDOM.STRING(형식, 갯수) → 랜덤 문자 출력

• ‘X’는 숫자, 대문자
• ‘P’는 특수기호 포함한 영문자 숫자
• ‘U’ 대문자
• ‘L’ 소문자

-- 숫자처리함수
-- ABS: 절대값 부호없는 값으로
SELECT ABS(10), ABS(-10)
FROM DUAL;

-- MOD: 나눗셈의 나머지 출력
SELECT MOD(3,2)
FROM DUAL;

-- 소수점 처리 함수
-- ROUND: 소수점 반올림 (반올림할 숫자[, 자리수])
SELECT ROUND(3.6235325, 3) -- 3.624 출력
FROM DUAL;
-- FLOOR: double -> int , 무조건 소수점 버리고 정수로 변환
SELECT FLOOR(3.55555555)
FROM DUAL;
-- TRUNC: 소수점 버리지만 자리수 지정 가능
SELECT TRUNC(3.55555555, 2)
FROM DUAL;
-- CEIL : 소수점 무조건 올림
SELECT CEIL(125.3424)
FROM DUAL;
-- DBMS_RANDOM.VALUE() 함수 : 랜덤숫자 출력 -> 0~1 사이의 실수 랜덤
SELECT DBMS_RANDOM.VALUE(), FLOOR(DBMS_RANDOM.VALUE() * 10) +1,
    FLOOR(DBMS_RANDOM.VALUE(0,10)) --> 0부터 10
FROM DUAL;

 

(3) 날짜 처리 함수

1. 기본 날짜 출력

날짜끼리 산술 연산처리도 가능하다

출력 형식 바꿀 수 있음

1. SYSDATE → 시스템의 년, 월, 일
   • java.sql.Date랑 연결됨(JDVC)
2. SYSTIMESTAMP → 오늘 년, 월, 일, 시, 분, 초
   • java.sql.TimeStamp랑 연결됨(JDVC)
3. NEXT_DAY(기준일, ‘토’) → 지정 요일과 가까운 날자 반환
   1. 2번째 매개변수는 LOCALE값을 가지고 국가에 맞는 언어를 사용해야함
4. LAST_DAY(날짜) → 그 달의 마지막 날 출력
5. ADD_MONTHS(날짜, 더할 수) → 개월수를 더하는 기능
6. MONTHS_BETWEEN(날자, 날자) → 두 날짜 사이의 개월수
7. EXTRACT(YEAR, MONTH, DAY FROM 날짜) → 년, 월, 일 따로 출력 (NUMBER)

-- 날짜 처리 함수
SELECT SYSDATE, SYSTIMESTAMP
FROM DUAL;
-- 24/03/15,	24/03/15 03:45:16.619058000 GMT 출력

-- NEXT DAY :  매개변수로 전달받은 요일 중 가장 가까운 날짜 출력
SELECT NEXT_DAY(SYSDATE, '토')
FROM DUAL; -- SESSION SET에서 언어가 한글 일 때 실행 가능
SELECT NEXT_DAY(SYSDATE, 'SAT')
FROM DUAL;-- SESSION SET에서 언어가 AMERICAN 일 때 실행가능

-- LOCALE 값을 가지고 국가에 맞는 언어를 사용해야함
SELECT * FROM V$NLS_PARAMETERS;
ALTER SESSION SET NLS_LANGUAGE = 'KOREAN';

-- LAST_DAY : 그 달의 마지막 날을 출력해줌
SELECT LAST_DAY('93/11/09')
FROM DUAL;

-- ADD_MONTHS : 개월수를 더하는 기능
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 5)
FROM DUAL;

-- MONTHS_BETWEEN : 두 날짜 사이의 개월수를 구해줌
SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, '93/11/09')), 
        FLOOR(MONTHS_BETWEEN(SYSDATE, '01/08/10'))
FROM DUAL;

-- EXTRACT(YEAR, MONTH, DAY FROM 날짜) : 날짜의 년, 월, 일 따로 출력할 수 있는 기능
SELECT EXTRACT(YEAR FROM SYSDATE),
        EXTRACT(MONTH FROM SYSDATE),
        EXTRACT(DAY FROM SYSDATE)
FROM DUAL;

-- 사원들의 입사개월수 조회: 사원명, 부서코드, 입사일, 입사월
SELECT EMP_NAME, DEPT_CODE, HIRE_DATE, FLOOR(MONTHS_BETWEEN(SYsdate, hire_date)) AS 입사한지몇개월
FROM EMPLOYEE;

-- 오늘 군대에 입대한다면 전열일과 복무일수, 짬밥수(하루 3끼)를 출력하기
SELECT ADD_MONTHS(SYSDATE, 12 + 6) AS 전역날, 
        ABS(ADD_MONTHS(SYSDATE, 12 + 6) - Sysdate) AS 복무일수,
        ABS(ADD_MONTHS(SYSDATE, 12 + 6) - SYSDATE) * 3 AS 짬밥수
FROM Dual;

--사원 중 12월 입사자 조회
SELECT *
FROM EMPLOYEE
WHERE EXTRACT(MONTH FROM HIRE_DATE) = '12';

 

(4)형변환 함수

오라클DB에는 자동형변환이 잘 작동하지만 필요에 따라 강제 형변환 진행할 대 사용

1. 문자형

타입: CHAR, VARCHAR2, NVARCHAR2

• VARCHAR2를 많이 사용

TO_CHAR(숫자, ‘형식’) → 숫자, 날짜를 특정 문자열 패턴에 맞춰 변환

• 숫자 패턴
  • 0: 변환대상값의 자리수가 지정자리수와 일치하지 않을 때 값이 없으면 0을 표시함
  • 9: 변환대상값의 자리수가 지정자리수와 일치하지 않을 때 값이 없으면 생략함
  • L: 화폐단위로 표시 → LOCALE 기준

 

-- 정수 -> 문자열 :  TO_CHAR
SELECT 123456, TO_CHAR(123456, '000,000,000'), 
        TO_CHAR(1234567, '999,999,999'),
        TO_CHAR(1234567, 'L999,999,999'),
        TO_CHAR(180.5, '000,000,000'), 
        TO_CHAR(180.5, '000,000.000'),
        TO_CHAR(180.5, '999,999.99')
FROM DUAL;
-- 출력: 123456, 000,123,456, 1,234,567, ￦1,234,567
		-- 000,000,181,	000,180.500, 180.50

 

• 날짜 패턴
  : 표시: Y/R = 년, M 월, D 일, H 시(HH: 24시), MI 분, SS 초
  
  • YY: 2000s
  • RR: 적용 기준
    --현재년도 입력년도 계산년도
    --00~49   00~49 현세기
    --00~49   50~99 전세기
    --50~99   00~49 다음세기
    --50~99   50~99 현세기
  • 현재 24년, 입력된 데이터가 62면 전세기 1990으로 계산되어 출력됨
  • 만약 1948년 데이터일 경우 현세기로 적용이 되는 오류가 생길 수 있음(2048)

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YY.MM.DD HH24:MI:SS')
FROM DUAL;
-- 24.03.15 06:14:38 출력

-- 사원명, 급여, 입사일 출력
-- 사원의 급여를 화폐단위 표시하여 단위별로 쉼표 구분으로 출력
-- 입사일: -구분하여 년월일 출력
SELECT EMP_NAME as 이름, 
        TO_CHAR(SALARY, 'L999,999,999') as 월급여, 
        TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') as 입사일
FROM EMPLOYEE;

@ YY, RR 차이
SELECT EMP_NAME, EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO, 1,2), 'YY')),
                EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO, 1,2), 'RR'))
FROM EMPLOYEE;

2. 숫자

타입: NUMBER(정수, 실수)

TO_NUMBER(문자, 형식) : 문자 → 숫자

: 숫자 형식의 문자열을 숫자로 변경해서 연산할 때 사용, 형식 위와 동일

3. 날짜

타입: DATE, TIMESTAMP

TO_DATE(’문자’ 또는 숫자, ‘형식’) : 문자 → 날짜 or 숫자 → 날짜

• 파싱할 때 문자형 말고 무조건 날짜형으로 바꿔서 전달해야 오류가 적다
• 🚨 날짜를 표현한 숫자 중 0으로 시작되는 경우는 TO_CHAR를 거쳐서 진행해야만 한다

SELECT TO_DATE('24-03-14', 'YY-MM-DD'),
        -- TO_DATE(000614, 'YYMMDD') 불가능
        TO_DATE(TO_CHAR(000614, '000000'), 'YYMMDD')
FROM DUAL;

-- 24/03/14,	00/06/14 출력

-- 주민등록번호 앞자리로 출생년도 4자리 추출하기
to_char(to_date(substr(emp_no, 1, 2), 'RR'), 'YYYY')

extract(year from to_date(to_char(hire_date, 'RRMMDD')))

4. null 처리 대체 함수

1. NVL(컬럼명, 대체값)
2. NVL2(컬럼명,NOT NULL일때 대체값, NULL일때 대체값)

-- null 처리 대체 함수: NVL, NVL2
SELECT EMP_NAME, SALARY, BONUS, **NVL(BONUS, 0), NVL2(BONUS, '있따', '없따'),
        NVL2(DEPT_CODE, '정규직', '인턴')**
FROM EMPLOYEE;

5. 컬럼 값에 따라 출력 내용을 변경하는 함수

조건식과 비슷함

1. DECODE(컬럼명, ‘예상값1’, ‘출력할 값1’,[….])
   : 자바 switch와 비슷, 한개 데이터마다 검증
2. CASE
       WHEN 조건 THEN true일때 출력할 값
       [WHEN 조건2 THEN true일때 출력할 값]
       [ELSE 디폴트. 값 ]
   END

자바 IF문과 비슷

-- 사원 직책명 출력하기: 조인문으로 나중에는 대체할 수 있음
@ **1. DECODE :** 
SELECT EMP_NAME, 
    **DECODE(JOB_CODE, 'J1', '대표', 'J2', '부사장', 
    'J3', '부장', 'J4', '차장', '사원') AS 직책**
FROM EMPLOYEE;

-- 재직중인 직원과 퇴사한 직원의 수를 조회하시오
SELECT DECODE(ENT_YN, 'N', '재직', '퇴사') 재직여부, COUNT(*) 수
FROM EMPLOYEE
GROUP BY DECODE(ENT_YN, 'N', '재직', '퇴사');

@ **2. CASE :**
SELECT EMP_NAME, JOB_CODE,
    **CASE
        WHEN JOB_CODE = 'J1' THEN '대표'
        WHEN JOB_CODE = 'J2' THEN '부사장'
        WHEN JOB_CODE = 'J3' THEN '부장'
        WHEN JOB_CODE = 'J4' THEN '차장'
        ELSE '사원'
    END AS 직책**
FROM EMPLOYEE;