[ 7주차 ] Oracle 데이터베이스 (함수 사용 쿼리문)

함수 관련 공부했던 쿼리문에 주석을 달고 올리려고 한다.

함수

-- ROUND(반올림) ROUND(X ,1)에서 1의의미는 1자리 표현해라 및 1번째 자리에서 반올림해라
SELECT ROUND(45.275,1) FROM DUAL;
--  1  2  3  4 . 5 6 7 
-- -4 -3 -2 -1 0 1 2 3 자리NUM
SELECT ROUND(45.275, -1) FROM DUAL; -- -1은 4번째 자리니까 5.를 기준으로 반올림
SELECT ROUND(45.675, 0) FROM DUAL;
SELECT ROUND(45.675) FROM DUAL;

SELECT PAY, ROUND(PAY, -2) 두번째자리에서반올림 FROM PERSONNEL;

-- TRUNC(절삭) 반올림 그런거 없이 그냥 무조건 버려라
SELECT TRUNC(45.245,2) FROM DUAL; -- 45.24출력
SELECT TRUNC(49.245, -1) FROM DUAL; -- 40출력
SELECT TRUNC(12345, -2) FROM DUAL; -- 12300 출력

-- CEIL/FLOOR (올림/내림) - 정수만 반환
SELECT CEIL(461.21) FROM DUAL; -- 무조건 올림 462 출력
SELECT FLOOR(461.99) FROM DUAL; -- 무조건 내림 461출력

-- MOD (나머지)
SELECT MOD(10,3) FROM DUAL; -- 1 출력
SELECT MOD(10,2) FROM DUAL; -- 0 출력

-- ABS (절대값)
SELECT ABS(-123) FROM DUAL; -- 123 출력

--SIGN 결과값이 양수:1 음수:-1 0이나오면 : 0
SELECT SIGN(100-10),SIGN(100-100),SIGN(10-100) FROM DUAL;

-- POWER 지수 (2에4승)
SELECT POWER(2,4) FROM DUAL; -- 16 출력

-- SQRT 제곱근
SELECT SQRT(9) FROM DUAL; -- 3출력

 

문자 함수

--UPPER(소문자 -> 대문자) LOWER(대문자->소문자)
SELECT UPPER('Oracle') FROM DUAL; -- ORACLE
SELECT LOWER('ORACLE') FROM DUAL; -- oracle

-- INITCAP (첫글자를 대문자로)
SELECT INITCAP('korea fighting') FROM DUAL; -- Korea Fighting

-- CONCAT
SELECT CONCAT('KOREA','FIGHTING')FROM DUAL; -- KOREAFIGHTING

-- LENGTH
SELECT LENGTH('KOREA') FROM DUAL; -- 5
SELECT LENGTH('KOREA FIGHTING') FROM DUAL; -- 14

-- SUBSTR
SELECT SUBSTR('ABCDEFG',1,3) FROM DUAL; -- 1번째 자리부터 3자리 ABC
SELECT SUBSTR('ABCDEFG',3,2) FROM DUAL; -- 3번째 자리부터 2자리 CD

-- 뒤에서 3번째 문자부터 2개의 문자
SELECT SUBSTR('ABCDEFG',-3,2) FROM DUAL; -- EF
-- 4에서부터 끝까지
SELECT SUBSTR('ABCDEFG', 4) FROM DUAL; -- DEFG

--RPAD/LPAD
--941234-2032568
--941234-2******

-- 14번째 자리중 비어있는 곳에 * 을 출력
SELECT RPAD('941234-2032568',8,'*') FROM DUAL; -- 941234-2
SELECT RPAD(SUBSTR('941234-2032568',1, 8),14,'*') JUMIN FROM DUAL; -- 941234-2******

-- RTRIM/LTRIM

SELECT RTRIM('ABBBBBB','B') FROM DUAL; -- A
SELECT RTRIM('A      ',' ') FROM DUAL; -- A

SELECT LTRIM('BBBBABB','B') FROM DUAL; -- ABB

 

날짜 함수

--날짜 함수 (많이 씀)
--날짜는 STRING으로 처리하지만 연산도 가능
SELECT SYSDATE FROM DUAL; --GETDATE() 현재 날짜와 시간을 가져옴;

SELECT SYSDATE + 10 FROM DUAL; -- 10일 더하기
SELECT SYSDATE-1,SYSDATE,SYSDATE+1 FROM DUAL;

-- ROUND(반올림)
-- YEAR로 반올림해라는 YEAR는 6월 30일이 기준
SELECT SYSDATE,ROUND(SYSDATE,'YEAR') FROM DUAL;
-- 15일 기준
SELECT SYSDATE,ROUND(SYSDATE,'MONTH') FROM DUAL;

-- TRUNC(절삭)
SELECT SYSDATE,TRUNC(SYSDATE,'YEAR') FROM DUAL;

SELECT SYSDATE,TRUNC(SYSDATE,'MONTH') FROM DUAL;

-- MONTH_BETWEEN(달수)
SELECT ROUND((MONTHS_BETWEEN(SYSDATE,'1993-02-15'))/12) FROM DUAL;

