-
004. 문자열 / 숫자 데이터 / 날짜 데이터 관련 함수, 변환 함수, CASE문(조건문, 분기문)SsY/Class 2023. 3. 28. 18:01728x90
2023.03.27 (월)
SCOTT 계정 실습
- 지난 주에 이어서...
- 새 파일 USER 확인 및 LTRIM(), RTRIM()
SELECT USER FROM DUAL; --==>> SCOTT
문자열, 숫자 데이터, 날짜 데이터 관련 함수
- 문자열 관련 함수
- 문자열 잘라내기 (특히 공백제거 함수)
- LTRIM() , RTRIM()
- TRIM 손톱깎이! 또깍 또깍 또깍 해서 자르다가 다른 부분이 나오면 멈춤
근데 뭉텅이로 자르는게 아니라 두 번째 파라미터 값 안에 있는 문자들을 하나하나하나로 자르게 됨!..
- 두 번째 매개변수가 없으면 무조건 공백 제거 함수로 활용됨!
- 한글도 처리 가능
--○ LTRIM() SELECT 'ORAORAORACLEORACLE' "1" -- 오라 오라 오라클 오라클 , LTRIM('ORAORAORACLEORACLE','ORA') "2" , LTRIM('AAAORAORAORACLEORACLE','ORA') "3" , LTRIM('oRAORAORAORACLEORACLE','ORA') "4" --// 소문자 안잘라내짐 , LTRIM('ORA ORAORACLEORACLE','ORA') "5" --// 공백 역시 안잘라내짐 , LTRIM(' ORAORAORACLEORACLE',' ') "6" , LTRIM(' ORACLE') "7" -- 왼쪽 공백 제거 함수로 활용 (두 번째 파라미터 생략) FROM DUAL; --==>> /* ORAORAORACLEORACLE CLEORACLE CLEORACLE oRAORAORAORACLEORACLE ORAORACLEORACLE ORAORAORACLEORACLE ORACLE */ --//『방향, 연속성』 에 주의, 『완성형으로 처리되지 않는다』 = 조각조가내어 제거하게 됨 --> 첫 번째 파라미터에 해당하는 문자열을 대상으로 -- 왼쪽부터 연속적으로 두 번째 파라미터에서 지정한 글자와 같은 글자가 등장할 경우 -- 이를 제거한 결과값을 반환한다. -- 단, 완성형으로 처리되지 않는다. --○ RTRIM() SELECT 'ORAORAORACLEORACLE' "1" -- 오라 오라 오라클 오라클 , RTRIM('ORAORAORACLEORACLE','ORA') "2" , RTRIM('AAAORAORAORACLEORACLE','ORA') "3" , RTRIM('oRAORAORAORACLEORACLE','ORA') "4" --// 소문자 안잘라내짐 , RTRIM('ORA ORAORACLEORACLE','ORA') "5" --// 공백 역시 안잘라내짐 , RTRIM(' ORAORAORACLEORACLE',' ') "6" , RTRIM('ORACLE ') "7" -- 오른쪽 공백 제거 함수로 활용 (두 번째 파라미터 생략) FROM DUAL; --==>> /* ORAORAORACLEORACLE ORAORAORACLEORACLE AAAORAORAORACLEORACLE oRAORAORAORACLEORACLE ORA ORAORACLEORACLE ORAORAORACLEORACLE ORACLE */ --//『방향, 연속성』 에 주의, 『완성형으로 처리되지 않는다』 = 조각조각내어 제거하게 됨 --> 첫 번째 파라미터에 해당하는 문자열을 대상으로 -- 오른쪽부터 연속적으로 두 번째 파라미터에서 지정한 글자와 같은 글자가 등장할 경우 -- 이를 제거한 결과값을 반환한다. -- 단, 완성형으로 처리되지 않는다.
SELECT LTRIM('이박주양이박주양양양이이이박박한이박주', '이박주양') "테스트" FROM DUAL; --==>> 한이박주 --// 한글도 처리 가능, 조각내어 처리함 확인 가능
- TRANSLATE()
- 1:1로 바꾸어줌
- 맵핑되어있는 것들끼리 바꿔주며, 바꿀 대상이 없어도 멈추지 않으며, 끝까지 맵핑되어있는 것을 바꾼 후 반환
--○ TRANSLATE() --> 1:1 로 바꾸어준다. SELECT TRANSLATE ('MY ORAVLE SERVER' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' , 'abcdefghijklmnopqrstuvwxyz') "테스트" FROM DUAL; --==>> my oravle server SELECT TRANSLATE('010-1357-2468' , '0123456789' , '영일이삼사오육칠팔구') "테스트" FROM DUAL; --==>> 영일영-일삼오칠-이사육팔- REPLACE()
- 묶음으로 바꾸어 줌
--○ REPLACE() SELECT REPLACE('O MY ORACLE ORAHOME', 'ORA', '오라') "테스트" FROM DUAL; --==>> O MY 오라CLE 오라HOME
- 숫자 데이터 처리 함수
- ROUND()
- 반올림을 처리해주는 함수
- 두 번째 파라미터 :
양수 : 소수점 이하 N 번째 자리까지 표시해라 (N+1번째 자리에서 반올림해라!)
음수 : 정수 기준으로 십의자리 > 백의자리 > ... 까지 유효한 표현으로 나아가게 됨
--○ ROUND() 반올림을 처리해주는 함수 SELECT 48.678 "1" , ROUND (48.678, 2) "2" -- 소수점 이하 둘째자리까지 표현(→ 셋째 자리에서 반올림) , ROUND (48.674, 2) "3" , ROUND (48.678, 1) "4" , ROUND (48.674, 0) "5" --// 6번과 동일 (정수만 표현하겠다) , ROUND (48.674) "6" -- 두 번째 파라미터가 0일 경우 생략 가능 , ROUND (48.674, -1) "7" -- 10의 자리까지 유효한 표현 , ROUND (48.674, -2) "8" , ROUND (48.674, -3) "9" FROM DUAL; --==>> 48.678 48.68 48.67 48.7 49 49 50 0 0
- TRUNC()
- 절삭을 처리해주는 함수
- 두 번째 파라미터는 ROUND() 와 동일하게 처리
--○ TRUNC() 절삭을 처리해주는 함수 SELECT 48.678 "1" , TRUNC (48.678, 2) "2" -- 소수점 이하 둘째자리까지 표현(→ 셋째 자리에서 절삭) , TRUNC (48.674, 2) "3" , TRUNC (48.678, 1) "4" , TRUNC (48.674, 0) "5" --// 6번과 동일 (정수만 표현하겠다) , TRUNC (48.674) "6" -- 두 번째 파라미터가 0일 경우 생략 가능 , TRUNC (48.674, -1) "7" -- 10의 자리까지 유효한 표현 , TRUNC (48.674, -2) "8" , TRUNC (48.674, -3) "9" FROM DUAL; --==>> 48.678 48.67 48.67 48.6 48 48 40 0 0
- MOD()
- 나머지를 반환하는 함수 (자바 『%』 연산)
--○ MOD() 나머지를 반환하는 함수 (자바 『%』 연산) SELECT MOD(5,2) FROM DUAL; --> 5를 2로 나눈 나머지 결과값 반환 --==>> 1
- POWER()
- 제곱의 결과를 반환하는 함수
--○ POWER() 제곱의 결과를 반환하는 함수 SELECT POWER(5,3) "확인" FROM DUAL; --> 5의 3승을 결과 값으로 반환 --==>> 125
- SQRT()
- 루트 결과 값을 반환하는 함수
--○ SQRT() 루트 결과 값을 반환하는 함수 SELECT SQRT(2) FROM DUAL; --> 루트 2에 대한 결과값 반환 --==>> 1.41421356237309504880168872420969807857
- LOG()
- 로그 값을 반환하는 함수
- 오라클은 상용로그만 지원하는 반면, MS-SQL 은 상용로그 자연로그 모두 지원한다
--○ LOG() 로그 함수 -- (※ 오라클은 상용로그만 지원하는 반면, MS-SQL 은 상용로그 자연로그 모두 지원한다) SELECT LOG(10,100) "확인1" ,LOG(10,20) "확인2" FROM DUAL; --==>> 2 1.30102999566398119521373889472449302677
- 삼각함수 / 역함수
- SIN, COS, TAN
- ASIN, ACOS, ATAN (범위 : -1 ~ 1)
--○ 삼각 함수 -- 싸인, 코싸인, 탄젠트 결과 값을 반환한다. SELECT SIN(1), COS(1), TAN(1) FROM DUAL; --==>> /* 0.8414709848078965066525023216302989996233 0.5403023058681397174009366074429766037354 1.55740772465490223050697480745836017308 */ --○ 삼각함수의 역함수 (범위: -1~1) -- 어싸인, 어코싸인, 어탄젠트 SELECT ASIN(0.5), ACOS(0.5), ATAN(0.5) FROM DUAL; --==>> /* 0.52359877559829887307710723054658381405 1.04719755119659774615421446109316762805 0.4636476090008061162142562314612144020295 */- SIGN()
- 연산의 결과 값을 1, 0 , -1 로 반환하는 함수
- 적자/흑자 개념을 나타낼 때, 주로 활용하게 된다
--○ SIGN() 서명, 부호, 특징 --//동일하게 싸인함수 라고 부름 --> 연산 결과 값이 양수이면 1, 0이면 0, 음수이면 -1 을 반환한다. SELECT SIGN(5-2) "1" , SIGN(5-5) "2" , SIGN(5-9) "3" FROM DUAL; --==>> 1 0 -1 --> 매출이나 수지와 관련하여 적자 및 흑자 개념을 나타낼 때 주로 활용한다.
- ASCII(), CHR()
--○ ASCII(), CHR() → 서로 상응하는 함수 SELECT ASCII('A') "1" , CHR(65) "2" FROM DUAL; --==>> --> ASCII() : 매개변수로 넘겨받은 문자의 아스키코드 값을 반환한다. -- CHR() : 매개변수로 넘겨받은 문자를 아스키코드 값으로 취하는 해당 문자를 반환한다.
- 날짜 관련 함수
- 세션 변경
--※ 날짜 관련 세션 설정 변경 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; --==>> Session이(가) 변경되었습니다.
- 연산의 기본 단위 "DAY(일수)"
--※ 날짜 연산의 기본 단위는 DAY(일수)이다. CHECK!!! SELECT SYSDATE "1" , SYSDATE+1 "2" , SYSDATE-2 "3" , SYSDATE+3 "4" FROM DUAL; --==>> /* 2023-03-27 10:11:54 -- 현재 2023-03-28 10:11:54 -- 1일 후 ( → +1) 2023-03-25 10:11:54 -- 2일 전 ( → -2) 2023-03-30 10:11:54 -- 3일 후 ( → +3) */ --○ 시간 단위 연산 SELECT SYSDATE "1" , SYSDATE + 1/24 "2" , SYSDATE - 2/24 "3" FROM DUAL; --==>> /* 2023-03-27 10:14:36 -- 현재 2023-03-27 11:14:36 -- 1시간 후 2023-03-27 08:14:36 -- 2시간 전 */
--○ 현재 시간과.. 현재 시간 기준 1일 2시간 3분 4초 후를 조회한다. /* --------------------------------------------- 현재 시간 | 연산 후 시간 --------------------------------------------- 2023-03-27 10:15:22 | 2034-03-28 12:18:26 --------------------------------------------- */ -- 방법1 SELECT SYSDATE "현재시간" ,SYSDATE + 1 + (2/24) + (3/(24*60)) + (4/(24*60*60)) "연산 후 시간" FROM DUAL; -- 방법2 SELECT SYSDATE"현재시간" ,SYSDATE + ((24*60*60)+ (2*60*60)+ (3*60) + 4) /(24*60*60) "연산 후 시간" --// 더하는 시간 모두를 초로 더하여 나누어 계산 FROM DUAL;
- 날짜 타입의 연산
- 날짜 - 날짜 = 일수
※ 데이터 타입의 변환 TO_DATE( )
- 문자 타입을 날짜 타입으로 변환을 수행할 때 내부적으로 해당 날짜에 대한 유효성 검사가 이루어진다.
--○ 날짜(타입) - 날짜(타입) = 일수 -- EX) (2023-09-17) - (2023-03-27) -- 수료일 현재일 SELECT TO_DATE('2023-08-17','YYYY-MM-DD') - TO_DATE('2023-03-27','YYYY-MM-DD') "확인" FROM DUAL; --==>> 143 ------------------------------------------------------------------------------------ --○ 데이터 타입의 변환 SELECT TO_DATE('2023-08-17','YYYY-MM-DD') "확인" -- 날짜 형식으로 변환된 결과 반환 FROM DUAL; --==>> 2023-08-17 00:00:00 --※ TO_DATE() 함수를 활용하여 문자 타입을 날짜 타입으로 변환을 수행할 때 -- 내부적으로 해당 날짜에 대한 유효성 검사가 이루어진다. ------------------------------------------------------------------------------------- SELECT TO_DATE('2023-08-57','YYYY-MM-DD') "확인" FROM DUAL; --==>> 에러 발생 --// 유효한 날짜 확인 /* ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must be between 1 and last day of month" *Cause: *Action: */ SELECT TO_DATE('2023-13-17','YYYY-MM-DD') "확인" FROM DUAL; --==>> 에러 발생 --// 유효한 날짜 확인 /* ORA-01843: not a valid month 01843. 00000 - "not a valid month" *Cause: *Action: */
- ADD_MONTHS()
- 개월 수를 더해주는 함수
--○ ADD_MONTHS() 개월 수를 더해주는 함수 SELECT SYSDATE "1" ,ADD_MONTHS(SYSDATE,2) "2" ,ADD_MONTHS(SYSDATE,3) "3" ,ADD_MONTHS(SYSDATE,-2) "4" FROM DUAL; --==>> /* 2023-03-27 10:35:50 -- 현재 2023-05-27 10:35:50 -- 2개월 후 2023-06-27 10:35:50 -- 3개월 후 2023-01-27 10:35:50 -- 3개월 전 */ --> 월을 더하고 빼기
- MONTHS_BETWEEN()
- 개월 수 차이를 반환하는 함수
--○ MONTHS_BETWEEN() -- 첫 번째 인자값에서 두 번째 인자값을 뺀 개월 수를 반환 --> 개월 수의 차이를 반환하는 함수 --※ 결과값의 부호가 『-』(음수) 로 반환되었을 경우에는 -- 첫 번째 파라미터에 해당하는 날짜보다 -- 두 번째 파라미터에 해당하는 날짜가 『미래』라는 의미로 확인할 수 있다. SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2002-05-31', 'YYYY-MM-DD')) "확인" FROM DUAL; --==>> 249.885296445639187574671445639187574671 SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2023-08-17', 'YYYY-MM-DD')) "확인" FROM DUAL; --==>> -4.66302531362007168458781362007168458781
- NEXT_DAY()
- 첫 번째 파라미터를 기준 날짜로 하여 돌아오는 가장 빠른 요일을 반환하는 함수
- 간혹 에러가 뜰 경우 DATE LANGUAGE 가 다른 언어로 설정되어있을 경우가 있다.
(설치방법 등에 따라 기본적으로 영어나 다른 언어로 설정되는 경우 있음)
--○ NEXT_DAY() -- 첫 번째 파라미터를 기준 날짜로 돌아오는 가장 빠른 요일 반환 SELECT NEXT_DAY(SYSDATE,'토') "1" , NEXT_DAY(SYSDATE,'월') "2" FROM DUAL; --==>> 2023-04-01 2023-04-03 --※ 추가 세션 설정 변경 ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH'; --==>> Session이(가) 변경되었습니다. --○ 위에서 조회했던 쿼리문을 세션 설정을 변경한 후 다시 조회 SELECT NEXT_DAY(SYSDATE,'토') "1" , NEXT_DAY(SYSDATE,'월') "2" FROM DUAL; --==>> /* ORA-01846: not a valid day of the week 01846. 00000 - "not a valid day of the week" *Cause: *Action: */ SELECT NEXT_DAY(SYSDATE,'SAT') "1" , NEXT_DAY(SYSDATE,'MON') "2" FROM DUAL; --==>> /* 2023-04-01 2023-04-03 */ --※ 세션 설정 변경 ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN'; --==>> Session이(가) 변경되었습니다.
- LAST_DAY()
- 해당 날짜가 포함 된 그 달의 마지막 날을 반환하는 함수
--○ LAST_DAY() -- 해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환한다. SELECT LAST_DAY(SYSDATE) -- 2023년 3월 27일 FROM DUAL; --==>> 2023-03-31 SELECT LAST_DAY(TO_DATE('2023-02-10','YYYY-MM-DD')) "확인" FROM DUAL; --==>> 2023-02-28
--○ 오늘부로... ㅅㅎ이가... 군대에 또 끌려(?) 간다. -- 복무기간은 22개월로 한다. -- 1. 전역 일자를 구한다. SELECT ADD_MONTHS(SYSDATE,22) "확인" FROM DUAL; --==>> 2025-01-27 -- 2. 하루 꼬박꼬박 세 끼 식사를 해야 한다고 가정하면 -- ㅅㅎ이가 몇 끼를 먹어야 집에 보내줄까... -- 복무기간 * 3 -- -------- -- (전역일자 - 현재일자) -- (전역일자 - 현재일자) * 3 SELECT (전역일자 - 현재일자) * 3 FROM DUAL; SELECT (ADD_MONTHS(SYSDATE,22) - SYSDATE) * 3 FROM DUAL; --==>> 2016 --// 내 풀이 SELECT TO_DATE(ADD_MONTHS(SYSDATE,22),'YYYY-MM-DD') "전역일자", (TO_DATE(ADD_MONTHS(SYSDATE,22),'YYYY-MM-DD')- TO_DATE(SYSDATE,'YYYY-MM-DD'))*3 "밥 먹기" FROM DUAL; --==>> /* 전역일자 밥 먹기 ---------- ---------- 2025-01-27 2016 */
- 날짜를 숫자로 대상으로 하여 계산
--○ 현재 날짜 및 시각으로부터... -- 수료일(2023-08-17 18:00:00) 까지 남은 기간을.. -- 다음과 같은 형태로 조회할 수 있도록 쿼리문을 구성한다. /* --------------------------------------------------------------------- 현재 시각 | 수료일 | 일 | 시간 | 분 | 초 | --------------------------------------------------------------------- 2023-03-27 11:29:30 | 2023-08-27 18:00:00 | 143 | 6 | 30 | 30 | --------------------------------------------------------------------- */ ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; --==>> Session이(가) 변경되었습니다. SELECT SYSDATE "현재 시각" ,TO_DATE('2023-08-17 18:00:00','YYYY-MM-DD HH24:MI:SS') "수료일" ,절삭((수료일 - 현재시각)) "일" -- 정수부분만 ,절삭((수료일 - "일")*24)"시간" ,절삭( 나머지 ((수료일 - "일")*24*60, 60) )"분" ,절삭( 나머지 ((수료일 - "일")*24*60*60, 60) )"초" FROM DUAL; -- 내 풀이 SELECT SYSDATE "현재 시각" ,TO_DATE('2023-08-17 18:00:00','YYYY-MM-DD HH24:MI:SS') "수료일" ,TRUNC((TO_DATE('2023-08-17 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE)) "일" -- 정수부분만 ,TRUNC(MOD(((TO_DATE('2023-08-17 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24,24)) "시간" ,TRUNC(MOD(((TO_DATE('2023-08-17 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24*60,60)) "분" ,TRUNC(MOD(((TO_DATE('2023-08-17 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24*60*60,60)) "초" FROM DUAL; --강사님 풀이 ----------------------------------------------------------------------------------------- --『1일 2시간 3분 4초』를... 『초』로 환산하면... SELECT (1일) + (2시간) + (3분) + (4초) FROM DUAL; SELECT (1*24*60*60) + (2*60*60) + (3*60) + (4) FROM DUAL; --==>> 93784 --『93784』 를 다시 『일, 시간, 분, 초』로 환산하면... SELECT TRUNC(TRUNC(TRUNC(93874/60)/60)/24) "일" , MOD(TRUNC(TRUNC(93784/60)/60),24) "시간" , MOD(TRUNC(93784/60),60) "분" , MOD(93784,60) "초" FROM DUAL; --==>> 1 2 3 4 ----------------------------------------------------------------------------------------- -- 수료일까지 남은 기간 확인(날짜 기준) → 단위 : 일수 SELECT 수료일자 - 현재일자 FROM DUAL; -- 수료일자 SELECT TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') "수료일자" FROM DUAL; --==>> 2023-08-17 18:00:00 SELECT TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE " 남은 일수" FROM DUAL; --==>> 143.152615740740740740740740740740740741 --> 현재일을 기준으로 수료일까지 남은 일수( 단위 : 일) -- 수료일까지 남은 기간 확인 (날짜 기준) → 단위 : 초 SELECT (수료일까지 남은 일수) * (24 * 60 * 60) "남은 초" FROM DUAL; SELECT (TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60) "남은 초" FROM DUAL; --==>> 12368243.99999999999999999999999999999998 SELECT TRUNC(TRUNC(TRUNC((T)/60)/60)/24) "일" , MOD(TRUNC(TRUNC((T)/60)/60),24) "시간" , MOD(TRUNC((T)/60),60) "분" , MOD((T),60) "초" FROM DUAL; SELECT TRUNC(TRUNC(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60)/60)/24) "일" , MOD(TRUNC(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60)/60),24) "시간" , MOD(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60),60) "분" , MOD((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60),60) "초" FROM DUAL; --==>> 143 3 35 52.99999999999999999999999999999996 --==>> 143 3 34 3.99999999999999999999999999999996 SELECT TRUNC(TRUNC(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60)/60)/24) "일" , MOD(TRUNC(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60)/60),24) "시간" , MOD(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60),60) "분" , TRUNC(MOD((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60),60)) "초" FROM DUAL; --==>> 143 3 33 18 ----------------------------------------------------------------------------------------- SELECT SYSDATE "현재시각" , TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') "수료일" , TRUNC(TRUNC(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60)/60)/24) "일" , MOD(TRUNC(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60)/60),24) "시간" , MOD(TRUNC((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60)/60),60) "분" , TRUNC(MOD((TO_DATE('2023-08-17 18:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24 * 60 * 60),60)) "초" FROM DUAL; --==>> /* 현재시각 수료일 일 시간 분 초 ------------------- ------------------- ---------- ---------- ---------- ---------- 2023-03-27 14:29:24 2023-08-17 18:00:00 143 3 30 36 */더보기--○ 과제 -- 본인이 태어나서 현재까지... -- 얼마만큼의 일, 시간, 분, 초를 살았는지... (살고있는지...) -- 위의 내용처럼 조회하는 쿼리문을 구성한다. /* ----------------------------------------------------------------------------------- 현재 시각 | 태어난 시각 | 일 | 시간 | 분 | 초 ----------------------------------------------------------------------------------- 2023-03-27 20:20:10 | XXXX-XX-XX XX:XX:XX | XXXXXXXXXX | XX | XX | XX ----------------------------------------------------------------------------------- */
- 날짜 데이터 대상으로 반올림, 절삭 수행
- DD 와 DAY 를 헷갈리지 말것
--※ 날짜 데이터를 대상으로 반올림, 절삭을 수행할 수 있다. --○ 날짜 반올림 SELECT SYSDATE "1" -- 2023-03-27 → 기본 현재 날짜 , ROUND(SYSDATE, 'YEAR') "2" -- 2023-01-01 → 년도까지 유효한 데이터 (상반기, 하반기 기준) , ROUND(SYSDATE, 'MONTH') "3" -- 2023-04-01 → 월까지 유효한 데이터 (15일 기준) , ROUND(SYSDATE, 'DD') "4" -- 2023-03-28 → 날짜까지 유효한 데이터 (정오 기준) , ROUND(SYSDATE, 'DAY') "5" -- 2023-03-26 → 날짜까지 유효한 데이터 (수요일 기준 → 이전/다음 일요일) FROM DUAL; --○ 날짜 절삭 SELECT SYSDATE "1" -- 2023-03-27 → 기본 현재 날짜 , TRUNC(SYSDATE, 'YEAR') "2" -- 2023-01-01 → 년도까지 유효한 데이터 , TRUNC(SYSDATE, 'MONTH') "3" -- 2023-03-01 → 월까지 유효한 데이터 , TRUNC(SYSDATE, 'DD') "4" -- 2023-03-27 → 날짜까지 유효한 데이터 , TRUNC(SYSDATE, 'DAY') "5" -- 2023-03-26 → 날짜까지 유효한 데이터 (지난주 일요일) FROM DUAL;
- 변환함수
- 날짜나 통화 형식이 맞지 않을 경우, 같은 값을 확인하기 어려우므로
하단의 더보기를 통해 같은 세션 설정 값을 통해서 설정할 수 있도록 한다.
--■■■ 변환 함수 ■■■-- -- TO_CHAR() : 숫자나 날짜 데이터를 문자 타입으로 변환시켜주는 함수 -- TO_DATE() : 문자 데이터(날짜 형식)를 날짜 타입으로 변환시켜주는 함수 -- TO_NUMBER() : 문자 데이터(숫자 형식)를 숫자 타입으로 변환시켜주는 함수더보기--※ 날짜나 통화 형식이 맞지 않을 경우 -- 세션 설정 값을 통해 설정할 수 있도록 한다. ALTER SESSION SET NLS_LANGUAGE = 'KOREAN'; --==>> Session이(가) 변경되었습니다. --// 해당 언어 한국어 ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN'; --==>> Session이(가) 변경되었습니다. --// 날짜 형식을 한국어로 ALTER SESSION SET NLS_CURRENCY = '\'; --==>> Session이(가) 변경되었습니다. --// 화폐단위 : 원 (￦) ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; --==>> Session이(가) 변경되었습니다. --// 보여지는 날짜형식 연-월-일
- TO_CHAR()
- 시각적으로 보이는 모양은 날짜 형식을 띄고 있으나 "문자타입" 임을 자각
SELECT 10 "1" , TO_CHAR(10) "2" --// 시각적으로는 같아보이나 문자타입 FROM DUAL; --==> 10 10 -- (컬럼내 우측정렬) (컬럼내 좌측정렬) --//(컬럼 내 좌측정렬 되어있음) SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "1" -- 2023-03-27 ,TO_CHAR(SYSDATE, 'YYYY') "2" -- 2023 ,TO_CHAR(SYSDATE, 'YEAR') "3" -- TWENTY TWENTY-THREE // 한글로 구성되어있지 않음 ,TO_CHAR(SYSDATE, 'MM') "4" -- 03 ,TO_CHAR(SYSDATE, 'MONTH') "5" -- 3월 // 이렇게 쓸 수 있음 ,TO_CHAR(SYSDATE, 'MON') "6" -- 3월 // 단, 이렇게 많이 씀 ,TO_CHAR(SYSDATE, 'DD') "7" -- 27 ,TO_CHAR(SYSDATE, 'DAY') "8" -- 월요일 // 요일 전체 출력 ,TO_CHAR(SYSDATE, 'DY') "9" -- 월 // 요일 줄여서 출력 ,TO_CHAR(SYSDATE, 'HH24') "10" -- 15 ,TO_CHAR(SYSDATE, 'HH') "11" -- 03 ,TO_CHAR(SYSDATE, 'HH AM') "12" -- 03 오후// AM 이든 PM 이든 현재 시각에 따라 ,TO_CHAR(SYSDATE, 'HH PM') "13" -- 03 오후// 오전, 오후로 나온다 ,TO_CHAR(SYSDATE, 'MI') "14" -- 07 ,TO_CHAR(SYSDATE, 'SS') "15" -- 28 ,TO_CHAR(SYSDATE, 'SSSSS') "16" -- 54448 → S 다섯개 → 금일 흘러온 전체 초 (0시 0분 0초 부터 누적된 초) ,TO_CHAR(SYSDATE, 'Q') "17" -- 1 → (사)분기 FROM DUAL;
- TO_NUMBER()
- 문자타입을 숫자 타입으로
- 날짜 타입을 숫자 타입으로 만들기 위해서는 날짜 > 문자 > 숫자 로 거쳐 가게 됨
SELECT '04' "1" , TO_NUMBER('04') "2" FROM DUAL; --==>> 04 4 // 숫자 타입은 앞의 0 이 탈락되게 된다 SELECT TO_CHAR(4) "1" , '4' "2" FROM DUAL; --==>> 4 4 // 동일한 문자 타입의 4 SELECT TO_CHAR(SYSDATE,'YYYY') "확인" FROM DUAL; --==>> 2023 → 문자 타입 SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) "확인" FROM DUAL; --==>> 2023 → 숫자 타입- EXTRACT()
- 연, 월, 일 만을 날짜에서 추출하여 바로 숫자 타입으로 바꿀 수 있는 함수
- 이외의 것은 불가능
--○ EXTRACT() SELECT TO_CHAR(SYSDATE, 'YYYY')"1" -- 2023 → 연도를 추출하여 문자타입으로... , TO_CHAR(SYSDATE, 'MM')"2" -- 03 → 월을 추출하여 문자타입으로 ... , TO_CHAR(SYSDATE, 'DD')"3" -- 27 → 일을 추출하여 문자타입으로... , EXTRACT(YEAR FROM SYSDATE) "4" -- 2023 → 연도를 추출하여 숫자 타입으로... , EXTRACT(MONTH FROM SYSDATE) "5" -- 3 → 월을 추출하여 숫자 타입으로... , EXTRACT(DAY FROM SYSDATE) "6" -- 27 → 일을 추출하여 숫자 타입으로... FROM DUAL; --==>> 2023 03 27 2023 3 27 -- ----------- ----------- -- 문자 타입 숫자 타입 --> 년, 월, 일 이하 다른 것은 불가~
- TO_CHAR() 활용
- 형식 맞춤 표기 결과값 반환
--○ TO_CHAR() 활용 → 형식 맞춤 표기 결과값 반환 SELECT 70000 "1" -- 70000 // 숫자타입 , TO_CHAR(70000) "2" -- 70000 // 문자타입 , TO_CHAR(70000, '999,999')"3" -- 70,000 // 옵션 추가하여 구두점 표시 , TO_CHAR(70000, '$999,999')"4" -- $70,000 // 옵셥 추가하여 통화기호 표시 , TO_CHAR(70000, 'L999,999')"5" -- \70,000 // 옵션 : 통화 기호 표시해라 (\원) , LTRIM(TO_CHAR(70000, 'L999,999'))"6" -- \70,000 --CHECK FROM DUAL; --// 통화기호에 따라서 표시할 때 필요한 자리를미리 확보해두기 때문에 공백이 많이 발생하게 됨 --// 따라서 LTRIM(왼쪽 공백제거) 와 함께 같이 사용하는 것이 일반적이다.
- 날짜관련 함수 ~년 ~월 ~일 ~시 ~분 ~초 이후 시간 조회 하는 함수
-TO_YMINTERVAL(), TO_DSINTERVAL()
--※ 날짜 세션 설정 변경 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; --==>> Session이(가) 변경되었습니다. --○ 현재 시간을 기준으로 1일 2시간 3분 4초 후를 조회한다. SELECT SYSDATE "현재시각" , SYSDATE + (1일) + (2시간) + (3분) + (4초) "1일2시간3분4초후" FROM DUAL; SELECT SYSDATE "현재시각" , SYSDATE + ( 1 + 2/24 + 3/24/60 + 4/24/60/60 ) "1일2시간3분4초후" FROM DUAL; --==>> /* 2023-03-27 15:47:19 2023-03-28 17:50:23 */ --○ 현재 시각을 기준으로 1년 2개월 3일 4시간 5분 6초 후를 조회한다. -- TO_YMINTERVAL(), TO_DSINTERVAL() --// YM 연,월 // DS 날짜,초 SELECT SYSDATE "현재시각" , SYSDATE + TO_YMINTERVAL('01-02') + TO_DSINTERVAL('003 04:05:06') "연산 결과" FROM DUAL; --==>> /* 2023-03-27 15:51:03 2024-05-30 19:56:09 */
- CASE 문 ( 조건문, 분기문)
/* CASE // 상황 WHEN // ~일 때 THEN // ~를 구성하겠다 ELSE // 그 외에는 ~로 하겠다 END // CASE문을 끝내겠다. */- 관찰
더보기SELECT CASE 5+2 WHEN 7 THEN '5+2=7' ELSE '5+2는 몰라요' END "결과 확인" FROM DUAL; --==>> 5+2=7 SELECT CASE 5+2 WHEN 9 THEN '5+2=9' ELSE '5+2는 몰라요' END "결과 확인" FROM DUAL; --==>> 5+2는 몰라요 --// 확장판 WHEN과 THEN의 갯수를 늘인다. SELECT CASE 1+1 WHEN 2 THEN '1+1=2' WHEN 3 THEN '1+1=3' WHEN 4 THEN '1+1=4' ELSE '몰라요' END "결과 확인" FROM DUAL; --==>> 1+1=2 SELECT CASE 3+1 WHEN 2 THEN '3+1=2' WHEN 3 THEN '3+1=3' WHEN 4 THEN '3+1=4' ELSE '몰라요' END "결과 확인" FROM DUAL; --==>> 3+1=4 SELECT CASE 5+1 WHEN 2 THEN '5+1=2' WHEN 3 THEN '5+1=3' WHEN 4 THEN '5+1=4' ELSE '몰라요' END "결과 확인" FROM DUAL; --==>> 몰라요 SELECT CASE 1+2 WHEN 1 THEN '1+2=1' WHEN 2 THEN '1+2=2' WHEN 3 THEN '1+2=3' WHEN 4 THEN '1+2=4' WHEN 5 THEN '1+2=5' ELSE '몰라요' END "결과확인" FROM DUAL; --==>> 1+2=3 --// 확장판 CASE 와 WHEN을 묶는 형태와 THEN의 갯수를 늘인다. SELECT CASE WHEN 1+1=3 THEN '1+1=3' WHEN 2+2=4 THEN '2+2=4' WHEN 3+3=9 THEN '3+3=9' ELSE '몰라요' END "결과 확인" FROM DUAL; --==>> 2+2=4 CASE WHEN THEN TRUE/FALSE 를 확인하여 결과 도출 SELECT CASE WHEN 1+1=3 THEN '1+1=3' WHEN 2+2=4 THEN '2+2=4' WHEN 3+3=6 THEN '3+3=6' ELSE '몰라요' END "결과 확인" FROM DUAL; --==>> 2+2=4 --//분기가 이루어져있을 때 TRUE 인 값을 찾게 되면 이후는 보지 않고 완료 함- 활용 관찰
더보기--○ CASE WHEN THEN ELSE END 구문 활용 SELECT CASE WHEN 5<2 THEN '5<2' WHEN 5>2 THEN '5>2' ELSE '5와 2는 비교불가' END "결과 확인" FROM DUAL; --==>> 5>2 SELECT CASE WHEN 5<2 OR 3>1 AND 2=2 THEN 'ㅈㅎ1만세' WHEN 5>2 OR 2=3 THEN 'ㅈㅎ2만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; /* SELECT CASE WHEN F OR T AND T THEN 'ㅈㅎ1만세' WHEN 5>2 OR 2=3 THEN 'ㅈㅎ2만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN T THEN 'ㅈㅎ1만세' WHEN 5>2 OR 2=3 THEN 'ㅈㅎ2만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; */ --==>> ㅈㅎ1만세 SELECT CASE WHEN 3<1 AND 5<2 OR 3>1 AND 2=2 THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; /* SELECT CASE WHEN 3<1 AND 5<2 OR 3>1 AND 2=2 THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN F AND F OR T AND 2=2 THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN F OR T AND 2=2 THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN T AND T THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN T THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; */ --==>> ㅈㅎ1 만세 SELECT CASE WHEN 3<1 AND (5<2 OR 3>1) AND 2=2 THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; /* SELECT CASE WHEN 3<1 AND (F OR T) AND 2=2 THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN F AND T AND 2=2 THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN F AND T THEN 'ㅈㅎ1 만세' WHEN 5<2 AND 2=2 THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN F THEN 'ㅈㅎ1 만세' WHEN F AND T THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; SELECT CASE WHEN F THEN 'ㅈㅎ1 만세' WHEN F THEN 'ㅈㅎ2 만세' ELSE 'ㅎㅈ만세' END "결과 확인" FROM DUAL; */ --==>> ㅎㅈ만세- DECODE()
--○ DECODE() SELECT DECODE(5-2,1,'5-2=1',2,'5-2=2',3,'5-2=3','5-2는 몰라') "결과 확인" FROM DUAL; --==>> 5-2=3
더보기○ 문제) (풀이는 내일...)
--○ TBL_SAWON 테이블을 활용하여 다음과 같은 항목들을 조회한다. -- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일 -- , 정년퇴직일, 근무일수, 남은일수, 급여, 보너스 -- 단, 현재나이는 한국나이 계산법에 따라 연산을 수행한다. (태어난 해에 한 살 념념) -- 또한, 정년 퇴직일은 해당 직원의 나이가 한국나이로 60세가 되는 해(연도)의 -- 그 직원의 입사 월, 일 로 연산을 수행한다. -- 그리고, 보너스는 근무일수에 따라 1000일 이상 2000일 미만인 사원은 -- 해당 사원의 원래 급여 기준 30%를 지급하고, -- 2000일 이상 근무한 사원은 -- 해당 사원의 원래 급여 기준 50%를 지급할 수 있도록 처리한다. SELECT * FROM TBL_SAWON; SELECT SANO "사원번호" , SANAME " 사원명" , JUBUN "주민번호" , CASE WHEN SUBSTR(JUBUN,7,1) IN (1,3) THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN (2,4) THEN '여성' ELSE '외계인인가보지..' END "성별" , (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - CASE WHEN SUBSTR(JUBUN,1,2) >=23 THEN '19' || SUBSTR(JUBUN,1,2) WHEN SUBSTR(JUBUN,1,2) <23 THEN '20' || SUBSTR(JUBUN,1,2) ELSE '에?' END + 1 )"현재나이" , HIREDATE "입사일" , TO_DATE(((CASE WHEN SUBSTR(JUBUN,1,2) >=23 THEN '19' || SUBSTR(JUBUN,1,2) WHEN SUBSTR(JUBUN,1,2) <23 THEN '20' || SUBSTR(JUBUN,1,2) ELSE '에?' END + 59) || SUBSTR(TO_CHAR(HIREDATE,'YYYYMMDD'),5,4)),'YYYY-MM-DD') "정년퇴직일" , TRUNC(SYSDATE - HIREDATE) "근무일수" , TRUNC(TO_DATE(((CASE WHEN SUBSTR(JUBUN,1,2) >=23 THEN '19' || SUBSTR(JUBUN,1,2) WHEN SUBSTR(JUBUN,1,2) <23 THEN '20' || SUBSTR(JUBUN,1,2) ELSE '에?' END + 59) || SUBSTR(TO_CHAR(HIREDATE,'YYYYMMDD'),5,4)),'YYYY-MM-DD') - SYSDATE) "남은 일수" , SAL "급여" , CASE WHEN TRUNC(SYSDATE - HIREDATE) >= 1000 AND TRUNC(SYSDATE - HIREDATE) < 2000 THEN TO_CHAR(SAL * 0.3) WHEN TRUNC(SYSDATE - HIREDATE) >= 2000 THEN TO_CHAR(SAL*0.5) ELSE '?양심 ㅇㄷ?' END "보너스" FROM TBL_SAWON; -- 성별 구하기 SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN (1,3) THEN '남성' WHEN SUBSTR(JUBUN,7,1) IN (2,4) THEN '여성' ELSE '외계인인가보지..' END "성별" FROM TBL_SAWON; SELECT (해당 연도) - (태어난연도) + 1살 "현재나이" FROM TBL_SAWON; -- 현재나이 구하기 SELECT (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - CASE WHEN SUBSTR(JUBUN,1,2) >=23 THEN '19' || SUBSTR(JUBUN,1,2) WHEN SUBSTR(JUBUN,1,2) <23 THEN '20' || SUBSTR(JUBUN,1,2) ELSE '에?' END + 1 )"현재나이" FROM TBL_SAWON; --태어난 연도 SELECT CASE WHEN SUBSTR(JUBUN,1,2) >=23 THEN '19' || SUBSTR(JUBUN,1,2) WHEN SUBSTR(JUBUN,1,2) <23 THEN '20' || SUBSTR(JUBUN,1,2) ELSE '에?' END FROM TBL_SAWON; --정년 퇴직일 구하기 SELECT 60살 되는 해 + 입사일자 FROM TBL_SAWON; SELECT TO_DATE(((CASE WHEN SUBSTR(JUBUN,1,2) >=10 THEN '19' || SUBSTR(JUBUN,1,2) WHEN SUBSTR(JUBUN,1,2) <10 THEN '20' || SUBSTR(JUBUN,1,2) ELSE '에?' END + 59) || SUBSTR(TO_CHAR(HIREDATE,'YYYYMMDD'),5,4)),'YYYY-MM-DD') "정년퇴직일" FROM TBL_SAWON; -- 60살이 되는 해 SELECT (CASE WHEN SUBSTR(JUBUN,1,2) >=23 THEN '19' || SUBSTR(JUBUN,1,2) WHEN SUBSTR(JUBUN,1,2) <23 THEN '20' || SUBSTR(JUBUN,1,2) ELSE '에?' END + 59) FROM TBL_SAWON; -- 근무일수 구하기 SELECT TRUNC(SYSDATE - HIREDATE) "근무일수" FROM TBL_SAWON; -- 남은 (근무) 일수 구하기 SELECT 정년 퇴직일 - 오늘 날짜 FROM TBL_SAWON; SELECT TRUNC(TO_DATE(((CASE WHEN SUBSTR(JUBUN,1,2) >=10 THEN '19' || SUBSTR(JUBUN,1,2) WHEN SUBSTR(JUBUN,1,2) <10 THEN '20' || SUBSTR(JUBUN,1,2) ELSE '에?' END + 59) || SUBSTR(TO_CHAR(HIREDATE,'YYYYMMDD'),5,4)),'YYYY-MM-DD') - SYSDATE) "남은 일수" FROM TBL_SAWON; -- 보너스 구하기 -- 그리고, 보너스는 근무일수에 따라 1000일 이상 2000일 미만인 사원은 -- 해당 사원의 원래 급여 기준 30%를 지급하고, -- 2000일 이상 근무한 사원은 -- 해당 사원의 원래 급여 기준 50%를 지급할 수 있도록 처리한다. SELECT CASE WHEN TRUNC(SYSDATE - HIREDATE) >= 1000 AND TRUNC(SYSDATE - HIREDATE) < 2000 THEN TO_CHAR(SAL * 0.3) WHEN TRUNC(SYSDATE - HIREDATE) >= 2000 THEN TO_CHAR(SAL*0.5) ELSE '?양심 ㅇㄷ?' END "보너스" FROM TBL_SAWON;728x90'SsY > Class' 카테고리의 다른 글
- 지난 주에 이어서...