java 执行oracle 存储过程_java使用jdbc执行Oracle存储过程实例

Oracle 创建存储过程

--建立程序包

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE TEST_CURSOR IS REF CURSOR;

end TESTPACKAGE;

--创建返回列表的存储过程

CREATE OR REPLACE PROCEDURE TESTC

(

P_CURSOR out TESTPACKAGE.TEST_CURSOR

)

IS

BEGIN

OPEN P_CURSOR FOR

SELECT * FROM epv_dev.eprk_notice_test;

END TESTC;

package com.abc.demo;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import java.util.ArrayList;

import java.util.Hashtable;

import java.util.List;

import oracle.jdbc.OracleTypes;

public class Test {

public static String driver = "oracle.jdbc.driver.OracleDriver";

public static String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:epadbbak";

public static String userName = "sky";

public static String userPwd = "sky123";

public static Statement stmt = null;

public static ResultSet rs = null;

public static Connection conn = null;

public static CallableStatement proc = null;

/**

* 获得数据库连接，当连接为空时，创建连接

* @return

*/

public static Connection getConnection() {

if (conn == null) {

try {

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, userName, userPwd);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

return conn;

}

/**

* 执行查询单个参数的存储过程

* @return

*/

public static Object executeQuertProType() {

Object result = "";

conn = getConnection();

try {

proc = conn.prepareCall("{ call epa_dev.TESTB(?,?) }");

proc.setString(1, "sdlkj");

proc.registerOutParameter(2, Types.VARCHAR);

proc.execute();

result = proc.getString(2);

} catch (SQLException e) {

e.printStackTrace();

}finally{

closeAll();

}

return result;

}

public static List> executeQuertProTable(){

List> result = null;

conn = getConnection();

try {

proc = conn.prepareCall("{ call epa_dev.testc(?) }");

proc.registerOutParameter(1, OracleTypes.CURSOR);

proc.execute();

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

if (null == rs)

return result;

result = new ArrayList>();

while (rs.next()) {

Hashtable row = new Hashtable();

java.sql.ResultSetMetaData rsmd = rs.getMetaData();

for (int i = 1; i <= rsmd.getColumnCount(); i++) {

if (null == rs.getObject(i)) continue;

row.put(rsmd.getColumnName(i).toLowerCase(), rs.getObject(i));

}

result.add(row);

}

} catch (SQLException e) {

e.printStackTrace();

}

return result;

}

/**

* 关闭连接，释放资源..

*/

public static void closeAll() {

try {

if (rs != null) {

rs.close();

}

if (stmt != null) {

stmt.close();

}

if (conn != null) {

conn.close();

}

} catch (SQLException ex1) {

}

}

public static void main(String[] args) {

String result = (String) executeQuertProType();

List> templist = executeQuertProTable();

System.out.println("result:" + templist);

}

}