当前位置: 动力学知识库 > 问答 > 编程问答 >

oracle - Commit In loop gives wrong output?

问题描述:

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;

网友答案:

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.

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: