import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import oracle.jdbc.OracleTypes;

import oracle.jdbc.oracore.OracleType;

public class Test

{

	static void 简单的查询()

	{

		String sql="select * from emp where deptno=?";

		String[] paras={10+""};

		ResultSet rs=JDBCUtil.doQuery(sql, paras);

		try

		{

			while (rs.next())

			{

				int empno = rs.getInt("empno");

				String ename = rs.getString("ename");

				System.out.println(empno + "--" + ename);

			}

		}

		catch (Exception e)

		{

			System.out.println("查询异常！");

		}

		finally

		{

			JDBCUtil.close(rs);

		}

	}

	static void 简单的修改()

	{

		String sql="update emp set empno=? where ename=?";

		int result=JDBCUtil.doUpdate(sql, new String[]{"7369","老白"});

		if(result!=1)

		{

			System.out.println("修改失败！");

		}

		else

		System.out.println("修改成功！影响行数为："+result);

	}

	static void 调用无参存储过程()

	{

		//获取链接

		Connection con=JDBCUtil.getConnection();

		//执行存储过程

		String sql="{call system.update_emp_comm()}";

		int result=0;

		try

		{

			CallableStatement cmt = con.prepareCall(sql);

			result = cmt.executeUpdate();

		}

		catch (Exception e)

		{

			e.printStackTrace();

			System.out.println("执行过程出现异常！");

			return;

		}

		System.out.println("执行完毕！影响行数为："+result);

	}

	static void 调用有入参出参存储过程()

	{

		//1 获取连接

		Connection con = JDBCUtil.getConnection();

		//2 执行存储过程

		String sql = "{call update_emp_sal_by_deptno(?,?,?)}";

		int result = 0;

		try

		{

			CallableStatement cmt = con.prepareCall(sql);

			cmt.setInt(1, 30);

			cmt.setInt(2, 2000);

			//将第3个参数注册出参

			cmt.registerOutParameter(3, OracleType.STYLE_INT);

			result = cmt.executeUpdate();

			//获取第3个参数，也就是出参，执行完毕后的值

			int rowcount = cmt.getInt(3);

			System.out.println("影响了" + rowcount + "条！");

		}

		catch (Exception e)

		{

			e.printStackTrace();

			System.out.println("执行过程出现异常！");

			return;

		}

		System.out.println("执行完毕，影响行数为："+result);

	}

	static void 调用有入参的函数()

	{

		//1 获取连接

		Connection con = JDBCUtil.getConnection();

		//2 执行函数

		String sql ="{? = call delete_emp_by_empname(?)}";

		int result = 0;

		try

		{

			CallableStatement cmt = con.prepareCall(sql);

			//将函数的返回值，当成出参来注册

			cmt.registerOutParameter(1, OracleType.STYLE_INT);

			cmt.setString(2, "白");

			result = cmt.executeUpdate();

			//获取第3个参数，也就是出参，执行完毕后的值

			int rowcount = cmt.getInt(1);

			System.out.println("删除了" + rowcount + "条！");

		}

		catch (Exception e)

		{

			e.printStackTrace();

			System.out.println("执行过程出现异常！");

			return;

		}

		System.out.println(result);

		System.out.println("执行完毕！");

	}

	static void 调用有返回游标的函数()

	{

		//1 获取连接

		Connection con = JDBCUtil.getConnection();

		//2 执行函数

		String sql ="{?=call get_max_min_sal_by_group()}";

		int result = 0;

		ResultSet rs = null;

		try {

			CallableStatement cmt = con.prepareCall(sql);

			//将函数的返回值，当成出参来注册

			cmt.registerOutParameter(1, OracleTypes.CURSOR);

			result = cmt.executeUpdate();

			//获取第1个参数，也就是返回值，即游标，即resultset

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

			while (rs.next()) {

				int deptno = rs.getInt("部门号");

				float max_sal = rs.getFloat("最高工资");

				float min_sal = rs.getFloat("最低工资");

				System.out.println(deptno + "，最高工资" + max_sal + ",最低工资"

						+ min_sal);

			}

		}

		catch (Exception e)

		{

			e.printStackTrace();

			System.out.println("执行过程出现异常！");

			return;

		}

		finally

		{

			JDBCUtil.close(rs); //关闭游标

		}

		System.out.println(result);

		System.out.println("执行完毕！");

	}

	public static void testProcedure()

	{

		String sql="{call queryEmpInfo(?,?,?,?)}";

		Connection conn=null;

		CallableStatement call=null;

		try

		{

			conn = JDBCUtil.getConnection();

			call = conn.prepareCall(sql);

			//赋值

			call.setInt(1, 7839);

			//对于out参数，申明

			call.registerOutParameter(2, OracleTypes.VARCHAR);

			call.registerOutParameter(3, OracleTypes.NUMBER);

			call.registerOutParameter(4, OracleTypes.VARCHAR);

			//调用

			call.execute();

			//取出结果

			String name = call.getNString(2);

			double sal = call.getDouble(3);

			String job = call.getString(4);

			System.out.println(name);

			System.out.println(sal);

			System.out.println(job);

		}

		catch (Exception e)

		{

			e.printStackTrace();

		}

	}

	public static void testFunction()

	{

		String sql = "{?=call queryEmpIncome(?)}";

		Connection conn = null;

		CallableStatement call = null;

		try

		{conn = JDBCUtil.getConnection();

		call = conn.prepareCall(sql);

		call.registerOutParameter(1, OracleTypes.NUMBER);

		call.setInt(2, 7839);

		//执行　　

		call.execute();

		//取出年收入　　

		double income = call.getDouble(1);

		System.out.println(income);

		}

		catch (Exception e)

		{

			e.printStackTrace();

		}

	}

	public static void main(String[] args)

	{

		//简单的查询();

		//简单的修改();

		//调用无参存储过程();

		//调用有入参出参存储过程();

		//调用有入参的函数();

		//调用有返回游标的函数();

		//testProcedure();

		testFunction();

	}

}