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;
/