Oracle 数据库入门之----------------------Java调用存储过程/函数

存储函数（有返回值）

--查询某个员工的年收入

 

create or replace function queryEmpIncome(eno in number) 

return number

is

       --定义变量保存月薪和奖金

       psal emp.sal%type;

       pcomm emp.comm%type;

begin

       --得到月薪和奖金

       select sal,comm into psal,pcomm from emp where empno=eno; 

       

       --返回年收入

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

 

end queryEmpIncome;

 

存储过程（无返回值）

带参数的存储过程

--给指定的员工涨100，并且打印涨前和涨后的薪水

 

create or replace procedure raiseSalary(eno in number)

is

       --定义变量保存涨前的薪水

       psal emp.sal%type;

begin

       --得到涨前的薪水

       select sal into psal from emp where empno=eno;

       

       --涨100

       update emp set sal=sal+100 where empno=eno;

       

       --要不要commit？

       

       dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));

end raiseSalary;

 

out参数

--查询某个员工的姓名 薪水和职位

 

/*

1、查询某个员工的所有信息 ---> out参数太多

2、查询某个部门中的所有员工信息 ----> 返回的是集合

*/

 

create or replace procedure queryEmpInformation(eno in number,

                                                pename out varchar2,

                                                psal   out number,

                                                pjob   out varchar2)

is

begin

  

   select ename,sal,job into pename,psal,pjob from emp where empno=eno;                                             

 

end queryEmpInformation;

 

在out参数中使用光标

 

--2、查询某个部门中的所有员工信息 ----> 返回的是集合

 

create or replace package mypackage is

 

       type empcursor is ref cursor;

       procedure queryEmpList(dno in number,empList out empcursor);

 

end mypackage;

/

create or replace package body mypackage is

 

       procedure queryEmpList(dno in number,empList out empcursor)

       as

       begin

         

          open empList for select * from emp where deptno=dno;

       

       end;

 

end mypackage;

 

JDBCUtils的主要内容

public class JDBCUtils {

	private static String driver = "oracle.jdbc.OracleDriver";
	private static String url = "jdbc:oracle:thin:@192.168.137.129:1521/orcl";
	private static String user = "scott";
	private static String password = "tiger";
	
	static{
		//注册驱动
		//DriverManager.registerDriver(driver)
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/*
	 * 运行Java：
	 * java -Xms100M -Xmx200M HelloWorld
	 * 
	 * 技术方向：
	 * 1、性能优化
	 * 2、故障诊断：死锁(JDK: ThreadDump)
	 *               Oracle: 自动处理
	 */
	public static void release(Connection conn,Statement st,ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				rs = null; ///-----> 原因：Java GC: Java的GC不受代码的控制
			}
		}
		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;
			}
		}
	}
}

 

TestOracle 的主要内容

package demo;

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

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import org.junit.Test;

public class TestOracle {
/*
 * create or replace procedure queryEmpInformation(eno in number,
                                                pename out varchar2,
                                                psal   out number,
                                                pjob   out varchar2)
 */
	@Test
	public void testProcedure(){
		//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{call queryEmpInformation(?,?,?,?)}";
		
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			
			//对于in参数，赋值
			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.getString(2);
			double sal = call.getDouble(3);
			String job = call.getString(4);
			
			System.out.println(name+"\t"+sal+"\t"+job);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, null);
		}
	}

/*
 * create or replace function queryEmpIncome(eno in number) 
return number
 */
	@Test
	public void testFunction(){
		//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{?=call queryEmpIncome(?)}";
		
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.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();
		}finally{
			JDBCUtils.release(conn, call, null);
		}		
	}


	@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);
			
			//对于in参数，赋值
			call.setInt(1,20);
			
			//对于out参数，申明
			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+"\t"+sal);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, rs);
		}		
		
	}
}