使用CallableStatement处理Oracle数据库的存储过程:
1.预处理SQL语句;
2.使用registerOutParameter方法注册变量数据类型;
3.为每一个“?”赋值;
4.执行操作。
实例:
TestProc.java:
01.
import
java.sql.*;
02.
03.
public
class
TestProc{
04.
05.
public
static
void
main(String[]args){
06.
Connectionconn=
null
;
07.
CallableStatementcstmt=
null
;
08.
try
{
09.
Class.forName(
"oracle.jdbc.driver.OracleDriver"
);
10.
conn=DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:mgc"
,
"system"
,
"admin"
);
11.
Stringsql=
"{call my_pro_name(?,?,?,?)}"
;
12.
cstmt=conn.prepareCall(sql);
13.
cstmt.registerOutParameter(
3
,Types.INTEGER);
14.
cstmt.registerOutParameter(
4
,Types.INTEGER);
15.
cstmt.setInt(
1
,
6
);
16.
cstmt.setInt(
2
,
4
);
17.
cstmt.setInt(
4
,
0
);
18.
cstmt.execute();
19.
System.out.println(cstmt.getInt(
3
));
20.
System.out.println(cstmt.getInt(
4
));
21.
}
catch
(ClassNotFoundExceptione){
22.
e.printStackTrace();
23.
}
catch
(SQLExceptione){
24.
e.printStackTrace();
25.
}
finally
{
26.
try
{
27.
if
(cstmt!=
null
){
28.
cstmt.close();
29.
cstmt=
null
;
30.
}
31.
if
(conn!=
null
){
32.
conn.close();
33.
conn=
null
;
34.
}
35.
}
catch
(SQLExceptione){
36.
e.printStackTrace();
37.
}
38.
}
39.
}
40.
41.
}
proc.sql:
01.
createorreplaceprocedure
my_pro_name
02.
(v_ainnumber,v_bnumber,v_retoutnumber,v_tmpinoutnumber)
03.
is
04.
begin
05.
if
(v_a>v_b)then
06.
v_ret:=v_a;
07.
else
08.
v_ret:=v_b;
09.
end
if
;
10.
v_tmp:=v_tmp+
1
;
11.
end;
12.
/