Commit In 循环给出错误的输出？
我试图在表消息中插入除 6 和 8 之外的 1 到 10 个数字，但是当我从表 messages1 中获取它时，输出将按此顺序出现 4 5 7 9 10 1 2 3 应该是这样的 1 2 3 4 5 7 9 10 根据逻辑，当我省略提交或将其放在其他地方时，它工作正常， 请解释一下为什么会发生这种情况？ 这是我的代码。
BEGIN
FOR i IN 1..10
LOOP
IF i<>6 AND i<>8
THEN
INSERT INTO messages1
VALUES (i);
END IF;
commit;
END LOOP;
END;
从消息1中选择*；
I am trying to insert 1 to 10 numbers except 6and 8 in table messages,but when i fetch it from table mesages1, output is coming in this order
4
5
7
9
10
1
2
3
It should be like this
1
2
3
4
5
7
9
10
According to the logic ,it works fine when i omit commit or put it some where else,
Please explain why it is happening?
this is my code.
BEGIN
FOR i IN 1..10
LOOP
IF i<>6 AND i<>8
THEN
INSERT INTO messages1
VALUES (i);
END IF;
commit;
END LOOP;
END;
select * from messages1;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您不使用
ORDER BY，则应假定结果出现的顺序未定义。结果通常与插入的顺序相同，但这并不能保证。最重要的是，如果您希望结果按某种特定顺序排列，请使用
ORDER BY。If you don't use
ORDER BY, you should assume the order the results appear in is undefined. Often the results are in the same order they were inserted in, but it's not guaranteed.Bottom line, if you want your results in some specific order, use
ORDER BY.正如 Matti 所说，您需要明确使用 order by 子句来保证正确返回订单。
当您有待处理的更改（即未提交的更改）时，您是唯一能够看到它们的人（通常......），因为它们尚未添加到其他数据所在的数据存储中。 Oracle 维护一个单独的挂起更改列表，它使用该列表来更改从主数据存储中获取的结果。在您的示例中，此列表的更改恰好按顺序返回，因为示例中的数据很少，Oracle 可能不需要以任何方式拆分挂起的数据来优化其存储。
提交数据后，它将进入主数据库存储，并根据表和分区的设置方式以多种可能的方式进行排序。
简而言之，数据在提交之前和之后来自两个不同的地方，碰巧它们以不同的顺序返回，但不要依赖它们并不总是那样表现。
As Matti says you need the order by clause explicity to guarantee the ordering is returned correctly.
When you have pending changes (ie uncommitted ones) you are the only one able to see them (generally...) this because they haven't been added to the data store where the other data is. Oracle maintains a separate list of pending changes which it uses to alter the results it it gets from the main data store. In your example the changing from this list happens to be returning in order, as there is very little data in the example Oracle presumably isn't needing to split the pending data in any way for optimise its storage.
Once the data is committed it will go into the main database storage and be ordered in any number of possible ways depending on how the table and partition is set up.
So in short, the data is coming from two different places before and after the commit, it just so happens they are returning in different orderings, but don't rely on them not always behaving like that.