{"id":1532,"date":"2017-11-25T13:46:19","date_gmt":"2017-11-25T06:46:19","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=1532"},"modified":"2025-05-25T06:10:46","modified_gmt":"2025-05-25T13:10:46","slug":"plsql-cursor-for-loop","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor-for-loop\/","title":{"rendered":"PL\/SQL Cursor FOR LOOP"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the PL\/SQL cursor FOR LOOP<\/code> statement to fetch and process every record from a cursor.<\/p>\n\n\n\n

Introduction to PL\/SQL cursor FOR LOOP statement #<\/a><\/h2>\n\n\n\n

The cursor FOR LOOP<\/code> statement is an elegant extension of the numeric FOR LOOP<\/a><\/code> statement.<\/p>\n\n\n\n

The numeric FOR LOOP<\/a><\/code> executes the body of a loop once for every integer value in a specified range.  Similarly, the cursor FOR LOOP<\/code> executes the body of the loop once for each row returned by the query<\/a> associated with the cursor.<\/p>\n\n\n\n

A nice feature of the cursor FOR LOOP<\/code> statement is that it allows you to fetch every row from a cursor without manually managing the execution cycle i.e.,  OPEN<\/code>, FETCH<\/code>, and CLOSE<\/code>.<\/p>\n\n\n\n

The cursor FOR LOOP<\/code> implicitly creates its loop index as a record<\/a> variable with the row type in which the cursor returns and then opens the cursor.<\/p>\n\n\n\n

In each loop iteration, the cursor FOR LOOP<\/code> statement fetches a row from the result set into its loop index. If there is no row to fetch, the cursor FOR LOOP<\/code> closes the cursor.<\/p>\n\n\n\n

The cursor is also closed if a statement inside the loop transfers control outside the loop, e.g., EXIT<\/code> and GOTO<\/a><\/code>, or raises an exception<\/a>.<\/p>\n\n\n\n

The following illustrates the syntax of the cursor FOR LOOP<\/code> statement:<\/p>\n\n\n

FOR<\/span> record<\/span> IN<\/span> cursor_name\nLOOP<\/span>\n    process_record_statements;\nEND<\/span> LOOP<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
1) record<\/strong><\/p>\n\n\n\n
The record<\/code> is the name of the index that the cursor FOR LOOP<\/code> statement declares implicitly as a %ROWTYPE<\/code> record variable of the type of the cursor.<\/p>\n\n\n\n
The record<\/code> variable is local to the cursor FOR LOOP<\/code> statement. It means that you can only reference it inside the loop, not outside. After the cursor FOR LOOP<\/code> statement execution ends, the record<\/code> variable becomes undefined.<\/p>\n\n\n\n
2) cursor_name<\/strong><\/p>\n\n\n\n
The cursor_name<\/code> is the name of an explicit cursor that is not opened when the loop starts.<\/p>\n\n\n\n
FOR<\/span> record<\/span> IN<\/span> (select_statement)\nLOOP<\/span>\n    process_record_statements;\nEND<\/span> LOOP<\/span>; \n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, the cursor FOR LOOP<\/code> declares, opens, fetches from, and closes an implicit cursor. However, the implicit cursor is internal; therefore, you cannot reference it.<\/p>\n\n\n\n
Note that Oracle Database automatically optimizes a cursor FOR LOOP<\/code> to work similarly to a BULK COLLECT<\/code> query. Although your code looks as if it fetched one row at a time, Oracle Database fetches multiple rows at a time and allows you to process each row individually.<\/p>\n\n\n\n
PL\/SQL cursor FOR LOOP examples #<\/a><\/h2>\n\n\n\n
Let’s look at some examples of using the cursor FOR LOOP<\/code> statement to see how it works.<\/p>\n\n\n\n
PL\/SQL cursor FOR LOOP example #<\/a><\/h3>\n\n\n\n
The following example declares an explicit cursor and uses it in the cursor FOR LOOP<\/code> statement.<\/p>\n\n\n
SET<\/span> SERVEROUTPUT ON<\/span>;\n\nDECLARE<\/span>\n  CURSOR<\/span> c_product\n  IS<\/span>\n    SELECT<\/span> \n        product_name, list_price\n    FROM<\/span> \n        products \n    ORDER<\/span> BY<\/span> \n        list_price DESC<\/span>;\nBEGIN<\/span>\n  FOR<\/span> r_product IN<\/span> c_product\n  LOOP<\/span>\n    dbms_output.put_line( r_product.product_name || ': $'<\/span> ||  r_product.list_price );\n  END<\/span> LOOP<\/span>;\nEND<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works:<\/p>\n\n\n\n
First, set serveroutput on to display output for the dbms_output.put_line:<\/p>\n\n\n
SET SERVEROUTPUT ON;<\/code><\/span><\/pre>\n\n\n
Second, declare a cursor based on a query that retrieves data from the products<\/code> table in the sample database:<\/p>\n\n\n
CURSOR c_product\n IS\n   SELECT \n       product_name, list_price\n   FROM \n       products \n   ORDER BY \n       list_price DESC;<\/code><\/span><\/pre>\n\n\n
Third, iterate over the record of the cursor and display the product name and list price of each to the output:<\/p>\n\n\n
FOR r_product IN c_product\n LOOP\n   dbms_output.put_line( r_product.product_name || ': $'<\/span> ||  r_product.list_price );\n END LOOP;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Cursor FOR LOOP with a SELECT statement example #<\/a><\/h3>\n\n\n\n
The following example is equivalent to the example above but uses a query in a cursor FOR LOOP<\/code> statement.<\/p>\n\n\n
SET<\/span> SERVEROUTPUT ON<\/span>;\n\nBEGIN<\/span>\n  FOR<\/span> r_product IN<\/span> (\n        SELECT<\/span> \n            product_name, list_price \n        FROM<\/span> \n            products\n        ORDER<\/span> BY<\/span> list_price DESC<\/span>\n    )\n  LOOP<\/span>\n     dbms_output.put_line( r_product.product_name ||\n        ': $'<\/span> || \n        r_product.list_price );\n  END<\/span> LOOP<\/span>;\nEND<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works:<\/p>\n\n\n\n
First, declare an implicitly declared record variable r_product wihtin the FOR LOOP. The cursor will hold one row returend by the SELECT statement.<\/p>\n\n\n\n
Second, specify a query that creates an implicit cursor: <\/p>\n\n\n
(SELECT product_name, list_price FROM products ORDER BY list_price DESC)<\/code><\/span><\/pre>\n\n\n
PL\/SQL engine will:<\/p>\n\n\n\n
    \n
  • Execute the SELECT query.<\/li>\n\n\n\n
  • Open an internal cursor for the result.<\/li>\n\n\n\n
  • Fetch rows from this cursor one by one.<\/li>\n\n\n\n
  • Close the cursor automatically when it completes processing all rows.<\/li>\n<\/ul>\n\n\n\n
    Third, display the product and price within the loop body between LOOP and END LOOP.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Note that besides the cursor name, you can use a SELECT<\/a><\/code> statement as shown below:<\/p>\n\n\n