测试oracle存储过程，光标，连接等的java代码

package demo.test;

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

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import org.junit.Test;

import demo.util.JDBCUtils;

/*
*
* 性能:
* Statement < PreparedStatement < CallableStatement
*/
public class TestOracle {

/*
 * create or replace

procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
*/
@Test
public void testProcedure(){
//{call [(,, …)]}
String sql = “{call queryEmpInfo(?,?,?,?)}”;
Connection conn = null;
CallableStatement call = null;
try{
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//赋值
call.setInt(1, 7839);
//对于out参数，申明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);

        //调用
        call.execute();

        //取出结果
        String name = call.getString(2);
        double sal  = call.getDouble(3);
        String job = call.getString(4);
        System.out.println(name);
        System.out.println(sal);
        System.out.println(job);
    }catch (Exception e) {
        e.printStackTrace();
    }finally{
        JDBCUtils.release(conn, call, null);
    }
}

/*
 * create or replace

function queryEmpIncome(eno in number)
return number
*/
@Test
public void testFunction(){
//{?= call [(,, …)]}
String sql = “{?=call queryEmpIncome(?)}”;
Connection conn = null;
CallableStatement call = null;
try{
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);

        call.registerOutParameter(1, OracleTypes.NUMBER);
        call.setInt(2, 7839);

        //执行
        call.execute();

        //取出年收入
        double income = call.getDouble(1);
        System.out.println(income);
    }catch (Exception e) {
        e.printStackTrace();
    }finally{
        JDBCUtils.release(conn, call, null);
    }
}


/*
 * 问题：
 * 1. 光标是否被关？
 * 2. 是否能在MYSQL上执行？
 */
@Test
public void testCursor(){
    String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
    Connection conn = null;
    CallableStatement call = null;
    ResultSet rs  =null;
    try {
        conn = JDBCUtils.getConnection();
        call = conn.prepareCall(sql);

        call.setInt(1, 10);
        call.registerOutParameter(2, OracleTypes.CURSOR);

        //执行
        call.execute();

        //取出集合
        rs = ((OracleCallableStatement)call).getCursor(2);
        while(rs.next()){
            String name = rs.getString("ename");
            String job = rs.getString("empjob");
            System.out.println(name+"的职位是"+job);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        JDBCUtils.release(conn, call, rs);
    }
}

}