JDBC调用Oracle的存储过程

茹6641 · 发表于 2016-7-29 02:21:24

　　本文主要是总结如何实现 JDBC调用Oracle的存储过程，从以下情况分别介绍：

[1]、只有输入IN参数，没有输出OUT参数
[2]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）
[3]、既有输入IN参数，也有输出OUT参数，输出是列表
[4]、输入输出参数是同一个（IN OUT）

【准备工作】
创建一个测试表TMP_MICHAEL ，并插入数据，SQL如下：
Sql代码

create table TMP_MICHAEL
(
USER_ID VARCHAR2(20),
USER_NAME VARCHAR2(10),
SALARY NUMBER(8,2),
OTHER_INFO VARCHAR2(100)
)
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values ('michael', 'Michael', 5000, 'http://sjsky.iyunv.com');
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values ('zhangsan', '张三', 10000, null);
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values ('aoi_sola', '苍井空', 99999.99, 'twitter account');
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values ('李四', '李四', 2500, null);

Oracle jdbc 常量：
Java代码

private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
private final static String DB_CONNECTION = "jdbc:oracle:thin:@127.0.0.1:1521:Ora11g";
private final static String DB_NAME = "mytest";
private final static String DB_PWd = "111111";

[一]、只有输入IN参数，没有输出OUT参数

存储过程 TEST_MICHAEL_NOOUT 的相关SQL：
Sql代码

CREATE OR REPLACE PROCEDURE TEST_MICHAEL_NOOUT(P_USERID IN VARCHAR2,
P_USERNAME IN VARCHAR2,
P_SALARY IN NUMBER,
P_OTHERINFO IN VARCHAR2) IS
BEGIN
INSERT INTO TMP_MICHAEL
(USER_ID, USER_NAME, SALARY, OTHER_INFO)
VALUES
(P_USERID, P_USERNAME, P_SALARY, P_OTHERINFO);
END TEST_MICHAEL_NOOUT;

调用代码如下：
Java代码

/**
* 测试调用存储过程：无返回值
* @blog http://sjsky.iyunv.com
* @author Michael
* @throws Exception
*/
public static void testProcNoOut() throws Exception {
System.out.println("------- start 测试调用存储过程：无返回值");
Connection conn = null;
CallableStatement callStmt = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
// 存储过程 TEST_MICHAEL_NOOUT 其实是向数据库插入一条数据
callStmt = conn.prepareCall("{call TEST_MICHAEL_NOOUT(?,?,?,?)}");
// 参数index从1开始，依次 1,2,3...
callStmt.setString(1, "jdbc");
callStmt.setString(2, "JDBC");
callStmt.setDouble(3, 8000.00);
callStmt.setString(4, "http://sjsky.iyunv.com");
callStmt.execute();
System.out.println("------- Test End.");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != callStmt) {
callStmt.close();
}
if (null != conn) {
conn.close();
}
}
}

运行后查询数据库内容,已经成功插入数据，截图如下：

[二]、既有输入IN参数，也有输出OUT参数，输出是简单值（非列表）

存储过程 TEST_MICHAEL 的SQL如下：
Sql代码

CREATE OR REPLACE PROCEDURE TEST_MICHAEL(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 TMP_MICHAEL T
WHERE T.SALARY >= V_SALARY;
ELSE
SELECT COUNT(*)
INTO P_COUNT
FROM TMP_MICHAEL T
WHERE T.SALARY >= V_SALARY
AND T.USER_ID LIKE '%' || P_USERID || '%';
END IF;
DBMS_OUTPUT.PUT_LINE('v_count=:' || P_COUNT);
END TEST_MICHAEL;

调用程序如下：
Java代码

/**
* 测试调用存储过程：返回值是简单值非列表
* @blog http://sjsky.iyunv.com
* @author Michael
* @throws Exception
*/
public static void testProcOutSimple() throws Exception {
System.out.println("------- start 测试调用存储过程：返回值是简单值非列表");
Connection conn = null;
CallableStatement stmt = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
stmt = conn.prepareCall("{call TEST_MICHAEL(?,?,?)}");
stmt.setString(1, "");
stmt.setDouble(2, 3000);
// out 注册的index 和取值时要对应
stmt.registerOutParameter(3, Types.INTEGER);
stmt.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index对应
int i = stmt.getInt(3);
System.out.println("符号条件的查询结果 count := " + i);
System.out.println("------- Test End.");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
}
}

   测试程序就是查询薪水3000以上人员的数量，运行结果如下：
