Java 代码调用 oracle 存储过程

数据库本身是可以写定时任务的，不过这只局限于数据库中的数据变化；而 Java 可以写定时任务调用存储过程，从而与数据库进行交互。

本人用的是 oracle 数据库
以上是两个存储过程，一个用于插入数据，一个用于查询数据

CREATE OR REPLACE PROCEDURE insert_procedure(
  PARA1 IN VARCHAR2,
  PARA2 IN VARCHAR2
) AS
BEGIN
 INSERT INTO xiao_test (tid, tname) VALUES (PARA1, PARA2);
END insert_procedure;

------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE select_procedure(
  para_id IN VARCHAR2,
  tname OUT sys_refcursor /* 这个sys_refcursor类型在SYS.STANDARD包中 */
) AS
BEGIN
 OPEN tname FOR
  SELECT * FROM xiao_test WHERE tid = para_id;
END;

事先准备的测试表（这个应该是我之前测试存储生僻字用的表）

数据库连接工具类

public class DBUtil {
	public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
	public static final String URL = "jdbc:oracle:thin:@localhost:1521/orcl";
	public static final String USERNAME = "ftba";
	public static final String PASSWORD = "ftba123";

	//通过静态代码块 注册数据库驱动
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	//获得Connection
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	//获得Statement
	public static Statement getStatement() {
		Statement st = null;
		try {
			st = getConnection().createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return st;
	}

	//关闭ResultSet
	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//关闭Statement
	public static void closeStatement(Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//关闭Connection
	public static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//关闭全部
	public static void closeAll(ResultSet rs, Statement sta, Connection conn) {
		closeResultSet(rs);
		closeStatement(sta);
		closeConnection(conn);
	}

}

测试类

public class CallProcedure {
	public static void main(String[] args) {
		insert_call();
		select_call();
		System.out.println("over");
	}

	//执行存储过程 插入数据
	public static void insert_call() {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pst = null;
		
		CallableStatement proc = null; // 创建执行存储过程的对象
		try {
			proc = conn.prepareCall("{ call insert_procedure(?,?) }");
			proc.setString(1, "8"); // 设置第一个输入参数
			proc.setString(2, "java call procedure"); // 设置第二个输入参数
			proc.execute();// 执行
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭IO流
				proc.close();
				DBUtil.closeAll(null, pst, conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 执行存储过程 查询数据
	public static void select_call() {
		Connection conn = DBUtil.getConnection();

		CallableStatement stmt;
		try {
			stmt = conn.prepareCall("{ call select_procedure(?, ?) }"); 
			stmt.setString(1, "8");
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet) stmt.getObject(2);
			while (rs.next()) {
				System.out.println(rs.getString("tid") + "====" + rs.getString("tname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeConnection(conn);
		}
	}
}

执行测试类，我这里是执行了两次

再在 plsql 查询数据库，数据成功插入到数据库