5. 함수와 시퀀스(SEQUENCE)

 

Oracle SQL에서 함수와 시퀀스에 대해 알아보겠습니다.

---

1. 함수

Oracle SQL에서 어떠한 테이블 항목의 합계/평균/최댓값/최솟값/개수를 알고 싶을 때는 함수를 이용할 수 있습니다.

공통된 사용방법은 SELECT [함수명]([항목명]) FROM [테이블명]; 입니다.

위 테이블을 이용하여 예시를 들어보겠습니다.

​

1) 합계 - SUM([항목명])

SUM 함수는 원하는 값들의 합을 보여줍니다.

ex_table의 ex_num 항목의 모든 값들의 합을 조회하고 싶다면 SELECT SUM(ex_num) FROM ex_table; 으로 합계를 조회할 수 있습니다.

​

2) 평균 - AVG([항목명])

AVG 함수는 원하는 값들의 평균을 보여줍니다.

ex_table의 ex_num 항목의 모든 값들의 평균을 조회하고 싶다면 SELECT AVG(ex_num) FROM ex_table; 으로 평균을 조회할 수 있습니다.

​

3) 최대 - MAX([항목명])

MAX 함수는 조회하기를 원하는 값들 중에서 가장 큰 값을 표시합니다.

ex_table의 ex_num 항목의 모든 값들 중 가장 큰 값을 조회하고 싶다면 SELECT MAX(ex_num) FROM ex_table; 으로 최댓값을 조회할 수 있습니다.

​

4) 최소 - MIN([항목명])

MIN 함수는 최대값과 비슷하게 조회하기를 원하는 값들 중에서 가장 작은 값을 표시합니다.

ex_table의 ex_num 항목의 모든 값들 중 가장 작은 값을 조회하고 싶다면 SELECT MAX(ex_num) FROM ex_table; 으로 최솟값을 조회할 수 있습니다.

​

5) 개수 - COUNT([항목명])

COUNT 함수는 원하는 항목의 값들의 개수를 나타냅니다.

언뜻 보면 동일한 값들은 제외하고 개수를 센다고 생각 할 수 있는데, 동일한 값이더라도 모두 각각의 값으로 보고 개수를 알려줍니다.

ex_table의 ex_date 항목의 모든 값들의 개수를 구하고자 하면 SELECT COUT(ex_date) FROM ex_table;으로 개수를 구할 수 있습니다.

위와 같이 홍길동과 로미오는 같은 날짜를 가지고 있지만 COUNT에서는 3개라고 출력됩니다.

COUNT 함수는 테이블 제약 조건중 NOT NULL 또는 UNIQUE, PRIMARY KEY 선언이 되지 않고, DEFAULT값이 지정되지 않은 컬럼에 대하여 빈 값이 있다면 그 값을 세지 않습니다.

---

2. 시퀀스 (SEQUENCE)

시퀀스는 레코드에 번호(인덱스)를 순차적으로 부여해주는 용도로 이용됩니다.

​

1) 시퀀스 생성방법: CREATE SEQUENCE [시퀀스 명][옵션1][옵션2]...;

시퀀스는 생성시에 옵션을 선택하여 생성합니다. (필수X)

위처럼 특별한 옵션를 지정하지 않고 시퀀스를 생성한다면 1부터 시작하고 1씩 커지며 최댓값을 지정하지 않는 시퀀스가 생성됩니다.

​

2) 시퀀스 옵션

시퀀스 생성시에 옵션을 추가하여 시퀀스에 대하여 설정할 수 있으며, 필수적이지 않고 각각의 옵션들은 문자로 구분하지 않고 띄어쓰기로 구분합니다.

START WITH n	n부터 시작하겠다.
INCREMENT BY n	n씩 증가시키겠다, 음수로 설정시 감소, default값: 1
MAXVALUE n NOMAXVALUE	최댓값을 n으로 지정 최댓값을 지정하지 않음, default 설정
MINVALUE n NOMINVALUE	최솟값을 n으로 지정 최솟값을 지정하지 않음, default 설정
CYCLE NOCYCLE	최댓값/최솟값 도달 시 순환하겠다. (다시 최솟값/최댓값으로 시작)하겠다. 순환하지 않겠다. (도달하면 시퀀스 이용 불가), default 설정
CACHE NOCACHE	미리 메모리에 생성 해두겠다. default 20 미리 메모리에 생성해두지 않겠다.

​

3) 시퀀스 사용: [시퀀스 명].NEXTVAL

시퀀스는 .NEXTVAL 으로 이용할 수 있습니다.

NEXTVAL은 해당 시퀀스의 현재 값을 옵션에 맞게 증가시킵니다.

넘어간 시퀀스의 인덱스는 이전 인덱스로 돌아올 수 없습니다. 때문에 중복 인덱스가 부여되지 않아 UNIQUE의 성질을 띕니다.

위와 같이 NEXTVAL을 한번 사용하면 ex_seq의 옵션에 맞게 1이 자동으로 추가됨을 볼 수 있고

다시한번 NEXTVAL을 사용하면 다음 숫자인 2가 출력됩니다.

​

4) 시퀀스 현재 값 조회: SELECT [시퀀스 명].CURRVAL FROM DUAL;

시퀀스를 사용할 때는 .NEXTVAL을 이용했습니다.

NEXTVAL이 인덱스값을 증가시킨다면, .CURRVAL은 현재 인덱스 값을 보여줍니다.

위에서 .NEXTVAL을 두번 사용했기 때문에 현재 ex_seq의 값은 2를 보여줍니다.

주의할 점은 시퀀스를 만들고 한번도 테이블에 시퀀스값을 추가하지 않았을 경우에는 CURRVAL(현재값 조회)를 하지 못합니다.

​

5) 현재 유저의 모든 시퀀스 정보 조회: SELECT * FROM USER_SEQUENCES;

USER_SEQUENCES를 조회하여 현재 접속한 계정이 가지고 있는 모든 시퀀스들의 정보를 조회할 수 있습니다.

여기서 ex_seq를 생성할 때 옵션들을 따로 정해주지 않아 default값으로 지정되어서 CACHE_SIZE가 20이고, LAST_NUMBER은 캐시값만큼 이미 생성시켜 대기중이므로 21을 나타내고 있습니다.

​

6) 시퀀스 삭제: DROP SEQUENCE [시퀀스 명]

DROP SEQUENCE ex_seq; 를 이용하여 시퀀스를 삭제하고, 시퀀스를 조회하였을 때 어떠한 시퀀스도 조회되지 않음을 볼 수 있습니다.