Oracle DB/orcle sql
[ Oracle sql ] 02. 데이터베이스 함수 (단일 행 함수)
HAyooni
2022. 8. 6. 18:21
1. 함수 (FUNCTION)
(1) 단일 행 함수 : N개의 값을 읽어 N개의 결과 리턴
(2) 그룹 함수 : N개의 값을 읽어 한 개의 결과 리턴
SELECT 절에는 단일 행 함수와 그룹 함수를 함께 사용할 수 없다 : 결과 행의 개수가 다르기 때문
함수를 사용할 수 있는 위치 : SELECT절, WHERE절, GROUP BY절, HAVING절, ORDER BY절
2. 단일 행 함수
(1) 문자 관련 함수
|
종류
|
의미
|
예시
|
결과
|
|
LENGTH
|
글자 수 반환
|
SELECT LENGTH('오라클') FROM DUAL;
|
3
|
|
LENGTHB
|
글자의 바이트 사이즈 반환
*한글은 3byte 그 외 1byte |
SELECT LENGTHB('오라클') FROM DUAL;
|
9
|
|
INSTR |
해당 문자열에 대한 처음 위치 *ZERO-BASE가 아님 |
SELECT INSTR('AABAACAABBAA', 'A') FROM DUAL;
|
1
|
|
SELECT INSTR('AABAACAABBAA', 'B', 1) FROM DUAL;
뒤에 숫자만큼 건너뛴 다음 해당 문자열의 첫번째 위치 |
9
|
||
|
SELECT EMAIL, INSTR (EMAIL, '@') FROM EMPLOYEE;
EMPLOYEE 테이블에서 이메일의 @위치를 반환 |
|
||
|
LPAD
|
왼쪽부터 임의의 문자열을 덧붙여 길이 N개의 문자열 반환
|
SELECT LPAD(EMAIL, 20) FROM EMPLOYEE;
|
|
|
RPAD
|
오른쪽부터 임의의 문자열을 덧붙여 길이 N개의 문자열 반환
|
SELECT RPAD(EMAIL, 20) FROM EMPLOYEE;
|
|
|
LTRIM
|
값의 왼쪽에서 지정한 문자를 제거한 나머지 반환
|
SELECT LTRIM('000123456', '0') FROM DUAL;
|
123456
|
|
RTRIM
|
값의 오른쪽에서 지정한 문자를 제거한 나머지 반환
|
SELECT RTRIM('HYACABACC', 'BAC')FROM DUAL;
|
HY
|
|
TRIM
|
앞, 뒤 양쪽에서 지정한 문자를 제거한 나머지 반환
|
SELECT TRIM('Z' FROM 'ZZZHYZZZ') FROM DUAL;
|
HY
|
|
SUBSTR
|
특정 문자 일부분을 반환
|
SELECT SUBSTR('HELLOMYGOODFRIENDS', -10, 2) FROM DUAL;
|
OO
|
|
LOWER
|
대문자를 소문자로 반환
|
SELECT LOWER('Welcome To My World') FROM DUAL;
|
welcome to my world
|
|
UPPER
|
소문자를 대문자로 반환
|
SELECT UPPER('Welcome To My World') FROM DUAL;
|
WELCOME TO MY WORLD
|
|
INITCAP
|
각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환하여 반환
|
SELECT INITCAP('Welcome To My World') FROM DUAL;
|
Welcome To My World
|
|
CONCAT
|
문자열 이어쓰기
|
SELECT CONCAT('가나다라', '123') FROM DUAL;
|
가나다라123
|
|
REPLACE
|
특정 문자를 원하는 문자로 변경
|
SELECT REPLACE('박하윤 수강생은 오라클을 수업중이다.', '수강생', '학생') FROM DUAL;
|
박하윤 학생은 오라클을 수업중이다.
|
실습문제
1. EMPLOYEE테이블에서 이름, 이메일, 이메일의 아이디 조회 (아이디 추출 -> @ 위치 파악 -> 첫 번째 문자부터 @앞까지 반환)
SELECT EMP_NAME, EMAIL, SUBSTR (EMAIL, 1, INSTR(EMAIL, '@')-1)FROM EMPLOYEE;2. 주민등록번호를 이용하여 이름과 성별을 나타내는 부분 조회
SELECT EMP_NAME, SUBSTR(EMP_NO, 8, 1) FROM EMPLOYEE;3. EMPLOYEE 테이블에서 남자만 조회(사원 명, '남')
SELECT EMP_NAME, '남'FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = 1;4. EMPLOYEE 테이블에서 여자만 조회(사원 명, '여')
SELECT EMP_NAME, '여'FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = 2;5. EMPLOYEE테이블에서 직원들의 주민번호를 이용하여 사원명, 생년, 생월, 생일 조회
SELECT EMP_NAME, SUBSTR(EMP_NO, 1, 2)"생년", SUBSTR(EMP_NO, 3, 2)"생월", SUBSTR(EMP_NO, 5, 2)"생일" FROM EMPLOYEE;6. EMPLOYEE 테이블에서 사원명, 주민번호 조회 , 주민번호의 뒷자리는 *로 바꿔서 조회
SELECT EMP_NAME, REPLACE(EMP_NO, SUBSTR(EMP_NO, 8 ), '******') FROM EMPLOYEE;
SELECT EMP_NAME,RPAD(SUBSTR(EMP_NO,1,7),14,'*')FROM employee;
SELECT EMP_NAME, SUBSTR(EMP_NO, 1, 7) || '*******' 주민번호 FROM EMPLOYEE;
SELECT EMP_NAME, CONCAT(SUBSTR(EMP_NO, 1, 7), '*******')FROM EMPLOYEE;
(2) 숫자 관련 함수
|
종류
|
의미
|
예시
|
결과
|
|
ABS
|
절대값
|
SELECT ABS(10.9), ABS(-10.9), ABS(10), ABS(-10) FROM DUAL;
|
10.9 10.9 10 10
|
|
MOD
|
나머지 (나누어지는 수의 부호를 따라감)
|
SELECT MOD(10, 3), MOD(-10, 3), MOD(10, -3) , MOD(10.9, 3) FROM DUAL;
|
1 -1 1 1.9
|
|
ROUND
|
반올림
|
SELECT ROUND(123.456), ROUND (123.789), ROUND(123.456, 0), ROUND(123.456, 1),
ROUND(123.456, -1), ROUND(123.456, -2) FROM DUAL; |
123 124 123 123.5 120 100
|
|
CEIL
|
1의 자리에서 올림
|
SELECT CEIL(123.456) FROM DUAL;
|
124
|
|
FLOOR
|
수학적 내림
|
SELECT FLOOR(123.789) FROM DUAL;
|
123
|
|
TRUNC
|
절삭(소수점 버리기) , 범위 지정 가능
|
SELECT TRUNC(123.789, 1) FROM DUAL;
|
123.7
|
(3) 날짜 관련 함수
|
종류
|
의미
|
예시
|
결과
|
|
SYSDATE
|
시스템 날짜 반환
|
SELECT SYSDATE FROM DUAL;
|
22/03/15
|
|
MONTHS_BETWEEN
|
개월 수의 차를 숫자로 리턴
|
SELECT EMP_NAME, HIRE_DATE, MONTHS_BETWEEN(SYSDATE, HIRE_DATE) FROM EMPLOYEE;
|
테이블 데이터에 맞는 개월 수 반환
|
|
ADD_MONTHS
|
기존 날짜에 지정한 숫자만큼의 개월수를 더한 날짜를 반환
|
SELECT ADD_MONTHS(SYSDATE, 4) FROM DUAL;
|
22/07/15
|
|
NEXT_DAY
|
기준 날짜에서 구하려는 요일의 가장 가까운 날짜 리턴
|
SELECT SYSDATE, NEXT_DAY(SYSDATE, '목요일') FROM DUAL;
|
22/03/17
|
|
LAST_DAY
|
해당 달의 마지막 날짜
|
SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL;
|
22/03/31
|
|
EXTRACT
|
년, 월, 일 정보 추출 반환
|
SELECT EMP_NAME, EXTRACT(YEAR FROM HIRE_DATE) 입사연도, EXTRACT(DAY FROM HIRE_DATE)입사월
,EXTRACT(DAY FROM HIRE_DATE)입사일 FROM EMPLOYEE; |
각각 테이블 데이터에 맞는 연도, 월, 일 반환
|
실습문제
1.EMPLOYEE테이블에서 사원 명, 입사일-오늘, 오늘-입사일 조회 (단, 별칭은 근무일수1, 근무일수2로 하고 모두 정수처리(내림)와 양수로 처리)
SELECT EMP_NAME, FLOOR(ABS(HIRE_DATE- SYSDATE)) 근무일수1, FLOOR(ABS(SYSDATE - HIRE_DATE)) 근무일수2
FROM EMPLOYEE;2. EMPLOYEE테이블에서 사번이 홀수인 직원들의 정보 모두 조회
SELECT * FROM EMPLOYEE
WHERE MOD(EMP_ID,2) =1;3. EMPLOYEE 테이블에서 근무년수가 20년 이상인 직원 모든 정보 조회
SELECT * FROM EMPLOYEE
WHERE MONTHS_BETWEEN(SYSDATE, HIRE_DATE) >= 240;
SELECT * FROM EMPLOYEE
WHERE ADD_MONTHS(HIRE_DATE,240) < SYSDATE;
SELECT * FROM EMPLOYEE
WHERE (SYSDATE-HIRE_DATE) / 365 >= 20;4. EMPLOYEE 테이블에서 사원명, 입사일, 입사한 달의 근무일 수 조회
SELECT EMP_NAME, HIRE_DATE, (LAST_DAY(HIRE_DATE)- HIRE_DATE) FROM EMPLOYEE;5. EMPLOYEE 테이블에서 사원의 이름, 입사 연도, 입사 월, 입사일 조회
SELECT EMP_NAME, EXTRACT(YEAR FROM HIRE_DATE) 입사연도, EXTRACT(DAY FROM HIRE_DATE)입사월,
EXTRACT(DAY FROM HIRE_DATE)입사일
FROM EMPLOYEE;6. EMPLOYEE테이블에서 사원의 이름, 입사일, 근무연수 조회 (단, 근무 연수는 현재연도 - 입사연도로 조회)
SELECT EMP_NAME, HIRE_DATE, EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) 근무연수
FROM EMPLOYEE;
(4) 형변환 함수
|
종류
|
의미
|
예시
|
결과
|
|
TO_CHAR
|
날짜/숫자형 데이터를 문자형 데이터로 변경
* 문자로 인식 : 왼쪽 정렬 / 숫자로 인식 : 오른쪽 정렬 |
SELECT 1234 LITERAL_NUMBER , TO_CHAR(1234) FROM DUAL;
|
1234 1234
|
|
TO_DATE
|
문자/숫자형 데이터를 날짜형 데이터로 변경
|
SELECT TO_DATE(20220315, 'YYYYMMDD') FROM DUAL;
|
22/03/15
|
|
TO_NUMBER
|
문자형 데이터를 숫자형 데이터로 변경
|
SELECT '1234', TO_NUMBER('1234') FROM DUAL;
|
1234 1234
|
실습문제
SELECT TO_CHAR(1234, '99999') A FROM DUAL; --'99999' 공간을 의미(9또는 0을 사용)
SELECT TO_CHAR(1234, 'L99999') FROM DUAL; --L 원화 표시
SELECT TO_CHAR(1234, '$99999') FROM DUAL; --$ 달러 표시
SELECT TO_CHAR(1234, 'FML99999') FROM DUAL; --FM 공백 제거
SELECT TO_CHAR(1234, '99,999') FROM DUAL; --콤마를 이용하여 자리수 지정
SELECT TO_CHAR(1234, '$999') FROM DUAL; --자릿수가 부족할 경우 #으로 보여짐
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; -- 시간 표시
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL; -- 오전/오후 표기
SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS') FROM DUAL; --오전/오후 표기
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; -- 24시간 기준 형식
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY AM HH:MI:SS') FROM DUAL; -- 연,월,일,요일,시간
SELECT TO_CHAR(SYSDATE, 'YYYY-FMMM-DD DAY AM HH:MI:SS') FROM DUAL; -- 00이 아닌 한자리수로 나오게 됨(월뿐만 아니라 다)
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" DAY AM HH:MI:SS') FROM DUAL; -- "YYYY년 MM월 DD일 요일" 형식으로 출력
SELECT TO_CHAR(SYSDATE, 'YYYY'), TO_CHAR(SYSDATE, 'YY'), TO_CHAR(SYSDATE, 'YEAR') FROM DUAL; -- 2022 22 TWENTY TWENTY-TWO
SELECT TO_CHAR(SYSDATE, 'MM'), TO_CHAR(SYSDATE, 'MONTH'),
TO_CHAR(SYSDATE, 'MON'), TO_CHAR(SYSDATE, 'RM') --03 3월 3월 III
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; --Q는 해당 날의 분기를 나타낸다.--TO_DATE :문자/숫자형 데이터를 날짜형 데이터로 변경(시간까지는 불가능)
SELECT TO_DATE('20220315', 'YYYYMMDD') FROM DUAL;
SELECT TO_DATE(20220315, 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('20220713', 'YYYYMMDD'), 'YYYY"년" MM"월" DD"일"') FROM DUAL;
SELECT TO_CHAR(TO_DATE('220713 175019', 'YYMMDD HH24MISS'), 'YY-MM-DD AM HH:MI:SS DY') FROM DUAL;
--TO_DATE 에서의 연도 표시 : YY, RR
SELECT TO_DATE('980503', 'YYMMDD'),
TO_DATE('980503', 'RRMMDD'),
TO_DATE('140918', 'YYMMDD'),
TO_DATE('140918', 'RRMMDD')
FROM DUAL;
SELECT TO_CHAR(TO_DATE('980503', 'YYMMDD'), 'YYYYMMDD'),
TO_CHAR(TO_DATE('980503', 'RRMMDD'), 'YYYYMMDD'),
TO_CHAR(TO_DATE('140918', 'YYMMDD'), 'YYYYMMDD'),
TO_CHAR(TO_DATE('140918', 'RRMMDD'), 'YYYYMMDD')
FROM DUAL;
-- Y : 두 자리 연도에 무조건 현재 세기(21세기, 20XX) 적용
-- R : 두 자리 연도가 50이상일 때, 이전 세기(20세기, 19XX) 적용 / 두 자리 연도가 50 미만일 때는 현재 세기(21세기, 20XX) 적용
--TO_NUMBER : 문자형 데이터를 숫자형 데이터로 변환
SELECT '1234', TO_NUMBER('1234') FROM DUAL;
SELECT '10,000' + '5,000' FROM DUAL;
SELECT TO_NUMBER('10,000', '999,999'), TO_NUMBER('5,000', '999,999') FROM DUAL; --형식의 타입을 적어주면 변환 가능
SELECT TO_NUMBER('10,000', '999,999') + TO_NUMBER('5,000', '999,999') FROM DUAL;(5) NULL 처리 함수
|
종류
|
의미
|
예시
|
결과
|
|
NVL
|
데이터가 NULL일 경우 지정한 값으로 대체해주는 함수
|
SELECT EMP_NAME, BONUS, NVL(BONUS, 0)FROM EMPLOYEE;
테이블에 있는 데이터 타입에 맞도록 치환해야한다 |
EMPLOYEE 테이블의 해당 열에 NULL이 존재하는 경우 0으로 표현
|
|
NVL2
|
값이 존재 한다면 두 번째 인자 값으로 변경
값이 존재하지 않는다면 세 번째 인자 값으로 변경 |
SELECT EMP_NAME, BONUS, NVL2(BONUS, 0.7, 0.5) FROM EMPLOYEE;
|
EMPLOYEE테이블의 해당 열이 NULL이 아닐 경우 0.7, NULL일 경우 0.5 표현
|
|
NULLIF
|
비교하는 값이 같으면 NULL
다르면 앞에 있는 값 반환 |
SELECT NULLIF(123, 123), NULLIF(123, 132) FROM DUAL;
|
(NULL) (123)
|
(6) 선택 함수
|
종류
|
의미
|
예시
|
|
DECODE
|
해당하는 값에 대한 원하는 값 반환
|
DECODE(계산식|컬럼명, 조건값1, 선택값1, 조건값2, 선택값2, ...)
|
|
CASE~WHEN ~
THEN |
범위에 대해 들어갈 때 주로 사용
|
CASE WHEN 조건식 THEN 결과값
ELSE 결과값 END 별칭 |
실습문제
1. EMPLOYEE 테이블에서 직원의 아이디, 직원의 이름, 주민등록번호를 조회하되 남자는 남, 여자는 여로 표시될 수 있게 조회
SELECT EMP_ID, EMP_NAME, EMP_NO,
DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여')
--DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남','여') : 1 이 아닌 다른 값은 다 여로 뽑아줘
FROM EMPLOYEE;2. 직원의 급여를 인상하고자 함 직급코드가 J7인 직원은 10% 인상, 직급코드가 J6인 직원은 15% 인상, 직급코드가 J5인 직원은 20% 인상 그외 5%인상 , 직원 테이블에서 직원명, 직급코드, 급여, 인상급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY,
DECODE(JOB_CODE, 'J7', SALARY*1.1, 'J6', SALARY*1.15,
'J5', SALARY*1.2, SALARY *1.05) 인상급여
FROM EMPLOYEE;3. EMPLOYEE 테이블에서 직원의 아이디, 직원의 이름, 주민등록번호를 조회하되 남자는 남, 여자는 여로 표시될 수 있게 조회
SELECT EMP_ID, EMP_NAME, EMP_NO,
CASE WHEN SUBSTR(EMP_NO, 8, 1) = 1 THEN '남'
WHEN SUBSTR(EMP_NO, 8, 1) = 2 THEN '여'
END 성별
FROM EMPLOYEE;
SELECT EMP_ID, EMP_NAME, EMP_NO,
CASE SUBSTR(EMP_NO, 8, 1) WHEN '1' THEN '남'
ELSE '여'
END 성별
FROM EMPLOYEE;4. 직원의 급여를 인상하고자 함 직급코드가 J7인 직원은 10% 인상, 직급코드가 J6인 직원은 15% 인상, 직급코드가 J5인 직원은 20% 인상 그외 5%인상 , 직원 테이블에서 직원명, 직급코드, 급여, 인상급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY,
CASE WHEN JOB_CODE = 'J7' THEN SALARY*1.1
WHEN JOB_CODE = 'J6' THEN SALARY*1.15
WHEN JOB_CODE = 'J5' THEN SALARY*1.2
ELSE SALARY *1.05
END 인상급여
FROM EMPLOYEE;
SELECT EMP_NAME, JOB_CODE, SALARY,
CASE JOB_CODE WHEN 'J7' THEN SALARY*1.1
WHEN 'J6' THEN SALARY*1.15
WHEN 'J5' THEN SALARY*1.2
ELSE SALARY *1.05
END 인상급여
FROM EMPLOYEE;