如何在Java程序中调用存储过程

如何在Java程序中调用存储过程(一)？

(1)使用scott/tiger用户在Oracle中创建2个表并插入几条数据。

Create table carl_test(A varchar2(200));

create table carl_test1(B varchar2(200));

--insert some data into carl_test1 table

insert into carl_test1 values('carl1');

insert into carl_test1 values('carl2');

insert into carl_test1 values('carl3');

commit;

(2)使用scott/tiger用户在Oracle中创建一个测试存储过程，该存储

过程有三个参数，第一个参数为输入参数，最后两个参数为输出参数。为简单起见，没有在该存储过程中加入异常捕获机制。

CREATE OR REPLACE PROCEDURE carl_sp_test

( v_monthly IN varchar2,

last_inserted_rows_num OUT number,

all_effected_rows_num OUT number)

IS

BEGIN

/*删除carl_test表中所有记录*/

delete carl_test;

/*将删除行数赋值给总影响行数*/

all_effected_rows_num := SQL%Rowcount;

commit;

/*将用户输入参数插入carl_test表中*/

insert into carl_test(a) values(v_monthly);

all_effected_rows_num:= all_effected_rows_num + SQL%Rowcount;

/*将表carl_test1中的所有记录插入到carl_test1中*/

insert into carl_test

select* from carl_test1;

/*获得影响记录数*/

last_inserted_rows_num:=SQL%Rowcount;

all_effected_rows_num:= all_effected_rows_num + SQL%Rowcount;

commit;

END carl_sp_test;

(3)使用scott/tiger用户在SQL/Plus中测试上述存储过程

SQL> variable all_effected_rows_num number;

SQL> variable last_inserted_rows_num number;

SQL> exec carl_sp_test('first var',:last_inserted_rows_num,:all_effected_rows_num);

PL/SQL procedure successfully completed

last_inserted_rows_num

---------

3

all_effected_rows_num

---------

4

SQL> print last_inserted_rows_num;

last_inserted_rows_num

---------

3

SQL> print all_effected_rows_num;

all_effected_rows_num

---------

4

SQL>

上述结果表示测试成功

(4)使用下面的Java类TestStoreProcedure.java测试存储过程。

package test;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

/**

* This class is used to test Oracle store procedure

* @author CarlWu

*

*/

public class TestStoreProcedure{

/**

* 测试主方法

* @param args

*/

public static void main(String[] args) {

Connection con=null;

try {

//获得数据库连接

ng.Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:LEGDB");

//测试存储过程

TestStoreProcedure sp=new TestStoreProcedure();

sp.execute(con,"carl_sp_test","carl");

} catch (ClassNotFoundException cnfe) {

cnfe.printStackTrace();

} catch (SQLException sqlE) {

sqlE.printStackTrace();

}catch(Exception e){

e.printStackTrace();

}finally {

if (con != null) {

try {

con.close();

} catch (Exception e) {

}

}

}

}