Stored Procedures that call the Oracle return cursor type in JDBC

1. Create a table:

Create Table stud (
Sid int,
Sname varchar2 (50)
)
Insert a data record
Insert into stud values (1, 'Tom ')

2. Define a cursor through the package
 
Create or replace package pack_stud
Is
-- Declare a cursor type
Type cur is ref cursor;
End;
3. Create a stored procedure

Create or replace procedure pro_select_stud (
My_cur out pack_stud.cur
)
Is
Begin
Open my_cur for select * from stud;
End;

4. Call in JDBC

Import java. SQL. callablestatement;
Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. resultset;

Import oracle. JDBC. Driver. oracletypes;

 

Public class testprocur {

Public void test (){

Connection con = NULL;
Resultset rs = NULL;
Callablestatement CST = NULL;
Try {
Class. forname ("oracle. JDBC. Driver. oracledriver ");
// The Tan below is the database name, the default is orcl, the love is the access password, the default is Tiger,
// 1521 indicates the port number. The default value is 1521.
Con = drivermanager. getconnection ("JDBC: oracle: thin: @ 192.168.1.103: 1521: Tan", "Scott", "love ");
String SQL = "{call pro_select_stud (?)} ";
CST = con. preparecall (SQL );
CST. registeroutparameter (1, oracletypes. cursor );
Cst.exe cute ();
Rs = (resultset) Cst. GetObject (1 );
While (Rs. Next ()){
System. Out. println (Rs. getstring (2 ));
}
} Catch (exception e ){
E. printstacktrace ();
} Finally {

 

Try {

If (RS! = NULL ){
Rs. Close ();
}
If (CST! = NULL ){
CST. Close ();
}
If (con! = NULL ){
Con. Close ();
}
} Catch (exception e ){
E. printstacktrace ();
}

}
}

Public static void main (string [] ARGs ){
Testprocur TPC = new testprocur ();
TPC. Test ();
}

}