带有序列和触发器的 oracle 自动增量无法正常工作
这是我的问题我有这段代码可以在oracle数据库中创建一个自动增量变量:
here is my problemI have this code to make an autoincrement variable in oracle database:
CREATE TABLE Korisnici
(
id_korisnika number PRIMARY KEY,
ime_korisnika varchar2(200),
prezime_korisnika varchar2(200),
broj_telefona varchar2(30),
adresa_korisnika varchar2(400)
)
/
create sequence test_seq
start with 1
increment by 1;
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON Korisnici FOR EACH ROW
BEGIN
SELECT test_seq.NEXTVAL
INTO :NEW.id_korisnika
FROM DUAL;
END;
/
如果我从头开始一切都很好,我的数字为 1,2,3,4....我关闭程序,再次打开,oracle 数据库连接再次启动.我再添加一个输入,我有像 20、21、22、23 这样的数字......我把程序放在我的 android 上并从不同的设备连接,当我输入一个用户时,我有 30,31,33,34...
If I start from begining everything works great, I have numbers as 1,2,3,4.... I close the program, open it again, so oracle database connection is once again started. I add one more input and I have numbers like 20,21,22,23... I put program on my android and connect from different device, when I input one user I have 30,31,33,34...
为什么会这样?以及如何修复它?
Why is this happening? And how to fix it?
谢谢
这是我从数据库读取数据的过程
Here is my proc for reading data from database
CREATE OR REPLACE PROCEDURE Citanje_korisnika( p_rc OUT SYS_REFCURSOR )
AS
BEGIN
OPEN p_rc
FOR SELECT *
FROM Korisnici;
END;
我是 oracle 数据库的新手.
I am a bit of newbie in oracle database.
推荐答案
使用 NOCACHE 指定 SEQUENCE 将停止一次缓存 20 个数字的会话并提供帮助.
Specifying the SEQUENCE with NOCACHE will stop a session caching 20 numbers at a time and help.
create sequence test_seq
start with 1
increment by 1
NOCACHE;
但是,如果您希望获得一个完全连续的序列,这很难实现 - 如果(例如)回滚插入,则从序列中获取的数字将丢失".
However, if you're hoping for a completely contiguous sequence this is very difficult to achieve - numbers taken from the sequence are "lost" if (for example) an insert is rolled back.
根据您的评论,我想知道您是否忘记了 COMMIT?
Based on your comment, I wonder if you're forgetting to COMMIT?
相关文章