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.

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.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

  • http://www.slideshare.net/jeremyhoub/pizza-inn-coupons-great-discount 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

    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

    Thanks Dude..Good Example…

  • Aasim

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