Example of a result set returned with a cursor (Oracle stored procedure)

Create the following in the Sqlplus:
1, Package

Sql> Create or Replace package types
2 AS
3 type CursorType is REF CURSOR;
4 End;
5/

The package has been created.

2. function
sql> Create or Replace function sp_listemp return Types.cursortype
2 AS
3 L_cursor Types.cursortype;
4 begin
5 Open L_cursor for select ID, name of title from Cf_news Order by id;--table
6 return l_cursor;
7 End;
8/

function has been created.

3. Process

sql> Create or Replace procedure Getemps (P_cursor in Out Types.cursortype)
2 AS
3 begin
4 Open P_cursor for select ID, name of title from Cf_news Order by id;--table
5 end;
6/

Procedure has been created.

4, to establish 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; "; The previously established function is called 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.execute ();
ResultSet RSet = (ResultSet) cstmt.getobject (1);

while (Rset.next ())
System.out.println (rset.getstring (1));
Cstmt.close ();
}
}