oracle与jdbc的结合

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

public class JDBC_Batch_Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// JDBC批处理 执行批量语句
		// 插入emp dept
		Connection connection = OracleConnection.getConnection();
		Statement statement = null;

		String sqlDept = "insert into dept values(55,'批处理部门','福建')";
		String sqlEMP = "insert into emp(empno,ename,sal,deptno) values(8001,'JDBC',5000,55)";
		try {
			// /设置自动提交为false
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			statement.addBatch(sqlDept);
			statement.addBatch(sqlEMP);

			statement.executeBatch();
			connection.commit();

			connection.setAutoCommit(true);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			// 出现异常需要回滚数据
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {

			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		System.out.println("OK");

	}

}

callableStatement.setint(1,值)//参数位置
callableStatement.setint("参数名",值);//参数名为定义的形参名字


注意过程调用中的参数命名表示法
位置、名称、混合(当出现名称时后面剩下的只能是名称表示)
但是JDBC中只能是位置或者名称

通过参数名称设置值，只能通过参数名称获得值

callableStatement.setInt("empno_p", 8888);
// out类型的参数
callableStatement.registerOutParameter("ename_p", Types.VARCHAR);
callableStatement.registerOutParameter("sal_p", Types.INTEGER);
boolean ex = callableStatement.execute();
System.out.println("ex=" + ex);
// if(callableStatement.execute()){
String ename = callableStatement.getString("ename_p");
int sal = callableStatement.getInt("sal_p");

同理：通过位置设置参数，只能通过位置获得对应out参数的值

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

import oracle.jdbc.OracleTypes;

public class TestCallStatement {
	static void noReturnValue() {
		// create or replace procedure add_dept(id_p number, dname_p
		// varchar2,loc_p varchar2) is
		// begin
		// --不会受父事务的影响
		// insert into dept values(id_p,dname_p,loc_p);
		// commit;--进行自主提交业务
		// dbms_output.put_line('过程提交完成...');
		// end add_dept;
		Connection connection = OracleConnection.getConnection();
		CallableStatement callableStatement = null;
		try {
			callableStatement = connection
					.prepareCall("{call add_dept(?,?,?)}");
			// 位置
			// callableStatement.setInt(1, 56);
			// callableStatement.setString(2, "JDBC过程插入");
			// callableStatement.setString(3, "福州");
			// 名称
			callableStatement.setInt("id_p", 57);
			callableStatement.setString("dname_p", "JDBC过程插入57");
			callableStatement.setString("loc_p", "福州57");
			callableStatement.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				callableStatement.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		System.out.println("OK");

	}

	static void haveReturnValue_notList() {
		// create or replace procedure out_pro(empno_p in number,
		// ename_p out varchar2,
		// sal_p out number) is
		// begin
		// select ename, sal into ename_p, sal_p from emp where empno = empno_p;
		// exception
		// when NO_DATA_FOUND then
		// ename_p := 'NULL';
		// sal_p := -1;
		// end out_pro;
		Connection connection = OracleConnection.getConnection();
		CallableStatement callableStatement = null;
		try {
			callableStatement = connection.prepareCall("{call out_pro(?,?,?)}");
			callableStatement.setInt(1, 8888);
			callableStatement.registerOutParameter(2, Types.VARCHAR);
			callableStatement.registerOutParameter(3, Types.INTEGER);
			callableStatement.execute();
			String ename = callableStatement.getString(2);
			int sal = callableStatement.getInt(3);
			System.out.println("ename=" + ename);
			System.out.println("sal=" + sal);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				callableStatement.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		System.out.println("OK");

	}

	static void haveReturnValue_isList() {
		// 包头
		// create or replace package test_pack is
		// --定义公共变量
		// type dept_cursor is REF CURSOR ;
		// procedure dept_proc(p_dept_cursor out dept_cursor);
		// function emp_function return varchar2;
		// function order_fun return varchar2;
		// end test_pack;
		// 包体
		// create or replace package body test_pack is
		// --不需要重复定义游标
		// --注意过程out类型的游标参数
		// procedure dept_proc(p_dept_cursor out dept_cursor) is
		// begin
		// open p_dept_cursor for
		// select * from dept;
		// end dept_proc;
		// end test_pack;
		Connection connection = OracleConnection.getConnection();
		CallableStatement callableStatement = null;
		ResultSet resultSet = null;
		try {
			callableStatement = connection
					.prepareCall("{call test_pack.dept_proc(?)}");
			// oracle.jdbc.OracleTypes.CURSOR
			callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
			callableStatement.execute();
			resultSet = (ResultSet) callableStatement.getObject(1);
			while (resultSet.next()) {
				System.out.println(resultSet.getInt(1) + "-"
						+ resultSet.getString(2) + "-"
						+ resultSet.getString("loc"));

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				callableStatement.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		System.out.println("OK");

	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// noReturnValue();
//		haveReturnValue_notList();
		haveReturnValue_isList();

	}

}