Oracle получить id вставленной записи
Обновлено: 07.07.2024
У меня есть несколько необработанных SQL-запросов для SQL Server, которые используют SCOPE_IDENTITY для получения сгенерированного идентификатора для конкретной вставки сразу после того, как эта вставка происходит все в одном исполнении.
каков наилучший способ сделать это для базы данных Oracle?
можно ли это сделать на Oracle через стандартный SQL или мне нужно переключить это, чтобы использовать хранимую процедуру и поместить что-то подобное в тело сохраненного proc?
Если это должен быть сохраненный proc, то каков де-факто стандартный способ получения последнего сгенерированного порядкового номера, заботясь о том, чтобы учесть, что, вероятно, будут перекрывающиеся выполнения на нескольких потоках, поэтому этот механизм должен будет получить правильный сгенерированный ID и не обязательно абсолютный последний сгенерированный ID.
Если два выполняются одновременно, каждый должен возвращать правильный сгенерированный идентификатор из каждого соответствующего вызова. Обратите внимание, что я не использую SQL Server "@@IDENTITY " из-за этого многопоточного характера вызовов.
Я бы предпочел сохранить его как raw SQL, если это возможно, так как мне намного проще управлять на разных платформах (один файл, содержащий SQL-блок каждой платформы, разделенный тегами идентификации СУБД). Хранимые процессы-это немного больше работы для меня, но я могу пойти этим путем, если это единственный возможный способ.
немного расширяя ответы от @Guru и @Ronnis, вы можете скрыть последовательность и сделать ее более похожей на автоматическое приращение с помощью триггера и иметь процедуру, которая делает вставку для вас и возвращает сгенерированный идентификатор в качестве параметра out.
затем вы можете вызвать процедуру вместо простой вставки, например, из anoymous блок:
вы можете сделать вызов без явного анонимного блока, например из SQL * Plus:
. и используйте переменную bind :l_batchid чтобы впоследствии ссылаться на сгенерированное значение:
в Oracle нет функций автоматического увеличения для столбца. Вам нужно создать объект SEQUENCE. Вы можете использовать последовательность, как:
. чтобы вернуть следующий номер. Чтобы узнать последнюю созданную последовательность nr (в вашем сеансе), вы должны использовать:
этот сайт имеет несколько полных примеров использования последовательностей.
делать это как хранимая процедура имеет много преимуществ. Вы можете получить последовательность, вставленную в таблицу, используя синтаксис insert into table_name values returning .
или просто вернуть some_seq_val . В случае, если вы не используете последовательность и прибываете последовательность по некоторым расчетам, вы можете использовать returning into эффективно.
вы можете использовать приведенную ниже инструкцию, чтобы получить вставленный идентификатор переменной.
теперь вы можете получить значение, используя оператор
вы можете сделать это с помощью одного оператора-предполагая, что вы вызываете его из JDBC-подобного соединителя с функциональностью параметров in/out:
Я вставить некоторые значения в таблицу. Есть столбец, значение которого генерируется автоматически. В следующей инструкции моего кода я хочу получить это значение.
можете ли вы сказать мне, как это сделать правильно?
@@IDENTITY не является безопасной областью и вернет вам идентификатор из другой таблицы если у вас есть триггер вставки в исходной таблице, всегда используйте SCOPE_IDENTITY()
Это, как я сделать мой хранимых процедур для MSSQL с ID автоматически.
Если вы используете PHP и MySQL, вы можете использовать функцию mysql_insert_id (), которая сообщит вам идентификатор элемента, который вы только что установили.
Но без вашего языка и СУБД я просто снимаю в темноте здесь.
Это очень хорошо работает в SQL 2005:
Он имеет преимущество возврата всех идентификаторов, если ваш оператор INSERT вставляет несколько строк.
опять нет языкового агностического ответа, но в Java он выглядит так:
Если вы работаете с Oracle:
вставка в таблицу (поля. ) значения (значений. ) Возврат (список полей. ) в переменные. )
вставить в PERSON (NAME) значения ('JACK') возвращая ID_PERSON в vIdPerson
или если вы звоните. Java с CallableStatement (sry, это мое поле)
ВСТАВИТЬ В PERSON (NAME) ЗНАЧЕНИЯ ('JACK') ВОЗВРАТ ID_PERSON INTO ?
и объявление параметра autput для оператора
нет стандартного способа сделать это (так же, как нет стандартного способа создать автоматически увеличивающиеся идентификаторы). Вот два способа сделать это в PostgreSQL. Предположим, что это ваша таблица:
вы можете сделать это в двух операторах, пока они являются последовательными операторами в одном и том же соединении (это будет безопасно в PHP с пулом соединений, потому что PHP не возвращает соединение обратно в пул, пока ваш скрипт сделано):
lastval() дает вам последнее автоматически сгенерированное значение последовательности, используемое в текущем соединении.
другой способ-использовать PostgreSQL возвращение положения о вставить о себе:
эта форма возвращает результирующий набор, как выберите заявление, а также удобно для возврата любого вида расчетного значения по умолчанию.
важно отметить, что использование запросов поставщика SQL для получения последнего вставленного идентификатора безопасно использовать, не опасаясь параллельных подключений.
Я всегда думал, что вам нужно создать транзакцию, чтобы вставить строку, а затем выбрать последний вставленный идентификатор, чтобы избежать извлечения идентификатора, вставленного другим клиентом.
но эти запросы конкретного поставщика всегда извлекают последний вставленный идентификатор для текущего подключения к база данных. Это означает, что последний вставленный идентификатор не может быть затронут другими вставками клиента, пока они используют свое собственное соединение с базой данных.
из сайта я узнал следующие вещи:
SQL-сервер – функция @@Identity против функция scope_identity() против функция ident_current – получить последней вставленной личность запись 25 марта 2007 года по pinaldave
ВЫБЕРИТЕ @ @ IDENTITY Он возвращает последнее значение идентификатора, полученное в соединении, независимо от таблицы, которая произвела значение, и независимо от области действия оператора, который произвел значение. @Функция @ @ Identity возвращает последнее значение идентификатора, введенного в таблицу в текущем сеансе. Хотя @ @ IDENTITY ограничен текущим сеансом, он не ограничен текущей областью действия. Если у вас есть триггер в таблице, который вызывает создание идентификатора в другой таблице, вы получите идентификатор, который был создан последним, даже если это был триггер, который его создал.
ВЫБЕРИТЕ ФУНКЦИИ SCOPE_IDENTITY() Он возвращает последнее значение идентификатора, созданное в соединении и оператором в той же области, независимо от таблицы, которая произвела значение. SCOPE_IDENTITY (), как и @@IDENTITY, вернет последнее значение identity, созданное в текущем сеансе, но также ограничит его вашей текущей областью. Другими словами, он возвращает последнее значение идентификатора, которое вы явно создали, а не любое удостоверение, созданное триггером или пользовательской функцией.
выберите IDENT_CURRENT ('tablename’) Он возвращает последнее значение идентификатора, полученное в таблице, независимо от соединения, которое создало значение, и независимо от области действия оператора, который произвел значение. IDENT_CURRENT не ограничивается областью действия и сеансом; он ограничен указанной таблицей. Функция ident_current возвращает значение идентификатора, созданное для указанной таблицы в любом сеансе и области.
Здравствуйте, Igor Trofimov, Вы писали:
iT>Минус варианта с отдельным запросом curval в том, что это лишний вызов на сервер, лишний разбор.
Имхо основной минус все-таки в том, что отсутствует гарантия возврата правильного id. Returning возвращает значение поля, вне зависимости от того, какое значение и как туда попало. Что же до currval. Допустим, я делаю триггер:
И вышеупомянутый код с currval время от времени идет лесом.
Здравствуйте, C_Angel, Вы писали:
C_A>Здравствуйте, Horror_Infinity, Вы писали:
H_I>>Совсем бредовый вариант. Непосредственно после INSERT
H_I>>
H_I>>
C_A>Все варианты в ветке в принципе реализуемы, НО объясните одно "НО":
C_A>Что будет если между insert и select пройдет ещё один insert и select следовательно даст неверный вариант, или я ощибаюсь?
.currval будет возвращать последнее значение сгенерированное предыдущим вызовом nextval в пределах сессии.
если при этом последовательность будут "дергать" в других сессиях, то следующий вызов nextval просто даст число на N больше
чем .currval
Здравствуйте, Пацак, Вы писали:
П>Здравствуйте, C_Angel, Вы писали:
C_A>>Что будет если между insert и select пройдет ещё один insert и select следовательно даст неверный вариант, или я ощибаюсь?
C_A>>Может как то в транзакции это все?
П>По идее генерация новых значений для первичного ключа должна работать вне контекста транзакции (я сильно удивлюсь, если sequence в оракле работают иначе), так что в одной транзакции выполнялись insert'ы или в разных — должно быть безразлично, значение вернется неверное и в том и в другом случае. Выход из положения — см. выше: заранее (непосредственно перед вставкой) получать gen_id_sequence.nextval на клиента и после этого вставлять его в insert как и остальные поля.
curval использовать можно, но осторожно (curval является аналогом пакетной переменной)
Все будет возвращаться верно при условии что в триггере
последовательность выбирается ровно 1 раз и именно это значение помещается в PK
p.s.
Если не вызвать nextval то на curval получите:
ORA-08002: sequence XXX is not yet defined in this session
Здравствуйте, Пацак, Вы писали:
П>Разве что чисто в познавательных целях: не работаю я с ораклом. Но вообще механизм довольно удобный. Интересно, а есть ли что-то подобное в стандарте?
Насколько я помню, не заглядывая в текст — есть в SQL2003. Но к сожалению, не сделано последнего и самого правильного шага: не предусмотрено возможности создания автоинкрементного поля, запитанного из именованной последовательности.
C_A>Народ, еще варианты?
А чем предложенный не нравится?
Вариантт — обернуть INSERT в процедуру, возвращающую значение ID.
Или генерить ID на клиенте.
[sql]INSERT INTO WARE(NAME, MAKERID, UNITID, TYPEID, DETAIL) VALUES [].
После хочется узнать с каким ID создалось. ID — автоинкрементное.
PS: Пишется на Java.
Здравствуйте, C_Angel, Вы писали:
Во-первых, в INSERT добавить конструкцию RETURNING. Подробности — в документации по Oracle.
Во-вторых, в случае Java придется чуть извернуться. Если не ошибаюсь, JDBC (либо jdbc-драйвер Oracle) непосредственно RETURNING не поддерживает (не поддерживал, когда я писал соответствующий код). Поэтому нужно выполнять анонимный блок, то есть
Этот код оборачивается в CallableStatement, делается registerOutParameter, ну и соответственно после выполнения можно прочитать id.
В программе данные отображаются через DBGrid. Связь с таблицей , связь через ADOQuery.
Yes metot: dinamiciski svoystva Ado Update Resync
begin
if not ADODataSet1.Active then ADODataSet1.Open;
ADODataSet1.Properties["Update Resync"].Value:= adResyncInserts+adResyncInserts;
begin
if not ADODataSet1.Active then ADODataSet1.Open;
ADODataSet1.Properties["Update Resync"].Value:= adResyncAutoIncrement+adResyncInserts;
> Удобство разработки
Под какую платформу?
Вот мне надо под WinCE, например. Круг подозреваемых резко сокращается, не правда ли?
Под винду? Ну вот сейчас по гневу богов пишу на оракле. После MSSQL, со всеми его интеграциями в студию и прочими плюшками, оракл сосёт. Я таких наркоманских тулзов ещё не видел.
Скорость
По виденным и экслуатировавшимся приложениям -- MSSQL.
Тут канает отмаза про "настоящих шотландцев", но это возвращение к первому пункту.
Но к пункту 3 это не относится. MS SQL на больших объемах (от миллиарда записей) начинает задыхаться. Это признавали сами разработчики от MS. Посему у нас региональные налоговые живут на мс скл, а центральная общая БД пишется на оракле.
почему надо выбирать ее (дороже) - хм. оракл вроде как дешевле. 700 баксов за энтерпрайз версию - не считаю что дорого. А если дорого - есть практически полнофункциональный XE.
> "показушник" почесал репу
Кривой показушник попался :)
Но, собственно, я не утверждал, что оракл лучше всех.
По производительности на малых объемах он сравним с мс скл. А на совсем малых - вполне возможно, что уступает и другим СУБД.
> очевидно. но тогда получается, что даже с кривыми руками
> проще написать под MSSQL (это про удобство).
А что такое PL по-твоему?
Ну так, простенько :
Небольшой сайтик, при клике на кнопке вызвается некий веб-сервис, возврашающий массив данных. Массив парсится, обрабатывается, результат сохраняется в БД и выводится в браузер юзеру.
Для реализации этого на оракле достаточно использовать только pl/sql.
Кстати, : используется в оракле как префикс параметров в клиентских и динамических запросах. Ну и в операторе :=.
> Вот, собсно, я и спрашиваю: является ли это критерием удобства?
собственно, у каждой СУБД есть свой язык хранимых процедур. У некоторых СУБД - больше, чем один.
Вопрос о критериях удобства - это не ко мне, я не вижу смысла меряться СУБД и их языками.
Следят за первыми двумя :)
> Ничего, что мне одному одновременно приходится поддерживать
> свое приложение на трех платформах ? ( ORACLE, MSSQL, Firebird
> )
> А ну, да - еще демонстрашка на DBISAM :)
> Всё то же самое есть и в Postgres
А мужики не знали
> но как бе. не было в новейшей истории случаев наездов с их стороны.
> Оракл официально бесплатен для некоммерческого использования.
$200 лицензия на одного пользователя (standard)
Игорь, не надо считать мои задачи и сравнивать со своими.
Деньги - тем более.
Фигня. Сказали же:
У тебя "интересная манера" строить и приводить умозаключения.
Не напрягает ?
> Игорь, не надо считать мои задачи и сравнивать со своими.
>
> Деньги - тем более.
не надо приводить стоимость в качестве аргумента к чему-либо.
> Oracle вполне себе известен, как дорогое решение для сколь-
> нибудь объемно-значимых бизнес-задач.
> Есть и другие, значительно менее затратные решения
Во. Нашел. Оракл халявый для разработки (если я правильно перевел) :
> only for the purpose of developing, testing, prototyping
> and demonstrating your application
Вот ссылка на лицензию Express
We grant you a nonexclusive, nontransferable limited license to use the programs for:
(a) purposes of developing, prototyping and running your applications for your own internal data processing operations;
(b) you may also distribute the programs with your applications;
(c) you may use the programs to provide third party demonstrations and training; and
d) you may copy and distribute the programs to your licensees provided that each such licensee agrees to the terms of this Agreement
Читайте также: