Shulou(Shulou.com)05/31 Report--

Editor to share with you how to randomly get any row of data in the oracle database. I hope you will get something after reading this article. Let's discuss it together.

For rownum, it is the number that the oracle system assigns sequentially to the rows returned from the query, the first row being 1, the second row 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

Recently, when I looked at oracle materials, I understood the concept of rownum. In the past, I only knew how to simply add, delete, modify and query database tables.

After seeing the concept of rownum, it suddenly occurred to me that many business scenarios should be applicable, such as when giving awards at random.

We can first check the total number of prizes that can be distributed in the prize table, and 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

Get the prize, so the value obtained in this way, when there is a certain amount of concurrency, the probability of getting the same data is relatively small. In order to support the situation of high concurrency, you can consider adding an optimistic lock to the prize table.

If it really happens that the same prize is operated at the same time, there is an optimistic lock, after the prize has been distributed, another process will throw an abnormal org.hibernate.StaleObjectStateException, and then we can catch the exception and re-send the user to the prize list to get the prize!

Give a general introduction to the use of rownum! Many articles have been introduced!

For rownum, it is the number that the oracle system assigns sequentially to the rows returned from the query, with the first row assigned 1, the second row 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 conditions equal to a certain value

The first row of data can be found by select rownum,id,name from student where rownum=1;, but it cannot be queried when rownum is not 1; for example

Select rownum,id,name from student where rownum = 2; this cannot query the data.

2.rownum for query conditions greater than a certain value

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

3.rownum for query conditions less than a certain value

Select rownum,id,name from student where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.