JDBC调用存储过程：四种分类详解及实例(Oracle)

sjsky

浏览: 921696 次
性别:
来自: 上海

最近访客更多访客>>

ouaijsun

码猿工

u010469169

yannier

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

J2EE

Java JDBC 存储过程 Oracle

blog迁移至 :http://www.micmiu.com

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

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

【准备工作】

创建一个测试表TMP_MICHAEL ，并插入数据，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.iteye.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 常量：

    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：

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;

调用代码如下：

 /**
     * 测试调用存储过程：无返回值
     * @blog http://sjsky.iteye.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.iteye.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如下：

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;

调用程序如下：

 /**
     * 测试调用存储过程：返回值是简单值非列表
     * @blog http://sjsky.iteye.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如下：

CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS

  -- Author  : MICHAEL  http://sjsky.iteye.com
  TYPE TEST_CURSOR IS REF CURSOR;

END TEST_PKG_CURSOR;

再创建存储过程 TEST_P_OUTRS 的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;

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

 /**
     * 测试调用存储过程：有返回值且返回值为列表的
     * @blog http://sjsky.iteye.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[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);
            }
            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();
            }
        }
    }

运行结果如下:

[四]、输入输出参数是同一个（IN OUT）

创建存储过程TEST_P_INOUT 的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;

调用存储过程的代码：

/**
     * 测试调用存储过程： INOUT同一个参数：
     * @blog http://sjsky.iteye.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.iteye.com/blog/1246657

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

----------------------------- 分 ------------------------------ 隔 ------------------------------ 线 ------------------------------

查看图片附件

3
顶

3
踩

分享到：

EhCache之初试 | dom4j使用小结(基础入门级)

2011-11-10 09:41
浏览 9306
评论(6)
分类:编程语言
查看更多

6 楼 1028546747 2012-05-22

赞一个

5 楼 xwl1991 2011-11-11

学习学习……

4 楼 sjsky 2011-11-10

liupopo 写道

呵呵，再写一个：
[5] 既有输入IN参数，也有输出OUT参数，输出是简单值和列表
这样的例子吧。

比如做分页查询，需要同时获得总记录数和当前页的记录列表

有意思，不过这个和之前的介绍的几种情况结合一下就可以了，现在网上这种分页的应该很多的

3 楼 liupopo 2011-11-10

2 楼 sjsky 2011-11-10

greatwqs 写道

写的漂亮

谢谢兄弟的顶

1 楼 greatwqs 2011-11-10

写的漂亮

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论