Oracle 3일차(1)-숫자함수,날짜함수,문자함수,그룹함수, 분석함수

1. 숫자함수

ROUND : 반올림

  1 2  3 4 .   5 6

 -4 -3 -2 -1 0 1  2

 

 소숫점 아래,0 : 그 자리까지 표시

 소숫점 위     : 그 자리에서 반올림

TRUNC : 절삭

CEIL/FLOOR : 올림/ 내림. 자릿값X, 무조건 올리고 내림

MOD : 나머지

ABS : 절대값

SIGN : 양수 1, 음수 -1, 영:0

POWER : 지수

SELECT POWER(2,4) FROM DUAL;  ▶16

SQRT : 제곱근

SELECT SQRT(9) FROM DUAL;   ▶3

2. 문자함수

UPPER : 소문자  → 대문자

LOWER : 대문자 → 소문자

SELECT UPPER(‘oralce’) FROM DUAL;  ▶ORACLE

INITCAP : 첫번째 문자는 대문자로

SELECT INITCAP('KOREA FIGHTING') FROM DUAL;    ▶ Korea Fighting

CONCAT : 두 단어를 합침. ||를 더 많이 사용

SELECT CONCAT(PNO, PNAME) FROM PERSONNEL;

LENGTH : 길이  ▶ LENGTH(‘KOREA’)

SUBSTR

SUBSTR(‘EXAMPLE’,NUM1,NUM2)  

→ EXAMPLE의 NUM1부터 NUM2까지

SELECT SUBSTR(‘ABCDEF’,1,3) FROM DUAL;  ▶ ABC

INSTR

INSTR(‘EXAMPLE’, ‘CHAR’)  ▶ EXAMPLE에서 처음 CHAR의 위치

RPAD / LPAD

RPAD(WORD, 5 ,’*’) ▶WORD에서 부족한 글자수는 *로 바꿔 5글자를 만듦

RTRIM / LTRIM

RTRIM(WORD,CHAR) ▶ WORD에서 CHAR를 지움

SELECT LTRIM('BBBABBB','B') FROM DUAL;  ▶ ABBB

3. 날짜함수

SYSDATE

▶ 현재날짜

▶ 연산가능

ROUND : 반올림

SELECT STARTDATE, ROUND(‘2019-08-08’, 'YEAR') FROM PERSONNEL;     ▶ 2019-01-01

SELECT STARTDATE, ROUND(‘2019-08-08’, 'MONTH') FROM PERSONNEL;  ▶ 2019-08-01

EXTRACT

SELECT STARTDATE

EXTRACT(YEAR FROM STARTDATE) 년,

EXTRACT(MONTH FROM STARTDATE) 월,

EXTRACT(DAY FROM STARTDATE) 일

FROM DUAL;

SELECT

EXTRACT(YEAR FROM TO_DATE('2019-01-08')) 년,     ▶ 2019

EXTRACT(MONTH FROM TO_DATE('2019-01-08')) 월,  ▶ 1

EXTRACT(DAY FROM TO_DATE('2019-01-08')) 일         ▶ 8

FROM DUAL;

TRUNC : 그냥 버림

SELECT STARTDATE, ROUND(‘2019-08-08’, 'YEAR') FROM PERSONNEL;     ▶ 2029-01-01

SELECT STARTDATE, ROUND(‘2019-08-08’, 'MONTH') FROM PERSONNEL;  ▶ 2019-08-01

MONTHS_BETWEEN : 달수

LAST_DAY : 월의 마지막 날

LAST_DAY(TO_DATE(‘2019-08-05’)) ▶ 2019-08-31

NEXT_DAY

SELECT NEXT_DAY(SYSDATE,'토요일') FROM DUAL;       ▶ 다음 토요일

SELECT NEXT_DAY('2019-10-10','일요일') FROM DUAL;    ▶ 다음 일요일

ADD_MONTHS

ADD_MONTHS(SYSDATE,7)      ▶ SYSDATE에서 7개월 후

TO_CHAR

♤날짜

▶ MM : 달수(3)

▶ NON : 3문자 달 이름(MAR)

▶ MONTH : 달의 이름(MARCH)

▶ DD : 달의 날짜 수(22)

▶ D : 주의 일수(2)

▶ DY : 3문자의 요일이름(MON)

▶ DAY : 요일 이름(MONDAY)

▶ YYYY : 연도(2019)

▶ YY : 2문자 연도(19)

▶ RM : 로마식 달수(IX)

TO_CHAR(SYSDATE, ‘YY’)

♤시간

▶ HH,HH12 : 12시간

▶ HH24 : 24시간

▶ MI : 분

▶ SS : 초

▶ AM, PM, A.M., P.M. : 오전 오후

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; -- 14:45:40

SELECT TO_CHAR(SYSDATE, 'AMHH:MI') FROM DUAL; -- 오후02:45

SELECT TO_CHAR(SYSDATE, 'AM HH:MI') FROM DUAL; -- 오후 02:45

SELECT TO_CHAR(SYSDATE, 'P.M. HH:MI') FROM DUAL; -- 오후 02:45

♤숫자

TO_CHAR(12506, ‘99,999.99’)   ▶ 12,506.00

TO_CHAR(12506,'$099,999')     ▶ $012,506

TO_CHAR(-12506,'99,999PR')   ▶ -12,506

♤서수 - SP, TH

TO_CHAR(SYSDATE,'DD')  ▶ 08

TO_CHAR(SYSDATE,'DDSP')    ▶ EIGHT

TO_CHAR(SYSDATE,'DDTH')  ▶O8TH

TO_CHAR(SYSDATE,'DDSPTH') ▶ EIGHTTH

TO_DATE : 문자를 날짜로 변경

TO_DATE(‘2019-05-09’, ‘YYYY-MM-DD’)   ▶ ‘YYYY-MM-DD’ 안적어도 괜찮

4. 그 외

TO_NUMBER : 문자(숫자)를 숫자로 변경

▶ 연산가능

TO_NUMBER(‘1123’)

NVL

NVL(BONUS,0)   

→ BONUS값이 있으면 출력, 없으면 0출력

DECODE(IF)

DECODE(DNO, 조건1, 값1, 조건2, 값2, 그외)   

→DNO가 조건1을 만족하면 값1 출력. 조건1과 조건2 둘다X 그외 출력

5. 그룹함수

COUNT : 행개수

COUNT(*)

AVG : 평균 / SUM : 합계

AVG(PAY) / SUM(PAY)

MAX / MIN : 최대 / 최소

MAX(PAY) / MIN(PAY)

6. 분석함수

DISTINCT ~~ OVER(PARTITION BY ~~)부서별 평균 구하기

SELECT DISTINCT DNO,AVG(PAY) OVER(PARTITION BY DNO) 부서별평균

FROM PERSONNEL;

RANK( ) WITHIN GROUP(ORDER BY ~~ DESC) 월급 1200을 받는 사람 순위

SELECT RANK(1200) WITHIN GROUP(ORDER BY PAY DESC) RANK

FROM PERSONNEL;

SELECT PNAME, PAY, RANK() OVER(ORDER BY PAY DESC) RANK

FROM PERSONNEL;

FIRST_VALUE(~) OVER(PARTIRION BY ~~ ORDER BY ~~)전체사원의 급여와 함께 부서별 최고 급여

SELECT DNO,PNAME,PAY,FIRST_VALUE(PAY) OVER(PARTITION BY DNO ORDER BY PAY DESC) AA

FROM PERSONNEL;