Introduction to PL/SQL Cursor Loop
PL/SQL cursor loop statement is used for fetching and processing each and every record one by one which is referred to by a cursor. The FOR LOOP is used by the cursor for carrying out the repetitive task of processing the records retrieved from the cursor reference. This type of cursor for loop is a great extension of the traditional type of numeric for loop in which an integer was used to store a value that will be incremented or decremented on each loop traversal.
Further, the manipulation and modification of the integer was also the extra work added. That is why, the use of cursor for loop is very efficient as none of the value needs to be maintained for looping out and all the execution cycle containing the opening, closing, and fetching of the index or cursor that was in traditional integer for loops is maintained internally automatically by cursor loop. In this article, we will have a look at the syntax, working and implementation of the cursor loops in PL/ SQL along with the help of certain examples.
Functions of Cursor Loop
Each cursor is associated with a certain query and for all the records retrieved from that query, the cursor loop does the execution of the body of the loop once for each record. An implicit creation of the loop index is being done while using cursor for loop which helps to retrieve each and every row from the cursor and the best part is that there is no need to manage the cycle of execution involving open, fetch and close steps.
The loop index has the datatype of ROWTYPE which internally returns the cursor and also opens the same later. While looping around the resultset using the cursor, each of the iterations has its associated record fetched inside the loop index. In case if none of the record is left for the iteration the cursor automatically closes its loop. In other situations, like the use of GOTO, EXIT, and raise an exception the cursor is automatically closed and gets out of the loop execution which transfers the control of execution flow outside the loop.
Syntax:
The syntax of the cursor loop also called as cursor for loop is as shown below:
FOR record value IN name_of_cursor
LOOP
Processing for each of the individual record of cursor;
END LOOP;
The terms used in the syntax are explained below:
- recordValue – The name of the index used by cursor and having the ROWTYPE datatype which acts as the record variable for cursor type. Note that the scope of this variable is local scope hence, it can be only used inside the cursor loop and not outside it. After all, the execution of iterations of the cloop are completed the value of the record variable becomes undefined.
- Name_of_cursor – As the name suggest it is the cursor name that is declared explicitly and remains close till loop starts. We can also make the alternative usage of SELECT statement in place of the name of cursor which retrieves a number of records. When SELECT function is used, the DBMS creates an implicit cursor which opens, fetches, and closes internally. One of the disadvantages, in this case, is that we cannot refer that cursor further in our program as it is maintained internally while using select query.
The optimization of the cursor for loop is done by the oracle DBMS that makes it work in a similar manner as that of bulk collect. Hence, even though it might look like the record is being fetched one by one it does not happen so. The Oracle DBMS retrieves and fetches all the records at once and the processing is done one by one.
Example
Let us now look at how we can make the use of cursor for loop in our code with the help of certain examples.
Example #1
Let us have a look at the example that demonstrates the usage of how the cursor
can be used in PL/ SQL to retrieve the information about a particular entry in the table. Consider that we have a table called customer details which stores the details of the customers. To check the contents of the table, we can fire the following query in SQL –
SELECT * FROM [customers_details];
The output of execution of above query statement is as shown below showing the contents of the table customer details –
To retrieve the details of the table in such a way that each of the customer’s first name is retrieved and the contact details showing its mobile number is retrieved, we can create a procedure in PL/ SQL. In this procedure, we will make use of the cursor named sample cursor which will point out to all the resultset of the customer details table having its f_name and mobile number fields.
Further, we will loop through the result by rotating it in the loop where we will take each of the individual record of the cursor table contents one by one in a variable named sample variable. Then we will keep on displaying the result in such a way the DBMS output will contain the name and its corresponding mobile number with an in-between string attached as “having the mobile number”.
DECLARE
CURSOR sampleCursor
IS
SELECT
f_name, mobile_number
FROM
customers_details
BEGIN
FOR sampleVariable IN sampleCursor
LOOP
dbms_output.put_line( sampleVariable.f_name || ' has the contact number ' || sampleVariable.mobile_number );
END LOOP;
END;
The output of above code is as displayed below:
Example #2
Consider one more example where we have a table named Employees which has the following contents in it when retrieved by using the query statement:
SELECT * FROM [Employees]
The execution of above instruction gives following output:
Now, in case if we want to retrieve all the data of the Employees table in such a way that it should display the first name and then the birth date in the format – firstname has his birthday on birthdate. For that thing, we can make the use of following procedure –
DECLARE
CURSOR sampleCursor
IS
SELECT
FirstName, BirthDate
FROM
Employees
BEGIN
FOR sampleVariable IN sampleCursor
LOOP
dbms_output.put_line( sampleVariable.FirstName || ' has his birthday on ' || sampleVariable.BirthDate );
END LOOP;
END;
The output of above procedure is as shown below:
Conclusion
We can make the use of cursor loop in PL/ SQL to loop around a particular cursor or the records retrieved from select query in order to perform certain task again and again for each of the record from the result set.
Recommended Articles
This is a guide to PL/SQL Cursor Loop. Here we discuss the Introduction, syntax, Working of Cursor Loop, examples with code implementation. You may also have a look at the following articles to learn more –
