그룹 함수


 

구분

설명

 SUM

 그룹의 누적 합계를 반환한다.

 AVG

 그룹의 평균을 반환한다.

 COUNT

 그룹의 총 개수를 반환한다.

 MAX

 그룹의 최대값을 반환한다.

 MIN

 그룹의 최소값을 반환한다.

 STDDEV

 그룹의 표준편차를 반환한다.

 VARIANCE

 그룹의 분산을 반환한다.

 

# GROUP BY 절(데이터 그룹)

특정 컬럼을 기준으로 그룹 함수를 사용 할 경우 GROUP BY 절을 사용한다.

SELECT 컬럼명, 그룹 함수

 FROM 테이블명

WHERE 조건 (연산자)

GROUP BY 컬럼명;

 

# HAVING 절(그룹 제한)

그룹 함수의 결과를 제한 할 경우 HAVING 절을 사용한다.

SELECT 컬럼명, 그룹함수

 FROM 테이블명

GROUP BY 컬럼명 

HAVING 조건 (연산자);

시퀀스(Sequence)


테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기이다. 시퀀스를 기본키로 사용하게 되면 사용자의 부담을 줄일 수 있다.


CREATE SEQUENCE sequence_name

[START WITH n] // 시퀀스 번호의 시작 값을 지정할 때 사용

[INCREMENT BY n] // 연속적인 시퀀스 번호의 증가치를 지정할 때 사용

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}]

 

 

# MAXVALUE n | NOMAXVALUE

MAXVALUE는 시퀀스가 가질 수 있는 최대값을 지정한다.

NOMAXVALUE는 ASCENDING 순서일 경우 승이고, DESCENDING 순서일 경우 -1로 설정된다.

 

# MINVALUE n | NOMINVALUE

MINVALUE는 시퀀스가 가질 수 있는 최대값을 지정한다.

NOMAXVALUE는 ASCENDING 순서일 경우 1이고, DESCENDING 순서일 경우 으로 설정된다.

 

# CYCLE | NOCYCLE

CYCLE은 지정된 시퀀스 값이 최대값까지 증가가 완료되면 다시 START WITH 옵션에 지정한 시작 값에서 다시 시퀀스를 시작하도록 한다.

NOCYCLE는 증가가 완료되면 에러를 유발시킨다.

 

# CACHE n | NOCACHE

CACHE는 메모리상의 시퀀스 값을 관리하도록 하며, 기본값은 20이다.

NOCA-CHE는 원칙적으로 메모리 상에서 시퀀스를 관리하지 않는다.

 

# 시퀀스 객체 정보 보기

SELECT sequence_name, min_value, max_value, increment_by, cycle_flag
 FROM user_sequences;



1. CURRVAL과 NEXTVAL

CURRVAL(CURRENT VALUE)은 시퀀스의 현재 값을 반환하고, NEXTVAL(NEXT VALUE)은 현재 시퀀스 값의 다음 값을 반환한다.
CURRVAL을 사용하기 위해 NEXTVAL을 먼저 사용해 값을 미리 생성해 두어야 한다.

# CURRVAL와 NEXTVAL을 사용할 수 있는 경우

- 서브 쿼리가 아닌 SELECT 문
- INSERT 문의 SELECT 절
- INSERT 문의 VALUE 절
- UPDATE 문의 SET 절

 

# CURRVAL와 NEXTVAL을 사용할 수 없는 경우

- VIEW의 SELECT 절
- DISTINCT 키워드가 있는 SELECT 문
- GROUP BY, HAVING, ORDER BY 절이 있는 SELECT 문
- SELECT, DELETE, UPDATE의 서브 쿼리
- CREATE TABLE, ALTER TABLE 명령의 DEFAULT 값

 

# 시퀀스 객체로부터 새로운 값 생성

SELECT dept_seq.nextval from dual;

 

# 시퀀스 객체로부터 현재 값 보기

SELECT dept_seq.currval from dual;


2. 시퀀스 제거와 수정

# 시퀀스 제거

DROP sequnce [seq];

 

# 시퀀스 수정

ALTER SEQUENCE sequence_name
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}]


CREATE SEQUENCT와 구조가 동일하나 START WITH 옵션은 변경할 수 없다. 다른 번호에서 다시 시작하려면 이전 시퀀스를 삭제하고 다시 생성해야 한다.

