Java calls the Oracle stored procedure and returns the result set

I. Stored Procedure calls without return values

Stored Procedure:

Create or replace procedure pro_1 (para1 in varchar2, para2 in varchar2)

Begin

Insert into DBO. EMP (ID, name) values (para1, para2 );

End pro_1;

Java code:

Package com. icesoft. Service;

IMPORT java. SQL .*;
IMPORT java. SQL. resultset;

Public class callproceduretest1 {

Public callproceduretest1 (){
Super ();
}

Public static void main (string [] ARGs ){

String driver = "oracle. JDBC. Driver. oracledriver ";
String url = "JDBC: oracle: thin: @ 127.0.0.1: 1521: orcl ";
String user = "admin ";
String Pwd = "password ";

Connection conn = NULL;
Callablestatement cs = NULL;
Resultset rs = NULL;

Try {
Class. forname (driver );
Conn = drivermanager. getconnection (URL, user, PWD );
Cs = conn. preparecall ("{call DBO. pro_1 (?,?) }");
CS. setstring (1, "10 ");
CS. setstring (2, "Peter ");
Cs.exe cute ();
} Catch (sqlexception e ){
E. printstacktrace ();
} Catch (exception e ){
E. printstacktrace ();
} Finally {
Try {
If (RS! = NULL ){
Rs. Close ();
}
If (CS! = NULL ){
CS. Close ();
}
If (Conn! = NULL ){
Conn. Close ();
}
} Catch (sqlexception e ){
}
}

}

}

Note: The table EMP (ID, name) is used in the Stored Procedure pro_1 and must be created in advance.

2. Stored Procedures with returned values (non-result sets)

Stored Procedure:

Create or replace procedure pro_2 (para1 in varchar2, para2 out varchar2)

Begin

Select into para2 from EMP where id = para1;

End pro_2;

Java code:

Package com. icesoft. Service;

IMPORT java. SQL .*;

Public class callproceduretest2 {

Public callproceduretest2 (){
Super ();
}

Public static void main (string [] ARGs ){

String driver = "oracle. JDBC. Driver. oracledriver ";
String url = "JDBC: oracle: thin: @ 127.0.0.1: 1521: orcl ";
String user = "admin ";
String Pwd = "password ";
Connection conn = NULL;
Callablestatement cs = NULL;
Resultset rs = NULL;

Try {
Class. forname (driver );
Conn = drivermanager. getconnection (URL, user, PWD );
Cs = conn. preparecall ("{call DBO. pro_2 (?,?) }");
CS. setstring (1, "10 ");
CS. registeroutparameter (2, types. varchar );
Cs.exe cute ();

String name = cs. getstring (2 );
System. Out. println ("name:" + name );
} Catch (sqlexception e ){
E. printstacktrace ();
} Catch (exception e ){
E. printstacktrace ();
} Finally {
Try {
If (RS! = NULL ){
Rs. Close ();
}
If (CS! = NULL ){
CS. Close ();
}
If (Conn! = NULL ){
Conn. Close ();
}
} Catch (sqlexception e ){
}
}

}

}

Note: CS. the value 2 in getstring (2) is not arbitrary, but corresponds to the out column in the stored procedure. If the out column is in the first position, it is Proc. getstring (1). If it is the third position, it is Proc. getstring (3), Of course, you can also have multiple return values at the same time, that is, add a few more out parameters.

Iii. Back to list

Since the Oracle stored procedure does not return values, all of its return values are replaced by the out parameter, and the list is no exception. However, because it is a set, general parameters cannot be used, you must use pagkage. therefore, it must be divided into two parts,

1.Create a package. As follows:

Create or replace package mypackage

Type my_cursor is ref cursor;

End mypackage;

2.Create a stored procedure as follows:

Create or replace procedure pro_3 (p_cursor out mypackage. my_cursor) is

Begin

Open p_cursor for select * From DBO. EMP;

End pro_3;

It can be seen that the cursor (which can be understood as a pointer) is returned as an out parameter.

Java code:

Package com. icesoft. Service;

IMPORT java. SQL .*;
IMPORT java. SQL. resultset;

Public class callproceduretest2 {

Public callproceduretest2 (){
Super ();
}

Public static void main (string [] ARGs ){

String driver = "oracle. JDBC. Driver. oracledriver ";
String url = "JDBC: oracle: thin: @ 127.0.0.1: 1521: orcl ";
String user = "admin ";
String Pwd = "password ";

Connection conn = NULL;
Callablestatement cs = NULL;
Resultset rs = NULL;
Try {
Class. forname (driver );
Conn = drivermanager. getconnection (URL, user, PWD );
Cs = conn. preparecall ("{call DBO. pro_3 (?) }");
CS. registeroutparameter (1, Oracle. JDBC. oracletypes. cursor );
Cs.exe cute ();
Rs = (resultset) CS. GetObject (1 );

While (Rs. Next ()){
System. Out. println ("\ t" + Rs. getstring (1) + "\ t"
+ Rs. getstring (2) + "\ t ");
}
} Catch (sqlexception e ){
E. printstacktrace ();
} Catch (exception e ){
E. printstacktrace ();
} Finally {
Try {
If (RS! = NULL ){
Rs. Close ();
If (CS! = NULL ){
CS. Close ();
}
If (Conn! = NULL ){
Conn. Close ();
}
}
} Catch (sqlexception e ){
}
}

}

}