java调用函数和过程

1、导入jar包

jar包在oracle安装目目录中有

 

2、JDBCUtils工具类

package utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {
	
	private static String driver="oracle.jdbc.OracleDriver";
	private static String url="jdbc:oracle:thin:@192.168.146.128:1521/orcl";
	private static String user="scott";
	private static String password="tiger";
	
	//注册驱动
	static {
		//DriverManager.registerDriver(driver);
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//获得连接
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	//关闭连接
	public static void release(Connection conn,Statement st,ResultSet rs) {
		
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}

		if(st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				st = null;
			}
		}
		
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
		
	}
	
}

3、调用存储过程

/*
 * 存储过程的定义：
 * create or replace procedure informa(eno in number,
                                  pename out varchar2,
                                  pjob out varchar2,
                                  psal out number)
 * 
 */
	
@Test
public void testProcedure() {
	//api中定义sql的格式：{call <procedure-name>[(<arg1>,<arg2>, ...)]}
	String sql = "{call informa(?,?,?,?)}";
		
	Connection conn = null;
	CallableStatement call = null;
		
	try {
		conn = JDBCUtils.getConnection();
		call = conn.prepareCall(sql);
			
		//给in参数赋值
		call.setInt(1, 7499);
			
		//声明out参数
		call.registerOutParameter(2, OracleTypes.VARCHAR);
		call.registerOutParameter(3, OracleTypes.VARCHAR);
		call.registerOutParameter(4, OracleTypes.NUMBER);
			
		//执行
		call.execute();
			
		System.out.println(call.getString(2));
		System.out.println(call.getString(3));
		System.out.println(call.getDouble(4));
			
			
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		JDBCUtils.release(conn, call,null);
	}
}

4、调用存储函数

/*
 * 函数定义：获得指定员工的年薪
 * create or replace function queryIncome(eno in number)
	return number
 */
	
@Test
public void testFunction() {
	//api中sql格式：{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
	String sql = "{?=call queryIncome(?)}";
		
	Connection conn = null;
	CallableStatement call = null;
		
	try {
		conn = JDBCUtils.getConnection();
		call = conn.prepareCall(sql);
		
		call.registerOutParameter(1, OracleTypes.NUMBER);
		call.setInt(2, 7521);
			
		call.execute();
			
		System.out.println(call.getDouble(1));
			
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		JDBCUtils.release(conn, call, null);
	}
}

5、返回数据是集合

（1）定义包

create or replace package mypackage is

  type empcursor is ref cursor;
  procedure queryEmpList(eno in number,empList out empcursor);

end mypackage;

（2）创建包体

create or replace package body mypackage is

  procedure queryEmpList(eno in number,empList out empcursor) as
  begin
    open empList for select * from emp where deptno=eno;
  end queryEmpList;

end mypackage;

（3）java调用该程序

@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");
			double sal  = rs.getDouble("sal");
			System.out.println(name+"的工资是："+sal);
		}
			
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		JDBCUtils.release(conn, call, rs);
	}
		
}

包体中没有关闭光标，但在java程序中关闭ResultSet的时候，就关闭了光标。

注意：不能通过写java代码来干预java的垃圾回收机制

system.gc()请求运行垃圾回收