NVL, DECODE, CASE 함수



1. NULL을 다른 값으로 변환하는 NVL 함수

NVL(DATA1, DATA2)

// DATA1에는 NULL을 포함하는 컬럼 또는 표현식, DATA2에는 NULL을 대체하는 값을 기술한다.

// DATA1과 DATA2는 반드시 데이터 타입이 일치해야 한다.


NVL(COMM, 0) // COMM 컬럼의 NULL을 '0'으로 변환

NVL(HIREDATE, TO_DATE('2017/5/5', 'YYYY/MM/DD') // HIREDATE럼의 NULL을 '2017/5/5'로 변환

NVL(JOB, '매니저') // JOB 컬럼의 NULL을 '매니저'로 변환

 

 

# NVL2 함수

DATA1을 검사하여 그 결과가 NULL이 아니면 DATA2를 반환하고, NULL이면 DATA3을 반환한다.

NVL2(DATA1, DATA2, DATA3)

 

# NULLIF 함수

두 표현식을 비교하여 동일한 경우 NULL을 반환하고, 동일하지 않으면 첫 번째 표현식을 반환한다.

NULLIF(DATA1, DATA2)

 

# COALESCE 함수

인수 중에서 NULL이 아닌 첫 번째 인수를 반환한다.

COALESCE(DATA1, DATA2, ... , DATA-N)

// DATA1이 NULL이 아니면 DATA1을 반환

// DATA1이 NULL이고, DATA2가 NULL이 아니면, DATA2 반환

// DATA1부터 DATA-N-1까지의 값이 NULL이고, DATA-N이 NULL이 아니면, DATA-N 반환


SELECT COALESCE(COMM, SAL, 0)

 FROM EMP

ORDER BY DEPTNO;


2. DECODE 함수

SWITCH CASE문과 같은 기능으로 여러가지 경우에 대해서 선택할 수 있도록 한다.

DECODE (표현식, 조건1, 결과1,

조건2, 결과2,

조건3, 결과3,

기본결과n

)


3. CASE 함수

IF ELSE와 유사한 구조로 조건에 따라 서로 다른 처리가 가능한(여러 가지 경우에 대해서 하나를 선택) 함수이고, DECODE보다 확장된 기능이다.

DECODE 함수는 조건이 일치(=)하는 경우만 적용하지만, CASE 함수는 다양한 비교 연산자를 이용하여 조건을 지세할 수 있고, 범위를 지정할 수 있다.

CASE 비교대상값 WHEN 조건1 THEN 결과1

                  WHEN 조건2 THEN 결과2

                  WHEN 조건3 THEN 결과3

                  ELSE 결과n

END

형 변환 함수와 오라클 주요 함수 2



1. 형 변환 함수

날짜 출력 형식

숫자 출력 형식

숫자 출력 형식

종류

의미

종류

의미

구분

설명

 YYYY

 년도 표현(4자리)

 AM 또는 PM

 오전(AM), 오후(PM) 시각 표시

0

 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다.

 YY

 년도 표현(2자리)

 HH 또는 HH12

 시간(1~12)

9

 자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다.

 MM

 월을 숫자로 표현

 HH24

 24시간으로 표현(0~24)

L

 각 지역별 통화 기호를 앞에 표시한다.

 MON

 월을 알파벳으로 표현

 MI

 분 표현

.

 소수점

 DAY

 요일 표현

 SS

 초 표현

,

 천 단위 자리 구분
 DY  요일을 약어로 표현        

 

# TO_CHAR

날짜형 혹은 숫자형을 문자형(VARCHAR2)으로 변환한다.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD'), // 현재 날짜를 기본 형식과 다른 형태로 출력
TO_CHAR(SYSDATE, 'YYYY/MM/DD DAY), // 현재 날짜를 출력하되 요일까지 함께 출력
TO_CHAR(SYSDATE, 'YYYY/MM/DD, AM HH:MI:SS'), // 현재 날짜와 시간 출력
TO_CHAR(1234000, 'L999,999,999'), // 통화 기호를 붙여 출력
TO_CHAR(123456, '0000000000'), // 빈 여백을 0으로 채움
TO_CHAR(123456, '999,999,999') // 천 단위 자리 구분
 FROM DUAL;

 

# TO_DATE

문자형을 날짜형으로 변환한다.
SELECT hiredate=TO_DATE(20170101, 'YYYYMMDD'), // 2017년 1월 1일에 입사한 사원 검색
TRUNC(SYSDATE-TO_DATE('2017/01/01', 'YYYY/MM/DD')) // 현재 날짜에서 2017년 1월 1일을 뺀 결과 출력(며칠이 지났는지 출력)
 FROM DUAL;

 

# TO_NUMBER

문자형을 숫자형으로 변환한다.
SELECT TO_NUMBER('20,000', '99,999')
- TO_NUMBER('10,000', '99,999') // 수치 형태의 문자 값의 차
 FROM DUAL;


2. 날짜 함수

구분

설명

 SYSDATE

 시스템에 저장된 현재 날짜를 반환한다.

 MONTHS_BETWEEN

 두 날짜 사이가 몇 개월인지를 반환한다.

 ADD_MONTHS

 특정 날짜에 개월 수를 더한다.

 NEXT_DAY

 특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환한다.

 LAST_DAY

 해당 달의 마지막 날짜를 반환한다.

 ROUND

 인자로 받은 날짜를 특정 기준으로 반올림한다.

 TRUNC

 인자로 받은 날짜를 특정 기준으로 버린다.

 

# SYSDATE 함수

SELECT to_char(SYSDATE-1, 'YYYY/MM/DD'), // 어제

to_char(SYSDATE, 'YYYY/MM/DD'), // 오늘

to_char(SYSDATE+1, 'YYYY/MM/DD') // 내일

 FROM DUAL;

 

# ROUND 함수

ROUND(date, format)

 

포맷 모델

단위

 CC, SCC

 4자리 연도의 끝 두 글자를 기준으로 반올림

 SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y

 년(7월 1일부터 반올림)

 DDD, D, J

 일을 기준으로 반올림

 HH, HH12, HH24

 시를 기준으로 반올림

 Q

 한 분기의 두 번째 달의 16일을 기준으로 반올림

 MONTH, MON, MM, RM

 월(16일을 기준으로 반올림)

 DAY, DY, D

 한 주가 시작되는 날짜를 기준으로 반올림

 MI

 분을 기준으로 반올림

 

# TRUNC 함수

TRUNC(date, format)

SELECT to_char(TRUNC(hiredate, 'MONTH'), 'YYYY/MM/DD') // 특정 날짜(DATE)를 달(MONTH)을 기준으로 버리기

 FROM emp;

 

# MONTHS_BETWEEN 함수

MONTHS_BETWEEN(date1, date2)


SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)) // 근무달수(두 날짜 사이의 간격을 구함)

 FROM emp;

 

