Имитация поля autoincrement

#oracle #sql

Я знаю, что в Oracle нет возможности добавить для поля autoincrement, но знаю один
способ, как это сымитировать.

Кто еще знает? Чем больше вариантов, тем интереснее.

Вот тот, что я знаю:

create table test(id number);

/* sequence - это объект базы данных, 
который генерирует целые числа в соответствии с 
правилами, установленными во время его создания. */
create sequence test_sq start with 1;

/* Получить следуйщий id */
insert into test values(test_sq.nextval);
commit;

    

Ответы

Ответ 1

Однозначно только сиквенс, вариант с max 100% вызовет проблему при работе нескольких
пользователей. не посчитайте "троллем" но max не будет использовать ни один человек
который хотя бы сколько то проработал с Oracle


Ответ 2

Начиная с 12c (12.1.0.1 06/2013) появилась identity column (=auto-increment):    

create table items (id number generated always as identity primary key, name varchar2
(32));


До этого, кроме широко известного решения с генераторами последовательностей (sequences),
можно было в качестве сурогатных ключей использовать UUID:

create table items (id raw (16) default sys_guid (), name varchar2 (32));


, что давало экономию места 8 байт (~33%) на само поле и на индекс по сравнению с NUMBER.

Для попробовать:

insert into items (name) select 'item '||rownum from xmlTable ('1 to 3');
select * from items;

        ID NAME                            
---------- ----------
         1 item 1                          
         2 item 2                          
         3 item 3    

ID                               NAME                            
-------------------------------- ----------
849AD5F2914564AAE0530A01A8C08076 item 1                          
849AD5F2914664AAE0530A01A8C08076 item 2                          
849AD5F2914764AAE0530A01A8C08076 item 3


Ответ 3

Иногда интересно решение со строго возрастающей последовательностью, которая видна
и уникальна только для текущей сессии. Например, подготовка данных для вставки в целевые
таблицы, в которых используется другое решение для обеспечения уникальности. 

С переменными сессии это реализуется очень просто:      

create or replace package common as
    function nextSeq return number;
end;
/
create or replace package body common as
    seq_ number;
    function nextSeq return number is
    begin
        seq_ := seq_ + 1;
        return (seq_);
    end;
begin seq_ := 0;
end;
/


Конечно, использовать имеет смысл только во временных таблицах:

create global temporary table mytmptab (id number primary key, name varchar2 (32))
on commit preserve rows
;

insert into mytmptab 
    select common.nextSeq id, t.column_value.getClobVal () name
    from xmlTable ('"aaa", "bbb", "ccc"') t
    ;
select * from mytmptab;

        ID NAME                            
---------- --------------------------------
         1 aaa                             
         2 bbb                             
         3 ccc


Ответ 4

Вариант с сиквенсом самый лучший, потому что нативный.
Вариант с max() - медленный и не обеспечивает уникальности сгенерённого ключа.
Вариант с триггером - будут потери на переключение контекста между sql машиной и
pl/sql машиной. Кроме того триггер будет срабатывать при загрузке данных sql-loader-ом
или любой другой вставке с уже существующим идшником.


Ответ 5

-- подходит в случае маленьких транзакций 
-- (да это небезопасно! да это долго, да это еще 1 вариант )
select nvl (max(tt.id)+1, 0) from test tt;

-- тоже самое с использованием триггера
-- (да это небезопасно! да это долго, да это еще 1 вариант )   
create or replace trigger test_autoincrement 
before insert on test
for each row
begin
   select nvl (max(tt.id)+1, 0) into :new.id from test tt;
end;

-- триггер с использованием sequence (имхо наиболее оптимальный выбор)
create or replace trigger test_autoincrement 
before insert on test
for each row
begin
   select test_sq.nextval into :new.id from dual;
end;

UP1: исправил запросы


Ответ 6

MS SQL и другие базы используют очень удобный способ автоникремента. Чаще всего его
применяют для ключевых полей таблицы. Первоначально в Oracle не было автоинкремента,
но позднее в версии 12 (выпуск 2015 года), автоинкремент появился. 
Вот статья, котрая описывает оба подхода.

Я расскажу, как можно использовать sequence для генерации нового значения ключа.
Этот хороший способ для демонстрации того, что представляет собой структура sequnce,
и как создавать простые триггеры. 
При вставке нового значения (оператор insert) можно использовать построчный триггер
на таблице (before insert for each row). В примере ниже таблица называется demo, ключевое
поле demo.id. Тип этого поля можно выбрать произвольно (integer, varchar2 и т.д.).
Обычно выбирают тип integer. Поскольку поле является ключом, для него добавляют ограничение
уникальности и ограничение not null.

CREATE TABLE demo_table
(
id INTEGER NOT NULL,
[...]
CONSTRAINT demo_id_unique UNIQUE (id)
);

create sequence sq_demo_id start with 1;

create or replace trigger TRG_DEMO_INSERTS 
before insert on demo_table
for each row
begin
   select sq_demo_id.nextval into :new.id from dual;
end


Здесь sq_demo_id - счетчик, sequence c автоматическим увеличением на единицу при
каждом вызове. Если нужно будет узнать текущее значение счетчика, то используйте 
sq_demo_id.curval.
Создание тригера является однократным действием. Не нужно заботиться о конфликтах
с параллелными сеансами, эта проблема устраняется в триггере. 
Новый подход, создание счетчика через оператор identity ещё больше облегчает жизнь
программистам.

CREATE TABLE demo_table
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 
1),
[...] UNIQUE (id)
)


При его реализации похожие структуры создаются автоматически, самим ядром PL/SQL.
Они обладают меньшей гибкостью в настройках, но код становится легче, его проще поддерживать.


Ответ 7

В Oracle 12c и выше можно сделать что-то подобное:

--create table
CREATE TABLE TEST
(
   ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
   CONSTRAINT PK_TEST PRIMARY KEY(ID),
   CONSTRAINT UK_TEST UNIQUE(ID)
);


До Oracle 12с:

--create table
CREATE TABLE TEST
(
   ID INTEGER,
   CONSTRAINT PK_TEST PRIMARY KEY(ID),
   CONSTRAINT UK_TEST UNIQUE(ID)
);

--create sequence
CREATE SEQUENCE TEST_SEQ;

--create tigger using the sequence
CREATE OR REPLACE TRIGGER TEST_TRG 
  BEFORE INSERT ON TEST
  FOR EACH ROW
  WHEN (new.ID IS NULL)
  BEGIN
  SELECT TEST_SEQ.NEXTVAL
    INTO :new.ID
    FROM DUAL;
END;