Oracle Cursors | OPEN ,FETCH and CLOSE Cursor statements.

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 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 non sequentially.

Read:Cursor | Oracle PL/SQL Cursors and example.
Read:ORA-01000: maximum open cursors exceeded.

When you write generic cursor you can either use FETCH..,OPEN… and CLOSE cursor statements as give below.


CREATE OR REPLACE PROCEDURE Generic_Cursor IS
<pre>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;

Or you can use the FOR LOOP for iterating through the cursor. When you use FOR LOOP for iteration no need of Explicit use of FETCH..,OPEN… and CLOSE cursor statements.
The cursor will open automatically when entering FOR LOOP and will close the cursor once the loop ends.

Example given below

<pre>CURSOR PERSON_CUR(pAge NUMBER) isSelect * from Person p where p.age = pAge;

BEGIN    for i in PERSON_CUR(13) loop       dbms_output.put_line(i.firstname);    end loop;

END Param_Cursor;

Also read:

Technorati Tags:
, , , ,

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

1 thought on “Oracle Cursors | OPEN ,FETCH and CLOSE Cursor statements.

Leave a Reply

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

Paged comment generated by AJAX Comment Page