A cursor is a pointer to an area in the memory. It is specific temporary work area for a specific SQL statement. It stores information about the accessing oracle object statements.
A cursor can hold more than one row but ,it processes only one row at a time .It is used to retrieve multiple record from table.
There are two types of cursors:
- Implicit cursors
- Cursor which are automatically (implicitly) created whenever an SQL statement is executed by Oracle.
- Explicit cursors
- Cursor which are user defined cursors for have more control over the context area.
The syntax to create an explicit cursor is:
CURSOR <cursor_name> IS select_statement;
An explicit cursor has four steps:
- Declaring the cursor in declaration section for initializing in the memory.
- Opening the cursor by using OPEN command for allocating memory.
- Fetching the cursor by using FETCH command to retrieve data.
- Closing the cursor by using CLOSE command to release allocated memory.
Syntax :
DECLARE
records;
create cursor is <statement>;
BEGIN
OPEN cursor;
FETCH cursor;
process records;
CLOSE cursor;
END;
/
EXAMPLE :
1> DECLARE
2> e_rec employee%rowtype;
3> CURSOR e_cur IS
4> SELECT *
5> FROM
6> WHERE salary > 1000;
7> BEGIN
8> OPEN e_cur;
9> FETCH e_cur INTO e_rec;
10> dbms_output.put_line (e_rec.f_name || ‘ ‘ || e_rec.l_name);
11> CLOSE e_cur;
12> END;
13> /
Explanation of Example :
In the given example,
- we are creating a record ‘e_rec’ having same structure as of table ‘employee’ – line no 2
- we are declaring a cursor ‘e_cur’ from a select query – line no 3 – 6.
- we are opening the cursor in the execution section – line no 8
- we are fetching the cursor to the record – line no 9
- we are displaying the first name as f_name and last name as l_name of the employee in the record e_rec – line no 10
- we are closing the cursor – line no 11
