Updated April 5, 2023
Introduction to PL/SQL FOR Loop Cursor
Pl/ SQL for loop cursor statement is created to execute a particular block of code containing single or multiple statements for each and every individual row that is retrieved by executing the cursor select query statement. Usually, the loop cursor in Pl/ SQL is considered as an extension to the original integer or numeric for loop used in PL/ SQL. The only difference between the two for loops is that in PL/ SQL cursor for loop the iteration is made for each and every row retrieved from the select statement of cursor query while in the case of numeric for loop the iterations are decided on the basis of the number of integers inside the specified integer range values. In this article, we will have a look at the working of the for loop cursor, its syntax, and the implementation of the same with the help of demonstrations including the examples in it.
Working of FOR Loop Cursor
In numeric for loop the integer value which is considered may decrease or increase in value while every iteration is made. In the case of for loop cursor, there is no necessity of any integer value for iterations. The cycle of execution involving the steps such as fetching, opening, and closing of the index for the cursor rows is maintained internally which makes the use of for loop efficient and easy.
The cursor in PL/ SQL is associated with a particular select statement that returns single or multiple rows in its result set. The body of the for loop cursor is executed once per row retrieved in its result set. The loop index is created internally for referring to the individual rows of the select query statement and executing the complete cycle of execution involving the opening, closing, and fetching tasks for the index.
The type of the index created internally is %ROWTYPE which is responsible for opening up the cursor, fetching one by one the rows of the cursor query, and lastly after all the rows are completed, close the cursor respectively. The for loop cursor automatically closes and comes out of the loop in case of exceptional cases such as use of EXIT, GOT or exception occurrence. In that case, the flow of execution is transferred to the immediate next line where the loop ends.
Syntax:
The syntax of the PL/ SQL for loop cursor can be analyzed and understood easily by seeing the below description:
FOR returned_row_index IN my_cursor
LOOP
Block of code to be executed for each record of select query of cursor
END LOOP;The above syntax can be understood in a better way after reading about the terminologies used in it as provided here –
Returned_row_index – This is the index name of the ROWTYPE Data type which will be responsible for the cursor type records retrieved and behaving like a variable for the same. The variable which is declared for index management here has the local scope which results in the access of the variable restricted up to the cursor loop body only. The value of the returned row index variable becomes undefined after fetching all the rows is completed from the cursor for each of the iterations.
My_cursor – This can be any name that you want to provide to your cursor. It is necessary to specify the name of the cursor explicitly here to refer to it further. This cursor should be created priorly and should retrieve the rows in its result set.
The cursor name here can also be replaced with the use of the select statement which retrieves multiple rows in it as the PL/ SQL internally creates an implicit cursor for the select query which is specified in place of the cursor. But while using the select statement in place of the cursor name we cannot refer to the internal cursor created for the select statement anywhere further in the program if needed as it is maintained internally.
The use of for loop cursor is very efficient because it works similar to that of the bulk collector. In the for loop cursor, all the rows of the cursor are retrieved and fetched at once at the beginning itself. After that, for each of the fetched row, the body is executed individually. Hence, even though it may look that the cursor retrieves each row individually, internally it works just in the opposite manner.
Examples
In order to understand the implementation and working of the for loop cursor, let us look at certain examples demonstrating the usage of for loop cursor in Pl/ SQL.
Example #1
For demonstrating the working of the for loop cursor in PL/ SQL, we will firstly create a cursor retrieving the records of a particular table. For the first example, we will consider an existing table in our database whose name is employee details which contain the information of the employees working at different stores of a particular company. In order to firstly verify and check the contents of this table, we will need to execute the simple select query statement on that table as shown below –
SELECT * FROM employee_details;The output of the execution of the above query statement is as shown below showing the contents of the employee details table –
We will make use of the for loop cursor to iterate all the records of the employee details table. Firstly, we will declare and create a cursor that will retrieve the records of this table, and then for that the cursor we will use the for loop cursor in Pl/ SQL to display a message showing the salary of each of the employees as shown in the below Pl/ SQL program –
DECLARE
CURSOR my_employee_cursor
IS
SELECT
f_name , salary
FROM
employee_details
BEGIN
FOR my_employee_variable IN my_employee_cursor
LOOP
dbms_output.put_line( my_employee_variable.f_name || ' has the salary ' || my_employee_variable.salary );
END LOOP;
END;The output of the above program is –
Conclusion
The for loop cursor is used for iterating the result set of a particular select query which may retrieve single or multiple rows. For each of the rows retrieved the for loop, cursor is executed once.
Recommended Articles
We hope that this EDUCBA information on “PL/SQL FOR Loop Cursor” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
