PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features.PL/SQL supports SQL queries. It also supports the declaration of the variables, control statements, Functions, Records, Cursor, Procedure, and Triggers.
PL/SQL contains a declaration section, execution section, and exception-handling section. Declare and exception handling sections are optional.
PL/SQL Query to Retrieve Top 10 Records
In this article, we will learn how to retrieve the top 10 values from the database using PL/SQL. We are often required to display specific values to get insights about the database such as top performers, top gainers, or other data.
We will use a SELECT statement, table name, along with the condition or ORDER BY keyword to fetch the specific values. PL/SQL cursor can also be used to display the top 10 values.
- Using Select Statement
- Using Cursor
Setting up Environment
Let’s create a GEEKS table and insert the value in it:
Table:
CREATE TABLE GEEKS( ID NUMBER, NAME VARCHAR2(20) , SCORE NUMBER );
INSERT INTO GEEKS (ID, Name, Score) VALUES (1, 'Sam', 800);
INSERT INTO GEEKS (ID, Name, Score) VALUES (2, 'Joy', 699);
INSERT INTO GEEKS (ID, Name, Score) VALUES (3, 'Tom', 250);
INSERT INTO GEEKS (ID, Name, Score) VALUES (5, 'Anil', 525);
INSERT INTO GEEKS (ID, Name, Score) VALUES (4, 'Jay', 1050);
INSERT INTO GEEKS (ID, Name, Score) VALUES (6, 'Sunil', 54);
INSERT INTO GEEKS (ID, Name, Score) VALUES (7, 'Om', 87);
INSERT INTO GEEKS (ID, Name, Score) VALUES (8, 'Ajay', 528);
INSERT INTO GEEKS (ID, Name, Score) VALUES (9, 'Amit', 4564);
INSERT INTO GEEKS (ID, Name, Score) VALUES (10, 'Ajit', 55);
INSERT INTO GEEKS (ID, Name, Score) VALUES (11, 'Aditya', 464);
INSERT INTO GEEKS (ID, Name, Score) VALUES (12, 'Arsh', 4783);
INSERT INTO GEEKS (ID, Name, Score) VALUES (13, 'Akaay', 9999);
INSERT INTO GEEKS (ID, Name, Score) VALUES (14, 'Arshdeep', 897);
INSERT INTO GEEKS (ID, Name, Score) VALUES (15, 'Akash', 121);
INSERT INTO GEEKS (ID, Name, Score) VALUES (16, 'Yash', 241);
INSERT INTO GEEKS (ID, Name, Score) VALUES (17, 'Aniruddha', 151);
INSERT INTO GEEKS (ID, Name, Score) VALUES (18, 'Mahesh', 152);
INSERT INTO GEEKS (ID, Name, Score) VALUES (19, 'Siddhart', 531);
INSERT INTO GEEKS (ID, Name, Score) VALUES (20, 'Omkar', 541);
INSERT INTO GEEKS (ID, Name, Score) VALUES (21, 'Jeet', 155);
INSERT INTO GEEKS (ID, Name, Score) VALUES (22, 'Soham', 156);
INSERT INTO GEEKS (ID, Name, Score) VALUES (23, 'Rohit', 157);
INSERT INTO GEEKS (ID, Name, Score) VALUES (24, 'Hardik', 1578);
INSERT INTO GEEKS (ID, Name, Score) VALUES (25, 'Surya', 159);
1. Using Select Statement
In this approach, we will use the subquery to fetch the top 10 values from the table.
SELECT * FROM table_name WHERE ROWNUM <= 10 ORDER BY column DESC;If we use the above query it will consider only the first 10 rows from the table and log them in the descending order. It will not consider the remaining rows of the table and not order them in descending order.Hence we will use the subquery to log the top 10 values.
Syntax:
Select column1,column2 From (Select * from Table_name Order By column desc ) Where ROWNUM <=10) - The inner subquery returns the table in descending order of the column.
- Where ROWNUM <=10 is used to limit the number of rows returned from the select query.
- The outer select statement is used to retrieve the top 10 values.
Example:
SELECT ID,NAME, SCORE FROM (SELECT * FROM GEEKS ORDER BY score DESC)
WHERE ROWNUM <= 10;
Output:
PLSQL Query to retrieve Top 10 Record
2. Using Cursor
Oracle uses a special memory space called a context area for storing and retrieving information. The context area contains all the details related to the database. The cursor is the virtual pointer to the context area, in the database.
The major function of a cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time. We will use cursor to fetch top 10 values from the database.
Syntax:
DECLARE
--declare variable
--declare cursor
CURSOR cursor IS
SELECT column1,column2 .... FROM (SELECT * FROM table_name ORDER BY colmun) WHERE ROWNUM <= 10;
BEGIN
OPEN cursor ;
LOOP
FETCH cursor INTO declared variables;
EXIT WHEN cursor%NOTFOUND;
-- print the values
DBMS_OUTPUT.PUT_LINE(variables);
END LOOP;
-- Close the cursor
CLOSE cursor;
END;
- Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
- Open: A Cursor is opened and populated by executing the SQL statement defined by the cursor.
- Fetch: When the cursor is opened, rows can be fetched from the cursor one by one or in a block.
- Close: After data manipulation, close the cursor explicitly.
Example:
DECLARE
c_NAME GEEKS.NAME%TYPE; c_SCORE GEEKS.SCORE%TYPE;
--declare cursor
CURSOR top_val IS
SELECT NAME, SCORE FROM (SELECT * FROM GEEKS ORDER BY score DESC) WHERE ROWNUM <= 10;
BEGIN
--open cursor
OPEN top_val;
LOOP
FETCH top_val INTO c_NAME, c_SCORE;
EXIT WHEN top_val%NOTFOUND;
-- print the values
DBMS_OUTPUT.PUT_LINE('NAME: ' || c_NAME || ', SCORE: ' || c_SCORE);
END LOOP;
-- Close the cursor
CLOSE top_val;
END;
Output:
PLSQL Query to retrieve Top 10 Records
Explanation:
- In DECLARE section, a cursor is declared by defining the SQL statement that return the top 10 values as a result.
- In the BEGIN section, a cursor is opened and rows are fetched from the table.
- After displaying the values, the cursor is closed.
Conclusion
Retrieving top values from databases is crucial for gaining insights. In this article, we explored two effective methods using PL/SQL: utilizing subqueries and PL/SQL Cursors. Whether it’s analyzing top performers or identifying trends, mastering these techniques empowers developers to extract valuable information efficiently from their databases.