Randomly fetch any row of data in the Oracle database (rownum)

Recently read Oracle data, understand the concept of rownum, previously only known to the database table simple additions and deletions;

After seeing the concept of rownum, I suddenly thought that a lot of business scenarios should be applicable, for example, in the random awards,

We can binate check out the total number of prizes that can be issued in the prizes table, and then generate a random integer x in the total number of prizes by using the Random class in Java, and then call

SELECT * FROM (select RowNum no,id from Table where rownum<=x) where no >=x

Get out of the prize, so that the value obtained, in a certain amount of concurrency, the probability of taking the same piece of data is relatively small, in order to support high concurrency situation, you can consider adding an optimistic lock for the prize table,

If it happens that the same prize is in operation at the same time, there is an optimistic lock, after the prize has been issued, Another process to send this award will throw an exception org.hibernate.StaleObjectStateException, then we can catch the exception, and then re-give the user to the prize table to get prizes!

General introduction of the use of rownum it! Many articles are introduced!

For rownum It is the number of the Oracle system order assigned to the row returned from the query , the first row returned is assigned 1, the second row is 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 pieces equal to a value

Select Rownum,id,name from student where rownum=1; this is the first row of data can be detected, but when the rownum is not 1 o'clock, it is unable to query out the data;

Select Rownum,id,name from student where rownum = 2; This will not be able to query the data;

2.rownum for query bars larger than a value

SELECT * FROM (select RowNum No, id,name from student) where no>2; query rownum is greater than a value, you must use a subquery to And the rownum in the internal select must use aliases, otherwise the external query condition is not properly positioned rownum refers to the internal rownum

3.rownum for query bars less than a certain value

Select Rownum,id,name from student where rownum <3; queries the first 2 rows of data, so it can be queried directly, because Rownum<3, RowNum is incremented from 1, Conforms to the rownum rule of formation

Select Rownum,id,name from student where RowNum <=2; This is also possible

4.rownum and sort

Select RowNum, Id,name from (SELECT * from Student order by name), first to understand the concept of rownum, RowNum is after the data query, the system increments each row of data assigned a number;

Select RowNum, id,name from student order by name, the resulting sequence number is the following, because order by is the first query, after the query out in order, that is, before the sorting, rownum already have values!

3 200003 Lie triple
2 200002 Wang ER
1 200001 Zhang One
4 200004 Zhao Si

5. Ways to get data for a row

SELECT * FROM (select RowNum r,a from yourtable where rownum <= 5 order by name) where R >4; so you can get to the 5th row of data, sorted by name The 5th row of data