Random access to any row of data in the oracle database of rownum sample introduction

When looking at oracle data recently, I understand the concept of rownum. Before, I only knew to simply add, delete and change the database table;

After seeing the concept of rownum, it suddenly occurred to me that many business scenarios should be applicable, such as when awarding prizes randomly.

We can first look up the total number of prizes that can be awarded in the prize table, then generate a random integer X within the total number of prizes through the Random class of java, and then call

select * from (select rownum no,id from Table where rownum < =X ) where no > =X

Get the prize, so that the obtained value, when the concurrency is 1, the probability of getting the same data is relatively small. In order to support high concurrency, you can consider adding an optimistic lock to the prize table.

If there is an optimistic lock when the same prize is operated at the same time, after the prize has been issued, another process will throw an exception org. hibernate. StaleObjectStateException when issuing the prize again, and then we can capture the exception and then give this user a prize table again to get the prize!

Briefly introduce the use of rownum under 1! Many articles have been introduced!

For rownum, it is the number that the oracle system assigns sequentially to the rows returned from the query, the first row returned is assigned 1, the second row is assigned 2, and so on. This pseudo field can be used to limit the total number of rows returned by the query, and rownum cannot be prefixed with the name of any table.

1. rownum for query criteria equal to a value

select rownum, id, name from student where rownum=1; This is the first line of data can be found, but when rownum is not 1, is unable to query out the data; Such as

select rownum, id, name from student where rownum = 2; This makes it impossible to query the data;

2. rownum for query criteria greater than a certain value

select * from(select rownum no ,id,name from student) where no > 2; When querying rownum is greater than a certain value, the sub-query method must be used, and the alias name must be used for rownum in the internal select. Otherwise, the external query condition is that rownum cannot be located normally. rownum refers to the internal rownum

3. rownum for query criteria less than a certain value

select rownum,id,name from student where rownum < 3; Query the first two rows of data, which can be queried directly because rownum < At 3, rownum increases from 1, which is consistent with the generation law of rownum

select rownum,id,name from student where rownum < = 2; This is also ok

4. rownum and sorting

select rownum, id, name from (select * from student order by name); First of all, we should understand the concept of rownum. rownum is to assign a number to each row of data incrementally after the data is queried. If you use

select rownum, id, name from student order by name; The generated sequence number is the following; Because order by is to query first, after the query is sorted, that is, before sorting, rownum already has a value!

3 200 003 Lee 3
2 200 002 Wang 2
1 200 001 Sheet 1
4 200 004 Zhao 4

5. How to get a row of data

select * from (select rownum r,a from yourtable where rownum < = 5 order by name )where r > 4; In this way, the fifth row of data can be obtained, and it is the fifth row of data sorted by name