原文:
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();
}
}
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
