---
description: This tutorial covers PL/SQL Cursor definition, Implicit cursor, Explicit cursor, cursor attributes, for loop cursor statements with examples, etc.
title: Oracle PL/SQL Cursor: Implicit, Explicit, For Loop with Example
image: https://www.guru99.com/images/oracle-plsql-cursor-types.png
---

 

[Skip to content](#main) 

**⚡ Smart Summary**

Cursors in Oracle PL/SQL are pointers to the context area that holds the rows returned by an SQL statement. Two kinds exist: implicit cursors, created automatically for DML, and explicit cursors, declared and controlled by the programmer.

* 📍 **Context Area:** A cursor points to the context area that stores an SQL statement and its returned active set.
* ⚙️ **Implicit Cursor:** Oracle opens an implicit cursor automatically for every DML statement and single-row SELECT INTO.
* ✋ **Explicit Cursor:** A programmer declares, opens, fetches, and closes an explicit cursor for full control.
* 🔎 **Cursor Attributes:** %FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT report the status of the most recent operation.
* 🔁 **Cursor FOR Loop:** A FOR loop opens, fetches, and closes a cursor implicitly, needing no manual steps.
* 🤖 **AI Assistance:** AI assistants such as GitHub Copilot draft cursor loops and flag unclosed cursors.

[ Read More ](javascript:void%280%29;) 

![Oracle PL/SQL Cursor Implicit Explicit and FOR Loop](https://www.guru99.com/images/oracle-plsql-cursor-types.png)

## What is CURSOR in PL/SQL?

A cursor is a pointer to the context area. Oracle creates a context area for processing an [SQL](https://www.guru99.com/sql.html) statement, and this area contains all the information about the statement.

[PL/SQL](https://www.guru99.com/pl-sql-tutorials.html) allows the programmer to control the context area through the cursor. A cursor holds the rows returned by the SQL statement, and the set of rows the cursor holds is referred to as the active set. These cursors can also be named so that they can be referred to from another place in the code.

The cursor is of two types:

* **Implicit Cursor**
* **Explicit Cursor**

## Implicit Cursor

Whenever any [DML operation](https://www.guru99.com/sql-pl-sql.html) occurs in the database, an implicit cursor is created that holds the rows affected in that particular operation. These cursors cannot be named and, hence, they cannot be controlled or referred to from another place in the code. We can refer only to the most recent cursor through the cursor attributes.

## Explicit Cursor

Programmers are allowed to create a named context area to execute their DML operations and get more control over it. The explicit cursor should be defined in the declaration section of the [PL/SQL block](https://www.guru99.com/blocks-pl-sql.html), and it is created for the SELECT statement that needs to be used in the code.

Below are the steps involved in working with explicit cursors:

* **Declaring the cursor:** Declaring the cursor simply means creating one named context area for the SELECT statement that is defined in the declaration part. The name of this context area is the same as the cursor name.
* **Opening the cursor:** Opening the cursor instructs PL/SQL to allocate the memory for this cursor. It makes the cursor ready to fetch the records.
* **Fetching data from the cursor:** In this process, the SELECT statement is executed and the fetched rows are stored in the allocated memory. These are now called active sets. Fetching data from the cursor is a record-level activity, which means we can access the data in a record-by-record way. Each fetch statement fetches one active set and holds the information of that particular record. This statement is the same as a SELECT statement that fetches the record and assigns it to the variable in the INTO clause, but it will not throw any [exceptions](https://www.guru99.com/exception-handling-pl-sql.html).
* **Closing the cursor:** Once all the records are fetched, we need to close the cursor so that the memory allocated to this context area is released.

**Syntax**

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
<cursor_variable declaration>;
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

In the above syntax, the declaration part contains the declaration of the cursor and the cursor variable in which the fetched data will be assigned. The cursor is created for the SELECT statement that is given in the cursor declaration. In the execution part, the declared cursor is opened, fetched, and closed.

### RELATED ARTICLES

* [Oracle PL/SQL Collections: Varrays, Nested & Index by Tables ](https://www.guru99.com/complex-data-types-pl-sql.html "Oracle PL/SQL Collections: Varrays, Nested & Index by Tables")
* [PL/SQL Variable Scope & Inner Outer Block: Nested Structure ](https://www.guru99.com/nested-blocks-pl-sql.html "PL/SQL Variable Scope & Inner Outer Block: Nested Structure")
* [PL/SQL Acceptable Identifiers, Variable & Naming Conventions ](https://www.guru99.com/pl-sql-identifiers.html "PL/SQL Acceptable Identifiers, Variable & Naming Conventions")
* [Autonomous Transaction in Oracle PL/SQL ](https://www.guru99.com/pl-sql-tcl-statements.html "Autonomous Transaction in Oracle PL/SQL")

## Cursor Attributes

Both the implicit cursor and the explicit cursor have certain attributes that can be accessed. These attributes give more information about the cursor operations. Below are the different cursor attributes and their usage.

| Cursor Attribute | Description                                                                                                                   |
| ---------------- | ----------------------------------------------------------------------------------------------------------------------------- |
| %FOUND           | Returns the Boolean result TRUE if the most recent fetch operation fetched a record successfully; otherwise it returns FALSE. |
| %NOTFOUND        | Works opposite to %FOUND. It returns TRUE if the most recent fetch operation could not fetch any record.                      |
| %ISOPEN          | Returns the Boolean result TRUE if the given cursor is already open; otherwise it returns FALSE.                              |
| %ROWCOUNT        | Returns a numerical value giving the actual count of records affected or fetched by the operation.                            |

**Explicit Cursor Example:** In this example, we will see how to declare, open, fetch, and close an explicit cursor. We will project all the employee names from the emp table using a cursor. We will also use a cursor attribute to set the loop to fetch all the records from the cursor.

The screenshot below shows this explicit cursor example and its output in Oracle.

[](https://www.guru99.com/images/PL-SQL/110215%5F1042%5FSQLinPLSQL9.png)

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;
BEGIN
OPEN guru99_det;
LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line('Employee Fetched:'||lv_emp_name);
END LOOP;
Dbms_output.put_line('Total rows fetched is'||guru99_det%ROWCOUNT);
CLOSE guru99_det;
END;
/

**Output**

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Total rows fetched is 3

**Code Explanation**

* **Code line 2:** Declaring the cursor guru99\_det for the statement ‘SELECT emp\_name FROM emp’.
* **Code line 3:** Declaring the variable lv\_emp\_name with the [%type](https://www.guru99.com/pl-sql-data-types.html) anchored to emp.emp\_name.
* **Code line 5:** Opening the cursor guru99\_det.
* **Code line 6:** Setting the basic loop statement to fetch all the records in the emp table.
* **Code line 7:** Fetches the guru99\_det data and assigns the value to lv\_emp\_name.
* **Code line 8:** Using the cursor attribute %NOTFOUND to check whether all records in the cursor are fetched. If fetched, it returns TRUE and control exits the loop; otherwise control keeps fetching the data from the cursor and prints it.
* **Code line 10:** EXIT condition for the loop statement.
* **Code line 12:** Print the fetched employee name.
* **Code line 14:** Using the cursor attribute %ROWCOUNT to find the total number of records fetched by the cursor.
* **Code line 15:** After exiting the loop, the cursor is closed and the allocated memory is freed.

## FOR Loop Cursor statement

A cursor [FOR loop](https://www.guru99.com/oracle-plsql-for-loop.html) can be used for working with cursors. We can give the cursor name instead of a range limit in the FOR loop statement, so that the loop works from the first record of the cursor to the last record of the cursor. The cursor variable, opening of the cursor, fetching, and closing of the cursor are all done implicitly by the FOR loop.

**Syntax**

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
FOR I IN <cursor_name>
LOOP
.
.
END LOOP;
END;

In the above syntax, the declaration part contains the declaration of the cursor. The cursor is created for the SELECT statement that is given in the cursor declaration. In the execution part, the declared cursor is set up in the FOR loop, and the loop variable ‘I’ behaves as the cursor variable in this case.

**Oracle Cursor for Loop Example:** In this example, we will project all the employee names from the emp table using a cursor-FOR loop.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line('Employee Fetched:'||lv_emp_name.emp_name);
END LOOP;
END;
/

**Output**

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY

**Code Explanation**

* **Code line 2:** Declaring the cursor guru99\_det for the statement ‘SELECT emp\_name FROM emp’.
* **Code line 4:** Constructing the FOR loop for the cursor with the loop variable lv\_emp\_name.
* **Code line 6:** Printing the employee name in each iteration of the loop.
* **Code line 7:** Exit the loop (END LOOP).

**Note:** In a cursor-FOR loop, cursor attributes cannot be used, since opening, fetching, and closing of the cursor is done implicitly by the FOR loop.

## FAQs

🔁 What is a REF CURSOR in PL/SQL?

A REF CURSOR (cursor variable) is a pointer to a query result set. Unlike a static cursor, it can open different queries at run time and pass results between PL/SQL blocks or to client programs.

📦 How does BULK COLLECT differ from a normal cursor fetch?

A normal cursor fetches one row per FETCH, causing many context switches. [BULK COLLECT](https://www.guru99.com/pl-sql-bulk-collect.html) loads many rows into a collection in a single fetch, cutting overhead sharply on large result sets.

🎛️ Can you pass parameters to a PL/SQL cursor?

Yes. Declare a parameterized cursor such as CURSOR c(dept NUMBER) IS SELECT …, then pass values at OPEN c(10). Parameters let you reuse one cursor definition with different filter values.

🔒 What do FOR UPDATE and WHERE CURRENT OF do in a cursor?

FOR UPDATE locks the rows a cursor selects so no one else can change them. WHERE CURRENT OF then updates or deletes the exact row just fetched, without repeating the WHERE condition.

⚠️ What happens if an explicit cursor is left open?

Open cursors keep their memory reserved and count against the OPEN\_CURSORS limit. Leaving many open eventually raises ORA-01000: maximum open cursors exceeded, so always CLOSE an explicit cursor after use.

⚡ Why can a row-by-row cursor loop be slower than one SQL statement?

Each FETCH switches between the PL/SQL and SQL engines. Thousands of such context switches add up, so a single set-based SQL statement or BULK COLLECT usually processes the same rows far faster.

🤖 Can GitHub Copilot generate PL/SQL cursor loops?

Yes. [GitHub Copilot](https://github.com/features/copilot) drafts explicit OPEN, FETCH, and CLOSE loops or cursor FOR loops from a comment, adds %NOTFOUND exit checks, and suggests attribute names, though you should review the logic first.

🧠 How does AI help optimize cursor-heavy PL/SQL?

AI assistants flag row-by-row cursor loops that could become set-based SQL or BULK COLLECT, spot unclosed cursors, and explain %attribute behaviour. This machine-learning review improves performance before code reaches production.

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/oracle-plsql-cursor-types.png","url":"https://www.guru99.com/images/oracle-plsql-cursor-types.png","width":"700","height":"250","caption":"Oracle PL/SQL Cursor Types","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/pl-sql-cursor.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/pl-sql","name":"PL-SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/pl-sql-cursor.html","name":"Oracle PL/SQL Cursor: Implicit, Explicit, For Loop with Example"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/pl-sql-cursor.html#webpage","url":"https://www.guru99.com/pl-sql-cursor.html","name":"Oracle PL/SQL Cursor: Implicit, Explicit, For Loop with Example","dateModified":"2026-07-22T17:27:50+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/oracle-plsql-cursor-types.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/pl-sql-cursor.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown","description":"I'm Fiona brown, a Full Stack Developer with over a decade of experience, sharing practical guides on robust and scalable application development.","url":"https://www.guru99.com/author/fiona","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/fiona-brown-author.png","url":"https://www.guru99.com/images/fiona-brown-author.png","caption":"Fiona Brown","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"PL-SQL","headline":"Oracle PL/SQL Cursor: Implicit, Explicit, For Loop with Example","description":"This tutorial covers PL/SQL Cursor definition, Implicit cursor, Explicit cursor, cursor attributes, for loop cursor statements with examples, etc.","keywords":"pl-sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"dateModified":"2026-07-22T17:27:50+05:30","image":{"@id":"https://www.guru99.com/images/oracle-plsql-cursor-types.png"},"copyrightYear":"2026","name":"Oracle PL/SQL Cursor: Implicit, Explicit, For Loop with Example","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is a REF CURSOR in PL/SQL?","acceptedAnswer":{"@type":"Answer","text":"A REF CURSOR (cursor variable) is a pointer to a query result set. Unlike a static cursor, it can open different queries at run time and pass results between PL/SQL blocks or to client programs."}},{"@type":"Question","name":"How does BULK COLLECT differ from a normal cursor fetch?","acceptedAnswer":{"@type":"Answer","text":"A normal cursor fetches one row per FETCH, causing many context switches. BULK COLLECT loads many rows into a collection in a single fetch, cutting overhead sharply on large result sets."}},{"@type":"Question","name":"Can you pass parameters to a PL/SQL cursor?","acceptedAnswer":{"@type":"Answer","text":"Yes. Declare a parameterized cursor such as CURSOR c(dept NUMBER) IS SELECT ..., then pass values at OPEN c(10). Parameters let you reuse one cursor definition with different filter values."}},{"@type":"Question","name":"What do FOR UPDATE and WHERE CURRENT OF do in a cursor?","acceptedAnswer":{"@type":"Answer","text":"FOR UPDATE locks the rows a cursor selects so no one else can change them. WHERE CURRENT OF then updates or deletes the exact row just fetched, without repeating the WHERE condition."}},{"@type":"Question","name":"What happens if an explicit cursor is left open?","acceptedAnswer":{"@type":"Answer","text":"Open cursors keep their memory reserved and count against the OPEN_CURSORS limit. Leaving many open eventually raises ORA-01000: maximum open cursors exceeded, so always CLOSE an explicit cursor after use."}},{"@type":"Question","name":"Why can a row-by-row cursor loop be slower than one SQL statement?","acceptedAnswer":{"@type":"Answer","text":"Each FETCH switches between the PL/SQL and SQL engines. Thousands of such context switches add up, so a single set-based SQL statement or BULK COLLECT usually processes the same rows far faster."}},{"@type":"Question","name":"Can GitHub Copilot generate PL/SQL cursor loops?","acceptedAnswer":{"@type":"Answer","text":"Yes. GitHub Copilot drafts explicit OPEN, FETCH, and CLOSE loops or cursor FOR loops from a comment, adds %NOTFOUND exit checks, and suggests attribute names, though you should review the logic first."}},{"@type":"Question","name":"How does AI help optimize cursor-heavy PL/SQL?","acceptedAnswer":{"@type":"Answer","text":"AI assistants flag row-by-row cursor loops that could become set-based SQL or BULK COLLECT, spot unclosed cursors, and explain %attribute behaviour. This machine-learning review improves performance before code reaches production."}}]}],"@id":"https://www.guru99.com/pl-sql-cursor.html#schema-1149128","isPartOf":{"@id":"https://www.guru99.com/pl-sql-cursor.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/pl-sql-cursor.html#webpage"}}]}
```