20-1. 함수
< 함수 FUNCTION >
- 자바로 따지면 메소드와 같은 존재
- 전달된 값들을 읽어서 계산한 결과를 반환
#1. 문자열
LENGTH(STR) : 전달된 문자열의 글자 수 반환
LENGTHB(STR) : 전달된 문자열의 바이트 수 반환(한글은 3Byte, 나머지는 1Byte)
SELECT LENGTH('오라클!'), LENGTHB('오라클!')
FROM DUAL;
INSTR(STR) : 문자열로부터 특정 문자의 위치값 반환
SELECT INSTR('AABBBCCBABCAA', 'B', 1, 2)
FROM DUAL;
SUBSTR(STR, POSITION, LENGTH) : 문자열로부터 특정 문자열을 추출해서 반환
시작 위치가 음수일 경우 뒤에서부터 N번째 위치로부터 문자를 추출한다.
SELECT EMP_NAME, SALART
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) IN ('2', '4');
LPAD / RPAD(STR, 최종적으로 반환할 문자의 길이(바이트), 덧붙이고자하는 문자)
: 제시한 문자열에 임의의 문자를 왼쪽 또는 오른쪽에 덧붙여서 최종 N길이만큼의 문자열을 반환
SELECT RPAD('880101-2', 14, '*')
FROM DUAL;
LTRIM / RTRIM(STR, 제거시키고자 하는 문자)
: 문자열의 왼쪽 또는 오른쪽에서 제거시키고자 하는 문자들을 찾아서 제거한 나머지 문자열을 반환
SELECT LTRIM(',233425', ',')
FROM DUAL;
TRIM(BOTH/LEADING/TRAILING '제거시키고자 하는 문자' FROM STR)
: 문자열의 앞/뒤/양쪽에 있는 특정 문자를 제거
TRIM(BOTH/LEADING/TRAILING) 생략 갸능, 단 생략 시 기본값이 BOTH
SELECT TRIM('Z' FROM 'ZZZBKZZSJMZZ')
FROM DUAL;
SELECT TRIM(LEADING 'Z' FROM 'ZZZJGJZZKZZ')
FROM DUAL; -- LEADING : 앞쪽
SELECT TRIM(TRAILING 'Z' FROM 'ZZZKFJZZDKZZ')
FROM DUAL; -- TRAILING : 뒤쪽
- LOWER(STR)
: 다 소문자로 변경
- UPPER(STR)
: 다 대문자로 변경
- INITCAP(STR)
: 각 단어 앞글자만 대문자로 변경
결과값은 CHARACTER 타입으로 반환
SELECT LOWER('HELLO WORLD!')
FROM DUAL;
SELECT UPPER('hello world')
FROM DUAL;
SELECT INITCAP('hello world!')
FROM DUAL;
CONCAT(STR1, STR2)
: 전달된 두 개의 문자열을 하나로 합친 결과를 반환
SELECT CONCAT('123', 'ABC')
FROM DUAL;
REPLACE(STR, 찾을 문자, 바꿀 문자)
: STR로부터 찾을 문자를 찾아서 바꿀 문자로 바꾼 문자열을 반환
SELECT REPLACE('서울시 중구 남대문로 120', '120', '125')
FROM DUAL;
#2. 숫자열
ABS(NUMBER) : 절대값을 구해주는 함수
SELECT ABS(-10)
FROM DUAL;
MOD(NUMBER1, NUMVER2) : 두 수를 나눈 나머지값을 반환해주는 함수
SELECT MOD(10, 3)
FROM DUAL;
ROUND(NUMBER, 위치) : 반올림 처리해주는 함수
SELECT ROUND(123.456)
FROM DUAL; -- 123
SELECT ROUND(123.456, -1)
FROM DUAL; -- 120
CEIL(NUMBER) : 소수점 아래의 수를 무조건 올림 처리해주는 함수
SELECT CEIL(123.156)
FROM DUAL; -- 124
FLOOR(NUMBER) : 소수점 아래의 수를 무조건 버림 처리해주는 함수
SELECT FLOOR(123.999)
FROM DUAL; -- 123
TRUNC(NUMBER, 위치) : 위치 지정가능한 절삭 처리 함수
SELECT TRUNC(123.585, 2)
FROM DUAL;
- 날짜 관련 함수
SYSDATE : 현재 시스템 날짜 반환
SELECT SYSDATE
FROM DUAL;
MONTHS_BETWEEN(DATE1, DATE2) : 두 날짜 사이의 개월 수 반환(NUMBER타입 반환, DATE2가 더 미래일 경우 음수가 나올 수 있다)
SELECT EMP_NAME
, FLOOR(SYSDATE - HIRE_DATE) || '일' 근무일수
, FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월' 근무개월수
FROM EMPLOYEE;
ADD_MONTHS(DATE, NUMBER) : 특정 날짜에 해당 숫자만큼의 개월 수를 더한 날짜를 반환(DATE 타입 반환)
SELECT ADD_MONTHS(SYSDATE, 5)
FROM DUAL;
NEXT_DAY(DATE, 요일) : 특정 날짜에서 가장 가까운 요일 찾아 그 날짜 반환
SELECT NEXT_DAY(SYSDATE, '일요일')
FROM DUAL;
LAST_DAY(DATE) : 해당 특정 날짜 달의 마지막 날짜를 구해서 반환(DATE 타입 반환)
SELECT LAST_DAY(SYSDATE)
FROM DUAL;
-- 언어변경
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
EXTRACT : 년도 또는 월 또는 일 정보를 추출해서 반환(NUMBER타입으로 반환)
- EXTRACT(YEAR FROM DATE) : 특정 날짜로부터 년도만 추출
- EXTRACT( MONTH FROM DATE) : 특정 날짜로부터 월만 추출
- EXTRACT(DAY FROM DATE) : 특정 날짜로부터 일만 추출
SELECT EMP_NAME
,EXTRACT(YEAR FROM HIRE_DATE) "입사년도"
,EXTRACT(MONTH FROM HIRE_DATE) "입사월"
,EXTRACT(DAY FROM HIRE_DATE) "입사일"
FROM EMPLOYEE
ORDER BY "입사년도", "입사월", "입사일";
#3. 형변환
< 형변환 함수 >
TO_CHAR(NUMBER/DATE, 포맷) : 숫자형 또는 날짜형 데이터를 문자형 데이터 타입으로 변환(CHARACTER타입 변환)
0으로 지정 : 빈 공간을 0으로 채움
9로 지정 : 빈 공간을 공백으로 채움
맨 앞 L 입력 : 현재 설정된 나라(LOCAL)의 화폐단위가 나옴
, 입력 : 3자리마다 ,로 구분
SELECT TO_CHAR(1234, '00000')
FROM DUAL; -- 1234 => '01234' : 빈 공간을 0으로 채움
SELECT TO_CHAR(1234, '99999')
FROM DUAL; --1234 => '1234' : 빈 공간을 공백으로 채움
SELECT TO_CHAR(1234, 'L00000')
FROM DUAL; -- 1234 => '₩01234' : 현재 설정된 나라(LOCAL)의 화폐단위가 나옴
SELECT TO_CHAR(1234, 'L99999')
FROM DUAL; -- 1234 => '₩1234' : 현재 설정된 나라(LOCAL)의 화폐단위가 나옴
SELECT TO_CHAR(1234, 'L99,999')
FROM DUAL; -- 1234 => '₩1,234' : 3자리마다 ,로 구분
DATE(년월일시분초) => CHARACTER
YYYY-MM-DD : 년 - 월 - 일
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL;
PM HH:MI:SS : AM/PM은 오전/오후 출력
SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS')
FROM DUAL;
HH24 : 24시간 형식
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')
FROM DUAL;
MON DY, YYYY : MON은 몇 월 형식, DY는 일 빼는 형식
SELECT TO_CHAR(SYSDATE, 'MON DY, YYYY')
FROM DUAL;
YEAR은 영어로 년도수 출력
(** YY는 무조건 앞에 20이 붙어서 2000년대가 되고,
RR은 반올림되어서 월일 두자리가 50이상/ 이하에 따라 나뉜다 => 50이상이면 이전 세기, 50미만이면 현재세기)
SELECT TO_CHAR(SYSDATE, 'YYYY')
,TO_CHAR(SYSDATE, 'RRRR')
,TO_CHAR(SYSDATE, 'YY')
,TO_CHAR(SYSDATE, 'RR')
,TO_CHAR(SYSDATE, 'YEAR')
FROM DUAL;
RM은 로마숫자로 표현
(** MM은 약칭(12월, DEC), MON은 풀네임(12월, DECEMBER))
SELECT TO_CHAR(SYSDATE, 'MM')
,TO_CHAR(SYSDATE, 'MON')
,TO_CHAR(SYSDATE, 'MONTH')
,TO_CHAR(SYSDATE, 'RM')
FROM DUAL;
D는 1주일 기준 몇 일째(일요일부터), DD는 1일 기준 몇일째(1일부터), DDD는 1년 기준으로 몇일 째 되었는지 계산
SELECT TO_CHAR(SYSDATE, 'D')
,TO_CHAR(SYSDATE, 'DD')
,TO_CHAR(SYSDATE, 'DDD') -- DDD는 1년이 기준되는 날
FROM EMPLOYEE;
DY는 요일뺀 거 DAY는 요일을 붙힌 것
SELECT TO_CHAR(SYSDATE, 'DY')
,TO_CHAR(SYSDATE, 'DAY')
FROM DUAL;
TO_DATE(NUMBER / CHARACTER, 포맷) : 숫자형 또는 문자형 데이터를 넣으면 날짜형으로 변환(DATE타입 반환)
기본 포맷은 YY/MM/DD로 반환이 된다. ' 따옴표' 를 꼭 붙여주도록 한다.
SELECT TO_DATE(20230101)
FROM DUAL;
SELECT TO_DATE('140630', 'RRMMDD')
FROM DUAL;
TO_NUMBER(CHARACTER, 포맷) : 문자형 데이터를 숫자형으로 변환(NUMBER타입 반환)
정수이기 때문에 앞에 0이 날라감
SELECT TO_NUMBER('0123')
FROM DUAL;
NVL(컬럼명, 해당 컬럼값이 NULL일 경우 반환할 결과값)
컬럼에 값이 존재할 경우 컬럼의 값을 변환, 해당 컬럼의 값이 NULL일 경우 내가 제시한 특정값을 반환
SELECT EMP_NAME , BONUS, NVL(BONUS, 0)
FROM EMPLOYEE;
NVL2(컬럼명, 결과값1, 결과값2)
해당 컬럼값이 존재할 경우 결과값 1 반환, 해당 컬럼값이 NULL일 경우 결과값 2 반환
SELECT EMP_NAME, DEPT_CODE, NVL2(DEPT_CODE, '부서배치완료', '부서없음')
FROM EMPLOYEE;
NULLIF(비교대상1, 비교대상2)
두 개의 값이 동일할 경우 NULL 반환, 두 개의 값이 동일하지 않을 경우 비교대상1을 반환
SELECT NULLIF('123', '456')
FROM DUAL;
#4. 선택함수
< 선택 함수 >
DECODE(비교대상(컬럼명/산술연산/함수식), 조건값1, 결과값1, 조건값2, 결과값2, ....., 결과값)
자바에서의 SWITCH문과 유사
SELECT EMP_ID, EMP_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') "성별"
FROM EMPLOYEE;
CASE WHEN THEN 구문
(CASE WHEN 조건식1 THEN 결과값1
WHEN 조건식2 THEN 결과값2
.....
ELSE 결과값
END )
자바에서의 IF-ELSE IF 문 같은 느낌
SELECT EMP_ID, EMP_NAME,
CASE
WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남'
ELSE '여'
END "성별"
FROM EMPLOYEE;
#5. 그룹함수
< 그룹 함수 >
N개의 값을 읽어서 1개의 결과를 반환(하나의 그룹별로 함수 실행결과 반환
1. SUM(숫자타입컬럼) : 해당 컬럼값들의 총 합계를 반환해주는 함수
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';
2. AVG(숫자타입컬럼) : 해당 컬럼값들의 평균값을 구해서 반환
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEE;
3. MIN(ANY 타입 컬럼) : 해당 컬럼값들 중 가장 작은값 반환
SELECT MIN(SALARY) "최저급여", MIN(EMP_NAME), MIN(EMAIL), MIN(HIRE_DATE)
FROM EMPLOYEE;
SELECT * FROM EMPLOYEE;
4. MAX(ANY 타입 컬럼) : 해당 컬럼값들 중 가장 큰 값 반환
SELECT MAX(SALARY), MAX(EMP_NAME), MAX(EMAIL), MAX(HIRE_DATE)
FROM EMPLOYEE;
5. COUNT(*/컬럼명/DISTINCT 컬럼명) : 조회된 행 개수를 세서 반환
- COUNT(*) : 조회 결과에 해당하는 모든 행 개수를 다 세서 반환
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2';
- COUNT(컬럼명) : 제시한 해당 컬럼값이 NULL이 아닌것만 행 개수를 세서 반환
SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE;
- COUNT(DISTINCT 컬럼명) 제시한 해당 컬럼값이 중복값이 있을 경우 하나로만 세서 반환
SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;
20-2. GROUP BY ... HAVING
< GROUP BY 절 >
그룹을 묶어줄 기준을 제시할 수 있는 구문
** GROUP BY 안에 있어야 SELECT로 조회해볼 수 있다.
SELECT DEPT_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE;
< HAVING 절 >
그룹에 대한 조건을 제시하고자 할 때 사용되는 구문
SELECT DEPT_CODE, ROUND(AVG(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING AVG(SALARY) >= 3000000;
< 실행 순서 >
5 : SELECT * / 조회하고자 하는 컬럼명 / 산술연산식 / 함수식 AS "별칭"
1 : FROM 조회하고자 하는 테이블 명
2 : WHERE 조건식
3 : GROUP BY 그룹 기준에 해당하는 컬럼명 / 함수식
4 : HAVING 그룹함수식에 대한 조건식
6 : ORDER BY 정렬 기준에 해당하는 컬럼명 / 별칭 / 컬럼 순번 ASC/DESC NULLS FIRST / NULLS LAST
20-3. 집합 연산자
< 집합 연산자 SET OPERATOR >
굳이 잘 안쓴다고 함...
1. UNION(합집합 - 두 쿼리문 수행한 결과값을 더해서 중복되는 결과는 한 번만 조회)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;
2. UNION ALL : 여러 개의 쿼리 결과를 무조건 더하는 연산자(중복값이 여러 개 들어갈 수 있음)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION ALL
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;
3. INTERSECT (교집합 - 여러 쿼리 결과의 중복된 결과만을 조회)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
INTERSECT
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;
4. MINUS(차집합 - 선행 쿼리 결과에 후행 쿼리 결과를 뺀 나머지)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
MINUS
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;
'SQL(Oracle) > SQL 수업' 카테고리의 다른 글
#23. DML, DDL, DCL, TCL, OBJECT(VIEW) (0) | 2023.08.02 |
---|---|
#22. DDL(CREATE) (0) | 2023.08.01 |
#21. SELECT (JOIN) (0) | 2023.07.31 |
#19. 세번째 시험... +과제 (0) | 2023.07.27 |
#18. Oracle시작. SQL문 (0) | 2023.07.26 |