Oracle learning Notes database for random n records

Oracle Study Notes database to obtain random n records recently in the company's Internet of vehicles service system, met a demand, need to obtain random n records from the database. I have referred to the solution provided in SQL cookbook. I will take the following notes and share them with you. The www.2cto.com Solution uses built-in functions supported by DBMS to generate random values. Use this function in the order by clause to randomly sort records, and then use ROWNUM to limit the number of returned rows (in a random ORDER. Step 1. query the rows that meet the conditions: 1 select ename, job, sal from emp where sal> 2000. The query result is as follows: 2. use the built-in functions supported by ORACLE to sort records. 1 select ename, job, sal from emp where sal> 2000 order by dbms_random.value:

(Records generated each time are sorted differently) 3. limit the number of returned rows 1 select * from 2 (3 select ename, job, sal from emp where sal> 2000 order by dbms_random.value4) t 5 where rownum <= 5; the query result is as follows:

(Different records are generated and sorted each time) extended knowledge 1. about ROWNUM, many databases provide some statements, such as fetch first and LIMIT, to allow users to specify the number of rows returned from the query. Oracle has different practices, you must use the ROWNUM function to obtain the row number of each row (starting from 1 to Increase the number ). When ROWNUM <= 5 is used to return the first five rows, the following operation is performed: Oracle executes the query to obtain 1st rows that meet the conditions, which is called 1st rows. Are there five rows? If no row exists, Oracle returns another row because it must meet the condition that the row number is smaller than or equal to 5. If the row number reaches 5, Oracle no longer returns the row. Oracle obtains the next row and increments the row number (from 2 to 3 to 4 ). Return to step 2. We can see that the ROWNUM value of Oracle is assigned after each row is obtained, which is very important and a key point. For example, many Oracle developers want to return 5th rows by specifying ROWNUM = 5. This is an error. The following describes what will happen when ROWNUM = 5 is used: Oracle executes the query. Oracle obtains the first qualified row, which is called row 1st. Are there five rows? If not, Oracle discards these rows because they do not meet the conditions. If there are five rows, Oracle returns this row. However, the answer is that there will never be "five rows. Oracle obtains the next row, which is the first row. The reason is that the rows returned from the query must be 1. Turn to step 2. By taking a closer look at this process, you can know the reason why row 5th fails to be returned using ROWNUM = 5. If no rows 1st to 4th are returned, no 5th rows are returned. ROWNUM = 1 does return 1st rows, which seems to be in conflict with the preceding description. The reason is that ROWNUM = 1 returns 1st rows, regardless of whether the table has rows, Oracle will try to get at least 1 row. Take a closer look at the process described above and replace 5 with 1 to understand what is feasible to specify ROWNUM = 1 as a condition to return 1 behavior. If you want to obtain records within the specified row range, you can query the records by page. 2. A simple example of DBMS_RANDOM is shown in: 1 -- DBMS_RANDOM 2 -- The package provides a built-in random number generator that can be used to quickly generate random numbers. 3 -- 1. INITALIZE this process is used to initialize the DBMS_RANDOM package. When initializing the DBMS_RANDOM package, you must provide the random number seed 4 -- 2. SEED this process is used to reset the random number SEED 5 -- 3. RANDOM is used to generate RANDOM number 6 -- 4. TERMINATE this process is used to disable DBMS_RANDOM package 7 -- random NUMBER use Example 8 DECLARE 9 num INT; 10 seed NUMBER: = 10000000; 11 BEGIN12 dbms_random.initialize (seed); 13 FOR I IN 1 .. 10 LOOP14 num: = abs (dbms_random.random ()/seed); 15 dbms_output.put_line (num); 16 end loop; 17 dbms_random.terminate; 18 END; the running result is as follows: