原文:
java代码调用oracle存储过程
一、简介
与调用mysql存储过程类型过程都是如下
1、创建连接 Connection conn = DriverManager.getConnection(url, user, password);
2、创建CallableStatement CallableStatement statement = conn.prepareCall(sql);
3、设置参数
statement.setInt(1, id);
statement.registerOutParameter(2, Types.VARCHAR);
statement.registerOutParameter(3, Types.INTEGER);
statement.registerOutParameter(4, Types.VARCHAR);
4、执行
statement.execute(); 或 statement.executeUpdate();
5、获取返回
int age = statement.getInt(3);
只是oracle存储过程有的结果集是以游标的方式返回，此时我们需要调用ResultSet rs = (ResultSet) statement.getObject(1);方法回去结果集
二、代码
以下存储过程表结构如下：
DROP
TABLE
person ;
CREATE
TABLE
person (
id
NUMBER
(
11
)
NOT
NULL
,
username
VARCHAR2
(
255
)
NULL
,
age
NUMBER
(
11
)
NULL
,
password
VARCHAR2
(
255
)
NULL
,
PRIMARY
KEY
(id)
)
1、查询所有记录
存储过程代码如下：
create
or
replace
procedure
pro_person_findall(
p_cursor out pkg_const.r_cursor
)
is
begin
open
p_cursor
for
select
*
from
person;
exception
when
others
then
DBMS_OUTPUT.PUT_LINE(
'
获取信息发生错误
'
);
end
pro_person_findall;
调用代码如下
public
static
void
findAll() {
String driver
= "oracle.jdbc.driver.OracleDriver"
;
String url
= "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"
;
String user
= "wuxx"
;
String password
= "wuxx"
;
try
{
Class.forName(driver);
Connection conn
=
DriverManager.getConnection(url, user, password);
String sql
= "{call pro_person_findall2(?)}"
;
CallableStatement statement
=
conn.prepareCall(sql);
statement.registerOutParameter(
1
, oracle.jdbc.OracleTypes.CURSOR);
statement.execute();
ResultSet rs
= (ResultSet) statement.getObject(1
);
ResultSetMetaData rmd
=
rs.getMetaData();
System.out.print(rmd.getColumnName(
1) + " "
);
System.out.print(rmd.getColumnName(
2) + " "
);
System.out.print(rmd.getColumnName(
3) + " "
);
System.out.print(rmd.getColumnName(
4) + "\n"
);
while
(rs.next()) {
System.out.print(rs.getInt(
"id") + " "
);
System.out.print(rs.getString(
"username") + " "
);
System.out.print(rs.getInt(
"age") + " "
);
System.out.print(rs.getString(
"password") + " \n"
);
}
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
2、查询一条记录
存储过程如下
CREATE
OR
REPLACE
PROCEDURE
PRO_PERSON_FINDBYID(
v_id
IN
NUMBER
,
v_username OUT
VARCHAR2
,
v_age OUT
NUMBER
,
v_password OUT
VARCHAR2
,
p_count out
number
)
AS
BEGIN
SELECT
username, age, password
INTO
v_username, v_age, v_password
from
person
where
id
=
v_id;
p_count :
=
1
;
exception
when
others
then
p_count :
=
0
;
END
;
调用代码如下：
public
static
void
find(Integer id) {
String driver
= "oracle.jdbc.driver.OracleDriver"
;
String url
= "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"
;
String user
= "wuxx"
;
String password
= "wuxx"
;
try
{
Class.forName(driver);
Connection conn
=
DriverManager.getConnection(url, user, password);
String sql
= "{call PRO_PERSON_FINDBYID(?,?,?,?,?)}"
;
CallableStatement statement
=
conn.prepareCall(sql);
BigDecimal rid
=
new
BigDecimal(id);
statement.setInt(
1
, id);
statement.registerOutParameter(
2
, oracle.jdbc.OracleTypes.VARCHAR);
statement.registerOutParameter(
3
, oracle.jdbc.OracleTypes.NUMBER);
;
//
statement.registerOutParameter(4
, oracle.jdbc.OracleTypes.VARCHAR);
statement.registerOutParameter(
5
, oracle.jdbc.OracleTypes.NUMBER);
statement.execute();
int
flag = statement.getInt(5
);
if
(flag != 0
)
System.out.println(statement.getString(
2) + " "
+ statement.getInt(3) + " " + statement.getString(4
));
else
System.out.println(
"data not found!"
);
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
3、增加记录
存储过程代码如下：
create
or
replace
procedure
pro_person_insert(
p_id
number
,
p_username
varchar2
,
p_age
number
,
p_password
varchar2
,
p_count out
number
)
is
begin
insert
into
person (id, username, age, password)
values
(p_id, p_username, p_age, p_password);
p_count :
=
SQL
%
ROWCOUNT
;
--
SQL%ROWCOUNT为 隐士游标的属性
commit
;
exception
when
others
then
p_count :
=
0
;
end
pro_person_insert;
调用代码如下：
public
static
void
add(Integer id, String username,
int
age,
String u_password) {
String driver
= "oracle.jdbc.driver.OracleDriver"
;
String url
= "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"
;
String user
= "wuxx"
;
String password
= "wuxx"
;
try
{
Class.forName(driver);
Connection conn
=
DriverManager.getConnection(url, user, password);
String sql
= "{call pro_person_insert(?,?,?,?,?)}"
;
CallableStatement statement
=
conn.prepareCall(sql);
statement.setInt(
1
, id);
statement.setString(
2
, username);
statement.setInt(
3
, id);
statement.setString(
4
, u_password);
statement.registerOutParameter(
5, oracle.jdbc.OracleTypes.NUMBER);
//
增加记录是否成功的标记,1 成功，0失败
statement.execute();
System.out.println(statement.getInt(
5
));
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
4、更新记录
存储过程代码如下：
create
or
replace
procedure
pro_person_update(
p_id
number
,
p_age
number
,
p_password
varchar2
,
p_count out
number
)
is
begin
update
person
set
age
=
p_age, password
=
p_password
where
id
=
p_id;
p_count :
=
SQL
%
ROWCOUNT
;
commit
;
exception
when
no_data_found
then
p_count :
=
0
;
when
others
then
p_count :
=
-
1
;
end
pro_person_update;
调用代码如下：
public
static
void
update(Integer id,
int
age, String u_password) {
String driver
= "oracle.jdbc.driver.OracleDriver"
;
String url
= "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"
;
String user
= "wuxx"
;
String password
= "wuxx"
;
try
{
Class.forName(driver);
Connection conn
=
DriverManager.getConnection(url, user, password);
String sql
= "{call pro_person_update(?,?,?,?)}"
;
CallableStatement statement
=
conn.prepareCall(sql);
statement.setInt(
1
, id);
statement.setInt(
2
, age);
statement.setString(
3
, u_password);
statement.registerOutParameter(
4, oracle.jdbc.OracleTypes.NUMBER);
//
增加记录是否成功的标记
statement.execute();
System.out.println(statement.getInt(
4
));
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
5、删除记录
存储过程代码如下：
create
or
replace
procedure
pro_person_delete(
p_id
number
,
p_count out
number
)
is
begin
delete
from
person
where
id
=
p_id;
p_count :
=
SQL
%
ROWCOUNT
;
commit
;
exception
when
no_data_found
then
p_count :
=
0
;
when
others
then
p_count :
=
-
1
;
end
pro_person_delete;
调用代码如下：
public
static
void
delete(Integer id) {
String driver
= "oracle.jdbc.driver.OracleDriver"
;
String url
= "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"
;
String user
= "wuxx"
;
String password
= "wuxx"
;
try
{
Class.forName(driver);
Connection conn
=
DriverManager.getConnection(url, user, password);
String sql
= "{call pro_person_delete(?,?)}"
;
CallableStatement statement
=
conn.prepareCall(sql);
statement.setInt(
1
, id);
statement.registerOutParameter(
2, oracle.jdbc.OracleTypes.NUMBER);
//
增加记录是否成功的标记
statement.execute();
System.out.println(statement.getInt(
2
));
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
