[DB] 오라클 함수

박정현·2023년 10월 26일

Database db oracle 함수

💽 데이터베이스

목록 보기

9/11

함수

미리 사용할 목적으로 만들어 놓은 명령문을 그룹화한 것
사용자(개발자)가 원할 때 호출(Call)할 수 있다.

그룹 함수

group by ~ having 그룹에 대한 조건

Group by

그룹별 통계(똑같은 데이터를 하나의 행으로 묶어준다.) → 계산
1. 그룹에 사용될 컬럼은 셀렉트문에 사용된 컬럼
2. 집계함수와 group by를 같이 쓰면 일반컬럼과도 사용가능하다.

group by 와 having

group by: 특정 속성을 기준으로 테이블 전체를 그룹으로 설정
select절에는 집단 연산자나 group by에 ㅏ용한 속성명만을 사용
- select sum(value) from 테이블명 group by value;
공통되는 속성값으로 그룹핑을 했으므로, 각 그룸에서 개별 튜풀을 접근할 숭 없다.
- select sum(value), id from 테이블명 group by value; —id오류

having

각그룸에 대한 제약조건을 기술
having절은 group by 절과 같이 사용
where절은 전체 테이블의 조건 설정

집합함수

테이블의 전체 행을 하나 이상의 컬럼을 기준으로 그룸화하여 그 그룹 별 통계값을 출력하는 함수
- sum: 그룹의 합계
- avg: 그룹의 평균
- count : 그룹의 개수
- max: 그룹의 최대값
- min: 그룹의 최소값
- stdev: 그룹의 표준편차 → 오라클 stddev_pop
- var: 그룹의 분산 → 오라클 var_pop

페이징

round: 반올림
- select round(1.123) from dual; —소수첫째자리에서 반올림
- select round(1.123, 2) from dual; —소수 둘째자리에서 반올림
CEIL ceil → 올림
- select ceil(0.123) from dual;
FLOOR floor → 버림
- select floor(0.123) from dual;

예제

select round(12.386) from dual; --소수 첫째자리에서 반올림 -> 기본
select round(12.386,2) from dual; --소수 셋째자리에서 반올림
select ceil(12.386) from dual; -- ceil: 올림
select floor(12.386) from dual; -- floor: 내림

순위메기기

drop table test2;

create table test2(
    kor number
);

insert into test2(kor) values(100);
insert into test2(kor) values(50);
insert into test2(kor) values(90);
insert into test2(kor) values(70);
commit;

select kor from test2;
select kor as 국어점수 from test2;
select kor as 국어점수, rank() over(order by kor desc) from test2;

rank(): 순위메긴다.

order by : 내림차순으로 정렬

문자조작함수

lower

select concat(’java’_’programing’)

…

그룹핑

연결연산자

날짜함수

1) TO_CHAR

오라클에서 쿼리문을 작성할 때 TO_CHAR() 함수는 날짜, 숫자 등의 값을 문자열로 변환하는 함수이다. 자주 사용하는 기본 함수이므로 아래의 다양한 변환 방법을 알고 있으면 많은 도움이 된다.

2) 날짜 포맷 변경 (YYYY-MM-DD)

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')--20200723
     , TO_CHAR(SYSDATE, 'YYYY/MM/DD')--2020/07/23
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD')--2020-07-23
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')--2020-07-23 11:10:52FROM dual

YYYY: 년, MM: 월, DD: 일, HH24: 24시간, HH: 12시간, MI: 분, SS:초

3) 소수점 변경

SELECT TO_CHAR(123.456, 'FM990.999')--123.456
     , TO_CHAR(1234.56, 'FM9990.99')--1234.56
     , TO_CHAR(0.12345, 'FM9990.99')--0.12FROM dual

FM: 문자열의 공백제거

숫자의 최대 길이만큰 9999... 형식을 지정한다. (9 : 값이 없으면 표시안함, 0: 값이 없으면 "0"으로 처리)

정수은 지정한 형식보다 값의 길이가 길면 오류, 소수 지정한 길이보다 길면 반올림

4) 숫자의 천 단위 콤마 찍기

SELECT TO_CHAR(123467, 'FM999,999')--123,467
     , TO_CHAR(123467890, 'FM999,999,999')--123,467,890
     , TO_CHAR(123467, 'FML999,999')--￦123,467FROM dual

숫자의 최대 길이만큰 9999... 형식을 지정한다.

5) 지정한 길이 만큼 "0"으로 채우기

SELECT TO_CHAR(123)--123
     , TO_CHAR(123, 'FM00000')--00123FROM dual

6) 날짜의 "0" 없애기

SELECT TO_CHAR(SYSDATE, 'MM/DD')--07/03
     , TO_CHAR(SYSDATE, 'FMMM/DD')--7/3FROM dual

7) 임의의 구분자로 날짜 형식 만들기

SELECT TO_CHAR(SYSDATE, '""YYYY"년 "MM"월 "DD"일"')--2020년 07월 23일
     , TO_CHAR(SYSDATE, '""HH24"시 "MI"분 "SS"초"')--11시 12분 20초FROM dual

8) 시간의 오전, 오후 값 반환

SELECT TO_CHAR(SYSDATE, 'AM')--오전
     , TO_CHAR(SYSDATE, 'AM HH:MI:SS')--오전 11:44:31
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD AMHH:MI:SS')--2020-07-23 오전11:44:31FROM dual

오라클 언어 설정에 따라서 오전, 오후 또는 AM, PM으로 반환됨

9) 날짜의 요일 반환

SELECT TO_CHAR(SYSDATE, 'D')--5 : 1(일)~7(토)
     , TO_CHAR(SYSDATE, 'DY')--목
     , TO_CHAR(SYSDATE, 'DAY')--목요일FROM dual

오라클 언어 설정에 따라서 한글 또는 영문으로 반환됨

10) 1년기준 며칠, 몇주, 분기 반환

SELECT TO_CHAR(SYSDATE, 'DDD')--365일 기준 205일
     , TO_CHAR(SYSDATE, 'WW')--1년 기준 30주
     , TO_CHAR(SYSDATE, 'Q')--3분기FROM dual

11) 간편한 날짜 변환

SELECT TO_CHAR(SYSDATE, 'MON')--7월
     , TO_CHAR(SYSDATE, 'DL')--2020년 7월 23일 목요일FROM dual