Oracle Stored Procedure Cursor example

Here’s a stored procedure example in Oracle database, using Cursor to manipulate or navigate the records.

1. Table SQL Script

DBUSER table creation script.


CREATE TABLE DBUSER ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

2. Stored Procedure

A stored procedure, return the record as cursor type (SYS_REFCURSOR) base on the matched username.


CREATE OR REPLACE PROCEDURE getDBUSERCursor(
	   p_username IN DBUSER.USERNAME%TYPE,
	   c_dbuser OUT SYS_REFCURSOR)
IS
BEGIN

  OPEN c_dbuser FOR
  SELECT * FROM DBUSER WHERE USERNAME LIKE p_username || '%';
 
END;
/

3. Calls from PL/SQL

Get the returned ref cursor and navigate the records like this :


DECLARE 
  c_dbuser SYS_REFCURSOR;
  temp_dbuser DBUSER%ROWTYPE;
BEGIN
 
  --records are assign to cursor 'c_dbuser'
  getDBUSERCursor('mkyong',c_dbuser);
 
  LOOP
 
        --fetch cursor 'c_dbuser' into dbuser table type 'temp_dbuser'
	FETCH c_dbuser INTO temp_dbuser;
 
        --exit if no more records
        EXIT WHEN c_dbuser%NOTFOUND;

        --print the matched username
        dbms_output.put_line(temp_dbuser.username);
 
  END LOOP;
 
  CLOSE c_dbuser;
 
END;
/

Result
The records of username like ‘mkyong%’ are returned as cursor via getDBUSERCursor store procedure.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
hasto
Guest
hasto

hai mkyong, i have a question about opening and closing a cursor. what different if i running PL/SQL with close cursor in the end of query and i running PL/SQL without close cursor in the end of query?

Chandu
Guest
Chandu

Hi,
I have to create the procedure with 2 input paramaters
one is table name
second one is sys_ref_curosr (table data).
here
i have to insert the ref cursor data into input table name(input paramater) but for doing the insertion operation, we have to create the collection type to process the refcursor data
type type_name is table of table_name%rowtype —-> Here table name comes from input paramater (dynamically).
so how we can create the above type with dynamic table
or else
give me a alternate solution

Thanks
Chandu

vikash kumar
Guest
vikash kumar

this example is not working. It’s showing anonymous block completed even my table has the data.

Pizza inn coupons
Guest
Pizza inn coupons

At this time I am ready to do my breakfast, afterward having my
breakfast coming yet again to read other news.

Steven Andler
Guest
Steven Andler

Hello Mkyong

Thank you for this example. I am faily new to Oracle. I am a c# programmer ( sorry I know your a Java guy). I was wondering if you also have sample code showing how to access and return the Cursor info from the getDBUSERCursor written in C#.

Thank you.

Pavan
Guest
Pavan

Thanks Dude..Good Example…

Aasim
Guest
Aasim

Could you please let me know what is the diffrence between in, out parameter.