ㅁ < 함수 FUNCTION >
- 전달된 값을 가지고 특정 연산 수행 후 결과를 반환해줌.
- 전달된 값의 수와 반환된 값의 수를 통해 종류가 나뉨.
- 단일행 함수 : N개의 값을 읽어들여 N개의 결과값 반환 (매 행마다 함수 실행 결과를 반환)
- 그룹 함수 : N개의 값을 읽어들여 1개의 결과값 반환 (그룹을 지어 그룹별로 함수 실행 결과를 반환)
- 결과의 행 수가 다르기 때문에 단일행 함수와 그룹함수는 함께 사용할 수 없음.
- SELECT절, WHERE절, ORDER BY절, GROUP BY절, HAVING절 + DML 구문 등 다양한 곳에서 작성 가능.
- 그룹 함수는 5개 정도. SUM, AVG, COUNT, MAX, MIN
- 단일행 함수는 엄청 많다.
ㅁ 어떤 타입으로 반환하는지를 잘 알아야 중첩해서 잘 쓸 수 있다.
ㅁ < 문자 처리 함수 >
- 문자 데이터를 가지고 연산을 하는 함수.
- 반환 결과는 문자 또는 숫자가 될 수 있음.
- LENGTH('문자열값' | 컬럼) : 문자열의 글자 수를 NUMBER 타입으로 반환.
- LENGTHB('문자열값' | 컬럼) : 문자열의 바이트 수를 NUMBER 타입으로 반환.
ㅁ 참고
- 자음만이든 모음만이든 결합된 형태든 한글 한 글자당 3바이트.
- 영문, 숫자, 특수문자는 한 글자당 1바이트.
ㅁ 예제
SELECT LENGTH('오라클'), LENGTHB('오라클'),
LENGTH('ORACLE'), LENGTHB('ORACLE')
FROM DUAL;
- SELECT절은 뭐가 됐든 FROM절이 필수.
- 3, 9, 6, 6
- 영문자는 글자 수와 바이트 수가 같다.
ㅁ 예제2
SELECT *
FROM EMPLOYEE
WHERE LENGTH(EMAIL) = 13;
- 이메일이 13글자인 사원들의 모든 컬럼 조회
- WHERE절에도 함수 사용 가능.
ㅁ INSTR
- 문자열로부터 특정 문자의 시작위치를 찾아서 NUMBER 타입으로 반환
- 시작위치가 0부터 시작하지 않고 1부터 시작한다.
- INSTR('문자열값' | 컬럼, '찾고자하는문자') // 이 두 개는 필수
- INSTR('문자열값' | 컬럼, '찾고자하는문자', 찾을위치의시작값) // 찾을위치의시작값 생략 가능
- INSTR('문자열값' | 컬럼, '찾고자하는문자', 찾을위치의시작값, 순번) // 순번만 생략 가능
ㅁ 예제
SELECT INSTR('AABAACAABBAA', 'B')
FROM DUAL;
- 찾을 위치의 시작값은 1이 기본값. 순번(B가 3개인데 첫번째 B의 위치찾기)도 1이 기본값.
ㅁ 예제2 (EMAIL이 _ 앞에 3글자인 사원 조회)
SELECT *
FROM EMPLOYEE
WHERE EMAIL LIKE '___!_' ESCAPE '!';
SELECT *
FROM EMPLOYEE
WHERE INSTR(EMAIL, '_') = 4; // 이렇게도 가능. 숫자를 리턴한다.
ㅁ SUBSTR
- 문자열에서 특정 문자열을 추출해서 CHARACTER 타입으로 반환.
- SUBSTR('문자열값' | 컬럼, 시작위치)
- SUBSTR('문자열값' | 컬럼, 시작위치, 추출할문자갯수) // LENGTH생략시 끝까지.
- 시작위치가 음수일 경우, 맨 뒤가 -1이다.
- 추출할 문자 갯수(LENGTH)를 부적절하게 제시(음수 제시)시, 오류나지는 않고 NULL 반환.
ㅁ 예제
SELECT SUBSTR('SHOWMETHEMONEY', 7)
FROM DUAL;
- SUBSTR('SHOWMETHEMONEY', 7)
1 THEMONEY
ㅁ 예제2 (남성인 사원들 조회)
SELECT EMP_NAME, EMP_NO
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '1' OR SUBSTR(EMP_NO, 8, 1) = '3';
SELECT EMP_NAME, EMP_NO
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) IN ('1', '3');
ㅁ 예제3 (이메일 @ 이전까지만 추출)
SELECT EMP_NAME, EMAIL, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@')-1)
FROM EMPLOYEE;
- 함수 중첩 사용 가능하다.
ㅁ 예제4 (주민번호를 900113-2****** 형식으로 조회)
SELECT EMP_NAME, EMP_NO, SUBSTR(EMP_NO, 1, 8) || '******'
FROM EMPLOYEE;
ㅁ LPAD, RPAD
- 문자열에 특정 문자를 왼쪽 또는 오른쪽에 덧붙여서 CHARACTER 타입으로 반환
- LPAD | RPAD (STRING, 최종적으로반환할문자의길이) // 생략시 공백으로 채워짐
- LPAD | RPAD (STRING, 최종적으로반환할문자의길이, 덧붙이고자하는문자)
ㅁ 예제 (주민번호를 900113-2****** 형식으로 조회)
SELECT EMP_NAME, EMP_NO, RPAD( SUBSTR(EMP_NO, 1, 8), 14, '*' )
FROM EMPLOYEE;
ㅁ LTRIM, RTRIM
- 문자열에서 왼쪽 또는 오른쪽에 특정 문자들을 제거한 나머지 문자열을 CHARACTER 타입으로 반환.
- LTRIM | RTRIM (STRING) // 생략시 공백 제거.
- LTRIM | RTRIM (STRING, 제거하고자하는문자들)
- 제거하고자하는문자들에 있는 문자들을 전부 제거.
'0123456789'면 모든 숫자 제거.
ㅁ 예제
SELECT EMP_NAME, EMAIL, RTRIM(EMAIL, '@br.com')
FROM EMPLOYEE;
- 단점이 @ 바로 왼쪽에 b, r, ., c, o, m 있으면 그것도 생략됨. 한번 막혀야 멈춤.
REPLACE(EMAIL, 'br.com', '') 사용할 수 있다.
ㅁ TRIM
- 기본적으로 양 옆의 공백 제거. 가운데에 있는 공백은 제거 안 됨.
- LTRIM, RTRIM과 다르게 제거하고자하는문자를 한 개만 제시 해야 한다.
- LEADING, TRAILING 생략 가능.
ㅁ LOWER, UPPER, INITCAP
- 자바에서의 toLowerCase(), toUpperCase()와 비슷하다.
ㅁ 예제
SELECT '가나다라' || 'ABCD'
FROM DUAL;
- '가나다라' || 'ABCD'
1 가나다라ABCD
ㅁ 예제 (사원명, 입사년도 2자리만)
SELECT EMP_NAME, SUBSTR(HIRE_DATE, 1, 2)
FROM EMPLOYEE;
- 문자열이 와야 함. 날짜 타입이라 자동 형변환이 일어났다. ( 날짜타입 -> 문자타입 )
ㅁ 예제 (사원명, 생년월일 XX년 XX월 XX일)
SELECT EMP_NAME, EMP_NO, SUBSTR(EMP_NO, 1, 2) || '년' ||
SUBSTR(EMP_NO, 3, 2) || '월' ||
SUBSTR(EMP_NO, 5, 2) || '일' 생년월일
FROM EMPLOYEE;
ㅁ 예제 (사원명, 급여 만원 단위로)
SELECT EMP_NAME, SALARY, SUBSTR(SALARY, 1, LENGTH(SALARY)-4)
FROM EMPLOYEE;
ㅁ < 숫자 처리 함수 >
- 숫자 데이터를 가지고 연산을 수행하는 함수.
- 반환 결과 또한 숫자이다.
ㅁ
SELECT 10 / 3 FROM DUAL; // 3.333333333333
SELECT MOD(10, 3) FROM DUAL; // 1
ㅁ 예제 (사원명, 입사일 근무일 수)
SELECT EMP_NAME, HIRE_DATE, TRUNC(SYSDATE - HIRE_DATE)
FROM EMPLOYEE;
- 날짜 간의 연산은 시분초도 연산되어서 소수점 아래로 매우 지저분함.
ㅁ SELECT CEIL(123.000) FROM DUAL; // 이건 123. 소수로 취급 안 함.
ㅁ < 날짜 처리 함수 >
- 날짜 데이터를 가지고 연산을 수행한다.
- 반환값은 날짜 또는 숫자이다. (잘 알아야 한다)
ㅁ SYSDATE
- 현재 시스템의 날짜 및 시간 반환
- 시분초 정보는 더블클릭 후 연필 아이콘 클릭하면 나온다.
안나오면 날짜 타입이 아닌 것이다.
ㅁ 예제
SELECT SYSDATE
FROM DUAL;
- SYSDATE
1 24/07/17
ㅁ 예제2 (2일 전 날짜)
SELECT SYSDATE - 2
FROM DUAL;
- SYSDATE-2
1 24/07/15
ㅁ MONTHS_BETWEEN(DATE1, DATE2)
- 양수의 결과를 보고 싶으면 처음 인자가 더 커야 함.
- 내부적으로 DATE1 - DATE2 연산 후 나누기 30, 31이 진행됨.
일 단위로 나오고, 이것도 시분초까지 연산되어서 소수점 아래로 지저분함.
ㅁ 예제 (사원명, 입사일, 근무일수, 근무개월수)
SELECT EMP_NAME, HIRE_DATE, TRUNC( SYSDATE - HIRE_DATE) "근무일수",
TRUNC( MONTHS_BETWEEN(SYSDATE, HIRE_DATE) ) "근무개월수"
FROM EMPLOYEE
ORDER BY 4 DESC;
- 열 별칭을 정의할 때, 별칭에 한글을 사용할 경우 큰 따옴표로 감싸야 합니다.
ㅁ NEXT_DAY
- 특정 날짜 이후에 가까운 요일의 날짜를 DATE 타입으로 반환.
- NEXT_DAY( DATE, 요일(문자 혹은 숫자) )
- 오라클) 일요일이 1, 토요일이 7.
- 자바) 월요일이 1, 일요일이 7.
ㅁ 언어 체계 변경
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'FRIDAY')
FROM DUAL; // 정상 실행
ㅁ LAST_DAY로 근무일 수 계산(주말 제외)
- 결과 값이 숫자로 나오는 이유는 날짜 간의 차이를 일 단위로 계산하기 때문입니다. 데이터베이스에서는 일반적으로 날짜 간의 차이를 일 수로 계산하여 반환합니다.
ㅁ EXTRACT
- 숫자로 반환함.
ㅁ 예제
SELECT EMP_NAME
, EXTRACT(YEAR FROM HIRE_DATE) "입사년도"
, EXTRACT(MONTH FROM HIRE_DATE) "입사월"
, EXTRACT(DAY FROM HIRE_DATE) "입사일"
FROM EMPLOYEE;
- 이렇게 컴마까지 내려서 작성하는 경우가 많음.
문장 단위로 주석처리(--)하기 편해서.
- 마지막 세미콜론만 한 줄 내리는 경우도 있음. 주석처리 하기 편하려고.
ㅁ < 형변환 함수 >
- 날짜, 숫자, 문자 데이터를 다른 타입으로 변환시켜주는 함수
ㅁ TO_CHAR
- 숫자나 날짜타입 데이터 값을 CHARACTER 타입으로 변환시켜서 반환한다.
- 웹개발 하면서 많이 쓴다. 날짜를 문자열로 변환할 때.
ㅁ 예제
SELECT TO_CHAR(1234)
FROM DUAL; // 1234
- 질의 결과는 1234지만 엄연히 문자임. 따옴표 없어도.
- 보통 문자는 왼쪽 정렬, 숫자는 오른쪽 정렬.
ㅁ 예제2
SELECT TO_CHAR(1234, '99999')
FROM DUAL; // ' 1234'가 조회됨.
SELECT TO_CHAR('00000')
FROM DUAL; // '01234'가 조회됨.
- 99999, 00000 다섯자리 문자열로 변환하겠다는 의미.
- 99999는 빈 자리를 공백으로 채움.
- 00000은 빈 자리를 0으로 채움.
ㅁ 예제3
SELECT TO_CHAR(1234, 'L99999')
FROM DUAL; // '￦1234'
- L은 LOCAL. 현재 설정된 나라의 화폐단위가 붙음.
달러 붙이고 싶으면 L대신 $.
- 빈 자리 공백 X.
- 몇자리가 올진 모르지만 화폐단위 붙이는 용도. L99999999999999 이렇게 쓰기도 함.
SELECT TO_CHAR(12341234, 'L999,999,999')
FROM DUAL; // '￦12,341,234'
- 컴마 삽입
ㅁ 예제4
SELECT SYSDATE
FROM DUAL; // 24/07/17
SELECT TO_CHAR(SYSDATE)
FROM DUAL; // 24/07/17 (문자임)
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS')
FROM DUAL; // 오후 02:49:27
SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS')
FROM DUAL; // 오후 14:49:33 (현재 시간이라 똑같이 오후 나온 듯)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY')
FROM DUAL; // 2024-07-17 수요일
ㅁ 예제5 (24년 07월 17일)
SELECT TO_CHAR(SYSDATE, 'YYYY YY RRRR RR YEAR') // 2024 24 2024 24 TWENTY TWENTY-FOUR
, TO_CHAR(SYSDATE, 'MM MON MONTH RM') // 07 7월 7월 VII
, TO_CHAR(SYSDATE, 'DDD DD D') // 199(년 기준 몇일째인지) 17(월 기준) 4(주 기준)
, TO_CHAR(SYSDATE, 'DAY DY') // 수요일 수
FROM DUAL;
ㅁ 예제6
SELECT EMP_NAME, TO_CHAR( HIRE_DATE, 'YYYY년 MM월 DD일 (DY) ) // 에러
FROM EMPLOYEE;
SELECT EMP_NAME, TO_CHAR( HIRE_DATE, 'YYYY"년" MM"월" DD"일" (DY)' )
FROM EMPLOYEE;
- 1990년 02월 06일 (화)
ㅁ TO_DATE
- 숫자, 문자 타입 데이터를 DATE 타입으로 변환시켜서 반환.
- TO_DATE(NUMBER | CHARACTER, [포맷])
- 8자리 숫자 제시시 4개 년도, 2개 월, 2개 일.
- 6자리 숫자 제시시 2개 년도, 2개 월, 2개 일.
ㅁ 예제
SELECT TO_DATE(20101255)
FROM DUAL;
- 55일은 없기 때문에 오류남.
SELECT TO_DATE(070101)
FROM DUAL;
- 자바에서와 마찬가지로, 숫자 앞에 0붙이면 그냥 없는 걸로 인식함.
5자리 숫자로 인식해서 오류남.
SELECT TO_DATE('070101')
FROM DUAL;
- 오류 안남. 문자열로 인식.
ㅁ 예제2
SELECT TO_DATE('041030 143030')
FROM DUAL;
- 시분초로 하고 싶었는데 오류남.
SELECT TO_DATE('041030 143030', 'YYMMDD HH24MISS')
FROM DUAL;
- 출력은 04/10/30만 되지만 더블클릭 해보면 시분초 정보도 있음.
ㅁ 예제3
SELECT TO_DATE('980630', 'YYMMDD')
FROM DUAL;
- 출력은 98/06/30으로 되지만 더블클릭 해보면 2098년도임.
SELECT TO_DATE('980630', 'RRMMDD')
FROM DUAL;
- 출력은 98/06/30으로 되지만 더블클릭 해보면 1998년도임.
- YY : 무조건 현재세기로 반영
- RR : 두자리 년도 값이 50 미만일 경우 현재 세기, 50 이상일 경우 이전 세기.
ㅁ TO_NUMBER
- CHARACTER 타입의 데이터를 NUMBER 타입으로 변환해서 반환.
- DATE 타입은 불가.
ㅁ 예제
SELECT TO_NUMBER('01234')
FROM DUAL;
- 1234가 출력됨.
ㅁ 오라클은 자동 형변환 지원이 잘 되어있어서 문자 출력해도 자동으로 숫자 출력되는 경우도 많음.
SELECT '1000000' + '5000000'
FROM DUAL;
- 6000000이 출력됨. 자바처럼 10000005000000가 아니라.
- 저기에 컴마 등 특수문자가 있으면 자동형변환 안 됨. 오직 문자열에 숫자만 있는 경우에만.
ㅁ 예제2
SELECT TO_NUMBER('1,000,000', '9,999,999') + TO_NUMBER('5,000,000', '9,999,999')
FROM DUAL;
- 숫자 포맷쓰면 가능
ㅁ < NVL 처리 함수 >
- NVL(컬럼, 반환값)
해당 컬럼값이 존재할 경우 컬럼값 반환.
해당 컬럼값이 NULL일 경우 반환값 반환.
- NVL2(컬럼, 반환값1, 반환값2)
해당 컬럼값이 존재할 경우 반환값1 반환.
해당 컬럼값이 NULL일 경우 반환값2 반환.
ㅁ 예제
SELECT EMP_NAME, NVL(BONUS, '없음')
FROM EMPLOYEE;
- 오류. BONUS 컬럼값이 NUMBER타입이라서 CHARACTER 타입으로 반환하게끔 작성해서.
ㅁ 예제2
SELECT EMP_NAME, ( SALARY + SALARY*NVL(BONUS,0) ) * 12
FROM EMPLOYEE;
- 잘 됨.
ㅁ NULLIF 함수
- NULLIF(비교대상1, 비교대상2)
두 개의 값이 일치하면 NULL 반환.
두 개의 값이 일치하지 않으면 비교대상1 반환.
- 이런 것도 있다.
ㅁ DECODE
- DECODE(비교대상, 비교값1, 결과값1, 비교값2, 결과값2, ..., DEFAULT 결과값 )
- 비교대상 값을 가지고 특정 값과 일치하는지 비교 후 해당 결과값 반환.
- 비교대상을 꼭 컬럼값일 필요는 없고 연산식 가능.
ㅁ 예제
SELECT EMP_NAME, JOB_CODE, DECODE(JOB_CODE, 'J7', SALARY*1.1,
'J6', SALARY*1.15,
'J5', SALARY*1.2,
SALARY*1.05)
FROM EMPLOYEE;
- 마지막 디폴트는 조건을 안 쓴다.
- 반환되는 값들의 타입들을 전부 일치시켜야 한다.
ㅁ CASE WHEN THEN END
- 함수는 아니지만 선택함수처럼 사용할 수 있는 문법
- CASE 비교대상 // 비교대상과 비교값들이 동등한지 비교
WHEN 비교값1 THEN 결과값1
WHEN 비교값2 THEN 결과값2
...
[ ELSE 결과값N ]
END
- CASE
WHEN 조건식1 THEN 결과값2
WHEN 조건식1 THEN 결과값2
...
[ ELSE 결과값N ]
END
ㅁ 예제 (동등비교)
SELECT EMP_NAME
, CASE SUBSTR(EMP_NO, 8, 1)
WHEN '1' THEN '남'
WHEN '2' THEN '여'
END "성별"
FROM EMPLOYEE;
ㅁ 예제 (조건비교)
SELECT EMP_NAME
, CASE
WHEN SALARY >= 5000000 THEN '고급'
WHEN SALARY >= 3500000 THEN '중급'
ELSE '초급'
END "등급"
FROM EMPLOYEE;
ㅁ 그룹 함수는 NULL을 제외하고 계산한다.
ㅁ SUM, AVG, COUNT는 NUMBER를 받아서 NUMBER를 반환함.
- MAX, MIN은 NUMBER뿐 아니라 문자, 날짜 타입을 받아서 문자, 날짜 타입을 반환할 수도 있다.
- MIN 함수는 그룹 내에서 가장 이른 날짜를 반환합니다. (가장 예전 날짜)
- MIN 함수는 그룹 내에서 가장 작은 문자열을 반환합니다. (알파벳 빠른 순)
반환되는 데이터 타입은 해당 컬럼의 데이터 타입과 동일합니다.
ㅁ EMPLOYEE 전 사원의 총급여합
SELECT SUM(SALARY)
FROM EMPLOYEE
- 전체 사원이 '한 그룹'으로 묶임.
ㅁ
SELECT MIN(EMP_NAME)
, MIN(SALARY)
, MIN(HIRE_DATE)
FROM EMPLOYEE;
- 이 결과는 각각 다 별개임. 김해줄의 연봉과 입사일이 아님.
ㅁ
- COUNT(*) 조회된 결과의 모든 행 개수를 세서 반환 NULL O 중복 O
- COUNT(컬럼) 조회된 컬럼의 값들을 개수를 세서 반환 NULL X 중복 O
- COUNT(DISTINCT 컬럼) 해당 컬럼값에서 중복을 제거한 후 개수를 세서 반환 NULL X 중복 X
ㅁ 비교
SELECT COUNT(*)
FROM EMPLOYEE; // 23
SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE; // 21
SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE; // 6
SELECT DISTINCT DEPT_CODE // (null)을 포함한 7개의 행
FROM EMPLOYEE;
SELECT COUNT(DISTINCT NVL(DEPT_CODE, '없음')) // 7
FROM EMPLOYEE;
ㅁ 현재 테이블의 전체 행 개수 세기
SELECT COUNT(*)
FROM EMPLOYEE;
- COUNT(*)
1 23
ㅁ *과 COUNT(*)의 차이
ㅁ 보너스를 받는 남성 사원의 수
- 물론 WHERE에 조건을 하나(AND BONUS IS NOT NULL) 더 붙이고 COUNT(*)를 할 수도 있지만 이렇게도 가능하다.
'클라우드 활용 자바개발자 양성과정 > 02. 데이터베이스 활용' 카테고리의 다른 글
| 05. SUBQUERY (0) | 2024.07.19 |
|---|---|
| 04. JOIN (조인문) (0) | 2024.07.18 |
| 03. GROUP BY & HAVING (0) | 2024.07.18 |
| 01. SELECT (기본 문법) (0) | 2024.07.16 |
| 0. 오라클 설치 (0) | 2024.07.15 |
