Cursor in Oracle
==> A cursor is a temporary work area created in the system memory when a SQL statement is executed.
==> The Data that is Stored in the cursor is called the Active Data Set. As this data is stored due to execution of some SQL statement, it is also called Result set.
Types of Cursors:-
1. Implicit Cursor
2. Explicit Cursor
1. Implicit Cursor:-
These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed Oracle performs following operation to manage an implicit cursor
==> Reserves an area in memory to store data required to execute SQL Statement.
==> Occupy this area with required data.
==> Processes data.
==> Frees memory area by closes a cursor, When processing is completed.
The syntax to use attributes of implicit cursor:-
SQL%AttributeName
The Value of the cursor attribute always refers to the SQL command that was executed most recently.
| Attribute | Description |
|---|---|
| SQL%ISOPEN | Always returns false, as Oracle automatically closes cursors after executing SQL statement |
| SQL%FOUND | If SELECT found any record, or insert, Update affected any record, returns TRUE; Else, returns false. |
| SQL%NOTFOUND | If select found no any record, or insert, update and delete affected no any record, returns TRUE; Else, returns FALSE |
| SQL%ROWCOUNT | Returns number of records processed by select, insert, update or delete operation |
To understand implicit cursor we design pl/sql program as shown below in notepad with .sql extension.
Implicit Cursor
Now, We run the file using CMD to get output.
First of all open the CMD and go to drive where you save the file using following command.
Now type the "SQLPLUS" and Write username and password to open sql in the folder where you saved.
Now write "SET SERVEROUTPUT ON" to display the output without this output of program is unseen.
To execute the program you need to write @filename.sql
If the Row is in the table the output will be as shown below.
If the Row is not in table the output will be as shown below.
2. Explicit Cursor:-
An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.
The Steps required to manage explicit cursor and manipulate data are given below:
1. Declare a Cursor
2. Open a Cursor
3. Fetching Data
4. Processing Data
5. Closing Cursor
These Various steps are described below:-
1. Declare a Cursor
A cursor is defined in the Declaration section of the PL/SQL block using Following syntax
Syntax:
Cursor CursorName is Select …;
2. Open a Cursor
A cursor can be opened using open statement as described below
Syntax:
Open CursorName;
3. Fetching Data
To process a record individually, data stored in that record are need to be fetched into memory variables.
Syntax:
Fetch CursorName Into variable1, variable2 …;
4. Processing Data
This Step involves actual processing of the table data. Data are already fetched into variables, and these variable can be processed as per requirements.
5. Closing Cursor
A cursor Should be closed After the processing of Data completes. Closing a cursor releases memory allocated for that cursor.
Syntax:
Close CursorName;
To understand explicit cursor we design pl/sql program as shown below in notepad with .sql extension.
Explicit Cursor
After this we follow same steps as we follow above to run program.
Executing Explicit Cursor
==> Explicit For Cursor:-
In Explicit cursor We can print only one Row data. But using for loop we can print the all data. So it called Explicit For Cursor.
To understand explicit for cursor we design pl/sql program as shown below in notepad with .sql extension.
Explicit for Cursor
After this we follow same steps as we follow above to run program.
Executing Explicit For Cursor
I hope this article will help you