Sample Code for returning a result set using a cursor in an Oracle Stored Procedure

Oracle Stored ProcedureYesReturns a result set using a cursor.Its implementation method is what we will introduce in this article. Next we will introduce it. First, we create the following content in sqlplus:

1. Package

 
 

  
  
SQL> create or replace package types
  
  
2
  
  
3 type cursorType is ref cursor;
  
  
4 end;
  
  
5/
  
  
The package has been created.
 
 

2. Functions

 
 

  
  
SQL> create or replace function sp_ListEmp return types. cursortype
  
  
2
  
  
3 l_cursor types. cursorType;
  
  
4 begin
  
  
5 open l_cursor for select id, title from cf_news order by id; -- table name
  
  
6 return l_cursor;
  
  
7 end;
  
  
8/
  
  
The function has been created.
 
 

3. Stored Procedure

 
 

  
  
SQL> create or replace procedure getemps (p_cursor in out types. cursorType)
  
  
2
  
  
3 begin
  
  
4 open p_cursor for select id, title from cf_news order by id; -- table name
  
  
5 end;
  
  
6/
  
  
The process has been created.
 
 

4. Create an executable java console Program

 
 

  
  
Import java. SQL .*;
  
  
Import java. io .*;
  
  
Import oracle. jdbc. driver .*;
  
  
Class GetValues
  
  
Public static void main (String args [])
  
  
Throws SQLException, ClassNotFoundException
  
  
{
  
  
String driver_class = "oracle. jdbc. driver. OracleDriver ";
  
  
String connect_string = "jdbc: oracle: thin: @ 127.0.0.1: 1521: database ";
  
  
String query = "begin: 1: = sp_listEmp; end;"; // call the previously created function here!
  
  
Connection conn;
  
  
Class. forName (driver_class );
  
  
Conn = DriverManager. getConnection (connect_string, "scott", "tiger ");
  
  
CallableStatement cstmt = conn. prepareCall (query );
  
  
Cstmt. registerOutParameter (1, OracleTypes. CURSOR );
  
  
Cstmt.exe cute ();
  
  
ResultSet rset = (ResultSet) cstmt. getObject (1 );
  
  
While (rset. next ())
  
  
System. out. println (rset. getString (1 ));
  
  
Cstmt. close ();
  
  
}
 
 

Run the above Code in the Java compiler to implement this function.

The above is the whole process of returning the result set using the cursor in the Oracle stored procedure. This article will introduce it here. I hope this introduction will be helpful to you!