# ADD_MONTHS 함수

ADD_MONTHS(date, number)

SELECT to_char(ADD_MONTHS(hiredate, 6), 'YYYY/MM/DD') // 입사 6개월 후

 FROM emp;

 

# NEXT_DAY 함수

NEXT_DAY(date, 요일)

SELECT to_char(NEXT_DAY(hiredate), 'YYYY/MM/DD') // 마직막 날짜

 FROM emp;

DUAL 테이블과 오라클 주요 함수 1



1. DUAL 테이블

DUAL 테이블은 DUMMY 테이블로 쿼리문의 수행 결과가 하나의 로우로 출력되도록 구성되어 있다.

SELECT SYSDATE

 FROM DUAL;


2. 숫자 함수

구분 

설명

ABS

 절대값을 구한다.

COS

 COSINE 값을 반환한다.

EXP

 e(2.71828183...)의 n승을 반환한다.

FLOOR

 소수점 아래를 잘라낸다(버림).

POWER

 POWER(m, n) m의 n승을 반환한다.

SIGN

 SIGN (n) n<0이면 -1, n=0이면 0, n>0이면 1을 반환한다.

SIN

 SINE 값을 반환한다.

TAN

 TANGENT 값을 반환한다.

ROUND

 특정 자릿수에서 반올림한다.

LOG

 LOG 값을 반환한다.

TRUNC

 특정 자릿수에서 잘라낸다(버림).

MOD

 입력 받은 수를 나눈 나머지 값을 반환한다.

 

# ROUND 함수

ROUND(대상, 자릿수)

SELECT ROUND(34.5678) // 소수점 이하 반올림

ROUND(34.5678, 2) // 소수점 이하 두 번째 자리에서 반올림

