FETCH in Oracle

Introduction to FETCH in Oracle

FETCH statement is used in Oracle to retrieve row or rows of data from the result set of a multi row query as FETCH statement has the ability to retrieve one row of data at a instance, more than one row of data or even all rows of data present in the result set thereby allowing the developer to control the percentage of data from the actual result set is required and then storing the row/ rows of data retrieved from FETCH into corresponding columns selected from query.

Syntax:

FETCH cursorName INTO variableList;

Parameters:

• cursorName: It refers the name of the cursor from where we want to fetch the rows.
• variableList: It refers to the name of the variables in which we want to store the rows from the cursor. The variables should be comma delimited while specifying.

How does FETCH Statement work in Oracle?

• FETCH statement works in oracle as it is used to retrieve the rows from cursor so that the developer can do some sort of operation on it or store it into a corresponding variable.
•  So, FETCH is generally used in PL/SQL with cursor and also in SQL.
• The cursor is used to retrieve the result set from the database based on the SELECT statement and the conditions present in the SELECT statement.
• So once the cursor retrieves the result set which is a combination of rows and columns, we need to do some operation on the result set. So, in that case we use the FETCH statement to retrieve those rows from cursor.
• The FETCH statement allows us to control the number of rows we want to work or operate. We can retrieve one row, more than one or all the rows from the cursor.
• In case of SQL query or statement we write it at the end of the query, So after the query executes (with or without WHERE clause) and we get the result set based on the query.
• The FETCH clause present at the end helps us to limit the number of rows of data actually we want to retrieve and display as an output and we can change it as per our use.

Examples of FETCH in Oracle

Below are the examples mentioned:

Example #1

Finding the Age of the Employee using a Function.

In this example we are going to use FETCH statement inside a function to find the age of the concerned employee. The function would take the employee_id of the employee as a variable and would return the age of the employee if present or return a error number if the employee is not present.

Code:

PL/SQL Function

CREATE OR REPLACE Function FindAge
( employee_id IN varchar2 )
RETURN number
IS
v_age number;
CURSOR c1
IS
SELECT age
FROM employee
WHERE employee_id = employee_id;
BEGIN
OPEN c1;
FETCH c1 INTO v_age;
if c1%notfound then
v_age := 9999;
end if;
CLOSE c1;
RETURN v_age;
END;

If we see the above PL/SQL program, the function with name Find Age is created which takes the employee_id as a parameter in the function. The cursor is then initialized and it retrieves the age of the employee based on the employee_id the developer gave as an argument while calling the function. The FETCH statement if we see in the program then retrieves the age from the cursor and stores it in the age variable which is a local variable we declared at the beginning of the function. We then return the age or an error number in case there is no employee present in the table with the input employee id.

Output:

Let us now run the program in SQL developer and check the result.

As we can see in the screenshot above the function has been compiled with no errors. Let us now call the function and execute it in the SQL developer.

The below program calls the function and then displays the age of the employee.

Code:

SET SERVEROUTPUT ON
DECLARE
v_age NUMBER := 0;
BEGIN
v_age := FindAge('AD004');
DBMS_OUTPUT.PUT_LINE('Age is: ' || v_age);
END;

Output:

Let us now run the above statements in SQL Developer and check the result.

As we can see in the above screenshot the age of the employee gets displayed in the output.

Example #2

FETCH in SQL to limit the number of Rows.

In this example we will check the use of FETCH clause to limit the number of rows to be retrieved from database. So, since we have a table called employee in the database we will retrieve only five rows from employee table of employees having age less than 40 years.

Code:

SQL Query

SELECT * FROM EMPLOYEE WHERE AGE<40
FETCH NEXT 5 ROWS ONLY;

So, the above query should fetch only five rows from the employee table which satisfies the condition. Let us run the query in SQL developer.

Output:

As we can see in the above screenshot only five rows satisfying the condition (age less than 40) is displayed.

Advantages of using FETCH in Oracle

• FETCH is used to limit the number of rows we want to retrieve from the database or cursor in case of PL/SQL.
• It is important as it allows us to limit the number of rows Suppose if we use simple WHERE clause then it will return all the rows but in case of FETCH we can set a limit of rows and based on that the Oracle database will return the rows.

Conclusion

In this article we discussed about FETCH in oracle. We discussed the syntax and its parameters in this article along with the working of FETCH in Oracle. Later on we discussed two examples from PL/SQL and SQL.

This is a guide to FETCH in Oracle. Here we discuss the introduction, how does FETCH statement work in Oracle with examples and advantages. You may also have a look at the following articles to learn more –

1. Oracle Alter Table
2. Oracle FOREIGN Key
3. Oracle Full Outer Join
4. BETWEEN in Oracle