java调用oracle存储过程_做一点，记一点 ~ Java调用Oracle存储过程

一、需求

传入一个参数，返回一条或多条记录(列表)。

二、实现步骤

1. 编写Oracle存储过程。

-- 声明包和包体的语句应该分两次执行，即使声明在前定义在后，如果一起执行依然会编译出错

--调用能够返回多条记录的存储过程需要定义在包内

--声明包及包内的游标、存储过程

CREATE OR REPLACE PACKAGE pack_myPackage ISTYPE myCursorIS REF CURSOR;PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, myCursor OUT myCursor);ENDpack_myPackage;--定义包体

CREATE OR REPLACE PACKAGE BODY pack_myPackage IS

PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, outcursor OUT myCursor) IS

BEGIN

OPEN outcursor FORSELECT * FROM tableA WHERE P_NO =pNo;ENDproc_querySomeInfo;END pack_myPackage;

2. 编写Java代码

packagevip.yaocn.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;/*** 存储过程测试

*

*@authoryacon*/

public classProcedureTest {public static void main(String[] args) throwsException {

String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

String driverName= "oracle.jdbc.driver.OracleDriver";

String username= "scott";

String password= "tiger";

Connection conn= null;

CallableStatement cstmt= null;

ResultSet rs= null;try{

Class.forName(driverName);

conn=DriverManager.getConnection(url, username, password);

String callSql= "{call pack_myPackage.proc_getSomeInfo(?, ?) }";

cstmt=conn.prepareCall(callSql);

cstmt.setString(1, "1001");

cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

cstmt.execute();

rs= (ResultSet) cstmt.getObject(2);if (rs != null) {while(rs.next()) {

System.out.println(rs.getString(1));

}

}

}catch(Exception e) {

e.printStackTrace();

}finally{try{if (rs != null) {

rs.close();

}

}catch(Exception e) {

e.printStackTrace();

}try{if (cstmt != null) {

cstmt.close();

}

}catch(Exception e) {

e.printStackTrace();

}try{if (conn != null) {

conn.close();

}

}catch(Exception e) {

e.printStackTrace();

}

}

}

}

三、 其他需求

1. Java调用有返回值的Oracle存储过程(返回一个简单类型的值)

(1)定义存储过程：

CREATE OR REPLACE PROCEDURE proc_A(param1 IN VARCHAR2, param2 OUT VARCHAR2)AS

BEGIN

SELECT name INTO param2 FROM T_A WHERE id =param1;END;

(2)编写Java代码：

packagevip.yaocn.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;import java.sql.ResultSet;

/*** 存储过程测试

*

*@authoryacon*/

public classProcedureTest {public static void main(String[] args) throwsException {

String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

String driverName= "oracle.jdbc.driver.OracleDriver";

String username= "scott";

String password= "tiger";

Connection conn= null;

CallableStatement cstmt= null;

ResultSet rs= null;try{

Class.forName(driverName);

conn=DriverManager.getConnection(url, username, password);

String callSql= "{call proc_A(?, ?) }";

cstmt=conn.prepareCall(callSql);

cstmt.setString(1, "1001");

cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

cstmt.execute();

System.out.println("Output is:" + cstmt.getString(2));}catch(Exception e) {

e.printStackTrace();

}finally{try{if (rs != null) {

rs.close();

}

}catch(Exception e) {

e.printStackTrace();

}try{if (cstmt != null) {

cstmt.close();

}

}catch(Exception e) {

e.printStackTrace();

}try{if (conn != null) {

conn.close();

}

}catch(Exception e) {

e.printStackTrace();

}

}

}

}

2. Java调用无返回值的Oracle存储过程(无返回值)

(1)定义存储过程：

CREATE OR REPLACE PROCEDURE proc_B(param1 IN VARCHAR2, param2 IN VARCHAR2)AS

BEGIN

INSERT INTO T_B VALUES(param1, param2);END;

(2)编写Java代码：

packagevip.yaocn.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;import java.sql.ResultSet;

/*** 存储过程测试

*

*@authoryacon*/

public classProcedureTest {public static void main(String[] args) throwsException {

String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

String driverName= "oracle.jdbc.driver.OracleDriver";

String username= "scott";

String password= "tiger";

Connection conn= null;

CallableStatement cstmt= null;

ResultSet rs= null;try{

Class.forName(driverName);

conn=DriverManager.getConnection(url, username, password);

String callSql= "{call proc_B(?, ?) }";

cstmt=conn.prepareCall(callSql);

cstmt.setString(1, "1001");

cstmt.setString(2, "TEST");

cstmt.execute();

}catch(Exception e) {

e.printStackTrace();

}finally{try{if (rs != null) {

rs.close();

}

}catch(Exception e) {

e.printStackTrace();

}try{if (cstmt != null) {

cstmt.close();

}

}catch(Exception e) {

e.printStackTrace();

}try{if (conn != null) {

conn.close();

}

}catch(Exception e) {

e.printStackTrace();

}

}

}

}

四、相关知识

1. 在plsql developer中测试存储过程时可以在存储过程名上右击选择 “测试” ，如果需要提供参数可以在下方填入参数，如果没有参数则点击执行按钮即可。