ROUND(34.5678, -1) // 일의 자리에서 반올림

 FROM DUAL; 

 

# TRUNC 함수

SELECT TRUNC(34.5678, 2) // 소수점 이하 두 번째 자리에서 잘라내기

TRUNC(34.5678, -1) // 일의 자리에서 잘라내기

TRUNC(34.5678) // 소수점 이하 잘라내기

 FROM DUAL;

 

# MOD 함수

SELECT MOD(23, 2) //  23을 2로 나눈 나머지

 FROM DUAL;



3. 문자 처리 함수

# 대소문자 변환 함수

SELECT 'Welcome to Oracle'

UPPER('Welcome to Oracle'), // 입력한 문자값을 모두 대문자로 변환

LOWER('Welcome to Oracle'), // 입력한 문자열을 모두 소문자로 변환

INITCAP('WELCOME TO ORACLE') // 입력한 문자열의 이니셜만 대문자로 변환

 FROM DUAL;

 

# 문자 길이를 구하는 함수

SELECT LENGTH('Oracle'), LENGTH('오라클'), // 문자의 길이를 반환(한글 1Byte)

LENGTHB('Oracle'), LENGTHB('오라클') // 문자의 길이를 반환(한글 2Byte)

 FROM DUAL;

 

# 문자 조작 함수

구분

설명

CONCAT

 문자의 값을 연결한다.

SUBSTR

 문자를 잘라 추출한다(한글 1Byte).

SUBSTRB

 문자를 잘라 추출한다(한글 2Byte).

INSTR

 특정 문자의 위치 값을 반환한다(한글 1Byte).

INSTRB

 특정 문자의 위치 값을 반환한다(한글 2Byte).

LPAD, RPAD

 입력 받은 문자열과 기호를 정렬하여 특정 길이의 문자열로 반환한다.

 

# SUBSTR 함수

SUBSTR(대상, 시작위치, 추출할 개수)

SELECT SUBSTR('Welcome to Oracle', 4, 2) // 문자열의 4번째부터 2글자 추출

SUBSTR('Welcome to Oracle', -4, 2) // 문자열의 뒤쪽 4변째부터 2글자 추출

 FROM DUAL;

 

# SUBSTRB 함수

문자의 개수가 아닌 그 문자가 메모리에 저장되는 바이트 수를 센다. 영문 한 글자는 메모리에 1바이트로 저장되기 때문에 SUBSTR 함수와 SUBSTRB 함수의 결과가 동일하다. 

SELECT SUBSTR('웰컴투오라클', 4, 3), // '오라클'

SUBSTRB('웰컴투오라클', 4, 3) // '컴' SUBSTRB의 한글 한 글자는 3바이트를 차지하기 때문에 웰(1,2,3)컴(4,5,6)으로 컴(4)부터 3바이트 추출

 FROM DUAL;

 

# INSTR 함수

INSTR(대상, 찾을 글자, 시작 위치, 몇 번째 발견)

SELECT INSTR('WELCOME TO ORACLE', 'C'), // 문자열에서 C의 위치 찾기(시작 위치, 몇 번째 발견 생략 시 모두 1로 간주)

INSTR('WELCOME TO ORACLE', 'C', 6, 1) // 문자열의 6번째부터 첫 번째로 발견되는 C의 위치 찾기

 FROM DUAL;

 

# INSTRB 함수

바이트 수를 기준으로 문자의 위치를 구한다.

SELECT INSTR('데이터베이스', '이', 4, 1), 

INSTRB('데이터베이스', '이', 4, 1) // 한글 한 글자를 3바이트로 보기 때문에 데(1,2,3)이(4,5,6)에서 이(4) 위치에서 문자 1개('이') 위치 찾기

 FROM DUAL;

 

# LPAD/RPAD 함수

LPAD는 컬럼이나 대상 문자열을 명시된 자릿수의 오른쪽에 나타내고, 남은 왼쪽자리를 특정 기호로 채운다.
RPAD 또한 명시된 자릿수에서 왼쪽에 나타내고, 남은 오른쪽 자리를 특정 기호로 채운다.

SELECT LPAD('Oracle', 10, '#'), // ####Oracle

RPAD('Oracle', 10, '#') // Oracle####

 FROM DUAL;

+ Recent posts

티스토리 툴바