A cursor is a name for private SQL area.It is in private SQL area where the parsed statement and other information for processing the statement are kept.
Execution of a cursor puts the results of the query into a set of rows called the result set, which can be fetched sequentially or nonsequentially.

A simple example of Cursor is given below.This uses a generic Cursor example,in which we OPEN the cursor , Fetch the records,do some operation then CLOSE the cursor.

Step1: Create a table PERSON and insert few records into it.

create table PERSON(PERSON_ID NUMBER(19) not null,AGE       NUMBER(10),FIRSTNAME VARCHAR2(255),LASTNAME  VARCHAR2(255));

SQL> insert into Person values(1,10,'Geek','Greek');

1 row inserted

SQL> insert into Person values(2,12,'Seek','Bells');

1 row inserted

SQL> insert into Person values(3,13,'Creek','Dells');

1 row inserted

SQL>SQL> insert into Person values(4,13,'Sreek','Sells');

1 row inserted

SQL>

Step2: Create a Procedure for displaying the table data.

CREATE OR REPLACE PROCEDURE Generic_Cursor IS

CURSOR FETCH_INSERT ISSELECT * from PERSON;

new_Rec FETCH_INSERT%ROWTYPE;

BEGIN

 OPEN FETCH_INSERT;   LOOP     FETCH FETCH_INSERT INTO new_Rec;     EXIT when FETCH_INSERT%NOTFOUND;     DBMS_OUTPUT.put_line(new_Rec.FIRSTNAME);   END LOOP; CLOSE FETCH_INSERT;

END Generic_Cursor;

Step 3:Now test it using a small test program

SQL> exec Param_Cursor;

begin Param_Cursor; end;

ORA-01001: invalid cursorORA-06512: at "TEST.PARAM_CURSOR", line 10ORA-06512: at line 1

SQL>

Also Read:

• Oracle ‘ALTER TABLE’ to ADD columns
• CREATE INDEX as part of CREATE TABLE statement.
• Oracle Tables: Create Table as Select
• Oracle Tables: Create table with foreign key constraint
• ‘CREATE TABLE’ : create table with composite primary key

Technorati Tags:
Cursor, CLOSE cursor, open cursor, Fetch cursor, Oracle cursor