Java中调用Oracle存储过程及存储函数

一. 编写测试所用存储函数或函数

--1.创建一个带参数的存储过程，没有返回值
--给指定的员工涨100元的工资，并且打印涨钱和涨后的工资
create or replace procedure raisesalary(eno in number)
as
    --定义一个变量保存涨前的薪水
   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;

/*
  如何调用：
  begin
    raisesalary(7839);
    raisesalary(7566);
    commit;
  end;
*/

---

--2.存储函数，通过return关键字返回员工年薪
--查询某个员工的年收入
create or replace function getYearMoney (eno in number)
return number
as
   --定义变量保存员工的薪水和奖金
   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;

---

--3.存储过程,通过in和out参数控制输入和输出
--查询某个员工的姓名，工资和工作
create or replace procedure getEmpInfo(eno in number,
                                       pename out varchar2,
                                       psal   out number,
                                       pjob   out  varchar2)
as
begin
    --得到该员工的姓名，工资，职位
    select ename,sal,job into pename,psal,pjob from emp where empno=eno;
end;

/*
调用
declare
    eno number;
    pename varchar2(200);
    psal number;
    pjob varchar2(200);
begin
  eno:=7369;
  getEmpInfo(eno,pename,psal,pjob);
  dbms_output.put_line('姓名：'|| pename);
  dbms_output.put_line('工资：'|| psal);
  dbms_output.put_line('职位：'|| pjob);
end;
*/

---

--4.定义包头和包体
    该包里面是定义存储函数和存储过程，只是未实现，要在包体中实现，有点类似java中抽象类。
--包头
CREATE OR REPLACE Package mypackage as
    type empcursor is ref cursor;--定义存储过程返回的数据类型，该过程返回游标，类似集合
    procedure queryEmpList(dno in number,empList out empcursor);
end mypackage;

--包体，即包头中定义的存储过程或函数的实现
CREATE OR REPLACE Package body mypackage as
    procedure queryEmpList(dno in number,empList out empcursor) as
    begin
      --打开光标
      open empList for select * from emp where deptno=dno;
    end queryEmpList;
end mypackage;

二. 测试前准备工作
- 准备jdbc驱动 本测试使用 ojdbc14-10.2.0.4.0.jar
- 准备连接数据库等相关工具类

package demo.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:@127.0.0.1:1521:orcl";
    private static String user = "scott";
    private static String password = "tiger";

    //注册数据库驱动
    static {
        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;
    }

    //释放数据库的资源
    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;
            }
        }
    }
}

三. 编写java类调用存储过程或函数
主要是通过CallableStatement接口进行调用

//调用存储过程，getEmpInfo
package demo.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import org.junit.Test;
import demo.Utils.JDBCUtils;
import oracle.jdbc.driver.OracleTypes;
public class TestProcedure {
    @Test
    public void testProcedure() {
        String sql = "{call getEmpInfo(?,?,?,?)}";
        Connection conn = null;
        CallableStatement call = null;
        try {
            //得到一个链接
            conn = JDBCUtils.getConnection();
            //通过链接创建出statement
            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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, call, null);
        }
    }
}

---

//调用存储函数，getYearMoney
package demo.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import org.junit.Test;
import demo.Utils.JDBCUtils;
import oracle.jdbc.driver.OracleTypes;
public class TestFunction {
    @Test
    public void testFunction () {
        String sql = "{?=call getYearMoney(?)}";
        Connection conn = null;
        CallableStatement call = null;
        try {
            //得到一个链接
            conn = JDBCUtils.getConnection();
            //通过链接创建出statement
            call = conn.prepareCall(sql); 

            //对于输出参数，声明
            call.registerOutParameter(1, OracleTypes.NUMBER);
            //对于输入参数，赋值
            call.setInt(2, 7839);

            //执行调用
            call.execute();
            //取出结果
            double sals = call.getDouble(1);
            System.out.println("年薪为："+sals);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, call, null);
        }
    }
}

---

//调用包中定义的存储过程，与普通存储过程区别在于，包中定义存储过程或存储函数可以返回游标，并在java中进行解析，这样比在java中拿到结果集执行效率略高些。
package demo.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import demo.Utils.JDBCUtils;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
public class TestCursor {
    @Test
    public void testCursor(){
        String sql = "{call mypackage.queryEmpList(?,?)}";
        Connection conn = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            //得到一个链接
            conn = JDBCUtils.getConnection();
            //通过链接创建出statement
            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()) {
                //取出该员工的员工号，姓名，薪水和职位
                int empno = rs.getInt("empno");
                String name = rs.getString("ename");
                double salary = rs.getDouble("sal");
                String job = rs.getString("job");
                System.out.println(empno+"\t"+name+"\t"+salary+"\t"+job);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, call, rs);
        }
    }
}