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,

  1. we are creating a record ‘e_rec’ having same structure as of table ‘employee’  – line no 2
  2. we are declaring a cursor ‘e_cur’ from a select query – line no 3 – 6.
  3. we are opening the cursor in the execution section – line no 8
  4. we are fetching the cursor to the record – line no 9
  5. 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
  6. we are closing the cursor – line no 11

 

 

 

 

 

CONTACT US

We're not around right now. But you can send us an email and we'll get back to you, asap.

Sending

©2023 MYBSCIT.com. An initiative by some failures to make student scholars.

or

Log in with your credentials

Forgot your details?