本文主要是总结 如何实现 JDBC调用Oracle的存储过程,从以下情况分别介绍:
[1] 只有输入IN参数,没有输出OUT参数;
[2] 既有输入IN参数,也有输出OUT参数,输出是简单值(非列表);
[3] 既有输入IN参数,也有输出OUT参数,输出是列表;
[4] 输入输出参数是同一个(IN OUT)。
准备工作
创建一个表tb_jdbc_procedure,并插入数据,具体SQL语句如下:
--删除表
drop table tb_jdbc_procedure;
--新建表
create table tb_jdbc_procedure(
user_id varchar2(20),
user_name varchar2(10),
salary number(8,2),
other_info varchar2(100)
);
--插入数据
insert into tb_jdbc_procedure
(USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
('cobcmw', 'cobcmw', 5000, 'http://www.4spaces.org');
insert into tb_jdbc_procedure
(USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
('zhangsan', '张三', 10000, null);
insert into tb_jdbc_procedure
(USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
('aoi_sola', '苍井空', 99999.99, 'twitter account');
insert into tb_jdbc_procedure
(USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
('李四', '李四', 2500, null);
--查询
select * from tb_jdbc_procedure;
Oracle Jdbc连接参数:
String className = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "cobcmw";
String password = "1234567890";
下面针对四种情况进行测试:
[1] 只有输入IN参数,没有输出OUT参数
存储过程pro_jdbc_in创建的相关SQL:
-- 新建存储过程 pro_jdbc_in 只有输入IN参数,没有输出OUT参数
create or replace procedure pro_jdbc_in(p_user_id in varchar2,
p_user_name in varchar2,
p_salary in number,
p_other_info in varchar2) is
BEGIN
insert into tb_jdbc_procedure
(USER_ID, USER_NAME, SALARY, OTHER_INFO)
values
(p_user_id, p_user_name, p_salary, p_other_info);
END pro_jdbc_in;
调用的Java代码如下:
public static void main(String[] args) {
String className = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "cobcmw";
String password = "1234567890";
/**
* 执行存储过程:[1] 只有输入IN参数,没有输出OUT参数;
*/
try {
Class.forName(className).newInstance();
Connection conn = DriverManager.getConnection(url,user,password);
CallableStatement cs = conn.prepareCall("{ call pro_jdbc_in(?,?,?,?) }");
cs.setString(1,"xiaowang");
cs.setString(2,"小王");
cs.setInt(3,5000);
cs.setString(4,"http://yoloshine.com");
cs.execute();
conn.close();
cs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
[2] 既有输入IN参数,也有输出OUT参数,输出是简单值(非列表)
存储过程pro_jdbc_in_out_value的创建SQL语句如下:
--新建存储过程 pro_jdbc_in_out_value 既有输入IN参数,也有输出参数,输出是简单值(非列表)
CREATE OR REPLACE PROCEDURE pro_jdbc_in_out_value(P_USERID IN VARCHAR2,
P_SALARY IN NUMBER,
P_COUNT OUT NUMBER) IS
V_SALARY NUMBER := P_SALARY;
BEGIN
IF V_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
IF P_USERID IS NULL THEN
SELECT COUNT(*)
INTO P_COUNT
FROM tb_jdbc_procedure T
WHERE T.SALARY >= V_SALARY;
ELSE
SELECT COUNT(*)
INTO P_COUNT
FROM tb_jdbc_procedure T
WHERE T.SALARY >= V_SALARY
AND T.USER_ID LIKE '%' || P_USERID || '%';
END IF;
DBMS_OUTPUT.PUT_LINE('v_count=:' || P_COUNT);
END pro_jdbc_in_out_value;
Java调用代码如下:
public static void main(String[] args) {
String className = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "cobcmw";
String password = "1234567890";
/**
* [2] 既有输入IN参数,也有输出OUT参数,输出是简单值(非列表);
*/
Connection conn = null;
CallableStatement cs = null;
try {
Class.forName(className).newInstance();
conn = DriverManager.getConnection(url,user,password);
//调用存储过程:统计薪水5000以上的人员的数量
cs = conn.prepareCall("{ call pro_jdbc_in_out_value(?,?,?) }");
cs.setString(1,"");
cs.setDouble(2, 5000);
//注意:注册的index和取值时要对应
cs.registerOutParameter(3,Types.INTEGER);
//执行存储过程
cs.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index对应
int i = cs.getInt(3);
System.out.println("符号条件的查询结果 count := " + i);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if (null != cs) {
cs.close();
}
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
[3] 既有输入IN参数,也有输出OUT参数,输出是列表
首先需要创建PACKAGE PKG_JDBC_PROCEDURE_CURSOR 的SQL如下:
CREATE OR REPLACE PACKAGE PKG_JDBC_PROCEDURE_CURSOR IS
TYPE CURSOR_JDBC_PROCEDURE IS REF CURSOR;
END PKG_JDBC_PROCEDURE_CURSOR;
再创建存储过程 PRO_JDBC_IN_OUT_LIST 的SQL如下:
--新建存储过程PRO_JDBC_IN_OUT_LIST
CREATE OR REPLACE PROCEDURE PRO_JDBC_IN_OUT_LIST(P_SALARY IN NUMBER,
P_OUTRS OUT PKG_JDBC_PROCEDURE_CURSOR.CURSOR_JDBC_PROCEDURE) IS
V_SALARY NUMBER := P_SALARY;
BEGIN
IF P_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
OPEN P_OUTRS FOR
SELECT * FROM tb_jdbc_procedure T WHERE T.SALARY > V_SALARY;
END PRO_JDBC_IN_OUT_LIST;
Java调用部分的代码如下:
public static void main(String[] args) throws Exception {
String className = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "cobcmw";
String password = "1234567890";
/**
* [3] 有输入IN参数,也有输出OUT参数,输出是列表;
*/
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName(className).newInstance();
conn = DriverManager.getConnection(url,user,password);
cs = conn.prepareCall("{call PRO_JDBC_IN_OUT_LIST(?,?)}");
//输入参数
cs.setDouble(1, 3000);
//输出参数
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index对应
rs = (ResultSet) cs.getObject(2);
// 获取列名及类型
int colunmCount = rs.getMetaData().getColumnCount();
String[] colNameArr = new String[colunmCount];
String[] colTypeArr = new String[colunmCount];
for (int i = 0; i < colunmCount; i++) {
colNameArr[i] = rs.getMetaData().getColumnName(i + 1);
colTypeArr[i] = rs.getMetaData().getColumnTypeName(i + 1);
System.out.print(colNameArr[i] + "(" + colTypeArr[i] + ")"
+ " | ");
}
System.out.println();
while (rs.next()) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < colunmCount; i++) {
sb.append(rs.getString(i + 1) + " | ");
}
System.out.println(sb);
}
} catch ( SQLException e) {
e.printStackTrace(System.out);
} finally {
if (null != rs) {
rs.close();
}
if (null != cs) {
cs.close();
}
if (null != conn) {
conn.close();
}
}
}
[4] 输入输出参数是同一个(IN OUT)
创建存储过程PRO_JDBC_IN_OUT的SQL如下:
--创建存储过程PRO_JDBC_IN_OUT
CREATE OR REPLACE PROCEDURE PRO_JDBC_IN_OUT(P_USERID IN VARCHAR2,
P_NUM IN OUT NUMBER) IS
V_COUNT NUMBER;
V_SALARY NUMBER := P_NUM;
BEGIN
IF V_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
SELECT COUNT(*)
INTO V_COUNT
FROM tb_jdbc_procedure
WHERE USER_ID LIKE '%' || P_USERID || '%'
AND SALARY >= V_SALARY;
P_NUM := V_COUNT;
END PRO_JDBC_IN_OUT;
Java调用代码如下:
public static void main(String[] args) throws Exception {
String className = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "cobcmw";
String password = "1234567890";
/**
* [4] 输入输出参数是同一个(IN OUT);
*/
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName(className).newInstance();
conn = DriverManager.getConnection(url,user,password);
cs = conn.prepareCall("{call PRO_JDBC_IN_OUT(?,?)}");
cs.setString(1, "michael");
cs.setDouble(2, 3000);
// 注意此次注册out 的index 和上面的in 参数index 相同
cs.registerOutParameter(2, Types.INTEGER);
cs.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index对应
int count = cs.getInt(2);
System.out.println("符号条件的查询结果 count := " + count);
} catch (SQLException e) {
e.printStackTrace(System.out);
} finally {
if (null != cs) {
cs.close();
}
if (null != conn) {
conn.close();
}
}
}