Random access to any row of data in an Oracle database (rownum) sample Introduction _oracle

Looking at Oracle data recently, understand the concept of rownum, previously only know the database table for simple additions and deletions to check;

After seeing the concept of rownum, it suddenly occurred to me that many business scenarios should be applicable, for example, in the case of random awards,

We can from the total number of prizes in the table, then generate a random integer x in the total prize number through the Java random class, and then call

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

Get the prize, so that the value, in 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 for the prize table to add an optimistic lock,

If the same prize is actually in operation at the same time, with the 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 to the user to the table to get the prizes!

Roughly introduce the use of rownum! A lot of articles are introduced!

For RowNum, it is an Oracle system sequence that is assigned the number of rows returned from the query, the first row is assigned 1, the second row is 2, and so on, this pseudo field can be used to limit the 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 value

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

Select Rownum,id,name from student where rownum = 2; This cannot query the data;

2.rownum for query conditions that are greater 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 an alias, otherwise the external query condition is unable to locate rownum refers to the internal rownum

3.rownum query criteria for less than a value

Select Rownum,id,name from student where rownum <3; query the first 2 rows of data, which can be directly queried, because when rownum<3, the rownum is incremented from 1, Conform to the generation rule of rownum

Select Rownum,id,name from student where rownum <=2; this is OK

4.rownum and Sorting

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

Select RowNum, id,name from student ordered by name; The generated ordinal number is the following: Because the order by is the first query, after the query is sorted, that is, before the order, RowNum already have a value!

3 200003 Lie triple systems
2 200002 King Two
1 200001 Sheets A
4 200004 Zhao Si

5. How to obtain a row of data

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