ORACLE uses work areas to execute SQL statements, PL/SQL allows user to name private work areas and access the stored information. The PL/SQL construct to identify each and every work area used by SQL is called a Cursor.
There are two type of cursors.
- 1.Implicit cursors
- 2.Explicit cursors
1.Implicit SQL Cursors
The set of rows returned by a query is called the Active Set. Its size depends on the number of rows that meet the search criteria of the SQL query. The data that is stored in the cursor is called the Active Data Set.
ORACLE cursor is a mechanism used easily processes multiple rows of data. Cursors contain a pointer that keeps track of current row being accessed, which enables your program to process the rows at a time.
When a user executes the following SELECT statement
SELECT emp_no, emp_name,job, salary
WHERE dept = ‘physics’
The resultant dataset will be displayed as follows
1234 A.N Sharanu Asst. Professor 22,000.00
1345 N.Bharath Senior Lecturer 17,000.00
2.Explicit SQL Cursor
The following are the steps to using explicitly defined cursors within PL/SQL
- a)Declare the cursor
- b)Open the cursor
- c)Fetch data from the cursor
- d)Close the cursor
a)Declaring a sql cursor
Declaring a cursor enables you to define the cursor and assign a name to it. It has following syntax.
CURSOR cursor-name IS SELECT statement
Ex: CURSOR c_name IS
SELECT emp_name FROM emp WHERE dept = ‘physics’
b)Opening a sql cursor
Opening a cursor executes the query and identifies the active set that contains all the rows, which meet the query search criteria.
Open statement retrieves the records from the database and places it is in the cursor (private SQL area).
c)Fetching data from sql cursor:
The fetch statement retrieves the rows from the active set one row at a time. The fetch statement is used usually used in conjunction with iterative process(looping statements). In iterative process the cursor advanced to the next row in the active set each time the fetch command is executed. The fetch command is the only means to navigate through the active set.
FETCH cursor-name INTO record-list
Record-list is the list of variables that will receive the columns (fields ) from the active set.
FETCH c_name INTO name;
d) Closing a sql cursor:
Closing statement close/deactivates/disables the previously opened cursor and makes the active set undefined. Once it is closed, you cannot perform any operation on it. Once a cursor is closed, the user can reopen the cursor by using Open statement.
Ex: Close c_name;
Explicit sql cursor attributes:
Oracle provides certain attributes/cursor variables to control the execution of the cursor. Whenever any cursor (explicit or implicit) is opened and used. ORACLE creates a set of four system variables via which ORACLE keep track of the “current status” of the cursor. Programmers can access these variables they are
- %NOT FOUND: Evaluates to TRUE if the last fetch is failed i.e. no more rows are left.
- % FOUND: Evaluates to TRUE , when last fetch succeeded.
- %ISOPEN: Evaluates to TRUE if cursor is opened, otherwise evaluates to FALSE.
- %ROWCOUNT: Returns the numberof row fetched