뷰·시퀀스·자동 증가

CREATE SEQUENCE seq_order START WITH 1000 INCREMENT BY 1 CACHE 20;

SELECT NEXT VALUE FOR seq_order AS next_id;

INSERT INTO orders_seq VALUES (NEXT VALUE FOR seq_order, '김민준', 300);
INSERT INTO orders_seq VALUES (NEXT VALUE FOR seq_order, '박서준', 500);

SELECT
       id
     , cust
     , amt
  FROM orders_seq
 ORDER BY id;

-- 같은 세션에서 마지막으로 뽑은 값을 다시 확인한다(Oracle 의 CURRVAL 과 같은 역할)
SELECT CURRENT VALUE FOR seq_order AS current_id;

ID   | CUST   | AMT
-----+--------+----
1001 | 김민준 | 300
1002 | 박서준 | 500
(2행)

CURRENT_ID
----------
1002
(1행)

CREATE TABLE members (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, nm VARCHAR(20));
INSERT INTO members (nm) VALUES ('김철수'), ('이영희');

SELECT
       id
     , nm
  FROM members
 ORDER BY id;

-- GENERATED ALWAYS 는 id 값을 직접 지정할 수 없다
-- @error
INSERT INTO members (id, nm) VALUES (100, '직접지정');

-- GENERATED BY DEFAULT 는 값을 생략하면 자동 채번하되 직접 지정도 허용한다
CREATE TABLE members2 (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, nm VARCHAR(20));
INSERT INTO members2 (nm) VALUES ('김철수');
INSERT INTO members2 (id, nm) VALUES (100, '직접지정');

-- 카운터가 직접 지정한 100 을 기준으로 당겨지지 않아 다음 값은 여전히 2다
INSERT INTO members2 (nm) VALUES ('다음값');

SELECT
       id
     , nm
  FROM members2
 ORDER BY id;

ID | NM
---+-------
1  | 김철수
2  | 이영희
(2행)

예상 오류: Generated column "PUBLIC.MEMBERS.ID" cannot be assigned

ID  | NM
----+---------
1   | 김철수
2   | 다음값
100 | 직접지정
(3행)

SELECT NEXT VALUE FOR seq_order AS before_rollback;

SET AUTOCOMMIT FALSE;
INSERT INTO orders_seq VALUES (NEXT VALUE FOR seq_order, '취소될주문', 100);
ROLLBACK;

-- 롤백된 행은 없지만 방금 뽑은 번호는 이미 소모되어 되돌아오지 않는다
INSERT INTO orders_seq VALUES (NEXT VALUE FOR seq_order, '최도윤', 200);
COMMIT;
SET AUTOCOMMIT TRUE;

SELECT
       id
     , cust
     , amt
  FROM orders_seq
 ORDER BY id;

BEFORE_ROLLBACK
---------------
1003
(1행)

ID   | CUST   | AMT
-----+--------+----
1001 | 김민준 | 300
1002 | 박서준 | 500
1005 | 최도윤 | 200
(3행)

-- Oracle · Tibero
SET MODE Oracle;
CREATE SEQUENCE seq_ora START WITH 500 INCREMENT BY 1;

SELECT seq_ora.NEXTVAL AS next_id FROM DUAL;

-- CURRVAL 은 같은 세션에서 NEXTVAL 을 한 번 호출한 뒤에만 쓸 수 있다
SELECT seq_ora.CURRVAL AS curr_id FROM DUAL;

-- MySQL
SET MODE MySQL;
CREATE TABLE members_my (id INT AUTO_INCREMENT PRIMARY KEY, nm VARCHAR(20));
INSERT INTO members_my (nm) VALUES ('김철수'), ('이영희');

-- 이 커넥션이 마지막으로 만든 자동 증가 값(세션별로 따로 관리)
SELECT LAST_INSERT_ID() AS last_id;

NEXT_ID
-------
500
(1행)

CURR_ID
-------
500
(1행)

LAST_ID
-------
2
(1행)

-- MSSQL
SET MODE MSSQLServer;
CREATE TABLE members_ms (id INT IDENTITY(1,1) PRIMARY KEY, nm NVARCHAR(20));
INSERT INTO members_ms (nm) VALUES (N'김철수');
INSERT INTO members_ms (nm) VALUES (N'이영희');

-- 현재 범위(스코프)에서 마지막으로 만든 IDENTITY 값
SELECT SCOPE_IDENTITY() AS scope_id;

-- 2012 부터: SEQUENCE 객체도 함께 쓸 수 있다
CREATE SEQUENCE seq_ms START WITH 1 INCREMENT BY 1;

SELECT NEXT VALUE FOR seq_ms AS next_id;

SCOPE_ID
--------
2
(1행)

NEXT_ID
-------
1
(1행)