------- start 测试调用存储过程：返回值是简单值非列表
符号条件的查询结果 count := 4
------- Test End.

[三]、既有输入IN参数，也有输出OUT参数，输出是列表
  首先需要创建PACKAGE TEST_PKG_CURSOR 的SQL如下：
Sql代码

CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS
-- Author : MICHAEL http://sjsky.iyunv.com
TYPE TEST_CURSOR IS REF CURSOR;
END TEST_PKG_CURSOR;

再创建存储过程 TEST_P_OUTRS 的SQL如下：
Sql代码

CREATE OR REPLACE PROCEDURE TEST_P_OUTRS(P_SALARY IN NUMBER,
P_OUTRS OUT TEST_PKG_CURSOR.TEST_CURSOR) IS
V_SALARY NUMBER := P_SALARY;
BEGIN
IF P_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
OPEN P_OUTRS FOR
SELECT * FROM TMP_MICHAEL T WHERE T.SALARY > V_SALARY;
END TEST_P_OUTRS;

调用存储过程的代码如下：
Java代码

/**
* 测试调用存储过程：有返回值且返回值为列表的
* @blog http://sjsky.iyunv.com
* @author Michael
* @throws Exception
*/
public static void testProcOutRs() throws Exception {
System.out.println("------- start 测试调用存储过程：有返回值且返回值为列表的");
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
stmt = conn.prepareCall("{call TEST_P_OUTRS(?,?)}");
stmt.setDouble(1, 3000);
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index对应
rs = (ResultSet) stmt.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 = rs.getMetaData().getColumnName(i + 1);
colTypeArr = rs.getMetaData().getColumnTypeName(i + 1);
System.out.print(colNameArr + "(" + colTypeArr + ")"
+ " | ");
}
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);
}
System.out.println("------- Test Proc Out is ResultSet end. ");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != rs) {
rs.close();
}
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
}
}

运行结果如下:
------- start 测试调用存储过程：有返回值且返回值为列表的
USER_ID(VARCHAR2) | USER_NAME(VARCHAR2) | SALARY(NUMBER) | OTHER_INFO(VARCHAR2) |
michael | Michael | 5000 | null |
zhangsan | 张三 | 10000 | null |
aoi_sola | 苍井空 | 99999.99 | null |
jdbc | JDBC | 8000 | http://sjsky.iyunv.com |
------- Test Proc Out is ResultSet end.
[四]、输入输出参数是同一个（IN OUT）

创建存储过程TEST_P_INOUT 的SQL如下：
Sql代码

CREATE OR REPLACE PROCEDURE TEST_P_INOUT(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 TMP_MICHAEL

   WHERE USER_ID LIKE '%' || P_USERID || '%'

     AND SALARY >= V_SALARY;

  P_NUM := V_COUNT;

END TEST_P_INOUT;

调用存储过程的代码：
Java代码

/**

     * 测试调用存储过程： INOUT同一个参数：

     * @blog http://sjsky.iyunv.com

     * @author Michael

     * @throws Exception

     */

    public static void testProcInOut() throws Exception {

        System.out.println("-------  start 测试调用存储过程：INOUT同一个参数");

        Connection conn = null;

        CallableStatement stmt = null;

        try {

            Class.forName(DB_DRIVER);

            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);



            stmt = conn.prepareCall("{call TEST_P_INOUT(?,?)}");



            stmt.setString(1, "michael");

            stmt.setDouble(2, 3000);



            // 注意此次注册out 的index 和上面的in 参数index 相同

            stmt.registerOutParameter(2, Types.INTEGER);

            stmt.execute();



            // getXxx(index)中的index 需要和上面registerOutParameter的index对应

            int count = stmt.getInt(2);

            System.out.println("符号条件的查询结果 count := " + count);

            System.out.println("-------  Test End.");

        } catch (Exception e) {

            e.printStackTrace(System.out);

        } finally {

            if (null != stmt) {

                stmt.close();

            }

            if (null != conn) {

                conn.close();

            }

        }

    }

运行结果如下：
------- start 测试调用存储过程：INOUT同一个参数
符号条件的查询结果 count := 1
------- Test End.

到处基本介绍已经结束了，希望能给大家有所帮助。

本文连接：http://sjsky.iyunv.com/blog/1246657

转载请注明来自：Michael's blog @ http://sjsky.iyunv.com

账号		自动登录	找回密码
密码			立即注册

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

c++ size_t 和 int 的区别

[经验分享] JDBC调用Oracle的存储过程

浏览过的版块

扫码加入运维网微信交流群