Cursor | Oracle PL/SQL Cursors and example.

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

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:

Technorati Tags:
, , , ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

5 thoughts on “Cursor | Oracle PL/SQL Cursors and example.

  1. Pingback: Oracle CURSOR with parameter | SQL and PLSQL

  2. Pingback: Oracle Cursors | OPEN ,FETCH and CLOSE Cursor statements. | SQL and PLSQL

  3. Pingback: ORA-01000: maximum open cursors exceeded. | SQL and PLSQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page