========= shows that the table used for this case is an EMP table with an Oracle database ==========
--1, creating a package
--Package name is Testpackage,
--a type test_cursor is defined in the package, which is a cursor type
Create or replace package testpackage as
Type test_cursor is REF CURSOR;
End Testpackage;
--2, build stored procedures.
Create or replace procedure Sp_pro10
(Spno in Number,p_cursor out Testpackage.test_cursor) is
Begin
Open P_cursor for SELECT * from EMP where deptno=spno;
End
--3, calling in Java code
Package test;
Import java.sql.*;
public class testoracle{
Defining Connection Objects
public static Connection conn = null;
Defining CallableStatement Objects
public static CallableStatement cs = null;
Defines the returned result set object
public static ResultSet rs = null;
public static void Main (string[] args) {
try{
1. Load Oracle Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
2. Get the database connection
conn = Drivermanager.getconnection ("Jdbc:oracle:thin: @localhost: 1521:orcl", "Scott", "Tiger");
3. Instantiate the CallableStatement object
CS = Conn.preparecall ("{Call Sp_pro10 (?,?)}");
4, give. assigning values
Cs.setint (1,10);
5, to a second. assigning values
Cs.registeroutparameter (2,oracle.jdbc.oracletypes.cursor);
7. Implementation
Cs.execute ();
8, get the returned value, note that the return value is the second, here must write 2
ResultSet rs = (ResultSet) cs.getobject (2);
9, the output value of the printout
while (Rs.next ()) {
System.out.println (Rs.getint (1) + "" +rs.getstring (2));
}
}catch (Exception e) {
E.printstacktrace ();
}finally{
try{
if (rs!=null) {
Rs.close ();
rs = null;
}
}catch (Exception E0) {
E0.printstacktrace ();
}finally{
try{
if (cs!=null) {
Cs.close ();
CS = null;
}
}catch (Exception E1) {
E1.printstacktrace ();
}finally{
try{
if (conn!=null) {
Conn.close ();
conn = null;
}
}catch (Exception E2) {
E2.printstacktrace ();
}
}
}
}
}
}