package com.jckb.procedure;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

public class MainTest2  {

	/**

	 * 存储函数plsql

		create or replace function findEmpYearSal(pno in number,pname out varchar2,psal out number)

		  return number

		as

		  pcomm emp.comm%type;

		begin

		  select ename,sal,pcomm into pname,psal,pcomm from emp where empno=pno;

		  return psal*12+nvl(pcomm,0);

		end;

	 * */

	public static void main(String[] args) {

		//1、定义变量

		String driverClass = "oracle.jdbc.OracleDriver";

		String url = "jdbc:oracle:thin:@localhost:1521:orcl";

		String user = "scott";

		String password = "tiger";

		try{

		Class.forName(driverClass);

		//2、获取连接对象

		Connection connection = DriverManager.getConnection(url, user, password);

		//3、创建执行存储过程的语句对象

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

		CallableStatement callableStatement = connection.prepareCall(sql);

		//4、设置参数

		callableStatement.registerOutParameter(1, oracle.jdbc.OracleTypes.DOUBLE);

		callableStatement.setInt(2, 7521);

		callableStatement.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);

		callableStatement.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);

		//5、执行

		callableStatement.execute();

		//6、获取数据

		double yearSal = callableStatement.getDouble(1);

		String ename = callableStatement.getString(3);

		double sal = callableStatement.getDouble(4);

		System.out.println("姓名："+ename+" 员工工资："+sal + "年薪："+yearSal);

		//7、释放资源

		callableStatement.close();

		connection.close();

		}catch(Exception e){

			e.printStackTrace();

		}

	}

}