• Sharebar

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.
You can create Cursors which accepts parameters at runtime.

Read:Cursor | Oracle PL/SQL Cursors and example.
Read:ORA-01000: maximum open cursors exceeded.
Read:Oracle Cursors | OPEN ,FETCH and CLOSE Cursor statements.
Read:ORA-01001: invalid cursor

Example:

Step 1:

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>

Step 2:

Now write a small procedure to illustrate the cursor which accepts a parameter at run time.

create or replace procedure Param_Cursor as

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;

Step3:

Now run the procedure Param_Cursor.

SQL> set serveroutput on;SQL> exec Param_Cursor;CreekSreek

PL/SQL procedure successfully completed

SQL>

Technorati Tags:
, , , ,

 
Be Sociable, Share!

Tags: , , , ,

  • http://www.test.de Million einsacken

    Just want to say your article is impressive. The clarity in your post is simply striking and i can assume you are an expert on this subject. Well with your permission allow me to grab your rss feed to keep up to date with future post. Thanks a million and please keep up the good work.

SEO Powered by Platinum SEO from Techblissonline