Random acquisition of any row of data (rownum) in the oracle database, using lerownum

Random acquisition of any row of data (rownum) in the oracle database, using lerownum

Recently, when reading oracle data, I learned the concept of rownum. I used to only know how to add, delete, modify, and query database tables;

After seeing the concept of rownum, I suddenly thought that many business scenarios should be applicable. For example, during random awards,

We can first check the total number of prizes that can be issued in the prize table, then generate a Random integer X in the total number of prizes through the java Random class, and then call

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

Obtain the prize, so that the obtained value is less likely to get the same data when a certain amount of concurrency occurs. 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. Then, we can capture the exception and send the user a new one to the prize table to obtain the prize!

 

Let's give a general introduction to the use of rownum! Many articles are introduced!

For rownum, it is assigned fromQueryNumber of the row to be returned. The first row to be returned is allocated 1 and the second row is 2. Therefore, this pseudo field can be used to limitQueryThe total number of returned rows, and rownum cannot be prefixed with any table name.

1.RownumQueryEntriesParts

Select rownum, id, name from student where rownum = 1; this can be used to find the first row of data, but when rownum is not 1, data cannot be queried; for example

Select rownum, id, name from student where rownum = 2; data cannot be queried;

2. rownumQueryEntriesParts

Select * from (select rownum no, id, name from student) where no> 2; when querying a rownum value greater than a certain value, you must use the subquery method, the rownum in the internal select statement must use an alias. Otherwise, the external query condition cannot be properly located. rownum refers to the internal rownum.

3. rownumQueryEntriesParts

Select rownum, id, name from student where rownum <3; query the data of the first two rows, which can be directly queried, because when rownum <3, rownum increments from 1 to conform to the rownum generation rule.

Select rownum, id, name from student where rownum <= 2; this is also possible

4. rownum and sorting

Select rownum, id, name from (select * from student order by name); first, you need to understand the concept of rownum. After the data is queried, the system incrementally assigns a number to each row of data.

Select rownum, id, name from student order by name; then the generated sequence number is in the following circumstances; Because order by is first queried, and then sorted, that is, rownum has a value before sorting!

 
3 200003 Li San
2 200002 Wang 'er
1 200001 Zhang Yi
4 200004 Zhao Si

5. How to obtain a row of data

Select * from (select rownum r, a from yourtable where rownum <= 5 order by name) where r> 4; in this way, 5th rows of data can be obtained, 5th rows of data sorted by name

In the oracle database, how does one randomly query four data records based on the probability?

Select * from (select * from MEMBER order by dbms_random.value) where rownum <= 4

From: wenku.baidu.com/...6.html

How to quickly query the first row of data in an oracle database

Select * from table where rownum = 1