java 调用oracle function,java调用oracle中存储过程与调用function函数两例-Oracle

java调用oracle中存储过程与调用function函数两例，在java中调用存储过程与调用function函数类似但不相同。

1.调存储过程

有几个参数，用几个占位符在存储过程的()中

public static ArrayList Prc_Page(PageInfo page) {

ArrayList list = new ArrayList();

Map mp;

Session s = null;

Connection conn = null;

ResultSet rs = null;

CallableStatement proc = null;

try {

s = HibernateSessionFactory.getSession();

conn = s.connection();

proc = conn.prepareCall(“{call pages.prc_page(?,?,?,?,?,?,?,?,?)}”);

proc.setString(1, page.getP_tableName());

proc.setString(2, page.getP_strWhere());

proc.setString(3, page.getP_orderColumn());

proc.setString(4, page.getP_orderStyle());

proc.setInt(5, page.getP_curPage());

proc.setInt(6, page.getP_pageSize());

proc.registerOutParameter(7, OracleTypes.NUMBER);

proc.registerOutParameter(8, OracleTypes.NUMBER);

proc.registerOutParameter(9, OracleTypes.CURSOR);

proc.execute();

// page.setP_totalRecords(proc.getInt(“p_totalRecords”));

// page.setP_totalPages(proc.getInt(“p_totalPages”));

// list = (ArrayList) proc.getObject(“v_cur”);

page.setP_totalRecords(proc.getInt(7));

page.setP_totalPages(proc.getInt(8));

rs = ((OracleCallableStatement) proc).getCursor(9); // 得到输出结果集参数

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

while (rs.next()) {

mp = new HashMap(numberOfColumns);

for (int r = 1; r < numberOfColumns; r++) {

mp.put(rsmd.getColumnName(r), rs.getObject(r));

}

list.add(mp);

}

return list;

} catch (SQLException ex) {

ex.printStackTrace();

return list;

} catch (Exception ex2) {

ex2.printStackTrace();

return list;

} finally {

try {

if (proc != null) {

proc.close();

}

if (rs != null) {

rs.close();

}

if (conn != null) {

conn.close();

}

} catch (SQLException ex1) {

ex1.printStackTrace();

}

}

}

2.调用函数

第一个参数为游标，用占位符代替，？= 而且，call 必须小写，不能为CALL ，，函数()中的占位符表示的是函数所需要传入的参数

public String getQynsxx(int start, int limit, Map param) {

String pBA_ID = (String) param.get(“pBA_ID”);

String pFRDM = (String) param.get(“pFRDM”);

String prkrq_q = (String) param.get(“prkrq_q”);

String prkrq_z = (String) param.get(“prkrq_z”);

String pzsxm_dm = (String) param.get(“pzsxm_dm”);

System.out.println(“———日期起——“+prkrq_q.toString()+”—————-“);

System.out.println(“——–日期止——-“+prkrq_z.toString()+”—————-“);

System.out.println(“——–BA_ID——-“+pBA_ID+”—————-“);

System.out.println(“——–法人代码——-“+pFRDM+”—————-“);

System.out.println(“——–征收项目代码——-“+pzsxm_dm+”—————-“);

Connection conn = null;

ResultSet rs = null;

CallableStatement proc = null;

try {

//获得连接

Class.forName(“oracle.jdbc.driver.OracleDriver”);

conn= dao.getMyDataSource().getConnection();

proc = conn.prepareCall(“{? = call FUC_SWN_GET_QYNSXX(?,?,?,?,?)}”);

proc.registerOutParameter(1, OracleTypes.CURSOR);

proc.setString(2, pBA_ID);//BA_ID

proc.setString(3, pFRDM);

proc.setString(4, prkrq_q);

proc.setString(5, prkrq_z);

proc.setString(6, pzsxm_dm);

proc.execute();

// page.setP_totalRecords(proc.getInt(“p_totalRecords”));

// page.setP_totalPages(proc.getInt(“p_totalPages”));

// list = (ArrayList) proc.getObject(“v_cur”);

// String testSql = proc.getString(5);

rs=(ResultSet)proc.getObject(1);

int i =0;

StringBuffer result = new StringBuffer();

StringBuffer rjson = new StringBuffer();

while(rs.next()){

i++;

// System.out.println(rs.getString(“FRDM”)+”—“+rs.getString(“TYSHXYDM”)+”–“+rs.getString(“QYMC”)+”–“+rs.getString(“QYZT”));

rjson.append(“{“);

rjson.append(“\”ND\”:\””+rs.getInt(“ND”)+”\”,”);

rjson.append(“\”NSE\”:\””+rs.getString(“NSE”)+”\”,”);

rjson.append(“\”ZSXM\”:\””+rs.getString(“ZSXM”)+”\””);

rjson.append(“},”);

}

if(i!=0){

rjson.deleteCharAt(rjson.length()-1);

}

result.append(“{\”totalcount\”:\””+i+”\”,\”list\”:[“);

result.append(rjson);

result.append(“]}”);

return result.toString();

}

catch(Exception e)

{

e.printStackTrace();

}

finally

{

if (proc != null) {

try

{

proc.close();

} catch (SQLException e)

{

e.printStackTrace();

}

}

if (conn != null) {

try

{

conn.close();

} catch (SQLException e)

{

e.printStackTrace();

}

}

}

return “wrong”;

}