은은하게 코드 뿌시기

[DB] 시퀀스(sequence) - ORACLE/MYSQL sequence 만들기 본문

Database -Mysql,Oracle

[DB] 시퀀스(sequence) - ORACLE/MYSQL sequence 만들기

은은하게미친자 2022. 8. 29. 23:08
728x90

[ORACLE] 시퀀스(sequence) ?

: 오라클 데이터베이스 에서 특정 규칙에 맞게 연속 숫자를 생성하는 객체 

select max() 등의 방식을 사용해도 좋지만 데이터가 많아 질수록 가장 큰데이터를 찾고 새로운 번호를 계산하는 시간이 함께 늘어나고, 동시에 여러곳에서 새로운 번호를 요구 했을 경우 중복의 가능성이 있습니다.

 

+ MYSQL에서는 시퀀스기능이  없다, 하지만 함수형태로 만들어 쓸수는 있다. MYSQL 사용법은 아래에서 추가 설명

 

명령어 종류 적용방법
시퀀스 생성 CREATE SEQUENCE 시퀀스이름
[INCREMENT BY N]
[START WITH N]
[MAXVALUE N | NOMAXLVALUE]
[MINVALUE N  | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE N | NOCACHE]


생성할 시퀀스 지정 아래 절들을 지정하지 않을경우 1씩증가로 자동생성
생성할번호의 증가값, 기본값1
생성할 시작값, 기본값 1
최댓값, NOMAXVAUE일경우 오름차순엔10의27승, 내림차순일경우 -1
최소값, NOMINVALUE일경우 오름차순엔 1, 내림차순엔 -10의 26승
시퀀스 생성번호가 최댓값에 도달할경우 CYCLE 은 다시시작, NOCYCLE 오류발생
생성할 번호를 메모리에 미리 할당해 놓은 수를 지정, 기본값 20, NOCACHE 미리생성안함.
생성한 시퀀스 확인하기 SELECT * FROM USER_SEQUENCES;
시퀀스 에서 생성한 순번을 사용한 INSERT문 실행하기 
테이블명 : DEPT_SEQUNCE
시퀀스명 : SEQ_DEPT_SEQUENCE		 INSERT INTO DEPT_SEQUNCE(DEPTNO,DNAME,LOC)
VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL')


SELECT * FROM SEQ_DEPT_SEQUENCE;
시퀀스 삭제 DROP SEQUENCE SEQ_DEPT_SEQUENCE;


SELECT * FROM SEQ_DEPT_SEQUENCE;

MYSQL 에서 SEQUENCE 만들어 사용하기

  1. 시퀀스 사용할 테이블 생성하기
  2. 시퀀스를 생성/삭제 procedure만들기
  3. 생성한 시퀀스 현재값/다음값/값 수정 function 만들기
  4. 시퀀스 생성할 프로시저 실행
  5. 함수 호출해서 사용하기
시퀀스 사용할 테이블
생성하기
1
2
3
4
5
6
CREATE TABLE sequences 
(
 name varchar(32), 
 currval BIGINT UNSIGNED 
) 
ENGINE=InnoDB;
 cs
innoDB에만든다.
시퀀스 삭제하는 함수
1
2
3
4
5
6
7
DELIMITER $$
CREATE PROCEDURE `drop_sequence` (IN the_name text)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DELETE FROM sequences WHERE name=the_name;
END
 cs
시퀀스 생성하는 함수
1
2
3
4
5
6
7
8
DELIMITER $$
CREATE PROCEDURE `create_sequence`(IN the_name text)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DELETE FROM sequences WHERE name=the_name;
    INSERT INTO sequences VALUES (the_name, 0);
END
 cs
다음값을 가져오는 함수
1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$
CREATE FUNCTION `nextval`(the_name varchar(32))
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DECLARE ret BIGINT UNSIGNED;
    UPDATE sequences SET currval=currval+1 WHERE name=the_name;
    SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
    RETURN ret;
END
 cs
현재 값/ 마지막 사용값
을 가져오는 함수
1
2
3
4
5
6
7
8
9
10
DELIMITER $$
CREATE FUNCTION `currval` (the_name varchar(32))
RETURNS BIGINT UNSIGNED
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE ret BIGINT UNSIGNED;
    SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
    RETURN ret;
END
 cs
SEQ값을 수정하는함수
1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$
CREATE FUNCTION `setval` (the_name varchar(32), the_val BIGINT UNSIGNED)
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DECLARE ret BIGINT UNSIGNED;
    UPDATE sequences SET currval=the_val WHERE name=the_name;
    SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
    RETURN ret;
END
 cs
시퀀스를 생성할 
프로시저 실행
1
2
3
4
5
6
-- 안전모드해제
SET SQL_SAFE_UPDATES = 0;   
 
-- 시퀀스 
call create_sequence('test');

-- 안전모드ON
SET SQL_SAFE_UPDATES = 1;   
 cs
사용하기
1
2
3
4
5
6
7
8
-- 안전모드해제
SET SQL_SAFE_UPDATES = 0; 
 
-- 시퀀스값 조회
SELECT nextval('test');
 
-- 안전모드 ON
SET SQL_SAFE_UPDATES = 1;  
 cs

 

 

추가적인 응용 처리방식참고 : 

https://velog.io/@inyong_pang/MySQL-MySQL-Sequence-%EB%A7%8C%EB%93%A4%EA%B8%B0

 

[MySQL] MySQL Sequence 만들기

자동문자열 방식으로 Primary Key를 생성하고자 할 때, MySQL에서 시퀀스(sequnce)방식으로 구현 가능\-sequence테이블의 id(AUTO_INCEMENT인)값을 활용MySQL TRIIGER만들어서 LAPD함수의 인자로 원하는 문자열과 se

velog.io

 

728x90

'Database -Mysql,Oracle' 카테고리의 다른 글

[DB] 오라클 설치 19.3 / Oracle setup  (0) 2022.11.07
[DB] 동의어(synonym)-ORACLE/MYSQL  (0) 2022.08.29
[DB] 뷰(view) /인라인뷰/ ROWNUM  (0) 2022.08.26
[DB] 인덱스 - Oracle/MYSQL  (0) 2022.08.24
[DB] 데이터사전(data dictionary) - oracle, mysql / information_schema  (0) 2022.08.23
'Database -Mysql,Oracle' Related Articles more
Comments