How to use DBMS_RANDOM Package In SQL
The DBMS_RANDOM package provides random generation capabilities. It has several functions but we are going to make use of the RANDOM function from the package.
The RANDOM function generates integers in the range [-2^31, 2^^31).
Syntax:
DBMS_RANDOM.RANDOM
Example: The following query uses the RANDOM function
Query:
SELECT DBMS_RANDOM.RANDOM FROM DUAL;
Output:
Explanation: In this above query, DBMS_RANDOM.RANDOM
is used to generate a random number between 0 and 1. FROM
DUAL
is a special construct in Oracle used to select a single row from a dummy table. The query returns a single random number.
We can use the DBMS_RANDOM.RANDOM function to select a random record from the table. We will make use of the function in the ORDER BY clause and then use row_number to filter the records. The following query implements the above logic.
Query:
SELECT * FROM (
SELECT * FROM test
ORDER BY DBMS_RANDOM.RANDOM
)
WHERE rownum<2;
Output:
Explanation: In the above query, we have selects a single random record from the test
table. It does this by first ordering the records randomly using DBMS_RANDOM
.
RANDOM
in the inner query and then limiting the result to one row using rownum<2
in the outer query.
How to Select Random Record From Table in PL/SQL?
In database management selecting random records from a table can be a useful operation for various applications such as sampling data, generating random samples for testing or selecting winners in a lottery.
In this article, we will explore different methods to select random records from a table in PL/SQL, Oracle’s procedural language extension for SQL. By the end of this article, you will understand how to use different approaches to achieve this task effectively.