Java 访问 oracle 11g 中的存储过程

下面介绍几种常用的 Java 访问 oarcle 存储过程的示例，需要说明的是创建 Java 项目记得装载 odbc 驱动包，并创建按如下 student 表：

create table STUDENT
(
  sno   number,
  sname VARCHAR2(10),
  sage  number
);

1. 没有返回参数的过程

1.1 插入记录

首先创建如下存储过程：

create or replace procedure test1(inSno in number, inSname in varchar2, inSage in number) 
is
begin
    insert into student(sno, sname, sage) values(inSno, inSname, inSage);
    commit;
end;
/

编写如下程序，即可实现对存储过程的访问，从而实现对学生记录的插入：

package com.shoto;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class TestDemo {
    private Connection conn;
    private CallableStatement callStmt;
    private ResultSet rs;
    private String driver = "oracle.jdbc.driver.OracleDriver";
    private String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

    /**
     * 测试没有返回参数的过程的调用（插入）
     * @param inSno
     * @param inSname
     * @param inSage
     */
    public void testNotReturnParamInsert(int inSno, String inSname, int inSage) {
        try {
            try {
                conn = DriverManager.getConnection(url, "scott", "tiger");
                // 三个占位符表示三个参数
                callStmt = conn.prepareCall("{call test1(?, ?, ?)}");
                // 传入参数值
                callStmt.setInt(1, inSno);
                callStmt.setString(2, inSname);
                callStmt.setInt(3, inSage);
                // 执行
                callStmt.execute();
                System.out.println("执行成功了！");
            } finally {
                if (callStmt != null) {
                    callStmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new TestDemo().testNotReturnParamInsert(6, "Jane", 26);
    }
}

1.2 更新记录

同样创建如下存储过程：

create or replace procedure test2(inSno in number, inSage in number) 
is 
begin
    update student set sage = inSage where sno = inSno;
    commit;
end;
/

编写如下程序方法，即可实现对存储过程的访问，从而实现对学生记录的更新：

/**
* 测试没有返回参数的过程的调用（更新）
* @param inSno
* @param inSage
*/
public void testNotReturnParamUpdate(int inSno, int inSage) {
    try {
        try {
            conn = DriverManager.getConnection(url, "scott", "tiger");
            callStmt = conn.prepareCall("{call test2(?, ?)}");
            callStmt.setInt(1, inSno);
            callStmt.setInt(2, inSage);
            callStmt.execute();
            System.out.println("执行成功了！");
        } finally {
            if (callStmt != null) {
                callStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

2. 有返回参数的过程

创建如下存储过程：

create or replace procedure test3(inSno in number, outSname out varchar2, outSage out number) is 
begin
    select sname, sage into outSname, outSage from student where sno = inSno;
end;
/

编写如下程序方法，即可实现对存储过程的访问，从而实现对指定学号的学生姓名和年龄的获取：

/**
 * 测试有返回参数的过程的调用
 * @param inSno
 */
public void testReturnParam(int inSno) {
    try {
        try {
            conn = DriverManager.getConnection(url, "scott", "tiger");
            callStmt = conn.prepareCall("{call test3(?, ?, ?)}");
            callStmt.setInt(1, inSno);
            // out 类型的参数需要进行注册，并指明类型
            callStmt.registerOutParameter(2, Types.VARCHAR);
            callStmt.registerOutParameter(3, Types.INTEGER);
            callStmt.execute();
            // 获取返回结果
            String name = callStmt.getString(2);
            Integer age = callStmt.getInt(3);
            System.out.println("学生学号：" + inSno + ", 姓名：" + name + ", 年龄：" + age);
        } finally {
            if (callStmt != null) {
                callStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

3. 返回列表的过程

由于 oracle 存储过程要返回列表结果集，需要在存储过程的 out 参数中指明为 ref 游标类型的参数。为了实现这种效果，我们首先创建如下程序包头，并声明 ref 游标类型变量：

-- 创建程序包头
create or replace package pack1 is
    type test_cursor is ref cursor; -- 声明一个 ref 游标类型的类型变量
end pack1;
/

-- 创建存储过程
create or replace procedure test4(p_cursor out pack1.test_cursor) is -- out 参数类型为 test_cursor 类型，也即是 ref 游标类型
begin
    open p_cursor for select * from student; -- 返回的结果集为游标类型
end;
/

编写如下程序方法，即可获取列表结果集：

public void testReturnList() {
    try {
        try {
            conn = DriverManager.getConnection(url, "scott", "tiger");
            callStmt = conn.prepareCall("{call test4(?)}");
            // 注册为游标类型
            callStmt.registerOutParameter(1, OracleTypes.CURSOR);
            callStmt.execute();
            // 获取返回结果
            rs = (ResultSet) callStmt.getObject(1);
            while (rs.next()) {
                System.out.println("学生学号：" + rs.getInt(1) + ", 姓名：" + rs.getString(2) + ", 年龄：" + rs.getInt(3));
            }
        } finally {
            if (callStmt != null) {
                callStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

4. 返回带分页的列表的过程

创建如下存储过程：

-- 这里我们依旧使用上述事例的程序包包头 pack1
create or replace procedure test5(pageSize in number, pageNo in number, p_cursor out pack1.test_cursor) is
begin
    open p_cursor for 
        select * from (select s.*, rownum rn from student s) where rn >= pageSize * (pageNo - 1） + 1 and rn <= pageSize * pageNo;
end;
/

编写如下程序方法，即可获取列表分页结果集：

public void testReturnPageList(int pageSize, int pageNo) {
    try {
        try {
            conn = DriverManager.getConnection(url, "scott", "tiger");
            callStmt = conn.prepareCall("{call test5(?, ?, ?)}");
            callStmt.setInt(1, pageSize);
            callStmt.setInt(2, pageNo);
            callStmt.registerOutParameter(3, OracleTypes.CURSOR);
            callStmt.execute();
            rs = (ResultSet) callStmt.getObject(3);
            while (rs.next()) {
                System.out.println("学生学号：" + rs.getInt(1) + ", 姓名：" + rs.getString(2) + ", 年龄：" + rs.getInt(3));
            }
        } finally {
            if (callStmt != null) {
                callStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}