SQL Server Cursor
To execute SQL statements, a work area is used by the Oracle engine for its internal processing and storing the information.
PL/SQL allows the programmer to control the context area through the cursor. A cursor holds the rows returned by the SQL statement.
Use of Cursor
- The major function of a cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time.
- Cursors are used when the user needs to update records in a singleton fashion or in a row by row manner, in a database table.
The Data that is stored in the server Cursor is called the Active Data Set. - Oracle DBMS has another predefined area in the main memory Set, within which the cursors are opened. Hence the size of the cursor is limited by the size of this pre-defined area.
In this tutorial you will learn-
- Implicit Cursor
- Explicit Cursor
- Cursor Attributes
- FOR Loop Cursor statement
The cursor is of two types:
Cursors are classified depending on the circumstances in which they are opened.
- Implicit Cursor
- Explicit Cursor
Implicit Cursor
Whenever any DML operations occur in the database, If the Oracle engine opened a cursor for its internal processing it is known as an Implicit Cursor. It is created “automatically” for the user by Oracle when a query is executed and is simpler to code.
Explicit Cursor
A Cursor can also be opened for processing data through a PL/SQL block, on demand. Such a user-defined cursor is known as an Explicit Cursor.
Below are steps that involved in working with explicit cursors:
- Declaring the cursor Declaring the cursor simply means to create one named context area for the ‘SELECT’ statement that is defined in the declaration part. The name of this context area is same as the cursor name.
- Opening Cursor Opening the cursor will instruct the PL/SQL to allocate the memory for this cursor. It will make the cursor ready to fetch the records.
- Fetching Data from the Cursor In this process, the ‘SELECT’ statement is executed and the rows fetched is stored in the allocated memory. These are now called as active sets. Fetching data from the cursor is a record-level activity that means we can access the data in a record-by-record way.Each fetch statement will fetch one active set and holds the information of that particular record. This statement is same as ‘SELECT’ statement that fetches the record and assigns to the variable in the ‘INTO’ clause, but it will not throw any exceptions.
- Closing the Cursor Once all the record is fetched now, we need to close the cursor so that the memory allocated to this context area will be released.
Syntax for creating a cursor:
- 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 execution part, the declared cursor is opened, fetched and closed.
How to use Explicit Cursor?
There are four steps in using an Explicit Cursor.
- DECLARE the cursor in the Declaration section.
- OPEN the cursor in the Execution Section.
- FETCH the data from the cursor into PL/SQL variables or records in the Execution Section.
- CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
