среда, 30 марта 2011 г.

AUTO_INCREMENT поле в Oracle

Знакомые с MySQL программисты при первом знакомстве с СУРБД Oracle, вероятно, удивятся отсутствию в ней такой приятной возможности, как создание auto_increment полей, то есть полей, численное значение которых автоматически увеличивается при создании новых записей. Как правило, auto_increment поля используются как primary key - первичные ключи для таблиц.

В MySQL эти поля создаются буквально на лету:

CREATE TABLE users(
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL, PRIMARY KEY (id)
);

В такую таблицу можно вставлять записи, не задумываясь об автоматической генерации первичного ключа id, который будет увеличиваться на 1 для каждой новой записи. Иными словами, можно выполнить запрос вида

INSERT INTO userns(name) VALUES ("sterx"),("alex"),("kime");

и для каждой из созданных записей в случае MySQL будет автоматически вычислен id.

В Oracle auto_increment поля создаются несколько иначе, используются так называемые последовательности (sequence), или, другими словами, счетчики. Создадим последовательность:

CREATE SEQUENCE users_seq
START WITH 1 
INCREMENT BY 1 
NOMAXVALUE;

Как видно из объявления, последовательность начинается с 1 (конечно, при необходимости, можно задать другое значение), шаг последовательности также равен 1, последовательность не имеет максимального значения.

Теперь при создании записей в таблице нам необходимо присваивать полю id текущее значение счетчика-последовательности (или следующее значение счетчика в зависимости от реализации). Это можно сделать двумя способами - создать триггер, отслеживающий создание записей в таблице и присваивающий полю id значение счетчика, либо в самой команде INSERT использовать значение счетчика для задания значения id. Использование триггера с одной стороны усложняет реализацию, с другой стороны упрощает команды INSERT, поэтому выбор способа установки id оставлю на усмотрение читателя.

Рассмотрим оба варианта.

Использование триггера


create or replace trigger users_id_trg
before insert on users
for each row
begin
  if :new.id is null then
    select users_seq.nextval into :new.id from dual;
  end if;
end;

В теле триггера мы проверяем, что id новой записи не установлен пользователем, так как по своим причинам пользователь может захотеть установить значение id самостоятельно, не полагаясь на триггер. Если это не так - вычисляется новое значение счетчика и id устанавливается автоматически.

Использование хитрого INSERT


INSERT INTO users (id, name) VALUES (users_seq.nextval, 'sterx');

В данном случае новое значение счетчика вычисляется при каждом вызове INSERT, значение поля id вычисляется соответственно в самом теле команды без триггера.

PS. Созданные последовательности и триггеры легко удалить, поэтому не бойтесь с ними работать.

DROP SEQUENCE users_seq;
DROP TRIGGER users_id_trg;

12 комментариев:

  1. Здравствуйте!
    Во-первых спасибо за простой и понятный пост!

    единственное что не совсем понятно что такое dual в теле триггера

    ОтветитьУдалить
  2. Здравствуйте!
    Спасибо за комментарий.

    dual - это служебная таблица Oracle Database, она состоит из одного поля dummy и содержит одну запись со значением X. Это ее свойство, как правило, используется для возврата значений псевдо-колонок (констант, sysdate и т.п.):

    select 'Вася' from dual;
    select 2+3 as s from dual;
    select sysdate from dual;

    Вместо dual можно использовать любую другую таблицу с одной записью, однако, для таких целей принято использовать именно dual.

    Вообще говоря, dual - это обычная таблица, которой владеет пользователь SYS, соответственно, она может быть изменена - чего по понятным причинам крайне не рекомендуется делать. Это просто эталонная таблица с одним полем и одной записью.

    ОтветитьУдалить
  3. Здравствуйте.
    Согласен с Dzmitry, простая статья, все написано понятным языком, однако в моем варианте триггера auto_increment не используются последовательность. Его реализацию я описал в своем блоге.
    http://k-maxim.ru/bazy-dannyx/auto_increment-v-subd-oracle-database.html

    ОтветитьУдалить
  4. Apache47,

    Думаю методика Голобурдина реализована лучше, т.к в твоем варианте приходится проводить операции по сравнению. А тут просто переменная.

    ОтветитьУдалить
  5. Всё правильно, можно и так и так, НО:
    Если у вас в таблицу заносится много записей, многими юзерами, с бесчисленных коннектов, то сиквенс - единственная возможность сохранить уникальность без использования блокировок и всякой лабуды.
    А если это простая справочная таблица с занесением записей админом от случая к случаю, то используйте не просто макс+1, а каунт+1 (желательно с проверкой значения ид на уникальность - тогда точно последовательность ид сохранится идеально). Но это вариант для мазохистов...

    ОтветитьУдалить
  6. не работают ваши скрипты.

    CREATE OR REPLACE
    TRIGGER TRIGGER1
    BEFORE INSERT ON NEWS
    for each row
    BEGIN
    if :new.id is null then
    select sequence1.nextval into :new.id from dual;
    end if;

    --NULL;
    END;


    Error: ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

    ОтветитьУдалить
  7. И что такое вобще это new.id?

    ОтветитьУдалить
  8. похоже тригер не создался только потому, что в именах полей таблицы были зарезервированыне слова типа DATE.

    ОтветитьУдалить
  9. Спасибо. Кратко. Ясно. Очень помогли!

    ОтветитьУдалить
  10. Анонимный7 мая 2016 г., 11:42

    в 12с уже есть автоинкремент

    ОтветитьУдалить
  11. Спасибо! Взял за основу прием с триггером. Но я обошелся без создания последовательности (sequence). Я просто беру МАХ значение id и прибавляю к нему +1.

    create or replace trigger users_id_trg
    before insert on users
    for each row
    begin
    if :new.id is null then
    select MAX(id)+1 into :new.id from users;
    end if;
    end;

    ОтветитьУдалить