英文:
Use prepare statement for Oracle Db, get unique constraint violated
问题
我们尝试使用Java的PreparedStatement进行插入操作，但是遇到了ORA-00001：违反唯一约束（POSTIONS_TABLE_PK）的错误。
该应用程序针对Oracle数据库，表名为info：
postion_table 表的列：
position_id 数字 主键,
amount 数字,
account_id 数字
我们需要使 position_id 成为连续的数字，从1开始，依次为 1、2、3 .... n。例如，当我们删除 position_id 为10的记录时，下一个新插入的记录需要使用10作为 position_id。
准备好的语句插入查询如下：
BEGIN
DECLARE
position_id 数字;
BEGIN
SELECT NVL(MAX(t.position_id) + 1, 1)
INTO position_id
FROM positions_table t
WHERE account_id = 1;
INSERT INTO positions_table(position_id, amount, account_id)
VALUES(position_id, ?, ?);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20999, '错误: ' || sqlerrm);
ROLLBACK;
END;
END;
当我们尝试批量插入多条记录时，出现了唯一约束（TPOSTIONS_TABLE_PK）冲突的错误。
对于如何构建准备好的SQL语句，有什么建议吗？
英文:
We try to use java preparedstatement to do insert action, we got ORA-00001: unique constraint (POSTIONS_TABLE_PK) violated error.
The app target at Oracle DB. the table name info:
postion_table columns:
position_id number pk,
amount number,
account_id number
We need postion_id to be sequential number. start from 1. so will be 1, 2 ,3 .... n. For example, when we delete record with position_id 10, then the next new insert need to use 10 as position_id.
The insert query for prepared statement looks like:
BEGIN
DECLARE
position_id number;
BEGIN
SELECT NVL(MAX(t.position_id) + 1, 1)
INTO position_id
FROM positions_table t
WHERE account_id = 1;
INSERT into positions_table(position_id,amount, account_id)
values(position_id, ?, ?);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20999, 'Error : ' || sqlerrm);
ROLLBACK;
END;
END;
We got unique constraint (TPOSTIONS_TABLE_PK) violated error when we try to insert
multiple records in a batch.
Any suggestion on how to build the prepared SQL.
答案1
得分: 1
使用
SELECT NVL(MAX(t.position_id) + 1, 1)
在任何有多个用户的系统中都不起作用。因为两个用户都会运行该命令，他们都会发现POSITION_ID为（假设）17，然后他们都会尝试使用18，结果其中一个会失败。
使用序列（可能会产生间隔），但是当您希望获得一个没有间隔数字的行列表时，在查询时执行，即
select row_number() over ( partition by ... order by position_id )
如果您真的非常需要在插入时生成无间隔序列，那么一个相对可扩展的方法是：
1）创建一个包含所有数字的表（例如，从1到1,000,000），并对该列创建索引。
2）每个想要序列的人都会执行
open cursor for select ... from that_table for update skip locked;
然后从中获取一行。
随着时间的推移，每个数字都将被使用。但对于通常并不是真正业务需求的事情来说，这需要很多工作和复杂性。
英文:
Using
SELECT NVL(MAX(t.position_id) + 1, 1)
will never work in any system where you have more than a single user. Because two users will both run the command, they will both find POSITION_ID to be (say) 17, and they will both then try use 18, and one will fail.
Use a sequence (which might give you gaps) but then when you wish to get a list of rows with a gap-free number, do it at query time, ie
select row_number() over ( partition by ... order by position_id )
If you really really really need a gap free sequence generated at INSERT time, then a reasonably scalable method is:
-
create table with all the numbers (eg, 1 through 1,000,000) and index that column.
-
each person wanting a sequence does
open cursor for select ... from that_table for update skip locked;
and fetches a row from it.
Over time, every number will be used. But its a lot of work and complexity for something that is often not really a business requirement.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论