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.
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?
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
please send that
this example is not working. It’s showing anonymous block completed even my table has the data.
At this time I am ready to do my breakfast, afterward having my
breakfast coming yet again to read other news.
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.
Thanks Dude..Good Example…
Could you please let me know what is the diffrence between in, out parameter.
in is what you give as input and out is what you get as ouput.
For ex: 1+2=3 1 and 2 are input whereas 3 is an output.