-- LAST_DAY(말일 구하기)
SELECT SYSDATE,LAST_DAY(SYSDATE) FROM DUAL;

-- NEXT_DAT(다음날), 내가 지정해준날로
SELECT NEXT_DAY(SYSDATE,'토요일') FROM DUAL;

-- ADD_MONTHS(달 추가)
SELECT ADD_MONTHS(SYSDATE,12) FROM DUAL; -- 12개월 후

-- EXTRACT (추출)
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE) YEAR FROM DUAL;
SELECT SYSDATE, EXTRACT(MONTH FROM SYSDATE) MONTH FROM DUAL;
SELECT SYSDATE, EXTRACT(DAY FROM SYSDATE) DAY FROM DUAL;

 

변환 함수

-- 변환함수
-- TO_CHAR(TOSTRING)
-- MM : 달수(10)
-- MON : 3문자의 달수(3월이면 MAR)
-- MONTH : 달 이름(MARCH)
-- DD : 날짜(22)
-- D : 주의 수(2) 월요일이면 2 화 3 수 4 목 5....
-- DY : 3문자 요일 (MON)(THU)...
-- DAY : 요일 (MONDAY)
-- YYYY : 4문자 년도 (2023)
-- YY : 2문자 년도(23)
-- RM : 로마식 달수 (6월 : VI)

SELECT SYSDATE, TO_CHAR(SYSDATE, 'D') FROM DUAL; -- 2(월요일)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DY') FROM DUAL; -- 월
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DAY') FROM DUAL; -- 월요일
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD') FROM DUAL; -- 12 (오늘 6/12)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL; -- 2023-06-12
SELECT SYSDATE, TO_CHAR(SYSDATE, 'FMYYYY-MM-DD') FROM DUAL; -- 앞의 0자가 사라짐

-- 시간
-- HH,HH12 : 12시간
-- HH24 : 24시간 (13시 14시...)
-- MI : 분
-- SS : 초
-- SSSS : 초의 자리수 (나노타임, SSSSSS 가능)
-- AM,PM : 오전오후
-- A.M. , P.M. : 오전 오후

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SSSSSSSSSSSSSSSSSSSSSSSSS') FROM DUAL; -- S는 이만큼만 가능
SELECT TO_CHAR(SYSDATE, 'AMHH:MI') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'PM HH : MI') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'P.M. HH : MI') FROM DUAL;

-- 숫자형식
-- 9 : 자리수 (9999 -> 1234, 12345 : X)
-- 0 : 자리수가 비면 0으로 채움 (0999 -> 1234, 123 -> 0123)
-- . : 소수점
-- , : 천의자리

SELECT TO_CHAR(12506, '99,999') FROM DUAL; -- 12,506
SELECT TO_CHAR(12506, '099,999') FROM DUAL; -- 012,506
SELECT TO_CHAR(12506, '9,999') FROM DUAL; -- 자릿수가 부족해서 #####으로 나옴
SELECT TO_CHAR(12506, '999,999,999') FROM DUAL;

-- 기타
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD') FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDSP') FROM DUAL; -- 영어로 출력
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDTH') FROM DUAL; -- 12TH
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDSPTH') FROM DUAL; -- 영어로 출력 + TH

-- TO_DATE (날짜로 바꿔주는 함수)
SELECT TO_DATE('07-6월-22', 'YY-MONTH-DD') FROM DUAL;
SELECT TO_DATE('11:05', 'HH:MI') FROM DUAL;

-- TO_NUMBER
SELECT TO_NUMBER('123')+10 FROM DUAL;
SELECT TO_NUMBER('ABC')+10 FROM DUAL; -- X 에러

--NVL (NULL이아니면 값을 출력 NULL이면 지정해준값으로 출력)
SELECT NVL(컬럼명,0) FROM 테이블;

-- 급여가 3500이상인 경우 'GOOD', 미만인경우 'POOR'을 출력
-- SIGN : 양수면 1 0이면 0 음수면 -1
SELECT DECODE(SIGN(1 - 0),1,'A',0,'B','C') FROM DUAL; -- A 출력
SELECT DECODE(SIGN(1 - 1),1,'A',0,'B','C') FROM DUAL; -- B 출력
SELECT DECODE(SIGN(1 - 2),1,'A',0,'B','C') FROM DUAL; -- C 출력

 

그룹 함수

-- COUNT (NULL은 셀수없음) 결과는 무조건 1개
SELECT COUNT(*) FROM EMP; --EMP의 테이블 갯수
SELECT COUNT(JOB) FROM EMP; -- EMP 안의 MANAGER컬럼의 데이터갯수
SELECT COUNT(SAL) FROM EMP;

-- 급여가 3000이상인 사원 수
SELECT COUNT(*) FROM EMP
WHERE SAL >= 3000;

-- AVG (평균)
SELECT AVG(SAL) FROM EMP;

-- COMM의 평균
-- 보너스는 3명만 있기 때문에 NVL로 NULL값을 0으로 초기화후 계산
SELECT AVG(NVL(COMM,0)) FROM EMP;

-- SUM(합계)
SELECT SUM(SAL) FROM EMP;

-- MAX/MIN
SELECT MAX(SAL),MIN(SAL) FROM EMP;